GA4 is upon us. Rejoice!
I’ll be the first to admit that I basically loathe GA4, but I’m learning to cope with it. What choice do we have, right? With the incomprehensibly annoying countdown clock quickly approaching zero, it’s left many asking, how can I back up my old data?
As an SEO, one of the most critical reports for historic comparisons is the traffic acquisition report. Understanding where your traffic comes from, and how it converts is a critical piece of data you’ll want available in the future. But how do you get this data into a usable format to retrieve at a later date?
I like Excel. Sue me.
Sure, there are sexier ways to back up your data, or you can export it into a similar tool like Matomo, but I’m certain that many people’s comfort zone is Excel. Also, for many small business owners left in the dust by all the bells and whistles of GA4, all they need is the ability to review some basic historic metrics. So, with that in mind, I’m going to show you a simple process to export historical data from Universal Analytics into Excel in a usable format.
Prepare yourself for many downloads and Ctrl+C & Ctrl+Vs
If you’re allergic to monotonous tasks, I hate to break it to you, but you’ll be downloading a mountain of .csv files and doing a lot of copying and pasting here.
First things first, you need to decide just how far back you want to go with your data. For our example here I’ll set our time frame to the beginning of 2016.
Next, you’ll need to decide what data points you want for safe keeping. Once we hit 2024, this will be gone forever, so make smart decisions here. For my money, I think it’s important to keep all of these:
- Conversions/events (collect them all individually if you have more than one)
- Sessions by channel
- Conversions by channel
You might also find it valuable to have some other engagement metrics available as well to help you gauge seasonality, and general site performance over time like:
- Bounce rate
- Average session duration
- Pages per session
If you want to go ham, you can even grab these engagement metrics by individual traffic channels.
How you do it
Once you get things set up correctly, you can really move quickly through your data downloads. For our purposes here, we’ll just download overall data first, but the same process can be used for downloading info for individual channels/sources/mediums.
- The first thing you need to do is move to the report at Acquisition > All Traffic > Channels (or Source/Medium if you want to be more granular).
- The data you need to retrieve in your downloads is controlled by the tabs above the line graph, highlighted below. On the upper left select the metric you want to download, and make sure you have the time unit metric on the upper right set to “Day.”
- Now you can download a .csv to start compiling your data.
- Open your downloaded report and if you scroll down you’ll see the data for your chosen metric for each day of your time period.
- Now, just keep changing the metric dropdown to whichever data points you want, download, copy the metric numbers and paste them into your aggregated spreadsheet – just be sure to keep things aligned so the dates line up.
- Once you’ve got all of your data in place you can start having fun with formulas, adding your own columns to calculate conversion percentages, conversion percentage by channel, etc. etc.
- Turn the spreadsheet into a table and you can easily filter out data by a set of date ranges to quickly calculate monthly totals.