Build a reproducible analysis with explicit checks

Understand the method

The analysis should allow another reader to reproduce the numbers without relying on a screenshot. Choose a primary tool and make the calculation sequence visible. Both the spreadsheet and SQL routes start from the verified order grain and the same completed-status rule. A tool change must not silently change units, inclusion rules or the denominator of an average.

Define the calculation contract

Completed-order value sums order_value_eur for eligible completed orders. Completed count counts those orders. AOV divides that value by that count. Delivery coverage divides measured completed orders by all eligible completed orders; the observed delivery mean uses only measured durations. Status-share calculations belong to a separately stated population. A returned order’s recorded value is not a refund transaction or proof of a customer-cohort return rate.

Follow the SQL route

Run analysis_setup.sql in a new empty local SQLite database, then work through analysis_starters.sql. P1/P2 inspect the source, P3 produces monthly completed metrics and delivery coverage, P4 produces channel metrics and order shares, P5 compares explicit status-share denominators, P6 calculates a prior-observed-month comparison, P7 builds distribution bins and P8 separates baseline from an outlier-exclusion scenario. Compare your answers with analysis_solutions.sql only after trying the tasks.

Use P3 as the baseline and reconcile P4’s counts and values back to each month. Query P6 uses LAG, so its prior value is the previous observed row. It is a true April–May comparison in this fixture because both months are present; on a different source with gaps it would need a calendar-completeness check. The first prior value remains NULL, and a zero prior denominator also produces an unavailable percentage.

Follow the spreadsheet route

Import the prepared data with IDs as text, valid date values and numeric EUR amounts. Keep delivery blanks empty. Use the monthly conditional sums and counts practised in Module 2, then divide totals by eligible counts for AOV. Build a month/channel summary from the same data. Keep inputs separate from summaries, label cells and show calculations rather than typing the answer-key totals into a dashboard.

After filtering, be careful about totals that still include hidden records. Use an explicitly defined conditional calculation or a correctly configured summary rather than assuming a visible filter changes every formula. Recreate coverage with a measured-value count that preserves a known zero and excludes a missing value. Use the expected output file as a comparison after calculation, not as a substitute data source.

Record independent checks

April should have 20 completed orders and €2,900; May has 20 and €3,420. Channel values and counts should reconcile to these baselines. Across the full completed population, total value is €6,320 and AOV is €158. A zero reconciliation difference is useful only when both sides are independently derived rather than linked to the same already-aggregated cell.

Keep query names or worksheet references beside each reported measure. Record the engine or tool used and any untested features. The downloadable expected_outputs.json records query columns and rows; shares are fractions, while change_pct is in percentage units. This attention to definitions makes comparisons meaningful across tools.