How to sync your Shopify Orders and Line Items to BigQuery

Overview: SyncRange + Shopify + BigQuery

SyncRange is a no-code data connector that automates exporting e-commerce data to destinations including Google BigQuery. Connecting Shopify to BigQuery via SyncRange lets you combine sales data with other datasets, run complex SQL queries, and create dashboards in tools like Looker Studio. BigQuery is ideal for large volumes and analytics (it can handle millions of rows, complex joins, and supports machine learning), whereas spreadsheets hit limits.

Exporting Shopify orders (and their associated line items) to BigQuery unlocks benefits such as real-time sales reporting, inventory insights, and reliable backups. You can recover deleted records and join orders with marketing or finance data for deeper analysis.

SyncRange automates the data flow, keeping your warehouse up-to-date without manual CSV downloads

Prerequisites

Before you begin, ensure you have the following:

• Shopify store with admin access: You need a Shopify account. In SyncRange you’ll connect to your Shopify store so SyncRange can fetch Orders, Line Items, and other data.
• Google Cloud Project & BigQuery: Your Google Cloud project must have BigQuery enabled. You’ll need the BigQuery Admin or Editor role. In SyncRange’s Google integration dashboard, add a BigQuery destination by selecting your project and an existing dataset, or creating a new one (e.g. syncrange_exports).
• SyncRange account: Sign up for a SyncRange account (free tier available). In your dashboard Connections section, add two connections: one for Shopify and one for Google/BigQuery. For Shopify, enter your store URL and authorise SyncRange (or install from the Shopify App Store). For Google, authorise SyncRange and configure BigQuery as described above.
• Optional tools: Familiarity with SQL and BigQuery Console is helpful for schema review and analytics.

Setting Up the Sync in SyncRange

1. Create the Export: In SyncRange, go to Export Builder and click Create New Export. Choose Shopify as the data source, and select your connected Shopify store. For destination, pick your BigQuery connector and target dataset.
2. Configure Date Range: Choose how much history to fetch on the first run. For example, use Maximum (10 years) to pull all available orders and line items initially. After that, use Since last export with a daily schedule for incremental updates. (Note: “Since last export” ensures each run only adds orders that arrived since the previous run.)
3. Select Data Tabs: SyncRange presents separate tabs for each data type. Enable the Orders and Line Items tabs to export those tables. (You can enable other tabs like Customers or Products if needed.) Each tab can be configured independently.
4. Choose Columns: For each tab, click Select Columns and pick the fields you need. SyncRange exposes all Shopify fields, including nested objects (e.g. addresses) and arrays (e.g. discount codes) as JSON if desired. For example, on the Orders tab you might select Order ID, Order Number, Date, Customer Email, Total Price, Currency, Financial Status, Fulfilment Status, etc.. On the Line Items tab, pick Line Item ID, Order ID, Line Item Name, Quantity, Price, SKU, Variant ID, Product ID, Discount Amount, Tax, Currency, etc.. Use the “Select All”/“Deselect All” buttons or search box as needed.
5. Data Handling (Append vs Replace): For Orders and Line Items, use Append mode with incremental dates to build a historical log. Append mode adds new rows each run, leaving old data intact. (Append is ideal for time-series data like orders; use Replace only if you need to fully refresh static tables.) For example, after the initial full load, subsequent runs should use Append and “Since last export” to capture new orders each day.
6. Save & Test Run: Save the export configuration. Then click Run Export Now to pull data immediately. SyncRange will extract the selected Shopify orders and line items and load them into your BigQuery tables. Verify the tables in BigQuery: you should see rows inserted. Once confirmed, schedule the export (e.g. daily at 2am) to keep it automating.
7. Scheduling: Under Schedules, set the frequency (e.g. daily, or multiple times per day) to refresh. SyncRange will then automatically run the export on your schedule, appending new orders to BigQuery without manual intervention.

Available Shopify Fields

Below are the main fields you can export for Orders and Line Items. SyncRange makes every Shopify field available, including nested objects and arrays (often as JSON). Use these to guide your BigQuery schema.

Shopify Order Fields

Shopify Line Item Fields

Recommended BigQuery Schema

In BigQuery, you can let SyncRange auto-detect the schema or define a custom schema. Here are general recommendations:

• Data Types: Use INT64 for numeric IDs (order_id, line_item_id, customer_id, product_id, etc.), STRING for text fields, and NUMERIC or FLOAT64 for prices/totals if you need numeric maths (SyncRange’s default inserts money as strings, but you can cast them in BQ). Use TIMESTAMP for date/time fields (e.g. created_at, updated_at). JSON columns (for things like discount_codes (JSON)) can be loaded as STRING and parsed with PARSE_JSON().
• Partitioning: For large stores, enable partitioning on the Orders table by DATE(created_at) (or the order date). This improves query performance and cost for time-based analysis. For example: partition by day on the order date.
• Clustering: You can cluster on fields that are commonly filtered or joined on, such as customer_id, order_id, or product_id. Clustering improves lookups on those columns.
• Table Names: SyncRange will create tables like syncrange_shopify_orders and syncrange_shopify_line_items (prefix can be customised). Use clear, descriptive names.

Mapping Patterns & Transformations

When moving data from Shopify to BigQuery, common patterns include:

• Flattening Nested Data: Shopify’s REST responses nest objects (like shipping_address or tax_lines). SyncRange flattens top-level scalar fields and can emit nested structures as JSON strings. In BigQuery, you can use JSON_VALUE or PARSE_JSON to query those fields if needed. For example, SyncRange can output shipping_city and shipping_country separately, and output tax_lines (JSON) as a string for parsing.
• Handling Line Items: In BigQuery, you’ll typically have separate tables for orders and line_items. Link them via order_id or order_number. This avoids dealing with arrays of line items inside one row. You can then do joins between orders and line items in SQL.
• Currency and Money: Shopify returns prices as strings (e.g. "99.99"). If you loaded them as STRING, cast to NUMERIC in your queries (e.g. CAST(total_price AS NUMERIC)). Alternatively, have SyncRange auto-detect to NUMERIC. Keep in mind currency codes (e.g. USD, AUD); if all sales are in one currency, you can drop that column; otherwise include currency.
• Timestamps: Convert Shopify’s ISO strings to TIMESTAMP. BigQuery usually auto-detects these as TIMESTAMP. If not, use PARSE_TIMESTAMP().
• Discount and Note JSON: SyncRange can export discount code arrays as JSON (e.g. discount_codes (JSON)). In BigQuery use PARSE_JSON(column) and array functions to filter or unnest these. Similarly, note_attributes comes as a JSON array if enabled. Use these JSON columns for flexible filtering (e.g. custom order flags).
• Default Values: Shopify sometimes returns null for empty values (e.g. fulfillment_status). Handle nulls in your queries (e.g. use IFNULL(fulfillment_status,'none') as needed).

SyncRange also supports advanced features like automatically splitting comma-lists into JSON arrays, which is useful when you have multi-value fields (e.g. multiple discount codes or customer segments). This ensures your queries in BigQuery can treat them consistently (via PARSE_JSON).

Use Cases & Analytics Examples

• Daily Revenue: Sum sales by date.
• Top Products: Find best-selling items by quantity.
• Customer Lifetime Value: Aggregate spend per customer.
• Returns Analysis: If you mark returns via a flag (e.g. financial_status = 'refunded' or use a Returns table), analyse which products get returned most.
• Segmented Sales: Use note_attributes or customer tags (exported as JSON) to filter orders. E.g., revenue by a custom tag.
• Time-series trends: BigQuery’s analytics functions (window functions, date comparisons) let you see week-over-week growth, seasonal patterns, etc.

Troubleshooting and Best Practices

• API Rate Limits: Shopify enforces API rate limits (e.g. roughly 2 requests per second for REST). SyncRange handles this by pacing the calls, but very large exports (years of data) may need time. If you hit timeouts, try reducing the date range or splitting the export into smaller batches.
• Data Consistency: Use Append mode with “Since last export” for incremental loads to avoid duplicates. SyncRange marks the last export time and only fetches newer orders on each run. If you ever switch from Replace to Append, clean up duplicates or do a fresh full run.
• Initial Backfill: Consider doing an initial full historical pull (“Maximum” range) before switching to incrementals. This ensures your BigQuery tables start with all past data.
• Monitor Logs: Check SyncRange’s Export Logs (or Dashboard Logs) after the first few runs. If a run fails (e.g. due to a credential issue), the log will show the error.
• Handling Schema Changes: If you add new custom fields or metafields to Shopify, you can edit the export configuration and add those columns; SyncRange will include them going forward. For BigQuery, you may need to ALTER the table (or let SyncRange auto-detect the new column).
• BigQuery Quotas: Watch your BigQuery storage and query costs (especially on large tables). Use partitioning to limit scanned data. SyncRange’s BigQuery connector can write to “Replace” or “Write if Empty” if you prefer full snapshot loads, but Append is usually more efficient.
• Best Practice: Keep queries idempotent. For example, if you join orders and line items, write queries that handle both appended runs and full reloads seamlessly. And periodically verify counts: e.g. SELECT COUNT(*) FROM dataset.shopify_orders against Shopify’s order count.
• Incremental Failures: If an incremental run fails (perhaps due to expired Shopify token), fix the issue and re-run manually from the SyncRange console. The last successful timestamp is saved, so the next run will pick up from there.

Launch Checklist

Before going live, ensure you have:

• Shopify Connector: SyncRange is authorised in Shopify with read access to Orders and related data.
• BigQuery Destination: Google connection set up, dataset exists, permissions verified (BigQuery Data Editor at least).
• Export Configuration: Orders and Line Items tabs enabled in the export; required fields selected.
• Initial Test Run: Performed a manual run and verified all expected columns appear in BigQuery (row counts, sample values).
• Schedule: Export scheduled (e.g. daily at low-traffic time).
• Data Validation: Spot-check new data in BigQuery after first scheduled run (compare order counts with Shopify for sanity).
• Monitoring: Set up a notification or regular check of SyncRange logs to catch failures early.
• Documentation: Note the schema, data flow, and any quirks (e.g. if you use JSON fields) so your team knows how to use the data.
• Queries/Dashboards: Create a couple of SQL queries or dashboards (e.g. revenue by day, top products) to ensure the data meets your needs.

Once everything is configured, SyncRange will handle ongoing syncs in the background. Your BigQuery tables will stay current with minimal effort. You can then focus on analysing the data and growing your business without manual exports.