Generating Churn Rate Reports from Stripe Excel Exports in Python for Zapier and n8n

This article shows how to convert raw Excel invoice exports from Stripe into a repeatable, investor-ready churn rate report you can call from Zapier, Make, or n8n. You will get a concrete Python implementation that reads a Stripe-style Excel export, computes monthly customer churn and MRR churn for a specified window, and writes CSV/JSON outputs suitable for automated investor updates.

We target indie hackers and small agencies who manage multiple clients and need a reliable way to pipeline Stripe invoice exports into a signed-off metric for investor updates or weekly decks.

What this function expects and produces

Input: an Excel file (XLSX) exported from Stripe invoices. The script expects a sheet containing one row per invoice with these columns (exact names used in code below):

• invoice_id (string)
• customer_id (string)
• status (string) — e.g., paid, open, void
• amount_due_cents (integer) — total in cents
• currency (string) — e.g., USD
• invoice_date (ISO 8601 string or Excel datetime) — e.g., 2024-03-15T10:00:00Z
• plan_interval (string) — monthly or yearly

Processing steps (concrete): parse timestamps, filter to paid invoices, normalize amounts to monthly MRR-equivalent (divide yearly amounts by 12), compute per-customer last-paid dates and per-month active sets, compute monthly customer churn rate and MRR churn, and export a CSV and JSON summary.

Output: CSV file (e.g., churn_report_2024-03.csv) with columns month (YYYY-MM), active_customers, churned_customers, customer_churn_rate, starting_mrr_usd, churned_mrr_usd, mrr_churn_rate; plus a JSON summary for APIs.

Real-world scenario

Imagine an agency with 3 clients. For a single client you have a single Excel export named client_acme_invoices.xlsx containing 1,200 invoice rows for 2023-2024 and roughly 450 unique customer_ids. Investors want a monthly churn table covering the last 6 months, plus an explanation of how churn was measured.

Concrete example input row (CSV-like):

invoice_id,customer_id,status,amount_due_cents,currency,invoice_date,plan_interval
in_001,cust_12,paid,2000,USD,2024-02-05T15:12:00Z,monthly
in_002,cust_12,paid,24000,USD,2024-01-01T09:00:00Z,yearly
in_003,cust_24,void,0,USD,2024-01-12T08:00:00Z,monthly

Example dataset and the exact problem

Dataset: 1,000 rows of Stripe invoice exports covering 12 months with columns above. About 300 unique customers, mix of monthly and yearly plans. Problem to solve: investors want a deterministic monthly customer churn rate and corresponding MRR churn for the last 6 months. The challenge: Stripe exports include yearly invoices (need conversion), refunds/void rows, and inconsistent date formats.

Step-by-step workflow (end-to-end)

1. Get the monthly Stripe invoice export XLSX for a client (file: client_acme_invoices.xlsx).
2. Upload the file to an automation platform (Zapier/Make) or place in a cloud bucket.
3. Call the churn-report Python API (or run locally) to compute a 6-month churn table.
4. Save outputs: churn_report_2024-03.csv and churn_summary_2024-03.json.
5. Attach CSV/JSON to investor update email or push into Notion/Google Sheets via Zapier.

Algorithm (how churn is calculated)

1. Filter invoices to status == 'paid' and normalize invoice_date to UTC.
2. Convert amount_due_cents to monthly MRR-equivalent: if plan_interval == 'yearly', amount / 12; else amount as-is.
3. For each calendar month M compute: active_start = customers with a paid invoice in (M-1 window, i.e., within 30 days before M start).
4. churned_in_M = subset of active_start who have no paid invoice in the 30 days after M start.
5. customer_churn_rate = churned_in_M / len(active_start); mrr_churn = sum(MRR of churned customers) / starting_mrr.

Python implementation example

The snippet below is a minimal, runnable example using pandas and openpyxl. It reads an XLSX, computes a 6-month churn table, and writes CSV/JSON outputs.

import pandas as pd
from datetime import timedelta


def generate_churn_report(xlsx_path, months=6, churn_window_days=30, out_prefix='churn_report'):
    df = pd.read_excel(xlsx_path, engine='openpyxl')
    # normalize column names expected by this script
    df = df.rename(columns=str.strip)
    df['invoice_date'] = pd.to_datetime(df['invoice_date'], utc=True)
    # keep only paid invoices
    df = df[df['status'].str.lower() == 'paid'].copy()
    # convert cents to USD float
    df['amount_usd'] = df['amount_due_cents'] / 100.0
    # normalize to monthly MRR-equivalent
    df['mrr_usd'] = df.apply(lambda r: r['amount_usd'] / 12.0 if r['plan_interval']=='yearly' else r['amount_usd'], axis=1)

    last_date = df['invoice_date'].max().normalize()
    start_month = (last_date - pd.offsets.MonthBegin(months-1)).normalize()

    rows = []
    for i in range(months):
        month_start = (start_month + pd.DateOffset(months=i)).normalize()
        month_label = month_start.strftime('%Y-%m')
        before_window_start = month_start - pd.Timedelta(days=churn_window_days)
        after_window_end = month_start + pd.Timedelta(days=churn_window_days)
        # active at start: paid in the churn_window_days before month_start
        active_start = set(df[(df['invoice_date'] >= before_window_start) & (df['invoice_date'] < month_start)]['customer_id'].unique())
        # customers with paid invoice in the churn window after month_start
        active_after = set(df[(df['invoice_date'] >= month_start) & (df['invoice_date'] < after_window_end)]['customer_id'].unique())
        churned = sorted(list(active_start - active_after))
        starting_mrr = df[df['customer_id'].isin(active_start)].groupby('customer_id')['mrr_usd'].max().sum()
        churned_mrr = df[df['customer_id'].isin(churned)].groupby('customer_id')['mrr_usd'].max().sum()
        customer_churn_rate = (len(churned) / len(active_start)) if active_start else 0.0
        mrr_churn_rate = (churned_mrr / starting_mrr) if starting_mrr else 0.0
        rows.append({
            'month': month_label,
            'active_customers': len(active_start),
            'churned_customers': len(churned),
            'customer_churn_rate': round(customer_churn_rate, 4),
            'starting_mrr_usd': round(starting_mrr, 2),
            'churned_mrr_usd': round(churned_mrr, 2),
            'mrr_churn_rate': round(mrr_churn_rate, 4)
        })

    out_df = pd.DataFrame(rows)
    csv_path = f'{out_prefix}.csv'
    json_path = f'{out_prefix}.json'
    out_df.to_csv(csv_path, index=False)
    out_df.to_json(json_path, orient='records', date_format='iso')
    return {'csv': csv_path, 'json': json_path}

# Example call
if __name__ == '__main__':
    result = generate_churn_report('client_acme_invoices.xlsx', months=6)
    print('Wrote:', result)

How Functory Makes It Easy

To publish this as a Functory function, wrap the core logic in a single Python main(...) entrypoint that accepts typed parameters: main(file_path: str, months: int = 6, churn_window_days: int = 30) and returns a JSON-like dict or path to the generated CSV. On Functory you must pick an exact Python version (for example, 3.11.11) and provide a requirements.txt with pinned versions, one per line (e.g., pandas==2.1.1, openpyxl==3.1.2). The platform will expose the parameters as inputs in the web UI and as JSON fields on the HTTP API.

Implementation notes for Functory:

• Put your code in a file with main(...) that reads the uploaded file path (FilePath) and writes a path-like return (e.g., '/tmp/churn_report.csv').
• List dependencies pinned to exact versions so Functory provisions the correct environment.
• Triggering: you can call the Functory HTTP API from Zapier/Make/n8n by uploading the XLSX (file multipart) or providing a cloud URL; Functory returns the CSV file URL once processing finishes.

Benefits: no servers to manage, automatic CPU scaling, built-in logging (print()), and pay-per-use billing. You can chain functions — e.g., pre-processing in one function, churn calculation in another, and a reporting function to push results into Notion or Google Sheets.

Alternatives and why this approach is better

Common alternatives: manual spreadsheets where analysts filter and pivot; using the Stripe Dashboard (manual snapshots); or custom ETL pipelines in Airflow/Dagster. Manual spreadsheets are error-prone and slow; Stripe Dashboard gives visualizations but not reproducible CSVs for investor decks; heavy ETL systems are overkill for indie hackers.

This function-based approach is superior because it is reproducible, automatable (callable via Zapier/n8n), lightweight (single-file script to maintain), and gives consistent definitions (exact churn_window_days and conversion rules). For small teams, it replaces a manual 2–3 hour monthly task with a single API call.

Business impact

Quantified benefit: converting a manual spreadsheet process to this automated API reduces per-report preparation time from ~2 hours to under 5 minutes — a ~95% reduction in manual time. For an agency creating 10 client updates per month, that’s ~20 hours saved monthly.

Industry context

According to a 2024 OpenView SaaS benchmark report, median annual gross revenue churn for SaaS companies ranges around 5–7% depending on stage and product (OpenView, 2024). Investors commonly request month-over-month churn tables to validate these numbers.

Comparison to other developer workflows

Notebooks: quick to prototype but hard to automate and integrate with Zapier/n8n. CLI scripts: reproducible but require servers or cron jobs to be kept running. Functory-style API: one-file logic that becomes a hosted endpoint, removing deployment friction while keeping reproducibility and auditability.