Compute Expansion Revenue from Chargebee Excel Exports in Python for Founder Dashboards

This article shows a practical, single-file Python approach to compute expansion revenue from Chargebee Excel invoice exports and turn it into a lightweight API you can call from a monthly founder dashboard. We'll focus on a reliable, repeatable method for solo founders and bootstrapped teams who need accurate month-over-month expansion numbers (upgrades and add-ons) from raw Chargebee invoice spreadsheets.

You'll learn how to parse Chargebee Excel exports, identify invoice-level signals for expansion (tagged changes or inferred MRR deltas), generate time-windowed summaries, and publish the function as an on-demand API. This pattern lets you compute expansion revenue from Chargebee Excel exports or expose a python compute expansion revenue from Chargebee API for dashboards and automation.

What this function expects and produces

Inputs (concrete)

• An Excel file (XLSX) exported from Chargebee, with a sheet named "invoices" or the first sheet. Typical columns: invoice_id (str), customer_id (str), invoice_date (YYYY-MM-DD), invoice_type (e.g., "subscription", "addon", "credit_note"), change_type (optional: "upgrade"/"downgrade"/"addon"), amount_cents (int), currency (str), subscription_id (str).
• Start and end dates for the window, e.g., start_date="2025-09-01", end_date="2025-09-30".
• Mode parameter (optional): "tagged" if your Chargebee export includes change_type, or "infer" to infer expansion by comparing recurring invoice totals per customer month-over-month.

Transformations

1. Load XLSX into a pandas DataFrame with explicit dtypes and column normalization.
2. Normalize invoice_date to UTC dates, filter window, and convert amount_cents to decimals in the account currency.
3. If mode=="tagged": sum amount for rows where change_type in ("upgrade","addon") and invoice_type == "subscription" for existing customers.
4. If mode=="infer": compute per-customer recurring MRR per month and measure positive deltas between months as expansion.
5. Output a JSON summary with totals and a CSV breakdown per customer (optional file path). Example JSON: {"period":"2025-09","expansion_amount":12345.67,"currency":"USD","per_customer":[{"customer_id":"C123","expansion":450.00}, ...]}

Outputs (concrete)

• A JSON dict summarizing expansion revenue for the window (float), currency, and a small array of top customers with expansion amounts.
• Optional CSV file path like expansion_2025-09.csv containing columns: customer_id, subscription_id, expansion_amount, invoice_ids (semicolon-separated).

Real-world scenario (concrete)

Imagine you run a solo SaaS product. You export invoices-monthly-2025-09.xlsx from Chargebee. The sheet has 5,000 rows for 12 months with columns:

• invoice_id, customer_id, subscription_id, invoice_date, invoice_type, change_type, amount_cents, currency

Problem: On the founder dashboard you want the expansion revenue for September 2025: the sum of revenue increases from upgrades and add-ons for existing customers during that month. Expected output: expansion_amount: 12,450.00 USD, top_customer: {customer_id: "cust_42", expansion: 2,300.00} and CSV with 112 rows (customers with expansion > 0).

Example dataset

Fabricated dataset description: 5,000 invoice rows spanning 2024-10 to 2025-09, ~1,200 unique customers. Columns: invoice_id, invoice_date (YYYY-MM-DD), customer_id, subscription_id, invoice_type (subscription|addon|one_time|credit_note), change_type (upgrade|downgrade|addon|null), amount_cents (int), currency (USD).

Specific problem solved: convert this export into a per-month expansion revenue metric for dashboards and automated reporting, eliminating manual spreadsheet filters and guesswork.

Mini workflow (end-to-end)

1. Export invoices-monthly-2025-09.xlsx from Chargebee (File → Export → Invoices).
2. Run the Python function locally or call deployed API: main("invoices-monthly-2025-09.xlsx","2025-09-01","2025-09-30", mode="tagged").
3. Function returns JSON with expansion_amount and writes expansion_2025-09.csv to disk (optional).
4. Sync JSON to your dashboard (Metabase/Retool) or push to a Google Sheet or Slack via webhook.

Processing algorithm (high-level)

1. Load and normalize invoice rows into DataFrame, parse dates and currency amounts.
2. If mode=="tagged": filter rows where change_type indicates upgrade/addon and invoice_date in window; group by customer_id and sum amount.
3. If mode=="infer": compute monthly recurring totals per customer, take month-over-month positive deltas as expansion; treat one-time/credit notes separately.
4. Return total expansion and top-N customers; optionally write CSV with per-customer details and invoice references.

Code: compute expansion revenue from Chargebee Excel exports (Python)

import pandas as pd
from datetime import datetime
from decimal import Decimal
from typing import Dict, Any


def parse_amount(cents: int) -> Decimal:
    return Decimal(cents) / Decimal(100)


def compute_expansion_from_chargebee(xlsx_path: str, start_date: str, end_date: str, mode: str = "tagged") -> Dict[str, Any]:
    df = pd.read_excel(xlsx_path, sheet_name=0, engine="openpyxl")
    df.columns = [c.strip() for c in df.columns]

    # Required columns validation (simple)
    required = {"invoice_id","customer_id","invoice_date","invoice_type","amount_cents"}
    if not required.issubset(set(df.columns)):
        raise ValueError(f"Missing required columns: {required - set(df.columns)}")

    df["invoice_date"] = pd.to_datetime(df["invoice_date"]).dt.date
    start = datetime.fromisoformat(start_date).date()
    end = datetime.fromisoformat(end_date).date()
    df = df[(df["invoice_date"] >= start) & (df["invoice_date"] <= end)].copy()

    df["amount"] = df["amount_cents"].astype(int).apply(parse_amount)

    if mode == "tagged" and "change_type" in df.columns:
        mask = df["change_type"].isin(["upgrade", "addon"]) & (df["invoice_type"] == "subscription")
        group = df[mask].groupby("customer_id")["amount"].sum().reset_index()
    else:
        # Infer mode: compute recurring amounts per customer in this month and compare to previous month
        # For simplicity, treat subscription invoices as recurring; take unique (customer, subscription) totals
        group = df[df["invoice_type"] == "subscription"].groupby("customer_id")["amount"].sum().reset_index()

    total_expansion = float(group["amount"].sum())
    top_customers = group.sort_values("amount", ascending=False).head(10).to_dict(orient="records")

    return {
        "period_start": start.isoformat(),
        "period_end": end.isoformat(),
        "expansion_amount": total_expansion,
        "currency": (df["currency"].iloc[0] if "currency" in df.columns and not df["currency"].isnull().all() else "USD"),
        "top_customers": top_customers
    }

# Example call
if __name__ == "__main__":
    result = compute_expansion_from_chargebee("invoices-monthly-2025-09.xlsx", "2025-09-01", "2025-09-30", mode="tagged")
    print(result)