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. 

Excellent Analytics

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:

Excellent Analytics

Click for larger image.

 

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:

dimensions and metrics in Google Analytics

Click for larger image.

 

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).

Metrics in Excellent Analytics for Google Analytics

Click for larger image.

 

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.

filters in the Google Analytics API

Click for larger image.

 

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.

update query in Excellent Analytics

Click for larger image.

 

Bugs

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.

freaking Excellent Analytics!

Click for larger image.

 

  • 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.

Tips

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 the article are those of the guest author and not necessarily Search Engine Land.

Related Topics: Channel: Analytics | Search & Analytics

Sponsored


About The Author: is an SEO and analytics consultant. Her areas of expertise are analytics, technical SEO, and everything to do with data — collection, analysis, and beautification. She’s on a mission to rid the world of ugly data, one spreadsheet at a time. If you just can’t get enough data visualization tips, you can check out her blog, Annielytics.com.

Connect with the author via: Email | Twitter | Google+ | LinkedIn



SearchCap:

Get all the top search stories emailed daily!  

Share

Other ways to share:

Read before commenting! We welcome constructive comments and allow any that meet our common sense criteria. This means being respectful and polite to others. It means providing helpful information that contributes to a story or discussion. It means leaving links only that substantially add further to a discussion. Comments using foul language, being disrespectful to others or otherwise violating what we believe are common sense standards of discussion will be deleted. Comments may also be removed if they are posted from anonymous accounts. You can read more about our comments policy here.
  • http://www.annielytics.com/ Annie Cushing

    I use that tool almost daily but have never been able to get the GA part of it to work.

  • Randy Guzman

    Thanks a lot. It was a great a help to me.

  • http://twitter.com/richcaina Richelle Caina

    Wow.. Thanks Annie. This is really helpful. I am trying to follow your tips about bugs. Truly, it helps lessen my head banging moments.

  • importantlyernest

    Thanks Annie. It looks awesome. I’ve downloaded and your test drive will hopefully save me crashing a few times :-)
    If I DO bang my head, I’ll let you know.

  • treepodia

    Nice article with plethora of tips. Thank you! Does anyone else recommend this way of accessing the metrics?

  • Anshul Gautam

    This is an awesome

  • http://www.engag.io/Abdallah Abdallah Al-Hakim

    this looks extremely useful. Unfortunately, I running mac operating system :( I will need to find a way around it. This is the first time in a long while that I wished I had a windows OS!!

  • AM

    thanks, annie

  • http://www.annielytics.com/ Annie Cushing

    Thanks! Data Grabber does the same thing but is more finessed and has visualization built in. But it’s a paid tool – costs $300/year.

  • http://www.annielytics.com/ Annie Cushing

    This is one of the reasons my next laptop will be a PC.

  • http://www.annielytics.com/ Annie Cushing

    Thanks! Glad it’s helpful for you. :)

  • http://www.annielytics.com/ Annie Cushing

    Please do! I think I’ve hit most of the bugs, in both EA and the API. But if you run into any or get stuck on anything, I’ll help you get past it. Or die trying. :)

  • http://www.annielytics.com/ Annie Cushing

    Then my job here is done! Seriously, I wish I would have had this guide when I started!

  • http://www.annielytics.com/ Annie Cushing

    My pleasure. :)

  • http://www.annielytics.com/ Annie Cushing

    You’re welcome.

  • http://twitter.com/ampliofy Ampliofy

    Thanks for the mention! You may want to have a look at the next generation of Excellent Analytics: Excellent Analytics Pro. Now, it costs the equivalent of 50 cents a day so it’s not free. But then it comes with mass refresh. It currently has a 90-day free trial, no strings attached. No need to enter credit card details for the trial.

    BTW, a little secret: we’re Mac users ourselves. We use Excellent Analytics with Parallel Desktops or WMWare in OSX.

    One more tip is that EA returns data exactly how it is provided by the API. And this is a good resource to test if your combination of metrics and dimensions is supported by the API (that can seem like a “bug” but isn’t):
    https://developers.google.com/analytics/devguides/reporting/core/dimsmets

    If you run into issues with EA:
    http://excellentanalytics.com/help/

    Annie, feel free to reach out to us.

    Happy analyzing everyone, and liberate your data! :)

  • http://twitter.com/ampliofy Ampliofy

    We’re also considering releasing a 100 % free edition of EA Pro as well, but then without the mass refresh functions (paying 50 cents a day for that is bargain if you really do want it.) Any thoughts on that?

  • http://www.engag.io/Abdallah Abdallah Al-Hakim

    Wow! I think you are the first Mac to PC convert I have heard about. It is annoying that this program is not available through mac. I am not ready for the jump myself :)

  • http://twitter.com/alzahler Alejandro Zahler

    Thanks Annie. I’ve used thoroughly Excellent Analytics, and when used with a lot of data it started to slow my Excel. I switched I’ve to “vba functions” from Mikael Thuneberg. The best solution so far, though not free, but really worth the money.

  • http://www.annielytics.com/ Annie Cushing

    Oh yeah, I love Data Grabber. We just started playing with it at SEER. I wish they would add the functionality to update the query in the interface though. That’s my only complaint thus far.

  • http://www.annielytics.com/ Annie Cushing

    Let me think about that … YES, PLEASE. :)

  • Vishal

    Thanks Annie.

    I need your help ! ! !

    I am trying to restrict the data at sub-levels

    Here is an example –

    Country Region Cities Visits

    I want to restrict the data for top 10 cities with Visits for each change at Region

    Please advise how to do this using Excellent Analytics Queries in Excel.

    Thanks

  • http://www.annielytics.com/ Annie Cushing

    Hey Vishal,

    I can’t understand what you’re trying to do. But Excellent Analytics lets you decide how many results you want (which is where you can get the top 10). Everything else you’re trying to do can be accomplished w/ filters. In the byline at the end of the article is the option to connect w/ me via email. That might be better.

  • Vishal

    Hey Annie, Thanks for your reply

    I can restrict the total results up to 10,000 (0-10,000 in
    base version)

    However, I need to restrict data for top 10 cities with
    visits falling under different regions.

    For example, there are 10 regions – California, New Jersey, New
    York, Illinois, Pennsylvania, Florida, Texas, Massachusetts, Virginia and
    Michigan

    There can be more than 10 records for each regions but I
    need to restrict the records for the top 10.

    The total rows (records) will come to 100 (if all the
    regions have 10 records) even though the max-result is set to 10,000.

    I have 2 Alternative solutions -

    1. I can take the whole data dump and make a pivot then take
    the top 10.

    2. Restrict the data by filtering visits (visits greater
    than 1 or 10) to cut short the maximum results

    But I want to restrict the data thru query itself as I need
    to pull information across many segments and this is currently making my excel file size very heavy and difficult to manage the large data chunks.

    I came across itemsperpage function but not sure how to use
    it within Excellent Analytics query in Excel

    Please let me know if it’s possible in Excellent Analytics
    using queries in Excel

    Thanks,

    Vishal

  • http://www.annielytics.com/ Annie Cushing

    Hey Vishal,

    For the level of customization you need, you should look at one of the paid options, like Excellent Analytics Pro. They’re very inexpensive and come with technical support. EA wasn’t really intended for the heavy lifting you require.

    Annie

  • Vishal

    Thanks Annie.

 

Get Our News, Everywhere!

Daily Email:

Follow Search Engine Land on Twitter @sengineland Like Search Engine Land on Facebook Follow Search Engine Land on Google+ Get the Search Engine Land Feed Connect with Search Engine Land on LinkedIn Check out our Tumblr! See us on Pinterest

 
 

Click to watch SMX conference video

Join us at one of our SMX or MarTech events:

United States

Europe

Australia & China

Learn more about: SMX | MarTech


Free Daily Search News Recap!

SearchCap is a once-per-day newsletter update - sign up below and get the news delivered to you!

 


 

Search Engine Land Periodic Table of SEO Success Factors

Get Your Copy
Read The Full SEO Guide