Revenue Event Modeling
Mitzu is capable of calculating MRR and Subscriber metrics for SaaS businesses. It can also calculate revenue for E-Commerce businesses. However, as e-commerce businesses typically don't have recurring income, the support for this requires no data modeling effort.
Subscription payment-based revenue income​
Mitzu calculates MRR and Subscriber metrics based on the "physical" payments that a payment provider tracks.
We will consider Stripe as the payment provider through this page. However, others like Paddle, Chargebee, Appstore, and Google Play Store should be very similar.
Stripe example​
Let's consider a SaaS business that uses Stripe. For every Customer entity, you as a Stripe admin should create at least one Subscription entity.
This is often done automatically through payment links, pricing tables, or the Stripe API.
Each payment on the platform will be tied to an Invoice entity.
As Subscription has a period_start and period_end date, the invoice will
inherit those dates. Invoices also have the amount_paid field.
In its simplest form, the Invoice entity has these fields:
period_startperiod_endamount_paidsubscription_idcustomer_id
These fields are sufficient to calculate the MRR and Subscriber metrics for your business.
Edge cases to consider​
Of course, payment is a very complex topic and many edge cases may happen.
Just to name a few:
- Discounts were given
- Taxes and fees apply
- The customer upgrades or downgrades an existing subscription
- The customer asks for a refund
- Not all invoices will have subscriptions (this can happen in custom or enterprise deals)
- etc.
The edge cases above are stored in Stripe and various Stripe entities.
However, the simplest way to find out what happened during an invoice payment is to look at the invoice_line_items entity.
Together with the Invoice entity, it contains all information about the payment and the reasons behind final payment amount.
Product and subscription intervals​
Often, it is handy to segment payments based on the product that you sold or the interval of the subscription (e.g. yearly, monthly, quarterly) These will give your business great insights and help you understand what drives your revenue.
You need to correlate the product information with the invoices to have this information.
This is present in Stripe's Product entity, which can be linked to the Invoice entity through the plan field.
Customers vs. users​
As discussed, event analytics is always tied to a user or group. The primary identifier of the users is the user_id field.
That should be present in every event that was logged in the application or service.
In the case of Stripe, we have the Customer entity. Typically, the customer_id is not the same as the user_id in the application.
Therefore, in its basic form, the payment events are not possible to use in product analytics.
The simplest way to overcome this is to link the user_id as metadata on Stripe.
This way, we will have the actual user_id from the application or service for every paying customer.
Wrapping it up​
To summarize, the best way to model your revenue as events from Stripe (and other platforms) is
have every invoice_lint_item, invoice, customer, and subscription entity joined in a single table.
At Mitzu, we have created a template data model SQL code to cover the most common use cases. This SQL should be an example. You can use it as a starting point.
It all comes down to how you move your Stripe data to your data warehouse.
Stripe has a data warehouse mirroring capability for Snowflake and Redshift (at the moment). The code snippet above can work on top of that data format with Snowflake SQL flavor.
Revenue settings in Mitzu​
Once you have created your final table or materialized view containing the joined Stripe data, you can use it as an event table on the event table settings page.
Then, head over to the revenue settings page and configure the revenue settings.
Once done, you can calculate MRR and Subscriber metrics from the insights page.