Skip to content

Reporting & Stats Generation

Overview

Reporting is how PaperRun turns raw attributed orders and recipient records into the performance metrics shown on dashboards. The pipeline has two layers:

  1. Pre-computed daily aggregates — a Celery task (process_order_aggregations) runs every 15 minutes, grouping raw attributions into daily buckets stored as JSONB in attribution_aggregates_daily. This avoids loading 100k+ raw orders on every dashboard request. Every tick re-aggregates the trailing ORDER_AGGREGATION_SLIDING_WINDOW_DAYS (default 2) so late-arriving attributions and rule re-evaluations get folded back into already-written buckets, including today's in-progress bucket; buckets that no longer have any contributing attributions are deleted in the same run.
  2. Stats generation — when a dashboard request arrives (or a cache refresh fires), the system reads those aggregates, joins them with recipient cost/send data, and assembles a StatsResponse object containing top-level stats, rolling time-series, and experiment results.

Results are cached in Redis with a 4-hour TTL and refreshed by a second Celery task (process_reporting_sync, every 7.5 minutes).

MCP consumption. The MCP server reads this same cache (get_cached_campaign_stats, key /api/v1/reporting/summary/{org}/campaigns/{campaign}) but does not serve the raw StatsResponse. get_full_campaign_stats / get_org_campaign_metrics return a lean, ComputedMetrics-first payload (one graded, basis-tagged number per concept; gross/chart blocks dropped). The on-demand SQL fallback's raw roas/revenue are gross and overstate experiment/promoted campaigns — see Computed Metrics Grading and app/mcp/schema_docs.py.

Flow

graph TD
    A[Celery Beat] -->|Every 15 min| B[process_order_aggregations]
    B --> C[For each enabled org]
    C --> D[determine_buckets_to_process]
    D -->|widen by ORDER_AGGREGATION_SLIDING_WINDOW_DAYS| E[compute_aggregates_for_buckets]
    E -->|SQL GROUP BY day| Eup[upsert_daily_aggregates]
    Eup -->|writes touched buckets| F[attribution_aggregates_daily]
    Eup --> Esw[delete_stale_org_aggregates]
    Esw -->|deletes buckets in window<br/>with last_updated &lt; run_started_at| F
    F --> G[(PostgreSQL)]

    H[Celery Beat] -->|Every 7.5 min| I[process_reporting_sync]
    I --> J[For each eligible org]
    J --> K[generate_order_stats_from_aggregates]
    K --> L[fetch_daily_aggregates]
    L --> G
    K --> M[fetch_reporting_recipient_aggregates]
    M --> N[(campaign_recipients)]
    K --> O[Build StatsResponse]
    O --> P[set_cached_stats_response]
    P --> Q[(Redis Cache)]

    R[API Request] -->|GET /reporting/summary/:org| S{Cache hit?}
    S -->|Yes| T[Return cached StatsResponse]
    S -->|No| K

    U[API Request] -->|GET /reporting/summary/:org/campaigns/:id| V{Cache hit?}
    V -->|Yes| W[Return cached response]
    V -->|No| X[generate_campaign_stats_from_aggregates]
    X --> K
    X --> Y[generate_holdout_exports_from_aggregates]
    Y --> Z[Build experiment_results]
    Z --> O

    classDef process fill:#e1f5ff;
    classDef success fill:#e1ffe1;
    classDef decision fill:#fff3e0;
    classDef store fill:#f3e5f5;

    class B,C,D,E,Eup,Esw,I,J,K,L,M,O,X,Y,Z process;
    class T,W success;
    class S,V decision;
    class G,N,Q store;

The StatsResponse Object

StatsResponse (core/models/reporting.py) is the central data structure returned by all reporting endpoints. It is a dataclass with these fields:

Field Type Description
stats dict Top-level campaign metrics (see below)
rolling_monthly_stats list[dict] 30-day rolling time-series rows
last_rolling_month_stats dict \| None Most recent 30-day rolling window
last_delta7_stats dict \| None Most recent 7-day rolling window
first_send_date str \| None ISO date of earliest recipient send
last_refreshed str ISO timestamp of when stats were computed
stats_text str \| None Human-readable summary (used in exports)
experiment_results list[dict] Holdout experiment analysis rows

Top-level stats dict keys

Key Description
mailers_sent Total postcards sent
campaign_orders Total attributed orders
campaign_revenue Total attributed revenue
total_cost Total mailing cost
roas Return on ad spend (revenue / cost)
conversion_rate Orders / mailers sent
cpa Cost per acquisition (cost / orders)
aov Average order value
first_purchase_orders Orders where order_count = 1
first_purchase_revenue Revenue from first purchases
first_purchase_roas First purchase revenue / cost
repeat_orders Orders where order_count > 1
repeat_revenue Revenue from repeat orders
repeat_rate Repeat orders / first purchase orders
subscription_orders Orders flagged as subscriptions
subscription_revenue Revenue from subscription orders
subscribers New subscribers (subscription + first order)
discount_matched_orders Orders matched via discount code
customer_conversion_rate First purchase orders / mailers sent

Helper methods on StatsResponse

  • get_effective_roas(is_experiment) — returns incremental ROAS for experiments, first_purchase_roas otherwise
  • get_effective_cac(is_experiment) — returns incremental CAC for experiments, CPA otherwise
  • is_low_performing(is_experiment) — True if effective ROAS < 1.0 and CAC > 100
  • is_high_performing(is_experiment) — True if effective ROAS > 3.0 and CAC < 40
  • get_experiment_result(metric, stat, group) — lookup a specific experiment result row

Rolling Stats

Rolling stats provide a time-series view of campaign performance. They are generated by generate_rolling_stats_from_daily_aggregates() which:

  1. Takes daily order aggregates (one row per (campaign, day, holdout))
  2. Merges with daily recipient cost/send data
  3. Computes per-day metrics (roas, cpa, conversion_rate, profit)
  4. Computes cumulative metrics (cumulative_roas, cumulative_revenue, etc.)
  5. Resamples into rolling windows (default 30 days, also computed for 7 days)
  6. Adds derived fields (ltv, first_purchase_roas)

The last_rolling_month_stats and last_delta7_stats fields on StatsResponse contain the most recent rolling window, used by the frontend for delta/trend displays.

Experiment Results (Holdout Analysis)

When a campaign has a holdout group enabled, experiment_results contains rows comparing the treatment group (recipients who received mail) against the control group (holdout recipients who did not).

Analysis is computed for two metrics: - first_order — only first purchases (order_count = 1) - all_orders — all attributed orders

For each metric, the following stats are produced:

Stat Description
win_probability Bayesian probability that treatment outperforms control
incremental_revenue Revenue lift attributable to mailing
incremental_roas Incremental revenue / total cost
incremental_customer_acquisition_cost Cost per incremental customer (first_order only)
metric_uplift (test conv rate - control conv rate) / control conv rate
recipients Count for both test and control groups
orders Count for both test and control groups
revenue Total for both test and control groups
conversion_rate Rate for both test and control groups
cost Total mailing cost (test group only)
revenue_per_recipient Revenue / recipients for both groups

The statistical test uses a Z-test on conversion rates to determine win_probability and statistical significance.

Two Generation Paths

There are two parallel implementations for generating stats:

Path Module Data source Used by
Aggregate methods/reporting_aggregates.py attribution_aggregates_daily Production routes, cache refresh
Raw methods/reporting.py Raw attributions table Exports, legacy fallback

The aggregate path is 10-100x faster and is the default for all dashboard requests. The raw path exists for CSV exports that need per-order detail and as a legacy fallback.

Background Tasks

Task Schedule Queue Description
process_order_aggregations Every 15 min default Incrementally aggregates raw attributions into daily buckets (refreshes today's bucket every tick)
process_reporting_sync Every 7.5 min slow Refreshes Redis-cached StatsResponse for all orgs and campaigns

Caching

  • Redis key pattern: /api/v1/reporting/summary/{org_id} for org stats, /api/v1/reporting/summary/{org_id}/campaigns/{campaign_id} for campaign stats
  • TTL: 4 hours (set by reporting_sync), 20 minutes (set by route handlers on cache miss)
  • Cache reset: POST /v1/reporting/cache/reset clears all reporting keys for an org
  • Stats are serialized/deserialized via set_cached_stats_response / get_cached_stats_response

Key Files

  • methods/reporting_aggregates.py — aggregate-based stats generation (production path)
  • methods/reporting.py — raw stats generation, rolling stats, OpenAI analysis, enrichment extraction
  • methods/holdouts.py — experiment group creation, holdout analysis, A/B test significance
  • core/models/reporting.py — StatsResponse dataclass, Redis cache helpers
  • core/models/order_aggregates.py — daily aggregate model, SQL queries, upsert operations
  • core/models/reporting_recipients.py — recipient aggregate queries for cost/send data
  • celery/attribution_aggregates.py — background task for incremental aggregation
  • celery/reporting_sync.py — background task for cache refresh
  • routes/reporting_routes.py — API endpoints serving stats to the frontend