How To Make Google Analytics Talk To Excel, In Plain English
I’m spoiled now. I rarely go into the Google Analytics (GA) interface anymore. You know why? There are cool Excel plugins that will pull your data right into Excel. Of course, most of them are PC-swim only. Sorry, Mac users. I’m going to be testing out the only one I know of for Mac, but […]
I’m spoiled now. I rarely go into the Google Analytics (GA) interface anymore. You know why? There are cool Excel plugins that will pull your data right into Excel. Of course, most of them are PC-swim only. Sorry, Mac users. I’m going to be testing out the only one I know of for Mac, but I can’t verify how good it is.
(Disclaimer: I’m not in any way associated with any of the GA plugins for Excel or their manufacturers.)
Before jumping in, let’s talk about some basics that you will need to understand to navigate the API.
Learning The Language Of The API
The trickiest part about using the GA API is wrapping your brain around all the geek speak. Instead of using the same labels the interface uses (which are intuitive to non-bots), the API switches everything up. But here are some spy-grade deciphering tips that will help you avoid some of the traps that threw me when I was an API fledgling:
- Path: You’ll see path this and path that all through the page dimensions. Just think of path = URI (which is the URL minus the domain, or hostname in analyticsesse). So, the referral source + referral path together make up the page that sent you traffic, e.g., yourfanboy.com/landing-page-that-links-to-you/.
- Visit Bounce Rate vs. Entrance Bounce Rate: This has to be the dumbest thing I’ve seen in the API. I think two developers must have flipped for which metric they’d go with, and the quarter landed on its edge. The two metrics are nearly identical, but after some testing I discovered the Web interface uses Visit Bounce Rate.
- Duplication Between Traffic Sources And Campaigns: Under Dimensions (more on that in a minute), you’ll see a number of different categories, two of which are Traffic Sources and Campaigns. They contain the same options: referral path, campaign, source, medium, keyword, and ad content. I totally over-thought this one the first couple times I used it, but then realized that when you choose from one group, the identical metric in the other will also be selected. Just smile and wave.
- Revenue: In the API, revenue is referred to as Transaction Revenue.
You can access the API using a pretty impressive free tool called Excellent Analytics. It’s an easy install. Just click the obnoxious purple Download button in the upper-right corner of the page and follow the prompts.
Once it’s installed, you’ll see an Excellent Analytics tab. With that you’re ready to dive in and start playing with the API. By the time you finish this post, there’s a good chance you won’t want to use the interface very much, especially if you’re responsible for more than one site.
Step 1: Click the Account button and log in to GA.
Step 2: Click the New Query button to get things going. You’ll see this window open:
Step 3: Set time span. The Time Span tab in the upper-left corner gives you plenty of preset time spans. For monthly reports I just leave it set to Last Month.
Step 4: Choose your dimension(s). The ones I use most are Landing Page (under Page Tracking) and the Time dimensions, like Month, Year, and Date. Dimensions populate the rows in reports, and metrics are the values. A tip I learned from John Marshall of MarketMotive is if it can be measured with a number, it’s a metric. Here’s where they both appear in the UI:
Warning: With v. 5 of Google Analytics released last year, the date format was seriously jacked up. But you can use this formula in Excel to convert them to a standard date format.
Step 5: Choose your metrics. My personal faves are Visits, Visit Bounce Rate, Goal Completions (individual goals and Completions All), and Transaction Revenue (for e-commerce sites).
Step 6 (Optional): Apply filter(s). The most common filter I apply as an SEO is medium [Exact match] organic. Just as in the interface, you can use regular expressions (regex) for the operator. If regex scares the bejeezies out of you, I wrote a post that breaks regex down in the simplest, non-technical terms. Every marketer should at least know how to use the pipe character, dot, plus, asterisk, caret, and backslash.
Other filters I’ve used recently are:
- Country [contains a match for the regular expression]** China because of China’s antic with blocking Google Analytics,
- Operating System [contains a match for the regular expression] ios and Operating System Version [contains a match for the regular expression] ^6\.+ and Browser [contains a match for the regular expression] safari because of Safari shifting to secure search in iOS 6.
- Visits [greater than] 5,000 to only show the top mediums in a client’s monthly report.
** I’ve run into a bug in Excellent Analytics several times now, where exact match won’t catch something it should definitely match. But if I change it to regex match, it works. Hey, it’s free.
Very important: After you set up your filter, remember to hit the Add button. I can’t tell you how many times I’ve forgotten to hit this and have had to redo my filters. The filter will show up in the box below the Add and Remove buttons.
Step 7 (Optional): Apply an advanced segment. You can use any of the built-in segments GA offers or any you’ve created.
Pro Tip: Advanced segments cause data sampling, which is the bane of every marketer and analyst’s existence, only to be eclipsed by not provided. I use filters over segments in the API to keep sampling to a minimum.
Step 8 (Optional): Set the sort. You can use any of the metrics and dimensions you’ve set up to sort your data by. Then just click the Ascending button to toggle back and forth between ascending and descending. Just like you did with the filters, remember to click the Add button to add your sort.
Pro Tip: This is preference on my part, but I always include Visits, even if I’m not emphasizing visits in my charts, because it helps me prioritize the most important mediums, landing pages, PPC keywords, etc.
Step 9: Choose the profile(s) you want to run this report for. This is especially nice for agencies and in-house marketers who are responsible for multiple sites. If you choose more than one profile, Excellent Analytics will drop them into your spreadsheet side-by-side with an empty column in between them.
Pro Tip: If I create reports for multiple clients all at once, I will copy and paste the data sets into different worksheets to keep client data separate.
Step 10: Click the Execute button to run the query (worst name for a button).
Step 11: If you want to go back and update a query later on, just select the the header the plugin creates and click the Update Query button.
There are a number of bugs with the plugin and the API I’ve run into consistently. Hopefully, this head’s up will save you some of the head banging I’ve experienced.
- The Dimension box (for lack of a better term) is super buggy. In a perfect world, you should be able to update any query in a spreadsheet at any time. In reality, I’ve had no problem updating metrics, filters, or sort; but, dimension is the quintessential drama queen. I don’t think it’s ever updated properly, but that could just be the bitterness talking. The telltale sign Excellent Analytics is having a rip-roaring temper tantrum is: if, after updating your dimensions, you go to click the down-facing arrow to the left of the Dimension button and it changes to an up-facing arrow but the box doesn’t collapse. It should, instead, look like the screen capture in Step 2, only with dimensions in the text box. If this happens, your only option is to click Cancel and create a new query.
- If you add a new profile after setting up Excellent Analytics, it won’t show up in your list of profiles. I don’t know if there’s a fix for this. I’ve cheated and used another Google Analytics login to access it.
- Both Dimension and Metrics have up- and down-facing arrows that allow you to reorder your dimensions and metrics. (You can see a screenshot under Step 2.) Sometimes the up arrows don’t work for me. They’ll look like they do, but then they export in their original order in the spreadsheet. Oddly enough, when that happens, I use the down arrow instead, and Excellent Analytics has always given me a pass. Go figure.
- If you have multiple profiles in your account, whichever one is first alphabetically will always be added under Profiles. You will have to remove it, using the Remove button every time, if you’re not running a report for it. And there’s no way to set a more appropriate default account.
- As mentioned in Step 6, sometimes the exact match operator under Filter doesn’t work properly. One time, it didn’t match China for country, and another time it didn’t match safari. I checked the Web interface both times to make sure I wasn’t fat-fingering them, and they were correct. However, when I changed the operator from exact match to regex match, it worked. So there ya go. YMMV.
- The GA API screws up the formatting for percentages like bounce rate, exit rate, and conversion rate. They come in as whole numbers instead of fractions, so when you apply percentage formatting (under the Home tab), 48.12 will show up as 4812%. This isn’t an Excellent Analytics; but, it’s a bug in the GA API. The blame falls squarely on a Google engineer’s shoulders. Excel lets you fix that in its Paste Special dialog. Just enter the number 100 in a cell, copy it, select the affected cells, right-click, choose Paste Special, choose Divide, then click OK. You can see math on the fly in action here.
Having used this plugin for a while, I thought I’d also share some tips that will [hopefully] make your ramp-up time more efficient than mine was.
- The data is exported into Excel in a range with headers that’s perfect for pivot tables and charts. (Excel 2011 for Mac still doesn’t support pivot charts. Too much lame, too little time.) You can also create regular, static charts from the data, too, if you’re on a Mac, but it’s better to create even static charts from a pivot table because you can easily change the headings in pivot tables.
- If you’re using Excellent Analytics for monthly reports, once you hit the first of the new month, you can update each data set by just clicking on the header, choosing Update Query, and clicking Execute. If you’ve built pivot tables and/or charts, you can choose Refresh All (under Data > Connections), and all of your tables and charts should update to match the new month’s data. It’s pretty slick.
- With the exception of dividing all rate values by 100, I don’t touch any of the raw data that’s exported from the API because when you pull in a new month’s data, all of your formatting will get wiped out. I wait to format the data in the pivot tables and/or charts. #voiceofexperience
- When I’m finished with all my charts and tables, before sending a report upstream, I hide the ugly Raw Data tab. You can do this by right-clicking the tab at the bottom of the worksheet and choosing Hide from the contextual menu. I only want the sexy data to show.
- If you are torn between learning to use the API with a plugin or Google Docs, I would highly recommend using a plugin. I learned to use the API in Google Docs, and it was much harder. The reason is that you can’t easily browse dimensions and segments. And, learning to use the nomenclature for applying filters and segments was not intuitive. Because you can’t merely choose options from a drop-down, you have to constantly refer to the developer help files, which were definitely not written for non-developers. Plus, you then have to copy and paste or export into Excel each time, barring a script that will automate the process for you.
If you have any questions about how Excellent Analytics works, you can connect with Ampliofy (the company that generously donates its time to maintain the plugin), hollah at me on Twitter, or leave a comment below.
Opinions expressed in this article are those of the guest author and not necessarily Search Engine Land. Staff authors are listed here.