Downloads
Donors, grants and fund accounting

Nonprofit

A regional nonprofit: donors, gifts, pledges, campaigns and events, grants and proposals, fund accounting with a general ledger, payroll by fund, volunteers, program sites and a mobile food market. Donor map and a July-June fiscal calendar.

nonprofitSQL ServerMariaDB 49 tables
giftsconstituentsfundsgrantsgl_journal_lines

Read the guide

Nonprofit donors, grants and funds (nonprofit): Donor management, pledges, grants and proposals, fund accounting, payroll allocation and volunteers for a regional nonprofit.

Good for

Donor retention and campaignsGrant budgets and reportingFund accounting and functional expensesMapping donors and the mobile market

Questions to try

  • What share of donors give again next year?
  • When do we raise the money?
  • What are the largest fund balances?
  • How many households did each route serve?

Write each as a query in the SQL editor, or ask an AI assistant to walk you through it.

Connect to it

Server learnsql.cpetoday.com,1433, database nonprofit, in Excel or Power BI (Import mode) or DBeaver. These tables are in the dbo schema, so once you have picked the database you can use plain names such as appeals.

Download the Excel connection (.odc)

How to use it: download the file and open it. Excel opens it as a connection to nonprofit and asks for the SQL Server username and password from your CPE Today course. The file holds no password. The first table is selected by default, and you can pick others from the Data window. Setup details for other tools are on Connect.

Tables (49)

Click a table to preview it in the Playground.

Data dictionary

What each column means, its type and keys, and for codes and statuses the values seen in this copy. 16 of 49 tables are described so far. Read it as Markdown.

Click a table in the diagram above to jump to it.

appeals one row per appeal 7 columns

Individual appeals sent within a campaign, with cost and how many people were asked.

ColumnTypeKeyMeaningValues seen
appeal_id int PK Unique appeal ID.
campaign_id int FK to campaigns Campaign (campaigns).
appeal_name varchar(100) Appeal name.
channel varchar(20) How it was sent (mail, email and so on). Email 30 Mail 24 Social 6
appeal_date date Date sent.
cost decimal Cost of the appeal.
solicited_count int People asked.
bills one row per bill 17 columns

Bills from vendors that the nonprofit owes.

ColumnTypeKeyMeaningValues seen
bill_id int PK Unique bill ID.
vendor_id int FK to vendors Vendor (vendors).
bill_number varchar(30) Vendor's bill number.
bill_date date Bill date.
due_date date Due date.
paid_date date Date paid, if paid.
amount decimal Bill amount.
gl_account_id int FK to gl_accounts Not described yet
fund_id int FK to funds Fund charged (funds).
cost_center_id int FK to cost_centers Not described yet
grant_id int FK to grants Grant the cost is charged to, if any (grants).
event_id int FK to events Not described yet
status varchar(20) Bill status (see the values). Paid 13613 Open 226 Overdue 32
description varchar(200) Not described yet
fiscal_year smallint Not described yet
fiscal_quarter tinyint Not described yet
fiscal_period tinyint Not described yet
campaigns one row per campaign 8 columns

Fundraising campaigns, with goals.

ColumnTypeKeyMeaningValues seen
campaign_id int PK Unique campaign ID.
campaign_name varchar(100) Campaign name.
campaign_type varchar(30) Type of campaign. Event 18 Annual Appeal 12 Corporate 6 Major Gifts 6 Online 6 Program Drive 6 Sustainer 6 Year-End 6 Capital 1
fiscal_year smallint Fiscal year. 2023 12 2022 11 2024 11 2025 11 2026 11 2027 11
start_date date Start date.
end_date date End date.
goal_amount decimal Fundraising goal.
default_fund_id int FK to funds Not described yet
constituents one row per constituent 26 columns

People and organizations that support the nonprofit: donors, members, volunteers and prospects.

ColumnTypeKeyMeaningValues seen
constituent_id int PK Unique constituent ID.
constituent_number varchar(12) Number on the donor record.
constituent_type varchar(20) Individual or organization (see the values). Individual 20039 Organization 848
first_name varchar(40) First name.
last_name varchar(40) Last name.
organization_name varchar(150) Organization name, for organizations.
organization_type varchar(30) Not described yet
email varchar(120) Not described yet
phone varchar(20) Not described yet
address_line1 varchar(100) Not described yet
city varchar(60) Not described yet
state char(2) Not described yet
postal_code varchar(10) Not described yet
status varchar(20) Record status (see the values). Active 20500 Do Not Contact 258 Deceased 129
email_opt_in bit 1 if the constituent accepts email. True 15538 False 5349
mail_opt_in bit 1 if the constituent accepts mail. True 15514 False 5373
created_date date Date the record was created.
source varchar(40) How the constituent was added.
giving_capacity char(1) Estimated capacity to give. E 12964 (blank) 4207 D 2827 C 614 B 206 A 69
assigned_officer_id int FK to employees Gift officer responsible (employees). (blank) 20124 26 395 23 368
county varchar(60) Not described yet
country char(2) Not described yet
latitude decimal Not described yet
longitude decimal Not described yet
geocode_quality varchar(20) Not described yet
full_address varchar(220) Not described yet
employees one row per employee 17 columns

Staff, with job title, cost center and pay.

ColumnTypeKeyMeaningValues seen
employee_id int PK Unique employee ID.
employee_number varchar(10) Not described yet
first_name varchar(40) First name.
last_name varchar(40) Last name.
email varchar(100) Not described yet
job_title_id int FK to job_titles Job title (job_titles).
cost_center_id int FK to cost_centers Cost center (cost_centers). 1 14 2 12 7 9 3 6 4 5 5 5 6 5 8 2
manager_id int FK to employees Not described yet
work_location varchar(40) Not described yet
employment_type varchar(20) Full-time, part-time or other (see the values). Full-time 51 Part-time 7
fte decimal Full-time equivalent share. 1.0 48 0.75 4 0.5 3 0.8 3
pay_type varchar(10) Salaried or hourly (see the values). Salary 42 Hourly 16
current_pay decimal Current pay.
hire_date date Start date.
termination_date date Leave date, if any.
termination_reason varchar(40) Not described yet
work_site_id int FK to program_sites Not described yet
event_registrations one row per registration 8 columns

Who registered for each event.

ColumnTypeKeyMeaningValues seen
registration_id int PK Unique registration ID.
event_id int FK to events Event (events).
constituent_id int FK to constituents Not described yet
ticket_type varchar(30) Not described yet
ticket_count smallint Not described yet
amount_paid decimal Not described yet
attended bit Not described yet
gift_id int FK to gifts Not described yet
events one row per event 15 columns

Fundraising events such as galas and auctions.

ColumnTypeKeyMeaningValues seen
event_id int PK Unique event ID.
campaign_id int FK to campaigns Campaign (campaigns).
event_name varchar(100) Event name.
event_type varchar(30) Kind of event. Dinner 6 Donor Reception 6 Fun Run 6 Gala 6
event_date date Date of the event.
venue varchar(100) Venue. Alliance Community Hub 6 Lakeside Conference Center 6 Marymoor Park 6 Riverside Grange Hall 6
goal_amount decimal Revenue goal. 0.0 6 90000.0 6 120000.0 6 400000.0 6
ticket_price decimal Ticket price. 0.0 6 35.0 6 75.0 6 175.0 6
fmv_per_ticket decimal Not described yet
venue_address varchar(100) Not described yet
venue_city varchar(60) Not described yet
venue_state char(2) Not described yet
venue_postal_code varchar(10) Not described yet
venue_latitude decimal Not described yet
venue_longitude decimal Not described yet
funds one row per fund 8 columns

Funds that gifts and grants are restricted to, with the net asset class that reports them.

ColumnTypeKeyMeaningValues seen
fund_id int PK Unique fund ID.
fund_code varchar(12) Fund code.
fund_name varchar(120) Fund name.
net_asset_class varchar(40) Reporting class: with donor restrictions or without (see the values). With Donor Restrictions 101 Without Donor Restrictions 2
restriction_type varchar(30) Kind of donor restriction (see the values). Purpose 100 (blank) 1 Board Designated 1 Perpetual 1
program_id int FK to programs Program it supports (programs). 3 34 1 32 2 27 4 6 (blank) 4
is_active bit 1 while the fund accepts gifts. False 73 True 30
opened_date date Not described yet
gifts one row per gift 19 columns

Gifts received: one row per gift, with the donor, fund, campaign and whether it came from a pledge or recurring plan.

ColumnTypeKeyMeaningValues seen
gift_id int PK Unique gift ID.
constituent_id int FK to constituents Donor (constituents).
gift_date date Date received.
amount decimal Gift amount.
gift_type varchar(20) Kind of gift (see the values). One-time 48631 Recurring 44494 Event Ticket 6682 Matching Gift 1368 Pledge Payment 1014 In-Kind 719 DAF Grant 358 Stock 20
payment_method varchar(20) How it was paid (see the values). Credit Card 55857 Check 25109 ACH 19482 Cash 1657 In-Kind 719 Wire 442 Stock Transfer 20
fund_id int FK to funds Fund the gift supports (funds). 1 98824 5 1967 9 646 8 599 4 453 6 371 7 352 3 74
campaign_id int FK to campaigns Campaign credited (campaigns).
appeal_id int FK to appeals Appeal that prompted the gift (appeals).
pledge_id int FK to pledges Pledge the gift pays, if any (pledges).
recurring_gift_id int FK to recurring_gifts Recurring plan, if any (recurring_gifts).
batch_id int FK to gift_batches Gift batch it was entered in (gift_batches).
benefit_value decimal Value of any benefit the donor received, such as event tickets.
tribute_type varchar(20) Not described yet
tribute_name varchar(100) Not described yet
is_anonymous bit 1 if the donor asked not to be named. False 102837 True 449
fiscal_year smallint Fiscal year of the gift date. 2022 19945 2025 18679 2026 18255 2024 18131 2023 17782 2027 10494
fiscal_quarter tinyint Not described yet
fiscal_period tinyint Not described yet
gl_accounts one row per account 6 columns

The chart of accounts.

ColumnTypeKeyMeaningValues seen
gl_account_id int PK Unique account ID.
account_number varchar(10) Account number.
account_name varchar(80) Account name.
account_type varchar(20) Account type (see the values). Expense 21 Revenue 13 Asset 7 Net Assets 2 Liability 1
parent_account_id int FK to gl_accounts Not described yet
grant_budget_category varchar(40) Budget category used for grant reporting. (blank) 28 Other 7 Personnel 3 Contractual 2 Supplies 2 Direct Client Assistance 1 Travel 1
gl_journal_lines one row per journal line 7 columns

Debit and credit lines of general ledger entries, tagged by fund and cost center.

ColumnTypeKeyMeaningValues seen
journal_line_id int PK Unique line ID.
journal_entry_id int FK to gl_journal_entries Entry (gl_journal_entries).
gl_account_id int FK to gl_accounts Account (gl_accounts).
fund_id int FK to funds Fund (funds).
cost_center_id int FK to cost_centers Cost center (cost_centers). (blank) 79545 3 6941 1 6274 2 4632 4 2214 7 1342 5 1186 8 986 6 688
debit decimal Debit amount.
credit decimal Credit amount.
grant_payments one row per payment 9 columns

Money the funder pays on a grant.

ColumnTypeKeyMeaningValues seen
grant_payment_id int PK Unique payment ID.
grant_id int FK to grants Grant (grants).
payment_date date Date received.
amount decimal Amount.
payment_type varchar(20) Kind of payment (see the values). Reimbursement 159 Installment 105
reference_number varchar(30) Not described yet
fiscal_year smallint Not described yet
fiscal_quarter tinyint Not described yet
fiscal_period tinyint Not described yet
grants one row per grant 13 columns

Grants awarded to the nonprofit by funders.

ColumnTypeKeyMeaningValues seen
grant_id int PK Unique grant ID.
proposal_id int FK to proposals Proposal that led to it (proposals).
funder_id int FK to funders Funder (funders).
fund_id int FK to funds Fund (funds).
program_id int FK to programs Not described yet
grant_number varchar(30) Grant number.
grant_title varchar(150) Title.
award_amount decimal Amount awarded.
start_date date Start date.
end_date date End date.
is_reimbursable bit 1 if the funder pays costs after they are spent. False 58 True 36
indirect_rate decimal Indirect cost rate allowed. 10.0 78 15.0 5 0.0 4 5.0 4 12.0 3
status varchar(20) Grant status (see the values). Closed 68 Active 20 Closing Out 5 Pending Start 1
interactions one row per interaction 12 columns

Contact history: calls, visits and emails with constituents.

ColumnTypeKeyMeaningValues seen
interaction_id int PK Unique interaction ID.
constituent_id int FK to constituents Constituent (constituents).
employee_id int FK to employees Not described yet
opportunity_id int FK to opportunities Not described yet
interaction_date date Not described yet
interaction_type varchar(30) Not described yet
subject varchar(150) Not described yet
outcome varchar(40) Not described yet
follow_up_date date Not described yet
fiscal_year smallint Not described yet
fiscal_quarter tinyint Not described yet
fiscal_period tinyint Not described yet
opportunities one row per opportunity 15 columns

Solicitation pipeline: asks made to donors, with stage and expected amount.

ColumnTypeKeyMeaningValues seen
opportunity_id int PK Unique opportunity ID.
constituent_id int FK to constituents Prospect (constituents).
officer_id int FK to employees Gift officer (employees). 26 290 23 270
campaign_id int FK to campaigns Campaign (campaigns). 67 198 50 102 39 71 28 58 61 53 17 46 6 32
fund_id int FK to funds Fund (funds). 1 348 4 198 7 5 3 2 5 2 6 2 9 2 8 1
stage varchar(30) Pipeline stage (see the values). Closed Lost 237 Closed Won 87 Cultivation 70 Qualified 50 Solicitation 49 Verbal Commitment 41 Identified 26
ask_amount decimal Amount asked for.
expected_amount decimal Amount expected.
created_date date Not described yet
expected_close_date date Expected close date.
closed_date date Date closed.
gift_id int FK to gifts Gift that resulted (gifts).
fiscal_year smallint Not described yet
fiscal_quarter tinyint Not described yet
fiscal_period tinyint Not described yet
pledges one row per pledge 15 columns

Promises to give over time, with installments and any write-off.

ColumnTypeKeyMeaningValues seen
pledge_id int PK Unique pledge ID.
constituent_id int FK to constituents Donor (constituents).
campaign_id int FK to campaigns Campaign (campaigns). 67 75 45 25 56 25 39 24 50 22 34 18 12 14 23 14 28 8 17 7 1 6
fund_id int FK to funds Fund (funds). 1 163 4 75
pledge_date date Date pledged.
total_amount decimal Total promised.
installment_count smallint Number of payments. 12 102 3 49 5 47 4 40
frequency varchar(20) How often payments are due (see the values). Annual 136 Monthly 102
first_due_date date Not described yet
status varchar(20) Pledge status (see the values). Active 158 Fulfilled 51 Written Off 29
written_off_amount decimal Amount written off as uncollectible.
written_off_date date Not described yet
fiscal_year smallint Not described yet
fiscal_quarter tinyint Not described yet
fiscal_period tinyint Not described yet
33 more tables (no notes yet)

← All databases