Start here
Lessons and reference
Look things up
Start here
SQL
Power Query
Data modeling
Power BI and DAX
Finance playbooks
Run queries
Build and check
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.
Nonprofit donors, grants and funds (nonprofit): Donor management, pledges, grants and proposals, fund accounting, payroll allocation and volunteers for a regional nonprofit.
Write each as a query in the SQL editor, or ask an AI assistant to walk you through 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.
Click a table to preview it in the Playground.
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.
Individual appeals sent within a campaign, with cost and how many people were asked.
| Column | Type | Key | Meaning | Values 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 from vendors that the nonprofit owes.
| Column | Type | Key | Meaning | Values 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 |
Fundraising campaigns, with goals.
| Column | Type | Key | Meaning | Values 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 |
People and organizations that support the nonprofit: donors, members, volunteers and prospects.
| Column | Type | Key | Meaning | Values 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 |
Staff, with job title, cost center and pay.
| Column | Type | Key | Meaning | Values 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 |
Who registered for each event.
| Column | Type | Key | Meaning | Values 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 |
Fundraising events such as galas and auctions.
| Column | Type | Key | Meaning | Values 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 that gifts and grants are restricted to, with the net asset class that reports them.
| Column | Type | Key | Meaning | Values 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 received: one row per gift, with the donor, fund, campaign and whether it came from a pledge or recurring plan.
| Column | Type | Key | Meaning | Values 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 |
The chart of accounts.
| Column | Type | Key | Meaning | Values 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 |
Debit and credit lines of general ledger entries, tagged by fund and cost center.
| Column | Type | Key | Meaning | Values 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. |
Money the funder pays on a grant.
| Column | Type | Key | Meaning | Values 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 awarded to the nonprofit by funders.
| Column | Type | Key | Meaning | Values 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 |
Contact history: calls, visits and emails with constituents.
| Column | Type | Key | Meaning | Values 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 |
Solicitation pipeline: asks made to donors, with stage and expected amount.
| Column | Type | Key | Meaning | Values 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 |
Promises to give over time, with installments and any write-off.
| Column | Type | Key | Meaning | Values 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 |