Set up BigQuery Export

30/11/2019
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-APIs-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 API.
  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 Products & services 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://bigquery.cloud.google.com, 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). Editor permission is required in order to export data from Analytics to BigQuery.
  6. Redeem your coupon code.

    Go to cloud.google.com/redeem to redeem your rolling $500-per-month Google Analytics 360 credit for BigQuery. Your code will be included in an email from your Account Manager or Sales Partner. This step is necessary in order to receive the $500-per-month Google Analytics 360 credit for BigQuery, and must be completed before the export is initiated.
    If you are prompted with an alert to create a new Billing account, then add your organization ID to the end of the URL (https://console.developers.google.com/billing/redeem?organizationId=your organization ID).

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.

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

  1. Open your project at https://bigquery.cloud.google.com, and Click Create new dataset.
  2. A panel opens in which you enter the necessary information to create your dataset.
    • 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.

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 Edit permission 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.

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. We offer a $500-per-month credit for Google Analytics 360 clients towards usage of BigQuery. While we can't predict how much your monthly BigQuery charge will be because hits vary in size based on the information they contain, an export of 500M hits is about 1TB of data, which costs about $20 per month.

While we offer the $500-per-month credit, 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.

The $500-per-month credit covers all the services related to BigQuery that are listed here. The credit does not cover Cloud expenses that are not related to BigQuery, like Compute Engine and Cloud Storage.

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, 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 within 4 weeks after the integration is complete.

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.

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

* Nguồn: Google Analytics