Building a Stripe Sigma query for revenue recognition on subscriptions

0
votes

There are a bunch of separate services and addons for Stripe that will calculate monthly revenue recognition numbers from >1 month subscriptions (e.g. yearly plans).

However, with the arrival of Stripe Sigma, can anyone help on an SQL query that would identify all customers, their subscription period, and charged amount

I'm looking for these fields, at least:

  • Customer ID
  • Subscription ID
  • Subscription start date
  • Subscription end date
  • Currency
  • Charged/paid amount (the upfront payment)

Thanks!

1
stripe-paymentsaccounting

1 Answers

0
votes

Sharing my first attempt at this

select distinct
    invoices.customer_id as customer,
    invoices.id as stripe_invoice,
    invoice_line_items.period_start as subscription_start,
    invoice_line_items.period_end as subscription_end,
    date_trunc('month', invoice_line_items.period_start) as recognition_start,
    date_add('month', -1, invoice_line_items.period_end) as recognition_end,
    invoices.currency as currency,
    (invoices.amount_due - coalesce(invoices.tax, 0)) / 100.0 as collected_amount_excl_tax

from invoices
left join invoice_line_items
    on invoices.id = invoice_line_items.invoice_id

where invoices.amount_due > 0
and paid
and date_diff('day', invoice_line_items.period_start, invoice_line_items.period_end) > 31
order by invoices.customer_id, invoices.id

Then using a spreadsheet to calculate deferred and recognized every month.

Would love some feedback on what I'm missing / forgetting