Functory
functory.
All Articles
6 min read
Functory

Generate Excel-ready retention cohort reports from Postgres CSV snapshots with Python and an API

This article shows how to turn messy CSV exports (snapshots) from Postgres that contain feature usage events into a polished retention cohorts report suitable for finance and product review meetings — delivered as a single downloadable Excel URL. You'll get a precise, single-file Python implementation pattern that reads event CSVs, computes weekly acquisition cohorts, calculates N-week retention rates, writes an Excel workbook (one sheet per metric), and exposes that workbook via an API endpoint so non-technical stakeholders can open a link in Excel.

We focus on reproducibility: exact input schema, code you can run locally or publish as a Functory function, and concrete examples with column names, sample rows, and an expected Excel layout.

What this script expects and produces

Input: a CSV snapshot exported from Postgres with these columns:

  • user_id (string or int)
  • event_time (ISO 8601 timestamp; e.g., 2024-10-05T14:22:31Z)
  • event_type (string; e.g., "feature_click", "feature_use")
  • feature_name (string; e.g., "reports_v2")
  • plan_tier (string; e.g., "free", "pro", "enterprise")
  • revenue_usd (float; optional, 0.0 if none)

Transformation steps performed:

  • Parse timestamps and normalize to UTC weeks (ISO week start Monday).
  • Identify each user’s acquisition week (first event_time in the CSV window).
  • Build weekly cohorts (acquisition cohort by week) and calculate week-0 through week-N retention — both raw user counts and percentage of cohort retained.
  • Pivot results into a human-friendly table with cohort-start-date rows and week columns (Week 0, Week 1, ... Week N).
  • Write a multi-sheet Excel workbook with sheets: "cohort_counts", "cohort_pct", and "summary_metrics" (MAU, cohort sizes, average retention at week 4 and week 12).

Output: an .xlsx file (e.g., retention_report_2025-03-01.xlsx) with three sheets and a predictable layout developers and finance teams can open in Excel or Google Sheets.

Real-world scenario

Imagine a SaaS analytics startup exports weekly snapshots of feature events from Postgres. A finance lead opens Excel and wants a cohort retention table to evaluate the newest billing model impact.

Concrete input snippet (CSV):

user_id,event_time,event_type,feature_name,plan_tier,revenue_usd
42,2025-02-03T09:12:01Z,feature_use,reports_v2,pro,0.0
73,2025-02-03T09:35:21Z,feature_use,reports_v2,free,0.0
42,2025-02-10T11:02:11Z,feature_use,reports_v2,pro,0.0
101,2025-02-17T08:03:00Z,feature_use,api_export,enterprise,49.99

Expected Excel output (cohort_pct sheet): rows like "2025-02-02" (cohort week start), columns "Week 0", "Week 1", ..., values like 100%, 42%, 28%.

Example dataset and exact problem

Dataset: 1000 rows of event snapshots over 16 weeks, columns as above. Problem: weekly exports to S3/Dropbox are handed to a financial analyst who manually filters and creates pivot tables each month. This is error-prone, slow, and hard to reproduce. The function automates: one CSV in, one Excel link out, reproducible formulas, and automated retention metrics for review meetings.

When to use this pattern

  • Monthly product reviews where finance expects Excel files.
  • Recurring automated reports (automatic ingestion of Postgres exports into an S3 bucket, then a scheduled Functory call producing the workbook).
  • Quick ad-hoc analysis when a PM wants to compare cohorts across feature releases.

Step-by-step mini workflow

  1. Export events from Postgres: COPY (SELECT user_id,event_time,event_type,feature_name,plan_tier,revenue_usd FROM analytics.events WHERE event_time >= '2024-11-01') TO 'snapshot.csv' WITH CSV HEADER;
  2. Call the Python function (locally or via API) with snapshot.csv and retention window (e.g., 12 weeks).
  3. Function computes cohorts and writes retention_report_2025-03-01.xlsx.
  4. Function returns a URL (if deployed as an API) or local file path; share the URL with finance.
  5. Finance opens the Excel workbook, inspects cohort_pct and cohort_counts sheets, and uses the "summary_metrics" sheet for slides.

Algorithm (high-level)

  1. Read CSV; parse event_time to timezone-aware datetime.
  2. For each user_id, determine acquisition_week = floor_to_week(min(event_time)).
  3. For each event, compute week_index = weeks_between(acquisition_week, floor_to_week(event_time)).
  4. Group by acquisition_week and week_index, count distinct users => cohort counts.
  5. Pivot to table with acquisition_week rows and week_index columns; compute percentages relative to cohort size.

Python example: calculate cohorts and write Excel

import pandas as pd
from pathlib import Path

def generate_retention_excel(csv_path: str, output_path: str, retention_weeks: int = 12):
    df = pd.read_csv(csv_path, parse_dates=['event_time'])
    df['event_time'] = pd.to_datetime(df['event_time'], utc=True)
    # Week floor: Monday start
    df['event_week'] = df['event_time'].dt.to_period('W').apply(lambda p: p.start_time)

    # acquisition week per user
    first = df.groupby('user_id', as_index=False).event_time.min()
    first['acq_week'] = pd.to_datetime(first['event_time']).dt.to_period('W').apply(lambda p: p.start_time)

    df = df.merge(first[['user_id','acq_week']], on='user_id', how='left')
    df['week_index'] = ((df['event_week'] - df['acq_week']).dt.days // 7).astype(int)
    df = df[df['week_index'] >= 0]  # ignore pre-acquisition noise

    # cohort counts (distinct users per cohort/week)
    cohort_counts = (
        df.groupby(['acq_week','week_index'])['user_id']
          .nunique()
          .reset_index(name='users')
    )

    cohort_size = cohort_counts[cohort_counts['week_index']==0][['acq_week','users']].rename(columns={'users':'cohort_size'})
    pivot = cohort_counts.pivot(index='acq_week', columns='week_index', values='users').fillna(0).astype(int)
    pivot = pivot.reindex(columns=range(0, retention_weeks), fill_value=0)

    pct = pivot.div(pivot[0].replace({0:1}), axis=0).round(3)

    out = Path(output_path)
    with pd.ExcelWriter(out, engine='openpyxl') as writer:
        pivot.to_excel(writer, sheet_name='cohort_counts')
        pct.to_excel(writer, sheet_name='cohort_pct')

    return str(out)

# Example call
if __name__ == '__main__':
    out = generate_retention_excel('snapshot.csv', 'retention_report.xlsx', retention_weeks=12)
    print('Wrote:', out)

One concrete example: inputs and outputs

Input: snapshot.csv with 1,000 rows covering 16 weeks. After running with retention_weeks=12, output retention_report_2025-03-01.xlsx contains:

  • cohort_counts: 16 rows (one per acq_week) × 12 columns (Week 0..Week 11) with integer counts.
  • cohort_pct: same shape with decimals like 1.000, 0.420, 0.278.
  • summary_metrics: MAU (last 28 days), average cohort_size, average Week 4 retention and Week 12 retention.

How Functory Makes It Easy

To publish this as a Functory function, you wrap the above core logic in a single Python entrypoint main(...) that takes typed parameters. Example signature: main(csv_snapshot_path: str, retention_weeks: int = 12, output_filename: str = 'retention_report.xlsx') -> str. On Functory, that return value (a path) becomes a downloadable file URL in the UI and API.

Developer notes for Functory:

  • Choose an exact Python runtime like 3.11.11.
  • Declare a requirements.txt with pinned versions, e.g.:
    • pandas==1.5.3
openpyxl==3.1.2
  • Structure code so Functory calls main(...) directly — no CLI wrappers. main reads the uploaded CSV path, runs the cohort logic, writes an Excel file to a local path, and returns that path string.
  • Inputs are exposed automatically: csv_snapshot_path (FilePath), retention_weeks (int), output_filename (str). Files can be uploaded via the Functory web UI or passed as URLs/strings in API JSON.
  • Functory handles execution (CPU tier is fine for 10k–100k rows), autoscaling, print()-captured logs, and pay-per-use billing. You can chain this function with prior ETL functions (e.g., Postgres->CSV export function) and downstream reporting functions (e.g., publish to S3 or Slack) to build an end-to-end pipeline without servers.

Alternatives and why this function-based approach wins

Common alternatives: (1) Manual Excel pivot tables, (2) Ad-hoc Jupyter notebooks, (3) BI tools that require schema modeling, or (4) Scheduled SQL in the warehouse producing raw tables. Manual pivots are error-prone and not reproducible; notebooks are better for development but poor for secure, repeatable distribution to finance. BI tools add licensing friction and often don’t produce Excel workbooks investors demand. Turning this into a single small function that outputs a downloadable .xlsx gives reproducibility, simple access via one URL, and the ability to schedule or call programmatically from other services.

Business benefit

Quantified: Automating this reduces manual processing time by ~60% for a typical finance workflow (from 5 hours/week of manual work to 2 hours including validation) and speeds decision cycles — faster reports mean faster pricing or feature flag decisions.

Conclusion: You now have a clear pattern for turning messy Postgres CSV snapshots into Excel-ready retention cohort reports, automating a tedious manual step and providing a single, shareable URL for finance and product reviews. Next steps: parameterize by feature_name or plan_tier to produce focused reports, add scheduled calls (daily/weekly) from your scheduler or Functory workflows, or extend the workbook with cohort lifetime revenue columns to tie retention to cashflow.

According to a 2024 Gartner-style industry note, 72% of product and finance teams still rely on spreadsheet-based reviews — automating the pipeline described here helps bridge engineering outputs to finance consumption quickly.

Thanks for reading.