This is an article you'll enjoy reading if you have a medium-sized website with several years of history.
Especially if you've spent some time working on its analytics.
Because, in that case, you probably have a good historical data set in Universal Analytics.
And you'll know that in a few days, specifically on July 1, 2024, Google is going to carry out a mass deletion of data -the biggest in history, no less (heh heh)- and it will delete your website's data along with that of millions of other pages around the world.
Does this warning look familiar?
It is what it is.
Do I have the option of paying to keep my data?
No.
Or at least not directly in a Universal Analytics property accessible from your Google account as before.
But there are alternatives. Free and paid.
And in this article I'm going to tell you about the main ones and the ones I'll use on my own websites and my clients' sites.
In other words, I'll explain the step-by-step process I'm going to follow in my case, along with the Looker Studio report I'll use for the export, so you can use it if you're interested.
Which you probably are.
Ways to save your historical Universal Analytics data
There are several different options, but these are the main ones:
- Dump the data into Google BigQuery.
- Manually export the data from the tool's interface to CSV.
- Manually export the data from the API to CSV.
- Build a custom solution and use the API to save or export the data however you need.
- Use Looker Studio to manually export the data to Excel or CSV.
- Use semi-automated or fully automated systems.
Let's talk about each option.
Option 1: Dump the data into Google BigQuery
This option, using BigQuery, would seem at first glance to be the most logical choice for projects of a certain size, but it has several drawbacks:
The first is technical. If you're not a developer or don't have a strong technical background, you'll have to hire someone for this option. And it may not be worth the cost for the following reason.
Universal Analytics data (UA from now on) is exported in a RAW, unprocessed format. This means you won't see reports in a graphical interface like you're used to; instead, you'll have to format the data.
There's a good handful of hours of work after you make the data dump.
Also, on this route and all the others, bear in mind that the comparison between exported UA data and GA4 data won't match, because data is collected differently in UA and GA4, so you'll have to choose a point in time when you switch from using data from one source to the other, and there will be a significant change.
The main problem here will be Year on Year comparisons, which often won't make sense if we have to compare GA4 data for one period with UA data for another.
And I repeat: this is a general problem, not just a BQ problem.
Anyway, if you want to use BigQuery, here's an excellent article with the step-by-step instructions.
Option 2: Manually export the data from the tool's interface to CSV
Well, it's an option.
I looked into it at the time to see how many predefined or custom reports I would need.
And what data range I could use without sampling, which is something I strongly recommend avoiding at all costs.
After looking at the number of reports to export, the data results and knowing that you can only export 5.000 rows at a time per report, I ruled this system out completely in my case.
Anyway, if your website isn't very large and you only need 3 or 4 reports predefined or custom, plus a “month of year” dimension (which groups year and month into one), I'd consider it, because in less than an hour you can have a backup of your historical data ready.
Option 3: Manually export the data from the API to CSV
The easiest way to use the Universal Analytics API is to access its graphical interface here:
https://ga-dev-tools.google/dimensions-metrics-explorer
Once inside our beloved Query Explorer, choose the UA property, the dimensions, metrics and periods you're interested in:
And download the data:
That's the core of what you need to do. There's more to it (such as checking sampling), but it's another option.
The advantage over the previous one is that you can export 10.000 rows at a time, which is twice as many, so it gives you a bit more room to add dimensions to each export.
The downside is that you need to know the names of the metrics and dimensions in English.
Option 4: Build a custom solution and use the API to save or export the data however you need
I'm not going to go into detail, but if you're a developer, here's all the documentation you need to use the API in your project:
https://developers.google.com/analytics/devguides/reporting/core/v4?hl=es
Option 5: Use semi-automated or fully automated systems
There are quite a few, but I'll tell you about the two I've tried.
Supermetrics
It's a professional tool. It connects to your UA account and extracts the data into Excel or Google Sheets:
https://supermetrics.com/products/google-sheets?idev_id=2461
I used it at MásMóvil for certain reporting tasks and we were quite happy with it. Mainly for three reasons:
- Relatively easy to use.
- Powerful.
- Affordable.
If you're interested in this way of exporting data, here's another tutorial by Himanshu Sharma with the complete step-by-step process.
You should also know that there is a free plugin similar to Supermetrics, with the same purpose: dumping UA data into Google Sheets, but I think Supermetrics' (affordable) price is worth it for its greater ease of use and power.
GA3 Exporter
The simple solution for small projects:
You can export one property and up to 100.000 sessions per year for free.
The process is dead simple. So much so that I'm not even including screenshots. It goes like this:
- Log in with your Google account.
- Choose the UA property and account.
- Choose the free plan.
- Wait for the process to finish (they say about 10 min.). They'll notify you by email when you can download the report.
- Download the data.
The result is an explanatory PDF, without figures. Fine for very simple projects:
If you want the figures, then it's time to pay.
But anyway, with this and a custom UA report with 5 dimensions , you have the export covered for personal projects in less than 15 minutes.
Option 6: Use Looker Studio to manually export the data to Excel or CSV
Right, we've reached the last option, which is the one I'm going to use and the one I want to explain to you.
I'm going to use a Looker Studio report to extract the data, export it to CSV and store it in an Excel file with several tabs.
There are three reasons:
- It's free.
- It's powerful: I already told you before how to export more than 10.000 Google Analytics rows at once. And that's the system I'm going to use now.
- It's relatively simple. Even more so with what I'm about to tell you.
- It's relatively fast: it took me a whole day to build the template and review the dimensions and time frame to avoid sampling. Once the setup was defined, exporting and dumping the data into the final file took me 4 hours. And that's with the very high level of granularity I chose. On other client projects, with less granular data, it has taken me between one and two hours.
Bear in mind there is also a downside. The biggest one is that Looker Studio doesn't tell you whether it's pulling sampled UA data or not for the time period in question. So you have to open a similar report in UA and check it, then adjust either the period you're collecting or the number of simultaneous variables in Looker Studio.
In my case, I made a few assumptions beforehand:
- The period to export will always be annual or six-monthly. It's 10 years of data, so I can't see myself using a shorter period that would force me to do more exports. If I have to remove dimensions, I will.
- In some cases, such as the pages report, even though I wanted daily data, I had to do it by month, because Excel has a limit of 1M rows per sheet and one year already took up 800k rows. Yes, I could have used several Excel sheets, but I preferred to keep one and simplify it by month.
- There's an interesting metric, such as the checkout conversion rate, that I haven't extracted. If I need it, I can always calculate it using the rest of the extracted data.
Right, with my prerequisites explained, I'll now tell you about the process.
#1. Define the scope of the historical data backup
Make sure what you want is relevant. Let me explain: you may want “all possible data”, but before going down this route you should ask yourself three questions:
- Is my UA data good enough? In other words, have I taken care of digital analytics, the tool implementation, campaign tagging, filters, checking the number of transactions…? If the answer is no, I can assure you that having all the data will NOT be of any use to you. Because if you didn't care about it before when it was current data, you'll care even less when it's in the past.
- What data will I actually look at in the future? Because you'll most likely focus on traffic (annual or monthly, at most) and the revenue and conversions it generated. You may break that traffic down by channel or, for multinational projects, by country. And if you're a real geek, by device category. But that's it. Don't export data you KNOW you're never going to look at “just in case”. Especially because of the next point.
- The more reports and granularity I have, the more time I'll spend extracting them. Can you spend a day and a half on it like I did, or is it worth paying someone that amount to get the backup?
The outcome of this definition can lead to completely different scenarios.
Simple backup
And what we need may be as simple as this:
Advanced backup
Or something as complex as the case of Yo pongo el hielo, which has eight different reports, each with varying dimensions, metrics, extraction periods and considerations:
Channels
- Dimensions: Date: by day or month Source / Medium Campaign
- Fuente / Medio
- Campaña
- Device category
- Sesiones
- Páginas vistas
- Transacciones
- Revenue
Countries
- Dimensions: Year Month Country Source / Medium
- Mes
- País
- Fuente / Medio
- Device category
- Sesiones
- Páginas vistas
- Transacciones
- Revenue
Events
- Dimensions: Date: by day or month Event category Event action
- Categoría evento
- Acción evento
- Event label
- Unique events
Searches
- Dimensions: Year Month
- Mes
- Search term
- % de abandonos tras búsqueda
- % de refinamiento
- Tiempo después de búsqueda
- Average pageviews after search
Pages
- Dimensions: Year Month
- Mes
- Page
- Time on page
Landing pages
- Dimensions: Year Month Landing page
- Mes
- Landing
- Source / Medium
- Nuevos usuarios
- Rebote
- Transacciones
- Conversion rate
Ecommerce Products
- Dimensions: Year Month
- Mes
- Products
- Agregados al carrito
- Purchased
Ecommerce Promotions
- Dimensions: Date
- Promotion name
- Clicks
Important: this backup definition is for an ecommerce site, so the only conversions I consider are transactions. If you're going to use it as a guide or want to use the template I used and that you can get here, review this point for your own case:
You can download the template here:
Looker Studio template to export your data
Exporting your historical Universal Analytics data can be a real headache.
Especially because you may not even know where to start.
In this report you have everything you need to export your website's historical data -including ecommerce- using the fastest and easiest free method I know.
Besides the template, every day you'll receive a trick or tip (a good one) in your inbox to improve your business or digital project.
Intermediate backup
Between the basic and advanced options there may be a middle ground that fits your project, but that's something you have to decide, based on the three questions I opened this section with.
Read them again, stop for a moment to consider them and define your needs.
Now I'll continue with the next step in the process I followed at Yo pongo el hielo.
#2. Create or use a report in Looker Studio
Open Looker Studio and you have two options:
- Create a report tailored to your needs.
- Use the template I've made available above, which includes the 8 reports I showed you in the outline.
If you go with the second option, you can edit it however you like -I'll explain how in a moment- but first you need to make a copy of the report from the menu: File > Make a copy.
And select the UA property you want to extract the data from.
#3. Select the data and period
Once you're in your copy, select your UA property and view, as well as the data range you want to use, from here:
You'll have to repeat this on every page of the report.
#4. Edit dimensions and metrics
Optionally, if there's something in the report that doesn't suit you, you can change the dimensions and metrics by first clicking the table to open the Properties panel:
It's just as easy as creating custom reports in UA or explorations in GA4.
#5. Export the data
Right, so far there's nothing you couldn't have done with the Query Explorer I mentioned earlier. But this is where Looker Studio's magic comes in, because it lets us export far more than 10.000 rows.
I don't know the limit, but I can tell you I've exported files with 800.000 rows.
I don't know if you realize how important this is, but just in case, let me spell it out: with Looker Studio I export once and I have that 800k-line file.
If I want to do the same with Query Explorer, I'd have to export 80 files of 10k rows each, open them, then copy and paste the results into the first file to get the same 800k-row CSV.
One click vs 160? And with a wait between every step.
There's no comparison.
Right then, to export, all you have to do is:
#1. Be in the report in edit mode, not view mode (without activating the blue button):
#2. Right-click the table or click these three dots:
#3. Export values. These are the options I recommend unless you know exactly why you need different ones:
The reason is that LibreOffice recognizes the values better.
#6. Open the CSV in LibreOffice
I recommend this tool for opening CSVs because, in general and in Spanish, it recognizes values, decimals and other language-specific characteristics better than Excel:
Yes, in Excel you can tell it how to interpret the file and it will open it correctly too, but in my case it takes less time to open it in LibreOffice, copy the values and paste them into Excel. You can do it however you like.
Right, bear in mind that if, when making the copy of the report, you chose the wrong property, the values may appear in dollars instead of euros and the metrics will be in English.
To avoid that, simply select a property with “Spanish” values from here:
#7. Merge everything into one Excel file
The final step.
Now it's time to save the data in an Excel file, Google Sheets or whatever spreadsheet you prefer.
I did it like this:
In other words, one sheet for each report. Inside each one I have the entire historical data set.
To get the full history, since in my case it was 10 years and the downloads from Looker Studio used annual or six-monthly data, I did this:
- Download the CSV for the period in question.
- Open it in LibreOffice.
- Paste the values into Excel.
- Select the previous period in Looker Studio and export it.
- Open it in LibreOffice.
- Paste the values (without the headers, since we already have them from the previous CSV).
And so on until all 10 years of the 8 reports are filled in.
With this system and our data volume, the total number of downloads was close to 100, just to give you an idea. And the resulting Excel file is 100MB.
#8. Save the file in the cloud
This last step is optional but highly recommended: as soon as you're done, save the resulting file in a couple of cloud services, so that whatever happens, you'll always be able to recover it.
In my view, the data it contains is very, very valuable. And it should be treated accordingly.
A few more notes
Right, before wrapping up the article, I want to mention a few aspects we haven't talked about yet that affect the export.
Multi-domain websites
It's quite possible that if your project is multinational and covers a .es, .fr and .pt, you have separate UA properties for each domain.
So if you don't have a roll-up property configured, you have no choice but to repeat the process for each domain.
Changes to properties or views
It's also possible that, if your project has been around for several years, the “official” UA property or view changed at some point. In other words, the data from the first years is in one and the data from the following years is in another.
In these cases you'll have to set a changeover date and select the appropriate property-and-date combination for each period you export:
When merging, as long as you don't duplicate data by including a shared date interval (which you shouldn't), there will be no problem.
Inappropriate dimension combinations
If you create or edit the report, you may come across something like this:
There is no data.
This happens when there is a combination of dimensions that UA doesn't support. Either ever, or for a specific period.
For example, this will happen if you've added the “Device Category” dimension to the report and are trying to collect data from 2019 or earlier.
In my case, because I needed that dimension, my solution was the following:
- From April 2020 onward, it is populated.
- For earlier periods, I removed it from the Looker Studio report.
- Once the data was exported, in the resulting Excel file I added the value “(not set)” in the column corresponding to “Device Category” for the older dates:
Convert to number in Excel
Despite using the indicated settings, it's very likely that for currency fields, such as revenue, Excel will tell you there is an error “Number stored as text””:
To fix it, you'll need to:
- Select the entire column.
- Click the icon to open the window above.
- Select “Convert to Number”.
Excel will change those values to numbers and you'll then be able to operate on them as such.
Conclusions
We're wrapping up an intense article, as you'd expect after analyzing the different options available for storing your historical Universal data.
I hope it has helped you understand the options available to you and choose the one that suits you best, which could range from something very simple to the approach I followed, which gives you high data granularity and lets you check many of the things you may need later on.
That said, with the template I've shared with you, it'll be easier than doing it from 0.
Whatever you do, choose one, even if it's basic, and save that data. You won't regret doing it, but you will regret the opposite. You have until July 1 to finish it.
As always, if you liked the article, here are more GA4 tips, and if you'd like to receive my weekly article by email every Thursday, subscribe here.
Leave a Reply