Realtime Google Analytics data inside a Google Doc—a panacea!
Don't believe me? Check out that screenshot below. In this blog post I'll show how you can do this yourself, and I've created an easy template to help get you started.
Google Analytics is my favorite analytics product. And it's only been getting better with the new interface, flow visualization, and multi-channel funnels. Google Analytics is still best game in town for the price (it's free)!
But, despite all the flexibility that Google Analytics offers, sometimes you want to access data in a spreadsheet and create a truly custom report. That's where the Google Analytics Data Feed API comes in.
This blog post is going to show you how to create a custom report by connecting a Google Spreadsheet directly with your data from Google Analytics. When data is available directly in a spreadsheet you're able to make interesting comparisons, create the dashboard of your dreams, or chart data however you'd like. And the only requirement is that you have Analytics setup for your website. I've created a simple Google Spreadsheet template that makes the whole thing easy.
Analytics geeks: hold onto your seats!
It all started with the Data Feed Query Explorer
(Those who want to start accessing data in Google Docs should jump right to the next section.)
Before we dive in, a little background. A few weeks ago I was looking for a solution to directly access Google Analytics data in Microsoft Excel or Google Docs using the Google Analytics API.
I first discovered Google's excellent Data Feed Query Explorer. The explorer lets you connect to your Analytics account and pull custom data until your heart's content. This tool is not only an efficient way to figure out what's available via the API, but it's also great for pulling custom data. Want to see which organic keywords drove conversions on your site? Enter the details as below, after authenticating and adding your appropriate profile ID:
The Data Feed Query Explorer is a great way to explore the Google Analytics API, and to understand what data is available. If you're interested in understanding the API, experiment with the tool but also check out the API documentation.
While this tool is helpful, it didn't meet my goal of accessing this data within a live spreadsheet such as Google Spreadsheets. Enter Mikael Thuneberg. Mikeal wrote an excellent set of scripts that pulls data from the Google Analytics API, and allows you to access that data within a Google Spreadsheet. Nice work, Mikeal. He provides this code free of charge (and it's included in my template below), but feel free to reach out to him if you're interested in paying an expert for your custom reporting needs.
I used Mikeal's scripts to create a template that accesses Google Analytics data and allows you to customize it in almost any way. Let's get started!
Connecting Google Analytics to Google Docs
I've created a brief screencast to walk you through connecting your Google Analytics account to the template I've created, but the instructions are also written out below the video. (A small disclaimer: this spreadsheet is provided without warranty or support, so please use at your own risk!)
1) Make sure you have a Google Analytics account with data. Duh.
Make sure you're logged into Google Analytics on the computer you'll be using with my spreadsheet template.
2) Open the spreadsheet template and save a copy.
Open this Google Spreadsheet template, and save a copy to your own Google Account (as you cannot edit this public version). Once the spreadsheet is open, choose "File"... "Make a copy".
Get the Google Spreadsheet template here!
(open this and save a copy to your own Google account)
3) Enter your Google Analytics username.
Give the browser a few moments to make the duplicate copy. Once the copy is created, enter your Google Analytics username (usually an email address).
4) Enter your Google Analytics password.
Enter your Google Analytics password. Once entered, you may hide that row to obfuscate your password.
If the cell below the Profile ID shows an Auth Token (a very long alphanumeric string) you have successfully authenticated. If you have an issue, ensure you are logged into the same Google Account for which you are trying to access. If you still have any issues, such as a CAPCHA warning, wait 30 minutes and try again.
5) Enter your Google Analytics Profile ID.
You'll need to determine the Google Analytics Profile ID of the site you'd like to create a custom report for, and enter it into the Google Spreadsheet.
Log into GA (in a separate browser window) and open the profile for which you'd like to access data. Getting the profile ID isn't easy, and it differs based on which version of GA you use.
Once you're logged into Google Analytics, grab the profile ID from the browser address bar. Here's where you can find it depending which interface of Google Analytics you're using.
Finding your Profile ID in the Old Google Analytics Interface:
If you're using the old Google Analytics interface, your profile is highlighted below in yellow. In the example below it is 2917495 and should be entered into the spreadsheet as characters only.
Finding your Profile ID in the New Google Analytics Interface:
If you're using the new Google Analytics interface, your profile is highlighted below in yellow. In the example below it is 2917495 and should be entered into the spreadsheet as characters only.
Once you have the profile ID, add it to the appropriate field in the spreadsheet template. If everything worked, the cell below the Profile ID should display an Auth Token (a very long alphanumeric string). If you have any issues, ensure you are logged into the same Google Account for which you are trying to access. If you still have issues, such as a CAPTCHA warning, wait 30 minutes and try again.
6) Click the "Custom Report" tab to start accessing your data!
Now you're all set! Click on the "Custom Report" tab at the bottom of the Google Spreadsheet to start interacting with your data. Edit the cells in yellow to change what data is pulled, and for what data ranges. Read on to learn more about choosing which metrics to pull, and how to filter the data.
Customizing the data
When you jump into the "Custom Report" tab of the spreadsheet you'll notice several of the cells are yellow. You can update these cells to change what data is pulled from Google Analytics. For a full walkthrough of the spreadsheet template, be sure to watch the screencast earlier in this blog post.
There are four ways you can change the information that's pulled from Google Analytics into the spreadsheet.
Metric: Change which metric is pulled in that column of the spreadsheet—for example: visits, pageviews or bounces. Change this value and the cells below will update to pull that data. Check out Google's Dimensions & Metrics Reference for details on what data you can access.
Filter: Change how the data below is filtered, i.e. what data is included. Here you can specify a filter that will show only metrics for which the filter is true. For example, setting 'ga:medium==organic' in the filter cell will only show data where the traffic medium is organic search. The filter section is where you have a lot of power—you can even use regular expressions to do advanced filtering. To learn more about setting the filter cell, read Google's Data Feed documentation.
Start Date: Enter a date in the MM/DD/YYYY format to select the start date for cells in that particular row.
End Date: Enter a date in the MM/DD/YYYY format to select the end date for cells in that particular row.
How to make this actionable
So you've connected your Google Analytics account to a Google Spreadsheet. Now what? There's a lot you can do when you access your analytics in this format; I've included a few ideas below:
- Put interesting metrics next to one another. Have you ever wanted to see your total visits next to your organic search visits and goals completions? By choosing the metrics that get displayed in each column you can compare metrics however you like.
- Compare a variety of date ranges easily. Want to compare several days, weeks or months? Change the start and end dates and you can compare multiple periods.
- Create advanced filters. Get creative with your filters. Try creating a filter for organic search traffic (ga:mediun==organic), or for a set of keywords using regular expressions. There are unlimited ways you can slice and dice your data!
- Create calculated cells. Add a column to the spreadsheet and calculate your conversion rate by dividing your goal completions by your visits.
- Create your ultimate dashboard. Probably the most useful way to use this report is to create a dashboard of your favorite key performance indicators. This spreadsheet can automate your weekly or monthly reporting by pulling all of the relevant metrics in one swoop!
These are just a few of the many ways you can use Google Analytics data within a spreadsheet. I'd love to hear your ideas for how to make this actionable—please let me know in the comments.
A few technical notes
- The Google Analytics API is rate limited, so you may occasionally receive errors because your spreadsheet has made too many API calls at once. Unfortunately, there's no easy way around this expect to reduce the number of rows or columns of data you're pulling. Please let me know in the comments if you've found a good workaround for this.
- Your password is in plaintext in the Setting tab of the spreadsheet. Be sure you don't share this Google Doc unless you want someone to have access to your Google Analytics password.
Be a data ninja!
I hope this template is useful and that you're now able to do all sorts of fancy things with your web analytics data. Please let me know how it works in the comments!
Thanks Jamie for mentioning my stuff!
You should also check out my other, more advanced tool called GA Data Grabber: https://gadatagrabber.automateanalytics.com/ - great for automating reporting if you have lots of Analytics/AdWord/adCenter accounts. That one works in Excel.
Thanks for creating such a useful script and making it available for all to use! The analytics community owes you a debt of gratitude. The template in this blog post is flexble, but also doesn't come with a lot of examples. Have you thought about create a similar set of fully complete templates for Google Spreadsheets?
Yeah, I certainly hope to do that at some point. But for now, with two kids and a mortgage, I can't really put much time into the free tools, but rather have to concentrate on the ones that bring some money, like GA Data Grabber..
Oh, I was suggesting you charge for the complete set of templates-- I bet folks would pay for that!
Maybe, but I'm not so sure - I have the one paid Google Docs spreadsheet available but its sales are tiny compared to the Excel tools.. so either there are just so much more Excel than GD users, or GD users simply expect to get everyhing for free :)
I imagine that's some of it, but it's likely more a cause of marketshare (Excel vs Google Docs). I expect over time those looking for a premium set of templates will increase, but can imagine your hesitence in working on something prior to the demand being there. Anyway, thanks agian for creating that script!
Thanks to you for giving it a lot more visibility!
If you ever want to do a blog post on GA Data Grabber, I'd be happy to offer any help you may need.
As someone whos been using GA Datagrabber (analytics and adwords) for a while I can really vouch for the amount of time it saves. The custom dashboards it lets you create are also fantastic.
I've been using the GA Data Grabber for well over a year now and it's cut my reporting time down to minutes instead of hours. Once you create the initial report for your client, the following months are simply a matter of changing the dates - it's a fantastic tool. Well worth the money. Thanks!
Oh man... we just hired a new eployee with reporting and skils from analysing data. (She dubbled our firms overall IQ;) And this post has a perfect timing for us when it comes giving her scills in how to understand and use Google Analytics.
Thanks for sharing. You made my week easier..
Hey Jamie,
Really cool post - I think this will be a legendary solution for some of my customers, but I'm experiencing some problems and I was hoping you could give me some advice.
I've added my user name, password and profile number correctly so that the sheet displays an authentication code. All looks right - but when I enter the custom reporting tab, it gives me erroros in the table. Here is a copy paste of the errors:
Fetching data failed (Request failed for https://www.google.com/analytics/feeds/data?ids=ga:36054920&start-date=2011-05-01&end-date=2011-05-31&max-results=100&start-index=1&metrics=ga:visits returned code 403. Server response: <?xml version="1.0" encoding="UTF-8"?><errors xmlns="https://schemas.google.com/g/2005"><error><domain>GData</domain><code>insufficientPermissions</code><internalReason>User does not have sufficient permissions for this profile.</internalReason></error></errors>)
To me it looks like a permission error, but I am the analytics account holder and original creater.
Do you know what may cause this?
Thanks in advance!
I am having the same issue...getting the same error message (fetching data failed). Can anyone provide an answer as to why this may be happening. The report looks fantastic, and I'd love to test it out.
Were you ever able to get this resolved? I'm hit with the same 403 authentication failure.
I'm encountering the same problem with error code 403 displayed even though the username, pw and profile ID are entered correctly. Looked up and down this comments list and see that others have experienced this problem but I'm not seeing any resolution or workaround. Sure would like to get this working so that I can benefit like the others... looks like a pretty amazing tool.
Good news... I was able to get the spreadsheet to work for me. Here's how...
When entering my Analytics login credentials, Google sent a "suspicious sign in prevented" email to my gmail address. The email asks if you want to reset your password, and below that is a link to troubleshoot problems accessing your account. It's easy to overlook this. When clicking on the link you're directed to a "My client isn't accepting my username and password" page that provides a 5-step process for resolving the problem. Step number 3 directs you here (https://www.google.com/accounts/DisplayUnlockCaptcha) if you're still having problems. This link will take you to your Google account where you can authorize the Analytics spreadsheet to access your account.
This removed the roadblock in my instance... hope it works for others who encounter this problem.
I am still having the same issue as before "Fetching data failed" - and I do not get the "suspicious sign in prevented". Is there a workaround to it?!
Thanks!
Absolutely useful!! Thank you Jamie and Mikael!
Fantastic post! I can only thank you for all the work you put behind this post. Now I'v got some API to crack but what I'v seen so far it looks pretty SWEET. Would you be so kind to provide your @twitter so I can follow you?
Without a doubt, superb Post Jamie! and i an seriously impressed how to present this in the video...
BTW, You can find him on twitter by this @jamies
I have tried it out now and it's pretty awsome data I can pull out like organic growth over time and put into nice graphs.. But I checked out the API and there's a lot I can do there so.. Question @jamie:
Can I add fields like:
to the "Custom report sample" based on your code or do I have to update the code that pulls the results?
My english is'nt flawless since im a swede so I hope you understand my question! :)
You can find a list of all the parameters you can use with my script on my site: https://goo.gl/1sd0G
Great, but how do I put these fields into the spreadsheet? I only have the standard metric and filter.
I added the fields in bold/italic to the spreadsheet in this order:DimensionsMetricFilterSortMax-results
And updated the string (as shown under), I think I got the order wrong here? it wont pull the data for me.=getGAData(Settings!$C$10,Settings!$C$8,D$8,$A14,$B14,D$9,D$7,D$10,D$11)
Any thougths?
Take a look at the example spreadsheet I'm sharing at https://www.automateanalytics.com/2010/04/google-analytics-data-to-google-docs.html, on the right there's an example where the data is split by dimensions
I cracked it! If you want the whole Analytics API range in your spreadsheet this is what you have to do.
Add the following fields marked bold/italic in this order:
They should go in the B9,B10,B11 field in the "Custom Report"spreadsheet.
Now you have to update your "=getGAData" string (the string that pulls the data in your "coustom report sheet"). It should look like this (added fields bold/italic):
(Settings!$C$10,Settings!$C$8,C$7,$A14,$B14,C$8,C$9,C$10,C$11)
Now you can add dimensions, advanced segments and sort to your data. Enjoy!
Hi SuperlativB, I came across your post. Thanx for sharing the "crack" still handy even after 1,5 year ;-). By the way, is the sorting working for you? When I use segmenting I do not get desired order whether I use ga:visits or -ga:visits, i still get only 100 results in random order.
Glad you liked it. My Twitter username is @jamies.
AWESOME Post! I'll be purchasing Mikael's tool later this week.
Thanks ..a lot!! you have made my wish come true to make own customized dashboard with our desired metrics in a place... lots of thumbsup to you!! I am desparate to read your next post regarding GA!
I wish I had more thumbs to up you with.
This is an awesome time-saver!! Thank you Jamie and Mikael!
Anyone know how I can set this up to show total URL's Receiving Entrance Via Search and Total Non-Paid Keywords Sending Search Visits, just like in the SEOmoz traffic data?
Thanks!
This doc is amazing! Thank you for sharing. Has anyone been able to get it to spit out top keywords? I've tried digging around, but can't figure it out.
Google has just released Google Docs add-ons, and we at Supermetrics have two of these (one for Docs and one for Sheets) that link to Google Analytics, AdWords, Bing Ads, Facebook, Twitter and YouTube. These make it very simple to create shareable reports and dashboards, much easier than my custom functions used in this post. You can check out the new add-ons here:
Supermetrics for Google Sheets:https://chrome.google.com/webstore/detail/supermetrics/bnkdidgbiidpnohlnhmkehlimlnfhgce
Supermetrics for Google Docs:https://chrome.google.com/webstore/detail/jmdiehkocgfclpcllfehohafdegbhdcc
Again I'm with CB3, is there any kind of follow up to this? I think that this could potentially be the most useful tool I have come across. However without a little help to expand it's functionality, I'm stuck. Is anyone else using this tool? How have you made it work? Does anyone else fancy sharing their findings?
Fantastic post. I was wondering if there is a way to show the top pages visited or top keywords. I have gone through the documentation, but I don't see anything related to bringing in top links or keywords.
Again... Great post. Thanks.
You are just the best ! I didn't know it is possible to do. I'm working making customisation for my client.
Google Docs is a must have for my work.
Thanks again.
Matt
Thank you! I was in the process of putting very similar data into a Google Doc by hand for the last 6 months for my new employer. This spreedsheet has allowed me to build one that does exactly what I need it to do and it is completely up to date without me running reports constantly.
Help! The number that gets pulled into Google Docs does not match number that shows up in the query explorer (and Google Analytics) for the same query. I have double and triple checked that I am making the correct query.
Any ideas?
It's probably due to Google sampling the results. This often happens when you split by multiple dimensions or use advanced segments. What kind of query is it, and what's the magnitude of the discrepancy? Does Google Analytics match exactly with the query explorer?
Good job, man.
I've tried your template and it works great. I hope smart people like you keep diving into new ways of making SEO and Analytics worlds funny.
Thanks a lot.
Making SEO and Analytics worlds funny?? Making SEO and Analytics worlds brilliant :-)
Absolutely agree!
It's only a matter of words, not intention.
Yeah this is cool, a good way of providing a quick dashboard for clients and a nice alternative to downloading all those csvs.
Great post Jamie, you certainly know your Analytics.
I can see this being of some real practical use. Nice one :-)
Big thanks to Mikael Thuneberg for putting all the hard work into writing the scripts that make all of this possible from Google Spreadsheets and Microsoft Excel. He was extremely generous in making these scripts freely available for everyone to use, including the publisher of this blog post, and it's great to see Mikael's work getting so much publicity and appreciation from the analytics community. I'm especially thankful to him - we use his scripts every day where we work, saving me from doing lots of data entry!
Thanks for a great post Jamie!
Really appreciate that we can get some extremely useful custom data without needing to tip the brain scale at genius level - all because you and Mikael have been generous enough to share yours :)
I know from experience that it's much harder work producing a good screencast than you made it look, so thanks especially for making the effort to do it.
This was the perfect post to put that little bright spot in the week for those of us missing out on SearchLove :)
Sha
A great post! I loved the video as well. It's so easy to get confused when only text is available but using a video to explain made it really easy to follow. Thanks a lot!
Nice post Jamie, some really sweet GA information, VP of Marketing at SEOmoz but also VP of Analytics now =)
Fantastic and exactly what I've been trying to do (the wrong way apparently.) I didn't even think of trying to live link Analytics to Documents, I had been exporting this data and manually adding it to my own spreadsheets. Thanks for cutting out the manual labor!
Excellent! Love this post; I've just integrated Omniture with Excel for the company I'm working for at present and I've been meaning to figure out how to pull Google Analytics data into a Google doc/Excel sheet. Thanks a bunch! Very timely post.
Fantastic post - many thanks for sharing.
Looks like another tedious task bites the dust! Thanks for sharing :D
This is totally awesome! I have like 40 sites I need to monitor so this makes live much easier.
I wonder if anyone has a clean way of pulling the number of weekday hits. right now I have to use a formula that subtracts the weekend hits (which is calculated by applying the filter: ga:dayOfWeek==0,ga:dayOfWeek==6) from the total visits column, and then dividing that by 20 (the number of work days in a month). But thats gross and pretty static. I was hoping I could simply apply a filter like ga:dayOfWeek!=1,ga:dayOfWeek!=6 but that doesnt work, it gives me the total visits.
Is it possible to show the keywords?
Thanks for the help :)
Great post man! I would like to ask a few questions though (I know you probably don't want to answer anymore so I would understand if there was no response lol)
I am trying to use these same basic principles in order to create my own overall client dashboard to represent all of my clients on a single spreadsheet for internal purposes. The purpose of this spreadsheet would basically be to:
Basically I could write out the scripts in the Google Spreadsheet to calculate everything into the % change etc. All I need is to be able to pull in single direct statistics from the Analytics of multiple client's profiles in a single spreadsheet without the clutter.
It would be pretty simple I imagine, all that I would need is to be able to:
I could then use those two pieces of data and use a simple algorithm like =SUM(C1-D1)/D1 to calculate the % change in search over the given time period.
I would probably add a few more difficult queries such as without (not-provided) etc.
I would be using this to get a common understand of where each client is in their progress funnel and to see if there is any major casualty going on in rankings for this month.
Is there any way to pull in this information quickly, simply and easily? And to have it automatically re-access either hourly or daily?
Thanks!
Hi Sean, that's a tough one for me as I haven't gone that far with this worksheet, unfortunately. I imagine it would be possible to create a few tabs with the login info from each of your clients, and then one reporting tab. You'd need to edit the reporting cells to have the cells reflect the correct authentication tab. If all the profiles you need are accessible from the same login, you can just change the formula to reflect the different profile IDs of the different clients.
Regarding the automatic reporting-- that's not a capability of this worksheet unfortunately. I might recommend you try the GA Data Grabber for Excel. I'm not sure if it can pull from multiple profiles, but it might be possible. I suspect the GA Data Grabber for Excel could do the weekly or monthly updates, tho.
Awesome! Thanks for the tip! I'll look into the GA Data Grabber
Jamie - I have downloaded your version and saved as a copy. However, I don't have a tab for Custom Reports - do you think you could help me with that?
Katie
About this:The Google Analytics API is rate limited, so you may occasionally receive errors because your spreadsheet has made too many API calls at once.
Maybe it could be possible to make a delay with random time - 1-10 sec for a formula to work? When the report is ready and is big, when I open the spreadsheet I get half of my cells this an error.
Please tell me somebody, where can I edit a script to make a random delay? Even a 20 sec.delay could be ok when we talk about a ready report
Thank you so much for this- it's truly wonderful. I finally have made the basics work but now I want to complete the columns to pull things like daysSinceLastVisit, timeOnSite, etc. I looked at the dimensions & reference guide, but I just can't follow and keep getting errors.Can you please help? Beyond the basics here I am unable to move forward and I'd really like to use this as the starting point of my dashboard.Thanks so much in advance.
This REALLY is the post i have been look for. Brilliant information and thanks for the video.
I'm not sure how up for reviving this topic anyone is?....but, i'll admit it I'm a bit of a dullard when it comes to data and how google spreadsheets work.
Is there a clear place I can work out what other metrics and filters to use? I tried adding several others but they refused to work.
I'd like to be able to see how many conversions are coming from adwords clicks too, and to add multiple examples of keywords, so i can see at a glance which are converting best.
Any help would be greatly appreciated here, thanks.
same issue with error message (fetching data failed) I want to try it as well. It appears to be out of date.
Thank you for this!! Cheers
Hi,, very amazing, only one problem the file is giving me an error 404
can you reupload, thanks
Hi Jorge, sorry about that! This post is from 2011, and a number of things have changed since this. You can try seeing if it's available in archive.org, but it may not work with the current version of GA.
Template link is broken. 404 Error.
Sorry about that! This post is from 2011, and a number of things have changed since this. You can try seeing if it's available in archive.org, but it may not work with the current version of GA.
Wow That's nice
But Mr. Jamie Steven i have 1 issues about How to compare Check out step-1 and Checkout Step-2 hourly conversion in Google Analytic..? because i have compare the How many people pass the checkout step-1, checkout step-2, checkout step-3, of only hourly, so can you suggest me any ideas, suggestions or how to make custom report about it,?
I hope your reply will be affirmative,,,
Thank you,,
My reading got cut real short when I tried downloading the template..... https://xy.cm/vS2wI3 = D34D L1NK
This script/application should save a lot of time. But it seems suspect for me to follow the xy.cm/[something] links. But I did (hoping our firewall would warn me up in case of security issues). These links led me to a presumably Chinese server with an error message and not to your script.
After a little research on the web I found the application. I think that link shortener is compromised by somebody, is this possible?
Fantastic! I'm definately goin to try this out :-)
Thanks for the great post!
Little late... but many thanks for the google docs analytics sheet!
I do have a question for this one;
How can i render visits of top 5 referral pages and top 5 pages visited? I want to add 2 rows of those in my kpi sheet...
Anybody a suggestion?
Thanks,
Niels
I set all this up as per the tutorial (got the authorisation code, etc) yet when I click on custom report, all cells say "invalid profile number"I have changed date cells and still get this error.can you advise?
Having the same issue..."Fetching data failed". Help please!
I'd really love to use this, but literally every cell of my spreadsheet contains "Fetching data failed (Service invoked too many times in a short time: urlfetch. Try Utilities.sleep(1000) between calls.)" every time I log in. Has anyone found a legitimate fix for this, or is this project a lost cause? Does Google want to blockade us from using the API in Google Drive docs? That seems insane.
You should try the new version: https://www.automateanalytics.com/p/google-analytics-vba-functions.html. The errors should be much less likely to occur when using that.
HI Mikael,
Thanks for the spreadsheet. Incredibly helpful. I've clicked on your links to the automateanalytics.com site and I get a spinning gear. Am I doing something wrong?
This is brilliant. Thanks very much to all the original contributors and in the comments.
Does anyone know if it's possible to get multi-channel funnel data into the spreadsheet too?
Hi all--Love the marriage of Analytics and Docs. Thanks for the quality post, Jamie.
Have been messing with this for a couple days, and was getting the "fetching data failed" message as well. I found a solution via the AutomateAnalytics.com site that I wanted to share:
"If you get an error complaining about "rate limit exceeded", this is due to too many functions being updated simultaneosly (the GA API only allows 10 queries per second). You can avoid this by staggering the queries so that they are not all fired at the same second; to do this, go to Tools: Scripts: Script editor, and in the gatGAdata function, above the line which has the UrlFetchApp, insert these two rows:
var randnumber = Math.random()*5000;
Utilities.sleep(randnumber);"
You should be pasting these on lines 286 and 287 if you have the same setup as I do (and you should), between a "try {" line and the "var response" call.
Should solve your problems!
Hello- can I use similar procress for using Google docs for my shopping data feed? Thanks
Can you do the same for Google AdWords data?!
Just wondering if it is possible to combine things in the filter.
What I want to do is see all organic traffic but exclude the brand terms.
So I want to combine:
ga:medium==organic
&
ga:keyword!=brand
But I can't figure out how to do it.
Any ideas?
I've worked it out:
ga:medium==organic;ga:keyword!=brand
Hi
Just amazing...
I got for some time period data this error:
Fetching data failed (Request failed for https://www.google.com/analytics/feeds/data?ids=ga:38025661&start-date=2011-10-01&end-date=2011-10-31&max-results=100&start-index=1&metrics=ga:pageviews returned code 503. Server response: <errors xmlns='https://schemas.google.com/g/2005'><error><domain>GData</domain><code>rateLimitExceeded</code><internalReason>Insufficient quota to proceed.</internalReason></error></errors>)
any tips abou iy?
Many thks
@d.forlano try with the date format.
This is brilliant, mate - thanks! Getting the same error, though - and a new one today:
Fetching data failed (Service invoked too many times: urlfetch)
Know how to get around these two errors?Cheers
This is caused by restrictions in Google Docs preventing lots of http connections. You can make it less likely for the error to appear by adding this to the getGAdata script:
var randnumber = Math.random()*5000;
Utilities.sleep(randnumber);
You can duplicate the second line several times if you're running lots of queries.
If you already see this error, then you just have to wait until the block is lifted. One thing that may work is to make a copy of the document, I think in this case the new document is not blocked.
This is really good.
But I'm having a lot of trouble with seeing the data. 400 & 500 errors, fetching errors. I have added what was described above and still not really getting anywhere.
Also about the 'block being lifted'. I didn't view my data for over 48 hours and still nothing...
Also tried making a new document but this also didn't work.
Any other suggestions? Could it be that I am trying to view this at work with 10-15 other people on the network and accessing other Google tools (not the analytics to spread sheet url fetching though)?
Yes, sometimes it seems Google just returns these errors at random. Issues like this are the reason I recommend people use Excel for Analytics dashboards rather than Google Docs..
Thanks Mikael. So with excel there are no or less errors returned?
Yeah, it's much more reliable. Also, you have control over when to recalculate the formulas (if you set formula calculation to manual in Excel's settings, the formulas will only recalculate when you press F9. Or you can insert a button that recalculates a sheet). You can find a solution for Excel that's identical to the Google Docs one described in this post at https://www.automateanalytics.com/2009/08/excel-functions-for-fetching-data.html
If you're a Mac user, then the only option for Excel is GA Data Grabber: https://gadatagrabber.automateanalytics.com
Ok! So to try and fix the problem I put in a boatload of Utilities.sleep(randnumber); but it really didn't do anything.
So instead of having 5 months worth of data I reduced it to 3. From there it seems to work. I'm hoping it works properly when I check it out tomorrow.
Thanks for the tip Mike, worked great for me.
What a legend. I don't need to say anymore except Jamie you are a legend.
Brilliant!
I messed around a little with this tonight and started turning the Metric and Filter cells into dropdown toggles, so I could switch between a range of data on the fly.
Here's how you do that:
https://docs.google.com/support/bin/answer.py?hl=en&answer=186103
Then you choose "Create an in-cell dropdown list via a custom list" and enter your Metrics, Filters in a comma-delimited format.
I like this and have started testing the same. I make the column header equal to the value within the cell drop-down so that when I change the metric, the header changes.
Dropdown toggles sound fantastic - will try this too
Thanks for sharing this Jamie! And yeah, I can say with sites that receive >20+ organic search visits a day, the fetching fails most of the time. Initially I had all the data, but minutes later I began to see the error. I eliminated rows and columns but the error remains.
This happened to me once when I was testing the spreadsheet. Trying deleting your copy and creating a new copy via the template, that fixed any weirdness for me.
I reopened that one spreadsheet and the data populated fine now. Guess 'patients' is all that it needs :) Thanks for this awesome post Jamie!
Easy work around! Go to Tools --> Script Editor.
In the Analytics import script, find 'function getGAdata' (it's the last function in the script) - then find the line, 'var response = UrlFetchApp.fetch'
Above that, add:
var randnumber = Math.random()*5000;
Utilities.sleep(randnumber);
Utilities.sleep(randnumber);
And you're done! The doc will now collect the data slowly and won't hit the API rate limits. If you're still having problems, add more 'sleep' lines to increase the time taken.
I had a ton of errors, but your solution worked for me. Thanks!
HI, thank you for your post, same, i had loads of errors, and your code sorted it out, quick sitx, thanks a million
I love seeing great data visualization ideas like this - especially when they're free. More posts like this, please!
Awesome...plans for world domination back on track!
This is brilliant, been playing around with it quite a bit, I've added it to things like keyword rankings etc so its nearly the perfect dashboard - now to bring through the clients revenue!
Just one general Q on Google Docs, I've got multiple metrics like conv rate, visits, convs etc, is there any way to have the daa pull through to one chart and just select the data from radio buttons or a drop down that you want ot see? Can't find anything like this around?
any help would be appricated!
cheers
jamie
Hi- If you dont mind, could you please tell me how you set up the other metrics? for some reason, i can't get mine to work and am about to tear my hair out. Thanks so much,
Celina
Hi and thanks for this. The only problem is that the spreadsheet is not dynmaic, how can you force it to refresh datas ?
Hey guys, phenomenal post and a great help for all of us trying to gain quicker insight into what's going on in our businesses.
Has anyone been able to get Revenue data to report into this google doc?
Thanks!
DP
Thank You! This post made my week. I was able to edit the spreadsheet so that I can use it to track multiple analytics accounts (all available under one login.) Have a client with over 100 sites with unique profiles broken up amongst several accounts and was able to put snapshot data for all 110+ sites on a single tab. Just change the date range and it updates all sites simultaneously. Seriously awesome!
Hi Jamie, great post, lots of help! I have the new version of your gdocs spreadsheet. I cant seem to get the XML to work (column G)...! Any suggestions ?? The URL feed I did a workaround and got it to work (download the file). Thanks!
Hi Jamie:
Thanks! I share google spreadsheets with clients and it´s fantastic to have the data atomatically in the docs. Now i don't recive any mail about this things :-)
I've a question, ¿Exsits the same srcrips for adwords to have the spreadsheet of google docs actualized?
Thanks again!
Superb Post Jamie! That was a big help.
Same problem here!
I just loaded another script for the getauth... function...
This is amazing. Jamie & Mikael - Thanks so much.
I love this tool and am attempting to customize it to build a customized dashboard. That being said, I'm a little dense and still having trouble with some output. Specifically, I would like to output some list data (Visits by country, traffic by sources). I think some of the other posts have addressed this, and I've attempted to follow their guidance and instruction, but still can't seem to get it right. Any help on this would be #awesomestuffedsaugage
I did figure out how to get the list data to appear....@SuperlativB's response from Oct. 31, 2011 (above) worked for me. However, still struggling with getting the maxResult to work properly. I DO want to limit the data that is being returned, just can't figure out how to.
OK. Figured this out too! You need to pass the boolean value for includeHeaders in order for the maxResult parameter to work. You can pass the maxResult as an integer
Hey all, I've been using this tactic in a spreadsheet (=getgadata formulas) on my PC for weeks using Excel 2004, but recently got a Mac with Excel 2011 and the formulas aren't updating. Any idea how to fix?
I think the problem is that getGAauthentication token isn't working for some reason. Is there an issue with this in Excel 2011 for Mac?
Thank you for this great tool!
I seem to be getting caught up somewhere with my credentials, they are correct, but I am hitting an error code, see below.
#NAME?
error: Unknown function name GETGAAUTHENTICATIONTOKEN
Any ideas?
Cheers,
Joe
I had the same prob but got it to work. Seems it is a bug in the spreadsheet reading the name of the function. May I first suggest that you try fiddling with the function name in the cell. So it says in cell C10:
=getGAauthenticationToken(C6,C7)
... so try putting in some spaces (not within the function name though) e.g.
= getGAauthenticationToken ( C6 , C7)
Having said all this, I actually got it to work in a more laborious and dramatic way (which is why I suggest trying the simple way above which might work too). I altered the name of the function in actual script editor (from the spreadsheet, go to the menu Tools>Script editor ... once in the script editor you can see
function getGAauthenticationToken(email, password) {
I changed this to
function getGA(email, password) {
and saved the script. I then selected this function using the Select Function menu item in the script editor; then I clicked the Run icon, a triangle. This authenticated the script. I then went back to the spreadsheet and changed the function in cell C10 to now read
=getGA(C6,C7)
then it all worked beautifully.
It's amazing...you saved my time and you saved my life..man.
Thanks!!!!!!