How to create a Universal Analytics Backup using the Analytics Canvas UA Backup Utility
This article goes through the steps of creating a backup of your Universal Analytics data using the Analytics Canvas UA Backup tool. The tool contains a library of common tables as well as a query builder for you to create your own custom queries to complete your backup.
This article relates to the Universal Analytics Backup Utility offered by Analytics Canvas. To backup your UA data using the main Canvas platform, follow this article.
Before you begin...
There is no "one click" backup offered by Google for your Universal Analytics data, nor is it possible to backup "everything" or "all" of your UA data.
Using the Universal Analytics Reporting API V4, you can request data in sets of 9 dimensions and 10 metrics. The collection of both custom tables you define and the tables included by the backup tool will define your Universal Analytics backup.
🛑 There is no "one-click" backup of UA data 🛑
We suggest starting with your existing dashboards and reports and surveying them to determine which dimensions and metrics are included in each chart, table, and scorecard. Be sure to account for filter controls as those values will need to be in the query as well.
Your website will have unique settings for custom dimensions, custom metrics, goals, and events. Be sure to capture these in your queries and to customize our table library to include these fields as required in your tables.
Most often, dashboards and reports require more than 1 query. Expect to have queries at different levels of scope (User, Session, Hit), or more broadly, expect to have queries that cover the different categories in your report. Categories include eCommerce, Ads, Behavior, Events, traffic attribution reports, etc.
Our table library will help to supplement your custom queries. Modify the library to suit your needs, including adding custom fields, selecting appropriate time granularities, and selecting the correct level of detail.
These customizations together with your custom queries provide a usable backup of your website.
Some rules based on data destination
The amount of data contained in your backup is related to the number of sessions and the level of detail you wish to extract. Very large sites looking for low levels of detail can have a backup with just a few million rows. Whereas a small site with lots of history and custom fields for users and sessions can have a site well over 10M rows.
The following rules determine when each data source will be available as an option for your backup:
| Destination Data Source | Limits |
|---|---|
| CSV Available | Rows < 50 Million AND no single table > 20M |
| BigQuery Available | Rows < 50 Million AND no single table > 20M |
| BigQuery Selected (cannot be unselected) | Rows > 50 Million OR Single Table > 20M |
| Excel Available | Total rows < 20M. Rows > 1M exported as CSV |
| Sheets Available | Total rows < 20 M. Worksheets > 10M cells exported as CSV |
- When Excel is selected, tables that exceed 1M rows will be included as a CSV file and not in the main backup workbook.
- When Sheets is selected, tables that exceed 10M cells will be included as CSV files.
- If any given table will exceed 20M rows, BigQuery will be the only export option available.
To see what the data looks like in each destination, refer to this article in our knowledge-base.
Step-by-step guide to creating your backup
To define your backup, start by creating a new subscription, or logging in to your existing Analytics Canvas subscription.
Define your data location and name your backup
In Analytics Canvas, you will now find a section called "UA Backup" in the left navigation menu. Click on that menu item to get started.
You will see a list of your existing data locations. As Analytics Canvas will first assemble your backup in BigQuery before exporting it to your preferred destination, you will need to define a data location for the backup.
Click the (+) button in your desired region to create a new backup, or go to Admin > BigQuery Quota and Locations to create a new location.
If you are backing up to BigQuery, ensure your Canvas is created in the same data location as your BigQuery Dataset.
Clicking the "+" button in your desired region will open the "Create Backup" window, where you will need to create a backup name and provide a short description. When complete, click "Next Step:Data Categories" or the "Data Categories" tab to continue.
Preconfigure your backup's Table Library
The Table Library is the list of tables developed by Analytics Canvas for the purpose of the UA Backup utility. Your selections in this step determine your default tables.
Choose the categories that are relevant for your website. These can be modified at a later time if you want to include more data.
Once complete, select "Next Step: Export Options" or the "Export Options" tab to continue. To continue with only custom queries, leave them unchecked and click Next Step.
If you don't already have a Universal Analytics authorization token, you will be asked to create one. Ensure that you permit Analytics Canvas to "See and download your Google Analytics data" from the Google Analytics accounts associated with the Google account.
Next, you will need to choose an export type. To learn more about the export types and related limits, visit this article on the knowledge-base.
Selecting Sheets will have the most instances of "Low" and "Medium" level of detail on the default queries. While selecting BigQuery will include more Mediums and Highs, they will not all be set to high.
Having pre-configured the Table Library, it is time to define Views and queries.
Define Views and Time Period to be included in the backup
You can select from one or more views, and from more than 1 Google Analytics credential to define your backup. Select your View or Views and click Next Step.
While you can select more than one View, the sites must be tagged identically to generate a backup. If they are different, configure a backup for 1 view, then make copies for subsequent Views. Modify the copies with the custom fields unique to each View
Canvas will now query the selected views to see how much history is available and how many sessions occurred in that window.
Customizing the Table Library
Canvas will now begin estimating the number of rows to be included in the backup. While it is running, you can:
- Create your custom queries (Create Tables)
- Modify the Table Library (Configure Predefined)
- Remove tables from the backup (Remove Tables)
The Row estimator will continue to update based on the selections you have made. Since the next step requires knowledge of the tables and their estimated size, you cannot proceed to the next step until the estimate has completed.
Adding your own custom queries
No backup will be considered complete if it cannot satisfy your current reporting requirements. To be able to power your existing dashboards and reports that may be connected to the API, you will need to create tables that contain the required data.
Click "Create Table" to bring up the custom query builder.
Google Analytics limits you to 9 dimensions and 10 metrics per query. A selected segment counts towards your list of dimensions.
The query editor has enabled ALL listed dimensions and metrics available for the Universal Analytics Reporting API V4. You can select from the Standard segments or Custom segments visible to your account, and you can define filters in the same way as you do in the web interface.
Configuring the Predefined Queries in the Table Library
The table library was generated based on your earlier selections. By clicking "Configure Predefined", you can modify the default selections in the following ways:
- Select one or more tables to include in or exclude from the library
- Select the time granularity (Period) for the query, including Days, Weeks, Months, and Years. These will be the date dimensions of the selected table(s).
- Select the detail level.
To see the list of included dimensions and metrics in each table, select the "Show query dimensions and metrics" option. The list of dims and metrics will update as you change the level of detail.
When you are finished with your modifications, click "Update". The estimate will automatically update.
Working with the Table Library
On the main Table Library, you can edit a pre-configured table to:
- Change the time granularity (Days, Weeks, Months, Years)
- Change the level of detail (low, medium, high)
- Convert the query to a Custom query
Converting to a custom query modifies the pre-configured table. It will now have the label "_edited" added to it. You can modify pre-defined queries to include your own goals, custom fields, segments, and filters.
If you don't wish to convert it to a custom query, you can simply change the level of detail or the time granularity, and click Update.
Clicking the Copy icon will keep the existing table, generate a copy of it, and put you into the query editor so that you can modify it. It will have the label "_copy". You can modify this query just like any other custom query.
Exporting your Universal Analytics data to your Data Destination
Having configured your queries, you are now ready to define the data destination where Canvas will write your UA data.
To see what the data looks like in each destination, refer to this article in our knowledge-base.
Having configured your queries, you are now ready to define the data destination where Canvas will write your UA data.
Exporting your UA data to BigQuery
To export to BigQuery, you must permit the Analytics Canvas Service account for your subscription to access the BigQuery project and Dataset you want to write into.
This means you must first create the Project and Dataset in BigQuery. You must also provide at least BigQuery Read Session User permission on the Project in Google Cloud's IAM, and BigQuery Data Editor on the Dataset in BigQuery.
Click Select Dataset to be shown the Service Account for your subscription. Then head over to the Google Cloud Console to add permissions for Analytics Canvas to write into your dataset in BigQuery.
Permissions will be granted in 2 places. To connect to your BigQuery Dataset, first permit the Analytics Canvas Service Account for your subscription to access the BigQuery Project AND your Dataset where you want to write data into. Learn more in this article from our knowledge base.
1) Add BigQuery Read Session User at the Project Level in Google Cloud IAM & Admin.
2) Add BigQuery Data Editor on the Dataset where you'd like to write into within BigQuery.
Having granted permissions, if you followed the steps above, the project should be available to select within Analytics Canvas within 10 minutes (usually much faster).
Return to Analytics Canvas and click the Refresh All button. If you don't see your Project and Dataset, wait a few minutes and click Refresh All again. If after 15 minutes you don't see your dataset, review the steps above to ensure everything is correct, and make sure that your BigQuery dataset is in the same region as the UA Backup you created.
If you are backing up to BigQuery, ensure your UA Backup is created in the same data location as your BigQuery Dataset.
If you do not see your dataset in Analytics Canvas, verify the location of the dataset and that you have created your backup in the same data location.
Exporting your UA data to Excel
To Export to Excel, simply select the checkbox next to the Excel file. Your data will be delivered into an Excel workbook, subject to the export rules above. Your data will be available as a download from within Analytics Canvas once your backup is complete.
Exporting your UA data to Google Sheets
To Export to Sheets, you must first authorize a credential with access to Google Sheets, then point to a Folder in your Google Drive where the data will be delivered.
Exporting your UA data to CSV
To Export to CSV, simply leave the other options unchecked and click "Next Step". Your data will be available in a series of CSV files, one per table, subject to the export limits described above. Your data will be available as a downloadable .ZIP file from within Analytics Canvas once your backup is complete.
Validation and Purchase
The final step, after you have configured a backup location, is to validate the output and run the backup. At this step, you will review the cost, the row estimates, and accept the terms of the backup.
After clicking "Review and Purchase" you will be taken to the Run screen. If you do not already have tokens you can purchase them by clicking "Purchase Credits". You will be prompted to purchase the number of credits needed for this backup. If you purchase more than required, the credit will remain on your account.
If your account already has the required credits, you will simply have a Run button.
While your backup is running, you can review the log to see the progress. You do not need to keep this window open - you can close it and continue to make other backups or wait for the email to tell you that your backup is complete or needs further attention.
Once your backup changes status from Purchased to Running, you can click the Running link to see a detailed log of the progress.
You will receive an email when the backup is complete.
Backup Status Messages
You can leave your backup at any point, including when it is running, and return to it to continue where you left off. When you return to https://cloud.analyticscanvas.com/UAbackup, you will see your backup has one of the following status messages:
| Status | Definition |
|---|---|
| Beta Config | The backup was configured during the beta period. It is no longer editable. Copy it to put it into the Pending state, delete the Beta Config backup, then rename your copy to the original filename. |
| Pending | The backup configuration has been started but has not been run. You can continue to edit the backup and you can delete it. |
| Purchased | The backup has been configured and paid for, but has not run yet. You can no longer configure the backup, but you can copy it. |
| Running | The backup is currently running. Clicking the Edit button will bring up the status of the backup. |
| Paused for Quota | This is either the daily quota limit of 10,000 API calls per day, OR an hourly limit on retries if a particular table or partition failed. |
| Complete | The backup has been run to completion. You can copy it, edit it to add new tables, but you cannot modify tables that have already been run. |
| Failed | The backup run has failed. Refer to the error messages and action accordingly. Most often you simply need to re-run. |
Troubleshooting
If the solutions above do not resolve your issue, please contact support@analyticscanvas.com and provide as much detail as possible about your situation. Screenshots and screen share videos are very helpful and will generally result in a faster resolution.
FAQ
How much data is available?
Analytics Canvas can retrieve data up to the start date of your Google Analytics Views. During the beta period, you can load data into Canvas from any date range, but you will only be able to export data from 2023. When the product is publicly available, you will be able to export the full time period.
Should I export to the Agency BigQuery project or the Client's?
We strongly recommend writing directly to your client's BigQuery account. However, clients may be unable to setup BigQuery right away. You can load into your own BigQuery account and transfer the data later using a tool like Analytics Canvas.
What kind of training and support is available?
We pride ourselves delivering great customer service. If you need help, we will quickly respond. We have documentation, getting started videos and tutorials available on our website. Contact us or schedule a demo or a training session.
What if I have multiple Views to backup?
You have the option of creating a single backup or a separate backup per view. You would create a single backup in cases where the Views are tagged identically (generally in the same Property). If there is any variation in custom dimensions, custom metrics, events, or goals, you will want to create a separate backup per View.
For those with dozens or hundreds of views to backup, you can configure a single backup, then copy it to make things faster. You will then have a default selection to work from, and will only need to change the Views, the exports, and those queries you want to customize.