There are a bunch of great tools (free and paid) to help you understand and interpret your site traffic. I’m going to add to the pantheon by giving you a tool for free that I’ve made as a kind of dashboard for showing you week on week traffic metrics from Google Analytics. It’s built around the Google Analytics Add On for Google Sheets and requires the following:
- A Google Account.
- Access to Google Analytics.
- Access to Google Sheets.
- Google Chrome Browser.
- The Google Analytics Add On for Google Sheets.
I’ll talk you through each item and explain how they work together.
Before You Can Use the Google Analytics Add On for Google Sheets
Before we begin, you’ll need to have a Google Account. If you don’t yet have one set up you can find out more about how to do so here: Create a Google Account.
Once you have a Google Account you’ll automatically have access to Google Sheets and Google Analytics.
However, you’ll need to set up Google Analytics so it’s properly tracking visits to your site. Here’s a guide from the horse’s mouth: Get started with Analytics.
This only works with Google Chrome, so you’ll need to install that too if you don’t have it: Google Chrome Installation.
Once you’ve installed Chrome, you can install and use the Google Analytics Add On for Google Sheets. Fire up Chrome and visit the following page to install the add on (from Chrome itself) here: Install Google Analytics Add On for Google Sheets.
My Dashboard for Google Sheets using the Google Analytics Add On
You can copy the Google Sheet I use here:
SideGains’ Free Google Sheets Analytics Dashboard
When you have a Google Account, clicking the link above will automatically copy the dashboard to your Google Sheets account.
Using the SideGains Google Analytics Dashboard
The first step is to go to Google Docs and find the SideGains Google Analytics Dashboard you’ve copied.
Click on the sheet and it will open the dashboard. You should see the Google Sheet and it’ll look like this:
Everything’s will show zero at the moment and it’ll remain this way until you’ve added your Google Analytics view ID into the sheet, which pulls data into it when you run the add on.
The first thing you need to do is add your Google Analytics view ID into cell B2.
You can find the ID in your account by selecting the required account and view from the top left hand side of the page when logged into Google Analytics.
Clicking shows you the views relating to the selected account. Beneath the name of your view you see a numeric ID.
Make a note of the view ID and then type it into cell B2 of the SideGains Google Analytics Dashboard sheet.
At this point you’re ready to run the add on to update the sheet with your data.
You do this directly in the sheet itself by clicking: Add-ons > Google Analytics > Run reports.
The add on then does the work pulling your data from your Analytics account and populating it into the sheet.
You can run this process as often as you want to update the data, or as you can see in the image, schedule it to run at times to suit you.
I don’t schedule it personally since I only run it as and when I want to get a quick update for how things are going today.
What Does the Dashboard Show?
It’s a snapshot really showing:
- Today vs. the same day last week.
- Yesterday vs. the same day last week.
- The last 7 days (from yesterday) vs. the same 7 days prior.
- The current month (up to yesterday) vs. the previous month.
- Last month vs. the month before.
- The current year (up to yesterday) vs. last year.
The dashboard shows:
- Users
- Sessions
- % New Sessions
- Pages Per Session
- Avg. Session Duration
- Bounce Rate
Once the add on has run it populates the sheet, which contains formulas to calculate the differences between the values for the periods it shows.
As I say, the idea is to provide a quick reference of key stats throughout the day, but I’m working on some more sophisticated dashboards, which I’ll add to this page when I’m happy with them.
A Few Little Caveats…
- Since this is a fairly recent development project, there may be a few oddities about the way it works.
- You must also bear in mind that only one tab is visible: there are multiple tabs that contain the data for each dataset and also configuration and data sheets required to run the report… it’s best not to unhide these and play with them as the report will likely not run if you do.
- If you notice anything squiffy about how the report runs, or believe it isn’t working, please let me know.
- Feel free to use the sheet as you please… once you’ve copied, it no-one else can see any of the data contained within it unless you decide to share your Google sheet with them.
Please feel free to share my original Google Sheet through this link if you’d like:
SideGains’ Free Google Sheets Analytics Dashboard
That’s it for now.
Paul
Please let me know in the comments below if you’ve used this and what you thought about it.
Leave a Reply