Setting Up Google BigQuery

BigCommerce's native integration into Google BigQuery is designed for advanced analytics and custom reporting. This integration puts your BigCommerce data in your Google BigQuery account, which is a part of the Google Cloud Platform.

By having your BigCommerce data in Google BigQuery you will be able to:

• write custom SQL to analyze your data in Google BigQuery
• create custom reports by connecting your Google BigQuery account to your Business Intelligence Platform of choice (like Google Looker Studio, Tableau, and Microsoft Power BI).

Example use cases:

• Create a "Revenue by Product Category" report
• Create a "Revenue by Product Brand" report
• Create a "Cost of Goods Sold" or "Profit Margin" report
• See revenue by country, city, postal code, and more

• Four times per day at 0:00, 6:00, 12:00, and 18:00 UTC, we will automatically update your BigCommerce data in your Google BigQuery Project within your Google Cloud Platform account. The timing and frequency of this update cannot be changed.
• The data is represented in its most recent state only.
  • For example, if you have an order that moves from the order status Awaiting Fulfillment to Shipped, that order will only have 1 row in the orders database table and it'll be represented as Shipped.

See Google BigQuery | Data Sets for a full list of tables and field names created by the integration.

• This integration is only available to BigCommerce Pro and Enterprise plans.
• You will need to sign up for a Google Cloud account to use this solution.
• You will need to set up billing in Google Cloud, even if you are only using their free products. You will not be charged unless you manually switch to a paid plan.

By default, only the store owner user has access to this integration. To grant other users access you'll need to enable the Manage Data Warehouses permission.

Sign Up for Google Cloud

Sign up for an account. Any cost to you will be based on your usage of various Google Cloud products. The Google Cloud Free Tier provides all users limited access to many common Google Cloud products and services free of charge.

Set up billing in your Google Cloud account. This is a required step for the integration to work. Without this step, Google will reject BigCommerce's attemps to load data into your Google BigQuery account.

In the main Google Cloud navigation console go to Billing > Overview to get started.

Create a Project

In your Google Cloud console, click the navigation icon in the top left. Go to IAM & Admin › Manage Resources, then click Create Project. Fill out a project name.

Note: The Project name will show up in the SQL you write later to access your data, so it's helpful to write something intuitive that you can easily remember, but also something relatively short. As a starting point, we recommend using your company name (like bigcommerce) or an abbreviation of your company name (like bigc).

Create a Dataset

In your Google Cloud console, click the navigation icon in the top left. Scroll down to the Big Data section and click BigQuery.

On the left side of the screen, click the ⠇next to the Project you just created and select Create dataset.

Create a Dataset ID. We recommend using your domain name (like company.com). If you have multiple stores, we recommend each store to have its own dataset. Most merchants can leave the other default settings as is.

Set Up Permissions

Within Google Cloud, you'll need to set up two different permissions.

Go to IAM & Admin, select your project at the top of the page, then click Grant Access. Enter the following details and click Save.

• New principals: bigcommerce@bc-data-production.iam.gserviceaccount.com
• Role: BigQuery Job User

Go to BigQuery. On the left side of the screen, select your Project and open your Dataset, then click Sharing › Permissions.

Click Add principal and enter the following details.

• New principals: bigcommerce@bc-data-production.iam.gserviceaccount.com
• Role: BigQuery Data Owner

When you're finished click Save. This grants BigCommerce the appropriate permissions to load data into your BigQuery account.

Connect Your Store, Test Access, and Create Table

In your BigCommerce control panel, go to Settings › Data solutions. Under Data warehouses, click Connect next to Google BigQuery.

You can create your account and set up billing now if you haven't already done so. Click Next.

In Step 2: Define dataset, enter your BigQuery Project ID and Dataset ID in the following format, then click Next. Use a period to separate the Project ID and Dataset ID.

• my-project.my_data_set

In Step 3: Grant access, use the Test access button to ensure your project, dataset, and permission settings are configured correctly. Note that if you have not yet set up billing in your Google Cloud account, it will still be in "sandbox" mode and the test may fail.

If you've set up billing and the test still fails:

• Review your Project ID and Dataset ID and ensure they are formatted correctly.
• Review your permission settings and ensure they are applied to the correct Project and Dataset.

Click Next.

In Step 4: Table creation, select your timezone and click Finish.

Note: When selecting your timezone, think about what represents a "day" to you when you're asking analytical questions. For example, if you want to know "how much revenue did I make on Monday", the timezone you set here will determine when Monday begins and ends.

Back in Google Cloud, under BigQuery, you should see the database tables created in the dataset you specified.

Initially, these tables will have the schema defined (the columns and data types are specified) but there will not be any rows. The rows and your actual data will be filled in when the next (in this case, the first) scheduled data processing job runs. You can see this schedule in BigCommerce by going to Settings › Data solutions where the BigQuery status is listed.