BigCommerce to BigQuery 101 Simplified

As your BigCommerce Store starts to grow, it can be challenging to process the exponentially increasing data. Traditional on-premises databases become inefficient in effectively handling the rising data demands. A more economical and stable solution is opting for a Cloud Data Warehouse solution like Google BigQuery that provides On-demand scaling of both the storage and computing resources. 

Using BigCommerce’s native integration, you can easily 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 available in the Google Ecosystem such as Google Data Studio.

In this article, you will learn how to seamlessly start replicating data from BigCommerce to BigQuery.  

What is BigCommerce?

BigCommerce is a leading eCommerce platform that provides a complete package of tools to create and run an online store for your business seamlessly. Launched in 2009, BigCommerce is now assisting more than 60,000 business owners across 150+ countries. With a plethora of features available, you can design and customize your online shop and start selling your products globally in no time. You get complete control over your website and can perform various operations such as adding Products, Uploading Photos, Processing Orders, Creating Discount Coupons, Customizing Product Pages, etc. 

Key Features of BigCommerce

This SaaS platform is employed by various industries like electronics, healthcare products, jewelry, etc. Its huge popularity comes from the following eye-catching features:

• Manageability: From the beginning, when you land on the BigCommerce Dashboard, clear instructions are provided to help you through the basics. Processing orders, managing your inventory, and updating your stock levels are smooth processes. 
• Themes: BigCommerce offers a diverse collection of both free and paid templates. Using the Style Editor, you can customize these themes without any need for writing code. All of these themes are 100% optimized for all mobile devices providing the best customer experience.
• SEO: The Search Engine Optimization functionality of BigCommerce optimizes your website with improved loading speed and better ranks on the search engines. It provides you access to Metadata, Header Tags, Titles, URLs, etc, for customization according to your content.  
• Marketing: With robust marketing platforms such as Mailchimp, HubSpot, iContact, and Constant Contact, you send Automated emails, Coupon codes, Discounts, etc. Through BigCommerce, you can also market on various social media channels such as Facebook and Twitter.
• Customer Support: BigCommerce provides excellent 24/7 customer support via phone, live chat, and email to solve your queries. The subject experts analyze your inquiries and provide the best possible solution in short wait times. By opting for the Enterprise Plan, you get additional features with priority support. To learn more about customer support, you can visit the BigCommerce Support Center. 

What is Google BigQuery?

Launched in 2010, BigQuery is a Cloud-Based Data Warehouse service offered by Google. It is built to handle petabytes of data and can automatically scale as your business flourishes. Developers at Google have designed its architecture to keep the storage and computing resources separate. This makes querying more fluid as you can scale them independently without sacrificing performance. Using standard SQL, you can accurately analyze your data and execute complex queries from multiple users simultaneously.

Key Features of Google BigQuery

Google BigQuery has continuously evolved over the years and is offering some of the most intuitive features :

• On-Demand Storage Scaling: With ever-growing data needs, you can rest assured that it will scale automatically when required. Based on Colossus (Google Global Storage System), it stores data in a columnar format with the ability to directly work on the compressed data without decompressing the files on the go.
• Real-Time Analytics: Stay updated with real-time data transfer and accelerated analytics as BigQuery optimally allocates any number of resources to provide the best performance and results so that you can generate business reports when requested.
• BigQuery ML: Armed with machine learning capabilities, you can effectively design and build data models using existing SQL Commands. This eliminates the need for technical know-how of machine learning and empowers your data analysts to directly evaluate ML models.
• Optimization Tools: To boost your query performance, Google provides BigQuery partitioning and clustering features for faster results. You also change the default datasets and table’s expiration settings for optimal storage costs and usage.   
• Google Environment: Maintained and managed by Google, BigQuery enjoys easy and fluid integrations with various applications present in the Google Ecosystem. With little to no friction at all, you can connect BigQuery to Google Sheets and BigQuery to Google Data Studio for further analysis.

Prerequisites

• This integration is supported for BigCommerce Pro and Enterprise plans only.
• BigCommerce allows only the store owner to access this integration by default. However, you can grant other users access by enabling the Manage Data Warehouses permission.
• Need to 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

Here’s how Hevo, a cloud-based ETL tool, makes BigCommerce to BigQuery data replication ridiculously easy:

Step 1: Configure BigCommerce as a Source

Authenticate and Configure your BigCommerce Source.

Step 2: Configure BigQuery as a Destination

Now, we will configure BigQuery as the destination.

All Done to Setup Your ETL Pipeline

Once your BigCommerce to BigQuery 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.

By employing Hevo to simplify your data integration needs, you get to leverage its salient features:

• Schema Management: With Hevo’s auto schema mapping feature, all your mappings will be automatically detected and managed to the destination schema.
• Fully Managed: You don’t need to dedicate time to building your pipelines. With Hevo’s dashboard, you can monitor all the processes in your pipeline, thus giving you complete control over it.
• Data Transformation: Hevo provides a simple interface to cleanse, modify, and transform your data through drag-and-drop features and Python scripts. It can accommodate multiple use cases with its pre-load and post-load transformation capabilities.
• Faster Insight Generation: Hevo offers near real-time data replication, so you have access to real-time insight generation and faster decision-making. 

Method 2: Replicate data from BigCommerce to BigQuery Manually

You can start sending your data from BigCommerce to BigQuery manually by following the simple steps given below: 

Step 1: Create a Project. 

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

Step 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 on the Create Dataset Option and create a Dataset ID, for instance, using your domain name (like company.com).
• Click on the Navigation icon at the top left corner of your screen. Scroll down to the Big Data section and click on the BigQuery option. 

Step 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 on the Add button. You can provide the following information.
  • New principals: bigcommerce@bc-data-production.iam.gserviceaccount.com
  • Role: BigQuery Job User

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

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

Once you have completed the above steps for setting up data transfer from BigCommerce to BigQuery, click on the Save Button. The above permissions enable you to start transferring data from BigCommerce to BigQuery account.

Step 4: Connecting to BigQuery Data Warehouse 

• Go to your BigCommerce control panel and navigate to Advanced Settings › Data Solutions. Now, click on the Connect button present next to BigQuery under the Data Warehouses section.

• Enter details of the project you created earlier for replicating data from BigCommerce to BigQuery i.e. your BigQuery Project ID and Dataset ID in the following format & then click on the Next option.
  • Project_name:dataset_name

Step 5: Testing and Validation 

• Check if your project, dataset, and permission settings are configured correctly via the Test access button. 
• In case your billing in your Google Cloud account is not set up, the test will be conducted in the “sandbox” mode, and the test may fail.
• In case your test for the data loading process from BigCommerce to BigQuery fails in spite of the billing being already set up, you can do the following two things to rectify it and then click on 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 6: Final Set Up 

• Finally for completing the process to replicate data from BigCommerce to BigQuery, select your timezone and click on the Finish button. 

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

Why Connect BigCommerce to BigQuery?

When sending your data from BigCommerce to BigQuery, BigCommerce supports a native integration into Google BigQuery that enables advanced analytics and custom reporting. Connecting BigCommerce to BigQuery allows you to leverage the following benefits:

• Single Source of Truth: BigQuery acts as a Central Repository for all your data coming from different sources and swiftly analyzes massive volumes of data. 
• Custom Reporting: With advanced analytics at your disposal, you can make custom reports such as “Revenue by Product Category”, “Revenue by Product Brand”, “Cost of Goods Sold”, and “Profit Margin”. You can also filter out your revenue by country, city, postal code, and more.
• Standard SQL: Once you send your data from BigCommerce 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. 

Conclusion 

In this article, you have learned how to easily connect BigCommerce to BigQuery in easy-to-follow steps. BigCommerce’s native integration allows you to seamlessly transfer data from BigCommerce to BigQuery, allowing you to leverage 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 your data across several applications and databases in your business, it is important to consolidate it for a complete performance analysis of your business. To achieve this efficiently, you need to assign a portion of your engineering bandwidth to Integrate data from all sources, Clean & Transform it, and finally, Load it to a Cloud Data Warehouse, or a destination of your choice for further Business Analytics. All of these challenges can be comfortably solved by a Cloud-based ETL tool such as Hevo Data.   

Want to take Hevo for a ride? Sign Up for a 14-day free trial and simplify your Data Integration process. Do check out the pricing details to understand which plan fulfills all your business needs.

Share your experience of connecting BigCommerce to BigQuery! Let us know in the comments section below!

FAQs