=== Report 1: Order-to-payment gap by order status, aged cohort (written by Felix) === Engineer's note: Grain is order status, population restricted to orders placed 2023-01-01 through 2025-06-30 inclusive as requested. EUR conversion divides each amount by the EUR→currency rate (orders by placed_at date, captured payments by captured_at date), matching the convention that exchange_rates.rate is EUR-based. REPORT: Order-to-payment gap by order status, aged cohort QUESTION: Is the 20-40% gap between order value and captured payments explained by order status (i.e. structural - many orders simply never get paid), or does it look like something else, once we only look at orders old enough that timing lag can't be the excuse? order_status | order_count | pct_of_orders | orders_grand_total_eur | orders_with_captured_payment_count | matched_captured_payment_eur | gap_eur | gap_pct -------------+-------------+---------------+------------------------+------------------------------------+------------------------------+------------+-------- fulfilled | 18540 | 91.6 | 4863934.96 | 13120 | 3792892.83 | 1071042.14 | 22.0 cancelled | 788 | 3.9 | 158380.3 | 0 | 0.0 | 158380.3 | 100.0 refunded | 923 | 4.6 | 125051.87 | 7 | 195.09 | 124856.78 | 99.8 (3 rows) === Report 2: Invoice revenue: order-linked vs subscription-only (written by Viktor) === Engineer's note: EUR-currency invoices are summed directly (no FX join needed); all other currencies convert via exchange_rates matched on invoices.currency_code and the paid_at date. Rows with a currency/date combination missing from exchange_rates (none expected for in-range data) would show as NULL in the EUR columns rather than being silently dropped. REPORT: Invoice revenue: order-linked vs subscription-only QUESTION: Is invoice revenue additional revenue on top of what we already count in orders, or does some of it double-count order revenue we've already booked? month | invoice_paid_total_eur | invoice_order_linked_eur | invoice_subscription_only_eur | invoice_other_eur | pct_order_linked --------+------------------------+--------------------------+-------------------------------+-------------------+----------------- 2023-01 | 1208.67 | 0.0 | 1208.67 | 0.0 | 0.0 2023-02 | 57088.25 | 55586.86 | 1501.39 | 0.0 | 97.4 2023-03 | 92309.45 | 90098.34 | 2211.11 | 0.0 | 97.6 2023-04 | 87161.08 | 84076.61 | 3084.47 | 0.0 | 96.5 2023-05 | 107059.07 | 103953.39 | 3105.68 | 0.0 | 97.1 2023-06 | 125326.49 | 121785.3 | 3541.19 | 0.0 | 97.2 2023-07 | 78309.79 | 74767.17 | 3542.62 | 0.0 | 95.5 2023-08 | 95262.6 | 91291.37 | 3971.23 | 0.0 | 95.8 2023-09 | 71634.92 | 67326.75 | 4308.17 | 0.0 | 94.0 2023-10 | 81115.25 | 75548.86 | 5566.4 | 0.0 | 93.1 2023-11 | 119071.89 | 114019.55 | 5052.34 | 0.0 | 95.8 2023-12 | 106958.11 | 100806.09 | 6152.02 | 0.0 | 94.2 2024-01 | 90433.93 | 84877.57 | 5556.36 | 0.0 | 93.9 2024-02 | 101897.25 | 95804.11 | 6093.14 | 0.0 | 94.0 2024-03 | 116331.35 | 109527.5 | 6803.86 | 0.0 | 94.2 2024-04 | 83682.37 | 75268.16 | 8414.21 | 0.0 | 89.9 2024-05 | 140285.73 | 133408.8 | 6876.94 | 0.0 | 95.1 2024-06 | 146918.11 | 140129.78 | 6788.33 | 0.0 | 95.4 2024-07 | 155621.07 | 147339.35 | 8281.72 | 0.0 | 94.7 2024-08 | 157117.05 | 149446.85 | 7670.21 | 0.0 | 95.1 2024-09 | 149250.08 | 141244.75 | 8005.33 | 0.0 | 94.6 2024-10 | 133982.17 | 125708.78 | 8273.39 | 0.0 | 93.8 2024-11 | 148581.38 | 140597.13 | 7984.26 | 0.0 | 94.6 2024-12 | 174971.7 | 164551.7 | 10420.0 | 0.0 | 94.0 2025-01 | 131878.61 | 122947.74 | 8930.88 | 0.0 | 93.2 2025-02 | 174177.04 | 164555.01 | 9622.02 | 0.0 | 94.5 2025-03 | 196178.01 | 185889.65 | 10288.36 | 0.0 | 94.8 2025-04 | 197968.6 | 187140.39 | 10828.22 | 0.0 | 94.5 2025-05 | 154986.16 | 145026.16 | 9960.0 | 0.0 | 93.6 2025-06 | 170275.21 | 159622.58 | 10652.64 | 0.0 | 93.7 2025-07 | 139537.62 | 129789.15 | 9748.48 | 0.0 | 93.0 2025-08 | 135141.56 | 124271.46 | 10870.11 | 0.0 | 92.0 2025-09 | 151817.96 | 141333.26 | 10484.71 | 0.0 | 93.1 2025-10 | 170607.25 | 159984.18 | 10623.06 | 0.0 | 93.8 2025-11 | 213438.12 | 201408.36 | 12029.77 | 0.0 | 94.4 2025-12 | 246816.09 | 231688.26 | 15127.83 | 0.0 | 93.9 (36 rows) === Report 3: Payout net formula check via payment-linked refunds (written by Ines) === Engineer's note: Months with zero payouts show 0s with diff_pct left NULL (division by zero undefined). EUR amounts are passed through unconverted since exchange_rates may not carry a self-referential EUR/EUR row; all other currencies are converted via the EUR-based daily rate on the relevant date (paid_at for payouts, processed_at for refunds). REPORT: Payout net formula check via payment-linked refunds QUESTION: Once refunds are matched to the actual payments inside each payout (not just by calendar month), does net = gross - fee - refunds hold cleanly, confirming refunds are reflected once and only once in what we pay merchants? month | payout_gross_eur | payout_fee_eur | payout_net_eur | linked_refunds_eur | implied_net_eur | diff_eur | diff_pct --------+------------------+----------------+----------------+--------------------+-----------------+-----------+--------- 2023-01 | 94732.34 | 2358.26 | 89903.02 | 18424.65 | 73949.42 | 15953.6 | 17.7 2023-02 | 123011.03 | 2769.23 | 109079.28 | 11395.32 | 108846.49 | 232.79 | 0.2 2023-03 | 149703.05 | 3435.59 | 133587.23 | 19926.41 | 126341.05 | 7246.18 | 5.4 2023-04 | 138160.74 | 3227.3 | 112840.52 | 20267.35 | 114666.08 | -1825.57 | -1.6 2023-05 | 184243.19 | 4105.88 | 166767.36 | 16655.13 | 163482.17 | 3285.19 | 2.0 2023-06 | 116824.01 | 2853.18 | 99170.22 | 12630.82 | 101340.01 | -2169.8 | -2.2 2023-07 | 123832.57 | 2904.58 | 106823.44 | 12360.29 | 108567.71 | -1744.27 | -1.6 2023-08 | 146639.74 | 3253.97 | 129619.57 | 13411.78 | 129973.99 | -354.43 | -0.3 2023-09 | 179187.78 | 3957.2 | 161373.01 | 16827.87 | 158402.71 | 2970.3 | 1.8 2023-10 | 161532.9 | 3908.69 | 139980.97 | 20510.11 | 137114.1 | 2866.87 | 2.0 2023-11 | 374749.1 | 9037.78 | 337886.81 | 65953.12 | 299758.2 | 38128.61 | 11.3 2023-12 | 315250.22 | 7209.32 | 255711.71 | 40591.28 | 267449.62 | -11737.91 | -4.6 2024-01 | 214636.14 | 5121.57 | 158334.74 | 28614.61 | 180899.95 | -22565.21 | -14.3 2024-02 | 212084.59 | 5539.86 | 181653.19 | 16154.98 | 190389.75 | -8736.56 | -4.8 2024-03 | 304127.23 | 8182.78 | 279016.0 | 24766.99 | 271177.46 | 7838.55 | 2.8 2024-04 | 208772.66 | 5249.7 | 177512.11 | 24153.48 | 179369.48 | -1857.37 | -1.0 2024-05 | 211560.68 | 5641.83 | 185379.99 | 19649.83 | 186269.03 | -889.04 | -0.5 2024-06 | 192822.09 | 4767.01 | 166258.72 | 20773.68 | 167281.39 | -1022.67 | -0.6 2024-07 | 227390.96 | 5620.38 | 199746.14 | 39117.94 | 182652.63 | 17093.51 | 8.6 2024-08 | 189437.39 | 4710.35 | 163007.2 | 17924.01 | 166803.03 | -3795.83 | -2.3 2024-09 | 236448.09 | 5717.95 | 213008.92 | 18183.84 | 212546.29 | 462.63 | 0.2 2024-10 | 264461.29 | 6290.06 | 228906.86 | 35648.18 | 222523.05 | 6383.82 | 2.8 2024-11 | 317582.24 | 7443.25 | 279036.62 | 53387.35 | 256751.64 | 22284.98 | 8.0 2024-12 | 510885.63 | 11829.11 | 424628.34 | 91959.47 | 407097.05 | 17531.29 | 4.1 2025-01 | 336997.3 | 7925.21 | 253932.01 | 33021.1 | 296050.99 | -42118.98 | -16.6 2025-02 | 280023.05 | 6459.16 | 243182.77 | 16981.73 | 256582.16 | -13399.39 | -5.5 2025-03 | 255837.34 | 5985.68 | 223413.62 | 35238.5 | 214613.16 | 8800.46 | 3.9 2025-04 | 340937.07 | 7828.37 | 304687.99 | 32404.13 | 300704.57 | 3983.42 | 1.3 2025-05 | 334528.07 | 7178.24 | 278317.0 | 56865.11 | 270484.73 | 7832.27 | 2.8 2025-06 | 229136.55 | 5713.03 | 184551.27 | 23587.61 | 199835.92 | -15284.65 | -8.3 2025-07 | 239376.66 | 6028.64 | 203551.42 | 24232.38 | 209115.63 | -5564.21 | -2.7 2025-08 | 259058.79 | 6808.71 | 225922.27 | 48273.05 | 203977.03 | 21945.24 | 9.7 2025-09 | 259147.15 | 5871.99 | 227184.81 | 34190.15 | 219085.01 | 8099.8 | 3.6 2025-10 | 351845.4 | 8090.78 | 304170.97 | 50124.36 | 293630.26 | 10540.71 | 3.5 2025-11 | 471485.44 | 11427.35 | 412663.97 | 66072.53 | 393985.55 | 18678.41 | 4.5 2025-12 | 614615.16 | 14266.05 | 493277.91 | 71214.53 | 529134.58 | -35856.67 | -7.3 (36 rows)