Posted in:
Client FAQs

How to Export Historical UA Data

Monday, April 29, 2024
April 25, 2024
Tamara Hellgren
Read in
5 min.

Starting July 1, 2024, Google will begin switching off all Universal Analytics services, including UA APIs. If you want to maintain access to your UA historical data, there’s no time to lose. UA data will be deleted and there won’t be any way to access it through the Universal Analytics platform. You can’t migrate historical data from UA to GA4, but there are other ways to preserve all or some of it.

Why should you care about historical UA data?

You may not need to care. If your website was launched shortly before Google sunsetted UA, there might not be much data to preserve. If your website is a personal project and you don’t really analyze Search performance or onsite user behavior, then maybe it won’t make any difference to let it go.

But for many SEOs and business owners, especially those with longstanding domains and years of data in Universal Analytics, preserving some historical insights can be helpful for:

  • Long-term analysis of trends and patterns
  • Providing a baseline for benchmarking/forecasting
  • Regulatory compliance - it's possible businesses subject to the EU's General Data Protection Regulation (GDPR) might need to retain historical analytics data to prove they followed the rules about collecting/storing users' personal data

I don’t personally know about the last point, but it seems worth looking into if GDPR compliance is something you have to deal with.

Ways to preserve historical data from Universal Analytics

Here are some options for saving some or all of your UA data before it disappears forever:

Take screenshots of UA reports

Screenshotting your favorite UA reports/tables would be the fastest, lowest-effort way to preserve some UA data if you think you’ll only need to reference a few basic overviews.

Export UA data to spreadsheets

Exporting your UA data in a spreadsheet will capture much more detail than a screenshot and make it easier to use. If you want to visualize that data you’ll need to connect your Excel spreadsheet or Google Sheet to Looker Studio.

How to export UA data to a spreadsheet:

  • Choose a report. Set the date range and any segments you want to apply
  • Click Export - it’s in the upper right above the date picker
  • Select Google Sheets, Excel or CSV
  • Open up the spreadsheet right away and save it in a designated folder - or wait until you’ve exported a batch of them and then name/save them all.

This method of exporting has a 5000-row limit, so skip to the next section if you have more data than that.

Exporting tips:

  • Hurry up and get started: you’ll need to export multiple spreadsheets if you want to do year-over-year or period-over-period comparisons, since the exported data will be for a specific time period.
  • Save your spreadsheets with a consistent naming convention to make them easy to reference. some text
    • I suggest YYYY-MM-DD [Website Name] [UA Report Name] [Segment(s) Applied]
  • Make at least one backup copy of each export, especially if multiple people will have access to them.
  • Think about data privacy and decide how you’ll keep your historical UA data secure.

Using your spreadsheet as a Looker Studio data source

The easiest way to go about it is to find an existing Looker Studio report using a spreadsheet as a data source. Track down that spreadsheet and use it as a formatting reference (or make a copy and replace the data if you’re making the same type of report).

But if you’re starting from scratch:

  • Make a copy of the exported spreadsheet and save it with a unique name so you’ll know which Looker Studio report it feeds
  • Each column has to have a header - these will be the metrics in your LS charts, so name them wisely
  • Remove any columns you don’t need
  • The first column has to be Date - make sure the cells are formatted as a date field
  • Make sure cells with numbers are formatted as number (not text) - they ought to be by default, but if you run into difficulties this is a good thing to check
  • No merged cells
  • No sum/aggregate rows

This Looker Studio Help tutorial has more tips and step-by-step instructions for connecting a Google Sheet.

You can also use Google Sheets as a data source for other reporting platforms, including:

I haven’t used any of those myself, but I tracked down the most relevant resource page for each, linked above.

Create a Looker Studio Report in Google Sheets

This is a brand-new option that just started rolling out Friday, April 26, 2024: while in Google Sheets you can use the Looker Studio extension to create a new report. This looks like it might be easier than starting in Looker Studio and then selecting a Google Sheet as a data source. At the time of this update it hasn’t rolled out to our workspace yet, so I haven’t been able to try it.

Check out Google’s documentation on creating a Looker Studio report starting in Google Sheets, then see if you have access to it!

Export UA data using the Google Analytics add-on for Sheets 

This is the method Google recommends using, and it can simplify the process if you’re proficient in Google Sheets.

This Google Analytics Help page has instructions on how to do it and a report template you can copy. You can also query the exported data from BigQuery or use it in Looker Studio.

Export UA data using the Google Analytics Reporting API

Using the Google Analytics reporting API is another way to manually export UA data. You’ll need a developer, but it can really speed up the process. When you export using the reporting API, your data is exported to Cloud storage and you can connect it to Looker Studio from there.

Once your historical data is in the API a programmer can also build dashboards to visualize it or integrate it with other apps.

Export UA data to BigQuery

BigQuery is a cloud-based data platform for processing and analyzing large datasets. It’s owned by Google and has a pay-as-you-go pricing model. Gemini is now integrated with BigQuery, so it’s got AI-powered features too.

I have zero experience using BigQuery, but I know you can export your historical UA data into it and there are 3 ways to do that:

It’s really important to understand that:

  • If you choose option 3 and move all your historical UA data over to BigQuery it will be raw and unprocessed: think huge table with tons of rows, not broken down into specific reports like the kind you see in Universal Analytics.
  • It will cost you money just to store your UA data in BigQuery - and then cost you again when you want to analyze it: BigQuery storage pricing / BigQuery computing pricing

If you’re like me and don’t use BigQuery, it’s probably not the best path for saving your UA data. But if you really want to, this Optimize Smart article explains how to do it.

Import UA data into another analytics platform outside of Google

This option is similar to connecting UA data in Google Sheets to a reporting platform, but more direct.

Plausible, Fathom Analytics, Databox and others let you import UA data straight from Google Analytics, saving a step.

Again, I have no direct experience with any of these, but I’ve linked to the most helpful resource page for each one.

What to do if you still don’t know what to do

Being tasked with exporting Universal Analytics data before the expiration date can feel overwhelming, but it’s better to save something than stay paralyzed by not knowing the exact right approach.

In my opinion you can’t go wrong with exporting your UA data to Google Sheets. It’s free and easy to do on your own so there’s no excuse not to get started.

To help you decide what to export, here are some questions to ask yourself:

  • How often have you used your UA data since setting up GA4?
  • What do you care about on your website - specific pages, goals, traffic trends by channel?
  • What metrics do you use for reporting?
  • Are you aware that UA & GA4 metrics aren’t apples-to-apples?
  • What time periods do you report on (monthly, quarterly, etc)?
  • How far do you typically look back when reporting? Year-over-year? Two years back or more?
  • What would be the consequences if you let all but one year of UA data vanish?

If you’re still not sure where to start, use the suggested report exports below to at least save something. Hopefully once you get started you’ll realize what the most valuable data is for your domain. It’s easy to get stuck in the decision-making phase, and taking action can often help clarify things.

For each of the below UA reports, export the 12 most recent full months of data, one at a time: 

  • Audience > Mobile > Overview
  • Audience > Geo > Location (decide which geo primary dimension you value, add a secondary dimension if desired & select a conversion from the dropdown)
  • Acquisition > All Traffic > Channels
  • Acquisition > All Traffic > Source/Medium
  • Behavior > Site Content > All Pages
  • Behavior > Site Content > Landing Pages (select a conversion from the dropdown)
  • Behavior > Events > Overview
  • Behavior > Events > Pages*

*Or use Conversions > Goals > Overview and Conversions > Goals > Goal URLs, depending on whether you value events or goals more and how your UA is configured. Or export both!

Things to consider:

  • For Behavior reports, apply a segment for Organic Traffic (or another channel) or set Default Channel Grouping as the secondary dimension.
  • For any report, apply a segment for mobile users (or other device).
  • Keep an eye out for Annotations that could affect data - for example major website changes, known website performance issues, goal/event tracking changes, campaign start/stop dates, etc.

If you also need to export UA Annotations, here’s a great video on that.

<div class="post-note-cute">I hope this rundown was helpful, but if you feel it’s missing something crucial or you still have questions, don’t hesitate to reach out to <a href="mailto:info@momenticmarketingcom"></a></div>

The image in this blog was generated by Gemini. All the text was written by me (classified as human).

Bar chart showing increase over time with Momentic logo