How to Find Your Best Marketing Channel

how to find your best marketing channel

David Krevitt

Lover of laziness, connoisseur of lean-back capitalism. Potentially the #1 user of Google Sheets in the world.

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.

best marketing channel data studio report

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:

  1. Work with us directly to build you a custom Data Pipeline (hit me up here)
  2. Check out our Agency Data Pipeline course

Let’s get into it!

The Google Analytics Conversion Index (GACI)

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:

  1. Which marketing platform performed best?
  2. Can that platform scale?
  3. Which landing pages drove that performance?

You can create an index like this for any dataset – it’s just a technique for normalizing your data to a score of 1.

Applying the GACI to our website

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.

gaci youtube

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.

gaci seo 2

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.

Diving deeper

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.

alt text

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

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:

  • 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 DBT Cloud 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.

1. 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.

 

2. 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.

 

3. 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, don’t hesitate to holler. We’d love to help your team build your own.

Ready to build your pipeline?

My name is David, and I help companies automate their data analysis in BigQuery. I’m standing by to chat about how we can help you get more done.

FIND OUT MORE