4.3 Answering Business Questions with BigQuery: Worked Analyses

Google Cloud Associate Data Practitioner Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

About the Associate Data Practitioner Exam

2 Chapter 1: Data Processing Paradigms & Ingestion Strategies

1.1 ETL, ELT, and ETLT Transformation Paradigms 1.2 Data Transfer and Migration Tools 1.3 Data Quality, Cleaning, and Validation

3 Chapter 2: Data Formats & Storage Solution Selection

2.1 File Formats & Serialization Characteristics 2.2 Cloud Storage Buckets, Location Types & Storage Classes 2.3 Selecting Google Cloud Databases and Storage Services

4 Chapter 3: Loading Data & Modern Ingestion Architectures

3.1 Batch Loading Data into BigQuery 3.2 Streaming Ingestion & Deduplication Patterns 3.3 BigQuery External Tables, BigLake & Federated Queries 3.4 Schema Definition & Evolution for Loaded Data

5 Chapter 4: BigQuery SQL Analytics & Query Foundations

4.1 BigQuery SQL Syntax & Analytical Functions 4.2 Complex Data Types: Arrays, Structs, and JSON 4.3 Answering Business Questions with BigQuery: Worked Analyses

6 Chapter 5: BigQuery Performance Optimization & Cost Governance

5.1 Table Partitioning Strategies 5.2 Table Clustering & Data Skipping 5.3 Query Optimization, Execution Plans, and Cost Controls

7 Chapter 6: Interactive Analysis, Notebooks & Visual Presentation

6.1 Exploratory Analysis with Jupyter & Colab Enterprise 6.2 Looker Studio (Data Studio) Reporting & Dashboard Design 6.3 Enterprise BI with Looker & LookML Foundations 6.4 Building, Modifying & Sharing Looker Dashboards

8 Chapter 7: Machine Learning with BigQuery ML & Vertex AI

7.1 Planning an ML Project: BigQuery ML, AutoML, or Pretrained Models? 7.2 Creating, Training & Evaluating Models with BigQuery ML 7.3 Running Inference and Organizing Models in Model Registry 7.4 Using Pretrained Gemini Models from BigQuery with Remote Connections

9 Chapter 8: Pipeline Engines & Transformation Services

8.1 Transformation Engine Selection Matrix 8.2 Apache Beam & Cloud Dataflow Core Concepts 8.3 Stream Processing: Windowing, Watermarks, and Triggers

10 Chapter 9: Workflow Orchestration, Automation & Monitoring

9.1 SQL Pipelines with Dataform & Scheduled Queries 9.2 Workflow Orchestration with Cloud Composer & Workflows 9.3 Event-Driven Ingestion: Pub/Sub to BigQuery & Eventarc Triggers 9.4 Monitoring Pipelines: Dataflow Job UI, Cloud Logging & Cloud Monitoring

11 Chapter 10: Access Control, Governance & Data Sharing

10.1 IAM Principles & Role Hierarchies for Data Services 10.2 Cloud Storage Security & Access Controls 10.3 Fine-Grained Security: Column-Level & Row-Level Controls 10.4 Secure Data Sharing with Analytics Hub (BigQuery Sharing)

12 Chapter 11: Lifecycle Management, Reliability & Compliance

11.1 Object Lifecycle Management & Storage Tiering 11.2 High Availability & Disaster Recovery Architectures 11.3 Data Retention, Time Travel, and Point-in-Time Recovery 11.4 Encryption Key Management & Privacy Compliance

4.3 Answering Business Questions with BigQuery: Worked Analyses

Key Takeaways Explain This Topic AI

Turn a business question into a metric, a grain, filters, and a comparison before writing SQL.

Year-over-year growth uses DATE_TRUNC to monthly grain, LAG(revenue, 12), and SAFE_DIVIDE, which returns NULL instead of failing on a zero denominator.

Cohort retention groups customers by first-purchase month and counts distinct active customers by months since first purchase.

Funnels count distinct users at each step with COUNT(DISTINCT IF(...)), not raw events.

Validate results for join fan-out, distinct counts, time zones, and averages of averages before presenting them.

Last updated: October 2026

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

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."

Fix the grain. Decide what one row of the answer represents: one customer, one day, one region-month.

Set filters and the time window. Which orders count (completed only?), which dates, which time zone?

Choose a comparison. A number means little alone. Compare it with last period, the same period last year, a target, or another segment.

Validate, then present. Check totals and edge cases before anyone sees the chart.

• Business question Metric Grain Comparison
• "Is revenue growing?" Sum of completed order totals Month Same month last year (YoY)
• "Which products sell best in each region?" Revenue by product Region × product Rank within region

"Do new customers come back?" Share of a signup cohort active N months later Cohort month × months since first order Across cohorts

• "Where do shoppers drop off?" Users reaching each step Funnel step Step-to-step conversion
• "Who drives most revenue?" Cumulative revenue share Customer Top 20% vs. the rest
• 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

Check Why it matters

Compare a total with a trusted source (finance report, last month's dashboard) Catches wrong filters, such as including cancelled orders

Look for duplicates after joins Joining orders to order lines repeats order-level amounts (fan-out) and inflates sums

Use COUNT(DISTINCT ...) where entities, not rows, are counted Rows can repeat per event or per line item

Check time zones DATE(order_ts) uses UTC unless you pass a time zone, such as DATE(order_ts, 'America/New_York')

Check NULL handling AVG and COUNT(column) skip NULL s, which may or may not be what the business means

Avoid averaging averages The average of regional averages is not the company average unless regions are the same size; recompute from totals

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.