← Data Analytics from Scratch: SQL, Spreadsheets and Metrics
Lesson
Capstone: from dataset to a defensible conclusion
Learner can take a small dataset situation end to end — frame the question, pick the metric, the query/aggregate and the chart, check the thinking traps — and write a correct, hedged conclusion with self-review.
Putting it all together: a six-step mini-case
Putting it all together: a six-step mini-case
Good data analysis is a pipeline, not a single step. Here is a worked mini-case that integrates everything from Units 1–6.
Scenario: an e-commerce team wants to know whether their new checkout flow (launched in March) improved conversion. They have a table orders with columns month, flow_version ('old'/'new'), sessions (integer), conversions (integer).
Step 1 — Frame the question. "Did the conversion rate increase after the new checkout flow launched, and if so, by how much?"
Step 2 — Pick the metric. Conversion rate = conversions / sessions. Use percentage points (pp) for the absolute change and percent (%) for the relative change. Do not confuse the two: going from 4 % to 5 % is +1 pp but +25 % relative.
Step 3 — Write the query / aggregate. GROUP BY flow_version, then compute SUM(conversions) / SUM(sessions) for each group. Check: should you use mean of conversion rates per month (wrong — unequal session counts) or aggregate totals first (correct)?
Step 4 — Choose the chart. Because you are comparing two categories (old vs new flow), a bar chart is the right choice. The y-axis must start at zero.
Step 5 — Check the thinking traps. Did something else change in March (e.g. a marketing campaign)? That would be a confounder — correlation ≠ causation. Are you only looking at completed checkouts (survivorship bias)? Is the aggregate hiding a subgroup reversal — e.g. mobile improved but desktop worsened (Simpson's paradox)?
Step 6 — Write a hedged conclusion. "The aggregate conversion rate rose from 4.0 % (old flow) to 5.0 % (new flow), a gain of +1 pp (+25 % relative). The bar chart shows the new flow performs better overall. However, the data does not confirm causation: other March changes (e.g. a promotional campaign) could be a confounder. A controlled A/B test would be needed to establish a causal link. The subgroup split by device type was not examined and could reveal a Simpson's-paradox reversal."
The key discipline: always state what the data supports, what it does not support, and what additional analysis would be needed.
Lesson notes
Putting it all together: a six-step mini-case
Good data analysis is a pipeline, not a single step. Here is a worked mini-case that integrates everything from Units 1–6.
Scenario: an e-commerce team wants to know whether their new checkout flow (launched in March) improved conversion. They have a table orders with columns month, flow_version ('old'/'new'), sessions (integer), conversions (integer).
Step 1 — Frame the question. "Did the conversion rate increase after the new checkout flow launched, and if so, by how much?"
Step 2 — Pick the metric. Conversion rate = conversions / sessions. Use percentage points (pp) for the absolute change and percent (%) for the relative change. Do not confuse the two: going from 4 % to 5 % is +1 pp but +25 % relative.
Step 3 — Write the query / aggregate. GROUP BY flow_version, then compute SUM(conversions) / SUM(sessions) for each group. Check: should you use mean of conversion rates per month (wrong — unequal session counts) or aggregate totals first (correct)?
Step 4 — Choose the chart. Because you are comparing two categories (old vs new flow), a bar chart is the right choice. The y-axis must start at zero.
Step 5 — Check the thinking traps. Did something else change in March (e.g. a marketing campaign)? That would be a confounder — correlation ≠ causation. Are you only looking at completed checkouts (survivorship bias)? Is the aggregate hiding a subgroup reversal — e.g. mobile improved but desktop worsened (Simpson's paradox)?
Step 6 — Write a hedged conclusion. "The aggregate conversion rate rose from 4.0 % (old flow) to 5.0 % (new flow), a gain of +1 pp (+25 % relative). The bar chart shows the new flow performs better overall. However, the data does not confirm causation: other March changes (e.g. a promotional campaign) could be a confounder. A controlled A/B test would be needed to establish a causal link. The subgroup split by device type was not examined and could reveal a Simpson's-paradox reversal."
The key discipline: always state what the data supports, what it does not support, and what additional analysis would be needed.