How to set up Stripe reports

Jun 21, 2024

Creating and analyzing reports for your Stripe data helps you understand how you are making money and how you can improve.

This tutorial shows you how to sync your Stripe data to PostHog and then create a report of insights like customer count, gross revenue, recurring revenue, revenue churn, and more.

Linking Stripe data to PostHog

To start, you need both a Stripe and PostHog account. Once you have those, head to PostHog's Data pipeline page and:

  1. Under the sources tab, click New source
  2. Choose the Stripe option by clicking Link
  3. Enter your account ID and a restricted API key that can read the resources you want to query
  4. Press Next, keep all tables selected and click Import

For Stripe tables, incremental syncs will only sync new records and not update existing records. This is a limitation of the Stripe API in which it's not possible to query for updated data.

Linking Stripe Account

Once done, PostHog will automatically pull and format your Stripe data for querying. You can adjust the sync frequency, see the last successful run, and more in data pipeline sources tab.

Note: If you are missing a table, check your data pipeline sources tab to make sure it synced correctly.

Creating insights for your Stripe report

Now that your Stripe data is synced into PostHog, you can use it to create insights for your report. Each of these requires you to create a new insight in the product analytics tab.

Want to get started fast? Check out our Stripe starter report template.

Customer count

To start, we create a trend of customer count over time.

On the trends tab, change the aggregation to Unique users, and then click the data series, go to the Data Warehouse tab, hover over the stripe_customer table, and click Select. You might want to change the Distinct ID field from id to email as Stripe can give multiple id values to the same user email.

Customer Count Insights

This can also be done for any of the other Stripe data like charges, subscriptions, and invoices. You can also add filters based on property values like created_at, email, status, and more.

Gross revenue

Next, we can get our gross revenue by doing a similar process and selecting stripe_charge. For aggregation, we want Property value sum and then choose amount. We also want to filter out failed charges by clicking the filter button, selecting the status property, and making sure it doesn't equal failed.

Finally, to clean up the visualization, click enable formula mode to divide by 100 (the amount value is in cents) and click Options on the chart to add $ as a prefix.

Gross Revenue Insights

Monthly recurring revenue (average revenue per customer)

There are many ways to calculate monthly recurring revenue, but the easiest and most common is multiplying the number of customers by the average revenue per customer per month.

To do this, we'll rely on the stripe_invoice series. Make sure to set customer_id as the distinct ID field before you select it, and then change the aggregation type to unique users. We also want to filter out invoices with nothing paid, so add a filter where amount_paid is greater than 0.

Next, we can copy that series to create another but modify it to aggregate by property value average of amount_paid.

Finally, we use formula mode to divide the amount by 100 and then multiply by the number of users with (B/100)*A. You can add the prefix and likely want to change the graph to the last 180 days grouped by month.

Monthly Recurring Revenue Insights

Monthly recurring revenue (the Stripe way)

Stripe calculates MRR by "summing the monthly-normalized amounts of all active subscriptions at that time."

To mimic this calculation in PostHog, we need to write an SQL query that gets all the subscription items, normalizes the subscription amount, and then sums them up for each month. Because a lot of this data is in JSON, we need to extract the values.

SQL
WITH subscription_items AS (
SELECT
id,
current_period_start,
JSONExtractArrayRaw(items ?? '[]', 'data') AS data_items
FROM stripe_subscription
),
flattened_items AS (
SELECT
id,
current_period_start,
arrayJoin(data_items) AS item
FROM subscription_items
)
SELECT
toStartOfMonth(current_period_start) AS month,
sum(
case
when JSONExtractString(JSONExtractRaw(item, 'plan'), 'interval') = 'month'
then JSONExtractFloat(JSONExtractRaw(item, 'plan'), 'amount')
when JSONExtractString(JSONExtractRaw(item, 'plan'), 'interval') = 'year'
then JSONExtractFloat(JSONExtractRaw(item, 'plan'), 'amount') / 12
else 0
end
) / 100 AS MRR
FROM flattened_items
WHERE
JSONExtractBool(JSONExtractRaw(item, 'plan'), 'active') = true
GROUP BY month
ORDER BY month DESC

Revenue churn

For many companies, the amount of money they lose is just as important as the amount they retain. To measure this, we can track revenue churn.

To do this, we write SQL to query both the stripe_invoice and stripe_subscription for users with an invoice but without a subscription. This requires joining the tables together on the customer_id value, and looks like this for the last 30 days:

SQL
with
recent_invoices as (
select customer_id, amount_paid, created_at
from stripe_invoice
where created_at >= now() - INTERVAL 30 day
),
subscribed_customers as (
select DISTINCT customer_id
from stripe_subscription
)
select sum(recent_invoices.amount_paid)/100
from recent_invoices
LEFT JOIN subscribed_customers on subscribed_customers.customer_id = recent_invoices.customer_id
where empty(subscribed_customers.customer_id)

Revenue growth rate

To get revenue growth rate, query stripe_invoice to get the monthly amount paid sum, but then we use window functions to calculate growth. These smooth out the month-over-month changes to give us a 3-month average to use in our growth rate calculation.

SQL
WITH monthly_mrr AS (
SELECT
toStartOfMonth(created_at) AS month,
sum(amount_paid) / 100 AS mrr
FROM prod_stripe_invoice
WHERE status = 'paid'
GROUP BY month
ORDER BY month
),
mrr_with_growth AS (
SELECT
month,
mrr,
avg(mrr) OVER (
ORDER BY month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS mrr_avg,
avg(mrr) OVER (
ORDER BY month
ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING
) AS previous_mrr_avg
FROM monthly_mrr
)
SELECT
month,
mrr_avg AS mrr,
previous_mrr_avg AS previous_mrr,
(mrr_avg - previous_mrr_avg) * 100.0 / previous_mrr_avg AS mrr_growth_rate
FROM mrr_with_growth
WHERE previous_mrr_avg IS NOT NULL
ORDER BY month

The nice part about this is that we can still visualize the query data as a graph by choosing the line chart option below the query and then choosing month as the X-axis and MRR growth as the Y-axis.

Revenue growth rate graph

Usage by top customers

The great part of syncing your Stripe data in PostHog is using it alongside your product data. An example of doing this is querying the usage of top customers.

To do this, we get the top customers from the stripe_invoice and join their emails with their PostHog distinct_id to get a count of their events.

SQL
with
top_customers as (
select customer_email, sum(amount_paid) / 100 as total_paid
from stripe_invoice
where created_at >= now() - INTERVAL 30 day
group by customer_email
),
big_events as (
select count(*) as event_count, distinct_id
from events
group by distinct_id
)
select customer_email, total_paid, event_count
from top_customers
left join big_events on big_events.distinct_id = top_customers.customer_email
order by total_paid desc

You can further break this down by filtering for specific events like home_api_called.

Top customers usage graph

Further reading

Comments