Nonprofit Donor Cohort Retention and Decay Pipeline Builder

Role: Lead Nonprofit Analytics Architect with deep expertise in donor lifetime modeling and CRM warehousing.

Context

• Mission Sector: {{nonprofit_cause_sector}}
• Source Platform: {{primary_crm_platform}}
• Historical Analysis Range: {{historical_window_years}}
• Sustaining Gift Cutoff: {{recurring_gift_threshold}}
• Time Granularity: {{cohort_granularity}}
• Target SQL Engine: {{reporting_database_dialect}}

Task

Create a dual SQL and Python data pipeline script that transforms transactional giving history from {{primary_crm_platform}} into cohort-based donor retention matrices, churn indicators, and multi-year donor survival curves.

Method

1. Formulate a Common Table Expression (CTE) in {{reporting_database_dialect}} to aggregate individual transaction logs by unique donor ID and acquisition date.
2. Bucket each donor into an initial acquisition cohort based on {{cohort_granularity}} across {{historical_window_years}}.
3. Calculate activity flags for subsequent periods by checking giving events against {{recurring_gift_threshold}}.
4. Write a pivot query to compute active retention percentages per cohort index period.
5. Develop a Python wrapper script using sqlalchemy to execute the database query and extract the cohort matrix.
6. Compute average cohort decay rates, year-over-year retention velocity, and lapse probabilities using pandas.
7. Format the resulting retention matrix into an exportable summary table and log key attrition metrics.

Constraints

• MUST write dialect-specific SQL conforming precisely to {{reporting_database_dialect}} syntax standards.
• MUST NOT rely on proprietary third-party libraries outside of standard open-source Python analytics packages.
• Database queries MUST account for recurring vs. one-time gifts using {{recurring_gift_threshold}} logic.
• Calculations must handle dormant donor reactivation without artificially inflating new acquisition cohorts.

Output format

Deliver the solution in the following sequence:

1. SQL Query Specification: Formatted, documented SQL CTEs ready for database deployment.
2. Python Pipeline Script: End-to-end Python script containing query execution, matrix calculation, and cohort exports.
3. Operational Notes: Instructions on parameterizing cohort timeframes and interpreting calculated decay metrics.

Self-review

• Ensure SQL window functions correctly partition by donor identifier without duplicate counts.
• Confirm that Python transformations handle sparse matrices where older cohorts lack recent activity.
• Verify all 6 provided variables are explicitly referenced within the queries or pipeline code.