How to Efficiently Migrate Data from Shopify to BigQuery?

You have your complete E-Commerce store set up on Shopify. You Collect data on the orders placed, Carts abandoned, Products viewed, and so on. You now want to move all of this data on Shopify to a robust Data Warehouse such as Google BigQuery so that you can combine this information with data from many other sources and gain deep insights. Well, you have landed on the right blog.

This blog will discuss 2 step-by-step methods for moving data from Shopify to BigQuery for analytics. First, it will provide a brief introduction to Shopify and BigQuery. Afterward, the blog will explain the 2 methods of setting up your Shopify BigQuery integration. Read along to find out which method is the best for you!

What is Shopify?

Shopify is the e-commerce platform to run a business that allows businesses to quickly and easily set up and manage online stores. During this process, it provides all-in-one tools for setting up an online shop with free customisable templates, processing payments, and managing inventory. Shopify has established itself on the basis of its ease of use, large app ecosystem, and solid roots in small and large-scale operations.

Use Cases of Shopify

• Dropshipping: It helps dropshipping businesses by integrating with a lot of suppliers and automatising order fulfillment without holding any inventory.
• Multi-Channel Selling: It enables multichannel selling, spanning far beyond social networks to marketplace and even physical locations, with seamless integrations.
• Customizable Solutions: It provides customization options through a great variety of apps and themes so that your business can make its online presence and functions as appropriate.

What is Google BigQuery?

BigQuery is Google’s cloud enterprise data warehouse that primarily serves business agility in running complex SQL queries and performing analysis on huge datasets efficiently. It is based on Google technology called Dremel, using columnar storage and tree architecture to support high-speed scanning of data for querying efficiency.

BigQuery is serverless, highly scalable, and enlists Google’s infrastructure in the cloud for management. It uses access patterns to maintain efficiency in storage by dynamically optimising data structures for the handling of data in a changing environment for optimal performance.

Use Cases of BigQuery

Let us discuss some of the key use cases of Google BigQuery.

• Business Intelligence (BI): It can integrate with popular BI tools like Looker, Tableau, and Data Studio for in-depth reporting, visualization, and dashboard creation, helping you make data-driven decisions.
• Data Transformation and Cleaning: It facilitates ETL processes for cleaning, transforming, and making the data ready for any analysis; hence, assist in tasks related to data wrangling and preparation.

Want to learn more about BigQuery and how it is used? Check out our Google BigQuery Tutorial for a detailed guide.

Why Do Businesses Need to Replicate Shopify to Google BigQuery?

• You can integrate Shopify data—on sales, customers, and products—with all other business data in one place for complete analysis for you to derive better insights.
• You can get insights into sales trends, customer behaviour, and inventory management with BigQuery’s key analytics capabilities.
• You can handle large volumes of data efficiently and scale analytics capabilities as and when your business needs grow.

Methods to Set Up Shopify to BigQuery Integration

The following 2 methods can be used to transfer data from Shopify to Bigquery :

Prerequisites

To successfully load your data from Shopify to BigQuery, you need to meet the following requirements:

• A Shopify account.
• A BigQuery account.
• Working knowledge of RESTful APIs.
• A clear idea of what data you need to transfer.

Method 1: Using Hevo Data to Set Up Shopify to BigQuery Integration in just 2 Steps

Step 1: Configure Shopify as your Source.

To learn more about connecting your Shopify account to Hevo, visit Hevo’s docs on Shopify.

Step 2: Configuring BigQuery as Destination

To learn more about connecting your BigQuery account to Hevo, visit Hevo’s docs on BigQuery.

Method 2: Using a Custom Code to Set Up Shopify to BigQuery Integration

This approach requires you to custom code the ETL process manually by using various API calls. The whole process of transferring data from Shopify to BigQuery can be completed using the following 3 steps:

• Step 1: Pulling Data from Shopify using API Calls
• Step 2: Modifying the Extracted Data
• Step 3: Loading Data into BigQuery

Step 1: Pulling Data from Shopify using API Calls

Shopify exposes a wide set of RESTful APIs to its customers to extract data on orders, cart updates, inventory status, customers, and more. You can explore the complete set of APIs here.

Some things to note when using Shopify APIs: 

1. Since Shopify allows you to extract a wide variety of data sets, you would need to first list down the data points you would need and learn about the APIs that will help you to extract this data
2. To make a Shopify API call, you would need to provide API Authentication. This can be done by generating a username and password using the Shopify admin account.

Once you have these pre-requisites addressed, initiating an API call to extract product data from Shopify would look like this: 

curl --user user:password GET /admin/api/2019-10/products.json?limit=100

Shopify imposes limits on the amount of data that it sends out in a single API call. The default pagination limit is 50 if the limit parameter is not given. For the API call made above 250-page results will be shared. 

If you need to extract more data, then you would need to store the ID value of the last product that the API returned and then use this id to get the next data set. 

This is done as below.

curl --user user:password GET /admin/api/2019-10/products.json?limit=100&since_id=632910392 -o products.json

Where since_id is the last product id that was received on the previous API call. 

Once you have successfully executed this API call, you will now receive a response in JSON format. This is a nested JSON that contains all the information related to the products such as title, description, images, etc., and more importantly, the variants sub JSON which provides all the variant-specific information like inventory_quantity,  barcode, product price, etc.

Step 2: Modifying the Extracted Data

Now that you have extracted the data from Shopify, you will next need to load it to BigQuery. 

You can load data into BigQuery directly using API call or can upload the CSV / JSON file generated in the previous step using BigQuery UI.

Before you move data to BigQuery, make sure that the data types returned by the API response are supported by BigQuery. You can read more about BigQuery data types and conversions here.

Step 3: Loading Data into BigQuery

The 3rd and final step involves loading the modified data into Bigquery. This can be done as follows:

1. Upload the prepared data to Google Cloud Storage (using Gsutil or the Web Console)
2. Create a BigQuery table to store this data
3. Load to BigQuery from your GCS bucket using BigQuery’s command-line tool (bq) or any cloud SDK. Read more about bq commands here.

To learn more about loading data into BigQuery, read through this guide.

Limitations of the above approach:

1. Effort Intensive: Using code to move data from Shopify to BigQuery using custom code requires you to learn and bring together many different technologies. Given the learning curve involved, your data projects’ timelines can be affected.
2. Not Real-time: The process mentioned above does not help you bring data in real-time. You would have to configure a cron job and write extra code to bring data in real-time.
3. Data Consistency: Since you are extracting business-critical data out of Shopify, you would need to set up a separate process to validate the data received in BigQuery. Any inconsistency would negatively impact the decisions you may take as a business.
4. Cannot Transform Data: Many times, you would encounter use cases where you need to convert currencies or standardize time zones to perform efficient analytics. The above approach does not cover that.
5. Maintainance Overhead: If Shopify makes any changes on the API or say Google Bigquery is down, any break in your script will result in irretrievable data loss. Hence, you would need to constantly monitor and maintain the data

Before wrapping up, let’s cover some basics.

What Data can you move from Shopify to BigQuery?

Shopify generates abundant data. Here is a sample list of data that you can load from Shopify to BigQuery to further work on it:

• Orders: The orders data contains information about orders placed, order id, number of orders, etc. You can use this data to track your sales over time, & quickly look into the customer details.
• Products: Using your products data, you can add, delete, or modify any items in your to keep it up to date in your BigQuery. In addition, you can calculate your store’s profitability by retrieving product details or querying the database for any other condition.
• Checkouts: The information in this table pertains to abandoned checkouts. Checkouts that have been abandoned are those where the client has provided their billing and shipping information but has not finished the purchasing/ordering procedure.
• Transactions: This table includes transactional information. Every order that results in a monetary exchange generates a transaction in Shopify such as authorization, sale, refund, etc.
• Customers: Using the customer’s data, you can effortlessly get the list of your clients and segment them. Furthermore, you can remove duplicates by comparing Shopify data to those in your accounting or marketing platforms and the contacts can be easily loaded into 3rd-party tools to use them further.

What are the Data Format Requirements before Exporting Data from Shopify to BigQuery?

Before you load data from Shopify data to BigQuery, make sure it’s in a format it can comprehend. For example, if the API you’re using returns XML, you’ll need to convert it to a serialization BigQuery can understand first. There are now two data formats supported:

• CSV, as well as
• JSON

You must also ensure that the data types you are utilizing are those that BigQuery supports, which include the following:

• STRING
• INTEGER
• FLOAT
• BOOLEAN
• RECORD
• TIMESTAMP

Conclusion

The blog provided a brief introduction to Shopify and BigQuery and explained 2 step-by-step methods for connecting Shopify to BigQuery and transferring your data easily. The first method to set up Shopify to BigQuery integration involves manually building custom code using RESTful APIs. This method has certain limitations, which were discussed in the previous section.

Implementing Hevo Data can avoid the challenges of the first method. Sign up for our 14-day free trial and seamlessly migrate your data from Shopify to BigQuery.

FAQs to integrate Shopify with BigQuery