Stripe Sigma Query for Deferred Revenue: Invoice Lines With Service Periods
Stripe's standard invoice export has one row per invoice and its Period Start/End columns describe metered usage, not the subscription's service period. For deferred revenue you want one row per invoice line with that line's own period. Stripe Sigma (a paid Stripe add-on) can produce exactly that:
select
c.name as customer,
i.subscription_id as contract,
i.number as invoice,
date_format(i.created, '%Y-%m-%d') as invoice_date,
l.description as product,
l.amount / 100.0 as amount,
date_format(l.period_start, '%Y-%m-%d') as service_start,
date_format(date_add('second', -1, l.period_end), '%Y-%m-%d') as service_end,
upper(l.currency) as currency
from invoice_line_items l
join invoices i on i.id = l.invoice_id
left join customers c on c.id = i.customer_id
where i.status in ('paid', 'open')
order by i.created
Run it in Sigma, download the result as CSV and drop it into DeferSheet; the column names match DeferSheet's template, so mapping is automatic. Stripe's period_end is exclusive (the instant the period ends), which is why the query steps back one second to get the last service day.
No Sigma? Use Billing → Invoices → Export and set a default term in DeferSheet (12 months for annual plans, 1 for monthly); each invoice is spread over that term from its invoice date. Check the result against a few invoices you know.
Column and table names are from Stripe's Sigma schema; check Stripe's documentation if your account's schema differs. Amounts in the query are in the invoice currency's major unit (two-decimal currencies).