4.3 Answering Business Questions with BigQuery: Worked Analyses

4.3 Answering Business Questions with BigQuery: Worked Analyses

Core Focus: The exam guide asks you to "analyze data to answer business questions." That means turning a vague request ("Are customers coming back?") into a precise metric, writing SQL that computes it correctly, checking the result, and delivering it in the right form. This section walks through the patterns that cover most of those requests.

Sections 4.1 and 4.2 taught the SQL building blocks. Here you combine them into analyses a stakeholder would actually ask for.

---

From Question to Query: A Five-Step Method

1. Restate the question as a metric. "Are we losing customers?" becomes "monthly churn rate = customers active last month but not this month ÷ customers active last month."
2. Fix the grain. Decide what one row of the answer represents: one customer, one day, one region-month.
3. Set filters and the time window. Which orders count (completed only?), which dates, which time zone?
4. Choose a comparison. A number means little alone. Compare it with last period, the same period last year, a target, or another segment.
5. Validate, then present. Check totals and edge cases before anyone sees the chart.

---

Pattern 1: Trend and Growth (MoM and YoY)

WITH monthly AS (
  SELECT
    DATE_TRUNC(DATE(order_ts), MONTH) AS month,
    SUM(order_total) AS revenue
  FROM `sales.orders`
  WHERE order_status = 'COMPLETED'
  GROUP BY month
)
SELECT
  month,
  revenue,
  LAG(revenue, 1)  OVER (ORDER BY month) AS prior_month_revenue,
  LAG(revenue, 12) OVER (ORDER BY month) AS same_month_last_year,
  SAFE_DIVIDE(revenue - LAG(revenue, 12) OVER (ORDER BY month),
              LAG(revenue, 12) OVER (ORDER BY month)) AS yoy_growth
FROM monthly
ORDER BY month;

LAG(revenue, 12) assumes every month appears. If a month can be missing, join to a calendar built with GENERATE_DATE_ARRAY first. SAFE_DIVIDE returns NULL instead of an error when the denominator is zero.

Pattern 2: Top N Within Each Group

SELECT region, product_name, SUM(revenue) AS revenue
FROM `sales.order_lines`
GROUP BY region, product_name
QUALIFY RANK() OVER (PARTITION BY region ORDER BY SUM(revenue) DESC) <= 3;

Use RANK() if ties should all appear, ROW_NUMBER() if you need exactly three rows per region.

Pattern 3: Cohort Retention

WITH firsts AS (
  SELECT customer_id, DATE_TRUNC(MIN(DATE(order_ts)), MONTH) AS cohort_month
  FROM `sales.orders`
  GROUP BY customer_id
),
activity AS (
  SELECT DISTINCT
    o.customer_id,
    f.cohort_month,
    DATE_DIFF(DATE_TRUNC(DATE(o.order_ts), MONTH), f.cohort_month, MONTH) AS months_since_first
  FROM `sales.orders` AS o
  JOIN firsts AS f ON o.customer_id = f.customer_id
)
SELECT
  cohort_month,
  months_since_first,
  COUNT(DISTINCT customer_id) AS active_customers,
  SAFE_DIVIDE(COUNT(DISTINCT customer_id),
              MAX(COUNT(DISTINCT customer_id)) OVER (PARTITION BY cohort_month)) AS retention_rate
FROM activity
GROUP BY cohort_month, months_since_first
ORDER BY cohort_month, months_since_first;

Month 0 always has the largest count in a cohort, so dividing by the cohort's maximum gives the retention curve (100% at month 0).

Pattern 4: Funnel Conversion

SELECT
  COUNT(DISTINCT IF(event_name = 'view_item',   user_id, NULL)) AS viewers,
  COUNT(DISTINCT IF(event_name = 'add_to_cart', user_id, NULL)) AS cart_adders,
  COUNT(DISTINCT IF(event_name = 'purchase',    user_id, NULL)) AS buyers,
  SAFE_DIVIDE(COUNT(DISTINCT IF(event_name = 'purchase',  user_id, NULL)),
              COUNT(DISTINCT IF(event_name = 'view_item', user_id, NULL))) AS view_to_purchase_rate
FROM `web.events`
WHERE event_date BETWEEN '2026-09-01' AND '2026-09-30';

Count distinct users, not events: one shopper who views ten items is still one viewer.

Pattern 5: Segmentation and Contribution (Pareto)

WITH customer_revenue AS (
  SELECT customer_id, SUM(order_total) AS revenue
  FROM `sales.orders`
  WHERE order_status = 'COMPLETED'
  GROUP BY customer_id
)
SELECT
  customer_id,
  revenue,
  SUM(revenue) OVER (ORDER BY revenue DESC
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    / SUM(revenue) OVER () AS cumulative_share
FROM customer_revenue
ORDER BY revenue DESC;

Customers whose cumulative_share is at or below 0.8 are the group that drives 80% of revenue. A CASE expression on the same data turns spend into named segments ("High", "Mid", "Low") for a dashboard.

---

Validate Before You Present

Choosing How to Deliver the Answer

• One-off question: run the SQL in BigQuery Studio and save the query.
• Exploration with charts or statistics: use a notebook (Colab Enterprise) with BigQuery DataFrames (Section 6.1).
• Recurring question for many people: publish a dashboard in Looker or Looker Studio (Sections 6.2–6.4) on top of a summary table.
• Recurring calculation feeding other tools: materialize it with a scheduled query or Dataform (Section 9.1).

---

Common Exam Traps

• Fan-out after a join: Summing order_total after joining orders to order_items multiplies each order's total by its number of items. Aggregate each table to the same grain before joining, or sum a line-level amount instead.
• Dividing without protection: Plain / raises an error when the denominator is zero; SAFE_DIVIDE returns NULL.
• Counting rows instead of entities: "How many customers bought?" needs COUNT(DISTINCT customer_id).
• Missing periods in time series: LAG compares with the previous row, not the previous calendar month, if months are missing.