Monday, March 15, 2010

Google Analytics Evolution - Time Series GA Data using the API & App Engine

This project started with 3 main ideas:
  • Need to quickly pull visits, bounces, transactions by month for many sites? Check.
  • Want to rapidly chart sites' performance against each other? Check.
  • Need a simple interface to allow for getting data into Excel that any marketing person could use? Check.
I am happy to introduce Google Analytics Evolution. A new app hosted on Google's App Engine (for Java) using the Google Analytics API and Flot., a powerful jQuery plug-in for charting.

GA Evolution in Action
Reason for the app
I often have the task of slicing and dicing data for the many sites I oversee. at logismarket we operate our 16 sites in 15 countries. Using Google Analytics, the process of pulling data for reports across all of these sites, was a time-consuming and tedious task. Custom reports, advanced segments, and the great greasemonkey script, cleanerGAProfileSwitching, made things much easier, but I wanted to be able to work even faster. Additionally, any time that I wanted to see data over a longer period of time, my biggest pet peeve with GA, sampling, would rear its ugly head. Given the size of our sites and depending on the dimension or segment used, we can trigger sampling with only a few months of data. That makes using GA for measuring trends quite unreliable, especially for transactions, since the margin of error is much larger on the transactions than it is for visits (the basis of sampling in GA).

So when my boss asked me for a review of the last 18 months for all of our sites and to further segment the data into 3 different slices, I decided to start coding instead of pulling data right away. A little investment now in coding to get a big payback over time for me and anyone else who has the same kind of needs with GA.

Enjoy.


Examples of Usage
  • Site comparison: Pull data for the same period for 2 or more sites and click a column header to see the results in the chart
  • Segment comparison: Pull data from the same site (or more) and choose various segments for the same period and click to see the difference in the chart without the distortion caused by being forced to include All Visits (as it does in the GA UI). Also less likely to trigger sampling.
  • Quick data pull for multiple sites: Select all (or most) of your site profiles, get the data, copy and paste to Excel. Only minimal manipulation needed to get the data ready for Pivot Table usage :-D

Check out the free app today

Ideas for Future Expansion
While this app currently meets my needs I have identified some areas that I want to expand and improve the app. I am also open to suggestions. Please add a comment to this post or report an issue on the Google Code project page for GA Evoluation.
  • Add export to CSV feature. I am thinking to do this along similar lines as the charting... where you export the data that is already pulled and you select the tables. That way you know what you are going to get (eg, you can check Confidence Interval to make sure sampling was not triggered).
  • Make the metric selection dynamic with a set of default metrics. You can greatly expand the reporting capabilities, but I need to make sure that the reporting stays correct (keep apples to apples in charting of the data).
  • Add some calculated metrics not in API. Bounce Rate, eCommerce Conversion Rate are not available via the API because that info can be calculated after pulling the data. No need to burn free Google cycles when we can just calculate it ourselves. However, it would be more user friendly to have the option to get the data as we want it, not just as it is given to us by Google.
  • Other enhancements to flot charts. These are not fully defined yet, but I am thinking toward making the charts a bit more dynamic... still need some thought here.

20 comments:

  1. Very good article, thank you for the explanation. It will help me in my new job position.

    ReplyDelete
  2. Kudos for such sharing a truly useful example of using the GA API.

    You're doing some pretty cool stuff with the API.

    Ophir Prusak

    ReplyDelete
  3. @Ophir

    Thanks for the compliment.

    Please spread the word and post a link to this blog and to the tool so others can discover it.

    ReplyDelete
  4. Excellent app. Couldn't live without it! Thnx for sharing.

    Benito

    ReplyDelete
  5. Is it possible to select different profiles in different accounts (under the same login, obviously) and create a report from that?

    Cheers

    ReplyDelete
  6. @MyJobGroup

    You cannot combine 2 profiles into 1 data set. The results will still be split by profile. However, you can show the output from different accounts in the results area.

    + Just select the first profile(s)
    + Run the report (Click "Get Data")
    + Select the other account and profile(s) you want
    + Run the report (Click "Get Data")

    All of the data will appear below and the graph will include all result sets that have the checkbox marked (in each result set next to the vertical arrows icon)

    ReplyDelete
  7. Hi, excellent app :) is there a way to save the report templates so that I won't have to recreate the structure each time ?

    ReplyDelete
    Replies
    1. Vincent, there is not today but that is a great idea. I'll look into possibly adding that

      Delete
  8. I would love it if I could hit one button and have you pull up the stats for all my profiles across all my accounts in one go, instead of having to manually select each one. I'd also love to be able to resort the order of my selected metrics, and to sort the results.

    ReplyDelete
  9. I have an additional question: Can I import more than one user into your app? I am a volunteer for several nonprofit organizations and set up small business accounts, which each have different email addresses and Google Analytic accounts. I share the information with each organization so they can use it themselves, but they rarely do because they just are not very internet savvy. Can I combine many users with your app so I can check in on all these accounts I created at one time?

    ReplyDelete
  10. @Joyce,
    Sorry, you cannot login with multiple accounts because that is against the OAuth policies for Google. However, I would suggest the that for each of your separate accounts, add 1 of your email addresses as a reporter (or admin). Then, login to to GAE with that email account and then you'll have access to all of those profiles/accounts from 1 email address.

    ReplyDelete
  11. cantg log in. i do have a current account. you say maybe my google ccount is not associated with any analytics......uh.....
    so then what. could you eleborate as to the path forward to use

    ReplyDelete
  12. The site is working fine when I've tried a few different logins. Make sure you have your google account associated with an analytics profile.

    ReplyDelete
  13. WOW!!! Finally a way to see three dimensions on a Google Analytics report! I can now see Event Category, Action and Label in one place. Excellent work.

    ReplyDelete
  14. Is it possible to save the report in pdf or excel format

    ReplyDelete
    Replies
    1. Not yet. but you have two options:
      1) Use a screenshot tool like Skitch or SnagIt if you want to create a PDF
      2) For Excel, I just copy all of the tables of data and paste them into a spreadsheet.

      Neither are ideal but both work for now. If I can ever get the time, I'd like to add a CSV export option or possibly upload to Google Spreadsheet.

      Delete
  15. is there an api which we can connect to your console to get output??

    ReplyDelete
    Replies
    1. The API is the Google Analytics API. You should just connect to that directly to get the output. There are also plugins that essentially do the same thing for Excel and Google Spreadsheets

      Google Sheets
      https://developers.google.com/analytics/solutions/google-analytics-spreadsheet-add-on

      Excel
      http://www.analyticsedge.com/simply-free/

      Delete
    2. so the problem I am facing is that via the API I am unable to retrive yesterday's pageviews for a specific URL. And in your demo, you are able to retrive it.

      Delete
  16. That's very weird. Did you try either of those tools I recommended in my last comment?

    ReplyDelete