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.orders`
WHERE financial_status = 'paid'
  AND created_at >= DATE_SUB(CURRENT_DATE(), 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.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
  JSON_EXTRACT_SCALAR(d, '$.code') as discount_code,
  JSON_EXTRACT_SCALAR(d, '$.type') as discount_type,
  COUNT(*) as times_used,
  SUM(total_price) as revenue_with_discount,
  AVG(total_discounts) as avg_discount_amount
FROM `your_project.your_dataset.orders`,
UNNEST(JSON_EXTRACT_ARRAY(discount_codes)) as d
WHERE financial_status = 'paid'
GROUP BY discount_code, discount_type
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,
  DATE_DIFF(
    MAX(o.created_at),
    MIN(o.created_at),
    DAY
  ) as customer_lifespan_days
FROM `your_project.your_dataset.customers` c
LEFT JOIN `your_project.your_dataset.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 customer_cohorts AS (
  SELECT
    customer.id as customer_id,
    FORMAT_DATE('%Y-%m', MIN(created_at)) as cohort_month,
    DATE_DIFF(
      created_at,
      MIN(created_at) OVER (PARTITION BY customer.id),
      MONTH
    ) as months_since_first
  FROM `your_project.your_dataset.orders`
  WHERE financial_status = 'paid'
  GROUP BY customer.id, created_at
)
 
SELECT
  cohort_month,
  months_since_first,
  COUNT(DISTINCT customer_id) as customers,
  COUNT(*) as orders
FROM customer_cohorts
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.abandoned_checkouts`
WHERE completed_at IS NULL
  AND created_at >= DATE_SUB(CURRENT_DATE(), 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(
    DATE_DIFF(f.created_at, o.created_at, DAY)
  ) as avg_fulfillment_days,
  AVG(
    DATE_DIFF(f.delivery_date, f.created_at, DAY)
  ) as avg_delivery_days
FROM `your_project.your_dataset.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.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