Here is how I used Google Sheets to take backup of over a decade of my Universal Analytics data within 30 minutes.
In my opinion, Universal Analytics data should not be saved in BigQuery unless you have specific/complex tracking requirements. It's costly and too much headache.
For the majority of users, Google Sheets is enough for taking quick backups.
I recommend saving only the data you really need instead of backing up everything in Universal Analytics.
.
.
Follow the steps below to take a backup of your Universal Analytics data in Google Sheets:
Step-1: Decide the dimensions and metrics that are important to you.
Dimensions: Year and Channel group.
Metrics: Users, Sessions, Pageviews, Transactions and Revenue.
.
.
Step-2: Decide the time period for your backup.
I recommend backing up your data from the very first day you started collecting it in Universal Analytics up to December 31, 2022.
If you set up GA4 recently, take a backup of 2023 data. However, most businesses have been using GA4 for at least a year.
.
.
Step-3: Decide how you will retrieve the data.
For example:
Date Range: Jan 1, 2011 to Dec 2022
Metrics: Users, Sessions, Pageviews
Dimensions: Year.
Sort rows: Year
Sort Direction: Ascending
Secondary Sort: Users
Secondary Sort Direction: Ascending
Number of rows to fetch: 12
Step-4: Decide the connector you will use to retrieve Universal Analytics data into Google Sheets. I use Supermetrics for Google Sheets connector.
.
.
Step-5: Create a new query to retrieve the required data.
For more details, check out the article: www.optimizesmart.com/how-to-backup-google-universal-analโฆ