Donor Retention Lab

An original fundraising analytics application for exploring donor health, predicting repeat giving, and turning transaction history into a prioritized outreach list. The project demonstrates relational data modeling, SQL, ETL, Python data validation, predictive analytics, automated testing, and Streamlit reporting.

Live application: [https://analyticspy-fayyopqsycvhjzacg8y69r.streamlit.app/)

The repository includes deterministic synthetic data. Every donor and gift transaction is fictional, no real personally identifiable donor information is included, and the data can be regenerated locally for demonstration and analytics use.

What it includes

• Executive KPIs for repeat giving, recent revenue, and next-gift opportunity
• Historical holdout scoring with a random forest model
• Automatic weighted RFM fallback for smaller uploaded datasets
• Four action-oriented donor segments
• Monthly giving trends and acquisition-channel quality
• Quarterly retention cohort heatmap
• Campaign-ready donor export
• Flexible transaction upload with optional donor profiles
• Reproducible SQLite ETL layer with foreign-key and data-quality checks
• Compact ETL quality summary for the bundled dataset
• Fundraising SQL examples for donor, campaign, channel, and retention reporting

Architecture

Synthetic CSV → SQLite → SQL ETL / Validation → Python Analytics → Streamlit

• Synthetic CSV files provide deterministic, reproducible source records.
• SQLite provides relational storage, keys, constraints, and indexed access.
• SQL ETL / Validation checks identifiers, dates, amounts, duplicates, and donor references before loading, then supports relational queries and aggregations.
• Python and Pandas perform feature engineering, cohort analysis, and segmentation.
• Machine learning scores donor-retention likelihood using a historical holdout.
• Streamlit provides reporting, decision support, and campaign-ready exports.

The bundled dataset follows this database path. User-uploaded files continue through the existing in-memory Pandas validation and analytics workflow so uploads remain simple.

Data model

The schema simulates the type of constituent and transaction relationship commonly found in fundraising and advancement CRM systems. One donor can have many donation transactions, while each donation must reference one valid donor.

erDiagram
    DONORS ||--o{ DONATIONS : makes
    DONORS {
        TEXT donor_id PK
        TEXT join_date
        TEXT region
        TEXT acquisition_channel
        TEXT age_band
        INTEGER newsletter_opt_in
    }
    DONATIONS {
        TEXT donation_id PK
        TEXT donor_id FK
        TEXT donation_date
        REAL amount
        TEXT gift_type
        TEXT campaign
        TEXT payment_method
    }

Loading

A donor can have many donation transactions, while each donation belongs to one donor. SQLite dates are stored as ISO YYYY-MM-DD text so they remain portable and sortable.

Data dictionary

donors grain: one row per donor.

Field	SQLite type	Key	Description
donor_id	TEXT	Primary key	Synthetic constituent identifier
join_date	TEXT		Date the donor relationship began
region	TEXT		Broad geographic region
acquisition_channel	TEXT		Channel through which the donor was acquired
age_band	TEXT		Synthetic age-range category
newsletter_opt_in	INTEGER		Newsletter preference stored as 0 or 1

donations grain: one row per donation transaction.

Field	SQLite type	Key	Description
donation_id	TEXT	Primary key	Synthetic gift transaction identifier
donor_id	TEXT	Foreign key	References donors.donor_id
donation_date	TEXT		Date the gift was received
amount	REAL		Positive gift amount
gift_type	TEXT		One-time or recurring gift classification
campaign	TEXT		Fundraising campaign designation
payment_method	TEXT		Synthetic payment-channel category

Why SQLite?

SQLite was intentionally selected to provide a lightweight, reproducible relational database implementation while demonstrating SQL, ETL, relational modeling, constraints, data integrity, and analytical querying without requiring external infrastructure. Python's built-in sqlite3 module keeps the database layer small and easy to inspect; SQLite is not presented as an enterprise CRM platform.

Indexes

Three focused indexes support the common donor join and date- or campaign-based report filters: donations.donor_id, donations.donation_date, and donations.campaign. No performance benchmark or production-scale claim is implied.

Project structure

Donor_Retention/
├── app.py                 # Streamlit application and presentation flow
├── analytics.py           # Feature engineering, modeling, and cohorts
├── database.py            # SQLite validation, loading, and query helpers
├── visuals.py             # Reusable Altair charts
├── generate_data.py       # Deterministic synthetic-data generator
├── sql/
│   ├── schema.sql         # Tables, constraints, relationship, and indexes
│   └── queries.sql        # Advancement-oriented SQL reports
├── data/
│   ├── donors.csv         # Synthetic donor master data
│   └── donations.csv      # Synthetic gift transactions
├── tests/                 # Application, analytics, database, and chart tests
├── .gitignore             # Excludes local database and environment artifacts
├── requirements.txt
├── pyproject.toml
└── README.md

Run the project

Install the dependencies and launch Streamlit:

python -m venv .venv
source .venv/bin/activate
pip install -e .
python database.py
python -m unittest discover -s tests -v
streamlit run app.py

Or, with uv:

uv sync
uv run python database.py
uv run python -m unittest discover -s tests -v
uv run streamlit run app.py

Running database.py creates donor_retention.db, enables foreign-key enforcement, executes sql/schema.sql, validates the CSV records, loads only previously unseen primary keys, and reports processed, loaded, duplicate, invalid, and orphan counts. The database is generated locally and ignored by Git. This step is optional before launching Streamlit because the application creates a missing database automatically and reuses a valid existing database. Rerun python database.py after regenerating the CSV sources.

Example SQL Analysis

sql/queries.sql contains eight commented examples covering lifetime giving, gift frequency, average gifts by donor, recency, monthly totals, repeat donors, campaign performance, and acquisition-channel performance. Three representative examples are shown below; the full query library remains in the SQL file.

-- Lifetime giving by donor
SELECT d.donor_id, COALESCE(SUM(g.amount), 0) AS lifetime_giving
FROM donors AS d
LEFT JOIN donations AS g ON g.donor_id = d.donor_id
GROUP BY d.donor_id
ORDER BY lifetime_giving DESC;

-- Repeat donor identification
SELECT donor_id,
       CASE WHEN COUNT(*) > 1 THEN 'Repeat donor' ELSE 'One-time donor' END AS donor_status
FROM donations
GROUP BY donor_id;

-- Campaign performance
SELECT campaign, COUNT(DISTINCT donor_id) AS unique_donors,
       ROUND(SUM(amount), 2) AS total_raised
FROM donations
GROUP BY campaign
ORDER BY total_raised DESC;

Together, the examples demonstrate INNER JOIN, LEFT JOIN, WHERE, CTEs, grouping, ordering, SQLite date functions, aggregate functions, CASE WHEN, and COUNT DISTINCT.

Regenerate the synthetic data

python generate_data.py

The generator uses a fixed random seed and writes:

data/
├── donors.csv
└── donations.csv

Upload format

The transaction file requires:

Column	Description
donor_id	Donor identifier
donation_date	Gift date in a parseable format
amount	Positive numeric gift amount

Optional transaction columns are donation_id, gift_type, campaign, and payment_method.

A donor profile file is optional. If supplied, it requires donor_id; useful optional fields are join_date, region, acquisition_channel, age_band, and newsletter_opt_in.

How scoring works

The dashboard reserves the latest prediction window as a historical holdout. It builds features only from earlier gifts, labels donors who returned during the holdout, and reports ROC-AUC on a stratified validation sample. The trained model then scores the current donor file.

Features cover recency, frequency, lifetime and average giving, tenure, recurring-gift share, recent giving momentum, and newsletter engagement. Files without enough history or outcome variation use a documented weighted RFM benchmark instead.

Scores are decision support—not guarantees. Validate performance on your organization’s data and review outreach decisions for fairness before operational use.

Deploy on Streamlit Community Cloud

1. Push this project to a GitHub repository.
2. Sign in to Streamlit Community Cloud with GitHub.
3. Choose Create app and select the repository.
4. Set the branch to main and the entrypoint to app.py.
5. In advanced settings, select Python 3.12 and deploy.

The root-level requirements.txt contains the exact dependency versions verified by the automated test suite.