Guide
Chargebee Billing Analytics with BigQuery
Build a Chargebee to BigQuery pipeline for SaaS MRR, retention, revenue concentration, and dunning analysis with the open-source Bruin CLI.
The whole project is one command. Run it, point your coding agent at the Bruin MCP, and it can configure and run everything itself.
$ bruin init chargebee-bigqueryThree ways to go from here:
- Self-service it. Let the agent configure and run the whole thing, and ask it questions as you go.
- Follow this tutorial. Slower, and it explains why each setting matters - the part an agent will not guess for you.
- Hand the tutorial to the agent. Point it at this page and have it work through the steps with you.
The template README is the short reference for everything it ships.
What
Build a Chargebee billing analytics pipeline in BigQuery, starting from a template and ending with a dashboard you can run locally. You will have five raw source tables, typed staging models, a daily MRR history, and reports for SaaS revenue and collections.
chargebee_raw- Chargebee customers, subscriptions, invoices, transactions, and events loaded byingestrchargebee_stage- conformed billing models, recurring item MRR, payment flags, and the daily customer-currency snapshotchargebee_reports- MRR movements, retention, billings, plan mix, concentration, and dunning riskdashboards/- a Dashboards as Code file with Overview, Retention & Expansion, Monetization & Portfolio, and Collections & Risk tabs
The project keeps money in native currency minor units and reports each currency separately. That sounds like a small implementation detail until somebody asks why a single MRR number changed after a EUR customer signed up.
How
bruin init chargebee-bigquery writes the pipeline, connection placeholders, SQL assets, checks, and dashboard. You will run the raw, staging, and reports layers separately so each boundary is visible when something needs investigation. Then you will serve the dashboard with DAC and use an agent to extend the project.
The template is designed for seed to Series A SaaS teams that have outgrown Chargebee RevenueStory reports and need definitions they can review, join to CRM data, and keep in Git. It is open source and runs locally. BigQuery storage and query charges are separate from Bruin.
The current source is already in the Bruin repository. Use the Bruin CLI release that includes chargebee-bigquery when this guide is published; older binaries will not know the template name yet.
Before you start
- A Chargebee API key and your Chargebee site name (the
xxxinhttps://xxx.chargebee.com) - A Google Cloud project with BigQuery enabled and a service account JSON key that can create datasets and tables
- Git installed
- The Bruin CLI release that includes the
chargebee-bigquerytemplate
Guide overview
- 1Set Up Your EnvironmentInstall the Bruin CLI and DAC, create Chargebee and BigQuery connections, and initialize the template.
- 2Run the Raw Chargebee LayerLoad customers, subscriptions, invoices, transactions, and events into BigQuery with ingestr.
- 3Build the Staging LayerType Chargebee data, normalize recurring items, flag MRR eligibility, and write the daily snapshot.
- 4Publish SaaS Revenue ReportsCreate MRR, retention, billing, plan, concentration, and failed-payment reports from the snapshot history.
- 5Serve the Dashboard LocallyValidate, check, and serve the Chargebee billing dashboard on localhost with DAC.
- 6Extend and Deploy the PipelineAdd an account crosswalk, change the metric policy, port the models, and choose a deployment target.
Frequently asked questions
How do I load Chargebee data into BigQuery?
Initialize the open-source `chargebee-bigquery` template with `bruin init chargebee-bigquery`, configure your Chargebee API key and BigQuery connection, then run the raw layer. The template uses ingestr to load customers, subscriptions, invoices, transactions, and events into the `chargebee_raw` dataset.How does the Chargebee BigQuery template calculate MRR?
The staging layer unnests recurring subscription items, normalizes each item to a monthly run rate, and applies the `mrr_active_statuses` pipeline variable. A daily customer and currency snapshot preserves history so later reports can classify new, expansion, contraction, churn, and reactivation movements.What reports are included in the Chargebee analytics pipeline?
The template includes MRR by customer, MRR movements, subscription KPIs with GRR and NRR, invoice billings, MRR by plan, revenue concentration, and failed-payment dunning reports. It also ships a four-tab DAC dashboard for overview, retention, monetization, and collections.Can I use the Chargebee template with a warehouse other than BigQuery?
The template targets BigQuery, but the layers, metric definitions, snapshot strategy, and checks are portable. An agent can translate the asset types, destination, and BigQuery SQL functions for Snowflake, Databricks, ClickHouse, Redshift, Postgres, or another supported warehouse.Does the pipeline combine currencies?
No. Money remains in native minor units and every report keeps currency in its grain. The template does not invent an FX policy, so a dashboard cannot quietly add USD, EUR, and JPY together. Convert at the reporting layer only after you define an FX source and policy.How long does this Chargebee analytics tutorial take?
The guided setup takes about 22 minutes, excluding the first Chargebee backfill and BigQuery query time. The first run establishes the snapshot baseline; month-over-month retention and movement metrics need two contiguous monthly observations.
Resources
Get help & contribute