Nonprofit Donor Retention and Cohort LTV SQL Brief

Role: Lead Nonprofit Analytics Engineer specializing in donor lifecycle modeling and fundraising data warehouse architecture.

Context

• Source CRM Data Schema: {{crm_source_schema}}
• Analytical Database Engine: {{reporting_database_engine}}
• Cohort Evaluation Window: {{retention_window_months}}
• Donor Giving Tier Boundaries: {{giving_tier_thresholds}}
• Lapse Threshold Definition: {{lapse_definition_days}}
• Evaluated Campaign Types: {{target_campaign_types}}

Task

Author a donor retention SQL architecture brief that creates scalable cohort models, evaluates giving trajectory over time, and isolates donor lapse indicators across {{target_campaign_types}} to guide nonprofit stewardship strategies.

Method

1. Inspect entity relationships within {{crm_source_schema}} to isolate first-gift acquisition dates per donor entity.
2. Construct acquisition cohort groups partitioned by calendar quarter and initial gift amount using {{giving_tier_thresholds}}.
3. Compute rolling retention intervals using date-difference expressions aligned with {{retention_window_months}}.
4. Apply advanced analytic window functions (DENSE_RANK(), LEAD(), LAG()) to calculate days elapsed between consecutive contributions.
5. Categorize donors as active, renewed, upgraded, or lapsed based on the parameter {{lapse_definition_days}}.
6. Aggregate cumulative lifetime value (LTV) across distinct cohorts to track revenue yield curves.
7. Isolate multi-year giving trends across distinct categories in {{target_campaign_types}} to identify channel decay.

Constraints

• MUST optimize queries to prevent Cartesian products when joining high-volume transactional tables.
• MUST NOT drop zero-dollar pledge records without explicit defensive filtering in CTE comments.
• All cohort definitions MUST rely on deterministic date math native to {{reporting_database_engine}}.
• Code must exclude recurring automated test records and internal staff donor accounts.
• Query performance must support partitioning by donor state or region without full table scans.

Output format

Structure the deliverable into four sequential sections:

1. Metric Definitions & Transformation Logic (max 200 words)
2. Staged Data Pipeline Blueprint (numbered sequence)
3. Production SQL Cohort Analysis Script (complete, commented code block)
4. Performance Optimization & Materialization Plan (max 250 words)

Self-review

• Confirm cohort window arithmetic aligns with {{retention_window_months}} boundaries.
• Verify that tier categorization correctly handles edge values defined in {{giving_tier_thresholds}}.
• Check that donor churn classification honors the {{lapse_definition_days}} parameter.