Connecting Sheets and BigQuery When you reach the end of the spreadsheet

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 9/13).”

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

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

Eager to bridge the gap from Sheets to BigQuery?  Skip to the bottom of the post to grab a Sheets template for pushing data to your own BigQuery analytics warehouse.

Why move from Sheets to BigQuery

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

via GIPHY

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:

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, nevermind 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.

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 (learn more here).

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.

FYI, the Sheets template shared in the Vault at the bottom of the post helps you with all three.

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.

Note: Data pipelining (ETL) tools like Stitch, Segment, and Funnel.io are also helpful for getting data into BigQuery, but from our experience none of them (yet) cover all of the data sources you’re likely to need.

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:

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.

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.

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 (shout out to CIFL student Tom Davenport for helping us run 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:

bigquery google sheets dashboard

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 template from the Vault below to roll your own data warehouse, or consider our fully managed Agency Data Pipeline service if you’re looking to swing for the fences.

20 Comments

  1. Hey – I love your blog and this is all very timely as just been researching this approach myself…
    However – you keep mentioning a template available from the Vault at the bottom but I can’t find this – am I being silly here?

  2. Hi, I’m using this template to learn from and it’s very nice. However, when I create my own Google Sheet and use a similar script to pull my data *back* into Sheets my BigQuery table, I keep getting a “No OAuth token with Google Drive scope was found”. The script works when I’m testing it with a public table. But when I point to my own tables, it kicks up the error.

    Is there something I’m missing?

  3. This is awesome. Thank you so much for setting this up.

    On Step 5 (pull queries from big query) can I dump the data into a tab in a different workbook instead of in the workbook containing the script?

    On Step 3 (push data to bigquery) the data in the sheet is appended to the table instead of overwriting. How do I overwrite instead of appending?

    Thank you!

    • Hey Alissa, thanks for the kind words. On your questions:

      1) Currently the template only writes to the sheet containing the script. Drop a note to help@codingisforlosers.com and we could potentially customize that for you.

      2) BigQuery is an append-only database, so there’s no concept of updating existing rows. There’s a time_of_entry field that automatically gets written by the template, which you can roll into a window function to pull the most recent value for each unique ID.

    • Hey Makato, you can access it from the form at the bottom of the post – it’ll take you to a private Trello board which contains all CIFL templates.

  4. HI, Sorry I am not able to see the form that will lead me to vault.

  5. What does the TRUE/FALSE in “Live?” do? I tried both options and both times the whole data was pushed to the table.

  6. Hello,

    Is there any way I can change the language from LegacySQL to Standard SQL somewhere in the API?

Submit a comment