How to Automate Your Agency with Google BigQuery

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.

 

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.

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.

 

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.

• Browse the demo dashboard here
• Check out the demo workbook here

 

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.

• Check out the demo report here

 

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.

• Check out the demo report here

 

Start using BigQuery

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

• Take our free course on how to learn and apply BigQuery through Data Pipelines
• Partner with CIFL to build your own Data Pipeline and set of ‘Recipes’
• Use our tool, Query.Recipes, to set up some existing recipes we’ve already cooked up

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