Donor Retention Deep Dive

Uncover a retention crisis hiding behind record-high fundraising revenue.

Introduction

30 Second Summary

Every nonprofit celebrates when annual donations hit a new record. But rising revenue can hide a dangerous trend: a shrinking donor base that puts the whole organization at risk.

In this project, you will analyze donor data for a fictional nonprofit using SQL queries and Power BI visualizations. You will uncover a retention crisis hiding behind record-high revenue and deliver an executive brief with strategic recommendations.

What You'll Build

Imagine handing a VP of Development a dashboard and a one-page brief that prove their record-breaking revenue year is hiding a donor retention crisis.

By the end of this project, you'll have:

  • A set of SQL queries in DB Browser for SQLite that diagnose a nonprofit's donor file, calculating retention rates by cohort and channel to reveal a crisis hiding behind record-high revenue.
  • A Power BI dashboard with four visuals that tell the story from "revenue is at an all-time high" to "the donor base is shrinking fast."
  • A one-page executive brief that answers "are we healthy?" with specific numbers, cohort breakdowns, and three actionable recommendations for leadership.
  • Secret Mission: A donor concentration risk analysis that calculates what percentage of revenue comes from the top 10% of donors.

Are there any prerequisites?

This project assumes you have some experience with Power BI Desktop. Step 1 walks you through installing DB Browser for SQLite and loading the donor dataset.

Before We Start

Before We Start...

Before we dive in, let's lock in what you're about to build and why it matters. Heritage Community Foundation's VP of Development is asking a deceptively simple question: "Are we healthy?" Revenue is at an all-time high, but something feels off.

Set Up Your Analytics Toolkit

Every data analysis project starts with the same question: where does the data live, and how will you query it? Heritage Community Foundation's donor records are stored across two spreadsheet-style CSV files.

In this step, you will install DB Browser for SQLite, confirm Power BI Desktop is ready, and load the donor data into a database you can query with SQL.

In this step, get ready to:
  • Install DB Browser for SQLite and verify Power BI Desktop.
  • Save the donor and donation CSV files to a project folder.
  • Import the CSV files into a SQLite database and verify the data.
Install DB Browser for SQLite and verify Power BI Desktop

DB Browser for SQLite is a free, visual tool for creating and querying SQLite databases. You will use it to write SQL queries against the donor data.

  • Open your browser and go to the DB Browser for SQLite download page:
https://sqlitebrowser.org/dl/
  • Click Standard installer for 64-bit Windows to download the installer.
  • Run the downloaded installer and accept all the default options.
  • Once installation finishes, press the Windows key, type DB Browser, and press Enter to open it.

You should see the DB Browser for SQLite main window with tabs for Database Structure, Browse Data, Edit Pragmas, and Execute SQL.

Don't see DB Browser for SQLite?

Make sure the installer completed without errors. Try searching for SQLiteBrowser in the Start menu instead.

If you still cannot find it, redownload the installer from sqlitebrowser.org/dl/ and run it again.

Help me install DB Browser for SQLite on Windows.

Now confirm Power BI Desktop is installed.

  • Press the Windows key, type Power BI Desktop, and press Enter.

✔️ Power BI Desktop opens

Power BI Desktop is ready. Close it for now. You will use it in a later step.

ⓧ I can't find Power BI Desktop

  • Press the Windows key, type Microsoft Store, and press Enter.
  • In the Microsoft Store search bar, type Power BI Desktop and press Enter.
  • Click Install (it is free).
  • Once the install finishes, click Open to verify it launches to the home screen.
  • Close Power BI Desktop for now.
Save the dataset CSV files to your project folder

This project includes two data files that represent Heritage Community Foundation's donor records. You will save them as CSV files so you can reuse them in any tool.

  • Open File Explorer and navigate to your Documents folder.
  • Create a new folder called heritage_donor_analysis.

Now create the first data file. This file contains 25 donor records with biographical and acquisition data.

  • Press the Windows key, type Notepad, and press Enter to open it.
  • Copy and paste the following donor data into the empty Notepad window:
donor_id,first_name,last_name,city,state,first_gift_date,acquisition_channel
1,Margaret,Chen,Portland,OR,2019-03-15,direct_mail
2,Robert,Williams,Seattle,WA,2019-06-22,direct_mail
3,Patricia,Johnson,Denver,CO,2019-11-30,event
4,James,Wright,Austin,TX,2020-01-10,direct_mail
5,Linda,Martinez,Phoenix,AZ,2020-04-18,digital
6,Michael,Thompson,Chicago,IL,2020-09-05,major_gift
7,Susan,Garcia,San Diego,CA,2020-12-15,direct_mail
8,David,Brown,Nashville,TN,2021-03-20,digital
9,Karen,Davis,Atlanta,GA,2021-05-12,event
10,Thomas,Wilson,Boston,MA,2021-07-01,peer_to_peer
11,Nancy,Anderson,Minneapolis,MN,2021-09-15,digital
12,Christopher,Taylor,Dallas,TX,2021-11-28,direct_mail
13,Lisa,Harris,Miami,FL,2021-12-10,digital
14,Jennifer,Clark,Charlotte,NC,2021-08-05,digital
15,Sarah,Lewis,Philadelphia,PA,2021-04-20,event
16,Daniel,Robinson,Detroit,MI,2022-02-14,direct_mail
17,Mark,Walker,Richmond,VA,2022-06-30,peer_to_peer
18,Steven,Hall,Omaha,NE,2022-09-10,digital
19,Jessica,Lee,Tampa,FL,2022-11-25,digital
20,Maria,Rodriguez,Tucson,AZ,2022-05-18,event
21,Brian,Mitchell,Columbus,OH,2022-08-05,direct_mail
22,Elizabeth,Allen,Raleigh,NC,2023-01-15,digital
23,Richard,Young,Sacramento,CA,2023-03-22,direct_mail
24,Barbara,King,Kansas City,MO,2023-05-18,event
25,Joseph,Scott,Salt Lake City,UT,2023-11-20,digital

What is in this data?

Each row is one donor. The columns capture their ID, name, location, when they first gave, and which channel brought them in (direct mail, digital, event, major gift, or peer-to-peer).

  • In Notepad, click File, then Save As.
  • Navigate to your heritage_donor_analysis folder.
  • In the Save as type dropdown, select All Files (*.*).
  • Type donors.csv as the file name and click Save.

You should see donors.csv appear in your heritage_donor_analysis folder.

File saved as donors.csv.txt?

This happens if you did not change Save as type to All Files (*.*). Notepad adds a .txt extension by default. Delete the file, reopen Notepad, and save again with the correct file type selected.

Help me save a CSV file correctly in Notepad.

Now create the donations file. This file contains 74 transaction records spanning FY2021 through FY2024. The file is shown in three parts because it is long. Paste each part in sequence into the same Notepad window.

  • Open a new Notepad window (File > New Window).
  • Copy and paste the first part of the donations data:
donation_id,donor_id,donation_date,amount,channel,campaign
1,1,2021-04-10,250.00,direct_mail,spring_appeal
2,1,2021-12-15,500.00,direct_mail,year_end
3,2,2021-05-20,150.00,direct_mail,spring_appeal
4,2,2021-12-18,300.00,direct_mail,year_end
5,3,2021-03-12,200.00,event,gala
6,3,2021-12-01,150.00,event,year_end
7,4,2021-04-05,400.00,direct_mail,spring_appeal
8,4,2021-12-20,600.00,direct_mail,year_end
9,5,2021-06-15,35.00,digital,email_campaign
10,6,2021-09-10,2500.00,major_gift,cultivation
11,6,2021-12-22,3500.00,major_gift,year_end
12,7,2021-04-25,175.00,direct_mail,spring_appeal
13,7,2021-12-28,325.00,direct_mail,year_end
14,8,2021-04-01,50.00,digital,email_campaign
15,9,2021-05-20,200.00,event,gala
16,10,2021-08-15,75.00,peer_to_peer,walkathon
17,11,2021-10-01,25.00,digital,social_media
18,12,2021-12-01,450.00,direct_mail,year_end
19,13,2021-12-15,30.00,digital,email_campaign
20,14,2021-09-10,40.00,digital,social_media
21,15,2021-05-01,175.00,event,gala
22,1,2022-04-12,300.00,direct_mail,spring_appeal
23,1,2022-12-14,600.00,direct_mail,year_end
24,2,2022-05-18,200.00,direct_mail,spring_appeal
  • Place your cursor at the very end of the last line you just pasted. Press Enter once to start a new line, then paste the second part:
25,2,2022-12-20,350.00,direct_mail,year_end
26,3,2022-03-15,250.00,event,gala
27,4,2022-04-08,450.00,direct_mail,spring_appeal
28,6,2022-06-15,4000.00,major_gift,cultivation
29,6,2022-12-18,5000.00,major_gift,year_end
30,7,2022-04-20,200.00,direct_mail,spring_appeal
31,7,2022-12-22,350.00,direct_mail,year_end
32,8,2022-03-15,45.00,digital,email_campaign
33,9,2022-05-18,225.00,event,gala
34,12,2022-12-05,500.00,direct_mail,year_end
35,15,2022-05-10,150.00,event,gala
36,16,2022-04-15,275.00,direct_mail,spring_appeal
37,16,2022-12-12,425.00,direct_mail,year_end
38,17,2022-07-20,60.00,peer_to_peer,walkathon
39,18,2022-10-05,30.00,digital,social_media
40,19,2022-12-20,25.00,digital,email_campaign
41,20,2022-05-22,300.00,event,gala
42,21,2022-12-10,350.00,direct_mail,year_end
43,1,2023-04-14,350.00,direct_mail,spring_appeal
44,1,2023-12-16,750.00,direct_mail,year_end
45,2,2023-05-22,250.00,direct_mail,spring_appeal
46,3,2023-03-18,275.00,event,gala
47,6,2023-05-10,5000.00,major_gift,cultivation
48,6,2023-12-15,7000.00,major_gift,year_end
49,7,2023-04-22,225.00,direct_mail,spring_appeal
  • Place your cursor at the end of the last line again. Press Enter once, then paste the final part:
50,7,2023-12-20,400.00,direct_mail,year_end
51,9,2023-05-20,250.00,event,gala
52,12,2023-04-10,300.00,direct_mail,spring_appeal
53,12,2023-12-08,550.00,direct_mail,year_end
54,16,2023-04-18,300.00,direct_mail,spring_appeal
55,16,2023-12-14,500.00,direct_mail,year_end
56,20,2023-05-25,275.00,event,gala
57,21,2023-12-12,375.00,direct_mail,year_end
58,22,2023-02-10,20.00,digital,email_campaign
59,23,2023-04-05,225.00,direct_mail,spring_appeal
60,23,2023-12-18,400.00,direct_mail,year_end
61,24,2023-05-20,200.00,event,gala
62,25,2023-12-01,15.00,digital,social_media
63,1,2024-04-16,400.00,direct_mail,spring_appeal
64,1,2024-12-14,1000.00,direct_mail,year_end
65,2,2024-05-20,300.00,direct_mail,spring_appeal
66,6,2024-04-20,8000.00,major_gift,cultivation
67,6,2024-12-18,12000.00,major_gift,year_end
68,7,2024-04-25,250.00,direct_mail,spring_appeal
69,7,2024-12-22,450.00,direct_mail,year_end
70,12,2024-04-12,350.00,direct_mail,spring_appeal
71,12,2024-12-10,600.00,direct_mail,year_end
72,16,2024-04-20,350.00,direct_mail,spring_appeal
73,16,2024-12-16,550.00,direct_mail,year_end
74,23,2024-04-08,275.00,direct_mail,spring_appeal

What is in this data?

Each row is one donation. The columns capture the donation ID, which donor gave, the date, the dollar amount, the giving channel, and the campaign that triggered the gift.

The data spans four fiscal years (FY2021 through FY2024). You will query this data to uncover trends in donor behavior.

  • In Notepad, click File, then Save As.
  • Navigate to your heritage_donor_analysis folder.
  • Change Save as type to All Files (*.*).
  • Type donations.csv as the file name and click Save.

Open your heritage_donor_analysis folder in File Explorer. You should see both donors.csv and donations.csv listed.

These CSV files are yours to keep

You can reuse these files for future analysis in any tool that reads CSV, including Excel, Python, or Power BI.

Create the database and import the CSV files

With the CSV files saved, you can now load them into a SQLite database that you will query throughout this project.

  • Switch back to DB Browser for SQLite.
  • Click New Database in the toolbar.
  • Navigate to your heritage_donor_analysis folder.
  • Type heritage_donors.db as the file name and click Save.

What happens when you create a new database?

DB Browser may open a "Edit table definition" dialog right away. If it does, click Cancel to close it. You do not need to create tables manually because importing the CSV files will create them for you.

Now import the donors CSV file.

  • Click File in the menu bar.
  • Click Import, then click Table from CSV file....
  • Select donors.csv from your heritage_donor_analysis folder.
  • In the import dialog, make sure Column names in first line is checked.
  • Confirm the table name is set to donors.
  • Click OK.
  • Verify the import by clicking the Browse Data tab.
  • Select donors from the table dropdown at the top.

You should see 25 rows of donor records with columns for donor_id, first_name, last_name, city, state, first_gift_date, and acquisition_channel.

Don't see 25 rows?

If the table shows fewer rows, the CSV file may have extra blank lines or the wrong encoding. Delete the donors table (right-click it in Database Structure, then Delete Table), reopen donors.csv in Notepad to check for blank lines at the end, and re-import.

Help me fix my CSV import in DB Browser for SQLite.

Now import the donations CSV file using the same process.

  • Click File in the menu bar.
  • Click Import, then click Table from CSV file....
  • Select donations.csv from your heritage_donor_analysis folder.
  • Make sure Column names in first line is checked.
  • Confirm the table name is set to donations.
  • Click OK.
  • Click Write Changes in the toolbar to save everything to disk.

Why do I need to click Write Changes?

DB Browser for SQLite holds your changes in memory until you commit them. Clicking Write Changes writes everything permanently to the heritage_donors.db file on disk. If you skip this, your imports could be lost when you close the application.

Before you check the final result, take a moment to predict: how many rows do you expect to see in the donations table?

  • Click the Browse Data tab.
  • Select donations from the table dropdown.

You should see 74 rows of donation records. The first row shows donation_id 1, donor_id 1, a date of 2021-04-10, and an amount of 250.00.

Seeing fewer than 74 rows?

If you pasted the three parts of donations.csv with extra blank lines between them, the import may have created empty records. Delete the donations table, fix the CSV in Notepad by removing any blank lines, and re-import.

Help me fix my donations CSV import.

Your analytics toolkit is ready. You have DB Browser for SQLite installed, Power BI Desktop confirmed, and a database with 25 donors and 74 donations loaded and saved. Next up, you will write your first SQL queries to read the headline numbers that the board sees in annual reports.

Read the Headlines

Your database is loaded and both tables are verified. Now the real work begins. The VP of Development at Heritage Community Foundation has asked a simple question: is the organization healthy?

Your first move as an analyst is to check the headline metrics. Total revenue, average gift size, and donor count by year. These are the numbers the board sees in annual reports, and they set the stage for everything that follows.

In this step, get ready to:
  • Write a SQL query that calculates total revenue, gift count, and unique donors by fiscal year.
  • Check whether average gift size is trending up or down.
  • Examine the donor count trend for warning signs.
Write your first SQL query: total revenue by year

So far you have been browsing raw data in the Browse Data tab. To ask questions of the data, you need SQL queries.

  • In DB Browser for SQLite, click the Execute SQL tab at the top of the window.
  • You will see an empty text editor where you can type SQL statements, with a results grid below it.
  • Type the following query into the editor to calculate total revenue, total gifts, and unique donors for each year:
-- Query 1: Total revenue and donor count by fiscal year
SELECT
    strftime('%Y', donation_date) AS fiscal_year,
    SUM(amount) AS total_revenue,
    COUNT(*) AS total_gifts,
    COUNT(DISTINCT donor_id) AS unique_donors
FROM donations
GROUP BY fiscal_year
ORDER BY fiscal_year;

What does this query do?

This query reads every row in the donations table and groups the donations by year.

  • strftime('%Y', donation_date) extracts the four-digit year from each donation date. This is a built-in SQLite function for working with dates stored as text.
  • SUM(amount) adds up all the donation amounts within each year.
  • COUNT(*) counts every donation row. COUNT(DISTINCT donor_id) counts only unique donors, so a donor who gave three times is counted once.
  • GROUP BY fiscal_year collapses all the rows for each year into a single summary row.

Before you run this, think for a moment: do you expect Heritage Community Foundation's revenue to be going up, going down, or staying flat over the past four years?

  • Click the Execute SQL button (the play icon above the editor), or press Ctrl+Enter to run the query.

The results grid shows four rows, one for each year from 2021 to 2024. Revenue goes up every single year. At first glance, Heritage Community Foundation looks like it is thriving.

Jot these numbers down

You will reference these revenue figures in your executive brief later. Write down the total_revenue value for each fiscal year now so you have them handy.

Seeing an error instead of results?

  • Make sure every line is typed exactly as shown, including the semicolon at the end.
  • Check that you are in the Execute SQL tab, not the Browse Data tab.
  • Confirm the table name is donations (lowercase, plural). If you named it differently during import, adjust the query to match.

Still stuck? Help me debug my first SQL query in DB Browser for SQLite.

Check average gift size by year

Revenue is climbing. The next question a board member would ask: is that because more people are giving, or because each gift is getting larger?

  • Place your cursor below your first query in the editor (leave a blank line between them for readability).
  • Type the following query to calculate the average gift amount and average revenue per donor for each year:
-- Query 2: Average gift size by fiscal year
SELECT
    strftime('%Y', donation_date) AS fiscal_year,
    ROUND(AVG(amount), 2) AS avg_gift_size,
    ROUND(SUM(amount) / COUNT(DISTINCT donor_id), 2) AS avg_revenue_per_donor
FROM donations
GROUP BY fiscal_year
ORDER BY fiscal_year;

What does this query do?

  • AVG(amount) calculates the average across all individual gifts in that year.
  • SUM(amount) / COUNT(DISTINCT donor_id) calculates average revenue per donor, which accounts for donors who give multiple times.
  • ROUND(..., 2) rounds the result to two decimal places so the numbers are easy to read.
  • Highlight just this second query (select it with your mouse), then press Ctrl+Enter to run only the selected text.

Average gift size is also increasing year over year. This reinforces the story that everything is going well. Revenue up, average gifts up. A board member reviewing these numbers would feel confident.

Seeing results from the wrong query?

If you have multiple queries in the editor, DB Browser runs all of them by default and shows the result of the last one. To run only the query you want, highlight it with your mouse first, then press Ctrl+Enter. Only the highlighted text will execute.

Need more help? Help me run a specific query when I have multiple queries in the editor.

Spot the crack in the headline numbers

Revenue is up. Average gift size is up. But there is one more column hiding in your first query that tells a different story.

  • Scroll back up to your first query (Query 1) in the editor.
  • Highlight it and press Ctrl+Enter to run it again.
  • This time, focus on the unique_donors column in the results grid.

Do you see it? Revenue keeps climbing, but the number of unique donors is flat or declining. By the most recent year, the donor count has dropped sharply compared to earlier years. Fewer people are giving, yet total revenue is higher than ever.

Why does this matter?

A growing revenue number built on a shrinking donor base is a warning sign. It means the organization is increasingly dependent on a small number of donors giving larger amounts. If even one or two of those donors stop giving, revenue could collapse.

This gap between the headline story (revenue is great) and the underlying reality (the donor base is eroding) is exactly the kind of insight a Director of Fundraising Analytics is hired to surface. Note the unique_donors numbers alongside your revenue figures for the executive brief.

  • Save your SQL queries by clicking the Save SQL file button in the toolbar above the editor.
  • Save the file as step2_headline_queries.sql in your project folder.

✔️ Awesome, I've got everything!

Great. Make sure you have noted the revenue and donor count values for each year. You will use them in your executive brief.

ⓧ I'd like to double check the full code

Here are both queries you should have in your step2_headline_queries.sql file:

-- Query 1: Total revenue and donor count by fiscal year
SELECT
    strftime('%Y', donation_date) AS fiscal_year,
    SUM(amount) AS total_revenue,
    COUNT(*) AS total_gifts,
    COUNT(DISTINCT donor_id) AS unique_donors
FROM donations
GROUP BY fiscal_year
ORDER BY fiscal_year;

-- Query 2: Average gift size by fiscal year
SELECT
    strftime('%Y', donation_date) AS fiscal_year,
    ROUND(AVG(amount), 2) AS avg_gift_size,
    ROUND(SUM(amount) / COUNT(DISTINCT donor_id), 2) AS avg_revenue_per_donor
FROM donations
GROUP BY fiscal_year
ORDER BY fiscal_year;

You have read the headlines. Revenue is at an all-time high, average gifts are growing, and the board would be pleased. But those donor counts tell a quieter, more troubling story. Next up, you will dig into retention rates and cohort analysis to find out exactly where the donor base is breaking down.

Uncover What the Headlines Hide

In Step 2, you ran the numbers every board member asks about: total revenue, average gift size, and donor count. Revenue went up every year. On the surface, Heritage Community Foundation looks like it is thriving.

But the flat donor count you spotted is a warning sign. If revenue keeps climbing while fewer people are giving, something fragile is propping up those numbers. In this step, you will dig beneath the headlines with retention analysis and cohort segmentation to find out whether the donor base can sustain that growth.

In this step, get ready to:
  • Calculate donor retention rates year over year to see if donors are coming back.
  • Break retention down by first-time vs. repeat donors to find the root cause of churn.
  • Analyze performance by acquisition channel to identify where donors stay and where they leave.
Calculate donor retention rate year over year

Retention rate measures what percentage of donors who gave in one year came back to give the next year. It is the single most important metric for donor file health.

What is donor retention rate?

The formula is: (Returning Donors / Total Donors Last Year) x 100. A nonprofit with 100 donors last year and 45 who gave again this year has a 45% retention rate.

The industry average for overall donor retention is about 43%. Anything consistently below that signals a leaking bucket that no amount of new donor acquisition can fill.

  • Switch back to the Execute SQL tab in DB Browser for SQLite.
  • Clear any existing SQL from the previous step.
  • Calculate the year-over-year retention rate by pasting this query:
-- Query 3: Donor retention rate year over year
SELECT
    a.fiscal_year AS from_year,
    CAST(CAST(a.fiscal_year AS INTEGER) + 1 AS TEXT) AS to_year,
    COUNT(DISTINCT a.donor_id) AS donors_in_from_year,
    COUNT(DISTINCT b.donor_id) AS retained_donors,
    ROUND(
        COUNT(DISTINCT b.donor_id) * 100.0 / COUNT(DISTINCT a.donor_id),
        1
    ) AS retention_rate_pct
FROM
    (SELECT DISTINCT donor_id, strftime('%Y', donation_date) AS fiscal_year
     FROM donations) a
LEFT JOIN
    (SELECT DISTINCT donor_id, strftime('%Y', donation_date) AS fiscal_year
     FROM donations) b
ON a.donor_id = b.donor_id
   AND CAST(b.fiscal_year AS INTEGER) = CAST(a.fiscal_year AS INTEGER) + 1
WHERE CAST(a.fiscal_year AS INTEGER) < 2024
GROUP BY a.fiscal_year
ORDER BY a.fiscal_year;

What does this query do?

  • The two subqueries (aliased a and b) each build a list of unique donor-year pairs from the donations table.
  • The LEFT JOIN tries to match each donor in year X with the same donor in year X+1. If no match is found, the donor churned.
  • The ROUND(... * 100.0 / ..., 1) calculates the retention percentage to one decimal place.
  • Click the Execute button (the play icon) or press F5 to run the query.

You should see three rows in the results grid, one for each year-over-year transition (2021 to 2022, 2022 to 2023, 2023 to 2024). Notice that the retention_rate_pct declines with each row.

Seeing zero rows or an error?

  • Make sure you are on the Execute SQL tab, not the Browse Data tab.
  • Check that the table name is donations (lowercase, plural). If you named it differently during import, update the query to match.
  • If you see a "no such function: strftime" error, your SQLite version may be outdated. DB Browser for SQLite 3.13.1 includes a modern SQLite engine that supports this function.

Help me debug my retention rate query in DB Browser for SQLite.

Break retention down by first-time vs. repeat donors

Overall retention declining is a problem, but you need to know where the churn is concentrated. Are long-time supporters leaving, or is the organization losing new donors before they ever come back?

  • Clear the SQL editor in the Execute SQL tab.
  • Calculate retention separately for first-time and repeat donors by pasting this query:
-- Query 4: Retention by cohort type (first-time vs repeat)
SELECT
    a.fiscal_year AS from_year,
    CASE
        WHEN strftime('%Y', d.first_gift_date) = a.fiscal_year THEN 'first_time'
        ELSE 'repeat'
    END AS donor_cohort,
    COUNT(DISTINCT a.donor_id) AS donors_in_cohort,
    COUNT(DISTINCT b.donor_id) AS retained,
    ROUND(
        COUNT(DISTINCT b.donor_id) * 100.0 / COUNT(DISTINCT a.donor_id),
        1
    ) AS retention_rate_pct
FROM
    (SELECT DISTINCT donor_id, strftime('%Y', donation_date) AS fiscal_year
     FROM donations) a
JOIN donors d ON a.donor_id = d.donor_id
LEFT JOIN
    (SELECT DISTINCT donor_id, strftime('%Y', donation_date) AS fiscal_year
     FROM donations) b
ON a.donor_id = b.donor_id
   AND CAST(b.fiscal_year AS INTEGER) = CAST(a.fiscal_year AS INTEGER) + 1
WHERE CAST(a.fiscal_year AS INTEGER) < 2024
GROUP BY a.fiscal_year, donor_cohort
ORDER BY a.fiscal_year, donor_cohort;

What does this query do?

  • The CASE statement checks each donor's first_gift_date from the donors table. If their first gift year matches the current fiscal year, they are labeled first_time. Otherwise, they are repeat.
  • The JOIN donors d brings in the biographical data so the query can classify each donor into a cohort.
  • The GROUP BY a.fiscal_year, donor_cohort splits the retention calculation so you get a separate rate for each cohort in each year.

Before you run this, take a guess: do you think first-time and repeat donors retain at similar rates, or is there a gap?

  • Click Execute (or press F5) to run the query.

The gap is not subtle. First-time donor retention sits at or below 25%, while repeat donors retain at 60-70%. Heritage Community Foundation is acquiring donors who almost all disappear after their first gift.

Why does this matter so much?

The industry benchmark for first-time donor retention is about 19%, and for repeat donors it is about 69%. Heritage's repeat retention is healthy, but the organization is pouring money into acquiring new donors who never come back.

This is the root cause hiding behind the headline numbers. Revenue keeps growing because a small group of loyal, upgrading donors gives more each year. But the broad base of supporters is shrinking underneath them.

Seeing only one cohort type per year?

  • Make sure the donors table was imported with the correct column names. Click Browse Data, select the donors table, and confirm that the first_gift_date column exists and contains dates in YYYY-MM-DD format.
  • Check that you used JOIN donors d (not LEFT JOIN) for the first join. This inner join is intentional because every donation should have a matching donor record.

Help me debug my cohort retention query.

Analyze performance by acquisition channel

You now know that first-time donors churn at alarming rates. The next question is whether certain acquisition channels produce donors who stick around. If one channel brings in loyal, high-value donors while another produces one-and-done gifts, that changes where the VP should invest.

  • Clear the SQL editor again.
  • Analyze revenue, donor count, and average gift size by acquisition channel by pasting this query:
-- Query 5: Channel performance analysis
SELECT
    d.acquisition_channel,
    COUNT(DISTINCT dn.donor_id) AS unique_donors,
    SUM(dn.amount) AS total_revenue,
    ROUND(AVG(dn.amount), 2) AS avg_gift_size,
    COUNT(*) AS total_gifts,
    ROUND(SUM(dn.amount) / COUNT(DISTINCT dn.donor_id), 2) AS revenue_per_donor
FROM donations dn
JOIN donors d ON dn.donor_id = d.donor_id
GROUP BY d.acquisition_channel
ORDER BY total_revenue DESC;

What does this query do?

  • The JOIN connects each donation to its donor's biographical record, pulling in acquisition_channel from the donors table.
  • The revenue_per_donor column divides total channel revenue by unique donors, showing lifetime value per donor for each channel.
  • Ordering by total_revenue DESC puts the highest-performing channel at the top so the results tell a story from strongest to weakest.
  • Click Execute (or press F5) to run the query.

You should see five rows, one per channel. Look at the pattern: direct mail donors give larger gifts and return more often. Digital donors give small gifts and most churn quickly. The major gift channel produces outsized revenue from very few donors.

What do these channel results mean for the VP?

The revenue_per_donor column is the key. Direct mail donors have a much higher lifetime value than digital donors, even though digital acquisition is usually cheaper per donor. Spending less to acquire donors who never come back is not a bargain.

These channel insights will drive the budget recommendations in your executive brief.

Seeing fewer than five channels?

  • Confirm that the donors table has an acquisition_channel column. Switch to Browse Data, select the donors table, and check the column headers.
  • If you see a "no such column" error, the CSV may have imported with different column names. Check that "Column names in first line" was checked during import.

Help me debug my channel performance query.

  • Click Write Changes in the toolbar to save your database.

Take a moment to look at all three query results together. Revenue grows every year, but retention is declining. First-time donors churn at rates above 75%. The donor file is shrinking, and a small number of loyal, upgrading donors are masking the problem.

✔️ Awesome, I've got everything!

Great. Make sure you have saved your database by clicking Write Changes. Your three queries (retention year over year, cohort retention, and channel performance) are ready for the next step.

ⓧ I'd like to double check the full code

The complete step3_retention_queries.sql file contains three queries. Scroll up and confirm you have each one:

  • Query 3: Donor retention rate year over year using subqueries aliased a and b with a LEFT JOIN on consecutive years.
  • Query 4: Retention by cohort type using a CASE statement to classify donors as first_time or repeat based on first_gift_date.
  • Query 5: Channel performance analysis joining donations and donors to group by acquisition_channel.

You have uncovered the real story hiding behind Heritage's record revenue. The donor base is eroding, first-time donors are churning almost immediately, and a handful of major gift donors are carrying the weight. Next up, you will turn these findings into a Power BI dashboard that makes the problem impossible to ignore.

Build the Dashboard in Power BI

Your SQL queries exposed a serious problem hiding behind Heritage Community Foundation's headline numbers. Revenue is climbing, but the donor base is eroding underneath.

Numbers in a query grid are convincing to analysts, but board members need visuals. In this step, you will export your query results from DB Browser for SQLite as CSV files, load them into Power BI Desktop, and build a four-visual dashboard that makes the retention crisis impossible to ignore.

In this step, get ready to:
  • Export four SQL result sets from DB Browser as CSV files.
  • Import all four CSVs into Power BI Desktop.
  • Build a dashboard with four visuals, a title, and a subtitle that tells the full story.
Export your SQL results as CSV files

Rather than connecting Power BI directly to the SQLite database (which requires an ODBC driver), you will export each query's results as a CSV file. This is a common workflow in real-world analytics, where analysts often receive data as CSV exports from CRM systems.

Why export to CSV instead of connecting directly?

Connecting Power BI to SQLite requires installing and configuring an ODBC driver, which adds setup complexity. Exporting to CSV keeps things simple and mirrors how most fundraising analysts work day-to-day. CRM systems like Salesforce and Raiser's Edge typically export data as CSV files for downstream analysis.

  • Switch back to DB Browser for SQLite with your heritage_donors.db database open.
  • Click the Execute SQL tab.
  • Paste Query 1 (total revenue and donor count by fiscal year) into the SQL editor and run it by pressing Ctrl+Enter.
SELECT
    strftime('%Y', donation_date) AS fiscal_year,
    SUM(amount) AS total_revenue,
    COUNT(*) AS total_gifts,
    COUNT(DISTINCT donor_id) AS unique_donors
FROM donations
GROUP BY fiscal_year
ORDER BY fiscal_year;
  • With the results showing in the grid below, click the small dropdown arrow on the export/save results button in the Execute SQL toolbar.
  • Select Export to CSV.
  • In the export dialog, make sure the field separator is set to a comma (,).
  • Save the file as revenue_by_year.csv in your project folder.

Now repeat this process for the three remaining queries. Run each one, then export the results to CSV.

  • Run Query 3 (donor retention rate year over year) and export the results as retention_by_year.csv.
  • Run Query 4 (retention by cohort type) and export the results as retention_by_cohort.csv.
  • Run Query 5 (channel performance analysis) and export the results as channel_performance.csv.

Verify all four CSV files exist in your project folder. You should see revenue_by_year.csv, retention_by_year.csv, retention_by_cohort.csv, and channel_performance.csv alongside your original data files.

Don't see the export option in the toolbar?

Make sure you have run the query first so results appear in the grid below the SQL editor. The export button only becomes active when results are displayed. If you still cannot find it, try using the menu path File then Export then Table(s) as CSV file as an alternative for table-level export.

Help me export query results to CSV in DB Browser for SQLite.

Import the CSV files into Power BI Desktop

With your data exported, it is time to bring it into Power BI for visualization.

  • Open Power BI Desktop from the Start menu.
  • On the Home tab, click Get Data.
  • Select Text/CSV from the list.
  • Navigate to your project folder and select revenue_by_year.csv.
  • Click Open.
  • In the preview dialog, confirm the columns look correct (fiscal_year, total_revenue, total_gifts, unique_donors), then click Load.

You should see revenue_by_year appear in the Data pane on the right side of the screen.

  • Repeat the import process: click Get Data then Text/CSV for each remaining file.
  • Import retention_by_year.csv and click Load.
  • Import retention_by_cohort.csv and click Load.
  • Import channel_performance.csv and click Load.

The Data pane should now list all four tables: revenue_by_year, retention_by_year, retention_by_cohort, and channel_performance.

Columns showing as text instead of numbers?

If Power BI imported numeric columns (like total_revenue) as text, click Transform Data on the Home tab to open the Power Query Editor. Select the column header, then change the data type to Decimal Number or Whole Number. Click Close & Apply when done.

Help me fix column data types in Power BI.

Create four visuals that tell the story

Your dashboard will guide the viewer through a narrative arc. It starts with the good news (revenue going up), then reveals the problem (donor count going down), exposes the root cause (terrible first-time donor retention), and ends with actionable channel data.

Arrange your visuals in a 2x2 grid layout. The top-left hooks the audience with success. The top-right contradicts it. The bottom row explains why.

  • Make sure you are on the report canvas (not the Data or Model view) in Power BI Desktop.
  • In the Visualizations pane, click the Line chart icon to add a visual placeholder to the canvas.
  • Expand revenue_by_year in the Data pane.
  • Drag fiscal_year to the X-axis field well.
  • Drag total_revenue to the Y-axis field well.
  • Resize the chart and position it in the top-left quadrant of the canvas.

You should see a line climbing upward from 2021 to 2024. This is Visual 1: the "good news" that hooks your audience.

Why start with the good news?

Starting with revenue growth builds trust with the audience. If you lead with bad news, stakeholders can become defensive. By showing the metric they are proudest of first, you earn the right to reveal the problem in the next visual.

  • Click an empty area of the canvas to deselect the first visual.
  • In the Visualizations pane, click the Clustered column chart icon.
  • Expand revenue_by_year in the Data pane.
  • Drag fiscal_year to the X-axis field well.
  • Drag unique_donors to the Y-axis field well.
  • Resize and position this chart in the top-right quadrant.

The bars should show a declining trend. Revenue goes up on the left, donor count goes down on the right. That visual tension is the heart of your story.

  • Click an empty area of the canvas to deselect.
  • In the Visualizations pane, click the Clustered bar chart icon (or Grouped bar chart).
  • Expand retention_by_cohort in the Data pane.
  • Drag from_year to the X-axis (or Y-axis if using a bar chart) field well.
  • Drag retention_rate_pct to the Y-axis (or X-axis if using a bar chart) field well.
  • Drag donor_cohort to the Legend field well to split the bars by first_time vs repeat.
  • Resize and position this chart in the bottom-left quadrant.

You should see two groups of bars for each year. The repeat donor bars are much taller than the first-time donor bars. This is the root cause: new donors are not coming back.

Handling summarized data in Power BI

Power BI may try to sum or count your numeric fields by default. If retention_rate_pct shows an unexpectedly large number, click the dropdown arrow next to the field in the Y-axis well and change the aggregation from Sum to Don't summarize or Average. Since your CSV already contains pre-calculated retention percentages, you want Power BI to display them as-is.

  • Click an empty area of the canvas to deselect.
  • In the Visualizations pane, click the Table icon.
  • Expand channel_performance in the Data pane.
  • Drag acquisition_channel, unique_donors, total_revenue, avg_gift_size, and revenue_per_donor into the Columns field well.
  • Resize and position the table in the bottom-right quadrant.

The table shows at a glance which channels bring in donors that stay versus donors that churn. Direct mail and event channels should show higher revenue per donor compared to digital.

Visuals overlapping or too small?

Click the edge of any visual and drag to resize it. You can also hold Ctrl and click multiple visuals to select them, then use the Format tab in the ribbon to align them evenly. Aim for roughly equal-sized quadrants so no single visual dominates the page.

Help me arrange my Power BI visuals.

Add title, subtitle, and formatting
  • On the Insert tab in the ribbon, click Text box.
  • Type State of the Donor File: FY2021-FY2024 and format it as a large, bold heading.
  • Position the text box at the top of the canvas, above your visuals.
  • Add a second text box below the title.
  • Type Heritage Community Foundation and format it as a smaller subtitle.

Your canvas should now have a clear title at the top with four visuals arranged in a grid below it.

  • Select each visual one at a time and use the Format visual icon (the paint roller) in the Visualizations pane to apply consistent colors.
  • Choose a single accent color for the revenue line chart and donor count bars to keep the dashboard cohesive.
  • For the retention cohort chart, use two distinct colors (e.g., a blue for repeat and an orange for first-time) so the gap between the two groups stands out.

Before you save, take a moment to predict what this dashboard communicates. Does the left column tell a different story than the right column?

  • Save the file by pressing Ctrl+S.
  • Name the file heritage_donor_analysis.pbix and save it in your project folder.

Your completed dashboard should show a revenue line climbing up in the top left while donor count bars decline in the top right. The bottom left reveals first-time donor retention far below repeat donor retention. The bottom right table shows which channels drive lasting donors versus one-time givers.

What does this dashboard prove?

The top row creates visual tension. Revenue is at an all-time high, but the number of people giving is shrinking. That contradiction is the key insight.

The bottom row explains why. First-time donors almost never return, so the organization is spending money to acquire donors who vanish after one gift. The channel performance table shows where to invest for better retention. Together, these four visuals build an argument the VP can walk through with the board in under two minutes.

Your dashboard is ready. Next up, you will write the executive brief that translates these visuals into a document the VP can email directly to the board with clear findings and recommendations.

Write the Executive Brief

Your Power BI dashboard tells a clear visual story. But a VP of Development cannot email a dashboard link to the board and expect action.

In this step, you will write a one-page executive brief that translates your data findings into strategic recommendations. This is the document the VP attaches to an email and says "here is what the data shows and what we should do about it."

In this step, get ready to:
  • Write a situation statement framing the analysis.
  • Write three key findings, each tied to specific numbers from your queries.
  • Write three strategic recommendations backed by your data.
Open a new document and write the situation statement

Every executive brief starts with a situation statement. This is 2-3 sentences that tell the reader what prompted the analysis and what it covers.

  • Open Word, Google Docs, or any text editor you prefer.
  • At the top of the document, type a title: State of the Donor File: FY2021-FY2024.
  • Below the title, add a section heading: Situation.
  • Write 2-3 sentences that cover these three points:

Want to compare your situation statement to an example?

✔️ I'll try it first

Write the situation statement in your own words using the three points above. Save the document when you are done.

ⓧ Let me check my work

Here is an example situation statement you can compare yours against:

"Heritage Community Foundation's revenue reached an all-time high in FY2024. However, board members have raised concerns about whether the underlying donor base can sustain this growth trajectory. This analysis examines the State of the Donor File across FY2021-FY2024, evaluating donor retention, acquisition channel performance, and revenue concentration to answer that question."

Your wording does not need to match exactly. The important thing is that all three points are covered in 2-3 sentences.

  • Save your document.
  • You should see a title, a section heading, and a short paragraph framing the purpose of the analysis.
Write three key findings with supporting data

Each finding makes a claim about the donor file's health. Every claim must be backed by a specific number from your SQL query results.

  • Below the situation statement, add a section heading: Key Findings.

Write each of the following three findings as its own short paragraph. Pull the specific numbers from the query results you noted in Steps 2 and 3.

  • Finding 1: Revenue growth is masking a declining donor base. Cite the revenue growth percentage from FY2021 to FY2024 alongside the donor count decline percentage from the same period. Both numbers come from your revenue_by_year.csv results.
  • Finding 2: New donor retention is critically low. Cite the first-time donor retention rate from your cohort analysis and compare it to the repeat donor retention rate. Both numbers come from your retention_by_cohort.csv results.
  • Finding 3: Revenue is increasingly concentrated in a small number of donors. Reference your channel analysis showing major gift donors driving a disproportionate share of revenue while the broad donor base shrinks. Use numbers from your channel_performance.csv results.

Why tie every finding to a specific number?

Board members see dozens of reports. A finding that says "retention is low" gets skimmed. A finding that says "only 25% of first-time donors gave again" stops someone mid-read.

Every claim in an executive brief earns its credibility from a concrete data point. If a finding does not have a number next to it, it reads as opinion.

Here is a chance to check your findings against an example.

✔️ I'll try it first

Write all three findings using the data from your own query results. Save your document when you are finished.

ⓧ Let me check my work

Here is an example of how each finding could read. Your specific numbers may vary slightly based on how you calculated percentages.

Finding 1: "Revenue grew over 165% from FY2021 to FY2024, reaching an all-time high. However, the unique donor count dropped by more than 50% over the same period. A shrinking number of donors are giving larger amounts, creating a fragile revenue base."

Finding 2: "First-time donor retention sits below 25%, meaning fewer than 1 in 4 new donors give a second gift. By contrast, repeat donors retain at 60-70%. The organization is acquiring donors at significant cost who almost all leave after one gift."

Finding 3: "The major gift channel produces the highest revenue per donor by a wide margin, but it depends on very few individuals. Meanwhile, digitally acquired donors give small amounts and churn quickly. Revenue is increasingly dependent on a handful of major donors."

Each finding should name a specific number or percentage. If yours reads as a general observation without data, go back and add the number from your query results.

  • Save your document.
  • You should now see the situation statement followed by three numbered or labeled findings. Each finding names a specific data point from your analysis.
Write three strategic recommendations

Findings tell the board what is happening. Recommendations tell them what to do about it. Each recommendation should connect directly to one of your findings.

  • Below the findings, add a section heading: Strategic Recommendations.

Write each recommendation as a short paragraph that names the action and ties it back to the data.

  • Recommendation 1: Invest in a first-year donor stewardship program targeting the first 90 days after a gift. Tie this directly to Finding 2 (low first-time donor retention). Explain that a structured welcome series in the first 90 days is when a new donor decides whether to give again.
  • Recommendation 2: Shift acquisition budget toward channels with higher retention rates. Tie this to Finding 3 and your channel analysis. Name the specific channels (direct mail, events) that produce donors who stay, even if their cost-per-acquisition is higher.
  • Recommendation 3: Develop a donor pipeline beyond major gifts to reduce revenue concentration risk. Tie this to Findings 1 and 3. Explain that relying on a small number of major donors puts the organization at risk if even one stops giving.

Connecting recommendations to findings

A strong recommendation explicitly references the finding that justifies it. For example: "Given that first-time donor retention sits below 25% (Finding 2), we recommend..." This makes it impossible for the reader to dismiss the recommendation without also dismissing the data.

Want to see how your recommendations compare to an example?

✔️ I'll try it first

Write all three recommendations, tying each one back to a specific finding and data point. Save your document when you are finished.

ⓧ Let me check my work

Here is an example of how each recommendation could read:

Recommendation 1: "Given that fewer than 1 in 4 first-time donors give a second gift, we recommend investing in a structured first-year stewardship program. Focus on the first 90 days after a gift with a welcome call, an impact update at 30 days, and a personalized ask at 90 days. Converting even a small percentage of first-time donors into repeat donors would significantly stabilize the donor base."

Recommendation 2: "Direct mail and event-acquired donors give larger gifts and return at higher rates than digitally acquired donors. We recommend shifting acquisition budget toward these higher-retention channels, even if cost-per-acquisition is higher. A donor acquired at higher cost who gives for five years is more valuable than a low-cost donor who gives once."

Recommendation 3: "Revenue is increasingly concentrated in a small number of major gift donors. We recommend developing a mid-level donor pipeline (gifts of $500-$2,000) to create a broader revenue base. This reduces organizational risk and builds a bench of future major gift prospects."

  • Save your document.

Before you call it done, ask yourself: could someone read just this one-page document and understand what is wrong with the donor file and what to do about it?

  • Review your document from top to bottom and confirm it contains all of the following:
  • Confirm the entire document fits on one page.

Your completed brief is the kind of deliverable a VP of Development can email directly to the board. It answers the question "are we healthy?" with data, names the root cause, and gives the team three concrete next steps.

Brief running longer than one page?

Cut adjectives and filler phrases first. Executive readers prefer short, direct sentences. If a finding takes three sentences to explain, trim it to two by removing any sentence that restates what the number already proves.

If it is still too long, check whether your recommendations repeat context from the findings. A recommendation can reference a finding by number ("per Finding 2") instead of restating the data.

Help me trim my executive brief to one page.

Secret mission

Donor Concentration Risk Analysis

Your analysis uncovered a retention crisis, but there is an even deeper risk hiding in the data. What would happen if your biggest donor stopped giving tomorrow? In this secret mission, you will write advanced SQL using window functions to quantify exactly how much revenue depends on a handful of donors, then add the finding to both your dashboard and executive brief.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally using free tools, so there are no ongoing costs or cloud resources to worry about. Decide whether to keep your resources as a portfolio, close your applications for now, or delete everything entirely.

Resources you used:

  • Project folder containing heritage_donors.db, CSV data files, SQL query files, heritage_donor_analysis.pbix, and your executive brief.
  • DB Browser for SQLite (installed application).
  • Power BI Desktop (installed application).

Keep everything running

No action needed. Choose this if you plan to continue exploring the data or refining your analysis.

  • Leave DB Browser for SQLite and Power BI Desktop installed for future SQL and analytics practice.
  • Keep your project folder with all files saved. The .pbix file, executive brief, SQL queries, and CSV data files make a strong portfolio package for job applications.
  • All data files are synthetic, so there are no privacy or security concerns with keeping them on your machine.

Pause - I'll come back to this later

Close the applications to free up memory, but keep all files and tools installed so you can pick up where you left off.

  • Close DB Browser for SQLite. Your database and queries are saved in your project folder.
  • Close Power BI Desktop. Your heritage_donor_analysis.pbix file is saved locally and will reopen with all five visuals intact.
  • Your project folder stays on your machine. To resume, reopen the .db file in DB Browser for SQLite and the .pbix file in Power BI Desktop.

Delete - I don't want to use this again

Remove all project files and optionally uninstall the tools you installed during this project.

  • Close DB Browser for SQLite and Power BI Desktop if they are still open.
  • Open File Explorer and navigate to your project folder (the heritage_donor_analysis folder you created in Step 1).
  • Right-click the folder and select Delete. This removes all CSV files, SQL query files, the heritage_donors.db database, heritage_donor_analysis.pbix, and your executive brief.
  • To uninstall DB Browser for SQLite, open Settings, select Apps, then Installed apps. Search for DB Browser, click the three-dot menu, and select Uninstall.
  • To uninstall Power BI Desktop, stay in Settings > Apps > Installed apps. Search for Power BI Desktop, click the three-dot menu, and select Uninstall.

Nice Work!

Nice Work!

You just completed a full fundraising analytics engagement, from raw data to strategic recommendations. You now have three portfolio artifacts that demonstrate the exact skills a Director of Fundraising Analytics uses every day.

You learned how to:

  • Write SQL queries in DB Browser for SQLite that diagnosed a nonprofit's donor file health, uncovering a retention crisis hiding behind record-high revenue numbers.
  • Build a Power BI dashboard with four visuals that tell the story from "everything looks great" to "here is the real problem," making the donor retention decline impossible to ignore.
  • Write a one-page executive brief translating data findings into three strategic recommendations for a VP of Development.
  • Secret Mission: Analyzed donor concentration risk using SQL window functions and added the finding to both the dashboard and the executive brief.

Ready to quiz yourself?