Analytics

BigQuery for Shopify stores: the first useful queries

When a Shopify store outgrows dashboards, BigQuery answers the questions GA4 cannot. The setup, the first four queries, and how to keep the bill small.

By CartKernel · Published September 6, 2026

A Shopify store reaches for a warehouse at a specific moment: when the question it needs answered spans two systems, or reaches further back than a report will go. Which acquisition channel produces customers who order twice. What a landing page cohort is worth six months later. Which products get viewed a great deal and bought rarely. None of those fit in a standard analytics interface, and all of them are a short query once the data sits in one place.

BigQuery is the least effortful way to get there, because the GA4 export is free to turn on and the storage cost for a mid-sized store is small. What follows is the setup, four queries worth having on day one, and the habits that keep the bill in the range of a coffee rather than a subscription.

Turn on the export before you need it

The GA4 to BigQuery link only sends data forward from the day it is created. It does not backfill. Enable it now even if nobody will query it for six months, because the history you want in a year begins accumulating the day you switch it on.

In GA4, go to Admin, then Product links, then BigQuery links, and create a link to a Cloud project. Choose the daily export, and add the streaming export only if you have a real need for same-day data. Daily arrives as one table per date; streaming arrives continuously into a separate table and costs more.

Two settings deserve attention at creation. Pick the data location closest to your customers and keep every dataset in the same location, because BigQuery will not join tables across regions. And include the events for all your data streams, not just web, if you also run an app.

Getting Shopify orders alongside it

GA4 alone answers traffic questions. The interesting questions need order data, which means getting Shopify orders into the same dataset. Three routes, in rising order of effort:

A scheduled export. Export orders as CSV on a schedule and load them into a table. Crude, and adequate for a store closing a few thousand orders a month.
A connector. Several managed pipeline services replicate Shopify objects into BigQuery on a schedule with no code.
Webhooks into a table. A small Cloud Function subscribed to the orders webhooks writes each order as it happens. More control, more to maintain.

Whichever route, keep the raw order record. Resist the urge to load only the columns you think you need, because the next question always needs a column you dropped.

Query one: daily orders and revenue from the export

The reconciliation base. Everything else is suspect until this matches what the store says.

SELECT
  PARSE_DATE('%Y%m%d', event_date) AS day,
  COUNT(DISTINCT ecommerce.transaction_id) AS transactions,
  ROUND(SUM(ecommerce.purchase_revenue), 2) AS revenue
FROM `project.analytics_XXXXXXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260801' AND '20260831'
  AND event_name = 'purchase'
GROUP BY day
ORDER BY day;

The _TABLE_SUFFIX filter is not optional. Without it the wildcard scans every day of history you own, and the cost of a careless query is entirely in how much data it reads. Counting distinct transaction IDs rather than events also does something useful: if the count of purchase events exceeds the count of distinct IDs, you have a duplicate purchase path, which is the first thing to fix. The full method is in reconciling GA4 with Shopify orders.

Query two: sessions, conversion rate and revenue per session by channel

The report GA4 gives you, except you control the definitions and can keep it for years.

WITH sessions AS (
  SELECT
    CONCAT(user_pseudo_id, '-', CAST((
      SELECT value.int_value FROM UNNEST(event_params)
      WHERE key = 'ga_session_id') AS STRING)) AS session_key,
    ANY_VALUE(collected_traffic_source.manual_source) AS source,
    ANY_VALUE(collected_traffic_source.manual_medium) AS medium,
    COUNTIF(event_name = 'purchase') AS purchases,
    SUM(IFNULL(ecommerce.purchase_revenue, 0)) AS revenue
  FROM `project.analytics_XXXXXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260801' AND '20260831'
  GROUP BY session_key
)
SELECT
  IFNULL(source, '(direct)') AS source,
  IFNULL(medium, '(none)') AS medium,
  COUNT(*) AS sessions,
  SUM(purchases) AS purchases,
  ROUND(SUM(purchases) / COUNT(*) * 100, 2) AS cvr_pct,
  ROUND(SUM(revenue) / COUNT(*), 2) AS revenue_per_session
FROM sessions
GROUP BY source, medium
ORDER BY sessions DESC
LIMIT 25;

Note which source field you are using. The export carries a user-scoped traffic_source, which holds first acquisition and never changes, and event-scoped fields for what was collected at the time. They answer different questions, and quietly mixing them is how a channel report ends up unexplainable. Revenue per session is the column to sort on when you are deciding where to add budget, because it combines traffic quality and basket size in one figure.

Query three: view to purchase ratio by product

The one that changes merchandising decisions.

SELECT
  item.item_id,
  ANY_VALUE(item.item_name) AS item_name,
  COUNTIF(event_name = 'view_item') AS views,
  COUNTIF(event_name = 'add_to_cart') AS adds,
  COUNTIF(event_name = 'purchase') AS purchases
FROM `project.analytics_XXXXXXXXX.events_*`, UNNEST(items) AS item
WHERE _TABLE_SUFFIX BETWEEN '20260801' AND '20260831'
  AND event_name IN ('view_item', 'add_to_cart', 'purchase')
GROUP BY item.item_id
HAVING views > 100
ORDER BY SAFE_DIVIDE(purchases, views) ASC
LIMIT 50;

Sorted ascending, this lists the products that get attention and lose it. The reasons cluster: photography that does not show the thing buyers care about, a price that reads badly next to the competition, missing sizes, a shipping estimate that appears late, or reviews that answer the wrong objection. Take the top ten and open each product page as a shopper. Several of them will have the same fault, and product page CRO is the work that follows.

Query four: a customer cohort from Shopify orders

This one runs on the orders table rather than the GA4 export, because it needs the full purchase history rather than the measured share of it.

WITH first_orders AS (
  SELECT customer_id, MIN(DATE(created_at)) AS first_date
  FROM `project.shopify.orders`
  WHERE customer_id IS NOT NULL
  GROUP BY customer_id
)
SELECT
  DATE_TRUNC(f.first_date, MONTH) AS cohort_month,
  DATE_DIFF(DATE(o.created_at), f.first_date, MONTH) AS months_since,
  COUNT(DISTINCT o.customer_id) AS customers,
  ROUND(SUM(o.total_price), 2) AS revenue
FROM `project.shopify.orders` o
JOIN first_orders f USING (customer_id)
GROUP BY cohort_month, months_since
ORDER BY cohort_month, months_since;

Pivot the result and you have cumulative revenue per cohort by month, which is the honest basis for customer lifetime value and for deciding how long an acquisition payback period can reasonably be. Add the acquisition channel of the first order and the same table tells you which channels bring customers who come back, which is the question that quietly decides the whole budget.

Keeping the bill small

Always filter _TABLE_SUFFIX. It is the difference between scanning one day and scanning three years.
Never SELECT * on the events table. Columnar storage means you pay for the columns you name, and the nested item and parameter columns are the large ones.
Use the query validator. The editor shows the bytes a query will process before you run it. Read it every time until the habit sticks.
Set a project-level quota. A daily maximum on bytes billed turns a mistake into an error message rather than an invoice.
Materialise what you repeat. A scheduled query that writes a small daily summary table costs one scan a day, and every dashboard then reads the summary instead of the raw export.

That last habit is what makes a dashboard sustainable. Build a table with one row per day per channel, one with one row per order, and one with one row per customer, refresh them nightly on a schedule, and point every report at those three. The raw export stays available for the question nobody anticipated, and the routine reporting stops touching it. From there, connecting a reporting layer is straightforward, and which reports to check weekly covers what belongs on the front page of it.