1. Home
  2. Knowledge Base
  3. Universal Analytics Backup
  4. What does the UA backup look like in Sheets, Excel, BigQuery, and CSV?

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 rows 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 SourceLimits
CSV AvailableLess than 50M rows total AND no single table greater than 20M
BigQuery AvailableAlways available. Cannot be unselected when total backup is more than 50M rows or any single table is greater than 20M rows
Excel AvailableLess than 20M rows in the backup. Any table with more than 1M rows will be exported as a CSV file.
Sheets AvailableLess than 20M rows in the backup. Worksheets greater than 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"

Image

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. 

Image

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. 

Image

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.

Image

The data in each worksheet is a flat table starting at cell A1.

Image

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.  

Image

Viewing your data in the optional Looker Studio report

If you selected the Looker Studio report and associated tables, you can interact with your data in Looker Studio. The Preview report using the BigQuery connector can be found here. 

Image
Was this article helpful?

Related Tutorials/Video