Building a Stripe Sigma query for revenue recognition on subscriptions

Question

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!

Answer 1

call me late to the party, but I made it work with this script.

-- To use, change the date range in the `test` table and run
-- test selects all customers who have paid an invoice for an invoice that has been sent within the given date range
with test as(
  select distinct
    invoices.id as stripe_invoice,
    invoices.currency as currency,
    invoices.customer_id as customer,
    (invoices.amount_due - coalesce(invoices.tax, 0)) / 100.0 as total,
    invoice_line_items.period_start as recognition_start,
    invoice_line_items.period_end as recognition_end,
    DATE_DIFF('DAY', invoice_line_items.period_start,invoice_line_items.period_end) as days    

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

where invoices.amount_due > 0
and paid
AND YEAR(invoice_line_items.period_start) = 2023
AND MONTH(invoice_line_items.period_start) = 07

order by invoices.customer_id, invoices.id),

-- rev_calc calculates the revenue per day and sets the date ranges for the coming 13 months
rev_calc as(
SELECT stripe_invoice,
currency,
customer,
total,
recognition_start,
recognition_end,
date_add('hour', -1, date_add('month', 1, date_trunc('month', recognition_start))) AS last_day_first_month,
date_trunc('month', recognition_start + interval '1' MONTH) AS first_day_month2,
date_add('hour', -1, date_add('month', 2, date_trunc('month', recognition_start))) AS last_day_month2,
date_trunc('month', recognition_start + interval '2' MONTH) AS first_day_month3,
date_add('hour', -1, date_add('month', 3, date_trunc('month', recognition_start))) AS last_day_month3,
date_trunc('month', recognition_start + interval '3' MONTH) AS first_day_month4,
date_add('hour', -1, date_add('month', 4, date_trunc('month', recognition_start))) AS last_day_month4,
date_trunc('month', recognition_start + interval '4' MONTH) AS first_day_month5,
date_add('hour', -1, date_add('month', 5, date_trunc('month', recognition_start))) AS last_day_month5,
date_trunc('month', recognition_start + interval '5' MONTH) AS first_day_month6,
date_add('hour', -1, date_add('month', 6, date_trunc('month', recognition_start))) AS last_day_month6,
date_trunc('month', recognition_start + interval '6' MONTH) AS first_day_month7,
date_add('hour', -1, date_add('month', 7, date_trunc('month', recognition_start))) AS last_day_month7,
date_trunc('month', recognition_start + interval '7' MONTH) AS first_day_month8,
date_add('hour', -1, date_add('month', 8, date_trunc('month', recognition_start))) AS last_day_month8,
date_trunc('month', recognition_start + interval '8' MONTH) AS first_day_month9,
date_add('hour', -1, date_add('month', 9, date_trunc('month', recognition_start))) AS last_day_month9,
date_trunc('month', recognition_start + interval '9' MONTH) AS first_day_month10,
date_add('hour', -1, date_add('month', 10, date_trunc('month', recognition_start))) AS last_day_month10,
date_trunc('month', recognition_start + interval '10' MONTH) AS first_day_month11,
date_add('hour', -1, date_add('month', 11, date_trunc('month', recognition_start))) AS last_day_month11,
date_trunc('month', recognition_start + interval '11' MONTH) AS first_day_month12,
date_add('hour', -1, date_add('month', 12, date_trunc('month', recognition_start))) AS last_day_month12,
date_trunc('month', recognition_start + interval '12' MONTH) AS first_day_month13,
CASE
WHEN days < 0.1 THEN total
ELSE total/days
END as rev_a_day
FROM test)

-- here we calculate the revenue recognition per month
-- ANNUAL Calculation
-- the 1st month can start at anytime in the month so the formula is (days left in the month * revenue a day)
-- the 2nd to 12th month are (all days in the month * revenue a day)
-- the 13th month, the last month, is (days left until plan stops * revenue a day)
-- MONTHLY Calculation
-- the 1st month can start at anytime in the month so the formula is (days left in the month * revenue a day)
-- the 2nd month, the last month, is (days left until plan stops * revenue a day)

SELECT
stripe_invoice,
currency,
customer,
total,
recognition_start,
recognition_end,
-- rev_a_day,
CASE
WHEN recognition_start < recognition_end THEN ROUND(((DATE_DIFF('DAY', recognition_start, last_day_first_month))*rev_a_day),2)
ELSE total
END as first_month,
CASE
WHEN recognition_start + interval '1' MONTH < recognition_end THEN ROUND(((DATE_DIFF('DAY', first_day_month2, last_day_month2)+1)*rev_a_day),2)
WHEN recognition_start + interval '1' MONTH = recognition_end THEN ROUND(((DATE_DIFF('DAY', first_day_month2, recognition_end)+1)*rev_a_day),2)
ELSE 0
END as second_month,
CASE
WHEN recognition_start + interval '2' MONTH < recognition_end THEN ROUND(((DATE_DIFF('DAY', first_day_month3, last_day_month3)+1)*rev_a_day),2)
ELSE 0
END as third_month,
CASE
WHEN recognition_start + interval '3' MONTH < recognition_end THEN ROUND(((DATE_DIFF('DAY', first_day_month4, last_day_month4)+1)*rev_a_day),2)
ELSE 0
END as fourth_month,
CASE
WHEN recognition_start + interval '4' MONTH < recognition_end THEN ROUND(((DATE_DIFF('DAY', first_day_month5, last_day_month5)+1)*rev_a_day),2)
ELSE 0
END as fifth_month,
CASE
WHEN recognition_start + interval '5' MONTH < recognition_end THEN ROUND(((DATE_DIFF('DAY', first_day_month6, last_day_month6)+1)*rev_a_day),2)
ELSE 0
END as sixth_month,
CASE
WHEN recognition_start + interval '6' MONTH < recognition_end THEN ROUND(((DATE_DIFF('DAY', first_day_month7, last_day_month7)+1)*rev_a_day),2)
ELSE 0
END as seventh_month,
CASE
WHEN recognition_start + interval '7' MONTH < recognition_end THEN ROUND(((DATE_DIFF('DAY', first_day_month8, last_day_month8)+1)*rev_a_day),2)
ELSE 0
END as eighth_month,
CASE
WHEN recognition_start + interval '8' MONTH < recognition_end THEN ROUND(((DATE_DIFF('DAY', first_day_month9, last_day_month9)+1)*rev_a_day),2)
ELSE 0
END as ninth_month,
CASE
WHEN recognition_start + interval '9' MONTH < recognition_end THEN ROUND(((DATE_DIFF('DAY', first_day_month10, last_day_month10)+1)*rev_a_day),2)
ELSE 0
END as tenth_month,
CASE
WHEN recognition_start + interval '10' MONTH < recognition_end THEN ROUND(((DATE_DIFF('DAY', first_day_month11, last_day_month11)+1)*rev_a_day),2)
ELSE 0
END as eleventh_month,
CASE
WHEN recognition_start + interval '11' MONTH < recognition_end + interval '1' DAY THEN ROUND(((DATE_DIFF('DAY', first_day_month12, last_day_month12)+1)*rev_a_day),2)
ELSE 0
END as twelfth_month,
CASE
WHEN recognition_start + interval '12' MONTH < recognition_end + interval '1' DAY THEN ROUND(((DATE_DIFF('DAY', first_day_month13, recognition_end)+1)*rev_a_day),2)
ELSE 0
END as thirteenth_month
FROM rev_calc

Answer 2

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