Automation Innovation: Beginner’s Guide to Google Sheets’ GA API Integration

It’s 4pm on a Thursday. You have six monthly dashboards on your to-do list, and a boss/client(s) breathing down your neck. You know it’s a long, treacherous road to get the Google Analytics data you need. How will you get all of this done in time?

Now, imagine this option: You come into work on Thursday and all the data you need is there, ready to be picked apart and analyzed.

“Kevin, surely that’s too good to be true. It can’t be that easy can it? This can pull the dimensions and metrics I need based on my segments and filters?”

Yes astute reader, it’s pretty much that easy.

“So how does this work?”

Let me show you!

Where Can I Find This Tool?

If you use Google Sheets to power your reporting, adding this feature is simple. See the below screenshots for how to add:

1. Click on the Add-ons option on the top left of your Google sheet:

2. Select the “Get add-ons...” option

3. Hover over the Google Analytics add-on and click the blue +FREE button:

4. You’ll get a request for permission pop-up. Accepting this gives Google permission to use your Google Analytics data for the add-on, edit spreadsheets, etc. You can update these permissions in your Account Permissions at any time in the future.

5. Voilà! Your GA add-on is now installed

“Awesome! So my data is completely automated now?”

Slow down there buddy...let’s take this one step at a time.

How to Create a Report

Now when you click on the Add-ons drop down on your sheet, you will see a Google Analytics option. Hover over that and select the first option (Create New Report):

Here’s how to fill out each section:

1. Name Your Report
   • A simple description of the data you’re trying to pull
     • I like to always include the time frame since Google pulls this information for the sheet/tab name, so it helps to keep track of what you’re pulling
2. Select Account Information
   • Select the account, property, and view (profile) that you want to pull data from
     • Note, you must be logged in under the email address which has access to the Google Analytics information you need
3. Choose Metrics and Dimensions
   • Type in the metrics and dimensions that you want to pull, and select them
     • Google has this terrific resource which allows you to look up the naming conventions for all dimensions and metrics
4. Click create report

When you create your report you get an output:

“What are all these fields, and what can I do with them?”

Google has another resource on this (which has details on the actual terminology you need to use), but here are the quick takeaways (with links to Google’s more detailed breakdowns):

• Report Name: Whatever you want your output sheet to be called.

• Type: You’re probably going to want to keep this as “core”, but you can do some cool things with multi-channel funnels if you want to get adventurous.

• View (Profile) ID / ids: You can find this in your Google Analytics admin settings under View Settings, but if you’re creating a report via the add-on, Google pulls it for you (aren't they so helpful!).

• Start/End Date: Whatever time period you want to pull for. Note, if you pull for a long time period (with many dimensions and metrics) there’s a good chance you report is going to get sampled. There are some good tips for how to avoid this here. I highly recommend only entering the date you need once and then using cell references to build off of that. You can use the EOMONTH formula to make everything even easier.

• Last N Days: Defaults to 7 when you create a report. Is in reference to full days (i.e. will not pull today). You can use this instead of start/end dates if you prefer.

• Metrics/Dimensions: Again, use this link to look-up what you need, and separate your metrics/dimensions either with a comma or on a new line. You are limited to 10 metrics and 7 dimensions for each report column.

• Sort: You choose which dimensions and metrics you wish to sort by. You can sort by multiple, and separate them either with a comma or on a new line for the sort order you want. To sort by descending order, simply add a minus symbol (-) before the dimension/metric (i.e. -ga:bounces)

• Filters: Allows you to get only the data you need. You can create lots of useful filters using regular expressions (also known as RegEx) here. You can base filters off of metrics, dimensions, or both.
  • Here’s a nice resource to learn RegEx if you’re feeling ambitious

• Segment: Breaks down the data to include only the section you want. The easiest way to pull segment IDs is through Google’s query explorer.

• Sampling Level: You probably should always choose HIGHER_PRECISION to attempt to avoid sampling issues.

• Start Index: This could be used if you’re pulling over 10,000 rows of data.

• Max Results: The default number of results to show is 1,000, and the maximum you can specify is 10,000 (see how it ties in with Start Index?). If you’re pulling lots of data, or only need so much (say a top 10), you can specify that here.

• Spreadsheet URL: You can have the report results show up in another spreadsheet if you wish. You do need edit permissions for that spreadsheet enabled.

Once you set up one report, if you’re going to have similar reports, you can simply copy and paste your report set-up to the next column and make any necessary changes (say to date range). They will all run when you run your report.

“Alright Kevin, I’ve got this great report set-up, now how do I get my data?”

Simple. With the click of a button.

How to Run a Report

Once your report is set-up, click on the Add-ons dropdown, hover over Google Analytics again, and select Run reports. You’ll get spinning arrows that pop-up in a black box that says “Working”, and then hopefully you get a pop-up like this:

If you don’t get this message, you’ll get an error message, of which the typical causes are:

• You didn’t name a report (Fix: Name the report)
• You’re trying to pull too many metrics/dimensions at once (Fix: Limit the number of metrics/dimension you’re pulling or make more reports)
• You messed up the syntax for your parameters (Fix: Update the syntax using Google’s parameter reference)
• You attempted to combine incompatible dimension and metrics - i.e. sessions and pages (Fix: Update to have compatible dimensions and metrics - like sessions and landing pages)

An error message looks like this:

Note, you do have a quota for how many times you can run the report per day, but the limit is 50,000 requests and it’s based on the property/view level for the account you’re pulling from. So, unless you have some serious automation going, you should be okay.

After your report is run, you’ll get results, but also totals for all results, and some basic report information:

• Last Run On (helpful to make sure your automation works)
• View (Profile) Name (a good reminder in case you forget)
• Total Results Found
• Total Results Returned (difference between found/returned can show you when you need to run multiple reports)
• Contains Sampled Data (if this says Yes, you’ll want to fix it)

“Awesome, but where’s the automation you promised?”

It’s just a simple set-up. Let me show you how.

How to Schedule Reports

Going back to the Add-ons drop down, hover over Google Analytics and select Schedule reports. You’ll get another quick “working” message and then a pop-up asking you to enable reports to run automatically. Check that box. You should then see this:

You’ll note that you get two drop downs. Your options for these are:

• Run every hour
  • If selected, the second drop down disappears
• Run every day
  • You choose the hour of the day you want it to run
• Run every week
  • You choose the day of the week and the hour of the day you want it to run
• Run every month
  • You choose the day of the month and the hour of the day you want it to run

All you have to do now is pick the option that works best for you, hit save, and all your data will come in when you have it scheduled to.

“This makes my reporting and analysis so much easier!”

Right!? Imagine how many hours of manual data pulls you can eliminate with this tool.

Think of how powerful the capabilities of this add-on are. You can have information pulled into hidden sheets to automatically generate and update graphs (although Excel’s graphs are much better then Sheets’). You can set-up formulas such as vlookups to pull over data into other sheets. You can lump your data pulls together with pivot tables and get all kinds of helpful insights. You can be sure I’ll be posting more information on intermediate and advanced uses of this add-on in the future.

Now it’s time for you to dig in and see what you can do with your data. How can you make your reporting and analysis easier moving forward? What tips and tricks can you find? Share and let me know in the comments. Happy data digging!