This post is excerpted from the Build your Agency Data Pipeline course, which teaches you how power up your digital agency’s data analysis process.
To copy this analysis for yourself, grab the Tracking Plan from the Template Vault at the bottom of the post.
Every worthwhile data analysis starts with a question.
I’m super lazy when it comes to marketing this blog, so I set out to answer one question with our Google Analytics data:
Which marketing channel(s) should we be focusing on?
Over the past 2 years, traffic at CIFL has primarily been driven by organic search (for keywords like ‘google sheets query’) and our Youtube channel.
But the time had come to really dig into our GA data and take action – is there anywhere I should be investing resources that I’m not?
Being lazy like I mentioned, I always seek to answer a question with a single metric.
So I used the Agency Data Pipeline analysis process to cook up what we’ll call the ‘Google Analytics Conversion Index.’
The GACI (for short) ranks every marketing channel and landing page combination against all others over time:
The index is pretty simple:
% of total monthly conversions driven by the landing page / channel
% of total monthly sessions driven by the same
This allows us to see the relative conversion efficiency of a landing page or channel – is it better or worse than others at converting visitors to email subscribers?
You can create an index like this for any dataset – it’s just a technique for normalizing your data to a score of 1.
What’d we learn?
In the table above, you can see that YouTube is off the charts, with an index score of 7-12.
That means it’s 7-12 times more efficient than the average channel at converting visitors into email subscribers – not surprising given the high intent of a click-through from a YouTube video.
You can also see that Organic Search is slightly below average efficiency, with an index score of .7 – .9 depending on the month.
That’s not too surprising, given the informational intent of a search query like ‘Google Sheets query.’ The page that ranks covers in minute detail (meaning there’s a lower need to sign up for additional resources).
So the first takeaway is – make way more YouTube videos, and spend more time, energy and potentially $$ driving traffic to them.
Using a single normalized metric like this allows us to answer ancillary questions using a couple simple tables in Google Data Studio (see our report here), rather than requiring an entire dashboard:
You know how I feel about dashboards :).
For example, asking the question ‘Which landing pages drove that channel’s performance?’ allows us to view a relative conversion index within each channel.
From this we learned that templates like our Agency Project Management tracker convert really well from Organic Search (with a CI of 2.07 compared to the Query Function post’s .56).
That’s not too surprising, given the post is more action-oriented (ie download this template to do this thing), rather than information-oriented (learn about this Sheets function). Templates ftw!
How the pipeline’s built
If you’re eager to set this analysis up for yourself, grab the Tracking Plan with a detailed setup checklist from the Template Vault at the bottom of the post.
Although we’re huge fans here of Google Sheets, you can easily bust a Sheet’s seams doing even a simple Google Analytics time series analysis.
Let’s do the math:
A Sheet can hold a maximum of 2mm cells.
For this analysis, we split GA data by 9 columns:
Date, source, medium, campaign, landing page path, sessions, email signups conversions, course purchase conversions and conversion value
Each year for CIFL, we have roughly 70k rows of data at that granularity – meaning 9 * 70k = 630k cells per year.
That means if you’re analyzing 3 years of data (the history of this blog), you’ll have about 1.8mm cells occupied just for raw data.
The Sheet is basically full before you’ve started your analysis – no bueno.
And that’s on the CIFL blog, a site with less than 50 pages. If you’re running an eCommerce shop that has 1k+ product pages, you’re fully out of luck trying to doing daily landing-page and channel-level analysis Sheets.
That’s why at CIFL we’ve moved to doing this type of analysis in data pipelines built on the Agency Data Pipeline stack:
- BigQuery as a data warehouse
- Stitch + Google Apps Script to push data to BigQuery
- The DBT SQL framework to model raw data into tables of metrics, and Sinter to run them from ‘le cloud’
- Data Studio to visualize it all
Once set up, the entire pipeline runs itself, keeping the analysis refreshed on whichever schedule you require.
Firing up your own conversion index analysis
There are a few steps to follow when wiring up this analysis on your own site (the Tracking Plan available from the Template Vault form below contains a detailed walkthrough).
Get raw data flowing
There are two types of data required in this (or any) data pipeline:
- The raw data itself, from Google Analytics
- Some administrative mapping data, for example to specify how each Google Analytics source / medium translates into a ‘Channel’
I recommend using Stitch for pushing the first type of raw data to BigQuery, and the Tracking Plan itself (which contains a version of our Sheets to BigQuery connector) to push administrative settings to BigQuery.
Get your SQL models rolling
The SQL models from this Conversion Index analysis are open-sourced on Github.
You can fork them for yourself and run them directly via Sinter on your BigQuery database.
Visualize your conversion index in Data Studio
Once you’ve got your raw data flowing and modeled, you’re ready to copy our Data Studio template and fire it up for yourself.
You’ll probably want to hot-rod all 3 of these steps for yourself:
- Adding in different data sources (FB Ads cost data, for example)
- Modeling data in SQL to fit those new data sources / the conversion demands of your own site
- Visualizing your tables in Data Studio in a way that speaks to your team
To dive into building out your own data pipeline, grab the Tracking Plan template from the Vault below.
Don’t hesitate to holler to email@example.com with any questions, or if you’d like our team to build this analysis out for your site(s).