Obviously, if you only see a handful of data in the Google Analytics for your personal website, the number of rows Universal Analytics or GA4 lets you view or export is more than enough.
The problem comes when this is what you actually face:
You need to analyze 180 thousand rows. And to export them from the tool, you would have to go from 5 thousand to 5 thousand.
Not to mention when the business has several Analytics accounts / properties and no Roll-Up property that brings the data together.
Multiply the number of times you need to export that report until you have all the data (in this case 180 / 5 = 36 times) by the number of properties (let's say 8).
Are you seriously going to export 288 files?
And you're not going to miss any? Would you bet your life on it?
And then, when you merge them in Excel, are you sure you included them all? How are you going to check?
I don't know about you, but to me it sounds like an incredibly tedious task with a pretty high chance of errors and of leaving some data behind.
But as always, I'm here to give you solutions. Four, specifically:
- The easy but so-so one.
- The acceptable one, though not perfect.
- The best one. Which I'm convinced you are NOT going to use.
- Others that get the job done.
And watch out, because even if you don't think so, there's a very good chance you'll soon be forced to use one of them.
At least if you're considering saving your GA3 data history so you can access it after Google cuts off access in July 2024…
#1. Query Explorer: the Google Analytics API in your browser
When I discovered this tool a little over 10 years ago, I was amazed.
Reports with more than 5 dimensions.
10.000 rows of data.
OMG! This is much better than the Universal Analytics interface, I thought.
And I remember I started using it to find out which of the company’s customers accessed the customer area on the web and in the app. It cut my exports in half.
Wonderful.
The thing is that today this tool has two versions, and they're quite different:
Among the differences, for the use case we're dealing with here—exporting data—you'll find that:
- Universal API:
- Up to 10.000 rows of data.
- Data exportable to a TSV file.
- GA4 API:
- Up to 250.000 rows.
- There is no way to export a file. It's designed to help you build API requests so you can add them to other tools. You can, however, copy the data it returns in JSON format and import it into tools such as Klipfolio Powermetrics.
As you'll see, in 2024 this route is not the best one you can use and you'll only use it for very specific cases, but it's worth knowing about.
#2. Use Looker Studio
This is the method I want to focus on, because I think that, taking into account ease of use, price (free) and the quality of the data obtained, it's the best option for 90% of people.
It's not perfect. But you know what? You don't need it to be.
To use it, the first thing is to create a Looker Studio account and a Google Analytics account, which is where the data will be pulled from.
Once you have both, let's begin.
#1. Connect Analytics and Looker Studio
When you create a new report in Looker Studio, you'll get the option to connect to several sources. Choose Google Analytics:
The first time, a popup will ask you to authorize the connection to the Analytics account.
Do it.
#2. Choose the Google Analytics account and property
Not much to say here: choose the right account / property / view (if applicable) and you're done.
Accept when it warns you that you're about to add data to the report.
#3. Create your report
This is similar to GA4 Exploration reports.
Here you choose:
- The metrics and dimensions.
- The number of visible rows.
- The filters.
- The segments.
- Whether you want charts.
And shape the report:
#4. Add a date range control
From the dropdown I show you in the image:
Once it's added, select the period you want data for and let Looker Studio do its job.
#5. Export the data
When it finishes running the query, you'll see that the data is now displayed in the report.
If you hover over the table, you'll see a three-dot icon. Click it and you'll get the option to export the data:
Choose CSV.
Notice that in this case we're going to export more than 122 thousand rows in one go.
In other words, if we'd done it through the Google Analytics interface, we would have had to export 25 times.
13 with Query Explorer.
1 time with Looker Studio.
It takes a little while, but it's worth it when you open the exported CSV and see all 122.000 lines there:
Ooogh, what a joy, seriously!
Differences from Google Analytics reports
Now, one thing you need to keep in mind:
You may discover that, if you compare the data from Google Analytics reports with what Looker Studio returns, there are differences.
Especially in queries with multiple dimensions and long date ranges where there is sampling.
This is because, although the source is the same, the way the metrics are processed is slightly different:
In this case you can see how, in January 2023, the metrics for Google / Organic from Spain, in Looker Studio are:
- Sessions: 27.773.
- Transactions: 224.
- Revenue: 17.049€.
Whereas in the Universal Analytics graphical interface they are these:
- Sessions: 29.831.
- Transactions: 251.
- Revenue: 19.365€.
Similar, but not the same. Keep that in mind when carrying out your analyses.
Even so, I think this is by far the best way to export this kind of report with hundreds of thousands of rows (I seem to remember the limit is 750.000, not bad at all) in most cases.
However, we still have a couple more, very useful for certain niches of users.
#3. Using BigQuery
If you have a (very) technical profile, you can consider it.
The monetary cost is negligible for most projects. The problem is the time cost.
Both when importing Google Analytics data and, above all, when defining the corresponding queries every time you want to analyze certain metrics.
It's not easy, it's not immediate and, in 99.9% of cases, if it's only to export rows beyond the Google Analytics web interface limit, it's not worth it.
There are tools like DataSlayer that can help you with the import task, but as of today the cost is high:
#4. Other tools
There are tools like GA3 Exporter or the classic of classics, Supermetrics, that will make your life easier when moving your Analytics data wherever you want (BigQuery or Power BI, for example).
They're very useful for automating reporting, that's just how it is, but for today's use case, with Looker Studio available for free, I wouldn't think twice.
Conclusions
You've seen that you have several ways to export large amounts of data from Google Analytics—Universal or GA4—so you can work with it in your preferred tool.
You'll also have noticed how I've “subtly” pushed you toward one of them: Looker Studio.
And unless you're already using BigQuery or Supermetrics, Looker Studio is your lifeline if you want to avoid exporting file after file and having to merge them in another tool before you can really get to work with the data.
It's a tedious task with a high probability of failure.
So now you know: next time, if you see that your report exceeds 30 or 40 thousand rows, take a look at the Looker Studio process because it really is convenient, simple and free.
By the way, if you've made it this far, I'm pretty sure you work with Google Analytics regularly.
So, before I go, I want to tell you that I have a page here with a few tricks that you may not know.
And if you want to receive a new one in your email every week, all you have to do is subscribe below.
Leave a Reply