While Shopify offers incredible management for online stores, it lacks comprehensive reporting on shop performance. Therefore, it makes sense to connect Shopify to BigQuery, which offers advanced analytics capabilities and enables you to establish a single source of truth for all your data, including marketing, sales, finance, and more. Having data from various sources in a unified location is an ideal solution for creating multi and cross-channel reports. In this tutorial, we explore the practical methods for sending Shopify data into BigQuery.
Ways to move data from Shopify to BigQuery
- Coupler.io – a reporting automation platform designed to transform your data into valuable insights for enhancing your e-commerce store strategy. This tool allows you to connect Shopify to BigQuery and provides connectors for over 60 other data sources. With Coupler.io, you can also blend data from several Shopify stores or other apps and turn it into a report on the go. In addition to BigQuery, it supports spreadsheets, BI tools, and other data warehouses as destinations for your Shopify data. All of this is available for users without any technical or coding background!
- Manual integration involves data extraction from Shopify into a CSV file, followed by its upload into a BigQuery table.
- API integration is an approach for tech-savvy users since it requires using the Shopify API and the creation of a custom integration to fetch data and load it into BigQuery. However, you can still do it without coding here if you use the JSON/API connector by Coupler.io, which allows you to integrate Shopify to BigQuery in a custom way. However, you still need to perform API configurations on the Shopify side.
Method 1: Connect Shopify to BigQuery with Coupler.io
Coupler.io is designed to optimize your daily business workflows and optimize all the Shopify to BigQuery integration processes by setting schedules for regular data transfers. The overall setup will take you less than 5 minutes!
To start setting up the integration, click Proceed on the widget below. Shopify is set as a source, and BigQuery is selected as a target system for your convenience!
You’ll be offered to get started with Coupler.io for free with no credit card required.
Step 1. Collect Shopify data
The first step of the Shopify to BigQuery integration is to connect to your e-store account and grant Coupler.io access. For that, enter your online shop address into the Source account field and proceed with the on-screen instructions to connect it to Coupler.io.
Select the data entities you want to extract from your Shopify e-commerce store. Coupler.io allows you to get Inventory, Customers, Products, and Order items.
Specify additional parameters if you need to retrieve items with a particular status, creation date, etc.
During this initial step, you can add other data sources, including another Shopify e-store. For that, use the Add a few more sources search bar.
Select Shopify from the list and repeat the same procedure described above (connection, data entity selection, etc.).
Step 2. Transform data
Once you proceed to the transformation stage, Coupler.io will generate a preview of the selected data. Here, you can perform various operations on the obtained dataset, including:
- Sort and filter dataset values based on the specified criteria
- Add, delete, rename, and reorder columns
- Create custom formulas for proprietary metrics
- Quickly summarize and reveal key insights of your data by performing operations like sum, average, count, min, or max on specific columns
- Blend data if you’ve connected multiple sources, be it various Shopify stores or different applications
Step 3. Send data to BigQuery and schedule refresh
Finally, it’s time to integrate Shopify to BigQuery and send the store data to it. First, connect your Google BigQuery project using the Google Cloud JSON key file.
If you’ve connected multiple data sources, you’ll also need to select which data you want to import to BigQuery under the Data to share section.
Select the dataset and table where you want to import Shopify data. Enable the Autodetect table schema option for convenience.
Select one of the import modes:
- Replace for substituting the existing data with the new one
- Append for adding the new data to the existing one
Enable or disable the Last update column options and Update data each run option as necessary.
Take advantage of the scheduling feature to keep BigQuery tables updated with fresh Shopify data. It allows you to select either time intervals for refreshes or set specific times for data transfers.
To start the Shopify to BigQuery integration, click SAVE AND RUN.
You may also consider postponing the start of the data transfer if something still needs to be changed in the importer settings by clicking SAVE AND CLOSE.
See how fast and smooth Coupler.io can connect Shopify to BigQuery. With this tool, any integration scenario becomes a matter of minutes for the initial setup and a hands-free experience for data refreshes.
With Coupler.io, many daily operations can be optimized and enhanced, resulting in:
- More convenience for report creation
- Automated backups
- Always fresh data in your single source of truth based on BigQuery
Send your Shopify data to Google BigQuery with ease!
Method 2: Integrate Shopify to BigQuery via the Shopify API
While Coupler.io is based on the Shopify API, it extracts the most popular and widely used e-store items. If you need to go beyond these options, moving data from Shopify to BigQuery is possible by creating a custom integration with the Shopify API. This can be done either with custom coding, which requires a deep understanding of programming interfaces and languages, or by using the JSON API connectors provided by Coupler.io.
Get Shopify API token
In your Shopify account, click Apps and select App and sales channel settings.
A warning message will appear if you have not yet developed apps in your Shopify store. Click Allow custom app development to continue.
Click Create app and provide a name for this new app in the window that appears.
Then, click Configure Admin API scopes.
- Customers –
read_customers - Inventory –
read_inventory - Orders –
read_orders - Products –
read_products
Once everything is done, click Save.
Afterwards, you will find your Shopify API token in the Admin API section.
Export Shopify data with JSON API connector
To avoid complex scripts and hours of coding and script creation for Shopify to BigQuery integration, use the JSON API connector provided by Coupler.io to accelerate the process. To do so, click Proceed in the widget below to create the importer. We have already pre-selected BigQuery as a destination app.
In the JSON settings, fill in the required fields:
- URL –
https://{shop}.myshopify.com/api/{api_version}/graphql.json{shop}– insert your store name, which you can find{api_version}– insert the actual API version
The store name can be found in the URL address of your Shopify account – the last part of the address string, as shown in the screenshot below:
So, the sample URL string will look as follows:
https://916e84-97.myshopify.com/api/2024-07/graphql.json
- HTTP – pick up the required REST API method.
- GET – to retrieve a resource item
- PUT – to update an existing resource item
- POST – to create a new resource item
- DELETE – to delete a resource item
NOTE: Since retrieving data in this case is necessary, select the GET option from the HTTP method drop-down list.
- Request headers – specify your API access token using the following HTTP header.
X-Shopify-Access-Token:{your-api-access-token}
NOTE: You can find your Shopify access token in the Admin panel of your e-commerce store. Go to Apps on the left panel and select the recently created app to retrieve the token.
- URL query parameters – specify query parameters to narrow your search results (optional).
- Columns – provide the names of columns separated by a comma.
- Path – Enter the string to nested objects if any (optional).
The next two steps are similar to those described in the integration method above.
- Check the preview generated by Coupler.io and decide how to organize and manage data using the available options.
- Once ready, provide your BigQuery destination details to import Shopify data.
Method 3: Send Shopify data to Google BigQuery manually
The manual method doesn’t involve any additional tools for data transfer. It consists of two principal steps:
- Extracting Shopify data into a CSV file.
- Adding data from the file to Google BigQuery tables.
Step 1. Download a CSV file from Shopify
- Log in to your Shopify account by providing the necessary credentials.
- Go to the section from which you would like to export items. In our example, we extract the Products data.
- Click on Export in the upper part of the screen and specify the data extraction options.
- Once you click Export products, the file will arrive in the email box associated with your Shopify account. Click on that email and download the CSV file to your computer.
You can carry out the same procedure for data extraction from your other e-stores.
Step 2: Upload data from CSV to BigQuery
- Go to the Google Cloud Console and select BigQuery from the panel on the left.
- Create a new project or select an existing one. Make sure you have the necessary dataset ready for Shopify data import. If not, you can create one later in the file upload stage.
- Click +ADD and select the Local file options.
- In the window that appears, select the CSV file from your computer. Then, specify the project and dataset where the new table with Shopify needs to be added. Enter the table name and check the Auto detect option.
- Check the provided details once again and click Create table.
- In the list of tables, click on the needed one and go to the PREVIEW tab.
What data can be exported from Shopify to BigQuery?
Even though Shopify comprises a large quantity of heterogeneous data, not all of it can be exported, at least not with each integration method. For instance, the information on products, orders, inventory, and customers can easily be retrieved using Coupler.io. The same data and several other items (gift cards, discount codes, abandoned checkouts, etc.) can be extracted into a CSV file. Meanwhile, any data can be pulled using Shopify REST APIs.
Please find a detailed breakdown of Shopify items and their availability for extraction with each integration method mentioned above.
| Coupler.io | CSV | REST API | |
| Products | ? | ? | ? |
| Product collects | ? | ||
| Product collections | ? | ||
| Product variants | ? | ||
| Product smart collections | ? | ||
| Gift cards | ? | ? | |
| Inventory items | ? | ? | ? |
| Inventory levels | ? | ||
| Orders | ? | ? | ? |
| Abandoned checkouts | ? | ? | |
| Refunds | ? | ||
| Transactions | ? | ||
| Order risks | ? | ||
| Customers | ? | ? | ? |
| Discount codes | ? | ? | |
| Price rules | ? | ||
| Reports | ? | ? | |
| Metafields | ? | ||
| Articles | ? | ||
| Blogs | ? | ||
| Comments | ? | ||
| Pages | ? | ||
| Payments | ? | ||
| Disputes | ? | ||
| Balance | ? | ||
| Payouts | ? | ||
| Transactions | ? |
Ready-to-use dashboards to analyze your Shopify data
Google BigQuery is often perceived as a solid foundation for Shopify analytics and other advanced operations with data, but it doesn’t allow users to create visual reports and dashboards. Therefore, using BI and data visualization tools for reporting makes sense.
Coupler.io can connect to popular BI solutions, such as Power BI, Looker Studio, and Tableau. It also provides multiple pre-made dashboards for these tools. This lets you bypass data loading into BigQuery by quickly collecting data from Shopify and other sources and displaying it in your preferred reporting system.
Let’s look at some of the most popular reporting dashboard templates.
Shopify orders dashboard
This dashboard lets you track orders and sales based on the extracted Shopify data.
What insights does the dashboard provide?
- Regions where your products are the most popular and lots of sales take place, allowing you to retarget your sales strategy.
- Geographical locations with the highest number of refunded and pending orders help you detect problematic regions and start looking for an appropriate solution.
- Sources from which most sales orders result, allowing you to concentrate marketing strategies on the best-performing ones
- The top-selling items in the e-store help keep popular products in stock.
- Purchasing habits and dynamics of new or returning customers enable you to enhance your customer care approaches.
How to use it
This dashboard template is designed for use in Looker Studio and Google Sheets. Thanks to the pre-built Coupler.io connector, the information about your orders will be pulled from Shopify on a schedule and added to the report. To start using this template, follow the step-by-step instructions in the Readme tab.
Shopify marketing funnel dashboard
This dashboard provides information about the marketing funnel effectiveness of your Shopify store. It also connects to GA4 and popular ad platforms, such as Instagram Ads, Google Ads, etc., to get data about advertising campaigns.
What insights the dashboard provides
- KPIs, including ROI, average order amount, and many others, allow you to evaluate the current acquisition funnel performance.
- The weekly data for impressions, clicks, total users, and new users show how spending translates into sales, plus the ratio of costs spent on advertising to sales, allowing you to make data-driven decisions on boosting your store’s performance.
- Expense tracking for each advertising platform is done to avoid budget exceedance.
- The dynamics of PPC campaigns, website traffic, and Shopify store customers to compare results over time.
How to use it
This dashboard is designed for use in Looker Studio as a template equipped with a Coupler.io connector. It automates data extraction from Shopify, GA4, and ad platforms and loads it into the dashboard on a schedule. To start using this template, follow the guidelines provided in the Readme tab.
Shopify inventory dashboard
This Shopify dashboard allows you to track the inventory and order details from your e-commerce platform. It provides a clear overview of your product stock levels, order patterns, and return rates across all locations.
What insights the dashboard provides
- Detailed inventory observation with characteristics such as stock value, availability, average daily units sold, etc., allowing you to replenish your stock in a timely manner.
- Information on the return rates by country gives you an idea of the geographical areas with elevated refund levels.
- Sales monitoring in real-time allows you to explore the purchase items and peak sales times.
- Stock availability by city and country lets you compare stock across all your warehouse locations to optimize product distribution and prevent stockouts.
How to use it?
This dashboard template was created in Looker Studio. You can freely use it to populate your reports with always up-to-date data from Shopify using the built-in Coupler.io connector. Check the Readme tab for detailed guidelines on how to configure and use this template.
Refresher: Prepare a project in BigQuery before integration
Before you send your Shopify data to Google BigQuery, some preliminary steps need to be taken. This set of guidelines is particularly useful for those who are new to Google BigQuery and need to prepare storage requirements beforehand.
In your Google Cloud Console, select BigQuery from the left menu panel.
Click My First Project next to the Google Cloud sign in the upper panel and click New project.
Fill in the required fields or accept the automatic suggestions and click Create.
The newly created project appears in the Explorer panel.
Beyond the integration between Shopify and BigQuery
Real-world enterprise technological ecosystems often contain dozens of tools. Consequently, organizations often operate several e-commerce platforms, including Shopify, WooCommerce, BigCommerce, etc., along with other systems, the data from which also needs to be added to BigQuery. Instead of building separate data pipelines for each integration flow, consider using Coupler.io.
This platform can gather information from over 70 data systems, SaaS, apps, flat files, and other tools and send it to the same destination at once. All this requires less than 5 minutes and no coding experience, enabling you to analyze e-commerce experiences and efforts immediately!
Automate Shopify reporting with Coupler.io
Get started for free