BigQuery Example Queries

SELECT
  DATE(created_at) as order_date,
  COUNT(*) as orders,
  SUM(total_price) as revenue,
  AVG(total_price) as aov
FROM `your_project.your_dataset.shopify_orders`
WHERE financial_status = 'paid'
  AND created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY order_date
ORDER BY order_date DESC

SELECT
  li.product_id,
  li.title as product_name,
  COUNT(*) as times_ordered,
  SUM(li.quantity) as units_sold,
  SUM(li.price * li.quantity) as revenue
FROM `your_project.your_dataset.shopify_orders` o,
UNNEST(line_items) as li
WHERE o.financial_status = 'paid'
GROUP BY li.product_id, li.title
ORDER BY revenue DESC
LIMIT 20

SELECT
  discount_code,
  COUNT(*) as times_used,
  SUM(total_price) as revenue_with_discount,
  AVG(total_discounts) as avg_discount_amount
FROM `your_project.your_dataset.shopify_orders`,
UNNEST(REGEXP_EXTRACT_ALL(discount_codes, r"'([^']*)'")) as discount_code
WHERE financial_status = 'paid'
GROUP BY discount_code
ORDER BY times_used DESC

SELECT
  c.id as customer_id,
  c.email,
  c.first_name,
  c.last_name,
  COUNT(o.id) as order_count,
  SUM(o.total_price) as lifetime_value,
  MIN(o.created_at) as first_order,
  MAX(o.created_at) as last_order,
  TIMESTAMP_DIFF(
    MAX(o.created_at),
    MIN(o.created_at),
    DAY
  ) as customer_lifespan_days
FROM `your_project.your_dataset.shopify_customers` c
LEFT JOIN `your_project.your_dataset.shopify_orders` o
  ON c.id = o.customer.id
WHERE o.financial_status = 'paid'
GROUP BY c.id, c.email, c.first_name, c.last_name
ORDER BY lifetime_value DESC

WITH first_orders AS (
  SELECT
    customer.id as customer_id,
    DATE(MIN(created_at)) as first_order_date
  FROM `your_project.your_dataset.shopify_orders`
  WHERE financial_status = 'paid'
    AND customer.id IS NOT NULL
  GROUP BY customer_id
)
 
SELECT
  FORMAT_DATE('%Y-%m', f.first_order_date) as cohort_month,
  DATE_DIFF(DATE(o.created_at), f.first_order_date, MONTH) as months_since_first,
  COUNT(DISTINCT f.customer_id) as customers,
  COUNT(*) as orders
FROM `your_project.your_dataset.shopify_orders` o
JOIN first_orders f
  ON o.customer.id = f.customer_id
WHERE o.financial_status = 'paid'
GROUP BY cohort_month, months_since_first
ORDER BY cohort_month, months_since_first

SELECT
  DATE(created_at) as date,
  COUNT(*) as abandoned_carts,
  SUM(total_price) as abandoned_value,
  AVG(total_price) as avg_abandoned_value,
  COUNT(DISTINCT email) as unique_customers
FROM `your_project.your_dataset.shopify_abandoned_checkouts`
WHERE completed_at IS NULL
  AND created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY date
ORDER BY date DESC

SELECT
  DATE(o.created_at) as order_date,
  COUNT(DISTINCT o.id) as orders,
  AVG(
    TIMESTAMP_DIFF(f.created_at, o.created_at, DAY)
  ) as avg_fulfillment_days,
  AVG(
    TIMESTAMP_DIFF(f.delivery_date, f.created_at, DAY)
  ) as avg_delivery_days
FROM `your_project.your_dataset.shopify_orders` o,
UNNEST(fulfillments) as f
WHERE f.delivery_date IS NOT NULL
GROUP BY order_date
ORDER BY order_date DESC

SELECT
  REGEXP_EXTRACT(landing_site, r'utm_source=([^&]+)') as utm_source,
  REGEXP_EXTRACT(landing_site, r'utm_medium=([^&]+)') as utm_medium,
  REGEXP_EXTRACT(landing_site, r'utm_campaign=([^&]+)') as utm_campaign,
  COUNT(*) as orders,
  SUM(total_price) as revenue,
  AVG(total_price) as aov
FROM `your_project.your_dataset.shopify_orders`
WHERE financial_status = 'paid'
  AND landing_site IS NOT NULL
GROUP BY utm_source, utm_medium, utm_campaign
ORDER BY revenue DESC

-- Example: Create a purchase prediction model
CREATE OR REPLACE MODEL `your_dataset.purchase_prediction`
OPTIONS(model_type='logistic_reg') AS
SELECT
  IF(order_count > 0, 1, 0) as will_purchase,
  page_views,
  products_viewed,
  cart_adds,
  days_since_last_visit
FROM customer_behavior_features