Exploring the UA Backups in each of the 4 data destinations
This article shows you what the UA backup will look like in your chosen data destination, Google BigQuery, Sheets, Excel, or CSV. Data is extracted from the API and the results are stored in flat tables that any BI tool can connect to and any analyst can read from.
This article relates to the UA Backup Utility offered by Analytics Canvas. Learn more about the utility on the landing page and learn how to use it in this article of our knowledge base.
Data export options for Universal Analytics Backup utility
Export options include BigQuery, CSV, Excel, and Google Sheets. However, there are limits to each data source as defined by the sources themselves. For example, you cannot write more than 10M cells to Sheets, or 1M cells to a given Excel worksheet.
Beyond these limits, the size of a given file can limit its usefulness (it may not be able to load on the average PC). As such, the following table determines which export options will be available to you.
| 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 |
Viewing your data in your BigQuery Dataset
BigQuery is the best option for your Universal Analytics data. We recommend putting your UA data in a new project, separate from your GA4 data. If your backup overall is greater than 50M rows or any single table is greater than 20M rows, the only option available will be BigQuery.
In the BigQuery console, navigate to the Project and Dataset where you wrote your backup. There you will find each table labelled according to the name of the backup, the name of the table, the time granularity, and whether it's a detailed or summary table. For example: "Datamartist_Sales_View_AcquisitionAllTrafficReferralsDailyDetailed"
Click on a table, then click the Preview tab to see your data.
These tables can be pulled into a spreadsheet or connected to any BI tool for visualization and analysis.
Viewing your data in Google Sheets
Google Sheets is available as an Export option when your backup is less than 20M rows
Google Sheets has a limit of 10M cells per workbook. As such, Analytics Canvas will create one or more Google Sheets documents in order to house your backup. These will be located in the Google Drive location you selected.
Each workbook will have a summary tab as shown below, which outlines the tables, which sheet they are in, how many rows were backed up, and what query defines each table.
The tables are standard, flat tables starting at position A1 of each sheet. Each sheet is labelled according to the table name, but due to character limits, is abbreviated as defined in the Table List sheet.
Viewing your data in Excel
Excel is available as an Export option when your backup is less than 20M rows
Excel has a limit of 1M rows per worksheet. As such, Canvas will create one workbook and load data into worksheets where the tables are less than 1M rows. Tables that are more than 1M rows will be included as separate CSV files.
Similar to Google Sheets, there is an Index worksheet which describes the worksheets and what query is in each table.
The data in each worksheet is a flat table starting at cell A1.
Viewing your data in CSV files
CSV is available as an Export option when your backup is less than 50M rows and when no individual table is more than 20M rows.
If you have selected CSV files, or if you selected a spreadsheet format but the table was too big to fit, the data for each table will be exported into a separate CSV file. The files will be assembled into a .ZIP file and available for download within the UA Backup utility.
Each table will be exported into its own CSV file with the column headers in row 1 and the data following in row 2. Data is comma separated.
