Excel functions for fetching Google Analytics data

Integrate Google Analytics data into Excel workbooks – no plugin installation needed. Now also available: an advanced reporting tool based on these functions.

There are various solutions for fetching data from Google Analytics to Excel, as this article in Google’s Analytics blog describes. My approach is to use custom functions to achieve this, whereas the other solutions are Excel plugins you need to install. I think the benefits of this approach are:

  1. Speed: No need to install a plugin that slows down Excel
  2. Ease of use: The functions are used just the same as any of Excel’s built-in functions (like SUM or COUNT). No knowledge of VBA is required!
  3. Easy sharing: The code fetching the data from Google Analytics is embedded in the Excel workbook, so you can send the workbook to others, and they can refresh the GA data or modify the queries without them having to install anything
  4. Flexibility: The query parameters can be outputs from other functions or macros; execution of queries can be controlled with macros
  5. Compatibility: Works in Windows Excel 2003, 2007 and 2010 (maybe older versions too, haven’t tested). Also available for Google Docs Spreadsheet. For Mac OS X, we provide Supermetrics Data Grabber, which works with Mac Excel 2011 (in addition to Windows Excel versions 2003 and later).
  6. New API features immediately available: Whenever there are new metrics or dimensions added to the Analytics API, you can start fetching them immediately, without needing to wait for a plugin provider to add those to their tool.
  7. Customization: If you know VBA, you can easily modify the functions to fit your specific needs

Getting started

There two options to get started using my solution to import Analytics data to Excel:

  1. To try out an advanced analysis tool created with this technology, take a look at Supermetrics Data Grabber – it automatically creates charts and PowerPoints of your data, and can be used to automate analysis of Google Analytics, AdWords, BingAds and Facebook data
  2. To design reports on your own using our Supermetrics Functions, download the latest version of  this Excel file and start building from there. Just get an authentication token from http://AnalyticsFunctions.com and copy it to the sheet, and it should automatically load some of your data. I’ve added a few examples on how to use the functions to that file – by modifying it you can create all kinds of reporting solutions. Note that you need to have macros enabled in Excel’s security settings for the functions to work (after changing the settings, you may have to restart Excel).

You can find more information on our product and support pages for Supermetrics Functions.

Lots of thanks for everyone who have helped us develop this by reporting bugs and sharing their ideas.

172 Responses to “Excel functions for fetching Google Analytics data”

  1. Mikael Thuneberg

    Hey David,

    For sure it won’t work on Mac Office 2008, which has no support for VBA macros. It might work on Mac Office 2004, which does have VBA support (but might require some modifications). The next version of Mac Office (2010?) should again support macros.

    Of course if you run Windows on your Mac using Boot camp or VMware and have Excel installed there, the functions work just as they do on a PC..

    Reply
  2. Mikael Thuneberg

    Moi Janne,

    I don’t know Adwords that well, but taking a quick look at the API, I’d say it would be possible to build similar functions for that as well..

    Reply
  3. Anonymous

    Any idea if it this script could be easily used in Access? Thanks for the great tool. Fernando

    Reply
  4. Mikael Thuneberg

    I noticed that the getGAdata fucntion had been formatted by Blogger so that it didn’t work – Blogger didn’t correctly escape the percentage signs. I didn’t find a way to fix that so I now put the code for the functions in separate text files. So if you’ve had problems getting them to work try copying them again now. The template Excel has been fine all the time.

    x: Making similar functions for fetching Adsense data would probably be quite straightforward.

    Fernando: I tested the functions in Access and they do work. They also work in Powerpoint and I’d assume in Word too.

    Reply
  5. Ana Kravitz

    Very cool, thanks. I like to combine GA data with CRM data in a single Excel sheet – it’s much faster this way than logging in and exporting as csv.

    Reply
  6. Todd Cook

    This is fantastic! Reading through the code is 10 times easier than Google’s documentation. Thanks for your efforts!

    Reply
  7. Anonymous

    Is there a limit to the number of profiles it retrieves from GA Account ? I don’t see all sites / profiles. Is it possible to retrieve certain custom made segments that we track with setVar?

    Reply
  8. Mikael Thuneberg

    The limit in the getGAprofiles function was the API default of 1000 – I now increased it to 10000, the maximum allowed by the API. The example Excel file (Metrics_from_three_profiles.xls) previously fetched a maximum of 200 profiles, the new version I just uploaded fetches over 1400.

    You can retrieve values for your custom segments by putting “userDefinedValue” as the dimension in getGAdata, or using that as a filter, for example “userDefinedValue=xyz”. The list of all variables you can use with the functions is here: http://code.google.com/intl/fi-FI/apis/analytics/docs/gdata/gdataReferenceDimensionsMetrics.html

    Reply
  9. Sushi

    This is great, I can’t seem to get any data for queries on the dimension pagePath though. pageTitle works fine however…strange

    Reply
  10. Mikael Thuneberg

    Sushi: I think the problem is that you have some really long page paths, which exceed Excel’s limit of 255 character in one cell. I made a new version of the function which only shows the first 255 characters for dimension values, could you try if it works for you?

    Reply
  11. Sushi

    Mikael-you rock! Thank you so much, it works absolutely perfectly. The filter function worked perfectly too..I’m using it to pull back some outgoing links from our content reports. This is such a great tool. Thank you, thank you, thank you!

    Reply
  12. Todd

    This totally rocks. Although I have an ampersand (&) in my password so I have to URL encode the ampersand to a %26. For instance, if my password were drugsexrock&roll I would need to enter it as drugsexrock%26roll.

    Reply
  13. Mikael Thuneberg

    Thanks for pointing this out Todd, I hadn’t realized the password would require URI encoding as well. I uploaded a new version of the authentication function that should handle passwords with special characters correctly.

    Reply
  14. Tim

    Hi Mikael,

    This is awesome! If I can use this properly it is going to make my life a million times easier!

    You’ll have to bear with me, I’m new to custom functions and VB – I’ve downloaded the GA_data_fetch.xls example, but can’t find where from within that sheet I would edit the =getgadata function if I wanted to. It’s not apparent when I open the VB editor (I’m using Excel 2007). Could you help?

    Also, what should I do if I want to sort first by Dimensions, then by Metrics?

    Many thanks in advance for your reply and thanks again for the brilliant resource!

    Tim.

    Reply
  15. Tim

    Ah – ignore the first question, have just found them in Module 1 and Module 2! Apologies.

    However, I’d still like to know how to sort first by Dimensions then by Metrics if possible?

    Many thanks,
    Tim.

    Reply
  16. Tim

    I know it’s probably being greedy, but is there a reason why the query can return a maximum of 10,000 rows? For example, there are times where I want to retrieve a list of our keywords – is it possible to extend it to 50,000, as per the GA limit?

    Thanks,
    Tim.

    Reply
  17. Mikael Thuneberg

    Tim: The current API limit is 10000 rows per one query. I’ve been thinking about making a version of the function that loops through several queries to fetch larger data sets, but haven’t had the time to do that yet. And the same goes for sorting by both dimensions and metrics, it could quite be easily coded into the function.

    Matt: Take a look at my PPT solution, it works the same way in Access – the only difference to Excel is that you have to call the function in a VBA macro instead of writing it on a worksheet.

    Reply
  18. Tim

    Ah I see, thanks for the clarification – it’s not too much of a problem at the moment, 10000 rows is still a lot of data to play with, just thought I’d be greedy!

    Thanks again for this tool, it is proving really useful already! 🙂

    Tim.

    Reply
  19. Ben Pate

    Very nice! Unfortunately, this seems very advanced for me. I have a list of a bunch of keywords that my site ranks for, and I want to see, the revenue per keyword and conversion rate. I use Excel everyday and this is very exciting!

    Reply
  20. Mikael Thuneberg

    Ben: If you start building on the GA data fetch.xls file, you can use my functions just as any other functions in Excel – it’s not that difficult 🙂 For example, to get the results you asked for you should write something like =getgadata(token,profile number,”goal1completions&visits&transactionrevenue”,startdate,enddate,,”keyword”). Select a wide range of cells that can fit the result data, write the function to ethe formula bar and press ctrl+shift+entr. You can then calculate the conversion rate by dividing conversions by visits.

    If this sound too complicated, you can also try the plugin solutions like Excellent analytics, which may be easier to get started with. However, they are also much more limited in what you can do with them

    Reply
  21. daniel+mcc

    Hi Mikeal,
    On equick question, how to increase the number of rows displayed with the results of the query? I tried to play a bit with your GA_data_fetch file but couldn’t increase the number of rows. I copied your formula and pasted it as an array but without any good effect… How to achive it?

    Reply
  22. Mikael Thuneberg

    Hi Daniel,

    Here are the steps how to increase the range covered by the array formula:

    1. Select the upmost left cell of the current array
    2. Starting from that cell, select a range going downwards and to the right. The range you select must match or exceed the array’s current range both horizontally and vertically
    3. With the larger range now selected, click on the formula bar where the array formula should be visible
    4. Press ctrl, shift and enter simultaneously

    After these steps, the array formula should now be filling the larger range you selected.

    Reply
  23. daniel+mcc

    great, perfect! thanks for help

    do you know how to extract data from Events section? And from Site Search? I tried to play a little bit with your file to get Events data but couldn’t do it. Did you achieve it somehow?

    Reply
  24. Mikael Thuneberg

    Yeah, it is possible to fetch event and search data also. For example for events, put “uniqueEvents” as the metric and “eventCategory” as the dimension. For search, you can use for example “searchUniques” as the metric and “searchKeyword” as the dimension.

    You can check which variables you can use together here: http://code.google.com/intl/fi-FI/apis/analytics/docs/gdata/gdataReferenceValidCombos.html (unfortunately the page is a bit confusing..)

    Reply
  25. Shiraz

    Hi Mikael,
    Thanks for this amazing tool – this is very useful! One quick question for you. I am trying to extract some data which requires 5 dimensions. The spreadsheet only lets me display 4. The 5th column (dimension) gets truncated. How can I update this to allow more dimensions and more rows when needed.
    Thanks again!
    Shiraz

    Reply
  26. Shiraz

    Thanks Mikael…I didn’t realize that would also apply to columns. Thanks for the quick response and for this tool. It’s really very useful.

    Reply
  27. Tim

    Hi Mikael,

    Just a quick question, do you know if there is a maximum number of OR filter operations you can use in the filters field?

    Thanks,
    Tim.

    Reply
  28. Anonymous

    Thanks for this great solution, At home it works like a charm. At my work however i have to connect through a proxy. I tried different functions for the objhttp object, like setProxy, but unfortunately I cant get it working. Anybody an idea how to get this running through a proxy?

    Reply
  29. Khiero

    I am running into the same problem and will be glad to hear if somebody has a solution for fetching data through a proxy.

    Reply
  30. Mikael Thuneberg

    To get the functions working with a proxy, there’s a small change you need to do to the VBA code. Before the line objhttp.Open “GET”, URL, False you need to add:

    objhttp.setProxy(proxySetting, varProxyServer, varBypassList)

    And after objhttp.Open “GET”, URL, False you need to add

    objhttp.setProxyCredentials(username, password)

    For instructions on how to set the parameters to those lines, see http://msdn.microsoft.com/en-us/library/ms760236(VS.85).aspx and http://msdn.microsoft.com/en-us/library/ms763680(VS.85).aspx

    Reply
  31. digerati14

    Nice macro. This is very nicely done and works fantastic. Thanks for the brain power.

    Did you know the reply (Oct 22, 2009) that is supposed to show the steps to add more rows and/or columns in no longer on the site? I’d love to peek at that.

    Reply
  32. Jono Alderson

    Hello!

    I wondered if you’re planning to extend the features to support the new API functionality – specifically adding support for advanced segments?

    Reply
  33. Mikael Thuneberg

    Support for advanced segments is now available! The new getGAaccountData function can be used to fetch a list of your advanced segments, and these segments can then be used to restrict the queries done with the getGAdata function.

    I’ve also uploaded a new version of GA data fetch.xls to show how the functions are used.

    Reply
  34. Yo

    what information gets entered into the ‘filters’ field? Can I select a cell, or do I need to type out something? Whats the format? Mine doesn’t do anything.

    Reply
  35. Rob Kingston

    This is brilliant… I haven’t been able to use Excellent Analytics on my computer due to some incompatibilities with the plugin install.

    Do you know where we could potentially find some template reports for this tool?

    Reply
  36. Anonymous

    Hi Mikael, good job, thanks!
    I’m getting the right results, the only issue I encounter is that the filtering doesn’t seem to work, while I’ve tried several notations.
    I’m using the getgadata function in Excel 2007: =getgadata(A1;9999999;”uniquepageviews”;”2009-12-06″;”2010-01-06″;”pagepath%3D%3Dtest”;”pagepath&pagetitle”)
    greetz, Jan

    Reply
  37. Mikael Thuneberg

    There was a small bug in the previous version of the getGAdata function, causing filters to not always work correctly. I’ve uploaded new versions of getGAdata.txt and GA data fetch.xls where this is fixed.

    Reply
  38. Mikael Thuneberg

    Hi Rob,

    Sorry for not answering to you sooner. I don’t have other templates than the ones I’ve already posted that I could share. If anyone has templates that could be shared, please contact me and I can post them on this site.

    Reply
  39. Adrian P.

    Mikael,
    I very much appreciate all the work you have done. One question: for some reason the spreadsheet won’t fetch my list of advanced segments 🙁
    All I get is every #NAME? in every field; anything I’m possibly doing wrong? Thanks!

    Reply
  40. Mikael Thuneberg

    Hi Adrian,

    The #NAME error most likely appears because your Excel security settings prevent the execution of macros. Lower the security level and restart Excel, then the functions should work correctly.

    Reply
  41. Tim

    Hi guys,

    I get an error with the Segments section as well – all other values populate perfectly but the Segments section just has #VALUE!.

    I do have a lot of advanced segments created – perhaps this is a problem?

    Thanks,
    Tim.

    Reply
  42. Mikael Thuneberg

    Hi Tim,

    This is most likely caused by some of your segment names or definitions exceeding 255 characters in length, which is the maximum that can be placed in an Excel cell through VBA. I fixed the function now so that it only shows the first 255 characters in these cases.

    Reply
  43. Tim

    Fantastic – well spotted, it works perfectly now!

    Thanks again for these updates, much appreciated.

    All the best,
    Tim.

    Reply
  44. rolharm

    love your work!! thanks a lot. Using it a lot in excel already. Now trying to convert it to access, but having problem with the getData query.

    Any idea why the following query is not working?
    Expr1: getGAdata([token];123456789;”visits”;1-2-2010;15-2-2010)

    I am calling my token from an other table, which seems not to be the problem. It is recognizing the dates as an actual date, so no problem there neither…. realy breaking my head over this one! especially because your “test this” example is working great in my setup.

    thanks for any help on this!
    Roland

    Reply
  45. Anonymous

    Hi Mikael,

    Great job! Referring to a post made earlier by another reader, i’m trying to make a cerate e for other API calls, specifically the adwords API. I’ve gotten the client auth token, but lost on where to go next with creating the connection and making the api call. do you know of any resources (blogs, books, examples) that would provide some guidance. Thanks and great work again.

    Reply
  46. Anonymous

    Hi Mikael,
    Many thanks for this – this article is really awesome and helped me a lot!

    I saw that calculated metrics (avg. visit length for example) are not available through the api.

    Is there some way that I can use ga:timeOnPage/(ga:pageViews-ga:exits) directly in the metrics field?

    Thanks! Nicole

    Reply
  47. Mikael Thuneberg

    Hi!

    Anon: Unfortunately I don’t know any sources that would be helpful for developing a VBA solution for the Adwords API. I’m considering trying the same thing at some point.

    Nicole: If you don’t split by a dimension in the query, you can fetch all three metrics with their own queries to one cell and do the calculation there also, for example: =getgadata(timeonpage)/(getgadata(pageviews)-getgadata(exits)). If you’re splitting by a dimension then you need to fetch all three metrics to their own columns and then add an extra column where you do the calculation.

    Anton: varsågud!

    Reply
  48. Anonymous

    Hi Mikael,
    I tried to use your functions (both the template file and the embedding manually)but i always get an error message from VBA stating “compiler error: syntax error”.

    There highlighted section is
    “Dim rivej・As Double”
    in the getGAdata function.

    I am using a Japanese excel version and guess there might be a problem with language encoding or something.

    Could this be the problem?

    Thanks!
    Bene

    Reply
  49. Eivind

    Hi Mikael

    First – great work. I have one question about filters.

    > sign (greater than), >= (greater than or equal to), < (less than) etc. they don't seem to work. Other filters like == (equal to), != (does not match), =@ (contains substring) are working. Example: I want to show visits that happened more than 15 days ago (Visitor Recency). The filter for this is according to my understanding:
    daysSinceLastVisit>=15

    That returns a “No data found” message.
    Any suggestions?

    Reply
  50. Carlos

    Hi Michael
    you are doing a awesome job, i have a question,
    i just can get 110 days, if try to do more than that a get an error “you can not change part of the matrix”, could you help me out?

    thanks

    Reply
  51. rolharm

    MSACCESS problem.

    Mikael, just posting this to see if more people have the same problem.

    As mentioned above in my post, I do not get a query working.
    This because I do not have the knowledge in VBA to write the data in a Table.
    I found out that your coding is working fine and is providing me with the data in an array. However, I am not able to write this data from the array into a table.

    So, if there are more people with the same problem, it might be something to consider working on.

    for now, I will just keep on using the excel!

    Roland

    Reply
  52. Greg

    Hi Mikael! First of all, thanks for this amazing application, I love to use it 🙂 I’m working on Windows at my job, but I’m on a Mac at home. I have Office 2004, but still, can’t run the script correctly. Any idea on what I have to do to make it work?

    Reply
  53. Mikael Thuneberg

    Hi!

    Bene: it seems the Japanese version doesn’t understand the letter ä in the variable name rivejä (=Finnish for “rows”). If you replace that variable name with something else everywhere in the code where you can find it, the function will probably work. I can also change the name of that variable when I upload the next version of the function.

    Eivind: the GA API has a limitation that you cannot use greater/smaller than filters with dimensions, but only with metrics. This is true even for dimensions such as dayssincelastvisit that have numeric values. So you’d have to write the filter by combining lots of equal to filters, eg: daysSinceLastVisit==15;daysSinceLastVisit==16;daysSinceLastVisit==17… (or you can do the reverse and say not equal to 0, 1, 2 etc.)

    Carlos: You can expand the array by following the steps I wrote in the comment I posted on October 22, 2009.

    Greg: Mac Office 2004 supports VBA, so with some changes to the code it probably would be possible to get the functions working on it. Unfortunately I don’t have Office 2004 so I can’t test this.

    Reply
  54. Greg

    I think the problem is that Office2004 doesn’t have the msxml 6.0 parser. And I couldn’t find it anywhere (except in a windows dll). But if someone made the changes to the functions, I’m willing to test it 😉

    Reply
  55. Anonymous

    Hi Mikael,
    i exchanged it to rows and sarakkeita to columns and it works great.

    Thank you very much!
    Bene

    Reply
  56. Carlos

    Hi Mikael.

    Thanks for all you have done,

    i have one more question, is it possible to work with more than one profile in the same sheet, because is taking so long to calculate all my sheets.

    Reply
  57. Art

    Any idea how to make this work in iWork for Numbers, Pages and Keynote on the Mac?

    Would it be necessary for one to learn Applescript? How difficult would this be?

    Reply
  58. Mikael Thuneberg

    Hi!

    Carlos: Yes, it’s possible to fetch metrics from several profiles to one sheet. If Excel slows down trying to calculate the functions, turn formula calculation into manual in Excel’s settings. With this setting, the formulas will only be calculated when you press F9.

    Steve: getGAdata does support sorting. There is a sort parameter which you can set to TRUE – with this setting, the results will be sorted by the first metric, in descending order.

    Art: Unfortunately I can’t help you with this – it might be possible to do something similar with Applescript but I’m not familiar with that at all.

    Reply
  59. Arthur

    Hi Mikael,

    I was one of the guys with proxy problems a while ago. I have been able to work it somehow and am now enjoying your VBA code and the API into Excel. Very, very powerful, you save me and my colleagues a lot of time.

    Thanks a million,
    Arthur

    Reply
  60. Mike Glenn

    I just purchased the GA Grabber Tool. If anyone is out there sitting on the fence, don’t hesitate to buy this. If you have a GA account with many profiles this is lifesaver. I’ve been using it all day to work on some projects that I’ve been putting off because they’re just too tedious to do directly from GA.

    Reply
  61. John A

    Hi Mikael,

    I really appreciate what you have done. For me, I can’t seem to get the Advance Segments to work in the Access version of your VBA, although it works in your Excel Fetch. Is there a way for me to cut and paste the newer getGAdata () function from Excel to enable Segments in Access? Also, is there any provision for pulling the confidence interval metadata with your solution?

    Sincerely,
    John

    Reply
  62. John

    Thank you Mikael. Again, I am truly inspired by how you exemplify the best in what the Internet can be in terms of shared community.

    Most sincerely
    John

    Reply
  63. Anonymous

    Hey Mikael–

    Not sure what’s up here, but the authentication token gives me this: r=UnknownUrl=Url=https://www.google.com/accounts/ErrorMsg?Email=xxxxxxx&service=analytics&id=unknown&timeStmp=xxx&secTok=xxxx

    What’s going on?

    Reply
  64. Anonymous

    Hi Michael,

    I really liked ur post & trying ur xls file. But for some reason it’s not working. E.g. when I’m typing my GA username & password, it’s showing #NAME?

    Can u possibly tell me the reason.

    Thank you.
    Arijit

    Reply
  65. Mikael Thuneberg

    Arijit: the most likely cause for this is that your security settings prevent the execution of macros. The functions should work properly if you change the settings and restart Excel.

    Anon: I haven’t seen that kind of error before. Which Excel version are you using? Have you made any changes to the VBA code?

    Reply
  66. Anonymous

    Hi Mikael,

    Thanks a lot for your suggestions! The sheet is now working.

    Regards,
    Arijit

    Reply
  67. Mikael Thuneberg

    I’ve noticed that for some reason, fetching the account data from GA is now much slower than it used to be. This slow response also caused the getGAaccountData function to occasionally fail – I now uploaded a new version where the timeout limit is extended so that it shouldn’t fail, even if getting the data from GA takes a long time. I’ve updated the GA data fetch Excel file with the new function.

    Note that if you only need to fetch a list of profiles, you can still use the old getGAprofiles function, which is much faster (available here http://docs.google.com/View?id=dg6mf6rd_36cvnvnbdm) – that doesn’t support fetching segments though

    Reply
  68. Lukas Oldenburg

    Hello Mikael,

    I have downloaded and it works on my computer. On the computer of my colleague, we always get an “authentication error”. We both are being served through the same proxy server. Any idea why this could be?
    Lukas

    Reply
  69. Anonymous

    Hi Mikael,

    This site is brilliant!!! Thanks for everything it is such as great help.

    I have one question: How do I specifiy a pagepath using your spreadsheet. I can’t get it to work?

    Many thanks

    Richard

    Reply
  70. Lukas Oldenburg

    @Mikael: Yes, that must be it. I just found out that my PC can do more than the one of my colleagues. That must be it. Thanks again for this great, great resource!

    Reply
  71. Anonymous

    this is amazing thank you!

    I cannot seem to get other variables such as Bounce Rate into excel – any ideas?

    Reply
  72. Mike S

    Mikael,

    Great work! This is going to save me hours of copying and pasting. One question though. I can’t seem to get the advanced segment to work. Am I missing something? Does the segment name need to be formatted in a particular way?

    Thanks again!

    Reply
  73. Mikael Thuneberg

    Hi Mike,

    You need to input the ID number of the segment, not the name. There are two ways to get this ID number: you can fetch a list of your segments with the getGAaccountData function, or go to GA, select a segment for a report, and you can see its ID number in your browser’s URL as the parameter “seg0=” (if you’re only looking at this one segment). If you have the dashboard view open in the GA, select the segment from the list and press the Dahsboard link in the menu on the left to refresh the URL.

    Reply
  74. Mike S

    Thanks Miael! I got it working! I just encountered another problem. I keep getting a “no data found” message when I try to enter a dimension

    Thanks again!

    Reply
  75. Mike S

    I was trying to pull TransactionRevenue by Medium. I can get the info from your GA data fetch.xls but if I try to do it in another workbook book I get the “No Data Found” message

    Reply
  76. Anonymous

    Hi Mikael. I find this very interesting and useful thank you! There is on minor issue, entering the GA password that starts with the @ symbol doesn’t work. Is there a work around the error I get is ‘That function is not valid’.

    thanks!

    Reply
  77. Johann

    Hi Mikael, great work and thank you so much, this tool is gold!

    I made a small customisation to the getgadata function adding another optional parameter to choose whether to return 0 or ‘No data found’.

    I think it would be great if a cache could be implemented, maybe with just aggregate queries to start with. This would speed up updating formulae when there is a lot of data to fetch or when fetching data is slow. I’m not sure how to do this, but it’s something I would be keen to work out.

    Reply
  78. Christopher

    Hi Mikael and great product,

    was wondering in the “more complex data query” part of the spreasheet, im trying to extend it pass 100 rows, but it seems that it just repulls the header, any thoughts?

    Reply
  79. Christopher

    Mikael,

    wow, i should have read that first, worked like a charm(and fast too). My GA dashboard is going to have this as the backend. You RULE!

    Reply
  80. Michael

    Hi Mikael,

    How can I get this to work on a Mac? It doesn’t appear that ServerXMLHTTP is an option…

    Thanks,
    Michael

    Reply
  81. Mikael Thuneberg

    Hi Michael,

    No idea yet. I’ve been planning to create a Mac version when the next MS Office for Mac comes out, but haven’t yet given any thought to the details.

    Reply
  82. Michael A. Balazs

    Gotcha, thanks.

    I guess my question is really, is there a way to just do a CSV dump of analytics data? I want the date and four custom vars for each visit to parse offline with my own analyzer. I just don’t know how to get the data out.

    Michael

    Reply
  83. Mikael Thuneberg

    Just uploaded new versions of the functions. The main change is that in case of errors, the functions now return a better description of what went wrong with the query. The code is also a bit more efficient.

    I’ve updated GA data fetch.xls as well as the Google Docs text files with the latest version.

    Reply
  84. Eric

    Hi Mikael,

    This is great and thank you! The only problem is that I am unable to pull up the getAuthenticationtoken function. getGAaccount data function comes up just fine, but there is no option for getauthenticationtoken. Any help would be much appreciated.

    Reply
  85. matt

    Hello Mikael,

    I’ve been waiting to use this with Mac office 2011. It’s out now, and it looks like your doc may need to be updated somehow for OSX? I’ve enabled macros, and I can see that the VBA is there, but it’s simply returns #VALUE errors on the getGAauthenticationToken function. The same doc works perfectly in windows excel. Any advice you have would be great! Thanks again for all of your work here.

    Reply
  86. Adrian P.

    Mikael,
    I have the same problem as Matt; just installed Office 2011 for Mac, transferred the dashboard I’m using on a PC to my Mac and get the #VALUE error on the authentication function. Any help is much appreciated; you have done so much already!!!!

    Reply
  87. mglatz

    Hi Mikael,

    great work there! Last month Ive been fully employed with experimenting with your spreadsheet. I have 35 profiles and plenty of data do manipulate with. Please is there a way how to set startindex (to eg 10001) to fetch next “pages” of analytics data?
    thanks very much!

    Reply
  88. Lukas Oldenburg

    I have been using this now for some months, and it is excellent. The only thing I don’t like about it is the fact that you can’t use Excel while it operates. And in more complex spreadsheets, that can take up to 10 minutes. So sometimes, it happens that I accidentally cut and paste a formula into another cell – bam! – gotta wait 4 minutes before you can continue.

    Isn’t there a way to tell Excel it should interrupt its currently running VBA Script so these problems have an end? (My company is still working with Excel 2003, yes…)

    Reply
  89. Vimmy

    Hi Mikael,

    Thanks for the tool. Here is an issue that I am facing. I am trying to pull Visits & Page Views for 6 months (May, 2010 to Oct, 2010) and putting the dimensions as: Page Path & Network Location & Region.

    The message that comes up is: The query has timed out.

    How can I make this query happen?

    Looking forward to hear from you.

    Thanks,
    Vimmy

    Reply
  90. Lukas Oldenburg

    @Vimmy: I had the same problem initially, and it was because my company’s firewall blocked the request. I told the administrator to allow requests to google.com/analytics and then it worked.

    Your problem might be because of other issues though, maybe it is just too much data at once, that also sometimes causes a timeout. Try grabbing the data for example for 3 times 2 months and then just add up the results. That also makes the results better, because GA doesn’t sample the data that much.

    Reply
  91. kahraman

    Everything works great except for the timeonsite metric. This is displayed as a 5 or 6 digit number that doesn’t correspond to seconds or any other time unit (e.g. program returns the value 749539 when GA displays 00:14:44 ). I’ve tried numerous formatting changes in Excel but nothing works. Any ideas?

    Reply
  92. Mikael Thuneberg

    kahraman: divide the figure by 86400 (the number of seconds in a day) and apply Excel’s time formatting to the cell. This gives you the total time spent on all visits combined. To get the average visit length, divide the result of the previous division by the number of visits.

    Reply
  93. ustin

    It seems that the tool does not understand cyrrilic symbols. For instance, when a filter contains a word in cyrrilic, GA just omits the paramenter and returns the wrong value.

    Reply
  94. ustin

    Sorry, I used the outdated version.
    However I get another mistake. If I don’t change a metrics parameter and query GA several times in a row, the first one completes OK, but every next query I get the metric with additional “ga:”, encoded and appended to it. So I get it like this: “ga:ga%3Aga%253Aga%25253Aga%2525253Aga%252525253Apageviews”. I suppose the url encoding is blame for this.

    Reply
  95. Christian

    Hi Mikael,

    are you still planning on updating the macros for excel for mac 2011?
    I would really love to see that happen!

    Best Regards

    Christian

    Reply
  96. Mikael Thuneberg

    Hi Christian,

    Probably not, at least not as a free solution. It took quite an effort to get GA Data Grabber working on Mac Excel, and to get some return for the time used on that, it doesn’t make sense for me to distribute a free version.. Sorry

    Reply
  97. Anonymous

    Hi Mikael,

    thanks for your reply, I can understand your reasons very well.

    Best Regards

    Christian

    Reply
  98. Carl

    Hi Mikael,

    Firstly, this is a fantastic blog.

    I’ve been playing around making my own spreadsheet to pull data and have noticed that when filtering by medium==organic, I get different data within GA than I do within the spreadsheet.

    This only happens for SOME profiles though. It works perfectly fine for others…

    Any ideas why this might be? The accounts in question aren’t being sampled…

    Reply
  99. Ahmed

    Great Job…You simply rock..its going to save a lot for me and my collegues.

    Thanks Again.

    Reply
  100. Greg Tripp

    Excellent resource.
    However, when I run getGAaccountData(token,”goals”,”True”), Excel returns:

    Fetching account data failed: Subscript out of range

    Any suggestions?

    Reply
  101. Anonymous

    Mikael,

    I’m getting the same error as Greg, even with using True (no quotes) or 1. Any ideas?

    Thanks,
    Tyson

    Reply
  102. Susie

    Hi Mikael
    I have been using your incredibly useful tool. Many, many thanks for it. It has changed my life!
    However it has also slowed my Excel down massively. I have changed the calculation to manual, but when I do hit calculate it takes nearly half hour to update now. Is there any way I can speed this up – I have already added more RAM to my computer and that didn’t make any difference, and I tried running the document on another, more powerful computer, and even that didn’t make any difference. So it must be the speed of the internet connection and retrieval of information. Can you suggest ANYTHING which we could try to try and improve the speed. Except reducing the number of metrics I am fetching perhaps.
    Please help!!
    Susie

    Reply
  103. Mikael Thuneberg

    Hi Susie,

    The speed of your machine has very little impact on the calculation speed of the functions, as most of the time is spent waiting for the requested data to arrive from Google (processing the data in Excel is just around 1 % of the calculation time). One way to speed the calculation is to use one big array formula to fetch lots of data, instead of multiple separate instances of the function.

    What really speeds up the updating is running multiple queries concurrently. Our GA Data Grabber tool does this and can thus get the data much faster than the worksheet functions in GA_data_fetch.xls (for GA Data Grabber, see http://gadatagrabber.automateanalytics.com/). But this is difficult if not impossible to implement in the worksheet functions.

    Reply
  104. Susie

    Hi Mikael
    Many thanks. We are still using Data Fetch – altho we are also using large array formulae. I will look into GA Data Grabber though.
    Many thanks for your speedy response! (Much speedier than Google!!)
    Susie

    Reply
  105. Anonymous

    Hi Mikael,

    Great tool – it is turning out to be extremely useful.

    I also have seen the error message “Fetching account data failed: Subscript out of range” when trying to access the goals in the function getgaaccountdata. It seems to return the values ok for one of my accounts, but not another. Do you know what this error means?

    Thanks,
    Martin

    Reply
  106. Alan Ng

    Mikael

    Just to say a massive thank you for this contribution, your work is much appreciated and a donation coming. Hopefully after using we can come up with some cool uses for this will come back with feedback and donation!

    Reply
  107. Pam

    Thank you so much, I am excited to try this out! Do you know if there is any way to retrieve GA data directly into PowerPivot?

    Reply
  108. Lukas Oldenburg

    I love this tool, but is there a way to escape “:”? When I am trying to get data through a filter that contains a “:”, I always have to build around that since the : is being interpreted as some kind of a separator.

    Reply
  109. Anonymous

    Hi,
    I’m also facing the error “Fetching account data failed: Subscript out of range” upon trying to access goals.
    Did anybody come up with a solution to this?

    Reply
  110. Mikael Thuneberg

    API version 2.3 will be deprecated in 6 months, but as 2.4 is backward compatible, and queries to 2.3 will automatically start to return a 2.4 response, this should not cause any problems for getGAdata (if I interpret the article correctly).

    However, getGAaccountData will require updating, as it relays on the Data Export API Account Feed, which will be deprecated. I’m waiting for someone offering to pay for the work that requires 🙂

    Reply
  111. Bernhardt Scherer

    Hey Mikael,

    I also want to thank you for your great tool, makes work a lot easier for me.
    However, I tried to include multiple metrics in my query (visits and newVisits) but it wouldn’t work.
    Typed in the Metrics cell: visits;newVisits
    In the Data Feed Query Explorer it works.

    Thanks in advance!

    Reply
  112. Anonymous

    Mikael — re: “I’m waiting for someone offering to pay for the work that requires :)” — are referring to updating the free scripts that you provide, or updating the GA Data Grabber, which presumably uses the same functionality? I would assume that those of us paying $299/yr for the Grabber will be supported, correct?

    Reply
  113. Mikael Thuneberg

    Yes, GA Data Grabber will definitely be updated whenever changes in the APIs require that, and everyone with a valid license will get the update for free.

    In this case, the necessary changes were done to GA Data Grabber already a year ago, as I was anticipating the deprecation that is now happening.

    Reply
  114. Dave

    Great work Mikael, this is really cool stuff, I’ve learnt a lot. I was wondering if there was a dynamic way to find out the number of rows returned by a given query? i.e. visits by source for previous week might contain 100 one week, 150 another etc. At the moment I just have a 5000 row array with logic to find the amount of good data before copying it to a storage sheet. It works as-is, but if there was a smarter way of doing it that’d be cool.

    Keep up the good work!
    Dave

    Reply
  115. Mikael Thuneberg

    Note that Google is deprecating the ClientLogin authentication mechanism used by these functions. They still work, but will stop working at some point in the future (http://googledevelopers.blogspot.com/2012/04/changes-to-deprecation-policies-and-api.html). If you are building something for long term use, you should instead use GA Data Grabber, which uses the latest authentication mechanism (OAuth 2.0) and will not be affected by the deprecation (http://www.gadatagrabbertool.com/).

    Reply

Leave a Comment to Adrian P.

  • (will not be published)