How to Connect BigCommerce to BigQuery: Easy Steps Explained

As your BigCommerce store grows, processing the exponentially increasing data can be challenging. Traditional on-premises databases have become inefficient at handling the rising data demands. A more economical and stable solution is a cloud data warehouse solution like Google BigQuery, which provides on-demand scaling of storage and computing resources. 

Using BigCommerce’s native integration, you can quickly transfer data from BigCommerce to BigQuery and take full advantage of its advanced & powerful analytics engine. This also allows you to leverage other popular data services in the Google Ecosystem, such as Google Data Studio.

This article will teach you how to start replicating data and perform BigCommerce BigQuery integration seamlessly.  

What Is BigCommerce?

BigCommerce is a top eCommerce platform helping 60,000+ businesses across 150+ countries. It offers tools to design, customize, and manage online stores, including adding products, processing orders, and creating discounts. 

Key Features of BigCommerce

BigCommerce is popular across industries like electronics, healthcare, and jewelry due to its features:

• Manageability: Easy inventory and order management via a user-friendly dashboard.
• Themes: Customizable, mobile-optimized templates—no coding needed.
• SEO: Tools to boost website speed and search engine ranking.
• Marketing: Integrates with platforms like Mailchimp and supports social media marketing.
• Customer Support: 24/7 support via phone, chat, and email, with priority support on Enterprise plans.

What Is Google BigQuery?

BigQuery, launched in 2010 by Google, is a cloud-based data warehouse that scales automatically. It separates storage and computing, ensuring smooth performance. Using standard SQL, it handles petabytes of data and supports complex queries from multiple users simultaneously.

Key Features of Google BigQuery

• Scalable Storage: Automatically scales with growing data, storing it in a compressed columnar format for efficiency.
• Real-Time Analytics: Allocates resources for fast analytics and report generation.
• BigQuery ML: Build ML models using SQL easily without the necessity of advanced skills.
• Optimization Tools: Features like partitioning and clustering enhance query performance and cost efficiency.
• Google Integration: Seamlessly connects with Google tools like Sheets and Data Studio.

Prerequisites

• This integration is supported only for BigCommerce Pro and enterprise plans.
• By default, BigCommerce allows only the store owner to access this integration. However, you can grant other users access by enabling the “manage data warehouses” permission.
• You must sign up for a Google Cloud account and set up the billing.

How to Connect BigCommerce to BigQuery?

Method 1: Replicate Data From BigCommerce to BigQuery Using an Automated ETL Tool

Step 1.1: Configure BigCommerce As Your Source

Step 1.2: Configure BigQuery As Your Destination

That is all you need to do. Now, you can sit back and relax while your data automatically moves from BigCommers into BigQuery.

Once your ETL Pipeline is configured, Hevo will collect new and updated data from BigCommerce every five minutes (the default pipeline frequency) and duplicate it into BigQuery.

Method 2: Replicate Data From BigCommerce to BigQuery Manually

Step 2.1: Create a Project

• Click “Navigation Icon” in the top left of your Google Cloud Console Window. Navigate to “IAM & Admin › Manage Resources” & click “Create Project.” You can now fill out a project name at your convenience to replicate data.

Step 2.2: Create a BigQuery Dataset

• Go to the left side screen, and click on the” ⠇” option present next to the Project created in the above steps. Click the “Create Dataset” option and create a dataset ID, for instance, using your domain name (like company.com).
• Click the “Navigation icon” at the top left corner of your screen. Scroll to the “Big Data section” and click “BigQuery.”

Step 2.3: Set Up the Permissions for Your Google Cloud Account

• Navigate to “IAM & Admin,” choose your project present at the top of the page, and finally, click the “Add button.” Provide the following information:
  • New Principals: bigcommerce@bc-data-production.iam.gserviceaccount.com
  • Role: BigQuery Job User

• Navigate to BigQuery, choose your Project, and open your Dataset. Go to “Sharing › Permissions.”

• Now, click the “Add Principal” button and add the following information for the data transfer:
  • New Principals: bigcommerce@bc-data-production.iam.gserviceaccount.com
  • Role: BigQuery Data Owner

Once you have completed the above steps to set up data transfer, click the “Save Button.” The above permissions enable you to transfer data to your BigQuery account.

Step 2.4: Connecting to BigQuery Data Warehouse 

• Go to your BigCommerce control panel and navigate “Advanced Settings › Data Solutions.” Click the “Connect” button next to BigQuery under the “Data Warehouses” section.

• Enter details of the project you created earlier for replicating data, i.e., your BigQuery Project ID and Dataset ID, in the following format & click the “Next” option.
  • Project_name: dataset_name

Step 2.5: Testing and Validation 

• Check if the “Test access” button correctly configures your project, dataset, and permission settings. 
• If your billing in your Google Cloud account is not set up, the test will be conducted in the “sandbox” mode and may fail.
• In case your test for the data loading process fails despite the billing being already set up, you can do the following two things to rectify it and then click the “Next” option:
  • 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.

Step 2.6: Final Set Up 

• To complete the process of replicating data, select your timezone and click the “Finish” button. 

For those considering alternative data management options, explore how BigCommerce integrates with MySQL. This could be another viable approach for effectively managing and analyzing your eCommerce data.

Why Connect BigCommerce to BigQuery?

When sending your data from BigCommerce to a data warehouse, BigCommerce supports a native integration into Google BigQuery that enables advanced analytics and custom reporting.

• Single Source of Truth: BigQuery acts as a central repository for all your data from different sources and swiftly analyzes massive volumes of data. 
• Custom Reporting: Advanced analytics allow you to create custom reports such as “Revenue by Product Category,” “Revenue by Product Brand,” “Cost of Goods Sold,” and “Profit Margin.” You can filter your revenue by country, city, postal code, etc.
• Standard SQL: Once you send your data to BigQuery, you can write SQL queries to analyze your data.
• Business Intelligence: To visualize your data better, you can simply connect to various BI Tools such as Google Data Studio, Google Looker, Tableau, and Microsoft Power BI.

You can also explore how you can connect your BigCommerce data to other data warehouses such as Databricks.

Conclusion 

In this article, you learned how to connect BigCommerce to BigQuery in easy-to-follow steps easily. BigCommerce’s native integration allows you to seamlessly transfer data to BigQuery, leveraging advanced analytics and custom reporting capabilities. BigQuery enables you to execute standard SQL commands to analyze massive volumes of data efficiently. 

As you collect and manage data across several business applications and databases, it is important to consolidate it for a complete performance analysis. To achieve this efficiently, you need to allocate a portion of your engineering bandwidth to integrate data from all sources, clean and transform it, and finally, load it to a cloud data warehouse or a destination of your choice for further business analytics. A cloud-based ETL tool such as Hevo Data can comfortably solve these challenges.   

Want to try Hevo out? Sign up for a 14-day free trial to simplify your Data Integration process. Then, review the pricing details to determine which plan best meets your business needs.  

FAQs