When it comes to our marketing, I’m lazy.
I don’t like digging through a dozen reports to see what’s working.
So, I built a powerful dashboard that answers the simple, but wildly important question…
Which marketing channel(s) should we be focusing on?
This post will explore how to find your best marketing channel using our BigQuery x Data Studio dashboard.
Ready to dive in? We’re giving away our dashboard template FREE! Follow the instructions through the link below to get started.
The template is a stripped down version of our BigQuery Agency Data Pipeline. It’s pretty advanced stuff – if you find the workbook confusing, we have 2 options:
Let’s get into it!
Traffic here at CIFL comes mainly from organic search and our YouTube channel.
But after building our first piece of software, we’ve decided to triple down on our marketing.
This starts by digging deeper into Google Analytics data for insights…Is there anywhere we should be investing marketing dollars?
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 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
divided by.
% of total monthly sessions driven by the same This allows us to see the relative conversion efficiency of a landing page or channel – and answers 3 fundamental questions: You can create an index like this for any dataset – it’s just a technique for normalizing your data to a score of 1. We ran the GACI dashboard for our website, www.codingisforlosers.com. In the table below, 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 customers – 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! 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. The Sheet is basically crapping out before you’ve started your analysis – no bueno. That’s why at CIFL we’ve moved to doing this type of analysis in data pipelines built on the Agency Data Pipeline stack: Once set up, the entire pipeline runs itself, keeping the analysis refreshed on whichever schedule you require. There are a few steps to follow when wiring up this analysis on your own site. There are two types of data required in this (or any) data pipeline: 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. 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. 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: To dive into building out your own data pipeline, don’t hesitate to holler. We’d love to help your team build your own.
Applying the GACI to our website
Diving deeper
How the pipeline’s built
Firing up your own conversion index analysis
1. Get raw data flowing
2. Get your SQL models rolling
3. Visualize your conversion index in Data Studio
Get off the ground quickly, with customizable data pipeline Recipes for BigQuery.
GET COOKING