=== Report 1: Subscription-delivery discount orders with no overlapping active subscription (written by Viktor) === Engineer's note: For the lapsed/no-subscription classification, each orphan customer's "most recent subscription" is taken as their single latest subscription record (by started_at) for that tenant, and the 30-day lapse window is measured against that customer's most recent orphan-order date (customers can have several orphan orders). Customers who are neither lapsed nor no-record fall into an implicit 'other' (true unexplained) bucket, recoverable as 100% minus the two reported percentages. REPORT: Subscription-delivery discount orders with no overlapping active subscription QUESTION: Among the fully-comped 'Subscription delivery (prepaid)' orders, are the ones with no active subscription at the time concentrated in a few customers, or traceable to subscriptions that had just lapsed — or are they true unexplained giveaways? tenant_name | orphan_order_count | orphan_pct_of_discount_orders | distinct_orphan_customers | max_orphan_per_customer | pct_lapsed_subscriber | pct_no_subscription_record | total_discount_amount_eur -----------------+--------------------+-------------------------------+---------------------------+-------------------------+-----------------------+----------------------------+-------------------------- Grind Collective | 167 | 25.7 | 28 | 19 | 0.0 | 0.0 | 9308.11 Honeycomb Larder | 127 | 35.9 | 24 | 21 | 0.0 | 0.0 | 5978.91 Kibblewick | 100 | 30.7 | 21 | 12 | 0.0 | 0.0 | 3080.73 Feather & Fin | 49 | 46.2 | 10 | 8 | 0.0 | 0.0 | 1796.63 Saffron & Sage | 17 | 39.5 | 5 | 8 | 0.0 | 0.0 | 1974.96 The Balm Room | 1 | 6.3 | 1 | 1 | 0.0 | 0.0 | 178.84 (6 rows) === Report 2: Corrected incrementality read: pre-discount basket size on top marketing discounts, 2025 (written by Felix) === Engineer's note: I could not re-derive the exact "top 15 by discount_amount_eur" list from the prior report (no data access in dry runs), so I selected discount rows by exact name match on the four named campaigns (discounts.name IN (...)), which naturally includes every tenant/coupon-variant row sharing those names and excludes Volume tier and Subscription delivery as requested. Added "customers_with_no_comparison_order" as an explicit count column so the analyst sees that figure directly rather than only in a note. REPORT: Corrected incrementality read: pre-discount basket size on top marketing discounts, 2025 QUESTION: Using basket size before the discount is applied (subtotal), do customers actually buy bigger baskets when using our top marketing discount codes, or was the earlier post-discount comparison just showing the mechanical effect of the discount itself? discount_name | coupon_code | discounted_order_count | distinct_customers | avg_discounted_subtotal_eur | avg_comparison_subtotal_eur | subtotal_lift_eur | subtotal_lift_pct | pct_customers_with_comparison_order | customers_with_no_comparison_order ------------------+-------------+------------------------+--------------------+-----------------------------+-----------------------------+-------------------+-------------------+-------------------------------------+----------------------------------- January Sale 2025 | automatic | 1 | 1 | 7.5 | 149.02 | -141.52 | -95.0 | 100.0 | 0 Black Friday 2025 | BF2520 | 2 | 2 | 203.52 | 3433.38 | -3229.86 | -94.1 | 50.0 | 1 Black Friday 2025 | BF2515 | 1 | 1 | 64.36 | 627.23 | -562.87 | -89.7 | 100.0 | 0 Summer Sale 2025 | automatic | 1 | 1 | 15.25 | 103.72 | -88.47 | -85.3 | 100.0 | 0 Welcome 10% off | WELCOME10 | 1 | 1 | 6.24 | 37.42 | -31.18 | -83.3 | 100.0 | 0 Welcome 10% off | WELCOME10 | 4 | 4 | 11.66 | 54.79 | -43.14 | -78.7 | 100.0 | 0 Welcome 10% off | WELCOME10 | 2 | 2 | 78.48 | 340.65 | -262.17 | -77.0 | 50.0 | 1 Welcome 10% off | WELCOME10 | 6 | 6 | 68.12 | 251.57 | -183.45 | -72.9 | 66.7 | 2 Summer Sale 2025 | automatic | 4 | 4 | 93.57 | 305.02 | -211.45 | -69.3 | 100.0 | 0 January Sale 2025 | automatic | 2 | 2 | 45.44 | 141.37 | -95.93 | -67.9 | 100.0 | 0 January Sale 2025 | automatic | 22 | 21 | 366.38 | 1114.62 | -748.24 | -67.1 | 57.1 | 9 Black Friday 2025 | BF2530 | 4 | 3 | 32.21 | 84.69 | -52.48 | -62.0 | 100.0 | 0 Summer Sale 2025 | automatic | 1 | 1 | 6.46 | 16.73 | -10.27 | -61.4 | 100.0 | 0 Black Friday 2025 | BF2515 | 5 | 5 | 89.97 | 219.26 | -129.29 | -59.0 | 60.0 | 2 Black Friday 2025 | BF2515 | 2 | 2 | 21.64 | 51.9 | -30.26 | -58.3 | 100.0 | 0 Welcome 10% off | WELCOME10 | 3 | 3 | 200.97 | 462.74 | -261.77 | -56.6 | 66.7 | 1 Welcome 10% off | WELCOME10 | 2 | 2 | 14.71 | 32.26 | -17.55 | -54.4 | 100.0 | 0 Welcome 10% off | WELCOME10 | 3 | 3 | 452.34 | 962.16 | -509.82 | -53.0 | 100.0 | 0 Welcome 10% off | WELCOME10 | 2 | 2 | 326.18 | 692.15 | -365.97 | -52.9 | 50.0 | 1 Summer Sale 2025 | automatic | 2 | 2 | 74.72 | 157.55 | -82.83 | -52.6 | 100.0 | 0 Summer Sale 2025 | automatic | 5 | 3 | 77.62 | 162.57 | -84.95 | -52.3 | 100.0 | 0 January Sale 2025 | automatic | 3 | 2 | 117.6 | 232.44 | -114.84 | -49.4 | 50.0 | 1 Black Friday 2025 | BF2520 | 6 | 5 | 197.71 | 360.44 | -162.74 | -45.1 | 60.0 | 2 Welcome 10% off | WELCOME10 | 2 | 2 | 28.67 | 52.06 | -23.4 | -44.9 | 50.0 | 1 Welcome 10% off | WELCOME10 | 5 | 5 | 59.52 | 107.76 | -48.24 | -44.8 | 60.0 | 2 Summer Sale 2025 | automatic | 8 | 8 | 156.01 | 271.78 | -115.76 | -42.6 | 87.5 | 1 Welcome 10% off | WELCOME10 | 8 | 8 | 76.83 | 128.7 | -51.87 | -40.3 | 37.5 | 5 Black Friday 2025 | BF2515 | 8 | 8 | 78.28 | 129.0 | -50.71 | -39.3 | 62.5 | 3 Black Friday 2025 | BF2525 | 6 | 6 | 141.02 | 228.03 | -87.01 | -38.2 | 100.0 | 0 Welcome 10% off | WELCOME10 | 3 | 3 | 57.8 | 93.18 | -35.38 | -38.0 | 100.0 | 0 Welcome 10% off | WELCOME10 | 31 | 31 | 376.32 | 602.82 | -226.5 | -37.6 | 25.8 | 23 Welcome 10% off | WELCOME10 | 8 | 8 | 92.06 | 142.61 | -50.56 | -35.5 | 37.5 | 5 Welcome 10% off | WELCOME10 | 5 | 5 | 434.97 | 666.93 | -231.96 | -34.8 | 20.0 | 4 January Sale 2025 | automatic | 3 | 3 | 128.21 | 194.96 | -66.75 | -34.2 | 100.0 | 0 Black Friday 2025 | BF2520 | 3 | 3 | 123.27 | 177.7 | -54.43 | -30.6 | 66.7 | 1 Welcome 10% off | WELCOME10 | 1 | 1 | 92.49 | 131.95 | -39.46 | -29.9 | 100.0 | 0 January Sale 2025 | automatic | 8 | 7 | 135.92 | 193.46 | -57.54 | -29.7 | 85.7 | 1 Black Friday 2025 | BF2530 | 3 | 3 | 64.11 | 90.81 | -26.7 | -29.4 | 66.7 | 1 Black Friday 2025 | BF2530 | 8 | 6 | 43.89 | 61.02 | -17.12 | -28.1 | 100.0 | 0 Summer Sale 2025 | automatic | 5 | 4 | 101.5 | 138.5 | -36.99 | -26.7 | 75.0 | 1 Black Friday 2025 | BF2525 | 7 | 5 | 72.44 | 98.63 | -26.19 | -26.6 | 100.0 | 0 Summer Sale 2025 | automatic | 8 | 7 | 144.85 | 195.13 | -50.28 | -25.8 | 57.1 | 3 January Sale 2025 | automatic | 2 | 2 | 49.33 | 66.3 | -16.97 | -25.6 | 100.0 | 0 Welcome 10% off | WELCOME10 | 15 | 15 | 77.33 | 103.64 | -26.31 | -25.4 | 46.7 | 8 Welcome 10% off | WELCOME10 | 3 | 3 | 36.71 | 47.41 | -10.71 | -22.6 | 66.7 | 1 Welcome 10% off | WELCOME10 | 3 | 3 | 192.79 | 248.85 | -56.06 | -22.5 | 66.7 | 1 Black Friday 2025 | BF2525 | 2 | 2 | 77.49 | 99.71 | -22.22 | -22.3 | 100.0 | 0 Welcome 10% off | WELCOME10 | 4 | 4 | 130.91 | 165.52 | -34.62 | -20.9 | 50.0 | 2 Black Friday 2025 | BF2515 | 1 | 1 | 157.81 | 198.75 | -40.94 | -20.6 | 100.0 | 0 Black Friday 2025 | BF2525 | 2 | 2 | 203.36 | 243.75 | -40.4 | -16.6 | 100.0 | 0 (219 rows, first 50 printed) === Report 3: Return and refund rates on discounted orders vs. baseline, 2025 (written by Omar) === Engineer's note: I recomputed the top-15 discount set within this query (2025 discount_amount_eur from order_discounts, excluding discounts named 'Subscription delivery') since the prior night's exact ranking query wasn't available to me here; if that differs from the original lift-analysis set please flag it and I'll re-run against the exact 15 discount_ids. Coupon code is taken as the first coupon linked to the discount (or 'automatic' if none). REPORT: Return and refund rates on discounted orders vs. baseline, 2025 QUESTION: Do orders placed under our top discounts return or get refunded at a higher rate than normal, meaning the real cost is higher than the discount line alone suggests? discount_name | coupon_code | order_count | orders_with_rma | return_rate_pct | total_refunded_eur | refund_pct_of_discount_cost | baseline_return_rate_pct | return_rate_lift_pp --------------------------------+-------------+-------------+-----------------+-----------------+--------------------+-----------------------------+--------------------------+-------------------- Welcome 10% off | WELCOME10 | 4 | 1 | 25.0 | 157.79 | 4.16 | 3.57 | 21.43 January Sale 2025 | automatic | 124 | 53 | 42.74 | 4490.24 | 50.34 | 33.21 | 9.53 Volume tier 10+ | automatic | 38 | 4 | 10.53 | 1144.28 | 14.79 | 3.57 | 6.95 Volume tier 10+ | automatic | 102 | 10 | 9.8 | 3442.67 | 35.7 | 3.13 | 6.68 Volume tier 10+ | automatic | 104 | 8 | 7.69 | 18050.36 | 161.71 | 1.89 | 5.81 Summer Sale 2025 | automatic | 146 | 58 | 39.73 | 4415.98 | 53.47 | 37.31 | 2.42 Welcome 10% off | WELCOME10 | 260 | 103 | 39.62 | 12243.62 | 188.81 | 37.31 | 2.31 Subscription delivery (prepaid) | automatic | 43 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 Subscription delivery (prepaid) | automatic | 650 | 0 | 0.0 | 0.0 | 0.0 | 1.05 | -1.05 Subscription delivery (prepaid) | automatic | 326 | 0 | 0.0 | 0.0 | 0.0 | 1.05 | -1.05 Subscription delivery (prepaid) | automatic | 354 | 0 | 0.0 | 0.0 | 0.0 | 2.97 | -2.97 Volume tier 50+ | automatic | 15 | 0 | 0.0 | 0.0 | 0.0 | 3.13 | -3.13 Welcome 10% off | WELCOME10 | 7 | 0 | 0.0 | 0.0 | 0.0 | 3.13 | -3.13 Summer Sale 2025 | automatic | 24 | 4 | 16.67 | 404.73 | 11.34 | 21.52 | -4.85 Subscription delivery (prepaid) | automatic | 106 | 0 | 0.0 | 0.0 | 0.0 | 5.05 | -5.05 (15 rows)