1. Home
  2. Knowledge Base
  3. Universal Analytics Backup
  4. Step-By-Step Guide to Backup UA data with Analytics Canvas
  1. Home
  2. Knowledge Base
  3. Google Analytics [UA + GA4]
  4. Step-By-Step Guide to Backup UA data with Analytics Canvas

Step-By-Step Guide to Backup UA data with Analytics Canvas

This detailed, step-by-step article goes through the process of creating a backup of your Universal Analytics data using the Analytics Canvas UA Backup Utility.  

The utility contains a library of common tables which can be selected and modified, a custom query builder for you to create your own custom queries to complete your backup, and an optional interactive Looker Studio report together with related backup tables

Follow the steps below to create 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.

Analytics Canvas Table Library for backing up Universal Analytics data

The team at Analytics Canvas have designed and maintained commercial applications for extracting Universal Analytics data since 2010.  Historically, users have configured their own data pipelines and built their own Google Analytics data warehouses with the core Analytics Canvas data-prep tools.

With the sunset of Universal Analytics, and with no 'one-click' download available from Google, and due to limitations on the API, the way to create a backup is to download reports which can contain at most 9 dimensions and 10 metrics.

The Table Library developed by Analytics Canvas is a baseline for backing up this dataset. It provides a series of queries within each data Group available in the Google Analytics V3 Meta Data API.  These groups include Adwords, Traffic Sources, Geography, System, etc.

Image

The library consists of the preconfigured tables that Analytics Canvas has made available.  The tables have user-selectable date periods (day, week, month, or year), as well as a Level of Detail selector, which changes the number of dimensions within each table. 

Note: These tables are modeled after the Universal Analytics default reports interface, and do not represent a complete extraction of all available dimensions and metrics.  

Users have the option to include some, all, or none of the tables from the Table Library. 

Analytics Canvas Looker Studio Template for backing up Universal Analytics data

You have the option to add a 20 page Looker Studio report that includes pre-built and interactive charts, scorecards, and data tables that review a website's data.  The template is generated using the Looker Studio Linking API and connecting it to data exported by Analytics Canvas to your dataset in BigQuery or your Sheets in Google Drive. 

The template can be customized, white-labelled, or otherwise modified by the end-user. 

While the visual components tend to have between 1 and 4 fields, the data tables themselves consist of 28 independent tables linked to the 13 dashboards that can be added to the report. The tables contain up to 9 dimensions and 10 metrics each, in defined combinations that cannot be modified. 

Image

Inclusion of the templates requires a Google account with access to Looker Studio and either Google Sheets or Google BigQuery.

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:

⚠️

If you include the Looker Studio template, the only options are Sheets and BigQuery. If your data is too big for Sheets, your only option is BigQuery.

Destination Data SourceLimits + Availability
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
  • 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.  

If you're not yet sure what to backup, checkout our article "How to Backup Google Analytics data for Migration to GA4". 

The following are required to get started:

  • Analytics Canvas Online account
  • Google account with Viewer access to Universal Analytics View.

You may need: 

  • Access to Google BigQuery for your organization or for your client
  • A Google Drive folder to store your Google Sheets 
  • A Google account with Looker Studio available 

You can connect one or more Google accounts, such as data@yourCompany.com or youragency@gmail.com. The account must have a minimum of Viewer level access to the Universal Analytics View(s) that you’d like to backup.  Learn more about getting access to Views.

Overview of Key Steps:

  1. Define your backup and generate an initial estimate for one or more views 
  2. Modify your backup to your budget and data retention requirements 
  3. Connect / configure an export destination (BigQuery, Sheets, Excel, CSV)
  4. Run your backup
  5. Collect your data and Generate your Looker Studio report

The table library will generate an estimate after the first step.  This is only an initial estimate and should be reviewed and modified to fit your requirements.

Step 1 - Define your backup and generate an initial estimate

The first step is to create a new backup. The step-by-step guide will prompt you for inputs that are specific to your account and your website.  It will then generate a generic, initial estimate which you will further refine before running the backup. 

Create the backup in a data location and begin the backup wizard

In Analytics Canvas, you will 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.

Image

Clicking the "+" button in your desired region will open the "Create Backup" Wizard, where you will create a backup specification for one or more views.

Provide a Name, generally the name of the View or ViewID, and optional short description, then click "Next Step:Dashboards".

Image

Preview and Select the Looker Studio UA backup template

The template provided by Analytics Canvas can be deselected at this step.  If selected, it will include a series of tables that are required for different sections of the report, which can be configured later. 

Image

The Looker Studio report template is selected by default.  If you wish to skip the dashboards and just use the Table Library or your own custom tables, deselect this option.  You can always change your selection before running the backup. 

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.

Image

Choose a data destination to export your UA backup

Next, you will choose an export type.  To learn more about the export types and related limits, visit this article on the knowledge-base

The selection made here can be changed before the final export.  This is just to determine the configuration of the default tables.  

Selecting Excel or Sheets at this stage will reduce the level of detail on some tables in the Table Library and result in fewer rows than when BigQuery is selected. 

⚠️

Excel cannot be selected with the Looker Studio reports since the Linking API cannot be used to connect data from Excel to a Looker Studio template.

Image

Authorize Google Accounts for access to your Universal Analytics Views

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.

Image

Define Views 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.  Note that if you are including Dashboards, only 1 View can be selected. 

Image
⚠️

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

Define the Time Period for each view

Canvas will now query the selected views to see how much history is available and how many sessions occurred in that window.

Image

You can change the history of each view by adjusting the start and end date.  This will impact the price of dashboards and library tables.  Revisit this step if the backup estimate exceeds your budget. 

⚠️

Check your data retention settings. You may not get the full history of data in all tables.

Reviewing the Table Library and Initial Estimate

Based on the data categories selected, Analytics Canvas will include a series of suggested tables to get your backup started, and will begin estimating the number of rows to be included in the backup based on the default selections.  The default selections were configured based on the initial steps in the backup wizard (data location and selected tables). 

Once started, the estimate will run in the background. The estimate may take up to 20 minutes.  You may continue to work while it processes.  

Your price is based on the final estimate, after your configurations and changes, and you will pay the lower of the estimate or rows delivered.  If the rows delivered affected the price, you will receive a refund before your account is closed. 

Image

Once the estimate is complete, you will have your initial estimate based on the default settings.  You can now further refine the backup to fit your budget and your backup requirements. 

Step 2 - Modify your backup to your budget and data retention requirements

The initial estimate is a generic model that can apply to most websites.  You can now modify the estimate by removing tables, adding tables, and customizing tables as described below.  

You may continue to make changes while the estimate runs.  You can also close the application and return to complete the estimate later. 

Customizing the Table Library

Based on the data categories selected, Analytics Canvas will include a series of suggested tables to get your backup started, and will begin estimating the number of rows to be included in the backup based on the default selections.  The default selections were configured based on the initial steps in the backup wizard (data location and selected tables). 

Modifying Tables in your UA Backup

The following tools are now available: 

  • Add or remove tables from the Dashboard (Dashboards)
  • Create your custom queries (Create Tables
  • Modify the Table Library (Configure Predefined)
  • Remove tables from the backup in bulk (Remove Tables)
Image

Table Level Controls

There are also controls on the individual tables within the tables shown on the Tables menu, that allow you to Delete, Editor, or Copy tables.  

Tables with the Looker Studio icon belong to the Looker Studio Dashboard and can only be deleted.  

Tables with the Analytics Canvas icon originated from the Analytics Canvas Table library. 

Image

Delete an individual table

You can delete an individual table before the backup has been run.  This applies to Looker Studio tables as well as tables from the Table Library.

Edit or Modify a Table from the 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

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. 

Image

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.  

Copy a Table

You can copy a pre-defined table to get a new table with the same specifications.  You can then modify the table by editing it to add or remove dimensions and metrics, include your own goals, apply a segment, and / or apply filters.

Copied tables will keep their default names and will include "_copy" in the title.  Modify this name so that it makes sense for your backup.

Image

Select the Dashboard or Modify the Included Pages

During the initial setup, if you did not include dashboards but now want to add them, OR if you would like to remove some of the pages from the dashboard, click the Dashboards button.

Image

To include the dashboard, ensure the "Include Looker Studio Report" option is checked.  You can remove specific pages and related tables by un-checking them.  Checking "unselect all" will remove the supplemental pages from the dashboard but include the Overview page.  To remove the dashboard entirely, deselect "Include Looker Studio Report". 

Image

Creating your own Custom Queries and Tables

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.

Image
⚠️

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.

Image

When you are finished with your modifications, click "Update".  The estimate will automatically update. 

Changing the Time Period

The Time Period affects the level of time granularity.  While you can get detail down to DateHourMinute, the initial estimate for tables from the Table Library are based on Date. 

Changing the time granularity from Days to Weeks, Months, or Years may have a significant impact on your table size.  

You can choose the time granularity on each table individually, and you can choose to have multiple tables, each at a different time granularity.  This is helpful for cases involving unique metrics, such as Users. 

Changing the Detail Level in a Pre-defined Table

To help further affect the row count on each table, the utility provides three pre-set selections for each table. 

Low detail includes the fewest dimensions, but still make the table itself relevant to it's data category.  

Medium detail adds additional, related dimensions that are of 'medium' cardinality. 

High detail includes the maximum number of related dimensions in the data category.  Typical high-cardinality dimensions include PagePath or EventLabel. 

Remove one or more Tables from the Backup

Click the Remove Tables button to remove tables in bulk from a given backup. 

Image

In some cases, a set of tables will return an estimate of 0 rows.  This is normal in cases where a website did not enable Demographics & Interests, or did not have eCommerce enabled, yet the user configuring the backup selected those data categories. 

To avoid creating the tables and having no data in them, which may confuse future consumers of the backup, you can quickly remove all tables with 0 rows at once. 

⚠️

This does not apply to the Looker Studio reports. Those tables are included, but contain no data, so that the reports do not show an error message. 

Image

Step 3 - Connect / configure an export destination (BigQuery, Sheets, Excel, CSV)

The next step is to determine where to put the backup tables.  BigQuery is best. It is fast, cheap, and readily accessible by BI tools, spreadsheets, and business users.  

Exporting your Universal Analytics data to your Data Destination

The Row Estimate will continue to update based on the selections you have made.  Once the estimate has generated, Canvas will determine which backup options are available.

Image

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. Once you have decided, configure your data destination according to the instructions below. 

Exporting your UA data to BigQuery

⚠️

If your BigQuery account is in Sandbox / Free Trial mode, you must convert to a paid account before running the export. By default, tables in trial accounts expire after 60 days so you will lose your data if you do not change this setting on each table created during your BigQuery trial. 

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:

  1. BigQuery Read Session User permission on the Project in Google Cloud's IAM, and
  2. 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.

Image
🛑

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.

Image

2) Add BigQuery Data Editor on the Dataset where you'd like to write into within BigQuery.

Image

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. 

Image
⚠️

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.

ImageImage

Still having difficulty?  Review the troubleshooting guide

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. 

Image

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. 

Step 4 - Run your backup

Once you have configured your backup, you can run a one-time Preview for each View, or you can proceed to the final stage, "Preview and Purchase", where you can run your preview or your full backup. 

Preview 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, run an optional Preview, and accept the terms of the backup if you are ready to proceed with a purchase. 

Image

Optional Preview

While you can see what the tables will look like in this article, and what the dashboard will look like here, to help agencies, clients, and end-users to see what the backup will look like with their own data, an optional preview is available.  

The preview can be run once for each View. 

For those who need to view tables and make adjustments before the Preview or after the initial preview was run, the main Analytics Canvas application has all the tools needed.  If your trial of the main application has expired, simply reach out to us to discuss your options. 

Purchasing your UA 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 additional credits will remain on your account.  They can be used for additional backups or to add to the backup you created. 

Purchases are made on the website of our reseller, FastSpring LLC.  They will handle taxation for your region and the charge will appear on your credit card as "FS*nmodal" (nModal is the company that makes Analytics Canvas).  

To purchase by bank-transfer for orders over $1,000, contact us

Image

Running your UA backup

If your account already has the required credits, you will simply have a Run button.  Otherwise, collect the order number sent to your email by FastSpring (mailer@fastspring.com), and enter it into the application, then click "Validate Order". 

Image

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.

Image

Once your backup changes status from Purchased to Running, you can click the Running link to see a detailed log of the progress.

Image

You will receive an email when the backup is complete.

Step 5 - Collect your data and Generate your Looker Studio report

The user who ran the backup will receive an email when the task is complete.  Other users may login and check the status of the backup until completion.  

Once complete, the Exports link will appear in the row of the backup.  Click the link to bring up the Exports available for that backup.

Image

The exports dialogue will link to your data, provide a link to download your CSV or Excel files (where relevant), and provide details on picking up your Looker Studio report (if applicable). 

Image

For those that just want data tables, that's it!  Your data has been delivered and the service is complete.  

Create your Looker Studio UA Backup Report

To get your Looker Studio reports, click the "Create Report" link, and follow the detailed instructions found here

Other Controls for your UA Backup

Once you have reached the Tables menu of a backup, it will remain as a backup specification within your UA Backups menu.  You can work with the backup in the following ways: 

  1. Edit the backup
  2. Copy the backup 
  3. Delete the backup
Image

Edit a Backup

Click the Pencil icon to edit a backup.  Editing a backup will start you at the Tables menu.  You can navigate back to the Config section to change the View and the Time period, make modifications, or continue to run your backup while it is in the Pending state. 

Once the backup has been run, you can Edit the backup to add more tables to it.  Once run, you can no longer change the time period or any tables that have already been exported.  You can add Dashboards to a backup that ran without them by editing the backup, clicking the Dashboards button on the Tables menu, then running the backup for the additional tables.  

Copy a Backup

Click the Overlapping Documents icon to copy the backup.  The copy will keep all specs that existed at the time of the copy.  

This feature is often used by agencies who create a backup template, then copy the template for each client.  It is also used to generate multiple estimates of a given View.  One estimate might contain only the dashboards, one with the full tables, and still other copies with fewer years of data in the backup. 

Delete a backup

Prior to running a backup, it can be deleted.  Deleted backups are non-recoverable.  

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:

StatusDefinition
Beta ConfigThe 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.
PendingThe backup configuration has been started but has not been run. You can continue to edit the backup and you can delete it. You can also run a Preview or if ready, make your purchase and initiate the backup process.
PreviewA Preview has been requested and is now available. Click the link of the word "Preview" to bring up details of BigQuery dataset or download the preview tables.
PurchasedThe backup has been configured and paid for, but has not run yet. You can no longer change the View, the Time Period, or modify any tables that have been exported. You can add tables, the dashboards, and you can duplicate the backup when it has this status.
RunningThe backup is currently running. Clicking the Edit button will bring up the status of the backup.
Paused for QuotaThis 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.
CompleteThe 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.
FailedThe backup run has failed. Refer to the error messages and action accordingly. Most often you simply need to re-run.

Troubleshooting

If an issue arrises with your backup, don't worry - there is lots of time left to debug the issue and run your backup again.  The first thing to do is note the error message shown and refer to this section to resolve it.  If the issue persists, contact our support team for further assistance.

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 provided the data is still available.  Within the Google Analytics admin for your account, under Admin > Tracking Info > Data Retention, you can find settings that allow you to specify how long Google Analytics retains user and event data before automatically deleting it. Some sites are set to as little as 14 months, meaning that some detailed user and event data will not be available. All other data is retained for the full history of your site.

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.

Was this article helpful?

Related Tutorials/Video