The Stripe Modeling Guide We Wish We Had

Stripe is an incredibly powerful payment processor, but once you start trying to actually work with its data for analytics and financial reporting, you quickly realize... it's not exactly plug-and-play. The UI is really slick, but you probably won’t get everything you need there.

At Database Tycoon, we recently wrapped up a project where we modeled Stripe data in dbt to build clearer and more robust financial and revenue insights. Along the way, we uncovered some key lessons that made a huge difference in getting accurate numbers, especially for metrics like Monthly Recurring Revenue (MRR).

Here’s a breakdown of some of the first hurdles we ran into and what we recommend if you're diving into Stripe modeling yourself.

1. Timezones: Everything’s in UTC

The Challenge: Stripe stores all timestamps in Coordinated Universal Time (UTC): subscription start dates, invoice payments, refunds, you name it. If you're trying to reconcile transactions or calculate MRR across different time zones without adjusting for this, it’s really easy to misreport your revenue or see weird timing mismatches.

Our Take:

✅ In your dbt models, standardize everything to your business’s reporting time zone before you do any revenue aggregation.

✅ Double-check all your join conditions where timestamps are involved (especially for things like subscription start dates and payment dates). Even a small offset can throw off your numbers.

2. MRR Tracking: Watch Out for Canceled Customers

The Challenge: Once a subscription is cancelled, the subscription no longer counts towards your Monthly Recurring Revenue (MRR) i.e Stripe drops canceled customers from MRR calculations the moment they cancel, even if they're still in the middle of a paid service period. That means your revenue can look like it’s dipping, even when customers are technically still active and should be counted for the rest of their service period (and considered in your customer experience plan!).

Our Take:

✅ If you're using Stripe’s UI reports, check the setting for “Include canceled subscriptions in MRR calculations.” Adjust depending on what’s right for your business model.

✅ In your dbt models, use the canceled_at timestamp carefully. Don’t treat a cancellation as an immediate revenue loss if the customer is still within a paid service period.

✅ Build logic to track revenue through the end of the service period, not just until the cancellation.

3. Coupon Code Mayhem: Discounts Aren’t Always Straightforward

The Challenge: Stripe applies discounts through coupons, but the way these flow into invoices, subscriptions, and revenue can be tricky, especially when you're trying to model net revenue accurately. For example, a subscription might look like it's worth $100/month, but if there's a 20% discount, your real revenue is $80, and that needs to be reflected in your MRR and financial reporting.

Our Take:

✅ Always check whether a discount is recurring or one-time, and tie it to the right invoice when calculating revenue.

✅ Be extra careful with promotions, trial periods, and discounts that phase out over time, your MRR logic needs to account for those transitions cleanly.

4. Stripe Sigma: Good for Spot Checks, Not Deep Analysis

The Challenge: Stripe Sigma lets you query your Stripe data using Snowflake SQL, which sounds awesome, but it has a lot of limitations when it comes to joins, historical data, and flexibility. It’s great for quick validations, but it's not a substitute for real modeling in your own data warehouse (not to mention Stripe Sigma uses Snowflake SQL under the hood, so its queries aren't guaranteed to be compatible with other warehouses like Redshift or BigQuery without modification.)

Our Take:

✅ Use Stripe Sigma to quickly validate your dbt models against Stripe’s source of truth.

✅ But for anything serious, extract the raw Stripe data into your warehouse and use a transformation tool like dbt to transform and manage it properly.

✅ Set up tests to automate consistency checks instead of manually running Sigma queries every time.

5. Currency Conversions: Timing Matters More Than You Think

The Challenge: Stripe processes subscriptions and transactions in the customer’s original (presentment). However, the actual FX conversion to your default settlement currency happens later, when Stripe initiates the payout. This means the payment date and the conversion (settlement) date can differ, and if you're not careful, this can lead to mismatches in revenue or cash flow reporting.

Our Take:

✅ For accurate subscription analytics and MRR modeling, validate both the exchange rate and payout amount, especially if you’re reconciling cash flow or doing multi-currency reporting.

✅ Stripe does not store the historical FX rate used during conversion; instead, it only records the final converted amount. If you're not using FX Quotes, you'll need an external FX rate source tied to the transaction or payout date to calculate currency impacts manually.

Final Thoughts

Working with Stripe data is totally doable but it’s a little like solving a Rubik’s cube. You have to understand exactly how timestamps, cancellations, discounts, and currencies flow through the system, or your financial reporting will drift from reality fast.

The good news? With solid dbt modeling, thoughtful decisions around revenue logic, and a bit of extra care when handling service periods and FX rates, you can build a Stripe analytics stack that’s both reliable and scalable.

We’d love to hear how others are approaching Stripe + dbt—what’s worked, what hasn’t, and the lessons you’ve learned along the way.

We’ll be at the Stripe conference next week—come say hi and swap stories!