=== Report 1: Corrected categorization of all 291 active-bucket orphan subscription-delivery orders (written by Bea) === Engineer's note: The CASE in the `classified` CTE is exhaustive and mutually exclusive over the full active-bucket orphan population (no inner join drops rows), so the three category order counts are guaranteed by construction to sum to the full bucket (expected 291); please have the analyst confirm that total against last week's population count. One caveat: subscriptions have no separate status-history table, so "window coverage" for the fell-between/unexplained split uses only each subscription row's own started_at/ended_at bounds, not a time-varying status trail. REPORT: Corrected categorization of all 291 active-bucket orphan subscription-delivery orders QUESTION: With a query that doesn't silently drop unmatched orders, how do all 291 orphan orders (customer's most-recent subscription status = 'active') actually break down: preceded their first subscription, fell between two subscription stints, or genuinely unexplained? category | order_count | pct_of_bucket | distinct_customers | total_discount_eur ------------+-------------+---------------+--------------------+------------------- unexplained | 291 | 100.0 | 60 | 11746.39 (1 rows) === Report 2: Incrementality re-cut: order value matched by basket size (item count), 2025 (written by Felix) === Engineer's note: Promotion names are matched both via order_discounts.discount_id directly and via order_discounts.coupon_id -> coupons.discount_id, since either path can carry a named promotion; "no row in order_discounts" is interpreted as no discount row at all (not just these four) for the non-discounted side. All 5 buckets are always printed (COALESCEd to 0/NULL) even if empty for a given bucket. REPORT: Incrementality re-cut: order value matched by basket size (item count), 2025 QUESTION: Controlling for how many distinct items are in the order, do discounted orders (our four main marketing promotions) still show a higher average order value than non-discounted orders, or does the earlier lift mostly reflect that discounts land on already-bigger baskets? item_count_bucket | discounted_order_count | avg_discounted_subtotal_eur_pre_discount | nondiscounted_order_count | avg_nondiscounted_subtotal_eur | lift_eur | lift_pct ------------------+------------------------+------------------------------------------+---------------------------+--------------------------------+----------+--------- 1 item | 820 | 219.14 | 6079 | 100.93 | 118.21 | 117.11 2 items | 683 | 214.72 | 3571 | 154.43 | 60.3 | 39.05 3 items | 436 | 267.51 | 2091 | 237.53 | 29.98 | 12.62 4 items | 258 | 357.63 | 989 | 287.21 | 70.42 | 24.52 5+ items | 161 | 719.98 | 595 | 429.37 | 290.61 | 67.68 (5 rows) === Report 3: Tenant-level breakdown of the lapsed-subscriber leakage trend, by half-year (written by Ines) === Engineer's note: I reconstructed the "confirmed-leakage" population from the described rules since the original monthly trend query wasn't available to me: an orphan order is one carrying a 'Subscription delivery (prepaid)' order_discount where the customer has no subscription with status active/trialing/past_due whose started_at/ended_at window covers placed_at, and whose most-recent subscription (by started_at) has status='canceled'. Discount amounts are converted to EUR using the exchange rate as of the order's placed_at date (EUR-quoted rows default to a rate of 1 if no explicit EUR/EUR row exists). All 6 named tenants are zero-filled across all 6 half-year periods. REPORT: Tenant-level breakdown of the lapsed-subscriber leakage trend, by half-year QUESTION: Is the decline in lapsed-subscriber free-delivery leakage since late 2024 broad-based across all affected tenants, or driven by one or two tenants fixing something while others stayed flat? tenant_name | half_year | orphan_orders | distinct_customers | total_discount_eur -----------------+-----------+---------------+--------------------+------------------- Feather & Fin | 2023-H1 | 2 | 2 | 135.68 Feather & Fin | 2023-H2 | 2 | 2 | 113.74 Feather & Fin | 2024-H1 | 24 | 6 | 412.46 Feather & Fin | 2024-H2 | 36 | 7 | 1015.47 Feather & Fin | 2025-H1 | 35 | 7 | 959.48 Feather & Fin | 2025-H2 | 4 | 2 | 92.02 Grind Collective | 2023-H1 | 33 | 8 | 720.53 Grind Collective | 2023-H2 | 64 | 14 | 1991.57 Grind Collective | 2024-H1 | 112 | 23 | 5914.3 Grind Collective | 2024-H2 | 115 | 24 | 7393.65 Grind Collective | 2025-H1 | 80 | 17 | 4994.54 Grind Collective | 2025-H2 | 46 | 12 | 3219.31 Honeycomb Larder | 2023-H1 | 32 | 9 | 1751.05 Honeycomb Larder | 2023-H2 | 46 | 14 | 1652.82 Honeycomb Larder | 2024-H1 | 118 | 23 | 5282.23 Honeycomb Larder | 2024-H2 | 111 | 22 | 5778.15 Honeycomb Larder | 2025-H1 | 76 | 17 | 4020.56 Honeycomb Larder | 2025-H2 | 27 | 7 | 1025.16 Kibblewick | 2023-H1 | 0 | 0 | 0.0 Kibblewick | 2023-H2 | 0 | 0 | 0.0 Kibblewick | 2024-H1 | 26 | 9 | 792.68 Kibblewick | 2024-H2 | 51 | 14 | 1680.51 Kibblewick | 2025-H1 | 65 | 16 | 1755.31 Kibblewick | 2025-H2 | 22 | 8 | 734.29 Saffron & Sage | 2023-H1 | 8 | 3 | 646.41 Saffron & Sage | 2023-H2 | 6 | 1 | 580.86 Saffron & Sage | 2024-H1 | 8 | 2 | 556.78 Saffron & Sage | 2024-H2 | 7 | 3 | 517.12 Saffron & Sage | 2025-H1 | 12 | 5 | 1315.03 Saffron & Sage | 2025-H2 | 5 | 2 | 659.93 The Balm Room | 2023-H1 | 1 | 1 | 118.01 The Balm Room | 2023-H2 | 6 | 1 | 696.45 The Balm Room | 2024-H1 | 6 | 1 | 862.22 The Balm Room | 2024-H2 | 1 | 1 | 91.5 The Balm Room | 2025-H1 | 1 | 1 | 178.84 The Balm Room | 2025-H2 | 0 | 0 | 0.0 (36 rows)