Track your monthly channel results with Google Spreadsheet

Create a simple dashboard with Google Spreadsheet and analyze your channel performance with ease.

 

What are the main objectives?

  • Learn about “Channel Grouping” on Google Analytics
  • Customize the “Default Channel Grouping”
  • Configure a report with basic KPIs
  • Create a report
  • Add different attribution models (Link to Part 2)

 

Before getting started

We will assume that you already know Google Spreadsheet basics. Otherwise, we would encourage you to check out Google help or read our first article on how to create a Google Ads Dashboard.

 

Channel report on Google Analytics

Channels and Google Analytics

Do not get lost on Google Analytics and go straight to one of the best reports on Google Analytics.

Why do we really like this report?

The channel report gives you a great snapshot of your overall results by “medium“. In other words, instead of having a thorough breakdown of each source and medium, this report allows you to grasp the overall health of your website. Up to you then, to go more granular if you need.

 

What can you find on this report?

  • Organic Search: People having found your website organically (e.g. Google Search, Bing Search, Knowledge graph on Google…)
  • Paid Search: People having clicked on an ad to access your website (e.g. Google Ads, Bing Ads…)
  • Social: People having interacted with your social networks (e.g. Facebook, Instagram…)
  • Direct: People that went directly on your website (e.g. bookmarks, automatic fill…)
  • Referral: People coming from a third party website.

 

Is it enough for your business?

Let say that you are running a competition with a third party company. How could you easily track the traffic coming from their website? By default, this traffic is going to be considered as “referral traffic”, which risks being meaningless if you wish to use the channel report efficiently. That’s the reason why we often encourage to customize how and which channels are displayed.

 

Google Analytics would allow us tracking this example using different solutions. We chose to only focus on channels regarding the report we’ll be creating later.

 

Customize the “Default Channel Grouping”

You can choose how you group your channels. If by default, there is only one “channel grouping”, you can create additional ones to fit your needs (We’ll cover this topic in one of our next blog articles).

To do it, just go to your admin interface, select the view on which you’d like to apply the change, and then go to the channel setting section. If there is more than one channel grouping, please just select the “default channel grouping”.

 

How to add another channel?

    1. 1 –  Add a new channel and pick a name
      2 – Define rules: Select Source/Medium in the drop down
      3 – Select “contains”
      4 – Add the source/medium (Google should automatically autofill the name of the source/medium if you have any traffic coming from it)

Few words on the channel order

Google applies the rules by order. In order to succeed, the new channel needs to be ordered before the channel it should belong to. For instance, if you add a new referral source (e.g. traffic coming from a third party website), you’ll need to list that new channel above “Referral”. Otherwise, the new rule won’t be applied. Please leave a comment if you have any questions on this specific point.

 

“Channel report” on Google Spreadsheet

Configure a report with basic KPIs

We won’t mention the formulas needed on this step as most of them are the same than the one used in our previous article and we keep sharing the template with you (Link to template: Click here). We remain at your disposal in case.

Channel report Google Spreadsheet configuration

3 important elements

  • Report Name: We would advise you against changing the report names.
  • Report period: We chose to select 3 periods: Current month, Previous Month, Previous Year. Dates are created automatically with formulas allowing you to update your report when you need it.
  • Metrics & Dimensions: This template needs to remain simple. We only selected basic ones, but you can add as many compatible ones the API authorizes you.

 

How to create the report?

We simplified the template we’re sharing with you (Link to template: Click here). In order to get the best out of the document, you might just need to be aware of the following elements.

Monthly Channel Report - Google Spreadsheet import

1 – Channels

In the first column, you just need to list the channel you’d like to track

 

2 – Period

In the second column, you just need to use the period you’d like to track. To do so, you just need to add the report name you chose previously.

In order to reduce the amount of copy-pasting, we chose to automatically import the period with the following formulas.

    • Current Month -> =’Report Configuration’!$D$2
    • Previous Month -> =’Report Configuration’!$E$2
    • Previous Year ->=’Report Configuration’!$F$2

Follow the same process for each of your channel.

 

3 – Sessions & Goals

Sessions:

=SUMIF(INDIRECT(CONCATENATE(“‘”,B2,”‘!$A$16:$A$37″)),INDIRECT(“A2”),INDIRECT(CONCATENATE(“‘”,B2,”‘!$B$16:$B$37″)))

Goals:

=SUMIF(INDIRECT(CONCATENATE(“‘”,B2,”‘!$A$16:$A$37″)),INDIRECT(“A2”),INDIRECT(CONCATENATE(“‘”,B2,”‘!$C$16:$C$37″)))

 


We hope that this spreadsheet will as useful for you as it is for us. We remain at your disposal if you have any questions.

In our next part (Link to part 2), we’ll show you how to add another layer of analysis thanks to the multi-channel import.