Nonprofit donors, grants and funds (nonprofit)
Donor management, pledges, grants and proposals, fund accounting, payroll allocation and volunteers for a regional nonprofit.
Last updated
View as Markdown For AI agents All guides as text
On this page
You'll be able to
- Describe how donor, pledge, grant and fund data link together.
- Answer donor, pledge and lapsed-donor questions in SQL.
- Build fund accounting and payroll allocation summaries.
- Track volunteers and grant deliverables.
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, databasenonprofit, tables in thedboschema. See Connect Excel and Power BI. - In the browser: the database page 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.
- 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.
- 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.
- 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_creditswhen you want to credit the person andgiftswhen you want the cash. - Fiscal year. FY2025 is July 2024 to June 2025.
fiscal_periodsandpay_periods.fiscal_yearcarry it, and gifts needCASE 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?
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?
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?
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?
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?
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?
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?
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.
giftsis the fact table, withconstituents,funds,campaignsand 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_linesis the fact table, withgl_accounts,funds,cost_centersandfiscal_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.
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?
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:
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?
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?
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?
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.
Try it on the data
Open the nonprofit database page, browse it in the Playground, write your own queries in the SQL editor, see how the tables connect in the schema explorer, or ask an AI assistant.