Build an Analytics-Driven Dashboard in Google Spreadsheets

I’ve always yearned for custom, automated analytics reporting. To me it’s the holy grail of data dissemination for website owners, seo’s, and the like.

Once you get that data into a spreadsheet you can do just about anything with it. Up until recently though, nothing has been customizable enough, or easy enough to use in order to fully realize this. At least as far as I’m aware (I have been out of the game a little while – so correct me if I’m wrong).

Analytics integration with Google Spreadsheets isn’t entirely new, but the previous tool was pretty clunky, inspiring an “I’ll try that out someday” feeling.

When I found out there was a new Add-on that simplified the process I got to work immediately. The first thing I found is that there’s next to no guidance out there. Anything I could find is based on the older methods, and didn’t really help me out much (a lay-programmer).

Here’s where I started, with Google’s documentation.

Pretty sparse, but just enough information to hook me in for the next two days.

… Anyway, I toiled with the reference sheets and when night turned to day again I had built a crazy mammoth dashboard for one of our clients. In so doing I learned my way around the API and, not being much of a spreadsheet master, learned a lot about Google’s application too.

Get to it already…

Yes. Let’s make a simple dashboard together. You can get some incredibly detailed data in your spreadsheet. Basically anything you can see in Analytics, you can do here – and then from there the analysis is infinite… but this is going to be a long post as it is, so I’m just going to focus on the building blocks.

This tutorial will familiarize you with pulling data from Analytics and showing it in a dashboard-type-deal built in a Google Spreashseet.

You can view this very spreadsheet I’m going build right here. You can even download it and use it! I suggest you build one from scratch first though, to see how it ticks.

View Demo Sheet

Install the Add-on

  • Open a new Google Spreadsheet
  • Click Add-ons and select Get Add-ons
  • Search for Google Analytics and install the free Add-on
  • Click Add-ons > Google Analytics > Create a New Report

You should get a new pane on the right which allows you to build “reports.”

Reports are modular groups of queries that create their own sheet within your spreadsheet when run.

You can collect multiple metrics & dimensions with each report, but you’ll want to set up multiple reports to collect information from different dates, or with different combinations of metrics and dimensions.

Build a basic traffic overview report

This will give us Sessions, Users, New Users, and Goal Completions for the current reporting period, the previous one, and this period last year.

Give your report a name (you can change it later) and select the account, property, and view you want to pull from.

I’m naming mine “Basic Traffic Metrics CURRENT” for this tutorial.

We only need metrics for this one, so you can add them to the metrics field like so (we’ll look at dimensions and filters later).

Now click create report and a new sheet will be created containing your first query. This will be called “Report Configuration.”

This is where you can build all your queries. Further queries you build using the tool (in this spreadsheet) will show up here.

I suggest giving it a color.

Set up dynamic dates

Before I do anything else, I like to set up some dynamic date insertion se we can control it from the front end. You can also use this approach for any other variables you want to control.

Create a new sheet and call it dates. Give it a colour for quick reference

Define some start and end dates for your date ranges. I used “This Month, Last Month, and This Month Last Year.”

Jazz it up too, if ‘yer fancy.

Go back to your Report Configuration sheet and look at your Basic Traffic Metrics report. By default it’s set to collect data for the last 7 days. This parameter is perfectly good, but we want to use a dynamically changeable date range, so delete it.

In the Start Date cell, hit

=

then click the dates sheet tab, and select the start date for your first period. Press return, and your start date will now reflect whatever you put in that cell in the dates sheet.

Now do the same for the end date.

Next we’ll copy the same values from our first report, and paste them in the next column. This gives us two separate reports.

Name the second report “Basic Traffic Metrics PREVIOUS.”

Do this drill once more, distinguishing it with “LAST,” and then supplement the dates for the two new reports as you did with the first.

This will be enough to build our first dashboard “widget!”

Click Add-ons > Google Analytics > Run Reports.

If everything is copacetic, all 3 reports will run successfully and you’ll get a dialog box saying so. You’ll also see three new sheets, named corresponding to your report names! Nice!

If not, take another look at your values, silly :P

Now let’s head back to our original sheet (if you got rid of it or something, just make a new one). This will be our dashboard!

Let’s call it “Dashboard” and colour it for cool points.

Here you can get super creative, but we’ll keep it plain for the moment. Make a table to display your data.

Use the same method for filling in cells here as you did with the dates. So, to get the value for Sessions in the Current period, click the cell, hit

=

select the “Basic Traffic Metrics CURRENT” sheet and your desired cell, then hit return.

If you’re a keener you can also just put the formulas in manually.

Repeat for the remaining cells, and you’ve got yourself a shiny new dashboard widget!

1 piece of flair

Now let’s make a quick callout to show traffic change (read: how badly my traffic has dropped since last month).

Pick a cell, type

=SUM(

and subtract the Current Sessions value from the Previous one.

Now select the cell again and right-click > Conditional Formatting… and add the following rules:

Now we’ve got a pretty nifty, fully dynamic dashboard pulling data via the Analytics API, populating a table, and culminating in some conditionally formatted awesomeness.

Go us!

Change your date values and run it again to see everything in action!

Dimensions, filters & sorting

This is where we can bend the universe to our will. It’s also where things get more complicated. I’m going to run through one more example to demonstrate the use of dimensions, filters and sorting – and output one more table in our dashboard.

It’s still pretty easy, but I’m going to assume at this point you’ve played around with things  a bit and are getting the hang of it.

We’re going to add a table to show only organic, unbranded phrases, in descending order of sessions from the last year.

Here are the pertinent references:

Head back to your Report Configuration sheet. Use the report builder, or simply make another copy of one of your existing reports (I’ll be going cowboy from here on).

We’ll call this one “Organic Unbranded Sessions PAST YR” …’cause it’s my tutorial Y’all. Yeehaw.

We need Sessions for our Metric and Keyword for our Dimension.

We want to filter out anything branded. In this case, anything with my name. You can get all crazy with your regular expressions, but here I’ll just filter out “pierno” (use the Filter Syntax reference to get more grainy). Put this in the Filters field:

ga:keyword!@pierno

We want our list to start with the highest searched phrases so we’ll put this in the Sort field:

-ga:sessions

And we should probably set a limit for the results… Let’s go with 100.

TIP: If you only want to run certain reports, just remove the titles from  the Report Name cell. Only reports with titles will run.

Run it!

Now, dashboardify

Make your table (you can also play with widgets and any other data tools in Gsheets).

Now select the Keyword cell, and then hit

=

Select the first keyword from the new sheet, and hit return. Now drag the cell selector over to the adjacent Sessions cell. NOW drag both selected cells down until you don’t want to see my crappy keywords anymore.

That ought to be enough to get you on your way, Partner! Now hit the dusty trail and show us what kind of data wranglin’ you can muster.

Seriously though, if you made it this far let me know how you make out, if you have any tips, or if I’ve made any woeful omissions!

And again, you can view/download my demo sheet live on Google Drive and hack it to pieces!

[su_button url=”https://docs.google.com/spreadsheets/d/1LsCpXufbcDR7Zd9oc5rzzyaF8ggRAkstmklMSU8j91g/edit?usp=sharing” wide=”yes” center=”yes”  size=”6″ target=”blank” radius=”0″ icon=”icon: external-link” class=”blogbutton”]View Demo Sheet[/su_button]