How to Automate Your Agency with Google BigQuery

bigquery for agencies

Ryan Stewart

I've been helping marketers automate tedious tasks since '14. I love to talk shop, get at me on Twitter or Instagram.

Do any of the following statements resonate with your agency?

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.

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”).

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?

What logic do we do to that data?

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?

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:

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:

What logic do we do to that data?

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.

Ready to build your pipeline?

My name is David, and I help companies automate their data analysis in BigQuery. I’m standing by to chat about how we can help you get more done.

FIND OUT MORE