Downloads

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

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

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

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.