MODULE 04 — WORKED SOLUTIONS Attempt each exercise before comparing. Use README.txt for definitions. 1. Define the extract and build a useful data profile The raw extract has 46 rows and 44 distinct order IDs. A004 and Y007 each appear twice. The verified extract has 44 rows and 44 IDs. April and May each contain 22 orders: 20 completed, one returned and one cancelled. The analytical sales population therefore contains 40 completed orders, not 44 or 46 records. Check | Expected observation | Consequence | Grain | One verified order per order_id | Resolve repeated keys before totals | Value units | EUR, already in major currency units | Do not divide by 100 as in Module 3 | Monthly completed denominator | 20 orders per month | Use the same population for sales and AOV | Source coverage | Supplied April–May fixture only | Avoid claiming complete company trading coverage | A sound scope note is: “Compare recorded completed-order values in the supplied April–May 2026 extract. Preserve excluded statuses for status counts. Treat delivery blanks as unavailable measurements. This fictional sample cannot identify customer conversion, profit or a causal effect of the initiative.” Use the dictionary to check parsing rules, inspect categorical values, identify negative or blank amounts and review duplicate records. A source profile describes what arrived; it does not authorize repairs. 2. Turn data-quality findings into explicit rules Eight issue entries are expected: A004 duplicate_exact; Y007 duplicate_conflict; A005 category_format; A008 date_invalid; A009 value_negative; Y003 category_unknown; Y005 value_missing; Y006 delivery_type. The downloadable quality_log_solution.csv gives observed values, actions and their authority. A005 can become web by the documented trim/lowercase rule. Y003 cannot safely become web simply because “mobile” sounds digital: the reference confirms store. A009’s -110 becomes €110 only because the reference supports that correction. Y005’s amount is €190 in the reference. Y006’s delivery duration is four days. Without that reference, quarantine or mark unresolved values and state how totals are incomplete. Do not take absolute values, fill a mean or replace unknown channels with the largest category. For the prepared completed population there are seven missing delivery observations, six in April and one in May. Their values remain blank. Questions for the source owner include whether all orders in the period were exported, whether returned status reflects order-date or later events, and whether absent delivery measurements cluster by channel. These questions address coverage and interpretation, beyond format checks. 3. Prepare data with an auditable correction log Step | Records | Explanation | Raw extract | 46 | Includes two excess records | Exact duplicate removal | 45 | One excess A004 copy | Conflicting duplicate resolution | 44 | Keep verified store record for Y007 | Field corrections | 44 | Changes fields without removing orders | Completed population | 40 | Exclude two returned and two cancelled orders | There are 44 retained IDs and no duplicate IDs in the prepared output. A005 is web, A008 is 2026-04-08, A009 is 110 EUR, Y003 is store, Y005 is 190 EUR and Y006 is four delivery days. The downloadable reference is the comparison target, not permission to invent a generic automated correction rule. The worked Python file independently demonstrates the key-based reconstruction and verifies every prepared field. Leave all 11 reference delivery blanks intact: seven belong to completed orders and four to excluded statuses. In the raw extract there are 12 blank delivery records because duplicated A004 also has no measurement. Count differences here reflect duplicate resolution, not newly discovered delivery data. A third conflicting Y007 row should reopen an exception. Do not select it merely because it arrives later in the CSV. Retain the evidence and request a trusted source record or timestamp. If Y007’s channel cannot be settled, report the limitation in affected channel summaries rather than hiding it behind a file-order rule. 4. Explore distributions without deleting inconvenient values Measure | Completed population | Count / total | 40 / €6,320 | Mean / median | €158 / €110 | Q1 / Q3 (inclusive) | €100 / €180 | IQR / lower fence / upper fence | €80 / −€20 / €300 | Flagged record | A020, €1,000, verified | Without A020 | 39 orders, €5,320, mean €136.41 | The frequency counts are 7, 14, 18, 0 and 1 respectively. They sum to 40. Count values equal to €100 in the second bin, €200 in the fourth and €1,000 in the fifth. The 20th and 21st sorted completed values are both €110. Inclusive quartiles give positions 9.75 and 29.25 in zero-based indexing; the neighbouring values at those positions are €100 and €180 respectively. Calculate IQR as 180 − 100 = 80 and the upper fence as 180 + 1.5 × 80 = 300. The €1,000 order lies above the fence and is supported by the reference, so a defensible answer retains it and discusses concentration. A baseline mean of €158 and an exclusion mean of €136.41 describe different populations. Do not substitute the lower scenario into the original monthly totals. 5. Explain an aggregate trend using segments and weights Month / channel | Orders | Value EUR | AOV EUR | Order share | April / web | 18 | 1800 | 100 | 90% | April / store | 2 | 1100 | 550 | 10% | May / web | 2 | 180 | 90 | 10% | May / store | 18 | 3240 | 180 | 90% | April actual AOV = (18 × 100 + 2 × 550) / 20 = 145 EUR May actual AOV = (2 × 90 + 18 × 180) / 20 = 171 EUR April equal-mix = 0.5 × 100 + 0.5 × 550 = 325 EUR May equal-mix = 0.5 × 90 + 0.5 × 180 = 135 EURThe overall mean is weighted by orders, not by channel count or share of monetary value. AOV rises by (171 − 145) / 145 = 17.93%, while web falls by 10% and store by 67.27%. Store’s share rises from 10% to 90%, an 80 percentage-point change. Report the large store-baseline uncertainty: April store has n=2 and includes A020 at €1,000. The equal-mix results are scenarios. They help reveal sensitivity to composition but do not replace the observed AOVs or justify reallocating a budget. A defensible interpretation is that the supplied extract shows a higher aggregate value associated with a different channel composition, with sparse, outlier-sensitive segment baselines. It does not establish the initiative’s causal effect. 6. Keep missingness, coverage and denominators visible Measure | April | May | Eligible completed orders | 20 | 20 | Observed / missing durations | 14 / 6 | 19 / 1 | Observed coverage | 70% | 95% | Observed duration sum | 51 days | 67 days | Observed mean | 3.6429 days | 3.5263 days | Incorrect zero-filled mean | 2.55 days | 3.35 days | Returned / all statuses | 1/22 = 4.55% | 1/22 = 4.55% | Returned / (completed + returned) | 1/21 = 4.76% | 1/21 = 4.76% | Keep missing durations excluded from the observed-duration denominator and visible in the coverage denominator. All seven missing completed measurements belong to web orders. This is evidence of a missingness pattern, not permission to impute channel means or a demonstration of its cause. The four returned/cancelled blank durations belong outside the stated completed-delivery comparison. An appropriate conclusion is: “Observed completed-order delivery durations average 3.64 days for 14/20 orders in April and 3.53 for 19/20 in May. Different coverage, concentrated missingness and a small teaching extract prevent an all-order improvement claim.” Status proportions also require careful labels: a share of supplied order statuses does not measure customer behaviour over a defined follow-up window. 7. Deliver a reproducible exploratory evidence pack Worked decision note In the supplied April–May 2026 extract, completed-order value increased from €2,900 to €3,420, or 17.9%, with 20 completed orders in each month. Overall AOV rose from €145 to €171, but web AOV fell from €100 to €90 and store AOV from €550 to €180. Store’s order share changed from 10% to 90%. April’s store baseline has only two orders and includes verified order A020 at €1,000, so the comparison is sensitive to composition and a large order. The prepared dataset resolves two excess records and six field issues using declared rules or the supplied reference. Delivery means describe only 14/20 April and 19/20 May completed orders; the missing observations prevent an all-order service claim. Retain the verified large order in baseline results and report exclusions separately. Obtain comparable periods, channel-context evidence and costs before using this sample to justify expansion; it does not establish profitability or causation. Review the supporting evidence The monthly and channel result files should reconcile exactly to your prepared data. Show the 44 retained IDs, 40 completed orders, €6,320 combined baseline, €158 combined mean and €110 median. The optional worked script checks these relationships and prints the sensitivity and coverage calculations. Its assertions detect this fixture’s documented defects; they are not a general-purpose production cleaning service. A complete submission earns two points in each rubric category. If the counts match but Y007’s channel was chosen arbitrarily, preparation/provenance is not complete. If the totals match but delivery blanks were filled with zeros, missingness/denominators is materially wrong. If a graphic omits group counts and claims the initiative caused growth, communication needs revision. Use a changed input on a disposable copy to check that your calculations update or fail clearly rather than continuing to display a cached answer.