Connecting Sheets and BigQuery

Our Sheets connector for BigQuery, that allows you to push data up to BigQuery or query it back down into Sheets, is now live!

Grab the ‘Sheets + BigQuery connector’ workbook through the link below.

• Make a copy of the Sheets workbook

The connector is 100% free, we’re not asking for emails or money. But…I’m hoping you’ll subscribe to my YouTube Channel for future updates.

• Subscribe to my YouTube Channel

Let’s get into it!

PS – If you’re just getting started with BigQuery, you may want to pick up our free BigQuery course to accelerate your progress.

If you’re managing one website or marketing campaign, Google Sheets is perfect for analysis.

You can whip up a Sheets template to pull your Google Analytics, Facebook Ads and Adwords data into one place.

If you’re working for a startup or e-commerce business, this is likely all you’ll ever need.

But if you’re running a digital agency, where you may have 5, 10, 20, 50 or even 100 clients at a time – you pretty quickly reach the end of the spreadsheet.

You’ve reached the end of the spreadsheet when…

You’re using a template named “Ad Campaign Dashboard (NEW NEW LATEST).”

Questions like “where’d that Sheet go?” start to fly around the office.

And when you do, it’s time to put our adult pants on, and move your analytics to a grown-up database like Google BigQuery.

Why move from Sheets to BigQuery

The impact of pushing data up from Sheets into a database is hard to put into words…

 

You get to keep your training wheels on (by *seeing* your data in Sheets), but execute sweet moves by tapping into the power of SQL.

If you’re working in a team, this has two main effects:

 

1. Do better work

Every team wants to be data-driven, but you can’t really do that across an entire digital agency without a data…er…base.

Measuring ROI across an entire 30-person team just can’t be done from Google Sheets, never mind slicing data across clients, ad platforms, campaigns, and landing pages, etc.

Analyzing your entire set of results allows you to pick up on otherwise-hidden patterns in performance – patterns that hold the key to your growth as a team or agency.

 

2. Do less work

Wrangling data for reporting is, without question, the least favorite job of every digital marketing team.

Instead of spreading that pain out across different members of a team, data pipelining allows you to consolidate it in one person who’s really trained and equipped to handle it.

Time to fire your team from work they hate, and free them up to do work they love.

Bridging Google Sheets and BigQuery

At CIFL, we’re helping digital agencies make this leap, with a service called the Agency Data Pipeline.

But if you’re looking to set up your own BigQuery analytics warehouse, you’ll first need to take a long look in the mirror.

Keep looking.

OK, now we’re ready.

Now let’s apply that same level of focus to three aspects of your work with data, which you’ll need to define *very clearly* when working in BigQuery.

 

 

What data do you really need?

Moving into BigQuery is a great opportunity to trim the fat.

Since you need to define your database tables very specifically, there’s no reason to upload data that you know you’ll never use.

So for each unique data source (Google Analytics, FB Ads, etc), now’s the time to practice the Art of Tidying Up, and ask yourself “Does this bring me joy?”.

Then for each data source, you can ask yourself the same thing about the individual metrics they offer, since you’ll need to define exactly which columns you’ll need down the line (spend, clicks, conversions, etc).

If a data source or metric isn’t driving results for your team, time to donate it to the digital Salvation Army.

 

Can you automate pulling that data?

Once you’ve decided *which* data you’ll need in your BigQuery warehouse, you can dig into how you’ll get it.

If you’re already pulling data into Sheets using Blockspring or Supermetrics, then you’re all set.

But if you’re starting from scratch, then you’ll want to take your list of data sources, and compare it to the connectors offered by Supermetrics and Blockspring.

For most digital agencies and marketers, Supermetrics ability to pull data on multiple accounts in the same data query will make it the best fit.

Once you’ve got data pulled into Sheets, the tool at the bottom of the post will help you upload it to BigQuery.

 

What can you do with your data?

Now that you’ve got data properly trimmed and uploaded to BigQuery, you’re ready for the big show.

You’ve finally got the data-modeling power of SQL at your fingertips – what will you do with it?

There are a few basic BigQuery data modeling patterns we’ve found helpful for digital marketing data.

Dive deeper into these in our full BigQuery SQL tutorial.

1. Joining tables to build a full funnel

Say you’re running ad campaigns on LinkedIn for a B2B product.

LinkedIn will obviously give you spend, click and conversion data – but what about user engagement on your site, or ultimately sales in your accounting tool?

Using BigQuery makes it easy to tie those data sources together using JOINs, which can be super slow in Sheets.

 

2. Detecting anomalies

Picking out hot (or cold) landing pages and ad campaigns is super helpful in deciding where to spend your time.

But looking at this kind of momentum is almost impossible in Sheets (due to calculation time constraints).  In BigQuery, it becomes easy.

You can use ‘window’ or analytic functions to calculate moving averages across your data, and measure how your daily data compares to those averages.

 

3. Quality control

Are your FB Ads campaigns no longer being UTM tagged properly?

Did a landing page stop picking up conversions all of a sudden?

These kinds of unforced analytics tracking errors are unfortunately common, and totally preventable.

That same anomaly detection patterns above can be turned around to detect QC issues in your data.

How we use this at CIFL

Our internal data pipeline at CIFL is pretty simple, since there are three main channels that drive traffic to the blog and courses:

• Organic search
• YouTube
• Facebook Ads

The key for us is viewing conversion data across all of our channels in the same charts – so that we can see which content (blog posts, videos) are really driving CIFL forward.

We use the Sheets tool (same version you can grab below) to push data from four sources to BigQuery: Google Analytics, Google Search Console, Youtube and FB Ads.

At the end of a lot of data modeling (via BigQuery views), we end up with a consolidated dashboard that looks like so:

Update: based on popular demand, we also added the ability to pull data _back into _Sheets from BigQuery to the template below by writing SQL queries.

Ready to scale up to BigQuery?

Grab the Sheets <> BigQuery connector from our Getting Started with BigQuery course, where you’ll also find tons of other goodies.

If you need a hand building out your BigQuery data pipeline, always feel free to drop us a line.