Getting data from Google Analytics into Excel
Author: Sean Burton
Updated: 07 Aug 2019
Updated: 07 Aug 2019
I've just returned from MeasureCamp and one of the most common question I heard from people related to getting data out of Google Analytics and into Excel.
There are a few paid plugins out there such as Supermetrics and NextAnalytics that help you do this, as well as the now OpenSource Excellent Analytics which currently only supports the old Oauth method (due to be switched off by Google on 20th April!), and also the a free plugin from Analytics Edge.
You can also write your own VBA code in Excel to directly access the Google Analytics API, but this isn't for the faint hearted!
With this in mind, I thought I'd share a technique that I've used for pulling Google Analytics data into Excel without having to write a single line of code - not everyone can install plugins afterall! Just follow these steps:
- Create a new Google Sheet via Google Docs (https://docs.google.com/spreadsheets/u/0/).
- Select the 'Add-ons' menu & choose 'Get Add-ons'
- Search for 'Google Analytics', and then click on the '+Free' button to install it
- This will then ask you to authorise the 'Google Analytics' add-on and will then install a new 'Google Analytics' menu option within the 'Add-ons' menu - select this and then choose 'Create new report'
- A new panel will appear on the right-hand side, where you can select your Account, Property, Profile, as well as the desired Metrics and Dimensions. For example, if you want a trend of 'Sessions', then choose 'Sessions' as your metric and 'Date' as your dimension. When you're happy with your selection, click on the 'Create Report' button.
- You'll now see a new worksheet called 'Report Configuration', which will allow you to configure a load of other settings, such as Start and End Dates, filters, etc. You can change these at anytime, but for now we'll just use the defaults (data from the last 7 days). Once you're ready, click on the 'Run Reports' button within the Add-ons > Google Analytics' menu. Note that there is also the ability to 'Schedule Reports', which allows you to automate this process!
- You'll now see another new tab based on the 'Report Name' you choose above along with a status message for the report configuration.
- You should now see data from Google Analytics in your Google Sheet!
- We now need to do 2 more things:
- firstly, we need to create a sharable link so Excel can access the sheet - simply click on the 'Share' button and then on the 'Get shareable link' option
- secondly we need to get our Google Sheet to output as a basic HTML table - which is actually really simple when you know now! Sinply copy the URL from the browser address bar, which will be something like "https://docs.google.com/spreadsheets/d/XX..XX/edit#gid=1234567890". The bit between the "/d/" and "/edit" is the unique key for your workbook and the bit after "#gid" is the unique reference for your worksheet. Assuming that you copied the URL from the pge that contained you Google Analytics data, you simply need to replace the 'edit#' text with 'gviz/tq?tqx=out:html&', so you end up with "https://docs.google.com/spreadsheets/d/XX..XX/gviz/tq?tqx=out:html&gid=1234567890"
- Now that you have your data in a Google sheet, we're going to use Excel's native 'Data from web' option, which works for any HTML table you can access via a browser.
- So, take this newly formated URL and use it in Excel. Open Excel, click on the 'Data' tab and select 'From Web'. This will open a basic browser window - paste your newly formated URL into the 'Address:' box and click 'Go'. You should now see your Google Analytics data - without requiring any authentication. Simply click the little yellow arrow on the top-left, which should then turn green, and then click on the 'Import' button on the bottom right.
- Choose where you want your data to be outputted, click 'Ok', and you should then see your data appear in Excel after a few seconds.
- Done! Google Analytics data into Excel, without paying a penny OR writing a line of code
There's a lot of addtional things you can do using the above technique, including the ability to filter, sort, offset, your Google Sheet data, but I'll leave that for another post.
The beauty of the above, is that if we used the 'Schedule Reports' option, then our Google Doc will automatically update with the most recent data from the last 7 days (or whatever range we want). Now that we have data in Excel, all we need to do is update it, which is simply a case of right-clicking on the data in Excel and selecting 'Refresh' - a few seconds later your data are updated!
I've uploaded an end-to-end video that may help:
MP4 format (31.7 MB)
or
MOV format (27.6 MB)
I'd love to hear what you think. Is this helpful? Let me know in the comments...
Comments (9):
verified
Thanks for this article. Google API & Google Query Explorer are great, but many people aren't used to them. They are rather reluctant and prefer to stick to their old Excel. Your article might help to bridge the gap significantly.... Excellent!!!
Article Rating: 9 stars
Analyt reply
Aime - thanks for commenting!
Yes, I use the GA API quite a bit when scripting or building dashboards, but not everyone is technical or wants to use paid or unsupported plugins. If my articles helps anyone then I'm happy!
verified
If you have a Windows PC, why would you NOT use Analytics Edge Simply Free?
+ Embedded in Excel; no need to learn another tool like Google Sheets.+ Use the same terminology as the web interface; no need to learn the API field names.
+ It requires no coding; point and click with a simple wizard interface.
+ Single click refresh for all queries; not one refresh for each query.
+ No download limits; not limited to 10,000 rows per query.
+ Filter and segment builder included; no need to figure out segment id’s or filter syntax.
+ Flexible date ranges; no need to figure out how to get last month or last week’s data.
+ Minimize sampling feature; easily avoid sampling problems.
+ Secure operation; workbooks can be freely distributed without risk.
+ Easy to build templates for multi-client reporting; no manual sync of Sheets and workbooks.
+ Many free reports and expert articles provided, fully supported with a responsive forum.
Why torture yourself?
Article Rating: n/a stars
Analyt reply
Mike - Thanks for commenting.
I don't usually allow vendor comments, but as you're pushing a free tool mentioned in this blog you have a right to reply and so I've allowed it.
To be clear, I never said you MUST use the method and indeed I explicitly mention 'Analytics Simply Free!' as an alternative. The above is simply another way - not everyone can or wants to install plugins.
Analyt reply
Laurent - thanks for commenting.
Glad you liked the blog - as Albert Einstein once said 'Everything should be made as simple as possible but not simpler.'
verified
Doesn't work :( I get these messages - JavaScript isn't enabled in your browser, so this file can't be opened. Enable and reload. This version of Internet Explorer is no longer supported. Please upgrade to a supported browser. Dismiss
Article Rating: n/a stars
Analyt reply
Simon - firstly, thanks for commenting.
Sorry to hear you're having difficulties. I've dropped you an email to see if I can help resolve -first thing to check is that you have amended the URL correctly and that you can see the plain HTML table via a browser.
verified
I've never poked around in Google Docs so I had no idea it could connect into Google Analytics - makes sense when you think about it but I'd never thought about it. A great technique to store in the analytics armoury!
Article Rating: 9 stars
Analyt reply
Andy - thanks for commenting.
Yes, it's actually a relatively new addition to Google Sheets - used to be called Magic Scripts - but is a nice way of pulling any Google Sheet data into Excel. Of course using the full API is still the better longer term solution, but that's not for everyone.
verified
Hey Sean,
Thanks for this great article.
While there is no way to get the 'Data from the Web' on Excel 2011 for Mac, I had to find another way to download all the data from the Google Sheet.
From a Microsoft help page, I found the following vba code which lets you does exactly that. Hope that helps some people working on Mac ;) Best,
Alex
Sub URL_Get_Query()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://yourGoogleSheetURL", _
Destination:=Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub"URL;http://yourGoogleSheetURL", _
Destination:=Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Article Rating: 10 stars
Analyt reply
Alex - thanks for your comment and glad you liked the article:)
Yes, the VBA approach does work nicely on Excel for Mac - one I've also used myself. I'm currently working on a full VBA solution that will allow Excel for Mac & PC to connect directly to Google Analytics - watch this space.
verified
Hi Sean,
Thanks for the article. Very nicely written. I am new to GA and have a few queries. Would be great to get your thoughts on the below:
- I am trying to track a particular event (how many times is the 'next' button clicked within a particular page on my website). WHile I have setup GA on google docs, I have added 'Event' under dimension, but not able to figure out how can I track the specific event 'next'. In google docs, in the cell next to Dimensions it is showing as "ga:eventLabel".
- My requirement is to obtain the pageviews/sessions/event details on a daily basis. Can I automate these GA reports?
Amit
Article Rating: 8 stars
Analyt reply
Amit - thanks for commenting.
For Event tracking you need to ensure that you're capturing the 'Event Category', 'Event Action', and 'Event Label'. Events are treated separately from PageViews as an Event could be any event that happends on a page (including automated events). Are you capturing the Event details for the 'Next' button correctly at the moment? Have a look at the Google Analytics API docs - let me know how you get on.
verified
I really like that kind of GA to Excel import. It works fine when I am just retrieving one spreadsheet via external data import from web or via the VBA approach, but I really have trouble to refresh data with importing three or more spreadsheets at the same time.
Is there any troubleshooting to get multiple web connections in line?
Article Rating: 9 stars
Analyt reply
Michael - thanks for the comment.
I tend to use VBA to loop (in serial) through the various web data sources to refresh them if I'm trying to import more than a few - if you record a macro and then run the web data source connection, you'll be able to see how to write VBA - I've had up to 20 sheets being retrieved in this way.
verified
This step-by-step guide was very handy until the URL import into Excel where I got nothing but JavaScript, and no selection of what data to import in Google Sheets. Please advise.
Article Rating: 9 stars
Analyt reply
Clair - firstly, thanks for commenting.
Sorry to hear you're having difficulties. I've dropped you an email to see if I can help resolve. First thing to check is that you have amended the URL correctly - only remove the 'edit#' part of the URL and replace with 'gviz/tq?tqx=out:html&' - and that you can see the plain HTML table via a browser.
I've uploaded an end-to-end video that may help:
MP4 format (31.7 MB)
or
MOV format (27.6 MB)
Add new comment (subject to moderation):
| Full name: |  |
| Email: | |
| Website: optional |
|
| Article Rating: optional |
|
| Comment: optional |
|
 |
|
| Type the text shown in the image above: | |
|
|
@analytdata
facebook
LinkedIn