Build a Chargebee to BigQuery pipeline with open-source Bruin and ingestr. Model MRR, retention, plan mix, revenue concentration, and failed-payment risk in SQL your team can review.
Arsalan Noorafkan
Developer Advocate
Quick answer: Chargebee is good at billing. It is not meant to be your warehouse, your account model, or the place where every team agrees on MRR. A Chargebee analytics pipeline copies the source data into BigQuery, turns it into typed SQL models, records daily MRR, and publishes reports your finance, revenue, and product teams can join to the rest of the business. The open-source Chargebee to BigQuery template gives you the first version without building a connector and a reporting project from scratch.
The trigger is usually a revenue meeting. Someone has Chargebee's MRR, someone else has a board spreadsheet, and a third person has an answer from the CRM. The figures are close enough to be awkward and far enough apart to stop the conversation.
That is not a Chargebee bug. Chargebee is doing what a billing platform should do. The problem is that billing questions become company questions as soon as a SaaS team grows: which accounts are expanding, which plans create concentration risk, and whether failed payments are recoverable before they become churn.
Chargebee's built-in reporting is useful for operating Chargebee. The boundary appears when you need a metric that combines billing with the rest of your business.
| Chargebee surface | Good for | Where it stops |
|---|---|---|
| Billing and subscription views | Current subscription state, invoices, transactions, and customer activity | A fixed product boundary and current-state questions |
| RevenueStory reports | Standard recurring-revenue and retention views | Definitions and dimensions that do not match your finance or board policy |
| Chargebee APIs | Extracting source objects for a warehouse or custom process | Pagination, retries, schema handling, history, models, and checks become your job |
| Chargebee plus a warehouse | Joining billing to CRM, product, support, or marketing data | You still need ingestion, modelling, schedules, metric policy, and quality checks |
The last row is the right destination for most growing SaaS teams. It is also where the work tends to sprawl. A connector loads data. A transformation tool defines models. An orchestrator runs them. A dashboard tool holds the definitions everyone is debating. The data may be correct, but nobody can explain which version of MRR they are looking at.
The useful part of a warehouse model is not copying every API field. It is making the questions explicit.
Monthly recurring revenue is a run rate, not recognized revenue and not cash collected. A useful model tells you the customer, currency, active subscription count, MRR in native minor units, and the fields you will use to segment the account.
Chargebee subscriptions are mutable. If a customer changes from a $500 plan to a $300 plan, querying the subscription today does not reconstruct the $500 state from three months ago. The template addresses this with customer_currency_daily_mrr_snapshot, an insert-only history by customer, currency, and pipeline date. A rerun replaces only that date.
The trade-off is straightforward: history starts when the snapshot starts. You can backfill lifecycle episodes from dates on the subscription, but you cannot recreate every historical price without observations from when that price was active.
Once you have monthly snapshots, you can classify the change for each customer:
The important detail is the grain. Sum a customer's subscriptions before classifying the movement. Otherwise a plan swap can look like churn plus new business in the same month.
The template also keeps currencies separate. There is no honest way to calculate a consolidated MRR or NRR number until you choose an FX source, a conversion date, and a policy for rate changes. Keeping the currency column in every report makes that missing decision visible instead of silently wrong.
Finance will ask why billed revenue does not equal MRR. That is a good question. They are different measurements.
MRR is a normalized run rate for eligible recurring items. Billings come from invoices and depend on invoice state, finalization date, credits, payment timing, and one-off charges. The template has a monthly_invoice_billings report so those questions do not get forced into the MRR model.
Chargebee can tell you what a plan costs. A warehouse model can tell you how much of your current MRR depends on that plan, which add-ons are growing, and how much revenue comes from the top one, five, or ten customers.
This is the difference between a billing report and a planning input. Packaging decisions need plan-level MRR. Enterprise risk needs concentration. Neither should be a manual export that gets updated before the board meeting.
A failed payment is not automatically churn. Some failures recover during the retry window. Others become involuntary churn risk after retries are exhausted.
The failed_payment_dunning report separates recoverable and at-risk failed amounts using a configurable dunning_retry_window_days variable. The default is 30 days. Your policy may be different, but it should live in pipeline.yml, not in somebody's memory.
The Chargebee BigQuery template is a three-layer Bruin project.
chargebee_raw uses ingestr to load customers, subscriptions, invoices, transactions, and events. The raw boundary preserves what Chargebee sent so you can investigate a model without making the source API your only evidence.
chargebee_stage types timestamps, normalizes customer fields, unnests subscription items, calculates a monthly run rate, flags MRR eligibility, and writes the daily snapshot. Amounts stay in NUMERIC minor units with a _minor suffix.
chargebee_reports publishes:
| Report | Question |
|---|---|
monthly_mrr_by_customer | Which accounts pay what, by month and currency? |
monthly_mrr_movements | Why did MRR change? |
monthly_subscription_kpis | What are MRR, ARR, GRR, NRR, logo churn, and ARPA? |
monthly_invoice_billings | What was billed and collected? |
mrr_by_plan | Which plans and add-ons drive MRR? |
revenue_concentration | How dependent are we on the largest accounts? |
failed_payment_dunning | Which failed payments are recoverable or at risk? |
It also ships dashboards/chargebee-billing-analytics.yml, a DAC dashboard with Overview, Retention & Expansion, Monetization & Portfolio, and Collections & Risk tabs. The dashboard is YAML in the same repository as the pipeline. That matters when a metric changes because the review is a pull request, not a hunt through a UI's revision history.
The fastest way to evaluate this is not to design a perfect data platform. It is to run a small, real pipeline against a test site and see whether the reports answer the questions that keep coming up.
bruin init chargebee-bigquery
cd chargebee-bigquery
bruin validate .
bruin run --full-refresh .
Set CHARGEBEE_SITE to the site name only, configure CHARGEBEE_API_KEY, and give the gcp-default connection a BigQuery project and service account. The first full refresh loads the available history. The daily schedule then keeps raw tables current and records a new MRR snapshot.
The template includes pipeline variables for the decisions most likely to differ between companies:
bruin run --var concentration_top_n=5 \
--var 'mrr_active_statuses=["active","non_renewing","in_trial"]' \
--var dunning_retry_window_days=45 .
Change the policy deliberately. If trials are not committed revenue for your company, do not count them as MRR because a dashboard made the option easy to turn on.
There is no prize for writing your own Chargebee API client. Pagination, retries, incremental keys, and schema changes are boring work, which is precisely why they are easy to get subtly wrong.
The question is where you want the ownership to sit:
| Approach | Fits when | Trade-off |
|---|---|---|
| Chargebee reports only | The question stays inside billing | Limited joins, fixed definitions, and current-state history |
| Managed connector plus BI | You want someone else to operate extraction | Models, checks, metric policy, and warehouse cost remain separate |
| Custom API scripts | The extraction is genuinely unusual | You own pagination, retries, backfills, and schema drift |
| Bruin template | You want ingestion, SQL, checks, lineage, and scheduling as files | You need a team comfortable reviewing Git and SQL |
Bruin's case is simple. The CLI and ingestr are open source, the project runs locally, and the definitions are visible. Bruin Cloud is the managed step when you want scheduling, run history, catalog, lineage, access controls, and audit logs without operating all of that yourself.
That split is useful for startups. You can prove the reports on your own machine, deploy them to your VPC or a small runner, and add managed governance when the workflow has earned it.
A few caveats are worth keeping close to the SQL:
MRR is not revenue. It is a normalized recurring run rate. It does not replace revenue recognition, bookings, or cash reporting.
The first month is a baseline. Movement and retention need two contiguous monthly snapshots. Empty movement fields at the start are expected.
Currencies do not add themselves. Native minor units are safer than a fake global total. Add FX when you have a policy, not because the dashboard wants one number.
Snapshots do not recover history you never observed. Start the daily schedule before you need a clean churn trend. Future you will be grateful, which is not always a phrase I trust, but it applies here.
Once the first reports are useful, join chargebee_stage.customers to your CRM with a declared account crosswalk. Prefer an explicit CRM foreign key, then billing email, then a normalized company domain. Add product usage or support data after that.
The next reports usually become obvious: MRR by acquisition channel, expansion risk by account owner, CAC payback, trial conversion, or product usage before a failed payment. They are not generic dashboard widgets. They are the questions your team already asks, now backed by a model you can inspect.
Start with the Chargebee Billing Analytics guide for the commands, or read the template README if you want to inspect the assets first.
Stripe's dashboard, Sigma, and Data Pipeline each stop somewhere. Here is where they stop, how a three-layer Stripe analytics pipeline fixes it, how the tooling options compare, and why starting from a free local template beats assembling a stack.
PostHog is excellent at what it does and silent outside its own boundary: no retroactive account rollup, no join to billing or CRM, and an events API with a backfill trap most teams find the hard way. Here is where PostHog's own reporting stops, how a three-layer pipeline fixes it, and why starting from a free local template beats assembling a stack.
Learn how to load Shopify data into ClickHouse, choose an ingestion method, model a bronze-silver-gold ecommerce warehouse, and use Bruin to spend less time maintaining data tooling.
Practical updates on open-source data pipelines, AI analysts, governance, and what we are shipping at Bruin.
The signup form is hosted by Brevo. Allow marketing cookies to load it.