3.4K members online now
Understand information in your reports and troubleshoot reporting issues such as self-referrals, (not set) data, and inaccurate information
 
Guide Me
star_border
Reply
Highlighted

Export/Email Reports - Max Number of Rows

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hi

 

I was sure this would be an FAQ somehwre, but can't find anything. If you have a report that has more than 5,000 lines of data, how to you get Analytics to export all of the data to email or export to CSV etc? The export function only seems to send the data for the page that you can see at the time, with the constraint of the number of rows you've selected to view.

 

I have a report with 94,000 rows of data that I need to export to csv for import into our data warehouse, but can't figure out how to do it.

 

Any ideas please?

 

Thanks

Steve

1 Expert replyverified_user

Export/Email Reports - Max Number of Rows

Rising Star
# 2
Rising Star

If you're not using Premium then you can only export max 5000 rows - you could previously hack the URI to get more, but this is no longer available. The API increases this a little to 10000 rows. However, you could paginate a query to the API and then stitch the results together using the start-index function: https://developers.google.com/analytics/devguides/reporting/core/v3/reference#startIndex The google sheets google analytics add-on has a field for this. Still a bit of a pain, but at least you can get at your data. 

Export/Email Reports - Max Number of Rows

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Thanks John. Not sure Premium is an option at $150k p.a.! The rest of that is beyond my understanding unfortunately!

 

 

Re: Export/Email Reports - Max Number of Rows

Rising Star
# 4
Rising Star

 

The google analytics add on is for google sheets, and can be found here:
https://developers.google.com/analytics/solutions/google-analytics-spreadsheet-add-on 

 

It allows you to query the API (i.e. access your data directly from Google) in a simple way, which requires no programming skills. As you will see, the add-on includes a start index field so you can create multiple queries which select all your data rows. 

Screen Shot 2017-06-19 at 13.47.08.png

 

 

 

 

The first query delivers 10000 rows, starting at index 0. The second query, gives rows 10001 - 20001. The third query gives  20002 and and so on. Once you've got your data you'll need to copy and paste into a single master spreadsheet. 

 

 

 

 

 

 

 

Export/Email Reports - Max Number of Rows

Rising Star
# 5
Rising Star

Looks like the image has not uploaded, you can view it here instead: https://pasteboard.co/1ouILIacZ.png

Export/Email Reports - Max Number of Rows

Explorer ✭ ✭ ☆
# 6
Explorer ✭ ✭ ☆

In the same vein as John's suggestion using the GA addon for Google Sheet, essentially, what you need to do is partition your query into multiple API requests.

 

I've been using a tool called Analytics Canvas - it will handle the multiple queries automagically and act as an ETL (Export-Transform-Load) so you can also join multiple sources of data (even non GA data) and output to different formats like Google Sheets, Excel, database, even Tableau Export .tde format.

 

Note: I have no ties to this tool besides being a long time user Smiley Wink

Export/Email Reports - Max Number of Rows

Visitor ✭ ✭ ✭
# 7
Visitor ✭ ✭ ✭

That's great thanks John, I seem to have managed to build the query and the report is running, but now of course it seems to have hit a problem with google sheets itself and a limit of 200,000 cells! Looks like I've have to splt it into two separate query files as well.

 

Export/Email Reports - Max Number of Rows

Explorer ✭ ✭ ☆
# 8
Explorer ✭ ✭ ☆

Give a try to Analytics Canvas, you might be able to send it directly into your datawarehouse without going through an unnecessary intermediary Google Sheet.

Experts in the Topic
John W