Monday, June 2, 2008

Integrate Google Analytics with Google Docs using Greasemonkey

UPDATED 07/31/09: Not sure if the problem was upgrading to firefox 3.5 or if the GDocs API folks changed how they handle uploads, but the script started returning 415 errors from GDocs.  I worked with Google and now we have the problem fixed.  Should work for all again.

Any suggestions? Please add them in the comments.

UPDATED 05/04/09: All fixed now. However, I am starting to notice some odd 404 errors coming from the Google Docs API. The file uploads nonetheless, but every once in a while the confirmation message comes back as a 404. Any suggestions? Please add them in the comments.
UPDATED 04/30/09: Google just made another change to their code which broke the script. I will work on fixing it starting next week. When it is fixed, I'll make another post. Thanks for your patience.
UPDATED: I updated the script with many improvements to reliability and functionality.
UPDATED: I updated the script to handle security changes made by Google to Google Analytics
Recently I was inspired to start tinkering with Google Analytics using Greasemonkey after listening to a presentation by Avinash Kaushik.

One problem that we have at work is that our version of Excel for some reason does not like the CSV files that GA exports. Our copies of Excel XP (I know its 6+ years old, but we just moved to it last year) only see the graphing data, but they do not recognize the tabular data further down in the CSV files.

After seeing the Juice Analytics integration for seeing new reports in GA, I realized that I probably could solve my export problem too using Greasemonkey. I decided to integrate GA with Google Docs, so I can do a one-click export to create a Google spreadsheet for analyzing the GA data.

Now, on any report that offers a CSV download, the user sees a new option for Google Docs:







Clicking the link will log the user into the Google Docs service and upload the report. Currently I have the # of records limited to 10000 rows of data, but I may make that a configurable option. After the report is successfully uploaded, the user received a confirmation message with a choice to open up Google Docs in a new tab or just to remain in GA:











Greasemonkey handles your data securely and only limits access to values stored by the user script to the user script itself. There is an additional measure of security I added that fully encrypts user passwords. Finally, the export service can only upload to Google Docs using the exact same account that you are currently using in GA. If you want to share, I suggest you share the spreadsheet after it has been created.

Installation Instructions
  • You need Greasemonkey & Firefox 2+ installed
  • If you have Greasemonkey working you will see a little monkey icon in your status bar:
  • Now, download and install the user script (click OK/Accept on any message boxes. The file used to be called exporttogoogledocs.user.js, but apparently Userscipts.org renamed the file!)
  • After opening choosing to open up Google Docs, the pop-up blocker may stop the new tab from launching...if so, you will need to allow pop-ups from the domain www.google.com because of the way Google handles the redirect.

Happy Analysis!
Posted by at
Labels: , , ,

18 comments:

  1. AnonymousJune 3, 2008 at 2:31 AM

    Hello M-Freeman,

    Thanks for dropping by my blog. Its my pleasure helping you out. Let me know if I am of any more help to you.

    Cheers!

    ReplyDelete
  2. Avinash KaushikJune 6, 2008 at 7:14 AM

    Michael: Great plugin! So many people have asked me about it. :)

    Unfortunately I need to debug it more to make it work for me. It keeps giving me a error (There was a problem logging into Google Docs, Error 403 Forbidden. Please try uploading again or relogin into google analytics). If you have a tip about how to fix this please let me know. I have tried to log out of GA and also log into GA and log into Docs.

    I am sure it is operator error!

    Thanks again!!

    ReplyDelete
  3. UnknownJune 6, 2008 at 7:55 AM

    Thank you for the feedback. The password for Google Docs is set when you log into GA. Most users need to re-enter their password in GA everytime they want to access their account even if they click "Remember me on this computer." The plugin uses your credentials that you enter then to allow for the cross-site login (since each Google Service requires authentication for access via the API). Try signing out of GA and then sign in again by manually entering your password (normally not necessary if you have it saved on your PC). That should work. Please let me know if it still throws the error.

    I plan to enhance the script to re-ask the user for their password if an error is encountered during the authentication with Google Docs.

    ReplyDelete
  4. UnknownJune 6, 2008 at 2:28 PM

    OK. The script is now updated with better password error handling.

    ReplyDelete
  5. fruitmanJune 7, 2008 at 7:47 AM

    this is absolutely awesome.

    btw, i am not a gringo, but i am spanish and work on web analytics.

    you can contact me at pere (at) webanalytics.es if you're interested in sharing some thoughts about analytics in spain

    i also organize monthly meetings in barcelona, the "conversion thursdays" with jordi roura.

    cheers
    pere

    ReplyDelete
  6. UnknownJune 7, 2008 at 2:14 PM

    Thank you Pere for the kind compliment. I will take a look into the Conversion Thursdays events.

    Now I am working on tweaking the referring URL report....I want to include at least PageRank scores for the top 20 referring sites. Have a few thoughts on how to actually go about building it and defining the actual metrics.

    For example, it could be a few different reports
    - Avg page rank of the pages grouped by referring domains
    - PageRank of the top x referring urls
    - PageRank of the home pages for the top x referring domains
    - PageRank of newest referrers.

    These values are important for us to use as a guide to for roughly evaluating the quality of the links we receive.

    I would appreciate your thoughts on the subject and perhaps what would be the highest priority of the above items in your opinion.

    Thanks,
    Michael

    ReplyDelete
  7. UnknownJune 27, 2008 at 7:29 AM

    Hello
    I'm receiving an error mesage:
    Whoops! there was a problem.
    500 internal server error

    what can I do?

    thanks
    Franky

    ReplyDelete
  8. UnknownJune 27, 2008 at 8:32 AM

    #Franky,
    Sounds like it probably is a page timeout error. I receive a 500 error sometimes when I try to export very large reports.

    Does that happen with all exports, or just some? For example, if you do an export for 1 day's worth of data do you still get the error?

    ReplyDelete
  9. UnknownJune 27, 2008 at 8:41 AM

    Hi Martin
    It's my first time. don't have other experience. I will try again later and kip you informed!!
    thanks
    Franky

    ReplyDelete
  10. Erik VoldOctober 16, 2008 at 1:43 PM

    Hey, love the user script!

    I'm making a firefox extension to compile a bunch of GA user scripts together and was wondering if I could get permission from you to add this script to the extension?

    Let me know,
    Thanks,
    Erik Vold

    ReplyDelete
  11. sallymanderJanuary 7, 2009 at 6:08 PM

    Thank you, thank you, thank you. I tweeted about this last night as I was processing reports and this morning one of my followers sent a link to your post.

    One feature request, if I may. Could we get a prompt to name the file before it's uploaded to Google? I usually run about 20 reports at a time and it gets a bit confusing when they all come out named the same.

    ReplyDelete
  12. sallymanderJanuary 7, 2009 at 6:58 PM

    After taking a closer look at the exported reports, I'm not sure this will work for me. The reports I export are keyword reports with a search parameter. It seems that your plug in is pulling the full report without considering the search parameter.

    So, If I go into Traffic Sources>Keywords, then narrow to Paid results, then search for "igloo", the report on the screen only shows keywords containing "igloo", but the report that I open in Google Docs has all keywords.

    I've tried reinstalling the plug in, logging out of Google and restarting FF.

    Is this fixable (I hope)? Crossing my fingers.

    ReplyDelete
  13. UnknownJanuary 8, 2009 at 5:54 AM

    I will look into fixing the link to be more dynamic (adjusting itself for changes of dates, segments, and filters).

    Also, I will try to add a name your report input box. That is a good idea.

    Thanks for the suggestions.

    Cheers,
    Michael

    ReplyDelete
  14. UnknownJanuary 14, 2009 at 4:52 AM

    I updated the script to properly handle all Ajax based updates (filters, dates, segments). When you change a date or segment, however, the export bar area of the page is rewritten via a POST, so I needed to add an additional Export to Google Docs link in the Orange profile bar above.

    I will look into the naming of reports soon.

    I may also use that to add a field asking for report size limit (default 10000 records).

    ReplyDelete
  15. UnknownJanuary 14, 2009 at 7:02 AM

    OK, I went ahead and added the report naming functionality today. It was easy to do. Enjoy!

    ReplyDelete
  16. Sean MurricaneAugust 25, 2011 at 7:49 AM

    This is EXCELLENT. Exports the full data, not just the first 500 lines! You've made my life a LOT easier, pal! Thank you!

    ReplyDelete
  17. SpreadsheetPro.netJuly 24, 2013 at 5:07 PM

    Currently there is a better script, an easy explanation on how to use it is here: http://spreadsheetpro.net/how-to-import-google-analytics-data-into-google-spreadsheets-in-3-simple-steps/

    ReplyDelete
  18. UnknownJuly 24, 2013 at 5:25 PM

    Yes. I stopped maintaining this once Google enabled the export directly into GA directly a while back

    ReplyDelete

Subscribe to: Post Comments (Atom)