Shopify to BigQuery: 2 Easy Methods

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!

Methods to Set Up Shopify to BigQuery Integration

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

Method 1: Using Hevo Data to Set Up Shopify to BigQuery Integration

Hevo Data, a No-code Data Pipeline, helps you directly transfer data from Shopify and 150+ other data sources to Data Warehouses such as BigQuery, Redshift, Databases, BI tools, or a destination of your choice in a completely hassle-free & automated manner. Hevo is the only real-time ELT No-code data pipeline platform that cost-effectively automates data pipelines that are flexible to your needs.

Sign up here for a 14-Day Free Trial!

Hevo Data takes care of all your Data Preprocessing needs and lets you focus on key business activities and draw a much powerful insight on how to generate more leads, retain customers, and take your business to new heights of profitability. Hevo can set up your Shopify BigQuery integration process in the following 2 steps:

• Configure Shopify as Data Source: In this step, you need to connect your Shopify account with Hevo which will then set up a data pipeline for your data transfer process. Moreover, you can use the automapping feature to automate the schema management process as shown in the below image.

To learn more about connecting your Shopify account to Hevo, visit here.

• Configuring BigQuery as Destination: You need to connect your BigQuery account to Hevo as the destination for your data transfer process. This is shown in the below image.

To learn more about connecting your BigQuery account to Hevo, visit here.

Hevo takes care of automatically mapping all your data to relevant tables in BigQuery, giving you access to Shopify data in real-time. Sign up for a risk-free 14-day free trial with Hevo and experience a hassle-free data migration from Shopify to BigQuery.

Additionally, Hevo helps you clean, transform and enrich data both before and after moving the data warehouse, ensuring that you have analysis-ready data in your warehouse at any point.

The Advantages of Using Hevo

Hevo automates your Shopify to BigQuery process and also provides the following benefits :

• Simplicity: Hevo is super intuitive, super simple to use. With Hevo, you can start loading data from Shopify to BigQuery in just a few clicks.
• Real-time Data: Hevo’s real-time streaming architecture ensures that you move data from Shopify to BigQuery instantly, without any delay. This allows you to gain meaningful insights in real-time.
• Reliable Data Load: Hevo’s fault-tolerant architecture ensures that your data is loaded consistently and reliably without any loss of data.
• Scalability: Hevo is built to handle data of any scale. Additionally, Hevo can bring data from a multitude of data sources. Both these aspects ensure that Hevo can help you scale your data infrastructure as your data needs grow.

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.

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.

What is Shopify?

With the advent of the internet and cloud, Shopify has emerged as one of the easiest ways for individuals and businesses to set up an online eCommerce store. Shopify liberates the user of all the technical complexities and development effort making it super simple to set up a business online. Shopify has a range of products that handle everything from landing page set up to, payments to reporting. 

Although Shopify gives out a range of analytics reports, it is not always easy to unify Shopify data with the other data that a business may have. Eg: Advertising data from Facebook Ads or Google Ads might have to be combined with Shopify orders to understand the true ROI from these campaigns. That is where the need to move data from Shopify to BigQuery arises. 

To learn more about Shopify, visit here.

What is Google BigQuery?

Google BigQuery, a Cloud-based Data Warehouse solution offered by Google allows users to build SQL-like queries to answer deep analytical questions. BigQuery speed of execution combined with its pay for what you use payment model makes it a lucrative option for modern businesses that want to run analytics on large data sets. 

Businesses often tend to load data from different data sources – Shopify for eCommerce, Facebook/Google Ads for Advertising, Intercom for Support, and more to BigQuery and in turn answer deeper analytical questions by querying this data. 

To learn more about Google BigQuery visit here.

Why is Shopify BigQuery Integration needed?

Organizations aggregate all of their data from Shopify and other applications and tools into a data warehouse like Google BigQuery to quickly analyze and create reports. Let’s look at other reasons that push businesses to move data from Shopify to BigQuery:

• Centralize Data from various Sources: You can use Shopify to BigQuery integration to gather data from all of your Sales or other channels in one location. Leveraging BigQuery’s capabilities, you can swiftly extract the data you want and keep a track of all details about your items and consumers. Moreover, you can keep an eye on fulfillment and stock levels, and respond quickly if anything goes wrong.
• Keep a Data Backup: Keeping a Shopify Data Backup in Google BigQuery can help protect your data in the event of a Shopify outage or data loss. In addition, this helps you to export important information about your orders, products, or customers and keep it secure and accessible to you at all times.
• Detailed Analytics & Visualizations with BI Tools: Loading data from Shopify to BigQuery and then into your chosen BI tools like Tableau or Data Studio will help you to gain important insights and ease your decision-making process. You can create complex dashboards automatically and update them on a set schedule while also keeping track of your company’s critical KPIs and metrics.

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. It also explained 2 step by step methods by which you can connect Shopify to BigQuery and transfer 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.

Visit our Website to Explore Hevo

The challenges of the first method can be avoided by implementing Hevo Data, which can automate the data transfer process using its No-code Data Pipeline to load data from Shopify to BigQuery. Hevo provides a point and clicks interface that will allow you to move data without writing any code. Since Hevo is fully managed, the setup time is minimal and you will have your data from Shopify to BigQuery in minutes. It helps transfer data from Shopify to a destination of your choice for free.

Sign Up for a 14-day free trial to experience a hassle-free data load from Shopify to Google BigQuery.

Share your experience of connecting Shopify to BigQuery in the comments section!