Good news! The CIFL Connector for BigQuery Sheets template, which allows you to push data from Google Sheets up to BigQuery or query it back down, is now live in the CIFL Template Vault - click here to grab it.
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 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? Grab our Sheets template for pushing data to your own BigQuery analytics warehouse from the CIFL Template Vault.
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:
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 Pipeline2.
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.
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.
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.
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.
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
- 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:
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 CIFL Connector for BigQuery Sheets template from the Template Vault to roll your own data warehouse, or consider our fully managed Agency Data Pipeline service if you’re looking to swing for the fences.