Integrate VWO with BigQuery to Export Campaign Data

Overview of VWO <> BigQuery Integration

Integrating your VWO account with BigQuery (BQ) enables seamless streaming of your VWO experiment data into BigQuery. This integration helps consolidate your data in a robust and scalable data warehouse, allowing you to run complex SQL queries and conduct advanced data analysis efficiently.

For example, if you export GA and VWO data to BQ, you can combine historical website traffic data from GA with A/B test results from VWO. This enables you to analyze long-term trends, understand how A/B tests affect visitor behavior over time, and build more sophisticated attribution models to measure the effectiveness of your overall marketing strategy.

Configure the Integration

Configuring the VWO-BigQuery integration is a multi-step process:

Step 1: Create a Service Account for Your BQ Data

1. Log in to your Google Cloud account and select the project where you want to store your VWO experiment data in BigQuery.
2. From the main menu panel on the left, go to IAM & Admin > Service Accounts.
   
3. On the Service Accounts page, click Create service account, enter the service account name and account ID, and click Create and Continue.
4. You can skip the next step and click on Done to save your changes. Upon successful creation, the new service account is listed on the Service Accounts page.
5. Now, click on the newly created service account and go to the Keys tab.
6. Click Add Key > Create New Key, select JSON as the key type option, and click Create. The service JSON file is downloaded in your browser. Keep this file secure, as you will need it later.
   
    

Step 2: Create a Custom Role

1. In the Google Cloud console, go to IAM & Admin > Roles.
2. Click Create Role and enter the following details:
   • Title: VWO Custom Role
   • ID: vwo_custom_role
   • Stage: General Availability (GA)
3. Under Permissions, click Add Permissions and search for the permission bigquery.jobs.create.
4. Click Add and then Create to save the role. Upon successful creation, the custom role looks like this:
   
    

Step 3: Assign the Custom Role to Your Service Account

1. Go to IAM & Admin > IAM.
2. Click Add and enter the email ID of the service account created earlier.
3. Under Role, select VWO Custom Role (created in Step 2) and click Save.
   
    

Step 4: Create a Cloud Storage Bucket and HMAC Key for Your BQ Data

1. In the Google Cloud console, go to Cloud Storage.
2. Click Create Bucket and follow these steps:
   • Select Protection Tools as None or Object Versioning.
   • Ensure the bucket does not have a retention policy.
3. Once the bucket is created, go to the Bucket Permissions section.
4. Click Add Principal and assign the role Storage Object Admin to your service account.
5. Click SAVE.
   
    
6. Now, go to Cloud Storage Settings > Interoperability.
7. Click Create a Key for a Service Account.
   
    
8. Select the service account you created earlier.
    
9. Click Create Key and copy the Access ID and Secret Key.

Step 5: Create BigQuery Datasets and Assign Permissions

1. In the Google Cloud console, go to BigQuery.
2. Create a new dataset with your preferred name.
3. Go to the dataset permissions, click Add Principal, and assign the following roles to your service account:
   • BigQuery User
   • BigQuery Data Editor
     
      
4. Now, create another dataset named vwo_raw_data. This dataset will serve as the staging area for your VWO data before being processed into your chosen dataset.
5. Assign the same roles (BigQuery Data Editor and BigQuery User) to the service account for the vwo_raw_data dataset as well.

Step 6: Enable the BigQuery Integration in VWO

1. Log in to your VWO account.
2. From the left panel of your VWO dashboard, go to Configurations > Integrations. This menu provides integrations that can be enabled at the account level.
3. Browse for the BigQuery integration, click on it, and turn on the toggle switch to enable it.
4. You are auto-navigated to the Config tab to connect your BigQuery account with VWO.
5. Check the Push VWO Data to BigQuery checkbox and save it.
6. Click Connect BigQuery.

7. Enter the details in the following fields and click Create Connection.
   
    

   Field	Value
   Project ID	Specify the Google Cloud Project ID, where the required BigQuery dataset is present.
   Dataset Location	Specify the location of the BigQuery dataset.
   Dataset ID	Specify the ID of your selected BigQuery dataset.
   HMAC Key Access ID	Enter the HMAC key Access ID you created earlier.
   HMAC Key Secret	Enter the HMAC key Secret you created earlier.
   GCS Bucket Name	Specify the GCS Bucket Name you created.
   Service Account	Paste the content of the service account JSON file.

   
   
   Once the connection is established, your VWO experiment data will start streaming to your BigQuery account.

If you wish to enable the BigQuery integration for your existing campaigns, select the campaign, go to Configuration > Integrations, and select BigQuery.

BigQuery Data Structure and Export Overview

The VWO-BigQuery integration exports your campaign data to your BigQuery project, allowing you to run advanced SQL queries, combine experimentation data with other business data sources, and build custom reports outside the VWO UI.

For detailed information on the exported datasets, tables, column definitions, and reporting guidance, see VWO Campaign Data Export to BigQuery: Schema and Reporting Guide.

Need more help?

For further assistance or more information, contact VWO Support.