=== Report 1: Monthly revenue by source (written by Grace) === Engineer's note: Each of the four revenue measures is converted to EUR independently using exchange_rates matched to the event's own date and currency (orders by placed_at, payments by captured_at, payouts by paid_at, invoices by paid_at), so differences between them reflect genuine timing/definition gaps (e.g. payouts net out fees/refunds and lag payment capture) rather than conversion artifacts. 36 rows, one per month, all months present via calendar_days even if a source had zero activity. REPORT: Monthly revenue by source QUESTION: If we ask what revenue was in a given month from four different places (orders booked, payments captured, payouts settled, invoices paid), do we get the same answer, and how far apart are they? month | orders_booked_revenue_eur | payments_captured_eur | payout_gross_eur | invoice_paid_revenue_eur | refunds_eur | order_count | payment_count --------+---------------------------+-----------------------+------------------+--------------------------+-------------+-------------+-------------- 2023-01 | 91008.3 | 31637.57 | 53709.82 | 240.1 | 3393.01 | 499 | 361 2023-02 | 83045.67 | 61583.77 | 50590.75 | 30447.48 | 13034.09 | 522 | 415 2023-03 | 119694.55 | 48729.2 | 55055.62 | 11805.8 | 9083.75 | 611 | 494 2023-04 | 98534.83 | 89063.47 | 68499.6 | 53361.54 | 8376.32 | 653 | 534 2023-05 | 135971.02 | 88119.12 | 93673.67 | 34048.73 | 11213.65 | 772 | 638 2023-06 | 78438.84 | 87513.97 | 61954.44 | 51424.95 | 9932.92 | 713 | 606 2023-07 | 113754.47 | 79342.56 | 64924.71 | 40185.66 | 10670.89 | 762 | 645 2023-08 | 106351.86 | 86331.25 | 65320.68 | 44857.34 | 10041.38 | 813 | 672 2023-09 | 95570.89 | 68523.5 | 70968.13 | 26241.1 | 9028.08 | 877 | 712 2023-10 | 126934.07 | 113753.8 | 98006.93 | 43286.11 | 12739.55 | 1049 | 856 2023-11 | 249227.23 | 138897.26 | 192998.2 | 32148.67 | 16689.72 | 1787 | 1370 2023-12 | 188375.92 | 158862.82 | 172550.08 | 57431.19 | 45282.9 | 1540 | 1246 2024-01 | 169303.39 | 98061.9 | 112246.44 | 29369.94 | 28762.24 | 1275 | 1030 2024-02 | 135473.31 | 97579.58 | 112012.26 | 17026.41 | 15721.05 | 1077 | 941 2024-03 | 137254.12 | 137526.73 | 110386.35 | 66620.11 | 12224.57 | 1166 | 970 2024-04 | 161281.28 | 105686.23 | 115514.24 | 26086.24 | 16993.01 | 1194 | 978 2024-05 | 185763.86 | 133043.31 | 121296.23 | 50734.02 | 15230.21 | 1309 | 1086 2024-06 | 130547.67 | 100866.4 | 100219.73 | 42702.25 | 15315.98 | 1265 | 1032 2024-07 | 211861.18 | 125672.04 | 108894.15 | 55863.56 | 14215.29 | 1331 | 1063 2024-08 | 154838.72 | 125847.43 | 96008.55 | 66551.6 | 18128.78 | 1271 | 1047 2024-09 | 165908.69 | 184794.84 | 113343.79 | 104050.4 | 15130.15 | 1315 | 1069 2024-10 | 201478.08 | 163948.67 | 133784.95 | 74227.13 | 13860.5 | 1457 | 1190 2024-11 | 268367.57 | 181532.89 | 177317.93 | 67457.53 | 20434.5 | 2056 | 1605 2024-12 | 309476.98 | 228914.7 | 286218.09 | 69924.06 | 58827.06 | 2546 | 2044 2025-01 | 346981.34 | 219325.7 | 199172.31 | 87669.68 | 36758.42 | 1768 | 1455 2025-02 | 174721.1 | 185390.87 | 117777.21 | 94256.49 | 18604.99 | 1370 | 1162 2025-03 | 199394.65 | 166350.88 | 135070.48 | 74183.95 | 16479.69 | 1586 | 1332 2025-04 | 232332.56 | 210209.5 | 188851.76 | 69077.99 | 16429.99 | 1520 | 1263 2025-05 | 152855.42 | 128106.12 | 127570.11 | 55535.48 | 26537.35 | 1633 | 1311 2025-06 | 164239.26 | 136523.52 | 113673.02 | 65902.64 | 19509.64 | 1461 | 1213 2025-07 | 180052.83 | 172036.43 | 132602.63 | 77495.8 | 17897.73 | 1596 | 1307 2025-08 | 171818.78 | 125315.55 | 141126.23 | 47444.26 | 39838.78 | 1502 | 1229 2025-09 | 190535.95 | 152057.98 | 122449.4 | 70502.79 | 15922.5 | 1659 | 1355 2025-10 | 232741.04 | 168510.39 | 154129.95 | 74084.7 | 25748.61 | 1828 | 1455 2025-11 | 328682.7 | 227293.96 | 236843.96 | 76404.99 | 26661.72 | 2550 | 2013 2025-12 | 332982.57 | 323026.37 | 322182.01 | 79290.12 | 74611.41 | 2994 | 2754 (36 rows) === Report 2: Orders traced into captured payments (written by Ines) === Engineer's note: Captured-payment amounts are converted to EUR using the exchange rate as of their captured_at date (falling back to the order's placed_at date if captured_at is null); orders' grand_total uses the placed_at date. Months with zero qualifying orders still appear via the calendar_days spine, with all measures as 0/NULL for gap_pct. REPORT: Orders traced into captured payments QUESTION: Is there a clean, traceable link from an order to the money actually collected for it, or do orders vanish (no captured payment) or multiply (captured payments exceeding order value) along the way? month | orders_count | orders_grand_total_eur | orders_with_captured_payment_count | captured_payment_total_eur | orders_without_captured_payment_count | gap_eur | gap_pct --------+--------------+------------------------+------------------------------------+----------------------------+---------------------------------------+-----------+-------- 2023-01 | 275 | 91008.3 | 186 | 63230.42 | 89 | 27777.88 | 30.5 2023-02 | 265 | 83045.67 | 195 | 58918.04 | 70 | 24127.63 | 29.1 2023-03 | 314 | 119694.55 | 224 | 100212.61 | 90 | 19481.95 | 16.3 2023-04 | 331 | 98534.83 | 231 | 59987.32 | 100 | 38547.51 | 39.1 2023-05 | 402 | 135971.02 | 286 | 106163.21 | 116 | 29807.81 | 21.9 2023-06 | 362 | 78438.84 | 255 | 60036.91 | 107 | 18401.93 | 23.5 2023-07 | 392 | 113754.47 | 268 | 89999.97 | 124 | 23754.5 | 20.9 2023-08 | 370 | 106351.86 | 268 | 84115.43 | 102 | 22236.44 | 20.9 2023-09 | 415 | 95570.89 | 292 | 69220.7 | 123 | 26350.19 | 27.6 2023-10 | 528 | 126934.07 | 382 | 96716.31 | 146 | 30217.76 | 23.8 2023-11 | 1037 | 249227.23 | 685 | 161336.47 | 352 | 87890.75 | 35.3 2023-12 | 868 | 188375.92 | 590 | 124844.76 | 278 | 63531.16 | 33.7 2024-01 | 653 | 169303.39 | 440 | 123958.2 | 213 | 45345.19 | 26.8 2024-02 | 516 | 135473.31 | 371 | 107539.67 | 145 | 27933.64 | 20.6 2024-03 | 548 | 137254.12 | 361 | 100203.56 | 187 | 37050.57 | 27.0 2024-04 | 587 | 161281.28 | 401 | 132497.95 | 186 | 28783.33 | 17.8 2024-05 | 663 | 185763.86 | 450 | 149006.24 | 213 | 36757.63 | 19.8 2024-06 | 659 | 130547.67 | 436 | 95064.72 | 223 | 35482.96 | 27.2 2024-07 | 694 | 211861.18 | 434 | 177898.77 | 260 | 33962.41 | 16.0 2024-08 | 626 | 154838.72 | 410 | 122632.42 | 216 | 32206.3 | 20.8 2024-09 | 656 | 165908.69 | 430 | 128225.01 | 226 | 37683.68 | 22.7 2024-10 | 767 | 201478.08 | 517 | 160116.65 | 250 | 41361.43 | 20.5 2024-11 | 1177 | 268367.57 | 779 | 185993.69 | 398 | 82373.88 | 30.7 2024-12 | 1517 | 309476.98 | 1035 | 211290.01 | 482 | 98186.97 | 31.7 2025-01 | 900 | 346981.34 | 600 | 293029.21 | 300 | 53952.14 | 15.5 2025-02 | 706 | 174721.1 | 489 | 144461.25 | 217 | 30259.85 | 17.3 2025-03 | 811 | 199394.65 | 543 | 160832.69 | 268 | 38561.96 | 19.3 2025-04 | 777 | 232332.56 | 494 | 188455.63 | 283 | 43876.92 | 18.9 2025-05 | 865 | 152855.42 | 567 | 112335.77 | 298 | 40519.65 | 26.5 2025-06 | 782 | 164239.26 | 508 | 124764.35 | 274 | 39474.91 | 24.0 2025-07 | 894 | 180052.83 | 608 | 148728.79 | 286 | 31324.05 | 17.4 2025-08 | 781 | 171818.78 | 503 | 140972.86 | 278 | 30845.92 | 18.0 2025-09 | 882 | 190535.95 | 571 | 148873.74 | 311 | 41662.21 | 21.9 2025-10 | 986 | 232741.04 | 636 | 166776.96 | 350 | 65964.08 | 28.3 2025-11 | 1517 | 328682.7 | 1001 | 216334.52 | 516 | 112348.17 | 34.2 2025-12 | 1756 | 332982.57 | 1421 | 252419.74 | 335 | 80562.83 | 24.2 (36 rows) === Report 3: Payout reconciliation to gross, fees and refunds (written by Viktor) === Engineer's note: Payout month uses paid_at, falling back to arrival_date only when paid_at is null (36 months, 2023-01 to 2025-12, all months printed since the request's range is within the 50-row limit). Refunds are matched by calendar month only (not joined to the specific payout), since payment_refunds has no payout linkage in the schema - a mismatch in net_minus_refunds_diff_eur could reflect either true timing lag between refund processing and payout settlement, or an actual accounting gap. REPORT: Payout reconciliation to gross, fees and refunds QUESTION: Do payouts consistently reconcile as net = gross minus fee, and do refunds actually reduce what we pay out, or could refunds be double-counted or missing from the payout math? month | payout_gross_eur | payout_fee_eur | payout_net_eur | net_check_diff_eur | refunds_processed_eur | net_minus_refunds_diff_eur --------+------------------+----------------+----------------+--------------------+-----------------------+--------------------------- 2023-01 | 53709.82 | 1406.58 | 50121.03 | -2182.2 | 3393.01 | 1210.8 2023-02 | 50590.75 | 1342.13 | 39422.43 | -9826.2 | 13034.09 | 3207.89 2023-03 | 55055.62 | 1485.94 | 43280.45 | -10289.23 | 9083.75 | -1205.48 2023-04 | 68499.6 | 1982.09 | 56262.5 | -10255.01 | 8376.32 | -1878.69 2023-05 | 93673.67 | 2481.27 | 81170.17 | -10022.22 | 11213.65 | 1191.44 2023-06 | 61954.44 | 1677.97 | 49455.12 | -10821.35 | 9932.92 | -888.43 2023-07 | 64924.71 | 1766.4 | 53455.56 | -9702.76 | 10670.89 | 968.13 2023-08 | 65320.68 | 1750.02 | 52531.55 | -11039.12 | 10041.38 | -997.74 2023-09 | 70968.13 | 1916.18 | 60613.58 | -8438.36 | 9028.08 | 589.72 2023-10 | 98006.93 | 2531.86 | 82801.85 | -12673.22 | 12739.55 | 66.32 2023-11 | 192998.2 | 5184.93 | 172017.88 | -15795.39 | 16689.72 | 894.33 2023-12 | 172550.08 | 4526.79 | 124617.61 | -43405.67 | 45282.9 | 1877.23 2024-01 | 112246.44 | 2988.79 | 81279.5 | -27978.14 | 28762.24 | 784.1 2024-02 | 112012.26 | 3263.45 | 90349.8 | -18399.01 | 15721.05 | -2677.97 2024-03 | 110386.35 | 2972.1 | 95208.35 | -12205.89 | 12224.57 | 18.67 2024-04 | 115514.24 | 3242.26 | 94708.84 | -17563.14 | 16993.01 | -570.13 2024-05 | 121296.23 | 3490.98 | 102869.58 | -14935.68 | 15230.21 | 294.53 2024-06 | 100219.73 | 2773.41 | 82652.97 | -14793.35 | 15315.98 | 522.64 2024-07 | 108894.15 | 2844.58 | 91460.3 | -14589.27 | 14215.29 | -373.97 2024-08 | 96008.55 | 2727.22 | 77582.07 | -15699.26 | 18128.78 | 2429.53 2024-09 | 113343.79 | 2991.07 | 96348.19 | -14004.52 | 15130.15 | 1125.63 2024-10 | 133784.95 | 3558.57 | 115165.74 | -15060.63 | 13860.5 | -1200.14 2024-11 | 177317.93 | 4548.44 | 152658.86 | -20110.63 | 20434.5 | 323.87 2024-12 | 286218.09 | 7526.96 | 228039.14 | -50651.98 | 58827.06 | 8175.07 2025-01 | 199172.31 | 4954.45 | 151256.63 | -42961.24 | 36758.42 | -6202.82 2025-02 | 117777.21 | 2907.62 | 95906.6 | -18963.0 | 18604.99 | -358.01 2025-03 | 135070.48 | 3567.13 | 115334.32 | -16169.04 | 16479.69 | 310.65 2025-04 | 188851.76 | 5006.79 | 169982.71 | -13862.25 | 16429.99 | 2567.74 2025-05 | 127570.11 | 3482.02 | 98002.35 | -26085.75 | 26537.35 | 451.6 2025-06 | 113673.02 | 3123.65 | 91578.07 | -18971.31 | 19509.64 | 538.33 2025-07 | 132602.63 | 3604.89 | 108283.9 | -20713.84 | 17897.73 | -2816.11 2025-08 | 141126.23 | 4276.29 | 118864.4 | -17985.54 | 39838.78 | 21853.24 2025-09 | 122449.4 | 3165.85 | 100229.52 | -19054.03 | 15922.5 | -3131.54 2025-10 | 154129.95 | 4108.16 | 130131.01 | -19890.78 | 25748.61 | 5857.83 2025-11 | 236843.96 | 6380.01 | 199899.74 | -30564.21 | 26661.72 | -3902.49 2025-12 | 322182.01 | 8609.0 | 250319.95 | -63253.07 | 74611.41 | 11358.34 (36 rows)