<!-- https://learnsql.cpetoday.com/analyze/nonprofit -->
# Nonprofit donors, grants and funds (nonprofit)

`nonprofit` is the database of Cascade Community Alliance, a fictional regional nonprofit with about $7 million a year in revenue and 42 employees. It runs four programs (Food Security, Youth Opportunity, Housing Stability and Community Health Outreach) and is funded by individual donors, foundation and government grants, events and program fees.

It covers what a nonprofit actually keeps: **donor management** (CRM), **fundraising** (campaigns, pledges, events), **grants and proposals**, **fund accounting** with a general ledger, **personnel** records and **volunteers**. There are 49 tables and roughly 411,000 rows, identical on SQL Server and MariaDB. The fiscal year runs **July to June**, from FY2022 (July 2021) to October 2026, which is the first four months of FY2027.

## Connect to it

- **SQL Server:** server `learnsql.cpetoday.com,1433`, database `nonprofit`, tables in the `dbo` schema. See [Connect Excel and Power BI](/connect).
- **In the browser:** the [database page](/databases/nonprofit) opens the Playground, the SQL editor and the schema explorer.
- **As-of date:** 2026-10-24, stored in `dataset_info`.

## How a nonprofit differs

Three ideas explain most of the design.

1. **Funds, not just accounts.** Money is tracked by *fund* as well as by account. A fund is either *without donor restrictions* (General Operating, a board-designated reserve) or *with donor restrictions* (a capital campaign, an endowment, program funds, and one fund per grant). Every ledger line has a fund. The same accounts show a different story when you filter by fund.
2. **Functional expenses.** Costs are classed as Program, Management and General, or Fundraising, through the cost center on each line. That is what feeds the statement of functional expenses.
3. **Restricted money has to be spent as promised.** Grants carry a budget by category, payments, and deliverables with due dates. Staff time is allocated across funds by percent of effort.

## Tables at a glance

| Area | Tables | What they hold |
|---|---|---|
| Organization | `fiscal_periods`, `programs`, `cost_centers`, `funds` | 64 monthly periods, four programs, eight cost centers with a functional class, 103 funds |
| Donors and CRM | `constituents`, `constituent_relationships`, `interests`, `constituent_interests`, `interactions`, `opportunities` | 20,887 people and organizations, spouse and employer links, interests, 6,400 interactions, and a major-gift pipeline |
| Giving | `campaigns`, `appeals`, `events`, `event_registrations`, `gift_batches`, `gifts`, `soft_credits`, `gift_acknowledgments`, `pledges`, `recurring_gifts` | 103,286 gifts from 17,237 donors, 238 pledges, 1,976 monthly donors, 24 events |
| Grants | `funders`, `proposals`, `grants`, `grant_budgets`, `grant_payments`, `grant_deliverables` | 44 funders, 166 proposals, 94 awarded grants with budgets, receipts and reports |
| People | `job_titles`, `employees`, `salary_history`, `pay_periods`, `payroll_records`, `timesheet_allocations` | 58 employees over time, pay history, semi-monthly payroll, and effort split by fund |
| Volunteers | `volunteers`, `volunteer_shifts` | 1,420 volunteers and 21,000 shifts |
| Accounting | `gl_accounts`, `gl_journal_entries`, `gl_journal_lines`, `budgets`, `vendors`, `bills` | A 44-account chart, about 104,000 balanced lines, annual budgets, 113 vendors and 13,900 bills |
| Sites and field work | `program_sites`, `mobile_market_routes`, `mobile_market_stops`, `mobile_market_visits` | Seven program sites, a mobile food market with 3 routes and 24 stops, and 6,300 weekly visits |
| Geography and fiscal | `geo_states`, `geo_places`, `geo_postal_codes`, `fiscal_calendar` | Reference geography and a July-June fiscal calendar with giving seasons |
| Meta | `dataset_info` | The as-of date |

## How the tables join

```
constituents ──< gifts >── funds                  gifts → campaigns / appeals / pledges / recurring_gifts / gift_batches
      │             └──< soft_credits >── constituents      (matching gifts, donor-advised funds, household)
      ├──< opportunities ──< interactions >── employees     (major-gift pipeline)
      └──< event_registrations >── events >── campaigns

funders ──< proposals >── programs        proposals ──1:1── grants >── funds
grants ──< grant_budgets, grant_payments, grant_deliverables

employees ──< payroll_records >── pay_periods
employees ──< timesheet_allocations >── funds         (percent of effort by pay period)
gl_journal_lines >── gl_journal_entries >── fiscal_periods
gl_journal_lines >── gl_accounts, funds, cost_centers
bills >── vendors, gl_accounts, funds, cost_centers, grants
```

## Rules built into the data

- **Ledger.** Every journal entry balances, and so does every fund on its own. Cash and payables sit in the General fund; other funds balance through account `1900` (due from or to other funds). Pledges are recognized when made (`1100`), and later gifts reduce the receivable. In-kind gifts post revenue and an equal expense.
- **Grants.** Non-reimbursable grants are recognized at award. Government grants are *reimbursable*: revenue is recognized as costs are incurred, plus indirect, and collected quarterly. The indirect share goes to the General fund.
- **Payroll.** Payroll posts every pay period, split across funds by `timesheet_allocations`. Grant-funded staff are charged to the grant fund until its personnel budget is used up.
- **Simplified net assets.** Each fund carries its own balance. There are no release-from-restriction entries.
- **Soft credits.** Matching gifts are credited to the employee, donor-advised fund gifts to the advisor, and household gifts to the spouse. Use `soft_credits` when you want to credit the person and `gifts` when you want the cash.
- **Fiscal year.** FY2025 is July 2024 to June 2025. `fiscal_periods` and `pay_periods.fiscal_year` carry it, and gifts need `CASE WHEN MONTH(gift_date) >= 7 THEN YEAR(gift_date) + 1 ELSE YEAR(gift_date) END`.

## Views

| View | Use |
|---|---|
| `vw_donor_summary` | Gift count, lifetime giving, first and last gift per donor |
| `vw_pledge_balances` | Paid, written-off and open balance per pledge |
| `vw_fund_balances` | Opening net assets, revenue, expenses and ending net assets by fund |
| `vw_grant_budget_vs_actual` | Budget against actual spending by grant and category |
| `vw_functional_expenses` | Expenses by fiscal year, function class, cost center and account |

## Questions to try

**What share of donors give again the next year?**

```sql
WITH gift_fy AS (
  SELECT constituent_id,
         CASE WHEN MONTH(gift_date) >= 7 THEN YEAR(gift_date) + 1 ELSE YEAR(gift_date) END AS fy
  FROM gifts
  WHERE gift_type NOT IN ('In-Kind', 'Matching Gift')
), donors AS (
  SELECT DISTINCT constituent_id, fy FROM gift_fy
)
SELECT a.fy AS from_fy, COUNT(*) AS donors,
       SUM(CASE WHEN b.constituent_id IS NOT NULL THEN 1 ELSE 0 END) AS retained,
       ROUND(SUM(CASE WHEN b.constituent_id IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS retention_pct
FROM donors a
LEFT JOIN donors b ON b.constituent_id = a.constituent_id AND b.fy = a.fy + 1
WHERE a.fy BETWEEN 2022 AND 2025
GROUP BY a.fy
ORDER BY a.fy;
```

Retention is about 54% in FY2022 and 64-65% in the later years.

**How did each FY2025 campaign do against its goal?**

```sql
SELECT c.campaign_name, c.goal_amount, SUM(g.amount) AS raised,
       ROUND(SUM(g.amount) * 100.0 / c.goal_amount, 1) AS pct_of_goal
FROM campaigns c
JOIN gifts g ON g.campaign_id = c.campaign_id
WHERE c.fiscal_year = 2025
GROUP BY c.campaign_id, c.campaign_name, c.goal_amount
ORDER BY raised DESC;
```

**What are the largest fund balances?**

```sql
SELECT fund_code, fund_name, net_asset_class, ending_net_assets
FROM vw_fund_balances
ORDER BY ending_net_assets DESC
OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;
```

**How much of each grant's personnel budget has been spent?**

```sql
SELECT grant_number, budgeted_amount, actual_amount
FROM vw_grant_budget_vs_actual
WHERE budget_category = 'Personnel'
ORDER BY budgeted_amount DESC
OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;
```

**What is the split between program, administration and fundraising?**

```sql
SELECT function_class, SUM(expense_amount) AS expenses,
       ROUND(SUM(expense_amount) * 100.0 / SUM(SUM(expense_amount)) OVER (), 1) AS pct
FROM vw_functional_expenses
WHERE fiscal_year = 2025
GROUP BY function_class
ORDER BY expenses DESC;
```

In FY2025 about 64% of expenses are program, 19% management and general, and 17% fundraising.

**Which kinds of funders say yes most often?**

```sql
SELECT f.funder_type, COUNT(*) AS decided,
       SUM(CASE WHEN p.status = 'Awarded' THEN 1 ELSE 0 END) AS won,
       ROUND(SUM(CASE WHEN p.status = 'Awarded' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS win_rate_pct
FROM proposals p
JOIN funders f ON f.funder_id = p.funder_id
WHERE p.status IN ('Awarded', 'Declined')
GROUP BY f.funder_type
ORDER BY decided DESC;
```

**What is in each major-gift officer's pipeline?**

```sql
SELECT CONCAT(e.first_name, ' ', e.last_name) AS officer, o.stage,
       COUNT(*) AS opportunities, SUM(o.ask_amount) AS asked
FROM opportunities o
JOIN employees e ON e.employee_id = o.officer_id
WHERE o.stage NOT LIKE 'Closed%'
GROUP BY e.employee_id, e.first_name, e.last_name, o.stage
ORDER BY officer, o.stage;
```

All of these run on SQL Server and MariaDB. More ideas: lapsed donors who gave $1,000 or more, cost to raise a dollar by appeal, overdue grant deliverables, personnel cost by fund from effort allocations, volunteer hours by program, and bills that are overdue.

## Excel and Power BI

- **Giving model.** `gifts` is the fact table, with `constituents`, `funds`, `campaigns` and a date table as dimensions. Add a fiscal year column to the date table (July start), and set up time intelligence with a fiscal year end of June 30.
- **Fund accounting model.** `gl_journal_lines` is the fact table, with `gl_accounts`, `funds`, `cost_centers` and `fiscal_periods`. Revenue is credit minus debit, and expense is debit minus credit.
- **Measures to write.** Total giving, Donors, Retention %, Average gift, Raised vs goal, Net assets by fund, Program expense ratio, Grant spend vs budget.

## Maps and fiscal years

**Mapping.** About 18,500 constituents have a full address with `latitude`, `longitude` and `geocode_quality`, and so do funders, vendors, program sites and mobile-market stops. The **mobile food market** has three weekly routes with eight stops each, and `mobile_market_visits` records the households served and the pounds distributed, which is ready for a map of reach over time. See [maps and fiscal calendars](/analyze/maps-and-fiscal).

**Fiscal columns.** `gifts`, `pledges`, `bills`, `grant_payments`, `interactions`, `opportunities`, `gl_journal_entries`, `volunteer_shifts` and `mobile_market_visits` carry `fiscal_year`, `fiscal_quarter` and `fiscal_period` (July is period 1), matching `fiscal_periods`. `fiscal_calendar` adds `giving_season`, holidays and Giving Tuesday, so you no longer need the CASE expression to derive the fiscal year of a gift.

**When do we raise the money?**

```sql
SELECT f.giving_season, COUNT(*) AS gifts, SUM(g.amount) AS raised
FROM gifts g
JOIN fiscal_calendar f ON f.calendar_date = g.gift_date
WHERE g.fiscal_year = 2025
GROUP BY f.giving_season
ORDER BY raised DESC;
```

**Giving by fiscal quarter:**

```sql
SELECT fiscal_year, fiscal_quarter, SUM(amount) AS raised
FROM gifts
WHERE fiscal_year BETWEEN 2024 AND 2026
GROUP BY fiscal_year, fiscal_quarter
ORDER BY fiscal_year, fiscal_quarter;
```

**Which states give the most?**

```sql
SELECT s.state_name, COUNT(DISTINCT g.constituent_id) AS donors, SUM(g.amount) AS raised
FROM gifts g
JOIN constituents c ON c.constituent_id = g.constituent_id
JOIN geo_states s ON s.state_code = c.state
WHERE g.fiscal_year = 2025
GROUP BY s.state_name
ORDER BY raised DESC
OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY;
```

**How many households did each mobile market route serve?**

```sql
SELECT r.route_name, v.fiscal_year, SUM(v.households_served) AS households, SUM(v.pounds_distributed) AS pounds
FROM mobile_market_visits v
JOIN mobile_market_stops s ON s.stop_id = v.stop_id
JOIN mobile_market_routes r ON r.route_id = s.route_id
WHERE v.fiscal_year IN (2025, 2026)
GROUP BY r.route_name, v.fiscal_year
ORDER BY r.route_name, v.fiscal_year;
```

**Where do volunteers work?**

```sql
SELECT p.site_name, p.city, COUNT(*) AS shifts, SUM(vs.hours) AS hours
FROM volunteer_shifts vs
JOIN program_sites p ON p.site_id = vs.site_id
WHERE vs.fiscal_year = 2025
GROUP BY p.site_name, p.city
ORDER BY hours DESC;
```

Funders were originally given a random state; each funder now uses the state of its city so its address is consistent.

## Realistic imperfections

Acknowledgments are sometimes late or missing, a few grant deliverables are overdue, some pledges are written off, some bills are open or overdue, and some constituents are deceased or marked do-not-contact. FY2027 is only four months old, and many grants are recognized when awarded, so its revenue looks high next to its expenses.
