📘 We wrote a best seller!

📘 See our best seller

WEBRIS Digital Agency

Our Internal Tech Stack

  • Category : Strategy
  • July 30, 2020

Do any of the following statements resonate with your agency?

  • “We’re spending too much time wrangling data for reporting & analysis”
  • “We’re is struggling to attract & close enterprise accounts”
  • “Our reports and client deliverables could use improvement”

If so, read on – I’m going to show you how Google BigQuery can solve all these problems.

what is a data pipeline


WTF is Google BigQuery?

Here’s how Google explains it:

“BigQuery is a cloud-based data warehouse for storing + processing large datasets.”

Here’s how we explain it:


Allow me to illustrate with an example.

Let’s say you have an ecommerce shop as a client with 20,000 SKUs. Running any sort of data analysis on a website this size is slow, tedious or downright impossible.

Pulling the data down from each tool, formatting, analyzing…spreadsheets simply can’t handle the load.

Enter BigQuery.

BigQuery lets you just push all your data (via API) to a cloud based project where it can be processed and formatted at lightning speed. No more slow browsers or crashed spreadsheets, BigQuery can handle limitless data.

setting up bigquery

But wait…there’s more!

You can sync your BigQuery database with the visualization tool of your choice.

  • Google Sheets
  • Google Data Studio
  • Tableau
  • Looker

You’re able to combine data from different platforms into custom reports.

bigquery outputs


Why BigQuery for agencies?

There’s dozens of benefits to leveraging the power of BigQuery.

David offers his insights below:


1. Less work, better margins

A big portion of our team’s working hours get swallowed up in pulling data, formatting, analyzing and reporting on it. formatting it using macros in Excel. When you leverage BigQuery as a data pipeline, that becomes a thing of the past.

2. Unique capabilities that allow you to close more/bigger deals

With BigQuery, you have access to every piece of marketing data you could ever need, all in one place. With this data, you can create custom outputs that essentially function as your own custom software (we call them “Recipes”).

  • Technical SEO reports
  • Monthly status reports
  • Funnel visualizations

The ability to create custom Recipes is a tremendous value add for clients, and a huge unique selling proposition.

3. Limitless data processing capacity

No more crashing spreadsheets or slow software to deal with. Leverage enterprise processing power to speed up your work.

4. Complete data privacy

You own your own database, and control access to it. This is increasingly important when working with enterprise companies who insist on data privacy.


Examples of BigQuery in action

Here at CIFL, we help agencies leverage BigQuery through a process we call the “Agency Data Pipeline”. There are three main stages to creating an agency pipeline:

  1. Import all of raw data to your Google BigQuery database (GA, GSC, AdWords, FB Ads, etc)
  2. Write and apply all of the logic that would normally take place in Google Sheets automatically using SQL
  3. Output the information to where we need it, be that Google Sheets or Data Studio (aka Recipes)

The Recipes are the main automation vehicle. Recipes automate the tedious, repeatable reports that your team runs everyday.

Here are a few Recipes we use on a regular basis.


Recipe #1: The Website Quality Audit (WQA)

We call it the “swiss army knife” of SEO audits because it can be used in so many ways. Part technical audit, part content audit, part keyword research tool, it does a little bit of everything.

What goes into the Recipe?

  • A full Deepcrawl site scrape
  • Google Analytics traffic and conversions
  • Google Search Console impressions and CTRs
  • Majestic links
  • SEMrush keywords

What logic do we do to that data?

  • Map URL level data from all sources which leaves you an SEO data ‘map’
  • Auto assign page level actions based on a set of SEO best practice rules
  • Auto assign a ‘page type’ to each URL (i.e. blog post, product page, etc)

Imagine you have a 10,000 page eCommerce site to work through, how much time and effort would it save you to run this report and have the WQA just tell you what needs to be done for each page?

How to we analyze the Recipe?

The output is a Google Sheets audit (full of all the SEO data in the world) and a Data Studio dashboard.

bigquery recipe wqa


Recipe #2: The Ad Funnel Breakdown

The goal here is to get a picture of ROAS (return on ad spend) for ad campaigns by bringing together data from ad platforms, analytics, and CRMs. This includes adset, ad, and URL level data from:

What goes into the Recipe?

  • Google Ads
  • Bing Ads
  • Facebook Ads
  • Twitter Ads
  • LinkedIn Ads
  • Google Analytics
  • Adobe Analytics
  • Youtube ads

Any source of advertising data.

What logic do we do to that data?

From here we join the data from these individual sources into a consolidated view which includes:

  • Ad platform data (spend, impressions, clicks and conversions)
  • Analytics data (sessions, goal completions, transactions and revenue)
  • CRM data (leads, opportunities, deals and revenue)

How to we analyze the Recipe?

A Google Data Studio full funnel table showing ROAS by platform.

ad funnel breakdown


Recipe #3: Shopify Buyer Segmentation

The goal here is to get a picture of ROAS (return on ad spend) for ad campaigns by bringing together data from ad platforms, analytics, and CRMs. This includes adset, ad, and URL level data from:

What goes into the Recipe?

This Recipe calls for only 2 ingredients:

  • Shopify orders + customer data
  • Google Analytics order level attribution data

What logic do we do to that data?

  • Buyer-level frequency, spend and AOV
  • Growth + retention rates for each acquisition channel
  • Frequency segmentation, revenue segmentation

How to we analyze the Recipe?

A Google Data Studio full funnel table showing ROAS by platform.

shopify buyer segmentation


Start using BigQuery

You’ve got a few options to start utilizing BigQuery:

You can also join our free Slack Community to chat more about everything BigQuery.

Leave a Comment

Your email address will not be published. Required fields are marked *

Comments ( 0 )

About the author

David Krevitt

Find out more

Steal our organic traffic Blueprint.

We've helped hundreds of websites get more organic traffic from search engines. Give your email, get our blueprint.

Want to work with us?

We’ve helped hundreds of companies get more “ready to buy” visitors to their websites - we can do the same for you.