How to Automate Shopify Sales Reporting with Google BigQuery

Hey there! If you’re looking to automate your Shopify sales reporting by loading daily orders into Google BigQuery for dashboarding, you’re in the right place. In this guide, I’ll walk you through setting up an automated workflow using Make (formerly Integromat), Shopify, Google BigQuery, and Google Sheets. This setup should take about an hour and is geared towards those with an intermediate skill level.

Introduction

Manually exporting Shopify sales data and importing it into BigQuery can be time-consuming and prone to errors. By automating this process, you can ensure that your sales data is consistently updated in BigQuery, allowing for real-time dashboarding and analysis. We’ll use Make to create a workflow that pulls daily orders from Shopify, processes the data in Google Sheets, and then loads it into BigQuery.

Step-by-Step Guide

Step 1: Set Up Your Google BigQuery Environment

Before we dive into Make, let’s ensure your BigQuery environment is ready.

1. Create a Google Cloud Project: If you haven’t already, create a new project in the Google Cloud Console.
2. Enable BigQuery API: Navigate to the “APIs & Services” section and enable the BigQuery API for your project.
3. Create a Dataset and Table: In the BigQuery console, create a new dataset (e.g., shopify_sales) and within it, create a table (e.g., daily_orders) with the appropriate schema to store your Shopify order data.

Step 2: Prepare Google Sheets for Data Processing

We’ll use Google Sheets as an intermediary to process and format the data before loading it into BigQuery.

1. Create a New Google Sheet: Name it something like “Shopify Orders Processing”.
2. Set Up Columns: Define columns that match the fields in your BigQuery table, such as order_id, customer_name, total_price, order_date, etc.

Step 3: Configure Make to Automate the Workflow

Now, let’s set up the automation in Make.

1. Create a New Scenario: In Make, click on “Create a new scenario”.
2. Add Shopify Module:
   • Search for and add the Shopify module.
   • Select the “Watch Orders” trigger to monitor new orders.
   • Connect your Shopify account by entering your store’s URL and API credentials.
   • Set the trigger to watch for new orders created since the last run.
3. Add Google Sheets Module:
   • Search for and add the Google Sheets module.
   • Select the “Add a Row” action.
   • Connect your Google account and select the “Shopify Orders Processing” sheet.
   • Map the fields from the Shopify module to the corresponding columns in your Google Sheet. For example:
     • order_id → Order ID
     • customer_name → Customer Name
     • total_price → Total Price
     • order_date → Order Date
4. Add Google BigQuery Module:
   • Search for and add the Google BigQuery module.
   • Select the “Insert Rows” action.
   • Connect your Google Cloud account and select the appropriate dataset and table (e.g., shopify_sales.daily_orders).
   • Map the fields from the Google Sheets module to the corresponding columns in your BigQuery table.
5. Set Up Scheduling:
   • Click on the clock icon to schedule the scenario to run daily at a specific time.
6. Test and Activate:
   • Run the scenario once to ensure everything is working correctly.
   • If successful, activate the scenario to run automatically as scheduled.

Optional Enhancements

Once your basic workflow is set up, consider these quick-win enhancements:

• Error Handling: Add error handling modules in Make to notify you via email or Slack if the workflow encounters any issues.
• Data Validation: Implement data validation steps in Google Sheets to ensure data integrity before it’s loaded into BigQuery.
• Dashboard Integration: Use Google Data Studio to create dashboards that visualize your Shopify sales data directly from BigQuery.

By following these steps, you’ll have a robust, automated system that keeps your Shopify sales data up-to-date in BigQuery, enabling real-time analysis and reporting. Happy automating!