[UA] Set up BigQuery Export [Legacy]

You are viewing a legacy article about Universal Analytics. Learn more about Google Analytics 4 replacing Universal Analytics.
This feature is only available in Analytics 360, part of Google Marketing Platform.
Learn more about Google Marketing Platform.
In this article:

Step 1: Create a Google API Console project and enable BigQuery

 

  1. Log in to the Google APIs Console.
  2. Create a Google APIs Console project.

    You can create a new project or select an existing project.
  3. Navigate to the APIs table.

    Open the Navigation menu in the top-left corner, click APIs & Services, then click Library.
  4. Activate BigQuery.

    Under Google Cloud APIs, click BigQuery API. On the following page, click Enable.
  5. If prompted, review and agree to the Terms of Service.

Step 2: Prepare your project for BigQuery Export

 

  1. Ensure Billing is enabled for your project.

    If you do not have Billing enabled for your project, open the Navigation menu in the top-left corner, then click Billing.
  2. If prompted, create a billing account.

    A billing account is necessary to apply billing to a project. A single billing account may be shared across multiple projects. Follow the steps in the API console to create your billing account.
  3. Accept the free trial if it's available.

    If you are offered a free trial, it is safe to accept it; however, you must also enter billing details in order for BigQuery to continue receiving exported data once the free trial is over.
  4. Validate Billing enablement.

    Open your project at https://console.cloud.google.com/bigquery, and try to create a data set in the project. Click the blue arrow next to project name, then click Create data set. If you can create the data set, billing is setup correctly. If there are any errors, make sure billing is enabled.
  5. Add the service account to your project.

    Add analytics-processing-dev@system.gserviceaccount.com as a member of the project, and ensure that permission at the project level is set to Editor (as opposed to BigQuery Data Editor). The Editor role is required in order to export data from Analytics to BigQuery.

Step 2.1: [Optional] Prepare your BigQuery Dataset for EU storage

 

Consider localizing your dataset to the E.U. at this step.

Data is geolocated in the U.S. by default. Localizing your data to the EU after the initial export can cause issues with querying across BigQuery regions. Resolving those issue may require a transfer of data, which has associated costs. We recommend creating the E.U.-localized dataset at this point in order to avoid any negative side effects.

Google Analytics BigQuery Export is incompatible with GCP policies that prevent dataset creation in the US. If you have such a policy on your GCP project, you will have to remove it to export your data to the EU.

If you don’t want to geolocate your data in the EU, proceed to Step 3.

  1. Open your project at https://console.cloud.google.com/bigquery, and Click Create new dataset.
  2. A panel opens in which you enter the necessary information to create your dataset.
     
    Make sure you set Data Expiration to Never if you do not want BigQuery to automatically delete your data.
    • Enter an ID for the dataset. The dataset ID must be the same as the Analytics View ID, which you can find in the universal picker in Analytics.
    • For Data location, select EU.
    • Set the data expiration you want.
      Select Never if you ever want to do historical analysis. Once data expires, it is permanently unavailable.
    • Click OK.

Step 3: Link BigQuery to Google Analytics 360

 

It is our best practice, and we strongly advise, that you link no more than 300 Google Analytics reporting views to a single BigQuery Project. Doing so may degrade the export of intraday data.

You can link only 1 view per property.

After you complete the first two steps, you can enable BigQuery Export from Analytics Admin.

  1. Sign in to Google Analytics. Use an email address that has OWNER access to the BigQuery project, and also has the Editor role for the Analytics property that includes the view you want to link.
  2. Click Admin, and navigate to the Analytics 360 property that contains the view you want to link.
  3. In the PROPERTY column, click All Products, then click Link BigQuery.
  4. Enter your BigQuery project number or ID. (Learn more about how to locate your project number and ID.)
  5. Select the view you want to link.
  6. Optional: Select the email addresses at which you would like to receive daily success and/or failure notifications.
  7. Optional: Select your current-day export preference. Note that the continuous export option uses the Cloud streaming service, which includes an additional $0.05 charge per GB sent.
  8. Confirm that you have enabled billing and applied any relevant credits or coupons to your project.
  9. Click Save.
  10. If you need to stop the export, return to this page, and click Adjust Link in the BigQuery section.

You need the Editor role to unlink BigQuery from Analytics 360. 

  1. Click ADMIN > PROPERTY column > PRODUCT LINKS > All Products.
  2. Under BigQuery, click Adjust Link > Unlink.

Pricing and billing

BigQuery charges for usage with two pricing components: storage and query processing. You can review the pricing table and learn about the differences between interactive and batch queries.

You still need to have a valid form of payment on file in Cloud in order for the export to proceed. If the export is interrupted due to an invalid payment method, we are not able to re-export data for that time.

When you start seeing data

Once the linkage is complete, data should start flowing to your BigQuery project within 24 hours. 1 file will be exported each day that contains the previous day’s data (generally, during the morning of the time zone you set for reporting), and 3 files will be exported each day that contain the current day's data. We will provide a historical export of the smaller of 10 billion hits or 13 months of data.

Backfilling data

When you initially link an Analytics reporting view to BigQuery, Analytics exports 13 months or 10 billion hits (whichever is smaller) of historical data to BigQuery. That export of historical data happens only once per view. If you subsequently unlink a view and relink it to a different BigQuery project, Analytics does not perform another export of historical data for that view.

When you upgrade a property from Standard to 360, the data you collected before the upgrade that falls within the 13-month/10-billion-hit limit is also exported.

Avoiding export failures

Failure to complete and maintain each of the following items can temporarily disable your account and cause daily BigQuery Exports from Analytics to fail. Keep in mind that we cannot reprocess failed exports that are caused by your failure to complete or maintain each of the following.

  • Make sure the service account has the necessary permissions

    If at any point the service account (analytics-processing-dev@system.gserviceaccount.com) does not have EDIT access to the project, then data won’t be exported.
  • Ensure that billing is enabled.
  • Ensure that the BigQuery API is enabled.

    In Google Cloud Platform > your project > APIs & Services > Dashboard, make sure the BigQuery API is enabled.

Support

After you set up BigQuery Export, contact Analytics 360 support for issues related to linking BigQuery and Analytics 360.

For all other issues, e.g., billing, contact Google Cloud Support.

BigQuery Export

For updates and community support and tips about the Google Analytics 360 BigQuery Export feature, join the ga-bigquery-developers Google Group.

For information about the export and access to a sample data set, read the BigQuery Export documentation.

BigQuery

BI-vendor integration with BigQuery

Was this helpful?

How can we improve it?
true
Choose your own learning path

Check out google.com/analytics/learn, a new resource to help you get the most out of Google Analytics 4. The new website includes videos, articles, and guided flows, and provides links to the Google Analytics Discord, Blog, YouTube channel, and GitHub repository.

Start learning today!

Search
Clear search
Close search
Google apps
Main menu