Stripe Revenue Recognition - Instant Demo
Turn a Stripe invoice export into the month-end entries: every line spread by day over its service period, billed vs earned by month, the deferred revenue roll-forward, the journal entry, and a tie-out to the invoices.
9 steps · shared by Klajdi · September 21, 2026
No data is shared in this template. It contains only the recipe — column names and SQL logic. When you run it, your data is processed in your own browser and never leaves your machine.
What data it expects
Stripe invoice export (CSV)
Invoice · VARCHARCustomer · VARCHARAmount · VARCHARDate (UTC) · VARCHARPeriod Start (UTC) · VARCHARPeriod End (UTC) · VARCHAR
Connect QuickBooks Online or drop a CSV / Excel export with a similar layout — the AI adapts the workflow if your columns differ.
How it works — every step, readable
- 01Sample Stripe invoice export - a small software company (sample)appends result sets (e.g. a TOTAL row)
SELECT * FROM (VALUES ('il_1001', 'in_5001', 'NWB-2001', 'cus_A1', 'Alder Freight Co', 'Workspace plan - monthly', 'usd', '1200.00', '2025-09-03 00:00', '2025-09-03 00:00', '2025-10-03 00:00', 'false', 'paid'), ('il_1002', 'in_5002', 'NWB-2002', 'cus_A1', 'Alder Freight Co', 'Workspace plan - monthly', 'usd', '1200.00', '2025-10-03 00:00', '2025-10-03 00:00', '2025-11-03 00:00', 'false', 'paid'), ('il_1003', 'in_5003', 'NWB-2003', 'cus_A1', 'Alder Freight Co', 'Workspace plan - monthly', 'usd', '1200.00', '2025-11-03 00:00', '2025-11-03 00:00', '2025-12-03 00:00', 'false', 'paid'), ('il_1004', 'in_5004', 'NWB-2004', 'cus_A1', 'Alder Freight Co', 'Workspace plan - monthly', 'usd', '1200.00', '2025-12-03 00:00', '2025-12-03 00:00', '2026-01-03 00:00', 'false', 'paid'), ('il_1005', 'in_5005', 'NWB-2005', 'cus_A1', 'Alder Freight Co', 'Workspace plan - monthly', 'usd', '1200.00', '2026-01-03 00:00', '2026-01-03 00:00', '2026-02-03 00:00', 'false', 'paid'), ('il_1006', 'in_5006', 'NWB-2006', 'cus_A1', 'Alder Freight Co', 'Workspace plan - monthly', 'usd', '1200.00', '2026-02-03 00:00', '2026-02-03 00:00', '2026-03-03 00:00', 'false', 'paid'), ('il_1007', 'in_5007', 'NWB-2007', 'cus_A1', 'Alder Freight Co', 'Workspace plan - monthly', 'usd', '1200.00', '2026-03-03 00:00', '2026-03-03 00:00', '2026-04-03 00:00', 'false', 'paid'), ('il_1008', 'in_5008', 'NWB-2008', 'cus_A1', 'Alder Freight Co', 'Workspace plan - monthly', 'usd', '1200.00', '2026-04-03 00:00', '2026-04-03 00:00', '2026-05-03 00:00', 'false', 'paid'), ('il_1009', 'in_5009', 'NWB-2009', 'cus_A1', 'Alder Freight Co', 'Workspace plan - monthly', 'usd', '1200.00', '2026-05-03 00:00', '2026-05-03 00:00', '2026-06-03 00:00', 'false', 'paid'), ('il_1010', 'in_5010', 'NWB-2010', 'cus_A1', 'Alder Freight Co', 'Workspace plan - monthly', 'usd', '1200.00', '2026-06-03 00:00', '2026-06-03 00:00', '2026-07-03 00:00', 'false', 'paid'), ('il_1011', 'in_5011', 'NWB-2011', 'cus_A1', 'Alder Freight Co', 'Workspace plan - monthly', 'usd', '1200.00', '2026-07-03 00:00', '2026-07-03 00:00', '2026-08-03 00:00', 'false', 'paid'), ('il_1012', 'in_5012', 'NWB-2012', 'cus_A1', 'Alder Freight Co', 'Workspace plan - monthly', 'usd', '1200.00', '2026-08-03 00:00', '2026-08-03 00:00', '2026-09-03 00:00', 'false', 'paid'), ('il_1013', 'in_5013', 'NWB-2013', 'cus_B2', 'Birchline Dental Group', 'Workspace plan - monthly', 'usd', '800.00', '2025-09-07 00:00', '2025-09-07 00:00', '2025-10-07 00:00', 'false', 'paid'), ('il_1014', 'in_5014', 'NWB-2014', 'cus_B2', 'Birchline Dental Group', 'Workspace plan - monthly', 'usd', '800.00', '2025-10-07 00:00', '2025-10-07 00:00', '2025-11-07 00:00', 'false', 'paid'), ('il_1015', 'in_5015', 'NWB-2015', 'cus_B2', 'Birchline Dental Group', 'Workspace plan - monthly', 'usd', '800.00', '2025-11-07 00:00', '2025-11-07 00:00', '2025-12-07 00:00', 'false', 'paid'), ('il_1016', 'in_5016', 'NWB-2016', 'cus_B2', 'Birchline Dental Group', 'Workspace plan - monthly', 'usd', '800.00', '2025-12-07 00:00', '2025-12-07 00:00', '2026-01-07 00:00', 'false', 'paid'), ('il_1017', 'in_5017', 'NWB-2017', 'cus_B2', 'Birchline Dental Group', 'Workspace plan - monthly', 'usd', '800.00', '2026-01-07 00:00', '2026-01-07 00:00', '2026-02-07 00:00', 'false', 'paid'), ('il_1018', 'in_5018', 'NWB-2018', 'cus_B2', 'Birchline Dental Group', 'Workspace plan - monthly', 'usd', '800.00', '2026-02-07 00:00', '2026-02-07 00:00', '2026-03-07 00:00', 'false', 'paid'), ('il_1019', 'in_5019', 'NWB-2019', 'cus_B2', 'Birchline Dental Group', 'Workspace plan - monthly', 'usd', '800.00', '2026-03-07 00:00', '2026-03-07 00:00', '2026-04-07 00:00', 'false', 'paid'), ('il_1020', 'in_5020', 'NWB-2020', 'cus_B2', 'Birchline Dental Group', 'Workspace plan - monthly', 'usd', '800.00', '2026-04-07 00:00', '2026-04-07 00:00', '2026-05-07 00:00', 'false', 'paid'), ('il_1021', 'in_5021', 'NWB-2021', 'cus_B2', 'Birchline Dental Group', 'Workspace plan - monthly', 'usd', '800.00', '2026-05-07 00:00', '2026-05-07 00:00', '2026-06-07 00:00', 'false', 'paid'), ('il_1022', 'in_5022', 'NWB-2022', 'cus_B2', 'Birchline Dental Group', 'Workspace plan - monthly', 'usd', '800.00', '2026-06-07 00:00', '2026-06-07 00:00', '2026-07-07 00:00', 'false', 'paid'), ('il_1023', 'in_5023', 'NWB-2023', 'cus_B2', 'Birchline Dental Group', 'Workspace plan - monthly', 'usd', '800.00', '2026-07-07 00:00', '2026-07-07 00:00', '2026-08-07 00:00', 'false', 'paid'), ('il_1024', 'in_5024', 'NWB-2024', 'cus_B2', 'Birchline Dental Group', 'Workspace plan - monthly', 'usd', '800.00', '2026-08-07 00:00', '2026-08-07 00:00', '2026-09-07 00:00', 'false', 'paid'), ('il_1025', 'in_5025', 'NWB-2025', 'cus_C3', 'Cobalt Robotics', 'Workspace plan - monthly', 'usd', '2400.00', '2025-09-12 00:00', '2025-09-12 00:00', '2025-10-12 00:00', 'false', 'paid'), ('il_1026', 'in_5026', 'NWB-2026', 'cus_C3', 'Cobalt Robotics', 'Workspace plan - monthly', 'usd', '2400.00', '2025-10-12 00:00', '2025-10-12 00:00', '2025-11-12 00:00', 'false', 'paid'), ('il_1027', 'in_5027', 'NWB-2027', 'cus_C3', 'Cobalt Robotics', 'Workspace plan - monthly', 'usd', '2400.00', '2025-11-12 00:00', '2025-11-12 00:00', '2025-12-12 00:00', 'false', 'paid'), ('il_1028', 'in_5028', 'NWB-2028', 'cus_C3', 'Cobalt Robotics', 'Workspace plan - monthly', 'usd', '2400.00', '2025-12-12 00:00', '2025-12-12 00:00', '2026-01-12 00:00', 'false', 'paid'), ('il_1029', 'in_5029', 'NWB-2029', 'cus_C3', 'Cobalt Robotics', 'Workspace plan - monthly', 'usd', '2400.00', '2026-01-12 00:00', '2026-01-12 00:00', '2026-02-12 00:00', 'false', 'paid'), ('il_1030', 'in_5030', 'NWB-2030', 'cus_C3', 'Cobalt Robotics', 'Workspace plan - monthly', 'usd', '2400.00', '2026-02-12 00:00', '2026-02-12 00:00', '2026-03-12 00:00', 'false', 'paid'), ('il_1031', 'in_5031', 'NWB-2031', 'cus_C3', 'Cobalt Robotics', 'Workspace plan - monthly', 'usd', '2400.00', '2026-03-12 00:00', '2026-03-12 00:00', '2026-04-12 00:00', 'false', 'paid'), ('il_1032', 'in_5032', 'NWB-2032', 'cus_C3', 'Cobalt Robotics', 'Workspace plan - monthly', 'usd', '2400.00', '2026-04-12 00:00', '2026-04-12 00:00', '2026-05-12 00:00', 'false', 'paid'), ('il_1033', 'in_5033', 'NWB-2033', 'cus_C3', 'Cobalt Robotics', 'Workspace plan - monthly', 'usd', '2400.00', '2026-05-12 00:00', '2026-05-12 00:00', '2026-06-12 00:00', 'false', 'paid'), ('il_1034', 'in_5034', 'NWB-2034', 'cus_C3', 'Cobalt Robotics', 'Workspace plan - monthly', 'usd', '2400.00', '2026-06-12 00:00', '2026-06-12 00:00', '2026-07-12 00:00', 'false', 'paid'), ('il_1035', 'in_5035', 'NWB-2035', 'cus_C3', 'Cobalt Robotics', 'Workspace plan - monthly', 'usd', '2400.00', '2026-07-12 00:00', '2026-07-12 00:00', '2026-08-12 00:00', 'false', 'paid'), ('il_1036', 'in_5036', 'NWB-2036', 'cus_C3', 'Cobalt Robotics', 'Workspace plan - monthly', 'usd', '2400.00', '2026-08-12 00:00', '2026-08-12 00:00', '2026-09-12 00:00', 'false', 'paid'), ('il_1037', 'in_5037', 'NWB-2037', 'cus_D4', 'Dune Street Media', 'Workspace plan - monthly', 'usd', '500.00', '2025-09-18 00:00', '2025-09-18 00:00', '2025-10-18 00:00', 'false', 'paid'), ('il_1038', 'in_5038', 'NWB-2038', 'cus_D4', 'Dune Street Media', 'Workspace plan - monthly', 'usd', '500.00', '2025-10-18 00:00', '2025-10-18 00:00', '2025-11-18 00:00', 'false', 'paid'), ('il_1039', 'in_5039', 'NWB-2039', 'cus_D4', 'Dune Street Media', 'Workspace plan - monthly', 'usd', '500.00', '2025-11-18 00:00', '2025-11-18 00:00', '2025-12-18 00:00', 'false', 'paid'), ('il_1040', 'in_5040', 'NWB-2040', 'cus_D4', 'Dune Street Media', 'Workspace plan - monthly', 'usd', '500.00', '2025-12-18 00:00', '2025-12-18 00:00', '2026-01-18 00:00', 'false', 'paid'), ('il_1041', 'in_5041', 'NWB-2041', 'cus_D4', 'Dune Street Media', 'Workspace plan - monthly', 'usd', '500.00', '2026-01-18 00:00', '2026-01-18 00:00', '2026-02-18 00:00', 'false', 'paid'), ('il_1042', 'in_5042', 'NWB-2042', 'cus_D4', 'Dune Street Media', 'Workspace plan - monthly', 'usd', '500.00', '2026-02-18 00:00', '2026-02-18 00:00', '2026-03-18 00:00', 'false', 'paid'), ('il_1043', 'in_5043', 'NWB-2043', 'cus_D4', 'Dune Street Media', 'Workspace plan - monthly', 'usd', '500.00', '2026-03-18 00:00', '2026-03-18 00:00', '2026-04-18 00:00', 'false', 'paid'), ('il_1044', 'in_5044', 'NWB-2044', 'cus_D4', 'Dune Street Media', 'Workspace plan - monthly', 'usd', '500.00', '2026-04-18 00:00', '2026-04-18 00:00', '2026-05-18 00:00', 'false', 'paid'), ('il_1045', 'in_5045', 'NWB-2045', 'cus_D4', 'Dune Street Media', 'Workspace plan - monthly', 'usd', '500.00', '2026-05-18 00:00', '2026-05-18 00:00', '2026-06-18 00:00', 'false', 'paid'), ('il_1046', 'in_5046', 'NWB-2046', 'cus_D4', 'Dune Street Media', 'Workspace plan - monthly', 'usd', '500.00', '2026-06-18 00:00', '2026-06-18 00:00', '2026-07-18 00:00', 'false', 'paid'), ('il_1047', 'in_5047', 'NWB-2047', 'cus_D4', 'Dune Street Media', 'Workspace plan - monthly', 'usd', '500.00', '2026-07-18 00:00', '2026-07-18 00:00', '2026-08-18 00:00', 'false', 'paid'), ('il_1048', 'in_5048', 'NWB-2048', 'cus_D4', 'Dune Street Media', 'Workspace plan - monthly', 'usd', '500.00', '2026-08-18 00:00', '2026-08-18 00:00', '2026-09-18 00:00', 'false', 'paid'), ('il_1049', 'in_5049', 'NWB-2049', 'cus_E5', 'Eastbank Logistics', 'Workspace plan - monthly', 'usd', '1600.00', '2025-09-21 00:00', '2025-09-21 00:00', '2025-10-21 00:00', 'false', 'paid'), ('il_1050', 'in_5050', 'NWB-2050', 'cus_E5', 'Eastbank Logistics', 'Workspace plan - monthly', 'usd', '1600.00', '2025-10-21 00:00', '2025-10-21 00:00', '2025-11-21 00:00', 'false', 'paid'), ('il_1051', 'in_5051', 'NWB-2051', 'cus_E5', 'Eastbank Logistics', 'Workspace plan - monthly', 'usd', '1600.00', '2025-11-21 00:00', '2025-11-21 00:00', '2025-12-21 00:00', 'false', 'paid'), ('il_1052', 'in_5052', 'NWB-2052', 'cus_E5', 'Eastbank Logistics', 'Workspace plan - monthly', 'usd', '1600.00', '2025-12-21 00:00', '2025-12-21 00:00', '2026-01-21 00:00', 'false', 'paid'), ('il_1053', 'in_5053', 'NWB-2053', 'cus_E5', 'Eastbank Logistics', 'Workspace plan - monthly', 'usd', '1600.00', '2026-01-21 00:00', '2026-01-21 00:00', '2026-02-21 00:00', 'false', 'paid'), ('il_1054', 'in_5054', 'NWB-2054', 'cus_E5', 'Eastbank Logistics', 'Workspace plan - monthly', 'usd', '1600.00', '2026-02-21 00:00', '2026-02-21 00:00', '2026-03-21 00:00', 'false', 'paid'), ('il_1055', 'in_5055', 'NWB-2055', 'cus_E5', 'Eastbank Logistics', 'Workspace plan - monthly', 'usd', '1600.00', '2026-03-21 00:00', '2026-03-21 00:00', '2026-04-21 00:00', 'false', 'paid'), ('il_1056', 'in_5056', 'NWB-2056', 'cus_E5', 'Eastbank Logistics', 'Workspace plan - monthly', 'usd', '1600.00', '2026-04-21 00:00', '2026-04-21 00:00', '2026-05-21 00:00', 'false', 'paid'), ('il_1057', 'in_5057', 'NWB-2057', 'cus_E5', 'Eastbank Logistics', 'Workspace plan - monthly', 'usd', '1600.00', '2026-05-21 00:00', '2026-05-21 00:00', '2026-06-21 00:00', 'false', 'paid'), ('il_1058', 'in_5058', 'NWB-2058', 'cus_E5', 'Eastbank Logistics', 'Workspace plan - monthly', 'usd', '1600.00', '2026-06-21 00:00', '2026-06-21 00:00', '2026-07-21 00:00', 'false', 'paid'), ('il_1059', 'in_5059', 'NWB-2059', 'cus_E5', 'Eastbank Logistics', 'Workspace plan - monthly', 'usd', '1600.00', '2026-07-21 00:00', '2026-07-21 00:00', '2026-08-21 00:00', 'false', 'paid'), ('il_1060', 'in_5060', 'NWB-2060', 'cus_E5', 'Eastbank Logistics', 'Workspace plan - monthly', 'usd', '1600.00', '2026-08-21 00:00', '2026-08-21 00:00', '2026-09-21 00:00', 'false', 'paid'), ('il_1061', 'in_5061', 'NWB-2061', 'cus_F6', 'Fernhill Clinics', 'Workspace plan - monthly', 'usd', '950.00', '2025-09-26 00:00', '2025-09-26 00:00', '2025-10-26 00:00', 'false', 'paid'), ('il_1062', 'in_5062', 'NWB-2062', 'cus_F6', 'Fernhill Clinics', 'Workspace plan - monthly', 'usd', '950.00', '2025-10-26 00:00', '2025-10-26 00:00', '2025-11-26 00:00', 'false', 'paid'), ('il_1063', 'in_5063', 'NWB-2063', 'cus_F6', 'Fernhill Clinics', 'Workspace plan - monthly', 'usd', '950.00', '2025-11-26 00:00', '2025-11-26 00:00', '2025-12-26 00:00', 'false', 'paid'), ('il_1064', 'in_5064', 'NWB-2064', 'cus_F6', 'Fernhill Clinics', 'Workspace plan - monthly', 'usd', '950.00', '2025-12-26 00:00', '2025-12-26 00:00', '2026-01-26 00:00', 'false', 'paid'), ('il_1065', 'in_5065', 'NWB-2065', 'cus_F6', 'Fernhill Clinics', 'Workspace plan - monthly', 'usd', '950.00', '2026-01-26 00:00', '2026-01-26 00:00', '2026-02-26 00:00', 'false', 'paid'), ('il_1066', 'in_5066', 'NWB-2066', 'cus_F6', 'Fernhill Clinics', 'Workspace plan - monthly', 'usd', '950.00', '2026-02-26 00:00', '2026-02-26 00:00', '2026-03-26 00:00', 'false', 'paid'), ('il_1067', 'in_5067', 'NWB-2067', 'cus_F6', 'Fernhill Clinics', 'Workspace plan - monthly', 'usd', '950.00', '2026-03-26 00:00', '2026-03-26 00:00', '2026-04-26 00:00', 'false', 'paid'), ('il_1068', 'in_5068', 'NWB-2068', 'cus_F6', 'Fernhill Clinics', 'Workspace plan - monthly', 'usd', '950.00', '2026-04-26 00:00', '2026-04-26 00:00', '2026-05-26 00:00', 'false', 'paid'), ('il_1069', 'in_5069', 'NWB-2069', 'cus_F6', 'Fernhill Clinics', 'Workspace plan - monthly', 'usd', '950.00', '2026-05-26 00:00', '2026-05-26 00:00', '2026-06-26 00:00', 'false', 'paid'), ('il_1070', 'in_5070', 'NWB-2070', 'cus_F6', 'Fernhill Clinics', 'Workspace plan - monthly', 'usd', '950.00', '2026-06-26 00:00', '2026-06-26 00:00', '2026-07-26 00:00', 'false', 'paid'), ('il_1071', 'in_5071', 'NWB-2071', 'cus_F6', 'Fernhill Clinics', 'Workspace plan - monthly', 'usd', '950.00', '2026-07-26 00:00', '2026-07-26 00:00', '2026-08-26 00:00', 'false', 'paid'), ('il_1072', 'in_5072', 'NWB-2072', 'cus_F6', 'Fernhill Clinics', 'Workspace plan - monthly', 'usd', '950.00', '2026-08-26 00:00', '2026-08-26 00:00', '2026-09-26 00:00', 'false', 'paid'), ('il_1073', 'in_5073', 'NWB-2073', 'cus_G7', 'Granite Peak Supply', 'Workspace plan - monthly', 'usd', '3200.00', '2025-09-09 00:00', '2025-09-09 00:00', '2025-10-09 00:00', 'false', 'paid'), ('il_1074', 'in_5074', 'NWB-2074', 'cus_G7', 'Granite Peak Supply', 'Workspace plan - monthly', 'usd', '3200.00', '2025-10-09 00:00', '2025-10-09 00:00', '2025-11-09 00:00', 'false', 'paid'), ('il_1075', 'in_5075', 'NWB-2075', 'cus_G7', 'Granite Peak Supply', 'Workspace plan - monthly', 'usd', '3200.00', '2025-11-09 00:00', '2025-11-09 00:00', '2025-12-09 00:00', 'false', 'paid'), ('il_1076', 'in_5076', 'NWB-2076', 'cus_G7', 'Granite Peak Supply', 'Workspace plan - monthly', 'usd', '3200.00', '2025-12-09 00:00', '2025-12-09 00:00', '2026-01-09 00:00', 'false', 'paid'), ('il_1077', 'in_5077', 'NWB-2077', 'cus_G7', 'Granite Peak Supply', 'Workspace plan - monthly', 'usd', '3200.00', '2026-01-09 00:00', '2026-01-09 00:00', '2026-02-09 00:00', 'false', 'paid'), ('il_1078', 'in_5078', 'NWB-2078', 'cus_G7', 'Granite Peak Supply', 'Workspace plan - monthly', 'usd', '3200.00', '2026-02-09 00:00', '2026-02-09 00:00', '2026-03-09 00:00', 'false', 'paid'), ('il_1079', 'in_5079', 'NWB-2079', 'cus_G7', 'Granite Peak Supply', 'Workspace plan - monthly', 'usd', '3200.00', '2026-03-09 00:00', '2026-03-09 00:00', '2026-04-09 00:00', 'false', 'paid'), ('il_1080', 'in_5080', 'NWB-2080', 'cus_G7', 'Granite Peak Supply', 'Workspace plan - monthly', 'usd', '3200.00', '2026-04-09 00:00', '2026-04-09 00:00', '2026-05-09 00:00', 'false', 'paid'), ('il_1081', 'in_5081', 'NWB-2081', 'cus_G7', 'Granite Peak Supply', 'Workspace plan - monthly', 'usd', '3200.00', '2026-05-09 00:00', '2026-05-09 00:00', '2026-06-09 00:00', 'false', 'paid'), ('il_1082', 'in_5082', 'NWB-2082', 'cus_G7', 'Granite Peak Supply', 'Workspace plan - monthly', 'usd', '3200.00', '2026-06-09 00:00', '2026-06-09 00:00', '2026-07-09 00:00', 'false', 'paid'), ('il_1083', 'in_5083', 'NWB-2083', 'cus_G7', 'Granite Peak Supply', 'Workspace plan - monthly', 'usd', '3200.00', '2026-07-09 00:00', '2026-07-09 00:00', '2026-08-09 00:00', 'false', 'paid'), ('il_1084', 'in_5084', 'NWB-2084', 'cus_G7', 'Granite Peak Supply', 'Workspace plan - monthly', 'usd', '3200.00', '2026-08-09 00:00', '2026-08-09 00:00', '2026-09-09 00:00', 'false', 'paid'), ('il_1085', 'in_5085', 'NWB-2085', 'cus_H8', 'Harborview Labs', 'Workspace plan - monthly', 'usd', '650.00', '2025-09-15 00:00', '2025-09-15 00:00', '2025-10-15 00:00', 'false', 'paid'), ('il_1086', 'in_5086', 'NWB-2086', 'cus_H8', 'Harborview Labs', 'Workspace plan - monthly', 'usd', '650.00', '2025-10-15 00:00', '2025-10-15 00:00', '2025-11-15 00:00', 'false', 'paid'), ('il_1087', 'in_5087', 'NWB-2087', 'cus_H8', 'Harborview Labs', 'Workspace plan - monthly', 'usd', '650.00', '2025-11-15 00:00', '2025-11-15 00:00', '2025-12-15 00:00', 'false', 'paid'), ('il_1088', 'in_5088', 'NWB-2088', 'cus_H8', 'Harborview Labs', 'Workspace plan - monthly', 'usd', '650.00', '2025-12-15 00:00', '2025-12-15 00:00', '2026-01-15 00:00', 'false', 'paid'), ('il_1089', 'in_5089', 'NWB-2089', 'cus_H8', 'Harborview Labs', 'Workspace plan - monthly', 'usd', '650.00', '2026-01-15 00:00', '2026-01-15 00:00', '2026-02-15 00:00', 'false', 'paid'), ('il_1090', 'in_5090', 'NWB-2090', 'cus_H8', 'Harborview Labs', 'Workspace plan - monthly', 'usd', '650.00', '2026-02-15 00:00', '2026-02-15 00:00', '2026-03-15 00:00', 'false', 'paid'), ('il_1091', 'in_5091', 'NWB-2091', 'cus_H8', 'Harborview Labs', 'Workspace plan - monthly', 'usd', '650.00', '2026-03-15 00:00', '2026-03-15 00:00', '2026-04-15 00:00', 'false', 'paid'), ('il_1092', 'in_5092', 'NWB-2092', 'cus_H8', 'Harborview Labs', 'Workspace plan - monthly', 'usd', '650.00', '2026-04-15 00:00', '2026-04-15 00:00', '2026-05-15 00:00', 'false', 'paid'), ('il_1093', 'in_5093', 'NWB-2093', 'cus_H8', 'Harborview Labs', 'Workspace plan - monthly', 'usd', '650.00', '2026-05-15 00:00', '2026-05-15 00:00', '2026-06-15 00:00', 'false', 'paid'), ('il_1094', 'in_5094', 'NWB-2094', 'cus_H8', 'Harborview Labs', 'Workspace plan - monthly', 'usd', '650.00', '2026-06-15 00:00', '2026-06-15 00:00', '2026-07-15 00:00', 'false', 'paid'), ('il_1095', 'in_5095', 'NWB-2095', 'cus_H8', 'Harborview Labs', 'Workspace plan - monthly', 'usd', '650.00', '2026-07-15 00:00', '2026-07-15 00:00', '2026-08-15 00:00', 'false', 'paid'), ('il_1096', 'in_5096', 'NWB-2096', 'cus_H8', 'Harborview Labs', 'Workspace plan - monthly', 'usd', '650.00', '2026-08-15 00:00', '2026-08-15 00:00', '2026-09-15 00:00', 'false', 'paid'), ('il_1097', 'in_5097', 'NWB-2097', 'cus_J1', 'Juniper Health Partners', 'Workspace plan - annual (paid up front)', 'usd', '36000.00', '2025-10-01 00:00', '2025-10-01 00:00', '2026-10-01 00:00', 'false', 'paid'), ('il_1098', 'in_5098', 'NWB-2098', 'cus_K2', 'Kestrel Aerospace', 'Workspace plan - annual (paid up front)', 'usd', '60000.00', '2025-11-15 00:00', '2025-11-15 00:00', '2026-11-15 00:00', 'false', 'paid'), ('il_1099', 'in_5099', 'NWB-2099', 'cus_L3', 'Larkspur Foods', 'Workspace plan - annual (paid up front)', 'usd', '24000.00', '2026-01-10 00:00', '2026-01-10 00:00', '2027-01-10 00:00', 'false', 'paid'), ('il_1100', 'in_5100', 'NWB-2100', 'cus_M4', 'Meridian Title', 'Workspace plan - annual (paid up front)', 'usd', '18000.00', '2026-03-01 00:00', '2026-03-01 00:00', '2027-03-01 00:00', 'false', 'paid'), ('il_1101', 'in_5101', 'NWB-2101', 'cus_N5', 'Northwind Staffing', 'Workspace plan - annual (paid up front)', 'usd', '42000.00', '2026-06-20 00:00', '2026-06-20 00:00', '2027-06-20 00:00', 'false', 'paid'), ('il_1102', 'in_5102', 'NWB-2102', 'cus_P6', 'Pinecrest Schools', 'Workspace plan - annual (paid up front)', 'usd', '30000.00', '2026-08-05 00:00', '2026-08-05 00:00', '2027-08-05 00:00', 'false', 'paid'), ('il_1103', 'in_5103', 'NWB-2103', 'cus_Q7', 'Quarry Lane Builders', 'Workspace plan - quarterly', 'usd', '5400.00', '2025-09-01 00:00', '2025-09-01 00:00', '2025-12-01 00:00', 'false', 'paid'), ('il_1104', 'in_5104', 'NWB-2104', 'cus_Q7', 'Quarry Lane Builders', 'Workspace plan - quarterly', 'usd', '5400.00', '2025-12-01 00:00', '2025-12-01 00:00', '2026-03-01 00:00', 'false', 'paid'), ('il_1105', 'in_5105', 'NWB-2105', 'cus_Q7', 'Quarry Lane Builders', 'Workspace plan - quarterly', 'usd', '5400.00', '2026-03-01 00:00', '2026-03-01 00:00', '2026-06-01 00:00', 'false', 'paid'), ('il_1106', 'in_5106', 'NWB-2106', 'cus_Q7', 'Quarry Lane Builders', 'Workspace plan - quarterly', 'usd', '5400.00', '2026-06-01 00:00', '2026-06-01 00:00', '2026-09-01 00:00', 'false', 'paid'), ('il_1107', 'in_5107', 'NWB-2107', 'cus_R8', 'Redwood Credit Union', 'Workspace plan - quarterly', 'usd', '8100.00', '2025-09-12 00:00', '2025-09-12 00:00', '2025-12-12 00:00', 'false', 'paid'), ('il_1108', 'in_5108', 'NWB-2108', 'cus_R8', 'Redwood Credit Union', 'Workspace plan - quarterly', 'usd', '8100.00', '2025-12-12 00:00', '2025-12-12 00:00', '2026-03-12 00:00', 'false', 'paid'), ('il_1109', 'in_5109', 'NWB-2109', 'cus_R8', 'Redwood Credit Union', 'Workspace plan - quarterly', 'usd', '8100.00', '2026-03-12 00:00', '2026-03-12 00:00', '2026-06-12 00:00', 'false', 'paid'), ('il_1110', 'in_5110', 'NWB-2110', 'cus_R8', 'Redwood Credit Union', 'Workspace plan - quarterly', 'usd', '8100.00', '2026-06-12 00:00', '2026-06-12 00:00', '2026-09-12 00:00', 'false', 'paid'), ('il_1111', 'in_5111', 'NWB-2111', 'cus_C3', 'Cobalt Robotics', 'Unused time on Workspace plan after 27 Jun 2026', 'usd', '-1200.00', '2026-06-27 00:00', '2026-06-27 00:00', '2026-07-12 00:00', 'true', 'paid'), ('il_1112', 'in_5112', 'NWB-2112', 'cus_C3', 'Cobalt Robotics', 'Remaining time on Workspace Plus after 27 Jun 2026', 'usd', '1900.00', '2026-06-27 00:00', '2026-06-27 00:00', '2026-07-12 00:00', 'true', 'paid'), ('il_1113', 'in_5113', 'NWB-2113', 'cus_F6', 'Fernhill Clinics', 'Service credit - March outage', 'usd', '-475.00', '2026-04-02 00:00', '2026-03-01 00:00', '2026-04-01 00:00', 'false', 'paid'), ('il_1114', 'in_5114', 'NWB-2114', 'cus_K2', 'Kestrel Aerospace', 'Onboarding and data migration (one-time)', 'usd', '7500.00', '2025-11-15 00:00', '', '', 'false', 'paid'), ('il_1115', 'in_5115', 'NWB-2115', 'cus_N5', 'Northwind Staffing', 'Onboarding and data migration (one-time)', 'usd', '5000.00', '2026-06-20 00:00', '', '', 'false', 'paid'), ('il_1116', 'in_5116', 'NWB-2116', 'cus_P6', 'Pinecrest Schools', 'Onboarding and data migration (one-time)', 'usd', '4000.00', '2026-08-05 00:00', '', '', 'false', 'paid'), ('il_1117', 'in_5117', 'NWB-2117', 'cus_H8', 'Harborview Labs', 'Additional seats', 'usd', '390.00', '2026-05-15 00:00', '', '', 'false', 'paid'), ('il_1118', 'in_5118', 'NWB-2118', 'cus_D4', 'Dune Street Media', 'Workspace plan - monthly', 'usd', '500.00', '2026-08-18 00:00', '2026-08-18 00:00', '2026-09-18 00:00', 'false', 'void') ) AS t("id", "Invoice", "Invoice Number", "Customer", "Customer Name", "Description", "Currency", "Amount", "Date (UTC)", "Period Start (UTC)", "Period End (UTC)", "Proration", "Status") - 02Map the export's columns and drop names and emails (check this step)combines data from multiple inputs · aggregates rows into summary totals · buckets values by condition
WITH settings(amounts_are_in_cents) AS (VALUES (false)), fieldmap(field, header_key, priority) AS (VALUES ('line_id', 'id', 1), ('line_id', 'lineitemid', 2), ('line_id', 'invoicelineitemid', 3), ('invoice_id', 'invoice', 1), ('invoice_id', 'invoiceid', 2), ('invoice_number', 'invoicenumber', 1), ('invoice_number', 'number', 2), ('customer_id', 'customer', 1), ('customer_id', 'customerid', 2), ('description', 'description', 1), ('description', 'lineitemdescription', 2), ('description', 'product', 3), ('description', 'plan', 4), ('currency', 'currency', 1), ('amount', 'amount', 1), ('amount', 'lineitemamount', 2), ('amount', 'subtotal', 3), ('amount', 'total', 4), ('amount', 'amountdue', 5), ('invoice_date', 'dateutc', 1), ('invoice_date', 'date', 2), ('invoice_date', 'createdutc', 3), ('invoice_date', 'created', 4), ('invoice_date', 'finalizedatutc', 5), ('period_start', 'periodstartutc', 1), ('period_start', 'periodstart', 2), ('period_start', 'lineitemperiodstart', 3), ('period_start', 'serviceperiodstart', 4), ('period_end', 'periodendutc', 1), ('period_end', 'periodend', 2), ('period_end', 'lineitemperiodend', 3), ('period_end', 'serviceperiodend', 4), ('proration', 'proration', 1), ('proration', 'isproration', 2), ('status', 'status', 1), ('status', 'invoicestatus', 2) ), raw AS (SELECT row_number() OVER () AS line_no, * FROM input_1), long AS ( UNPIVOT (SELECT line_no, CAST(COLUMNS(* EXCLUDE (line_no)) AS VARCHAR) FROM raw) ON COLUMNS(* EXCLUDE (line_no)) INTO NAME header VALUE val ), keyed AS ( SELECT line_no, regexp_replace(lower(header), '[^a-z0-9]+', '', 'g') AS header_key, trim(val) AS val FROM long WHERE val IS NOT NULL AND trim(val) <> '' ), best AS ( SELECT k.line_no, f.field, arg_min(k.val, f.priority) AS val FROM keyed k JOIN fieldmap f ON f.header_key = k.header_key GROUP BY k.line_no, f.field ), wide AS ( SELECT r.line_no, MAX(b.val) FILTER (WHERE b.field = 'line_id') AS line_id, MAX(b.val) FILTER (WHERE b.field = 'invoice_id') AS invoice_id, MAX(b.val) FILTER (WHERE b.field = 'invoice_number') AS invoice_number, MAX(b.val) FILTER (WHERE b.field = 'customer_id') AS customer_id, MAX(b.val) FILTER (WHERE b.field = 'description') AS description, MAX(b.val) FILTER (WHERE b.field = 'currency') AS currency, MAX(b.val) FILTER (WHERE b.field = 'amount') AS amount_text, MAX(b.val) FILTER (WHERE b.field = 'invoice_date') AS invoice_date_text, MAX(b.val) FILTER (WHERE b.field = 'period_start') AS period_start_text, MAX(b.val) FILTER (WHERE b.field = 'period_end') AS period_end_text, MAX(b.val) FILTER (WHERE b.field = 'proration') AS proration_text, MAX(b.val) FILTER (WHERE b.field = 'status') AS status FROM raw r LEFT JOIN best b ON b.line_no = r.line_no GROUP BY r.line_no ), parsed AS ( SELECT w.*, TRY_CAST(regexp_replace(w.amount_text, '[^0-9.-]', '', 'g') AS DOUBLE) * (CASE WHEN s.amounts_are_in_cents THEN 0.01 ELSE 1 END) AS amount, COALESCE(TRY_CAST(w.invoice_date_text AS TIMESTAMP), try_strptime(w.invoice_date_text, '%m/%d/%Y %H:%M'), try_strptime(w.invoice_date_text, '%m/%d/%Y'), CASE WHEN TRY_CAST(w.invoice_date_text AS BIGINT) > 1000000000 THEN make_timestamp(TRY_CAST(w.invoice_date_text AS BIGINT) * 1000000) END) AS invoice_ts, COALESCE(TRY_CAST(w.period_start_text AS TIMESTAMP), try_strptime(w.period_start_text, '%m/%d/%Y %H:%M'), try_strptime(w.period_start_text, '%m/%d/%Y'), CASE WHEN TRY_CAST(w.period_start_text AS BIGINT) > 1000000000 THEN make_timestamp(TRY_CAST(w.period_start_text AS BIGINT) * 1000000) END) AS start_ts, COALESCE(TRY_CAST(w.period_end_text AS TIMESTAMP), try_strptime(w.period_end_text, '%m/%d/%Y %H:%M'), try_strptime(w.period_end_text, '%m/%d/%Y'), CASE WHEN TRY_CAST(w.period_end_text AS BIGINT) > 1000000000 THEN make_timestamp(TRY_CAST(w.period_end_text AS BIGINT) * 1000000) END) AS end_ts FROM wide w, settings s ) SELECT line_no, line_id, invoice_id, invoice_number, customer_id, description, upper(COALESCE(currency, 'USD')) AS currency, ROUND(amount, 2) AS amount, amount_text AS amount_as_written, CAST(invoice_ts AS DATE) AS invoice_date, CAST(start_ts AS DATE) AS period_start, CAST(end_ts AS DATE) AS period_end, COALESCE(lower(proration_text) IN ('true', 't', 'yes', '1'), false) AS is_proration, lower(COALESCE(status, '')) AS status, 'Customer names and emails are left out on purpose - only the customer id is carried forward' AS privacy_note FROM parsed ORDER BY line_no - 03Spread every invoice line over its service period, day by daybuckets values by condition · computes running / windowed totals · filters to the relevant rows
WITH l AS ( SELECT *, period_start IS NOT NULL AND period_end IS NOT NULL AND period_end > period_start AS has_period, COALESCE(period_start, invoice_date) AS ps FROM input_1 WHERE amount IS NOT NULL AND amount <> 0 AND status NOT IN ('void', 'draft') AND COALESCE(period_start, invoice_date) IS NOT NULL ), e AS (SELECT *, CASE WHEN has_period THEN period_end ELSE ps + 1 END AS pe FROM l), m AS ( SELECT e.*, CAST(unnest(generate_series(date_trunc('month', ps), date_trunc('month', pe - 1), INTERVAL 1 MONTH)) AS DATE) AS month_start FROM e ), d AS ( SELECT *, CAST(LEAST(pe, CAST(month_start + INTERVAL 1 MONTH AS DATE)) - GREATEST(ps, month_start) AS INTEGER) AS days_in_month, CAST(pe - ps AS INTEGER) AS days_total FROM m ), r AS ( SELECT *, ROUND(amount * days_in_month / days_total, 2) AS raw_revenue, SUM(ROUND(amount * days_in_month / days_total, 2)) OVER (PARTITION BY line_no) AS sum_raw, ROW_NUMBER() OVER (PARTITION BY line_no ORDER BY month_start DESC) AS from_last FROM d ) SELECT line_no, invoice_id, customer_id, description, currency, invoice_date, ps AS service_start, pe AS service_end_exclusive, amount AS line_amount, strftime(month_start, '%Y-%m') AS revenue_month, days_in_month, days_total, ROUND(CASE WHEN from_last = 1 THEN raw_revenue + (amount - sum_raw) ELSE raw_revenue END, 2) AS revenue, CASE WHEN has_period THEN 'Spread by day over the service period' ELSE 'No service period in the file - recognized on the invoice date' END AS method FROM r ORDER BY line_no, revenue_month - 04Billed vs earned, month by monthcombines data from multiple inputs · aggregates rows into summary totals · buckets values by condition
WITH rev AS (SELECT revenue_month AS month, SUM(revenue) AS revenue FROM input_1 GROUP BY revenue_month), bill AS (SELECT strftime(invoice_date, '%Y-%m') AS month, SUM(line_amount) AS billed FROM (SELECT DISTINCT line_no, invoice_date, line_amount FROM input_1) GROUP BY 1), close AS (SELECT MAX(month) AS close_month FROM bill) SELECT COALESCE(r.month, b.month) AS month, ROUND(COALESCE(b.billed, 0), 2) AS billed, ROUND(COALESCE(r.revenue, 0), 2) AS revenue_recognized, ROUND(COALESCE(b.billed, 0) - COALESCE(r.revenue, 0), 2) AS billed_minus_revenue, CASE WHEN COALESCE(r.month, b.month) < c.close_month THEN 'Closed' WHEN COALESCE(r.month, b.month) = c.close_month THEN 'Month being closed' ELSE 'Future - already billed, not yet earned' END AS period_status FROM rev r FULL OUTER JOIN bill b ON b.month = r.month, close c ORDER BY 1 - 05DEFERRED REVENUE ROLL-FORWARDbuckets values by condition · computes running / windowed totals · filters to the relevant rows
WITH m AS (SELECT * FROM input_1 WHERE period_status <> 'Future - already billed, not yet earned'), c AS ( SELECT month, billed, revenue_recognized, SUM(billed - revenue_recognized) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS closing FROM m ) SELECT month, ROUND(COALESCE(LAG(closing) OVER (ORDER BY month), 0), 2) AS opening_deferred_revenue, ROUND(billed, 2) AS plus_billed, ROUND(revenue_recognized, 2) AS less_revenue_recognized, ROUND(closing, 2) AS closing_deferred_revenue, CASE WHEN closing < 0 THEN 'Negative = earned before it was billed (unbilled revenue)' ELSE '' END AS note FROM c ORDER BY month - 06When the deferred balance turns into revenuecomputes running / windowed totals · filters to the relevant rows · sorts the output
WITH f AS (SELECT * FROM input_1 WHERE period_status = 'Future - already billed, not yet earned'), t AS (SELECT COALESCE(SUM(revenue_recognized), 0) AS total FROM f) SELECT f.month AS future_month, ROUND(f.revenue_recognized, 2) AS revenue_to_be_released, ROUND(t.total - SUM(f.revenue_recognized) OVER (ORDER BY f.month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS deferred_revenue_left_after FROM f, t ORDER BY f.month - 07JOURNAL ENTRIES for the month being closed (import-ready)appends result sets (e.g. a TOTAL row) · filters to the relevant rows · sorts the output
WITH c AS (SELECT * FROM input_1 WHERE period_status = 'Month being closed'), d AS (SELECT month, CAST(CAST(strptime(month || '-01', '%Y-%m-%d') + INTERVAL 1 MONTH AS DATE) - 1 AS DATE) AS entry_date, billed, revenue_recognized FROM c) SELECT 1 AS line, 'JE-1 Billings' AS entry, entry_date, 'Accounts receivable' AS account, ROUND(billed, 2) AS debit, NULL AS credit, 'Stripe invoices billed in ' || month AS memo FROM d UNION ALL SELECT 2, 'JE-1 Billings', entry_date, 'Deferred revenue', NULL, ROUND(billed, 2), 'Stripe invoices billed in ' || month FROM d UNION ALL SELECT 3, 'JE-2 Revenue recognition', entry_date, 'Deferred revenue', ROUND(revenue_recognized, 2), NULL, 'Revenue earned in ' || month || ' - straight-line by day over each service period' FROM d UNION ALL SELECT 4, 'JE-2 Revenue recognition', entry_date, 'Subscription revenue', NULL, ROUND(revenue_recognized, 2), 'Revenue earned in ' || month || ' - straight-line by day over each service period' FROM d ORDER BY line - 08Tie-out - schedule, roll-forward and file totalappends result sets (e.g. a TOTAL row) · filters to the relevant rows · sorts the output
WITH lines AS (SELECT * FROM input_1), sched AS (SELECT * FROM input_2), roll AS (SELECT * FROM input_3), rel AS (SELECT * FROM input_4), inc AS (SELECT COALESCE(SUM(line_amount), 0) AS v FROM (SELECT DISTINCT line_no, line_amount FROM sched)), allrev AS (SELECT COALESCE(SUM(revenue), 0) AS v FROM sched), closing AS (SELECT COALESCE((SELECT closing_deferred_revenue FROM roll ORDER BY month DESC LIMIT 1), 0) AS v), future AS (SELECT COALESCE(SUM(revenue_to_be_released), 0) AS v FROM rel), filetot AS (SELECT COALESCE(SUM(amount), 0) AS v FROM lines), excluded AS (SELECT COALESCE(SUM(amount), 0) AS v, COUNT(*) AS n FROM lines WHERE NOT (amount IS NOT NULL AND amount <> 0 AND status NOT IN ('void', 'draft') AND COALESCE(period_start, invoice_date) IS NOT NULL)) SELECT 1 AS ord, 'Every included line lands in the schedule exactly once' AS check_item, ROUND(inc.v, 2) AS expected, ROUND(allrev.v, 2) AS actual, ROUND(allrev.v - inc.v, 2) AS tie_out_difference FROM inc, allrev UNION ALL SELECT 2, 'Closing deferred revenue equals what is left to release', ROUND(future.v, 2), ROUND(closing.v, 2), ROUND(closing.v - future.v, 2) FROM future, closing UNION ALL SELECT 3, 'File total equals included lines plus excluded lines (void, draft, no date)', ROUND(filetot.v, 2), ROUND(inc.v + excluded.v, 2), ROUND(inc.v + excluded.v - filetot.v, 2) FROM filetot, inc, excluded ORDER BY ord - 09SUMMARY - what to book this monthaggregates rows into summary totals · buckets values by condition · appends result sets (e.g. a TOTAL row)
WITH lines AS (SELECT * FROM input_1), sched AS (SELECT * FROM input_2), monthly AS (SELECT * FROM input_3), roll AS (SELECT * FROM input_4), tie AS (SELECT * FROM input_5), cm AS (SELECT * FROM monthly WHERE period_status = 'Month being closed'), closing AS (SELECT month, closing_deferred_revenue AS v FROM roll ORDER BY month DESC LIMIT 1), next12 AS (SELECT COALESCE(SUM(revenue_recognized), 0) AS v FROM (SELECT * FROM monthly WHERE period_status LIKE 'Future%' ORDER BY month LIMIT 12)), top_def AS (SELECT customer_id, description, SUM(revenue) AS v FROM sched WHERE revenue_month > (SELECT month FROM cm) GROUP BY customer_id, description ORDER BY v DESC LIMIT 1), noper AS (SELECT COUNT(DISTINCT line_no) AS n, COALESCE(SUM(revenue), 0) AS v FROM sched WHERE method LIKE 'No service period%'), neg AS (SELECT COUNT(*) AS n, COALESCE(SUM(amount), 0) AS v FROM lines WHERE amount < 0 AND status NOT IN ('void', 'draft')), excl AS (SELECT COUNT(*) AS n, COALESCE(SUM(amount), 0) AS v FROM lines WHERE status IN ('void', 'draft')), cur AS (SELECT COUNT(DISTINCT currency) AS n, string_agg(DISTINCT currency, ', ') AS names FROM lines), cents AS (SELECT COUNT(*) AS n, COUNT(*) FILTER (WHERE amount_as_written NOT LIKE '%.%') AS whole, MEDIAN(ABS(amount)) AS med FROM lines WHERE amount IS NOT NULL), periods AS (SELECT COUNT(DISTINCT line_no) FILTER (WHERE method LIKE 'Spread%') AS with_period, COUNT(DISTINCT line_no) AS total FROM sched), ties AS (SELECT COALESCE(MAX(ABS(tie_out_difference)), 0) AS worst FROM tie), nolines AS (SELECT COUNT(*) AS n FROM sched), rows_out AS ( SELECT 1 AS ord, 'MONTH BEING CLOSED' AS area, (SELECT month || ': billed ' || (CASE WHEN (billed) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(billed)) AS BIGINT))) || ', earned ' || (CASE WHEN (revenue_recognized) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(revenue_recognized)) AS BIGINT))) FROM cm) AS finding, (SELECT CASE WHEN ABS(billed - revenue_recognized) < 1 THEN 'Billed and earned are the same this month' WHEN billed > revenue_recognized THEN 'Booking the billings as revenue would overstate the month by ' || (CASE WHEN (billed - revenue_recognized) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(billed - revenue_recognized)) AS BIGINT))) ELSE 'Booking the billings as revenue would understate the month by ' || (CASE WHEN (revenue_recognized - billed) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(revenue_recognized - billed)) AS BIGINT))) END FROM cm) AS next_step FROM cm UNION ALL SELECT 0, 'THIS EXPORT HAS NO SERVICE PERIODS', 'None of the ' || total || ' lines carries a period start and end, so everything was recognized on its invoice date and deferred revenue is zero by construction', 'Export the invoice LINE ITEMS with their period start and end (or add those columns) - an invoice-level export cannot show what is deferred' FROM periods WHERE total > 0 AND with_period = 0 UNION ALL SELECT 2, 'DEFERRED REVENUE', (SELECT CASE WHEN ABS(closing.v) < 1 THEN 'Nothing is deferred at the end of ' || closing.month ELSE (CASE WHEN (closing.v) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(closing.v)) AS BIGINT))) || ' billed but not yet earned at the end of ' || closing.month || ' - ' || CASE WHEN ABS(closing.v - next12.v) < 1 THEN 'all of it' ELSE (CASE WHEN (next12.v) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(next12.v)) AS BIGINT))) || ' of it' END || ' releases over the next 12 months' END FROM closing, next12), (SELECT CASE WHEN closing.v < 0 THEN 'Negative balance = revenue earned before it was billed - book it as unbilled revenue, not deferred' ELSE 'This is the liability the balance sheet should carry - the release schedule is its own step' END FROM closing) FROM closing UNION ALL SELECT 3, 'LARGEST DEFERRED ITEM', (SELECT customer_id || ' - ' || description || ': ' || (CASE WHEN (v) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(v)) AS BIGINT))) || ' still to be earned' FROM top_def), 'Annual and multi-month plans paid up front are where deferred revenue lives - check the service dates on the big ones' FROM top_def UNION ALL SELECT 4, 'LINES WITH NO SERVICE PERIOD', (SELECT n || CASE WHEN n = 1 THEN ' line worth ' ELSE ' lines worth ' END || (CASE WHEN (v) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(v)) AS BIGINT))) || CASE WHEN n = 1 THEN ' carries' ELSE ' carry' END || ' no service period, so recognized on the invoice date' FROM noper), 'Right for one-time fees; wrong for anything delivered over time - add the dates in Stripe or in the mapping step' FROM noper WHERE n > 0 UNION ALL SELECT 5, 'PRORATIONS AND CREDITS', (SELECT n || CASE WHEN n = 1 THEN ' negative line totalling ' ELSE ' negative lines totalling ' END || (CASE WHEN (v) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(v)) AS BIGINT))) || ' (upgrades, downgrades, service credits) reduce revenue over their own periods' FROM neg), 'Activity only - refunds and credit notes issued outside invoices are NOT in this file and need their own entry' FROM neg WHERE n > 0 UNION ALL SELECT 6, 'VOID AND DRAFT INVOICES', (SELECT n || CASE WHEN n = 1 THEN ' line worth ' ELSE ' lines worth ' END || (CASE WHEN (v) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(v)) AS BIGINT))) || CASE WHEN n = 1 THEN ' is' ELSE ' are' END || ' void or draft and left out' FROM excl), 'None needed - they are listed in the tie-out so nothing disappears silently' FROM excl WHERE n > 0 UNION ALL SELECT 7, 'MORE THAN ONE CURRENCY', (SELECT n || ' currencies in the file (' || names || ') - amounts were added together without conversion' FROM cur), 'Filter to one currency or add a rate table before relying on the totals' FROM cur WHERE n > 1 UNION ALL SELECT 8, 'ARE THE AMOUNTS IN CENTS?', 'No amount in the file has a decimal point, and the typical line is above 1,000', 'Exports from the Stripe API or Sigma are in cents - if that is this file, set amounts_are_in_cents to true at the top of the mapping step' FROM cents WHERE n > 0 AND whole = n AND med >= 1000 UNION ALL SELECT 9, 'NOTHING TO SCHEDULE', 'No line in the file had an amount and a date the mapping step could read', 'Open the mapping step - the header names it looks for are listed at the top and can be edited' FROM nolines WHERE n = 0 UNION ALL SELECT 10, 'TIE-OUT', (SELECT CASE WHEN worst < 0.01 THEN 'Schedule, roll-forward and file total agree to the cent' ELSE 'Largest difference across the three checks: $' || format('{:.2f}', worst) END FROM ties), (SELECT CASE WHEN worst < 0.01 THEN 'Ties - every invoiced dollar is either earned or sitting in deferred revenue' ELSE 'Does not tie - open the tie-out step to see which check is off' END FROM ties) FROM nolines WHERE n > 0 UNION ALL SELECT 99, 'VERDICT', COALESCE((SELECT (CASE WHEN (cm.revenue_recognized) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(cm.revenue_recognized)) AS BIGINT))) || ' earned in ' || cm.month || ', ' || (CASE WHEN (closing.v) < 0 THEN '-$' ELSE '$' END || format('{:,}', CAST(ROUND(ABS(closing.v)) AS BIGINT))) || ' deferred - ' || CASE WHEN ties.worst < 0.01 THEN 'ties to the invoices' ELSE 'does NOT tie' END FROM cm, closing, ties), 'Could not read this file as a Stripe invoice export - check the mapping step'), 'Straight-line by day over each line''s service period - usage billing, credit grants and bundled contracts need judgment' ) SELECT ord, area, finding, next_step FROM rows_out ORDER BY ord
Run this on your books
Free to try — no sign-up, no card. The workflow runs in your browser; your data never leaves your machine.
Make it your own →