MODULE 06 — WORKED SOLUTIONS
Attempt each milestone first. Query outputs are in expected_outputs.json.

1. Turn the business question into a project plan
A workable question is: “How does completed-order value change between April and May in the supplied extract, what context qualifies that change, and what should the operations lead investigate before expanding the initiative?” The scope is one verified order per ID, completed status, the supplied April–May window and EUR major units.
The planned evidence chain is raw source → correction log/prepared data → monthly and channel metrics → distribution/coverage checks → dashboard → case study and decision note. Acceptance criteria include 44 prepared IDs, 40 completed orders, reconciled channel totals, visible missingness and consistent filter states. These fixture checkpoints support review; they do not replace source validation.
Identify absent costs, customer exposure and experiment design as limits. Choose either a spreadsheet or local SQLite as the primary route. A short folder guide and an honest guided-work label make the submission more useful than a collection of unrelated exports.


2. Prepare the source and preserve the correction evidence
Stage | Records / orders | 
Raw source | 46 records / 44 IDs | 
Remove exact excess A004 | 45 records | 
Resolve conflicting excess Y007 | 44 records / 44 IDs | 
Apply six field corrections | 44 retained orders | 
Completed analytical population | 40 orders / 20 each month | 
Corrections are A005 → web, A008 → 2026-04-08, A009 → €110, Y003 → store, Y005 → €190 and Y006 → four delivery days. The completed source log is supplied in quality_log_solution.csv. Y003 demonstrates why “mobile” cannot simply be guessed to mean web; the reference confirms store.
P1 returns A004 and Y007 with two source records each. P2 returns the six field-exception IDs. Keep 11 verified delivery blanks, seven of them on completed orders. Without authoritative values, record unresolved issues and qualify affected results rather than filling guesses. The supplied orders SQL table is a reference, not evidence that an undocumented cleaning algorithm succeeded.


3. Build a reproducible analysis with explicit checks
Measure | April | May | 
Completed count | 20 | 20 | 
Completed value EUR | 2900 | 3420 | 
AOV EUR | 145 | 171 | 
Measured / eligible | 14 / 20 | 19 / 20 | 
Delivery coverage | 70% | 95% | 
Observed mean days | 51/14 = 3.6429 | 67/19 = 3.5263 | 
Returned / all statuses | 1/22 = 4.55% | 1/22 = 4.55% | 
P4 produces April store: 2 orders, €1,100, AOV €550, share 10%; April web: 18, €1,800, €100, 90%; May store: 18, €3,240, €180, 90%; May web: 2, €180, €90, 10%. These components reconcile exactly to P3. The alternative returned/(completed+returned) share is 1/21 = 4.76%, not the same as 1/22.
P6 retains April’s prior value and change as NULL. May change is (3420−2900)/2900 × 100 = 17.9310%. SQLite coverage/share outputs such as 0.7 mean 70%; change_pct is already 17.9310, not 0.179310. The complete expected columns and rows are in the supplied JSON file.


4. Investigate composition, concentration and missingness
Analysis | Worked result | 
Actual AOV | April €145 → May €171 | 
Channel AOV | Web €100 → €90; store €550 → €180 | 
Store order share | 10% → 90%, +80 percentage points | 
Hypothetical equal-mix AOV | €325 → €135, not actual AOV | 
Completed baseline | 40 orders / €6,320 / mean €158 | 
Exclude verified A020 scenario | 39 / €5,320 / mean €136.41 | 
P7’s five counts sum to 40. Retaining a zero-count €200–<€300 bin is useful: its absence would otherwise be ambiguous. The final bin includes €1,100 at its upper boundary, even though this fixture’s highest order is €1,000. Check the stated bin convention when reproducing the graphic.
A reasonable alternative explanation is a shift in the mix of recorded orders rather than uniform within-channel improvement. The sparse April store group and large verified order also influence the comparison. Request wider comparable channel history and source context for the mix change. These observations do not identify the initiative’s incremental impact or profitability.


5. Build and test the project dashboard
View | Value / count / AOV | Coverage | Comparison | 
May / All | €3,420 / 20 / €171 | 19/20 | +17.9% vs April/All | 
April / All | €2,900 / 20 / €145 | 14/20 | Unavailable: March absent | 
Combined / All | €6,320 / 40 / €158 | 33/40 | Unavailable: prior window absent | 
May / Web | €180 / 2 / €90 | 1/2 | −90% vs April/Web | 
May / Store | €3,240 / 18 / €180 | 18/18 | +194.5% vs April/Store | 
For the empty test, show count/value zero and unavailable AOV, coverage and percentage change. Reset restores May/All and its 20 underlying completed rows. If using static views, label the selection on each page and document that there are no interactive filters. Do not present a static screenshot as a tested interactive application.
Record source identity, checks performed and refresh limitations. The supplied dashboard is a separate Module 5 reference; the capstone’s contribution is your integrated report and the explanation of its design, calculations and verified behaviour.


6. Write a case study that connects evidence to a recommendation
Worked decision note
In the supplied April–May 2026 extract, completed-order value rises from €2,900 to €3,420, or 17.9%, with 20 completed orders in each month. Overall AOV increases from €145 to €171, but web AOV falls from €100 to €90 and store AOV from €550 to €180. Store’s order share changes from 10% to 90%. April store has only two orders, including verified A020 at €1,000, so the comparison is sensitive to composition and a large order. Delivery means cover 14/20 April and 19/20 May completed orders; they do not establish an all-order service improvement. Retain the verified high order in baseline results and label any exclusion separately. Investigate the mix shift, collect comparable periods and obtain costs before recommending expansion. This fictional guided extract establishes neither profitability nor a causal effect of the initiative.
Trace monthly figures to P3, channel figures to P4, relative value change to P6 and the named exclusion to P8. Link the preparation log and metric contract. The complete worked case study is available separately; use it to compare structure after drafting your own explanation.


7. Package reproducible evidence and describe your contribution
A clear contribution statement is: “This is a fictional guided project using DataCareerHub Northstar data. I recreated the preparation log, implemented monthly/channel metrics, tested selected dashboard states and wrote the interpretation. Supplied reference queries and the worked dashboard were used for comparison; my independent extension is identified separately.” Adapt that statement to what you actually did.
The recommended opening file is your case study, with links to methods and evidence. A folder guide should explain the SQL or spreadsheet route, specify EUR units and completed eligibility, and identify the static data edition. The supplied ZIP includes all local evidence dependencies; extract it before following links.
Do not add a real-client testimonial, savings claim or certification. Local course practice does not automatically publish a portfolio or grant a qualification. The stronger evidence is a reproducible method and an accurate description of your own analytical decisions.


8. Add an independent extension and complete the final review
Extension: delivery coverage | April | May | 
Web measured / eligible | 12 / 18 = 66.7% | 1 / 2 = 50% | 
Store measured / eligible | 2 / 2 = 100% | 18 / 18 = 100% | 
All measured / eligible | 14 / 20 = 70% | 19 / 20 = 95% | 
A worked interpretation is: “In the supplied completed-order population, missing delivery measurements are concentrated in web orders. April web coverage is 12/18 and May web coverage is 1/2, while both store groups are fully measured. The overall coverage increase therefore depends partly on changing group composition. The small May web group and unknown durations prevent a claim about channel delivery speed. Investigate the collection process before comparing all-order service outcomes.”
For the outlier extension, April excluding A020 has 19 orders, €1,900 and AOV €100; the €145 April baseline remains. A full submission earns two points in each rubric category when the evidence supports it. A copied case study with no implemented method or independent extension cannot be treated as a complete independent project merely because it looks polished.
