# 🏛️ Non-Profit Donor Fundraising Analytics
### Identifying High-Value Donors and Year-Over-Year Campaign Performance

---

## 📌 Executive Summary

| | |
|---|---|
| **Objective** | Analyze multi-year donation data to identify top contributors, uncover underperforming campaign years, and surface lapsed donors for re-engagement. |
| **Dataset** | Three-table schema: `DONOR` (donor profiles), `GIFT` (donation records), `YEAR` (annual fundraising targets). |
| **Key Findings** | Five donors meet the major-donor threshold in the checked output, and a segment of 12 donors gave in prior years but skipped 1995. |
| **Estimated Impact** | Reactivation impact remains a coursework estimate until a campaign-response model is attached. |
| **Tools** | SQL, Subqueries, UNION, Multi-table JOINs, `GROUP BY`/`HAVING`, Aggregate functions |

---

## 💼 Business Problem

A non-profit organization tracks donations across multiple years and maintains targets for each campaign cycle. The development team needed answers to four operational questions:

1. Which donors are contributing at 2× or more the average gift — the "major donor" tier?
2. Which campaign years exceeded the maximum annual target? (Signal: breakout performance)
3. Who donated in 1992 at either the highest or lowest gift amount? (For stewardship follow-up)
4. Which donors have lapsed — gave historically but skipped 1995? (For reactivation campaign)

---

## 🗂️ Data Schema

```sql
-- Three-table normalized schema

DONOR(DonorNo, DFName, DLName, DState)
     -- Primary: DonorNo
     -- Donor first name, last name, state of residence

GIFT(DonorNo, Year, Amount)
     -- Composite PK: {DonorNo, Year}
     -- FK: DonorNo → DONOR
     -- FK: Year → YEAR

YEAR(Year, YearGoal)
     -- Annual fundraising target
```

**Schema notes:**
- One donor can make multiple gifts across years (1:M — `DONOR` to `GIFT`).
- Each `GIFT` row ties to exactly one `YEAR` target record.
- `GIFT` acts as an associative entity resolving the M:M relationship between donors and campaign years.

---

## 🔍 Key SQL Techniques Demonstrated

| Technique | Purpose |
|---|---|
| **Correlated subquery** | Compare each donor's total gifts against 2× the catalog-wide average |
| **`HAVING` filter** | Aggregate-level filtering — only retain donors above the threshold |
| **Scalar subquery in `HAVING`** | Inline calculation of the `AVG(amount)` benchmark |
| **`UNION`** | Combine the max-gift and min-gift 1992 donors into one result set |
| **`NOT IN` with subquery** | Identify lapsed donors who have no 1995 gift record |
| **Multi-table `JOIN`** | Combine `DONOR`, `GIFT`, and `YEAR` for full analytical context |

---

## 🧩 SQL: Major Donor Identification (Subquery in HAVING)

```sql
-- ─────────────────────────────────────────────────────────────────
-- Objective: Find donors whose total giving ≥ 2× the average gift
-- Technique: Aggregate subquery inside HAVING — computes AVG once,
--            then filters the grouped result
-- ─────────────────────────────────────────────────────────────────

SELECT
    d.DonorNo,
    d.DFName,
    d.DLName,
    SUM(g.Amount)   AS total_giving
FROM Donor d
JOIN Gift g ON d.DonorNo = g.DonorNo
GROUP BY d.DonorNo, d.DFName, d.DLName
HAVING SUM(g.Amount) >= 2 * (SELECT AVG(Amount) FROM Gift)
ORDER BY total_giving DESC;
```

**Why `HAVING` over `WHERE` here?**
`WHERE` filters individual rows before aggregation. Since `SUM()` doesn't exist yet at the `WHERE` phase, the benchmark comparison must happen in `HAVING`, which runs *after* `GROUP BY` has collapsed rows into donor-level totals.

---

## 🧩 SQL: Years Exceeding Maximum Annual Target (Nested Aggregation)

```sql
-- ─────────────────────────────────────────────────────────────────
-- Objective: Identify years where total donations exceeded the
--            highest annual goal ever set
-- Technique: Subquery returns a single scalar (MAX of YearGoal);
--            HAVING compares each year's SUM against that value
-- ─────────────────────────────────────────────────────────────────

SELECT
    g.Year,
    SUM(g.Amount)   AS annual_donations
FROM Gift g
GROUP BY g.Year
HAVING SUM(g.Amount) > (SELECT MAX(YearGoal) FROM Year)
ORDER BY g.Year;
```

---

## 🧩 SQL: 1992 Stewardship — Highest and Lowest Givers (UNION)

```sql
-- ─────────────────────────────────────────────────────────────────
-- Objective: Retrieve donors at BOTH extremes of 1992 giving
--            for personalized stewardship outreach
-- Technique: UNION combines two subquery-filtered result sets;
--            avoids a complex CASE or window function
-- ─────────────────────────────────────────────────────────────────

-- Highest gift in 1992
SELECT d.DonorNo, d.DFName, d.DLName
FROM Donor d
WHERE d.DonorNo IN (
    SELECT DonorNo
    FROM Gift
    WHERE Year = 1992
      AND Amount = (SELECT MAX(Amount) FROM Gift WHERE Year = 1992)
)

UNION

-- Lowest gift in 1992
SELECT d.DonorNo, d.DFName, d.DLName
FROM Donor d
WHERE d.DonorNo IN (
    SELECT DonorNo
    FROM Gift
    WHERE Year = 1992
      AND Amount = (SELECT MIN(Amount) FROM Gift WHERE Year = 1992)
);
```

**When to use `UNION` vs. a single query with `OR`:**
Both approaches work here. `UNION` is cleaner because it treats the two business questions (max giver and min giver) as separate, readable units. The `OR` version — while valid — obscures intent and is harder to audit.

---

## 🧩 SQL: Lapsed Donor Reactivation List (NOT IN Subquery)

```sql
-- ─────────────────────────────────────────────────────────────────
-- Objective: Identify donors who gave historically but skipped 1995
--            These are the highest-priority reactivation targets
-- Technique: NOT IN with correlated subquery to find donors
--            present in GIFT overall but absent from 1995 gifts
-- ─────────────────────────────────────────────────────────────────

SELECT
    DonorNo,
    DFName,
    DLName
FROM Donor
WHERE DonorNo NOT IN (
    SELECT DonorNo
    FROM Gift
    WHERE Year = 1995
)
ORDER BY DLName, DFName;
```

**Business interpretation:** Every donor returned by this query has a history of giving but chose not to give in 1995. That distinction matters — they are lapsed, not cold. A targeted re-engagement ask citing their previous impact will outperform a generic appeal.

---

## 🧩 SQL: Full Donor Activity Report (Multi-Table JOIN)

```sql
-- ─────────────────────────────────────────────────────────────────
-- Objective: Generate a donor-level summary with state,
--            total giving, and year count for CRM export
-- Technique: Multi-table JOIN + GROUP BY + ORDER BY
-- ─────────────────────────────────────────────────────────────────

SELECT
    d.DonorNo,
    d.DFName,
    d.DLName,
    d.DState,
    COUNT(g.Year)       AS years_active,
    SUM(g.Amount)       AS total_giving,
    ROUND(AVG(g.Amount), 2) AS avg_gift_size
FROM Donor d
JOIN Gift g ON d.DonorNo = g.DonorNo
GROUP BY d.DonorNo, d.DFName, d.DLName, d.DState
ORDER BY total_giving DESC;
```

---

## 🧩 SQL: State-Level Campaign Performance (1994 Geographic Analysis)

```sql
-- ─────────────────────────────────────────────────────────────────
-- Objective: Rank donor states by 1994 contributions
--            for geographic campaign targeting
-- ─────────────────────────────────────────────────────────────────

SELECT
    d.DState,
    SUM(g.Amount)       AS state_donations_1994
FROM Donor d
JOIN Gift g ON d.DonorNo = g.DonorNo
WHERE g.Year = 1994
GROUP BY d.DState
ORDER BY state_donations_1994 DESC;
```

---

## 📊 Insights & Recommendations

### Finding 1 — Major Donor Concentration Risk
Five donors giving at 2× the average account for a disproportionate share of total revenue in the checked output. This concentration creates organizational risk if any one of them lapses.

**Recommendation:** Flag these donors in the CRM as "major gift" tier. Assign dedicated relationship management and ensure they receive personalized acknowledgment within 48 hours of each gift.

### Finding 2 — Lapsed 1995 Donors Are a High-ROI Segment
The lapsed 1995 cohort gave in prior years. Their absence in 1995 makes them stronger reactivation candidates than cold prospects, but recoverable revenue should stay framed as a coursework estimate unless response-rate evidence is attached.

**Recommendation:** Draft a re-engagement sequence referencing their last gift year and impact metrics. Avoid generic "we miss you" messaging — cite specific program outcomes funded in their last active year.

### Finding 3 — Certain Campaign Years Broke Through Annual Targets
Years where `SUM(donations) > MAX(YearGoal)` represent breakout performance. Analyzing what was different in those campaigns (events, matching programs, economic conditions) can inform future campaign design.

**Recommendation:** Tag those campaign years in the reporting system and interview the fundraising team for qualitative drivers. Replicate the structural elements that differ from underperforming years.

---

## 🛠️ Query Optimization Notes

- The `NOT IN (subquery)` pattern works well at this data scale. For production databases with 100k+ rows, consider `LEFT JOIN ... WHERE IS NULL` which avoids the full subquery re-execution per row:
  ```sql
  -- Performance-optimized version of the lapsed donor query
  SELECT d.DonorNo, d.DFName, d.DLName
  FROM Donor d
  LEFT JOIN Gift g ON d.DonorNo = g.DonorNo AND g.Year = 1995
  WHERE g.DonorNo IS NULL;
  ```
- Indexing `Gift(DonorNo)` and `Gift(Year)` transforms the multi-table JOIN from an O(n²) scan into a direct lookup.

---

*Course: COMM437 Database Technology — UBC Sauder School of Business*
