Data Analysis

Data Analysis

Overview

A comprehensive data analysis and storytelling skill optimized for financial, SaaS, and RevOps contexts. This skill provides structured workflows for turning raw data into actionable insights with full transparency on analytical decisions, bias awareness, and progressive disclosure reporting.

Workflow Overview

Every analysis follows a 7-phase process:

1. SETUP    → Initialize Marimo notebook (run init_marimo_notebook.py)
2. INGEST   → Load data, document sources and assumptions
3. EXPLORE  → EDA with logged decisions (why this viz, why this filter)
4. MODEL    → If needed, with interpretable-first approach
5. INTERPRET → Apply bias checklist, hedge appropriately
6. WISHLIST → Document data gaps and proxies used
7. OUTPUT   → Generate appropriate tier (slides/report/notebook)

Decision Logging Protocol

Every analytical choice must be logged. This creates an audit trail and enables reproducibility.

What to Log

Log Format in Notebook

# === DECISION LOG ===
# FILTER: Excluded trial accounts - 1,247 records removed
# METRIC: NRR over GRR because expansion is significant factor
# ASSUMPTION: Q4 seasonality similar to prior year - confidence: M
# PROXY: Support ticket sentiment for NPS - quality: Weak

Analysis Workflow Details

Phase 1: Setup

Run the initialization script to create a new Marimo notebook with pre-built scaffolding:

python scripts/init_marimo_notebook.py <notebook_name>

This creates a .py file with:

• Decision log cell (markdown)
• Data loading template
• EDA template cells
• Bias checklist cell

Phase 2: Data Ingestion

When loading data: 1. Document the source (file path, API, database query) 2. Record row/column counts 3. Note any immediate data quality issues 4. Log assumptions about data freshness

# === DATA SOURCE ===
# Source: sales_data_2024.csv
# Loaded: 2024-01-15
# Records: 15,847 rows x 23 columns
# Note: Data through 2024-01-10, 5-day lag from source system

Phase 3: Exploratory Data Analysis

Follow this EDA checklist:

• [ ] Distribution of key numeric variables
• [ ] Missing value patterns
• [ ] Outlier detection
• [ ] Time series patterns (if applicable)
• [ ] Segment breakdowns
• [ ] Correlation exploration

Log every visualization choice and filtering decision.

Phase 4: Modeling (If Needed)

Prioritize interpretability: 1. First choice: Descriptive statistics, cohort analysis 2. Second choice: Linear regression, decision trees 3. Last resort: Complex ML (document why simpler won't work)

Always provide:

• Model assumptions
• Feature importance / coefficients
• Confidence intervals
• What the model cannot tell us

Phase 5: Interpretation

Before finalizing insights, run the bias checklist. See references/biases.md for full checklist.

Quick check:

• [ ] Survivorship bias: Am I only looking at "survivors"?
• [ ] Simpson's paradox: Do segment trends differ from aggregate?
• [ ] Selection bias: Is my sample representative?
• [ ] Small-n warning: Is sample size sufficient for claims?

Hedge appropriately:

• Use "suggests" not "proves"
• State confidence levels
• Note what additional data would strengthen conclusions

Phase 5.5: Validation Gate (MANDATORY)

⚠️ GATE: Before proceeding to output, you MUST run the data quality validation checklist.

This is not optional. Run through references/data-quality-validator.md before finalizing:

Critical Patterns Checklist:

• [ ] Market Context: Are YoY changes compared to market/baseline?
• [ ] Weighting Sensitivity: If using composite scores, tested 5-6 weight scenarios?
• [ ] Bootstrap CIs: For small samples, generated P10/P50/P90 ranges?
• [ ] Survivorship Quantified: Data_Availability column added? Coverage % reported?

Statistical Checks:

• [ ] Sample sizes disclosed with confidence intervals?
• [ ] Multiple comparisons accounted for?
• [ ] Cherry-picked baselines avoided?

Logic Checks:

• [ ] Correlation not claimed as causation?
• [ ] Alternative explanations considered?
• [ ] Back-of-envelope validation passed?

Methodology Note on Time Horizons: When assessing skill vs luck (e.g., sales rep performance, investment returns):

• Longer time horizons (3+ years) reveal inconsistency that short windows hide
• More periods = higher bar for "likely skill"
• A rep who is "top 10%" for 2 quarters could easily be luck
• A rep who is "top 10%" for 12 quarters is more likely skill
• Always state the number of periods analyzed and what that implies for confidence

Do not proceed to Phase 6/7 until this checklist is complete.

Phase 6: Data Wishlisting

Document gaps and proxies. See references/data-wishlisting.md for patterns.

Format:

## Data Wishlist

| Missing Data | Proxy Used | Quality | Impact on Analysis |
|--------------|------------|---------|-------------------|
| Customer NPS | Support sentiment | Weak | Core finding, needs validation |
| True LTV | 12-month value | Moderate | Acceptable for segmentation |

Phase 7: Output Generation

Choose output tier based on audience and purpose:

---

Data Cleaning Workflow

For messy data that needs cleaning before analysis:

1. Profile the Data

python scripts/profile_data.py <csv_file> --output data_quality_report.md

This generates:

• Column-level statistics (nulls, uniques, types)
• Data quality score (A-F grading)
• Suspicious pattern detection
• Suggested cleaning steps

2. Apply Cleaning Patterns

Reference references/data-cleaning.md for:

• Missing value strategies (drop, impute, flag)
• Outlier detection methods (IQR, z-score, domain rules)
• Common transforms (pivot, melt, merge patterns)
• Type coercion recipes
• Deduplication patterns

3. Handle Datetime Issues

Reference references/datetime-handling.md for:

• Timezone conversion patterns
• Date parsing for mixed formats
• Fiscal calendar handling (FY vs CY)
• Period aggregation (daily → weekly → monthly)
• Business day calculations

---

Dashboard Building Workflow

For interactive monitoring dashboards:

1. Initialize Dashboard

python scripts/init_dashboard.py <dashboard_name>

This creates a Marimo dashboard with:

• KPI cards row
• Filter sidebar (segment, date range, period)
• Time series trend chart
• Summary data table
• Responsive layout

2. Apply Dashboard Patterns

Reference references/dashboard-patterns.md for:

• Marimo layout patterns (sidebar, tabs, grid)
• KPI card templates with sparklines
• Filter/slider patterns for interactivity
• Data table styling and formatting
• Time series with range selection
• Refresh patterns for live data

---

Data Quality Validation Workflow

Before presenting or accepting analytical claims:

Run the Data Quality Validation Checklist

Reference references/data-quality-validator.md for comprehensive checklists:

Statistical Sins:

• P-hacking / multiple comparisons
• Small sample extrapolation
• Missing confidence intervals
• Cherry-picked baselines

Chart Crimes:

• Truncated y-axis
• Dual y-axis manipulation
• 3D charts
• Misleading scales

Logic Fallacies:

• Correlation ≠ causation
• Ecological fallacy
• Base rate neglect
• Survivorship bias

Sanity Checks:

• Does this pass the smell test?
• Back-of-envelope validation
• Historical comparison
• Cross-source validation

---

Excel Output Workflow

For exporting analysis results to Excel with proper formulas and formatting:

Financial Model Standards

Reference references/xlsx-patterns.md for:

• Color coding convention (blue=inputs, black=formulas, green=cross-sheet links)
• Number formatting standards (currency, percentages, multiples)
• Formula construction rules (use formulas, not hardcoded values)
• Common formula patterns for analysis

Verification

After creating Excel files with formulas, always recalculate:

python scripts/recalc.py output.xlsx

This ensures:

• All formulas are calculated (openpyxl doesn't evaluate formulas)
• Zero formula errors (#REF!, #DIV/0!, etc.)
• JSON output shows any errors to fix

---

PDF Handling Workflow

For extracting data from PDFs or creating PDF reports:

Extracting Data

Reference references/pdf-patterns.md for:

• Text extraction (pypdf, pdfplumber)
• Table extraction to DataFrame
• OCR for scanned documents
• Command-line tools (pdftotext, qpdf)

Creating Reports

Reference references/pdf-patterns.md for:

• Basic report creation with reportlab
• Professional reports with sections and tables
• Embedding matplotlib charts in PDFs
• Merge/split operations

---

Reference Files

Load these as needed during analysis:

Scripts

Technology Stack

Example Invocations

Revenue analysis:

"Analyze our ARR trends by segment and identify drivers of growth/churn"

Pipeline analytics:

"Build a win rate analysis by deal size and sales rep"

Cohort analysis:

"Create a retention cohort analysis for customers acquired in 2023"

Forecasting:

"Project next quarter revenue based on current pipeline"

Board deck:

"Create an executive summary deck of our key SaaS metrics"

Data cleaning:

"Clean this messy CSV and profile the data quality"

Dashboard:

"Build a dashboard to monitor our key SaaS metrics"

Data validation:

"Validate these findings before I present them"

Excel output:

"Export this analysis to Excel with proper formulas and formatting"

PDF extraction:

"Extract the tables from this quarterly report PDF"

Financial model:

"Create a revenue projection model in Excel with scenario inputs"

Data Analysis Skill

Use this skill for data analysis, visualization, and storytelling in financial and RevOps contexts.

When to Use

• Analyzing revenue data, building forecasts, cohort analysis
• Churn modeling, pipeline analytics
• Creating data-driven reports, building dashboards
• Cleaning messy data, sanity-checking analytical claims
• Exporting to Excel with formulas, extracting data from PDFs

Instructions

1. Read ${CLAUDE_PLUGIN_ROOT}/SKILL.md for the complete workflow guide 2. Follow the 7-phase analysis process:

• SETUP: Initialize Marimo notebook
• INGEST: Load data, document sources
• EXPLORE: EDA with logged decisions
• MODEL: If needed, interpretable-first
• INTERPRET: Apply bias checklist
• WISHLIST: Document data gaps
• OUTPUT: Generate appropriate tier

Key Reference Files

Load these as needed during analysis:

Scripts

Decision Logging

Every analytical choice must be logged:

# === DECISION LOG ===
# FILTER: Excluded trial accounts - 1,247 records removed
# METRIC: NRR over GRR because expansion is significant factor
# ASSUMPTION: Q4 seasonality similar to prior year - confidence: M
# PROXY: Support ticket sentiment for NPS - quality: Weak

Data Analysis - Data Analysis Plugin for Claude Code

A comprehensive data analysis and storytelling skill optimized for financial, SaaS, and RevOps contexts. This plugin provides structured workflows for turning raw data into actionable insights with full transparency on analytical decisions, bias awareness, and progressive disclosure reporting.

Features

• Decision Logging: Track every analytical choice for audit trails and reproducibility
• Bias-Aware Interpretation: Built-in checklists for survivorship bias, Simpson's paradox, and more
• Progressive Disclosure: Slide deck -> detailed report -> full notebook with all decisions documented
• Financial Standards: Excel output with proper formulas, color coding, and zero formula errors
• Dashboard Building: Marimo-based interactive dashboards with KPI cards and filters

Installation

Option 1: Marketplace (Recommended)

# 1. Add the Casper Studios marketplace
/plugin marketplace add Casper-Studios/plugin-marketplace

# 2. Install the plugin
/plugin install data-analysis

Option 2: Git Clone + Local Plugin Directory

# Clone the repository
git clone git@github.com:Casper-Studios/plugin-marketplace.git

# Run Claude Code with the plugin directory
claude --plugin-dir ./plugin-marketplace

Commands

/data-analysis:analyze

Run the full data analysis workflow with decision logging and bias checking.

Workflow Overview

Every analysis follows a 7-phase process:

1. SETUP    → Initialize Marimo notebook (run init_marimo_notebook.py)
2. INGEST   → Load data, document sources and assumptions
3. EXPLORE  → EDA with logged decisions (why this viz, why this filter)
4. MODEL    → If needed, with interpretable-first approach
5. INTERPRET → Apply bias checklist, hedge appropriately
6. WISHLIST → Document data gaps and proxies used
7. OUTPUT   → Generate appropriate tier (slides/report/notebook)

Reference Files

Scripts

Technology Stack

Example Invocations

"Analyze our ARR trends by segment and identify drivers of growth/churn"
"Build a win rate analysis by deal size and sales rep"
"Create a retention cohort analysis for customers acquired in 2023"
"Project next quarter revenue based on current pipeline"
"Create an executive summary deck of our key SaaS metrics"
"Clean this messy CSV and profile the data quality"
"Build a dashboard to monitor our key SaaS metrics"
"Sanity check these findings before I present them"
"Export this analysis to Excel with proper formulas and formatting"
"Extract the tables from this quarterly report PDF"

Directory Structure

data-analysis/
├── .claude-plugin/
│   ├── plugin.json              # Plugin manifest
│   └── marketplace.json         # Marketplace metadata
├── commands/
│   └── analyze.md               # Main analysis command
├── scripts/
│   ├── init_marimo_notebook.py  # Notebook scaffolding
│   ├── profile_data.py          # Data quality profiling
│   ├── init_dashboard.py        # Dashboard scaffolding
│   ├── generate_pptx_summary.py # PowerPoint generation
│   └── recalc.py                # Excel formula recalc
├── references/
│   ├── metrics.md               # SaaS metrics definitions
│   ├── biases.md                # Analytical bias checklist
│   ├── data-quality-validator.md # Data quality validation checks
│   ├── data-cleaning.md         # Cleaning patterns
│   ├── datetime-handling.md     # Date/time patterns
│   ├── dashboard-patterns.md    # Marimo dashboard patterns
│   ├── visualization-guide.md   # Chart selection guide
│   ├── report-templates.md      # Report structures
│   ├── data-wishlisting.md      # Data gap documentation
│   ├── xlsx-patterns.md         # Excel output patterns
│   └── pdf-patterns.md          # PDF handling patterns
├── SKILL.md                     # Main skill documentation
└── README.md                    # This file

License

MIT

Analytical Bias Checklist

Use this checklist before finalizing any analysis. Each bias includes finance/RevOps examples and mitigation strategies.

---

Pre-Interpretation Checklist

Run through this checklist before drawing conclusions:

• [ ] Survivorship Bias - Am I only looking at "survivors"?
• [ ] Simpson's Paradox - Do segment trends differ from aggregate?
• [ ] Selection Bias - Is my sample representative?
• [ ] Collider Bias - Am I conditioning on an outcome?
• [ ] Confounding - Are there omitted variables?
• [ ] Small-n Warning - Is sample size sufficient?
• [ ] Recency Bias - Am I overweighting recent data?
• [ ] Confirmation Bias - Am I seeking data to confirm beliefs?

---

Survivorship Bias

Definition: Drawing conclusions only from entities that "survived" a selection process, ignoring those that didn't.

Finance/RevOps Examples

Red Flags

• "Our customers say..." (only current customers)
• "Our best customers do X" (survivorship in sample)
• "Successful companies have Y" (failed companies ignored)

Mitigation

1. Include churned customers in retention analysis 2. Analyze lost deals alongside won deals 3. Track cohorts from start - don't backfill 4. Ask: "What am I not seeing because it no longer exists?"

Logging Format

BIAS CHECK - Survivorship: [PASS/FLAG]
- Churned customers: [included/excluded]
- Action: [mitigation taken]

---

Simpson's Paradox

Definition: A trend in aggregated data reverses or disappears when data is separated into groups.

Finance/RevOps Examples

Classic case: Overall conversion rate is up, but conversion is down in every segment.

Revenue example:

• Overall ARPU increased
• ARPU decreased in both SMB and Enterprise
• Mix shifted toward Enterprise (higher ARPU base)

Red Flags

• Aggregate trend seems strong
• No segment breakdown provided
• Mix/composition changed over time

Mitigation

1. Always segment key metrics before concluding 2. Control for mix shifts in trend analysis 3. Report both aggregate and segment views 4. Weight by segment when appropriate

Logging Format

BIAS CHECK - Simpson's: [PASS/FLAG]
- Segments checked: [list]
- Mix shift: [yes/no - details]

---

Selection Bias

Definition: Sample is not representative of the population you're trying to understand.

Finance/RevOps Examples

Red Flags

• Opt-in surveys or programs
• "Hand-picked" samples
• Non-random assignment
• Response rates < 30%

Mitigation

1. Document selection criteria explicitly 2. Compare sample to population on key dimensions 3. Use stratified sampling when possible 4. Adjust for non-response if data available 5. Acknowledge limitations in findings

Logging Format

BIAS CHECK - Selection: [PASS/FLAG]
- Selection method: [random/convenience/other]
- Sample vs population: [comparison]
- Representativeness: [strong/moderate/weak]

---

Collider Bias

Definition: Conditioning on a variable that is caused by both the treatment and outcome, creating spurious associations.

Finance/RevOps Examples

Classic case: Analyzing only closed deals to understand what drives winning.

           Marketing Spend
                  ↓
            [Deal Size] ← Sales Effort
                  ↓
              Deal Won (collider)

If you only analyze won deals, you may find marketing spend negatively correlates with deal size - but this is spurious.

Other examples:

• Analyzing only retained customers for churn factors
• Studying only hired candidates for interview performance
• Looking at only successful products for launch factors

Red Flags

• Filtering data to an outcome (won deals, retained customers)
• "Among successful X, we find..."
• Post-hoc analysis of survivors

Mitigation

1. Include all observations - won AND lost, retained AND churned 2. Be explicit about conditioning variables 3. Use causal diagrams to identify colliders 4. Run sensitivity analysis with different filters

Logging Format

BIAS CHECK - Collider: [PASS/FLAG]
- Conditioning on: [variable]
- Risk assessment: [describe]

---

Confounding (Omitted Variable Bias)

Definition: A third variable influences both the predictor and outcome, creating a spurious correlation.

Finance/RevOps Examples

Red Flags

• Strong correlation without plausible mechanism
• "Users who do X have higher Y" (no randomization)
• No control variables in analysis

Mitigation

1. List potential confounders before analysis 2. Control for confounders in regression 3. Use quasi-experimental methods when possible (diff-in-diff, matching) 4. Acknowledge when confounding can't be ruled out 5. Say "associated with" not "causes"

Logging Format

BIAS CHECK - Confounding: [PASS/FLAG]
- Potential confounders: [list]
- Controls used: [list]
- Causal claim appropriate: [yes/no]

---

Small-n Warning

Definition: Drawing strong conclusions from insufficient sample sizes.

Minimum Sample Guidance

Finance/RevOps Red Flags

• "Enterprise customers prefer X" (n=12)
• "Q4 trend shows Y" (4 data points)
• "New feature increased Z" (tested on 50 users)

When to Flag

# Quick significance check
import scipy.stats as stats

# For proportions
n1, n2 = sample_sizes
if n1 < 100 or n2 < 100:
    print("SMALL-N WARNING: Results may be unstable")

# For segment analysis
if any(segment_counts < 30):
    print("SMALL-N WARNING: Some segments too small")

Mitigation

1. Report confidence intervals - wide CIs = small n 2. Pool small segments when appropriate 3. Use Bayesian methods for small samples 4. Clearly label preliminary/directional findings 5. Wait for more data before major decisions

Logging Format

BIAS CHECK - Small-n: [PASS/FLAG]
- Sample size: [n]
- Minimum recommended: [threshold]
- Action: [proceed/flag/wait for data]

---

Recency Bias

Definition: Overweighting recent observations relative to historical patterns.

Finance/RevOps Examples

• Last quarter's churn spike drives panic (but it's seasonal)
• Recent win streak leads to over-hiring
• Latest customer feedback dominates roadmap

Mitigation

1. Show historical context - at least 2-3 years 2. Adjust for seasonality explicitly 3. Use rolling averages to smooth noise 4. Compare to same period last year (YoY)

Logging Format

BIAS CHECK - Recency: [PASS/FLAG]
- Time period analyzed: [range]
- Historical comparison: [included/missing]
- Seasonality adjustment: [yes/no]

---

Confirmation Bias

Definition: Seeking or interpreting data to confirm pre-existing beliefs.

Warning Signs

• Analysis started with a conclusion
• Only supportive evidence presented
• Contradictory findings dismissed or unexplored
• "We knew X, and data confirms"

Mitigation

1. Pre-register hypotheses before analysis 2. Actively seek disconfirming evidence 3. Present contradictory findings prominently 4. Have someone else review the analysis 5. Document the question before starting

Logging Format

BIAS CHECK - Confirmation: [PASS/FLAG]
- Hypothesis stated upfront: [yes/no]
- Contradictory evidence explored: [yes/no]
- Alternative explanations: [list]

---

Summary Checklist Template

Copy this to your analysis notebook:

## Bias Checklist

| Bias | Status | Notes |
|------|--------|-------|
| Survivorship | [ ] Pass / [ ] Flag | |
| Simpson's Paradox | [ ] Pass / [ ] Flag | |
| Selection | [ ] Pass / [ ] Flag | |
| Collider | [ ] Pass / [ ] Flag | |
| Confounding | [ ] Pass / [ ] Flag | |
| Small-n | [ ] Pass / [ ] Flag | |
| Recency | [ ] Pass / [ ] Flag | |
| Confirmation | [ ] Pass / [ ] Flag | |

**Overall assessment:** [Ready to present / Needs caveats / Needs more work]

**Key caveats to include in findings:**
1.
2.
3.

Dashboard Patterns Reference

Marimo layout patterns, KPI cards, interactivity, and dashboard design for financial and RevOps dashboards.

---

Dashboard Design Principles

Information Hierarchy

1. TOP: Key metrics (KPIs) - What matters most?
2. MIDDLE: Trends and comparisons - How are we doing?
3. BOTTOM: Details and drill-downs - Why?

Dashboard Types

---

Marimo Layout Patterns

Sidebar Layout

import marimo as mo

@app.cell
def _(mo):
    # Create sidebar with filters
    segment_filter = mo.ui.dropdown(
        options=['All', 'Enterprise', 'Mid-Market', 'SMB'],
        value='All',
        label='Segment'
    )

    date_range = mo.ui.date_range(
        start=datetime(2024, 1, 1),
        stop=datetime.now(),
        label='Date Range'
    )

    sidebar = mo.vstack([
        mo.md("## Filters"),
        segment_filter,
        date_range,
        mo.md("---"),
        mo.md("Last updated: " + datetime.now().strftime("%Y-%m-%d %H:%M"))
    ])

    return segment_filter, date_range, sidebar


@app.cell
def _(mo, sidebar, main_content):
    # Layout with sidebar
    mo.hstack([
        mo.vstack([sidebar], align='start'),
        mo.vstack([main_content], grow=True)
    ], widths=[1, 4])
    return

Tab Layout

@app.cell
def _(mo, overview_content, details_content, trends_content):
    # Tabbed navigation
    tabs = mo.ui.tabs({
        "Overview": overview_content,
        "Details": details_content,
        "Trends": trends_content
    })

    mo.vstack([
        mo.md("# Revenue Dashboard"),
        tabs
    ])
    return (tabs,)

Grid Layout

@app.cell
def _(mo, kpi_cards, main_chart, table):
    # KPI row at top
    kpi_row = mo.hstack(kpi_cards, justify='space-around')

    # Main content below
    main_row = mo.hstack([
        main_chart,
        table
    ], widths=[2, 1])

    mo.vstack([
        kpi_row,
        mo.md("---"),
        main_row
    ])
    return

---

KPI Card Templates

Basic KPI Card

def kpi_card(title, value, subtitle=None, delta=None, delta_color=None):
    """
    Create a KPI card with optional delta indicator.

    Args:
        title: KPI name
        value: Current value (formatted string)
        subtitle: Optional context
        delta: Optional change indicator (e.g., "+5%")
        delta_color: 'green', 'red', or None
    """
    delta_html = ""
    if delta:
        color = delta_color or ('green' if delta.startswith('+') else 'red')
        delta_html = f'<span style="color: {color}; font-size: 0.9em;">{delta}</span>'

    subtitle_html = f'<div style="color: #666; font-size: 0.8em;">{subtitle}</div>' if subtitle else ""

    return mo.md(f"""
    <div style="
        background: #f8f9fa;
        padding: 20px;
        border-radius: 8px;
        text-align: center;
        min-width: 150px;
    ">
        <div style="color: #666; font-size: 0.9em; margin-bottom: 5px;">{title}</div>
        <div style="font-size: 2em; font-weight: bold; color: #333;">{value}</div>
        {delta_html}
        {subtitle_html}
    </div>
    """)

KPI with Sparkline

import matplotlib.pyplot as plt
import io
import base64

def kpi_card_with_sparkline(title, value, trend_data, delta=None):
    """
    KPI card with embedded sparkline.

    Args:
        title: KPI name
        value: Current value
        trend_data: List of values for sparkline
        delta: Change indicator
    """
    # Create sparkline
    fig, ax = plt.subplots(figsize=(3, 1))
    ax.plot(trend_data, color='#2E86AB', linewidth=2)
    ax.fill_between(range(len(trend_data)), trend_data, alpha=0.2, color='#2E86AB')
    ax.axis('off')
    plt.tight_layout()

    # Convert to base64
    buf = io.BytesIO()
    fig.savefig(buf, format='png', dpi=100, transparent=True)
    buf.seek(0)
    img_base64 = base64.b64encode(buf.read()).decode()
    plt.close()

    delta_html = f'<span style="color: green;">{delta}</span>' if delta else ""

    return mo.md(f"""
    <div style="
        background: #f8f9fa;
        padding: 15px;
        border-radius: 8px;
        text-align: center;
    ">
        <div style="color: #666; font-size: 0.9em;">{title}</div>
        <div style="font-size: 1.8em; font-weight: bold;">{value} {delta_html}</div>
        <img src="data:image/png;base64,{img_base64}" style="width: 100%; height: 40px;">
    </div>
    """)

KPI Row Builder

def build_kpi_row(metrics):
    """
    Build a row of KPI cards.

    Args:
        metrics: List of dicts with keys: title, value, delta (optional)

    Returns:
        Marimo hstack of KPI cards
    """
    cards = [
        kpi_card(
            title=m['title'],
            value=m['value'],
            delta=m.get('delta'),
            delta_color=m.get('delta_color')
        )
        for m in metrics
    ]

    return mo.hstack(cards, justify='space-around')


# Usage example
kpi_metrics = [
    {'title': 'MRR', 'value': '$1.2M', 'delta': '+8%', 'delta_color': 'green'},
    {'title': 'Customers', 'value': '1,247', 'delta': '+12'},
    {'title': 'NRR', 'value': '112%', 'delta': '+3pp'},
    {'title': 'Churn', 'value': '2.1%', 'delta': '-0.3pp', 'delta_color': 'green'},
]

kpi_row = build_kpi_row(kpi_metrics)

---

Filter and Interactivity Patterns

Dropdown Filter

@app.cell
def _(mo, df):
    # Single select dropdown
    segment_filter = mo.ui.dropdown(
        options=['All'] + df['segment'].unique().tolist(),
        value='All',
        label='Segment'
    )
    return (segment_filter,)


@app.cell
def _(df, segment_filter):
    # Apply filter
    if segment_filter.value == 'All':
        df_filtered = df
    else:
        df_filtered = df[df['segment'] == segment_filter.value]

    return (df_filtered,)

Multi-Select Filter

@app.cell
def _(mo, df):
    # Multi-select for segments
    segment_multiselect = mo.ui.multiselect(
        options=df['segment'].unique().tolist(),
        value=df['segment'].unique().tolist(),  # All selected by default
        label='Segments'
    )
    return (segment_multiselect,)


@app.cell
def _(df, segment_multiselect):
    # Apply multi-select filter
    df_filtered = df[df['segment'].isin(segment_multiselect.value)]
    return (df_filtered,)

Date Range Picker

@app.cell
def _(mo, df):
    # Date range picker
    date_range = mo.ui.date_range(
        start=df['date'].min(),
        stop=df['date'].max(),
        label='Date Range'
    )
    return (date_range,)


@app.cell
def _(df, date_range):
    # Apply date filter
    start_date, end_date = date_range.value
    df_filtered = df[
        (df['date'] >= pd.Timestamp(start_date)) &
        (df['date'] <= pd.Timestamp(end_date))
    ]
    return (df_filtered,)

Slider Filter

@app.cell
def _(mo, df):
    # Numeric slider
    min_revenue = mo.ui.slider(
        start=0,
        stop=int(df['revenue'].max()),
        step=1000,
        value=0,
        label='Min Revenue',
        show_value=True
    )
    return (min_revenue,)


@app.cell
def _(df, min_revenue):
    df_filtered = df[df['revenue'] >= min_revenue.value]
    return (df_filtered,)

Checkbox Toggles

@app.cell
def _(mo):
    # Toggle options
    show_trend = mo.ui.checkbox(label='Show trend line', value=True)
    exclude_outliers = mo.ui.checkbox(label='Exclude outliers', value=False)

    filter_options = mo.vstack([show_trend, exclude_outliers])
    return show_trend, exclude_outliers, filter_options

---

Data Table Patterns

Basic Sortable Table

@app.cell
def _(mo, df_filtered):
    # Interactive data table
    table = mo.ui.table(
        data=df_filtered,
        selection='single',  # or 'multi' for multiple selection
        pagination=True,
        page_size=20
    )
    return (table,)

Styled Table with Formatting

def format_table(df):
    """Apply formatting to DataFrame for display."""
    styled = df.style

    # Format currency columns
    currency_cols = ['revenue', 'mrr', 'arr']
    for col in currency_cols:
        if col in df.columns:
            styled = styled.format({col: '${:,.0f}'})

    # Format percentage columns
    pct_cols = ['churn_rate', 'growth_rate', 'conversion']
    for col in pct_cols:
        if col in df.columns:
            styled = styled.format({col: '{:.1%}'})

    # Highlight conditions
    if 'churn_rate' in df.columns:
        styled = styled.applymap(
            lambda x: 'color: red' if x > 0.05 else '',
            subset=['churn_rate']
        )

    return styled


@app.cell
def _(mo, df_filtered):
    styled_df = format_table(df_filtered)
    mo.ui.table(styled_df.data, pagination=True)
    return

Summary Table

def create_summary_table(df, group_by, metrics):
    """
    Create a summary table with aggregated metrics.

    Args:
        df: DataFrame
        group_by: Column to group by
        metrics: Dict of {column: aggregation}

    Returns:
        Summary DataFrame
    """
    summary = df.groupby(group_by).agg(metrics).round(2)
    summary = summary.sort_values(
        summary.columns[0],
        ascending=False
    )

    return summary


# Example usage
summary = create_summary_table(
    df=df_filtered,
    group_by='segment',
    metrics={
        'revenue': ['sum', 'mean'],
        'customers': 'nunique',
        'churn_rate': 'mean'
    }
)

---

Time Series Patterns

Time Series with Range Selection

@app.cell
def _(mo, df):
    # Period selector
    period = mo.ui.dropdown(
        options=['Daily', 'Weekly', 'Monthly', 'Quarterly'],
        value='Monthly',
        label='Aggregation'
    )
    return (period,)


@app.cell
def _(df, period, plt):
    # Aggregate based on period
    period_map = {
        'Daily': 'D',
        'Weekly': 'W',
        'Monthly': 'M',
        'Quarterly': 'Q'
    }

    df_agg = df.set_index('date').resample(period_map[period.value]).agg({
        'revenue': 'sum',
        'customers': 'nunique'
    }).reset_index()

    # Create chart
    fig, ax = plt.subplots(figsize=(12, 5))
    ax.plot(df_agg['date'], df_agg['revenue'], marker='o', linewidth=2)
    ax.set_title(f'Revenue Trend ({period.value})')
    ax.set_xlabel('')
    ax.set_ylabel('Revenue ($)')
    plt.xticks(rotation=45)
    plt.tight_layout()

    return (fig,)

Comparison Chart (vs Prior Period)

def create_comparison_chart(df, date_col, value_col, prior_periods=12):
    """Create chart comparing current to prior period."""
    fig, ax = plt.subplots(figsize=(12, 5))

    # Current period
    ax.plot(df[date_col], df[value_col], label='Current', linewidth=2, color='#2E86AB')

    # Prior period (shifted)
    df_prior = df.copy()
    df_prior[date_col] = df_prior[date_col] + pd.DateOffset(months=prior_periods)
    ax.plot(df_prior[date_col], df_prior[value_col], label='Prior Year',
            linewidth=2, color='#A23B72', linestyle='--', alpha=0.7)

    ax.legend()
    ax.set_title('Year-over-Year Comparison')
    plt.tight_layout()

    return fig

---

Refresh Patterns

Manual Refresh Button

@app.cell
def _(mo):
    refresh_button = mo.ui.button(label="Refresh Data")
    return (refresh_button,)


@app.cell
def _(refresh_button, load_data):
    # Re-run when button clicked
    refresh_button  # Reference triggers re-run
    df = load_data()
    last_refresh = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    return df, last_refresh

Auto-Refresh Display

@app.cell(hide_code=True)
def _(mo, last_refresh):
    mo.md(f"""
    <div style="text-align: right; color: #666; font-size: 0.8em;">
        Last updated: {last_refresh}
    </div>
    """)
    return

---

Dashboard Template

Complete dashboard structure:

"""
Revenue Dashboard

A Marimo dashboard for monitoring key SaaS metrics.
Run with: marimo edit dashboard.py
"""

import marimo
import pandas as pd
import matplotlib.pyplot as plt
from datetime import datetime

__generated_with = "0.10.0"
app = marimo.App(width="full")


@app.cell
def _():
    import marimo as mo
    return (mo,)


@app.cell(hide_code=True)
def _(mo):
    mo.md("# Revenue Dashboard")
    return


@app.cell
def _(mo, df):
    # === FILTERS ===
    segment_filter = mo.ui.dropdown(
        options=['All'] + df['segment'].unique().tolist(),
        value='All',
        label='Segment'
    )

    date_range = mo.ui.date_range(
        start=df['date'].min(),
        stop=df['date'].max(),
        label='Date Range'
    )

    return segment_filter, date_range


@app.cell
def _(df, segment_filter, date_range):
    # === APPLY FILTERS ===
    df_filtered = df.copy()

    if segment_filter.value != 'All':
        df_filtered = df_filtered[df_filtered['segment'] == segment_filter.value]

    start, end = date_range.value
    df_filtered = df_filtered[
        (df_filtered['date'] >= pd.Timestamp(start)) &
        (df_filtered['date'] <= pd.Timestamp(end))
    ]

    return (df_filtered,)


@app.cell
def _(mo, df_filtered, kpi_card):
    # === KPI CARDS ===
    total_revenue = df_filtered['revenue'].sum()
    total_customers = df_filtered['customer_id'].nunique()
    avg_revenue = df_filtered.groupby('customer_id')['revenue'].sum().mean()

    kpi_row = mo.hstack([
        kpi_card('Total Revenue', f'${total_revenue:,.0f}'),
        kpi_card('Customers', f'{total_customers:,}'),
        kpi_card('Avg Revenue/Customer', f'${avg_revenue:,.0f}'),
    ], justify='space-around')

    return (kpi_row,)


@app.cell
def _(mo, kpi_row, trend_chart, segment_filter, date_range, data_table):
    # === LAYOUT ===
    sidebar = mo.vstack([
        mo.md("## Filters"),
        segment_filter,
        date_range,
    ])

    main_content = mo.vstack([
        kpi_row,
        mo.md("---"),
        trend_chart,
        mo.md("---"),
        data_table,
    ])

    mo.hstack([
        sidebar,
        main_content
    ], widths=[1, 4])
    return


if __name__ == "__main__":
    app.run()

---

Dashboard Anti-Patterns

Avoid These

Best Practices

1. Start with questions: What decisions does this dashboard support? 2. Hierarchy matters: Most important info at top 3. Consistent formatting: Same scales, colors, formats 4. Context is king: Comparisons, targets, benchmarks 5. Mobile consideration: Design for smallest screen first 6. Performance: Pre-aggregate, limit data points

Data Cleaning Reference

Data quality checklist, common transforms, and cleaning patterns for financial and RevOps data.

---

Data Quality Dimensions

Before cleaning, assess data quality across these dimensions:

---

Pre-Cleaning Checklist

Run this checklist before any analysis:

def data_quality_check(df, name="Dataset"):
    """Run comprehensive data quality check."""
    print(f"=== DATA QUALITY CHECK: {name} ===\n")

    # Shape
    print(f"Shape: {df.shape[0]:,} rows x {df.shape[1]} columns")

    # Missing values
    missing = df.isnull().sum()
    missing_pct = (missing / len(df) * 100).round(1)
    missing_df = pd.DataFrame({
        'missing': missing,
        'pct': missing_pct
    }).query('missing > 0').sort_values('pct', ascending=False)

    if len(missing_df) > 0:
        print(f"\n Missing Values:")
        print(missing_df.to_string())
    else:
        print("\n No missing values")

    # Duplicates
    dupes = df.duplicated().sum()
    print(f"\n Duplicates: {dupes:,} ({dupes/len(df)*100:.1f}%)")

    # Data types
    print(f"\n Data Types:")
    print(df.dtypes.value_counts().to_string())

    return missing_df

---

Missing Value Strategies

Strategy Selection Guide

Is the missing data random (MCAR)?
├── YES → Safe to drop or impute
│   ├── < 5% missing → Drop rows
│   ├── 5-20% missing → Impute
│   └── > 20% missing → Flag column, consider dropping
└── NO (systematic) → Understand why before handling
    ├── MNAR (value-dependent) → Model the missingness
    └── MAR (dependent on other columns) → Conditional imputation

Drop Strategies

# Drop rows with any missing values (use sparingly)
df_clean = df.dropna()

# Drop rows with missing values in specific columns
df_clean = df.dropna(subset=['critical_column'])

# Drop columns with too many missing values
threshold = 0.3  # 30% missing
cols_to_drop = df.columns[df.isnull().mean() > threshold]
df_clean = df.drop(columns=cols_to_drop)

# Log the decision
# FILTER: Dropped 247 rows with missing revenue values
# RATIONALE: Revenue is required for analysis, < 2% of data

Imputation Strategies

# Numeric imputation
df['value'] = df['value'].fillna(df['value'].median())

# Segment-based imputation (better for heterogeneous data)
df['value'] = df.groupby('segment')['value'].transform(
    lambda x: x.fillna(x.median())
)

# Categorical imputation
df['category'] = df['category'].fillna('Unknown')

# Time series interpolation
df['metric'] = df['metric'].interpolate(method='linear')

# Log imputation decisions
# IMPUTE: Filled 142 missing deal_size values with segment median
# QUALITY: Moderate - assumes within-segment similarity

Flag Missing Data

Sometimes preserving the fact that data was missing is valuable:

# Create indicator for missingness
df['value_was_missing'] = df['value'].isnull().astype(int)

# Then impute
df['value'] = df['value'].fillna(df['value'].median())

# Log the approach
# DERIVED: value_was_missing flag for ML feature
# RATIONALE: Missingness may be predictive of outcome

---

Outlier Detection

Detection Methods

def detect_outliers_iqr(series, multiplier=1.5):
    """Detect outliers using IQR method."""
    Q1 = series.quantile(0.25)
    Q3 = series.quantile(0.75)
    IQR = Q3 - Q1
    lower = Q1 - multiplier * IQR
    upper = Q3 + multiplier * IQR

    outliers = (series < lower) | (series > upper)
    return outliers, lower, upper

def detect_outliers_zscore(series, threshold=3):
    """Detect outliers using z-score."""
    z_scores = (series - series.mean()) / series.std()
    return abs(z_scores) > threshold

Handling Outliers

Is the outlier a data error?
├── YES (impossible value) → Fix or remove
│   └── e.g., negative revenue, 200% churn rate
└── NO (extreme but valid)
    ├── Keep but flag → Mark for separate analysis
    ├── Winsorize → Cap at percentile threshold
    └── Transform → Log transform for skewed data

# Cap outliers at percentiles (winsorization)
def winsorize(series, lower_pct=0.01, upper_pct=0.99):
    """Cap values at percentile thresholds."""
    lower = series.quantile(lower_pct)
    upper = series.quantile(upper_pct)
    return series.clip(lower=lower, upper=upper)

# Log transform for right-skewed data
df['revenue_log'] = np.log1p(df['revenue'])  # log(1+x) handles zeros

# Flag outliers for review
outliers, lower, upper = detect_outliers_iqr(df['deal_size'])
df['deal_size_outlier'] = outliers

# Log the decision
# OUTLIER: Capped 23 deal_size values at 99th percentile ($500K)
# RATIONALE: Valid large deals but skewing averages

---

Common Transforms

Pivot and Melt

# Wide to long (melt)
df_long = df.melt(
    id_vars=['customer_id', 'date'],
    value_vars=['mrr_jan', 'mrr_feb', 'mrr_mar'],
    var_name='month',
    value_name='mrr'
)

# Long to wide (pivot)
df_wide = df.pivot_table(
    index='customer_id',
    columns='month',
    values='mrr',
    aggfunc='sum'
).reset_index()

# Log transformation
# TRANSFORM: Pivoted monthly MRR to wide format for cohort analysis

Merge Patterns

# Inner join (keep only matches)
df_merged = df1.merge(df2, on='customer_id', how='inner')

# Left join (keep all from left)
df_merged = df1.merge(df2, on='customer_id', how='left')

# Check for merge issues
print(f"Left rows: {len(df1)}")
print(f"Merged rows: {len(df_merged)}")
print(f"Unmatched: {len(df1) - len(df_merged)}")

# Handle many-to-many carefully
# Log merge decisions
# MERGE: Joined customers to transactions on customer_id
# NOTE: 47 customers had no transactions (excluded from analysis)

GroupBy Patterns

# Basic aggregation
summary = df.groupby('segment').agg({
    'revenue': ['sum', 'mean', 'median', 'count'],
    'churn': 'mean'
}).round(2)

# Multiple grouping levels
summary = df.groupby(['segment', 'month']).agg({
    'mrr': 'sum',
    'customers': 'nunique'
}).reset_index()

# Rolling calculations
df['mrr_3mo_avg'] = df.groupby('customer_id')['mrr'].transform(
    lambda x: x.rolling(3, min_periods=1).mean()
)

# Percentage of total
df['pct_of_total'] = df.groupby('segment')['revenue'].transform(
    lambda x: x / x.sum() * 100
)

---

Type Coercion Recipes

Numeric Coercion

# String to numeric (handle errors)
df['amount'] = pd.to_numeric(df['amount'], errors='coerce')
# 'coerce' turns unparseable values to NaN

# Remove currency symbols and convert
df['price'] = (df['price']
    .str.replace('$', '', regex=False)
    .str.replace(',', '', regex=False)
    .astype(float)
)

# Percentage strings to float
df['rate'] = (df['rate']
    .str.rstrip('%')
    .astype(float) / 100
)

Categorical Coercion

# String to category (memory efficient)
df['segment'] = df['segment'].astype('category')

# Ordered categories
segment_order = ['SMB', 'Mid-Market', 'Enterprise']
df['segment'] = pd.Categorical(
    df['segment'],
    categories=segment_order,
    ordered=True
)

# Boolean coercion
df['is_active'] = df['status'].map({'Active': True, 'Inactive': False})

Date Coercion

# String to datetime
df['date'] = pd.to_datetime(df['date'], format='%Y-%m-%d')

# Multiple formats (let pandas infer)
df['date'] = pd.to_datetime(df['date'], infer_datetime_format=True)

# Handle errors
df['date'] = pd.to_datetime(df['date'], errors='coerce')

# Extract components
df['year'] = df['date'].dt.year
df['month'] = df['date'].dt.month
df['quarter'] = df['date'].dt.quarter
df['day_of_week'] = df['date'].dt.dayofweek

---

Deduplication Patterns

Identify Duplicates

# Exact duplicates (all columns)
dupes = df[df.duplicated(keep=False)]

# Duplicates on specific columns
dupes = df[df.duplicated(subset=['customer_id', 'date'], keep=False)]

# Count duplicates
dupe_counts = df.groupby(['customer_id', 'date']).size()
dupe_counts[dupe_counts > 1]

Handle Duplicates

# Keep first occurrence
df_deduped = df.drop_duplicates(keep='first')

# Keep last occurrence (often want most recent)
df_deduped = df.drop_duplicates(
    subset=['customer_id', 'date'],
    keep='last'
)

# Aggregate duplicates
df_deduped = df.groupby(['customer_id', 'date']).agg({
    'revenue': 'sum',      # Sum values
    'status': 'last',       # Keep last status
    'notes': lambda x: '; '.join(x.dropna())  # Concatenate
}).reset_index()

# Log deduplication
# DEDUP: Removed 156 duplicate customer-date records, kept most recent

---

Data Smell Detection

Common suspicious patterns to check for:

Suspicious Distributions

def check_suspicious_patterns(df, column):
    """Flag suspicious data patterns."""
    series = df[column].dropna()

    issues = []

    # Excessive zeros
    zero_pct = (series == 0).mean() * 100
    if zero_pct > 50:
        issues.append(f"High zero rate: {zero_pct:.1f}%")

    # Suspiciously round numbers
    round_pct = (series % 100 == 0).mean() * 100
    if round_pct > 30:
        issues.append(f"Many round numbers: {round_pct:.1f}%")

    # Negative values where unexpected
    if series.min() < 0 and 'revenue' in column.lower():
        issues.append(f"Negative values present")

    # Single value dominance
    mode_pct = (series == series.mode()[0]).mean() * 100
    if mode_pct > 50:
        issues.append(f"Single value dominates: {mode_pct:.1f}%")

    return issues

Common Data Smells

Referential Integrity Checks

# Check foreign key relationships
missing_customers = set(transactions['customer_id']) - set(customers['customer_id'])
if missing_customers:
    print(f"WARNING: {len(missing_customers)} transactions have unknown customers")

# Check date ranges align
if transactions['date'].max() > customers['signup_date'].max():
    print("WARNING: Transactions exist after latest customer signup")

# Check value ranges
invalid_rates = df[df['churn_rate'] > 1.0]
if len(invalid_rates) > 0:
    print(f"WARNING: {len(invalid_rates)} records have churn rate > 100%")

---

Cleaning Pipeline Template

def clean_pipeline(df):
    """
    Standard cleaning pipeline. Customize per dataset.

    Returns cleaned df and cleaning log.
    """
    log = []
    df = df.copy()
    original_rows = len(df)

    # 1. Remove exact duplicates
    df = df.drop_duplicates()
    log.append(f"DEDUP: Removed {original_rows - len(df)} exact duplicates")

    # 2. Standardize column names
    df.columns = df.columns.str.lower().str.replace(' ', '_')

    # 3. Parse dates
    date_cols = ['created_at', 'updated_at', 'date']
    for col in date_cols:
        if col in df.columns:
            df[col] = pd.to_datetime(df[col], errors='coerce')

    # 4. Handle missing values
    # (customize based on columns)

    # 5. Validate ranges
    # (customize based on domain)

    # 6. Create derived columns
    # (customize based on needs)

    log.append(f"FINAL: {len(df)} rows, {len(df.columns)} columns")

    return df, log

---

Decision Logging for Cleaning

Log every cleaning decision:

# === CLEANING LOG ===
# FILTER: Removed 247 rows with null revenue (1.6% of data)
# IMPUTE: Filled 89 missing segment values with 'Unknown'
# OUTLIER: Capped deal_size at 99th percentile (23 values affected)
# DEDUP: Removed 156 duplicate customer-month records, kept latest
# TRANSFORM: Converted revenue from cents to dollars
# DERIVED: Created mrr_change = current_mrr - prior_mrr

---

Quality Score Template

Grade overall data quality:

def calculate_quality_score(df):
    """Calculate data quality score A-F."""
    missing_pct = df.isnull().mean().mean() * 100
    dupe_pct = df.duplicated().mean() * 100

    # Simple scoring (customize based on domain)
    score = 100 - missing_pct - dupe_pct

    if score >= 95: return 'A'
    elif score >= 85: return 'B'
    elif score >= 70: return 'C'
    elif score >= 50: return 'D'
    else: return 'F'

Data Quality Validation Reference

Statistical sins, chart crimes, logic fallacies, and sanity checks for reviewing analytical claims.

---

Pre-Review Checklist

Before accepting any analytical finding, run through these checks:

## Data Quality Validation Checklist

### Quick Smell Test
- [ ] Does the conclusion seem too good/bad to be true?
- [ ] Can I explain the finding to someone in plain English?
- [ ] What would have to be true for this to be wrong?

### Statistical Checks
- [ ] Sample size disclosed and appropriate?
- [ ] Confidence intervals or p-values provided?
- [ ] Multiple comparisons accounted for?
- [ ] Base rates considered?

### Visual Checks
- [ ] Y-axis starts at zero (or justified if not)?
- [ ] Scales consistent across comparisons?
- [ ] Chart type appropriate for the data?

### Logic Checks
- [ ] Correlation claimed as causation?
- [ ] Selection/survivorship bias addressed?
- [ ] Alternative explanations considered?

### Sanity Checks
- [ ] Back-of-envelope math works?
- [ ] Consistent with historical patterns?
- [ ] Consistent with other data sources?

---

Critical Analysis Patterns

Market Context

Principle: Always compare YoY changes against market or baseline performance.

Why It Matters:

• A rep down -5% sounds bad
• But if the market is down -14%, they're actually +9% vs market
• Without market context, you're measuring luck, not skill

Standard Analysis Pattern:

# Add market-relative performance to all YoY analyses
df['YoY_vs_Market'] = df['YoY_Change'] - market_yoy_change

Report Output:

Rep A: -5% YoY (but +9% vs market)
Rep B: +3% YoY (but -11% vs market)

Questions to Ask:

• What's the relevant baseline (market, industry, portfolio average)?
• Are we measuring absolute performance or relative performance?
• Is "down" actually "outperforming in a down market"?

---

Weighting Sensitivity

Principle: When composite scores use arbitrary weights, test sensitivity across multiple scenarios.

Why It Matters:

• If someone is ranked #1 with weights 40/30/30 but #8 with weights 33/33/33, the ranking isn't robust
• Arbitrary weight choices can drive conclusions more than underlying data
• "Top performer" claims need to hold across reasonable weight variations

Testing Protocol:

# Test 5-6 weighting scenarios
weight_scenarios = [
    {'revenue': 0.40, 'growth': 0.30, 'retention': 0.30},
    {'revenue': 0.33, 'growth': 0.33, 'retention': 0.33},
    {'revenue': 0.50, 'growth': 0.25, 'retention': 0.25},
    {'revenue': 0.25, 'growth': 0.50, 'retention': 0.25},
    {'revenue': 0.25, 'growth': 0.25, 'retention': 0.50},
]

# Track classification stability
classifications = []
for weights in weight_scenarios:
    score = compute_composite(df, weights)
    classifications.append(classify(score))

# Only "confident" if classification holds across ALL scenarios
df['Robust_Classification'] = 'confident' if all_same(classifications) else 'uncertain'

Reporting Guidance:

• Flag classifications that flip with different weightings as "uncertain"
• Only call something "top tier" if it's top tier across all reasonable weightings
• Document which weight assumptions would change the conclusion

---

Bootstrap Confidence Intervals

Principle: For small samples, use bootstrap to generate P10/P50/P90 ranges.

Why It Matters:

• Point estimates are misleading when 59 vs 61 is statistically indistinguishable
• Small samples (n < 30 or few time periods) have high variance
• Rankings based on point estimates overstate precision

Code Pattern:

import numpy as np

def bootstrap_confidence(data, n_bootstrap=1000, metric_func=np.mean):
    """Generate P10/P50/P90 for any metric."""
    bootstrap_scores = []
    for _ in range(n_bootstrap):
        sample = np.random.choice(data, size=len(data), replace=True)
        bootstrap_scores.append(metric_func(sample))

    p10, p50, p90 = np.percentile(bootstrap_scores, [10, 50, 90])
    return {'P10': p10, 'P50': p50, 'P90': p90}

# Usage
result = bootstrap_confidence(rep_performance_scores)
# Report: "Score: 61 (P10-P90: 54-68)" not just "Score: 61"

When to Use:

• Any ranking with < 30 observations per entity
• Performance scores based on < 5 time periods
• Any "top performer" or "bottom performer" claim

Reporting Pattern:

Rep A: 61 points (P10-P90: 54-68)
Rep B: 59 points (P10-P90: 52-66)
Conclusion: Statistically indistinguishable

---

Survivorship Bias Quantification

Principle: Don't just acknowledge survivorship bias—quantify it with a Data_Availability column.

Why It Matters:

• Saying "note: excludes reps with incomplete data" is not enough
• Readers need to know what % of the population is analyzed
• Missing data often correlates with poor performance (non-random)

Implementation Pattern:

# Add Data_Availability column to all entity-level analyses
df['Data_Availability'] = df.apply(
    lambda row: f"{row['periods_with_data']}/{total_periods} periods",
    axis=1
)

# Calculate coverage statistics
coverage_stats = {
    'total_population': total_entities,
    'included_in_analysis': included_count,
    'excluded_count': excluded_count,
    'coverage_pct': included_count / total_entities * 100
}

Required Reporting:

## Data Coverage

- Total reps in organization: 45
- Reps with complete data (all 12 quarters): 32 (71%)
- Reps with partial data (included with caveats): 8 (18%)
- Reps excluded (< 4 quarters): 5 (11%)

**Excluded reps:** [Names] - reasons: [new hire, territory change, leave]

**Caveat:** Results may overstate performance if excluded reps
were terminated for poor performance.

Questions to Ask:

• What % of the population has complete data?
• Is the missingness random or correlated with outcome?
• How would conclusions change if we included partial data?

---

Statistical Sins

P-Hacking / Multiple Comparisons

The Sin: Running many statistical tests until finding a "significant" result, then reporting only that one.

Warning Signs:

• "We found a significant effect" without mentioning what else was tested
• Subgroup analysis that wasn't pre-specified
• P-values just barely under 0.05 (p=0.048, p=0.049)
• No mention of multiple comparison corrections

Questions to Ask:

• How many comparisons were tested?
• Was the hypothesis stated before or after seeing the data?
• Is there a Bonferroni or FDR correction applied?

Rule of Thumb:

If you test 20 things at p<0.05, you expect 1 false positive by chance.

---

Small Sample Extrapolation

The Sin: Drawing strong conclusions from insufficient data.

Warning Signs:

• Strong claims from n < 30
• No confidence intervals shown
• "100% of customers" when n = 3
• Percentages from small counts (20% = 1 out of 5)

Questions to Ask:

• What's the sample size?
• How wide would the confidence interval be?
• Would the conclusion hold if 2-3 observations were different?

Quick Check:

For proportions: Need ~385 samples for 95% CI within +/- 5%
For means: Need ~30 per group for t-test validity
For segments: Need 20+ per segment for stable estimates

---

Missing Confidence Intervals

The Sin: Presenting point estimates without uncertainty.

Warning Signs:

• "The conversion rate is 5.2%" (no range)
• "Sales increased 12%" (no confidence bound)
• Single numbers presented as truth
• No mention of variance or error

Questions to Ask:

• What's the margin of error?
• How confident are we in this estimate?
• What's the range of plausible values?

Rule of Thumb:

Any metric should be: Value +/- Range (Confidence Level)
Example: "5.2% +/- 1.1% (95% CI)"

---

Cherry-Picked Baselines

The Sin: Choosing comparison points that make results look better.

Warning Signs:

• "Since [specific date]..." without justification
• Comparing to worst historical period
• Excluding "outlier" periods that don't support the narrative
• Different baselines for different metrics

Questions to Ask:

• Why was this baseline chosen?
• What would the result look like with a different baseline?
• Is this baseline representative?

Always Check:

• Last month, quarter, year
• Same period last year (YoY)
• Trailing average (3mo, 6mo, 12mo)
• Pre-COVID if comparing to 2020-2021

---

Survivorship Bias

The Sin: Only analyzing successes while ignoring failures.

Warning Signs:

• "Successful customers do X" (what about unsuccessful ones?)
• "Our retained customers say..." (what about churned?)
• Historical analysis starting after failures were removed
• Case studies only of wins

Questions to Ask:

• Who/what is excluded from this analysis?
• Would the conclusion change if we included failures?
• Is this sample representative of all cases?

---

Base Rate Neglect

The Sin: Ignoring how common something is overall when interpreting results.

Example:

• "Our test detected 80% of churners!"
• But: If only 2% of customers churn, and test has 10% false positive rate...
• 80% of 2% = 1.6% true positives
• 10% of 98% = 9.8% false positives
• Precision = 1.6 / 11.4 = 14% (most "detected churners" aren't actually churners)

Questions to Ask:

• What's the base rate of the outcome?
• What's the false positive rate?
• In absolute terms, how many true vs false positives?

---

Chart Crimes

Truncated Y-Axis

The Crime: Starting the y-axis above zero to exaggerate differences.

Example:

Misleading:         Honest:
|    *              |
|   /               |
|  /                |        ___*
| /                 |    ___/
+----               +----/--------
  Time                   Time

When Acceptable:

• Clearly labeled
• Focus on small but meaningful changes
• Stock prices, scientific data where zero isn't meaningful

Red Flags:

• Bar charts that don't start at zero
• Dramatic-looking line charts
• No axis labels visible

---

Dual Y-Axes Manipulation

The Crime: Using two y-axes scaled to imply false correlation.

Warning Signs:

• Two lines that track perfectly on different scales
• No justification for scale choices
• "These two things are clearly related!" (visually)

Better Approach:

• Use small multiples (separate charts)
• Normalize both series to same scale (% change, z-score)
• Show actual correlation coefficient

---

3D Charts

The Crime: Using 3D effects that distort perception.

Problems:

• Perspective makes bars/slices look different sizes
• Harder to read exact values
• No additional information conveyed
• Looks "fancy" but communicates worse

Rule: Never use 3D charts. Ever.

---

Misleading Scales/Aspect Ratios

The Crime: Stretching or compressing charts to change perception.

Warning Signs:

• Very wide or very tall aspect ratios
• Log scale without clear labeling
• Different scales on comparison charts
• Broken axes

Best Practice:

• Standard aspect ratio (~1.6:1 or 4:3)
• Consistent scales across comparisons
• Clear axis labels with units

---

Pie Chart Sins

The Crimes:

• More than 5 slices
• Slices that don't sum to 100%
• 3D pie charts
• Exploded slices
• Similar-sized slices that are hard to compare

When Pie Charts Are OK:

• 2-4 categories
• Parts of a whole (100%)
• Categories are very different sizes

Better Alternatives:

• Horizontal bar chart
• Treemap
• Simple table

---

Area Chart Manipulation

The Crime: Using area to represent single-dimension data.

Problems:

• Area grows quadratically, not linearly
• A circle twice as wide has 4x the area
• Icons of different sizes are misleading

Example:

If sales doubled, but icon is 2x wider AND 2x taller:
Icon appears 4x larger, not 2x

---

Logic Fallacies

Correlation vs Causation

The Fallacy: Assuming that because two things are correlated, one causes the other.

Classic Examples:

• Ice cream sales correlate with drowning deaths (both caused by summer)
• Countries that eat more chocolate have more Nobel laureates (wealth confounds both)
• Feature users have lower churn (users who would retain anyway use more features)

Questions to Ask:

• Is there a plausible mechanism for causation?
• Could there be a third variable causing both?
• What would a randomized experiment show?

Language Check:

• "Associated with" - OK for correlation
• "Causes" / "Results in" / "Leads to" - Requires causal evidence

---

Ecological Fallacy

The Fallacy: Assuming what's true for a group is true for individuals.

Example:

• "States with higher average income voted for X"
• Does NOT mean rich individuals voted for X
• Could be that poor people in rich states voted for X

In Business:

• "Enterprise segment has higher NPS"
• Doesn't mean every enterprise customer is happier
• Could be a few very happy customers pulling up average

Fix: Always check individual-level data when making individual-level claims.

---

Regression to the Mean

The Fallacy: Attributing normal variation to an intervention.

Example:

• Sales rep has best month ever
• Gets special coaching
• Next month is worse
• "Coaching didn't work!"

Reality: Extreme performance naturally regresses toward average.

Questions to Ask:

• Was performance unusually high/low before intervention?
• What's the natural variation in this metric?
• Is there a control group?

---

Texas Sharpshooter

The Fallacy: Drawing the target after the bullets are fired.

Example:

• Analyze 100 customer attributes
• Find one that correlates with churn
• Report as "Customers with X are 3x more likely to churn!"

Fix: Pre-register hypotheses or adjust for multiple testing.

---

Availability Heuristic

The Fallacy: Overweighting recent or memorable examples.

In Business:

• "Customers hate feature X" (based on 3 loud complaints)
• "Deal sizes are increasing" (remembering recent big wins)
• "Churn is spiking" (one notable logo lost)

Fix: Always check the aggregate data, not just memorable examples.

---

Sanity Checks

The Smell Test

Questions:

• Does this pass the "hmm, really?" test?
• Would I bet money on this being true?
• What would a skeptic say?
• What would have to be true for this to be wrong?

Red Flags:

• Results that perfectly confirm what stakeholder wanted to hear
• Dramatic changes that no one noticed happening
• Findings that contradict common sense without good explanation

---

Back-of-Envelope Validation

Method: Quick mental math to sanity check claims.

Example Claims to Validate:

Claim: "New feature increased revenue by $10M"
Check: Total customers * adoption rate * incremental spend
       10,000 * 20% * $X = $10M → X = $5,000 per adopter
       Does $5,000 incremental spend make sense?

Claim: "We have 150% NRR"
Check: Starting MRR * 1.5 = Ending MRR from that cohort
       If 10% churn, need 60% expansion on remaining 90%
       Does 60% expansion rate make sense?

Claim: "Response rate was 85%"
Check: Typical survey response rates are 10-30%
       85% is unusual - was this mandatory? Self-selected?

---

Historical Comparison

Always Ask:

• How does this compare to last period?
• How does this compare to same period last year?
• Is this within normal historical range?
• What's the trend over time?

Suspicious If:

• Current period is dramatically different with no explanation
• Pattern breaks without known cause
• Results don't match known events (seasonality, launches, etc.)

---

Cross-Source Validation

Method: Check the same metric from different sources.

Questions:

• Does CRM data match finance data?
• Does survey data match behavioral data?
• Do different calculation methods give similar results?

Red Flags:

• Large discrepancies between sources
• Only one source ever cited
• Metric definition changes between reports

---

"Too Good to Be True" Patterns

Watch For:

• Perfect trends (real data is noisy)
• Round numbers (real metrics are messy)
• Results exactly matching targets/expectations
• No caveats or limitations mentioned
• Universal agreement (some customers always disagree)

Rule of Thumb:

If it seems too good to be true, it probably is.
Dig deeper or get independent verification.

---

Review Workflow

When reviewing someone's analysis:

1. Source Check (2 min)

• Where does the data come from?
• What's the time period?
• Any known data quality issues?

2. Method Check (3 min)

• How was the metric calculated?
• What's excluded?
• Are comparisons apples-to-apples?

3. Statistical Check (3 min)

• Sample size appropriate?
• Confidence intervals provided?
• Multiple comparisons addressed?

4. Visual Check (2 min)

• Do charts follow best practices?
• Are scales appropriate?
• Any visual manipulation?

5. Logic Check (3 min)

• Causation claimed without evidence?
• Alternative explanations considered?
• Survivorship/selection bias addressed?

6. Sanity Check (2 min)

• Does back-of-envelope math work?
• Consistent with historical patterns?
• Would I bet money on this?

---

Diplomatic Pushback Phrases

When you spot issues, use these to push back professionally:

For Missing Information:

• "Could you share the sample size and confidence interval?"
• "What time period does this cover?"
• "How is [metric] defined in this analysis?"

For Questionable Methods:

• "I want to make sure I understand - how was this calculated?"
• "What would this look like with [alternative baseline/method]?"
• "Have we controlled for [potential confounder]?"

For Suspicious Results:

• "This is interesting - it's quite different from what I expected. Can we dig in?"
• "Let me do a quick sanity check on the math here..."
• "What would cause this to be wrong?"

For Chart Issues:

• "Could we see this with the y-axis starting at zero?"
• "Would it help to show this as [alternative chart type]?"
• "I'm having trouble reading the scale here..."

---

Summary Checklist

## Data Quality Validation Summary

### Before Presenting
- [ ] All data sources documented
- [ ] Sample sizes and confidence intervals included
- [ ] Charts follow best practices
- [ ] Alternative explanations considered
- [ ] Caveats and limitations stated
- [ ] Back-of-envelope math verified
- [ ] Historical context provided

### Before Accepting
- [ ] Passed smell test
- [ ] Method understood and appropriate
- [ ] Statistical validity confirmed
- [ ] Visual integrity verified
- [ ] Logic checked for fallacies
- [ ] Cross-validated where possible

Data Wishlisting Guide

Document data gaps, evaluate proxies, and rate quality of workarounds. Every analysis should include a wishlist of ideal data that wasn't available.

---

Why Wishlist Data?

1. Transparency - Stakeholders know what's missing 2. Prioritization - Helps data team know what to collect 3. Hedge appropriately - Findings are qualified correctly 4. Future-proofing - Next iteration can improve

---

Wishlist Format

Use this table format in every analysis:

## Data Wishlist

| Missing Data | Proxy Used | Quality | Impact on Analysis | Data Source If Available |
|--------------|------------|---------|-------------------|--------------------------|
| [Ideal data] | [What we used instead] | Strong/Moderate/Weak | [How it affects findings] | [Where we could get it] |

Example

## Data Wishlist

| Missing Data | Proxy Used | Quality | Impact on Analysis | Data Source If Available |
|--------------|------------|---------|-------------------|--------------------------|
| Customer NPS scores | Support ticket sentiment | Weak | Core finding re: satisfaction needs validation | Quarterly NPS survey (Delighted) |
| True customer LTV | 12-month revenue | Moderate | Acceptable for segmentation, not for CAC analysis | 24+ month cohort tracking |
| Marketing attribution | Last-touch attribution | Weak | Cannot assess multi-channel impact | Multi-touch attribution tool |
| Product usage depth | Login frequency | Moderate | Directionally correct, misses engagement quality | In-app analytics (Amplitude) |

---

Quality Rating Scale

Strong

Definition: Proxy closely approximates the ideal data with minimal information loss.

Characteristics:

• High correlation with ideal metric (r > 0.8)
• Same underlying construct
• Validated in similar contexts
• Widely accepted in industry

Examples:

Moderate

Definition: Proxy provides directional signal but has known limitations.

Characteristics:

• Moderate correlation with ideal (0.5 < r < 0.8)
• Captures part of the construct
• Some information loss
• Requires caveats in interpretation

Examples:

Weak

Definition: Proxy provides rough signal with significant limitations. Use with heavy caveats.

Characteristics:

• Low correlation with ideal (r < 0.5)
• Different underlying construct
• Significant information loss
• Should note as "directional only"

Examples:

---

Common Proxy Patterns by Domain

Revenue & Finance

Customer Success

Sales & Pipeline

Marketing

---

Derivation Patterns

When the exact data doesn't exist, sometimes you can derive it from combinations of available data.

Pattern: Calculate from Components

Example: Gross margin % not available

# Derive from available data
gross_margin_pct = (revenue - cogs) / revenue * 100

# Log the derivation
# DERIVED: Gross margin calculated from revenue and COGS
# ASSUMPTION: COGS is complete - confidence: High

Pattern: Impute from Segments

Example: Missing values in a field

# Use segment averages for imputation
df['value_imputed'] = df.groupby('segment')['value'].transform(
    lambda x: x.fillna(x.median())
)

# Log the imputation
# DERIVED: Missing values imputed with segment median (n=47 records)
# QUALITY: Moderate - assumes within-segment similarity

Pattern: Historical Averages

Example: Forecasting without complete data

# Use historical patterns
seasonal_factor = historical_data.groupby('month').mean()

# Log the assumption
# DERIVED: Seasonal factors from 2022-2023 data
# ASSUMPTION: Seasonality consistent year-over-year - confidence: Medium

Pattern: Proxy Combination

Example: Health score from multiple weak signals

# Combine multiple weak proxies into stronger composite
health_score = (
    0.3 * normalized_login_frequency +
    0.3 * normalized_feature_adoption +
    0.2 * normalized_support_tickets_inverse +
    0.2 * normalized_nps_score
)

# Log the construction
# DERIVED: Health score composite from 4 signals
# QUALITY: Moderate - validated against historical churn (AUC=0.72)

---

Impact Assessment

For each data gap, assess how it affects your analysis:

High Impact

• Affects core finding or recommendation
• Would change conclusion if different
• Central to the analysis question

Action: Heavy caveats, consider waiting for better data, flag as preliminary

Medium Impact

• Affects supporting analysis
• Wouldn't change main conclusion
• Useful but not critical

Action: Note in methodology, include in appendix

Low Impact

• Nice to have
• Marginal improvement
• Doesn't affect conclusions

Action: Note in wishlist, deprioritize for future collection

---

Wishlist Template

Copy this to your analysis notebook:

## Data Wishlist

### High Impact Gaps

| Missing | Proxy | Quality | Impact | Source |
|---------|-------|---------|--------|--------|
| | | | | |

### Medium Impact Gaps

| Missing | Proxy | Quality | Impact | Source |
|---------|-------|---------|--------|--------|
| | | | | |

### Low Impact Gaps

| Missing | Proxy | Quality | Impact | Source |
|---------|-------|---------|--------|--------|
| | | | | |

### Derivations Used

| Derived Metric | Components | Method | Confidence |
|----------------|------------|--------|------------|
| | | | |

### Recommendations for Data Collection

1. [Highest priority data to start collecting]
2. [Second priority]
3. [Third priority]

---

Decision Log Format

When using proxies, log them:

# PROXY: Support ticket sentiment for NPS - quality: Weak
# IMPACT: Core finding - needs validation with actual NPS data
# RATIONALE: Only available satisfaction signal; directional use only

# DERIVED: LTV calculated as 24-month cumulative revenue
# ASSUMPTION: Customer relationships > 24 months follow similar pattern
# CONFIDENCE: Medium - based on cohort analysis showing stabilization at 18mo

Datetime Handling Reference

Timezone hell, format parsing, fiscal calendars, and common datetime gotchas for financial and RevOps analysis.

---

Timezone Fundamentals

Golden Rules

1. Store in UTC - Always convert to UTC for storage and computation 2. Display in local - Convert to user's timezone only for display 3. Document the timezone - Every timestamp should have explicit timezone info 4. Be consistent - All datetimes in one analysis should use the same timezone

Timezone Conversion Patterns

import pandas as pd
from datetime import datetime
import pytz

# Make naive datetime timezone-aware
naive_dt = datetime(2024, 1, 15, 9, 0, 0)
utc_dt = pytz.UTC.localize(naive_dt)

# Convert between timezones
eastern = pytz.timezone('US/Eastern')
pacific = pytz.timezone('US/Pacific')
eastern_dt = utc_dt.astimezone(eastern)
pacific_dt = utc_dt.astimezone(pacific)

# Pandas timezone operations
df['timestamp_utc'] = pd.to_datetime(df['timestamp']).dt.tz_localize('UTC')
df['timestamp_eastern'] = df['timestamp_utc'].dt.tz_convert('US/Eastern')

# Remove timezone info (for comparisons)
df['timestamp_naive'] = df['timestamp_utc'].dt.tz_localize(None)

Common Timezone Codes

DST Gotchas

# Daylight Saving Time creates problems
# Spring forward: 2:00 AM doesn't exist (March)
# Fall back: 2:00 AM happens twice (November)

# Handle ambiguous times (fall back)
df['timestamp'] = pd.to_datetime(df['timestamp']).dt.tz_localize(
    'US/Eastern',
    ambiguous='NaT'  # Mark ambiguous as NaT
    # or ambiguous='infer' to guess
)

# Handle non-existent times (spring forward)
df['timestamp'] = pd.to_datetime(df['timestamp']).dt.tz_localize(
    'US/Eastern',
    nonexistent='shift_forward'  # Shift to valid time
    # or nonexistent='NaT' to mark as missing
)

---

Date Parsing

Common Format Codes

Parsing Common Formats

# ISO 8601 (ideal format)
df['date'] = pd.to_datetime(df['date'])  # Auto-detects

# American format (MM/DD/YYYY)
df['date'] = pd.to_datetime(df['date'], format='%m/%d/%Y')

# European format (DD/MM/YYYY)
df['date'] = pd.to_datetime(df['date'], format='%d/%m/%Y')

# With time
df['datetime'] = pd.to_datetime(df['datetime'], format='%Y-%m-%d %H:%M:%S')

# 12-hour time with AM/PM
df['datetime'] = pd.to_datetime(df['datetime'], format='%m/%d/%Y %I:%M %p')

# Excel serial dates
df['date'] = pd.to_datetime(df['date'], unit='D', origin='1899-12-30')

# Unix timestamps (seconds since 1970)
df['date'] = pd.to_datetime(df['timestamp'], unit='s')

# Unix timestamps (milliseconds)
df['date'] = pd.to_datetime(df['timestamp'], unit='ms')

Handling Mixed Formats

# Let pandas infer (slower but flexible)
df['date'] = pd.to_datetime(df['date'], infer_datetime_format=True)

# Handle errors gracefully
df['date'] = pd.to_datetime(df['date'], errors='coerce')  # Invalid -> NaT

# Try multiple formats
def parse_date_flexible(date_str):
    """Try multiple date formats."""
    formats = [
        '%Y-%m-%d',
        '%m/%d/%Y',
        '%d/%m/%Y',
        '%Y/%m/%d',
        '%B %d, %Y',
        '%d-%b-%Y',
    ]
    for fmt in formats:
        try:
            return pd.to_datetime(date_str, format=fmt)
        except ValueError:
            continue
    return pd.NaT

df['date'] = df['date_str'].apply(parse_date_flexible)

---

Fiscal Calendar Handling

Fiscal Year Setup

def get_fiscal_year(date, fy_start_month=1):
    """
    Get fiscal year for a date.

    Args:
        date: datetime
        fy_start_month: Month when FY starts (1=Jan, 2=Feb, etc.)

    Returns:
        Fiscal year (int)
    """
    if date.month >= fy_start_month:
        return date.year if fy_start_month == 1 else date.year + 1
    else:
        return date.year if fy_start_month == 1 else date.year

def get_fiscal_quarter(date, fy_start_month=1):
    """Get fiscal quarter (1-4) for a date."""
    # Adjust month to fiscal calendar
    adjusted_month = (date.month - fy_start_month) % 12 + 1
    return (adjusted_month - 1) // 3 + 1

# Apply to DataFrame
df['fiscal_year'] = df['date'].apply(lambda x: get_fiscal_year(x, fy_start_month=2))
df['fiscal_quarter'] = df['date'].apply(lambda x: get_fiscal_quarter(x, fy_start_month=2))

Common Fiscal Year Patterns

FY vs CY Comparison

# Create both calendar and fiscal year columns
df['calendar_year'] = df['date'].dt.year
df['calendar_quarter'] = df['date'].dt.quarter

# Fiscal year starting February
df['fiscal_year'] = df['date'].apply(lambda x: x.year if x.month < 2 else x.year + 1)
df['fiscal_quarter'] = df['date'].apply(
    lambda x: ((x.month - 2) % 12) // 3 + 1
)

# Log the convention
# ASSUMPTION: Using February fiscal year (FY2024 = Feb 2023 - Jan 2024)

---

Period Aggregation

Daily to Weekly

# Week starting Monday (default)
df['week'] = df['date'].dt.to_period('W').dt.start_time

# Week starting Sunday
df['week'] = df['date'].dt.to_period('W-SAT').dt.start_time

# ISO week number
df['iso_week'] = df['date'].dt.isocalendar().week
df['iso_year'] = df['date'].dt.isocalendar().year

Daily to Monthly

# Month period
df['month'] = df['date'].dt.to_period('M')

# First of month
df['month_start'] = df['date'].dt.to_period('M').dt.start_time

# Last of month
df['month_end'] = df['date'] + pd.offsets.MonthEnd(0)

# Month aggregation
monthly = df.groupby(df['date'].dt.to_period('M')).agg({
    'revenue': 'sum',
    'customers': 'nunique'
})

Monthly to Quarterly

# Quarter period
df['quarter'] = df['date'].dt.to_period('Q')

# Quarter label (Q1 2024)
df['quarter_label'] = df['date'].dt.year.astype(str) + ' Q' + df['date'].dt.quarter.astype(str)

# Quarterly aggregation
quarterly = df.groupby(df['date'].dt.to_period('Q')).agg({
    'revenue': 'sum'
})

Rolling Periods

# Last 30 days (trailing)
df_last_30 = df[df['date'] >= df['date'].max() - pd.Timedelta(days=30)]

# Rolling 3-month sum
df['rolling_3mo'] = df.groupby('customer_id')['revenue'].transform(
    lambda x: x.rolling(3, min_periods=1).sum()
)

# Year-to-date
df['ytd'] = df.groupby(df['date'].dt.year)['revenue'].cumsum()

# Trailing twelve months (TTM)
df['ttm_revenue'] = df.sort_values('date').groupby('customer_id')['revenue'].transform(
    lambda x: x.rolling(12, min_periods=1).sum()
)

---

Business Day Calculations

Business Days Between Dates

import numpy as np

# Business days between two dates
def business_days_between(start, end):
    """Count business days (excluding weekends)."""
    return np.busday_count(
        start.date() if hasattr(start, 'date') else start,
        end.date() if hasattr(end, 'date') else end
    )

df['days_to_close'] = df.apply(
    lambda row: business_days_between(row['created_date'], row['closed_date']),
    axis=1
)

# Add business days to date
from pandas.tseries.offsets import BusinessDay

df['due_date'] = df['created_date'] + BusinessDay(5)

Custom Holidays

from pandas.tseries.holiday import USFederalHolidayCalendar

# US federal holidays
cal = USFederalHolidayCalendar()
holidays = cal.holidays(start='2024-01-01', end='2024-12-31')

# Business days excluding holidays
def business_days_with_holidays(start, end, holidays):
    """Business days excluding holidays."""
    return np.busday_count(
        start.date(),
        end.date(),
        holidays=holidays.to_numpy().astype('datetime64[D]')
    )

# Custom business day offset
from pandas.tseries.offsets import CustomBusinessDay

custom_bd = CustomBusinessDay(holidays=holidays)
df['next_business_day'] = df['date'] + custom_bd

Month/Quarter Boundaries

from pandas.tseries.offsets import MonthEnd, QuarterEnd, YearEnd

# End of month
df['month_end'] = df['date'] + MonthEnd(0)

# End of quarter
df['quarter_end'] = df['date'] + QuarterEnd(0)

# Start of month
df['month_start'] = df['date'] - MonthEnd(1) + pd.Timedelta(days=1)

# Is end of month?
df['is_month_end'] = df['date'].dt.is_month_end

# Is end of quarter?
df['is_quarter_end'] = df['date'].dt.is_quarter_end

---

Common Datetime Gotchas

Gotcha 1: Comparing Timezone-Aware and Naive

# This will fail:
# aware_dt > naive_dt  # TypeError!

# Solution: Make both aware or both naive
naive_dt = aware_dt.replace(tzinfo=None)
# or
aware_dt = naive_dt.replace(tzinfo=pytz.UTC)

Gotcha 2: Date vs Datetime Comparisons

# Comparing date to datetime can be tricky
date_val = pd.Timestamp('2024-01-15').date()
datetime_val = pd.Timestamp('2024-01-15 10:00:00')

# This compares correctly:
df[df['datetime'].dt.date == date_val]

# But this includes only midnight:
df[df['datetime'] == pd.Timestamp('2024-01-15')]

# Include full day:
df[(df['datetime'] >= '2024-01-15') & (df['datetime'] < '2024-01-16')]

Gotcha 3: Week Number Boundaries

# ISO week can cross year boundaries
# Week 1 2024 starts on Monday Jan 1 2024
# But Dec 31 2023 might be in Week 1 2024

# Use ISO week/year together
df['iso_year'] = df['date'].dt.isocalendar().year
df['iso_week'] = df['date'].dt.isocalendar().week
df['iso_year_week'] = df['iso_year'].astype(str) + '-W' + df['iso_week'].astype(str).str.zfill(2)

Gotcha 4: Daylight Saving Time Gaps

# Some hours don't exist (spring forward)
# Some hours happen twice (fall back)

# 2024-03-10 02:00 doesn't exist in US/Eastern
# 2024-11-03 01:00 happens twice in US/Eastern

# Always work in UTC, convert for display only
df['timestamp_utc'] = df['timestamp'].dt.tz_convert('UTC')

Gotcha 5: Leap Years and Month Lengths

# February 29 only exists in leap years
# Months have 28-31 days

# Safe "same day last month" calculation
from dateutil.relativedelta import relativedelta

def same_day_last_month(date):
    """Get same day last month (handles varying month lengths)."""
    return date - relativedelta(months=1)

# Same day last year (handles leap years)
def same_day_last_year(date):
    return date - relativedelta(years=1)

Gotcha 6: String Sorting

# String dates sort incorrectly:
# '2024-1-15' < '2024-1-2' (wrong!)

# Always zero-pad or use datetime:
df['date_str'] = df['date'].dt.strftime('%Y-%m-%d')  # Zero-padded

# Or sort as datetime, then convert:
df = df.sort_values('date')
df['date_display'] = df['date'].dt.strftime('%B %d, %Y')

---

Datetime Validation

def validate_dates(df, date_col, min_date=None, max_date=None):
    """Validate date column and report issues."""
    issues = []

    # Check for nulls
    null_count = df[date_col].isnull().sum()
    if null_count > 0:
        issues.append(f"{null_count} null values")

    # Check for future dates
    future = df[df[date_col] > pd.Timestamp.now()]
    if len(future) > 0:
        issues.append(f"{len(future)} future dates")

    # Check for dates outside expected range
    if min_date:
        early = df[df[date_col] < pd.Timestamp(min_date)]
        if len(early) > 0:
            issues.append(f"{len(early)} dates before {min_date}")

    if max_date:
        late = df[df[date_col] > pd.Timestamp(max_date)]
        if len(late) > 0:
            issues.append(f"{len(late)} dates after {max_date}")

    return issues

# Log validation results
# VALIDATION: transaction_date - 12 null values, 3 future dates

---

Decision Logging for Dates

# === DATE HANDLING LOG ===
# TIMEZONE: All timestamps converted to UTC for analysis
# FISCAL: Using February fiscal year (FY2024 = Feb 2023 - Jan 2024)
# AGGREGATION: Weekly aggregation using Monday start (ISO week)
# FILTER: Excluded 47 records with dates > today (future dates)
# ASSUMPTION: Transaction timestamps are in US/Eastern - confidence: High

SaaS & RevOps Metrics Reference

Revenue Metrics

ARR (Annual Recurring Revenue)

Definition: The annualized value of recurring revenue from subscriptions.

Formula:

ARR = MRR × 12

Edge cases:

• Exclude one-time fees (setup, professional services)
• Include only committed, contracted revenue
• For mid-contract changes, use the new rate going forward

MRR (Monthly Recurring Revenue)

Definition: Normalized monthly value of recurring subscriptions.

Formula:

MRR = Sum of (each customer's monthly subscription value)

For non-monthly contracts:

Annual contract: MRR = Annual value / 12
Quarterly contract: MRR = Quarterly value / 3

MRR Components

MRR Movement:

Ending MRR = Starting MRR + New MRR + Expansion MRR - Contraction MRR - Churned MRR

---

Churn Metrics

Logo Churn Rate (Customer Churn)

Definition: Percentage of customers lost in a period.

Formula:

Logo Churn Rate = (Customers lost in period / Customers at start of period) × 100

Gotcha: Does NOT account for customer value. A $100/mo customer = a $100,000/mo customer.

Revenue Churn Rate (Gross Revenue Churn)

Definition: Percentage of MRR lost from existing customers.

Formula:

Gross Revenue Churn = (Churned MRR + Contraction MRR) / Starting MRR × 100

Note: This is always positive or zero. Does not include expansion.

Net Revenue Retention (NRR)

Definition: Revenue retained from existing customers including expansion.

Formula:

NRR = (Starting MRR - Churned MRR - Contraction MRR + Expansion MRR) / Starting MRR × 100

Interpretation:

• NRR > 100%: Expansion exceeds churn (healthy)
• NRR = 100%: Breaking even on existing customers
• NRR < 100%: Losing revenue from existing base

Benchmarks:

Gross Revenue Retention (GRR)

Definition: Revenue retained excluding expansion (floor of retention).

Formula:

GRR = (Starting MRR - Churned MRR - Contraction MRR) / Starting MRR × 100

Note: GRR can never exceed 100%. It's capped by definition.

Logo vs Revenue Churn Decision:

• Use logo churn when: customer count matters (product-led growth, network effects)
• Use revenue churn when: revenue concentration exists, enterprise focus

---

Customer Value Metrics

LTV (Lifetime Value)

Definition: Total revenue expected from a customer over their lifetime.

Simple formula:

LTV = ARPU / Monthly Churn Rate

With margin:

LTV = (ARPU × Gross Margin) / Monthly Churn Rate

With discount rate (DCF approach):

LTV = (ARPU × Gross Margin) / (Monthly Churn Rate + Monthly Discount Rate)

Cohort-based LTV: Track actual revenue per cohort over time. More accurate but requires historical data.

CAC (Customer Acquisition Cost)

Definition: Cost to acquire one new customer.

Formula:

CAC = Total Sales & Marketing Spend / Number of New Customers Acquired

Include:

• Marketing spend (ads, content, events)
• Sales salaries and commissions
• Sales tools and infrastructure

Exclude:

• Customer success (post-acquisition)
• Product development

LTV:CAC Ratio

Definition: Return on customer acquisition investment.

Formula:

LTV:CAC = LTV / CAC

Benchmarks:

CAC Payback Period

Definition: Months to recover customer acquisition cost.

Formula:

CAC Payback = CAC / (ARPU × Gross Margin)

Benchmarks:

• < 12 months: Excellent
• 12-18 months: Good
• 18-24 months: Acceptable for enterprise
• > 24 months: Concerning

---

Pipeline Metrics

Win Rate

Definition: Percentage of opportunities that convert to closed-won.

Formula:

Win Rate = Closed Won Deals / Total Closed Deals × 100

Note: Denominator is closed deals only (won + lost), not all opportunities.

By stage:

Stage Win Rate = Deals reaching Closed Won from Stage X / Deals entering Stage X

Sales Velocity

Definition: Speed at which pipeline generates revenue.

Formula:

Sales Velocity = (# Opportunities × Win Rate × Average Deal Size) / Sales Cycle Length

Interpretation: Revenue generated per day/week/month from pipeline.

Pipeline Coverage

Definition: Ratio of pipeline to quota/target.

Formula:

Pipeline Coverage = Total Pipeline Value / Revenue Target

Benchmarks:

• 3x coverage: Standard target
• 4x coverage: Conservative/enterprise deals
• 2x coverage: Risky, may miss target

Average Contract Value (ACV)

Definition: Average annualized value of new contracts.

Formula:

ACV = Total Contract Value of New Deals / Number of New Deals

For multi-year deals: Annualize the value.

Bookings vs Revenue

Example: 3-year, $120K contract signed Jan 1

• Bookings (Jan): $120K
• Monthly revenue: $3,333 (recognized over 36 months)
• Billings: Depends on payment terms

---

Cohort Analysis

Retention Cohorts

Structure:

• Rows: Cohort (usually by signup month)
• Columns: Periods since signup (Month 0, Month 1, etc.)
• Values: Retention rate or revenue retained

Example format:

Cohort    | M0   | M1   | M2   | M3   | M6   | M12
----------|------|------|------|------|------|-----
Jan 2024  | 100% | 92%  | 87%  | 84%  | 78%  | 71%
Feb 2024  | 100% | 94%  | 89%  | 85%  | 80%  | -
Mar 2024  | 100% | 91%  | 86%  | 82%  | -    | -

Cohort LTV

Formula:

Cohort LTV = Sum of revenue from cohort over N periods / Customers in cohort at M0

Usage: More accurate than formula-based LTV when you have sufficient history.

Time-to-Value (TTV)

Definition: Time from signup to first value realization.

Measurement options:

• First login after onboarding
• First key action completed
• First positive outcome achieved

Why it matters: Shorter TTV correlates with better retention.

---

Common Calculation Gotchas

Churn Calculation Timing

Problem: When does a churned customer count?

• Cancellation date: When they say they're leaving
• Contract end date: When service actually ends
• Last payment date: When revenue stops

Best practice: Use contract end date for revenue churn, be consistent.

Handling Free Trials

Problem: Should trials be in MRR calculations?

Best practice:

• Exclude free trials from MRR
• Track trial-to-paid conversion separately
• Include only when customer converts to paid

Multi-year Contracts

Problem: How to handle 2-3 year deals?

For MRR/ARR: Normalize to monthly/annual value For bookings: Record full contract value at signing For churn: Track at contract end, not artificially earlier

Refunds and Credits

Problem: How do refunds affect metrics?

Best practice:

• Partial refunds: Reduce MRR proportionally
• Full refunds: Treat as churn (or never count as new)
• Credits: Don't affect MRR unless applied

PDF Handling Patterns

Patterns for extracting data from PDFs and creating PDF reports.

Adapted from [Anthropic's pdf skill](https://github.com/anthropics/skills/tree/main/skills/pdf)

---

Tool Selection

---

Extracting Data from PDFs

Basic Text Extraction

from pypdf import PdfReader

def extract_text(pdf_path):
    """Extract all text from a PDF."""
    reader = PdfReader(pdf_path)
    text = ""
    for page in reader.pages:
        text += page.extract_text() + "\n"
    return text

# Usage
text = extract_text("report.pdf")
print(f"Pages: {len(PdfReader('report.pdf').pages)}")

Text with Layout Preservation

import pdfplumber

def extract_text_with_layout(pdf_path):
    """Extract text preserving layout (columns, spacing)."""
    with pdfplumber.open(pdf_path) as pdf:
        text = ""
        for page in pdf.pages:
            text += page.extract_text() + "\n\n"
    return text

Extract Tables to DataFrame

import pdfplumber
import pandas as pd

def extract_tables(pdf_path):
    """Extract all tables from PDF as DataFrames."""
    tables = []

    with pdfplumber.open(pdf_path) as pdf:
        for i, page in enumerate(pdf.pages):
            page_tables = page.extract_tables()
            for j, table in enumerate(page_tables):
                if table and len(table) > 1:
                    # First row as header
                    df = pd.DataFrame(table[1:], columns=table[0])
                    df['_source_page'] = i + 1
                    df['_table_num'] = j + 1
                    tables.append(df)

    return tables

# Usage
tables = extract_tables("financial_report.pdf")
for i, df in enumerate(tables):
    print(f"Table {i+1}: {df.shape}")
    print(df.head())

Advanced Table Extraction

def extract_tables_advanced(pdf_path, table_settings=None):
    """Extract tables with custom settings for complex layouts."""
    default_settings = {
        "vertical_strategy": "lines",
        "horizontal_strategy": "lines",
        "intersection_tolerance": 3,
    }
    settings = table_settings or default_settings

    tables = []
    with pdfplumber.open(pdf_path) as pdf:
        for page in pdf.pages:
            # Find tables with custom settings
            page_tables = page.extract_tables(table_settings=settings)

            # Alternative: extract with explicit lines
            if not page_tables:
                # Try text-based extraction
                page_tables = page.extract_tables(table_settings={
                    "vertical_strategy": "text",
                    "horizontal_strategy": "text",
                })

            for table in page_tables:
                if table:
                    tables.append(table)

    return tables

Extract Specific Pages

from pypdf import PdfReader

def extract_page_range(pdf_path, start_page, end_page):
    """Extract text from specific page range (1-indexed)."""
    reader = PdfReader(pdf_path)
    text = ""
    for i in range(start_page - 1, min(end_page, len(reader.pages))):
        text += reader.pages[i].extract_text() + "\n"
    return text

---

Creating PDF Reports

Basic Report

from reportlab.lib.pagesizes import letter
from reportlab.pdfgen import canvas

def create_simple_report(filename, title, content):
    """Create a simple PDF report."""
    c = canvas.Canvas(filename, pagesize=letter)
    width, height = letter

    # Title
    c.setFont("Helvetica-Bold", 18)
    c.drawString(72, height - 72, title)

    # Content
    c.setFont("Helvetica", 12)
    y = height - 120
    for line in content.split('\n'):
        if y < 72:  # New page if near bottom
            c.showPage()
            y = height - 72
        c.drawString(72, y, line)
        y -= 15

    c.save()
    return filename

Professional Report with Sections

from reportlab.lib.pagesizes import letter
from reportlab.platypus import SimpleDocTemplate, Paragraph, Spacer, Table, TableStyle, PageBreak
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.lib import colors
from reportlab.lib.units import inch

def create_analysis_report(filename, title, sections, tables=None):
    """
    Create a professional analysis report.

    Args:
        filename: Output PDF path
        title: Report title
        sections: List of dicts with 'heading' and 'content'
        tables: Optional list of (headers, rows) tuples
    """
    doc = SimpleDocTemplate(filename, pagesize=letter,
                           leftMargin=72, rightMargin=72,
                           topMargin=72, bottomMargin=72)

    styles = getSampleStyleSheet()

    # Custom styles
    title_style = ParagraphStyle(
        'CustomTitle',
        parent=styles['Title'],
        fontSize=24,
        spaceAfter=30
    )

    heading_style = ParagraphStyle(
        'CustomHeading',
        parent=styles['Heading1'],
        fontSize=14,
        spaceBefore=20,
        spaceAfter=10
    )

    story = []

    # Title
    story.append(Paragraph(title, title_style))
    story.append(Spacer(1, 12))

    # Sections
    for section in sections:
        story.append(Paragraph(section['heading'], heading_style))
        story.append(Paragraph(section['content'], styles['Normal']))
        story.append(Spacer(1, 12))

    # Tables
    if tables:
        for header_row, data_rows in tables:
            table_data = [header_row] + data_rows
            t = Table(table_data)
            t.setStyle(TableStyle([
                ('BACKGROUND', (0, 0), (-1, 0), colors.grey),
                ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
                ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
                ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
                ('FONTSIZE', (0, 0), (-1, 0), 12),
                ('BOTTOMPADDING', (0, 0), (-1, 0), 12),
                ('BACKGROUND', (0, 1), (-1, -1), colors.beige),
                ('GRID', (0, 0), (-1, -1), 1, colors.black),
            ]))
            story.append(t)
            story.append(Spacer(1, 20))

    doc.build(story)
    return filename


# Usage
sections = [
    {
        'heading': 'Executive Summary',
        'content': 'Revenue grew 15% YoY driven by enterprise expansion...'
    },
    {
        'heading': 'Key Findings',
        'content': '1. NRR improved to 115%\n2. Churn decreased to 2.1%\n3. Pipeline coverage at 3.2x'
    }
]

tables = [
    (['Metric', 'Q1', 'Q2', 'Q3', 'Q4'],
     [['Revenue', '$1.2M', '$1.4M', '$1.5M', '$1.7M'],
      ['Customers', '120', '135', '148', '162']])
]

create_analysis_report('analysis.pdf', 'Q4 Revenue Analysis', sections, tables)

Add Charts to PDF

from reportlab.lib.pagesizes import letter
from reportlab.platypus import SimpleDocTemplate, Image, Paragraph, Spacer
from reportlab.lib.styles import getSampleStyleSheet
import matplotlib.pyplot as plt
import io

def create_report_with_charts(filename, title, charts_data):
    """Create PDF report with embedded matplotlib charts."""
    doc = SimpleDocTemplate(filename, pagesize=letter)
    styles = getSampleStyleSheet()
    story = []

    story.append(Paragraph(title, styles['Title']))
    story.append(Spacer(1, 20))

    for chart_title, fig in charts_data:
        # Add chart title
        story.append(Paragraph(chart_title, styles['Heading2']))

        # Convert matplotlib figure to image
        img_buffer = io.BytesIO()
        fig.savefig(img_buffer, format='png', dpi=150, bbox_inches='tight')
        img_buffer.seek(0)
        plt.close(fig)

        # Add to PDF
        img = Image(img_buffer, width=6*inch, height=4*inch)
        story.append(img)
        story.append(Spacer(1, 20))

    doc.build(story)
    return filename


# Usage
fig1, ax1 = plt.subplots(figsize=(8, 5))
ax1.plot([1, 2, 3, 4], [100, 120, 115, 140])
ax1.set_title('Revenue Trend')

charts = [('Revenue Over Time', fig1)]
create_report_with_charts('report_with_charts.pdf', 'Financial Report', charts)

---

PDF Manipulation

Merge PDFs

from pypdf import PdfWriter, PdfReader

def merge_pdfs(pdf_list, output_path):
    """Merge multiple PDFs into one."""
    writer = PdfWriter()

    for pdf_path in pdf_list:
        reader = PdfReader(pdf_path)
        for page in reader.pages:
            writer.add_page(page)

    with open(output_path, 'wb') as output:
        writer.write(output)

    return output_path

# Usage
merge_pdfs(['report1.pdf', 'report2.pdf', 'appendix.pdf'], 'combined.pdf')

Split PDF

from pypdf import PdfReader, PdfWriter

def split_pdf(pdf_path, output_dir):
    """Split PDF into individual pages."""
    reader = PdfReader(pdf_path)
    paths = []

    for i, page in enumerate(reader.pages):
        writer = PdfWriter()
        writer.add_page(page)
        output_path = f"{output_dir}/page_{i+1}.pdf"
        with open(output_path, 'wb') as output:
            writer.write(output)
        paths.append(output_path)

    return paths

def extract_pages(pdf_path, page_range, output_path):
    """Extract specific pages from PDF."""
    reader = PdfReader(pdf_path)
    writer = PdfWriter()

    for i in page_range:
        writer.add_page(reader.pages[i - 1])  # 1-indexed

    with open(output_path, 'wb') as output:
        writer.write(output)

    return output_path

# Usage
extract_pages('report.pdf', range(1, 6), 'first_5_pages.pdf')

Add Watermark

from pypdf import PdfReader, PdfWriter

def add_watermark(input_pdf, watermark_pdf, output_pdf):
    """Add watermark to all pages."""
    reader = PdfReader(input_pdf)
    watermark = PdfReader(watermark_pdf).pages[0]
    writer = PdfWriter()

    for page in reader.pages:
        page.merge_page(watermark)
        writer.add_page(page)

    with open(output_pdf, 'wb') as output:
        writer.write(output)

    return output_pdf

---

OCR for Scanned PDFs

# Requires: pip install pytesseract pdf2image
# Also requires Tesseract OCR installed on system

import pytesseract
from pdf2image import convert_from_path

def ocr_pdf(pdf_path, language='eng'):
    """Extract text from scanned PDF using OCR."""
    # Convert PDF pages to images
    images = convert_from_path(pdf_path)

    text = ""
    for i, image in enumerate(images):
        page_text = pytesseract.image_to_string(image, lang=language)
        text += f"--- Page {i+1} ---\n{page_text}\n\n"

    return text

def ocr_pdf_to_dataframe(pdf_path):
    """OCR PDF tables to DataFrame (best effort)."""
    import pandas as pd

    images = convert_from_path(pdf_path)
    all_data = []

    for image in images:
        # Get structured data
        data = pytesseract.image_to_data(image, output_type=pytesseract.Output.DATAFRAME)
        all_data.append(data)

    return pd.concat(all_data, ignore_index=True)

---

Command Line Tools

Quick Text Extraction

# Using pdftotext (poppler-utils)
pdftotext input.pdf output.txt

# Preserve layout
pdftotext -layout input.pdf output.txt

# Specific pages
pdftotext -f 1 -l 5 input.pdf output.txt

Merge/Split with qpdf

# Merge
qpdf --empty --pages file1.pdf file2.pdf -- merged.pdf

# Extract pages 1-5
qpdf input.pdf --pages . 1-5 -- pages1-5.pdf

# Split into chunks
qpdf input.pdf --split-pages=10 output_%d.pdf

Extract Images

# Using pdfimages (poppler-utils)
pdfimages -j input.pdf output_prefix
# Creates output_prefix-000.jpg, output_prefix-001.jpg, etc.

---

Data Extraction Workflow

For extracting data from PDF reports for analysis:

def extract_financial_data(pdf_path):
    """
    Extract financial data from PDF report.
    Returns dict with metadata and DataFrames.
    """
    import pdfplumber
    import pandas as pd
    from pypdf import PdfReader

    result = {
        'metadata': {},
        'text': '',
        'tables': []
    }

    # Get metadata
    reader = PdfReader(pdf_path)
    result['metadata'] = {
        'pages': len(reader.pages),
        'title': reader.metadata.title if reader.metadata else None,
        'author': reader.metadata.author if reader.metadata else None,
    }

    # Extract text and tables
    with pdfplumber.open(pdf_path) as pdf:
        for i, page in enumerate(pdf.pages):
            # Text
            result['text'] += page.extract_text() or ''

            # Tables
            tables = page.extract_tables()
            for j, table in enumerate(tables):
                if table and len(table) > 1:
                    df = pd.DataFrame(table[1:], columns=table[0])
                    df['_page'] = i + 1
                    result['tables'].append(df)

    return result


# Usage
data = extract_financial_data('quarterly_report.pdf')
print(f"Found {len(data['tables'])} tables")

# Combine all tables if similar structure
if data['tables']:
    combined = pd.concat(data['tables'], ignore_index=True)

---

Quick Reference

---

Decision Logging

# === PDF HANDLING LOG ===
# SOURCE: quarterly_report.pdf - 12 pages
# EXTRACTION: Used pdfplumber for tables, 4 tables found
# QUALITY: Tables on pages 3-6 extracted cleanly, page 8 required manual cleanup
# OUTPUT: Combined to financial_data.xlsx for analysis