Nonprofit Donor Health Diagnostic

Uncover hidden donor churn using SQL and build a Power BI dashboard.

Introduction

30 Second Summary

Every nonprofit lives or dies by its donors. Leadership wants to know if the donor program is healthy, but the headline numbers can mask serious problems lurking underneath.

In this project, you will build a Donor Health Diagnostic using SQL and Power BI. You will take an ambiguous executive question, structure it into measurable dimensions, write analytical queries that expose a hidden churn problem, and build a dashboard that delivers a strategic recommendation.

What You'll Build

You can walk an interviewer through a live demo: run a query that shows an overall retention rate that looks fine, then segment the data and watch the story fall apart as mid-level donors reveal alarming churn that the aggregate was hiding.

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

  • A SQL analytical workflow that structures a vague business question into four measurable dimensions and uncovers a critical problem hiding behind healthy-looking aggregate metrics.
  • Interview-defensible SQL patterns you can explain on the spot: self-joins for retention, LEFT JOINs for correct denominators, NULLIF for division safety, CASE-inside-aggregate for segmentation, and CTEs for cohort identification.
  • A Power BI dashboard with KPI cards, retention trends, and a channel performance scorecard that communicates your findings in an executive-ready format.
  • Secret Mission: Write an executive brief that structures your findings into a one-page strategic recommendation document.

Are there any prerequisites?

You should be comfortable with SQL fundamentals: SELECT, JOIN, GROUP BY, and window functions. No prior Power BI experience is required.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters for your next interview.

This project walks you through the exact analytical process a Director of Fundraising Analytics would follow. Getting clear on the goal now keeps every query and dashboard choice intentional.

Set Up Your Analytics Environment

You have a vague executive question in front of you: "Is our donor program healthy?" Before you can investigate that with data, you need the right tools installed on your machine.

In this step, you will install SQL Server 2022 Express as your local database engine and SQL Server Management Studio (SSMS) as your query tool. Both are completely free.

In this step, get ready to:
  • Install SQL Server 2022 Express on your machine.
  • Install SQL Server Management Studio (SSMS) 22.
  • Connect to your local SQL Server and verify it works.
Install SQL Server 2022 Express

SQL Server 2022 Express is a free edition of Microsoft's enterprise database engine. It gives you a local SQL Server instance you can query without any cloud costs or subscriptions.

First, check whether SQL Server is already installed on your machine.

  • Press the Windows key, type Services, and press Enter to open the Services app.
  • Scroll through the list and look for any entry starting with SQL Server.

✔️ I see SQL Server (SQLEXPRESS) running

You already have SQL Server Express installed. Skip ahead to the next substep to install SSMS.

How do I know which version I have?

You will confirm the exact version in the final substep when you run SELECT @@VERSION inside SSMS. If it turns out to be an older version, the queries in this project will still work on SQL Server 2019 or later.

ⓧ I see SQL Server but it is a different instance or older

SQL Server Express editions can be installed side-by-side. You can add a fresh 2022 Express instance alongside your existing one.

  • Download SQL2022-SSEI-Expr.exe from the Microsoft Download Center.
  • Run the installer.
  • You will see three options: Basic, Custom, and Download Media. Select Basic.
  • Accept the license terms and let the installer complete with default settings.
  • When installation finishes, a summary screen appears. Confirm the instance name shows SQLEXPRESS.

What does Basic installation do?

The Basic option installs the SQL Server Database Engine with default settings and minimal prompts. It skips the full Installation Wizard and gets you running quickly.

ⓧ I don't see any SQL Server entry

SQL Server is not installed yet. Follow these steps to install it fresh.

  • Download SQL2022-SSEI-Expr.exe from the Microsoft Download Center. The file is about 4 MB.
  • Run the installer.
  • You will see three options: Basic, Custom, and Download Media. Select Basic.
  • Accept the license terms when prompted.
  • Let the installer download and install with default settings. This takes a few minutes depending on your internet connection.
  • When installation finishes, a summary screen appears showing the instance name. Confirm it says SQLEXPRESS.

What does Basic installation do?

The Basic option installs the SQL Server Database Engine with default settings and minimal prompts. It skips the full Installation Wizard and gets you running quickly.

Installation failed or shows an error?

Make sure you are running the installer as an administrator. Right-click the file and select Run as administrator.

If you see errors about disk sector size (common on Windows 11 with NVMe drives), search Microsoft support for the sector size troubleshooting article and apply the registry workaround before retrying.

Help me troubleshoot my SQL Server Express installation

  • After installation, go back to the Services app and confirm that SQL Server (SQLEXPRESS) now appears with a status of Running.
Install SSMS 22

SSMS is the graphical query tool you will use to write and run SQL against your local database. It connects directly to SQL Server Express with no additional configuration.

  • Press the Windows key and type SQL Server Management Studio.

✔️ I see SQL Server Management Studio 22

You already have SSMS 22 installed. Skip ahead to connecting to your SQL Server below.

ⓧ I see an older version of SSMS

SSMS 22 installs separately from older versions. You can install the new version alongside it.

  • Download vs_SSMS.exe from the official SSMS install page.
  • Run the file. It opens the Visual Studio Installer.
  • Click Install and wait for it to complete.

Why does SSMS use Visual Studio Installer?

SSMS 22 is built on the Visual Studio shell. The installer handles dependency management and updates through the same mechanism as Visual Studio itself.

ⓧ Nothing comes up

SSMS is not installed yet. Follow these steps.

  • Download vs_SSMS.exe from the official SSMS install page.
  • Run the file. It opens the Visual Studio Installer.
  • Click Install and wait for the installation to complete. This takes several minutes.
  • When it finishes, you will see a success message in the Visual Studio Installer window.

Why does SSMS use Visual Studio Installer?

SSMS 22 is built on the Visual Studio shell. The installer handles dependency management and updates through the same mechanism as Visual Studio itself.

Installer requires administrator permissions?

SSMS requires admin rights to install. Right-click vs_SSMS.exe and select Run as administrator if you see a permissions error.

Help me troubleshoot my SSMS 22 installation

Connect SSMS to your SQL Server

With both tools installed, you can now connect SSMS to your local SQL Server instance and confirm everything is working.

  • Press the Windows key, type SSMS, and press Enter to open SQL Server Management Studio.
  • The Connect to Server dialog appears automatically on first launch.
  • In the Server name field, type localhost\SQLEXPRESS.
  • Leave Authentication set to Windows Authentication.
  • Click Connect.

Dialog did not appear?

If the Connect to Server dialog did not appear on launch, click Connect in the Object Explorer pane on the left, then select Database Engine.

You should see Object Explorer on the left showing your server name. This confirms SSMS is connected to your local SQL Server.

Now verify the SQL Server version. Before you run the next query, predict: what version string do you expect to see in the results?

  • Click New Query in the toolbar to open a query editor window.
  • Run the following query by typing it and pressing F5:
SELECT @@VERSION;

The results pane shows a row containing "Microsoft SQL Server 2022" along with version details and your Windows edition. This confirms your database engine is running and accessible.

Connection failed or timed out?

Make sure the SQL Server service is running. Press the Windows key, type Services, and open the Services app.

Find SQL Server (SQLEXPRESS) in the list and confirm its status shows Running. If it says Stopped, right-click it and select Start.

Double-check that you typed localhost\SQLEXPRESS exactly, with a backslash (not a forward slash).

Help me connect SSMS to my local SQL Server Express instance

Your analytics environment is ready. Next up, you will load five years of donor transaction data into your database and start exploring it like a real analyst on their first day.

Load and Explore the Donor Database

In the previous step, you installed SQL Server 2022 Express and connected SSMS to your local instance. Your analytics environment is ready.

Before you can answer "Is our donor program healthy?", you need to understand what data you have, what it represents, and whether you can trust it. This is the "first hour with a new database" that interviewers ask about. In this step, you will load five years of synthetic donor data and run the disciplined exploration methodology that separates senior analysts from juniors.

In this step, get ready to:
  • Create the DonorHealth database and load synthetic donor transaction data spanning fiscal years 2020-2024.
  • Profile the data systematically using a repeatable methodology interviewers expect.
  • Validate data relationships and quality before trusting the data for analysis.
Create the database and load data

You need a database with realistic donor behaviour before you can start exploring. The setup script creates three tables and generates approximately 2,000 donors with controlled giving patterns across five fiscal years.

  • In SSMS, open a new query window by pressing Ctrl+N.
  • Paste the following script into the query editor. This creates the DonorHealth database and defines the three tables you will work with:
USE master;
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = 'DonorHealth')
    DROP DATABASE DonorHealth;
GO

CREATE DATABASE DonorHealth;
GO

USE DonorHealth;
GO

-- Donors: each person who has given
CREATE TABLE donors (
    donor_id INT PRIMARY KEY,
    first_name NVARCHAR(50),
    last_name NVARCHAR(50),
    acquisition_date DATE,
    acquisition_channel NVARCHAR(20),
    donor_tier NVARCHAR(20)
);

-- Campaigns: fundraising efforts with costs
CREATE TABLE campaigns (
    campaign_id INT PRIMARY KEY,
    campaign_name NVARCHAR(100),
    channel NVARCHAR(20),
    cost DECIMAL(10,2),
    fiscal_year INT
);

What does this code do?

This first section drops any existing DonorHealth database (so you can re-run safely), creates a fresh one, and defines two of the three tables.

  • The donors table stores one row per person, including when and how they were acquired and their giving tier.
  • The campaigns table stores each fundraising campaign with its cost and channel.
  • Continue pasting the next section below the code already in your query window. This defines the donations table and inserts all 24 campaign records:
-- Donations: individual gifts linking donors to campaigns
CREATE TABLE donations (
    donation_id INT IDENTITY(1,1) PRIMARY KEY,
    donor_id INT FOREIGN KEY REFERENCES donors(donor_id),
    campaign_id INT FOREIGN KEY REFERENCES campaigns(campaign_id),
    donation_date DATE,
    amount DECIMAL(10,2),
    fiscal_year INT,
    channel NVARCHAR(20)
);

-- Insert campaigns (4 channels x 5 fiscal years)
INSERT INTO campaigns (campaign_id, campaign_name, channel, cost, fiscal_year) VALUES
(1, 'Spring Appeal 2020', 'Direct Mail', 15000.00, 2020),
(2, 'Year-End Appeal 2020', 'Direct Mail', 18000.00, 2020),
(3, 'Digital Spring 2020', 'Digital', 5000.00, 2020),
(4, 'Gala 2020', 'Event', 25000.00, 2020),
(5, 'Spring Appeal 2021', 'Direct Mail', 16000.00, 2021),
(6, 'Year-End Appeal 2021', 'Direct Mail', 19000.00, 2021),
(7, 'Digital Spring 2021', 'Digital', 6000.00, 2021),
(8, 'Digital Fall 2021', 'Digital', 4500.00, 2021),
(9, 'Gala 2021', 'Event', 27000.00, 2021),
(10, 'Spring Appeal 2022', 'Direct Mail', 17000.00, 2022),
(11, 'Year-End Appeal 2022', 'Direct Mail', 20000.00, 2022),
(12, 'Digital Spring 2022', 'Digital', 7000.00, 2022),
(13, 'Digital Fall 2022', 'Digital', 5500.00, 2022),
(14, 'Gala 2022', 'Event', 28000.00, 2022),
(15, 'Peer to Peer 2022', 'Peer-to-Peer', 3000.00, 2022);

What does this code do?

The donations table uses foreign keys to link each gift back to a donor and a campaign. The IDENTITY(1,1) on donation_id auto-generates unique IDs for each record.

The campaign inserts cover four channels (Direct Mail, Digital, Event, Peer-to-Peer) across five fiscal years. This gives you a realistic multi-channel program to analyse.

  • Paste the remaining campaign rows below what you already have:
INSERT INTO campaigns (campaign_id, campaign_name, channel, cost, fiscal_year) VALUES
(16, 'Spring Appeal 2023', 'Direct Mail', 17500.00, 2023),
(17, 'Year-End Appeal 2023', 'Direct Mail', 21000.00, 2023),
(18, 'Digital Spring 2023', 'Digital', 8000.00, 2023),
(19, 'Digital Fall 2023', 'Digital', 6000.00, 2023),
(20, 'Gala 2023', 'Event', 30000.00, 2023),
(21, 'Peer to Peer 2023', 'Peer-to-Peer', 3500.00, 2023),
(22, 'Spring Appeal 2024', 'Direct Mail', 18000.00, 2024),
(23, 'Year-End Appeal 2024', 'Direct Mail', 22000.00, 2024),
(24, 'Digital Spring 2024', 'Digital', 9000.00, 2024);

The rest of the setup script generates approximately 2,000 donors and their donation histories using procedural logic. It controls retention rates by tier to simulate a real-world pattern you will uncover later in the project.

  • The donor generation section is long (it uses cursor-based iteration to create realistic giving patterns). Copy the complete setup.sql from the Complete Code section at the bottom of this project page and paste it into your query window, replacing everything you have so far.
  • Press F5 to execute the entire script.

What happens when this runs?

The script takes 30-90 seconds to complete. It uses a cursor to iterate through each donor and generate donations with tier-specific retention probabilities. You will see the Messages pane update as it processes.

When finished, the Messages pane displays: DonorHealth database created successfully.

You should see the success message in the Messages pane at the bottom of your query window.

Script took too long or errored out?

  • If you see a timeout or memory error, close the query window, open a new one with Ctrl+N, and re-run the script. SQL Server Express can be slow on first execution while the database files initialise.
  • If you see Database 'DonorHealth' already exists errors, make sure you pasted the complete script starting from USE master; which drops any existing database first.
  • help me debug my setup.sql execution error
Profile the data systematically

With the data loaded, you follow the disciplined exploration methodology interviewers expect. The goal is to answer: what do I have, what does it look like, and can I trust it?

  • Open a new query window by pressing Ctrl+N.
  • Start your exploration by checking the scale of your data. Paste and run this query to see row counts, date coverage, and unique donor count:
-- Start by understanding what you have
USE DonorHealth;
GO

-- Row counts per table
SELECT 'donors' AS table_name, COUNT(*) AS row_count FROM donors
UNION ALL
SELECT 'donations', COUNT(*) FROM donations
UNION ALL
SELECT 'campaigns', COUNT(*) FROM campaigns;

-- Date range and fiscal year coverage
SELECT
    MIN(donation_date) AS earliest_donation,
    MAX(donation_date) AS latest_donation,
    COUNT(DISTINCT fiscal_year) AS fiscal_years_covered
FROM donations;

-- How many unique donors gave at least once?
SELECT COUNT(DISTINCT donor_id) AS unique_donors FROM donations;
  • Press F5 to execute.

What should I see?

The Results pane shows three result grids. The first shows approximately 2,000 donor rows, approximately 8,500 donation rows, and exactly 24 campaign rows. The date range spans 2020 through 2024 across 5 fiscal years.

These numbers confirm your data loaded correctly and give you a sense of scale. This is a mid-size nonprofit database.

  • Next, profile the categorical fields and gift amounts. Clear the query window and paste this:
-- How are donors distributed across channels?
SELECT acquisition_channel, COUNT(*) AS donor_count
FROM donors
GROUP BY acquisition_channel
ORDER BY donor_count DESC;

-- How are donors distributed across tiers?
SELECT donor_tier, COUNT(*) AS donor_count
FROM donors
GROUP BY donor_tier
ORDER BY donor_count DESC;

-- Gift amount distribution (spread and central tendency)
SELECT
    MIN(amount) AS min_gift,
    MAX(amount) AS max_gift,
    AVG(amount) AS avg_gift,
    STDEV(amount) AS stdev_gift,
    COUNT(*) AS total_gifts
FROM donations;
  • Press F5 to execute.

What do these results tell you?

  • Channel distribution shows Direct Mail and Digital dominate acquisition, with Event and Peer-to-Peer as smaller channels.
  • Tier distribution shows roughly 60% Small donors, 25% Mid-Level, and 15% Major. This is a typical nonprofit shape.
  • The high STDEV relative to AVG tells you the gift amounts are widely spread. A few large gifts pull the average up. This hints that aggregate averages may be misleading.
  • Finally, check whether the critical fields have any null values. Clear the query window and paste:
-- Null checks on critical fields
-- Document nulls BEFORE analysis, not after you get weird results
SELECT
    SUM(CASE WHEN donor_id IS NULL THEN 1 ELSE 0 END) AS null_donor_id,
    SUM(CASE WHEN donation_date IS NULL THEN 1 ELSE 0 END) AS null_date,
    SUM(CASE WHEN amount IS NULL THEN 1 ELSE 0 END) AS null_amount,
    SUM(CASE WHEN channel IS NULL THEN 1 ELSE 0 END) AS null_channel
FROM donations;
  • Press F5 to execute.

All four columns should return 0. This confirms the data has no null values in the fields you will rely on for analysis.

Why check for nulls first?

In an interview, saying "I checked for nulls before running aggregates" signals discipline. A COUNT(*) includes null rows but COUNT(column) excludes them. If you did not know about nulls, your denominators could silently disagree.

Validate relationships and data quality

Profiling fields tells you what values exist. Validating relationships tells you whether the tables connect cleanly. In production databases, orphan records (foreign keys pointing to deleted parents) are common and silently corrupt joins.

  • Check for orphan records by running these two queries. Clear the query window and paste:
-- Orphan check: donations without matching donors
SELECT COUNT(*) AS orphan_donations
FROM donations d
LEFT JOIN donors dn ON d.donor_id = dn.donor_id
WHERE dn.donor_id IS NULL;

-- Orphan check: donations without matching campaigns
SELECT COUNT(*) AS orphan_campaigns
FROM donations d
LEFT JOIN campaigns c ON d.campaign_id = c.campaign_id
WHERE c.campaign_id IS NULL;
  • Press F5 to execute.

What does this pattern do?

The LEFT JOIN ... WHERE IS NULL pattern is the standard orphan detection technique. A LEFT JOIN keeps all rows from the left table. If the right table has no match, those columns come back as NULL. Filtering for those NULLs gives you records that have no parent.

Both results should return 0. This means every donation links to a valid donor and a valid campaign. Your joins will be clean in the analysis ahead.

Interview language for this step

When an interviewer asks "walk me through your first hour with a new database," your answer is this exact sequence: row counts for scale, date ranges for coverage, field profiling for value distributions, null checks for reliability, and orphan checks for relationship integrity. Describe it in that order.

Before you move on, let's confirm the database is fully loaded. What do you expect to see when you re-run the row count query?

  • Run the row count query one more time to confirm your final state:
SELECT 'donors' AS table_name, COUNT(*) AS row_count FROM donors
UNION ALL
SELECT 'donations', COUNT(*) FROM donations
UNION ALL
SELECT 'campaigns', COUNT(*) FROM campaigns;

You should see 3 rows in the result: donors with approximately 2,000 rows, donations with approximately 8,500 rows, and campaigns with exactly 24 rows.

Seeing fewer rows than expected?

  • If the donations table has 0 rows but donors has rows, the cursor-based generation section may have failed partway through. Re-run the complete setup.sql from scratch. The DROP DATABASE at the top handles cleanup automatically.
  • If you see an error about the database not existing, make sure the USE DonorHealth; line is at the top of your current query window.
  • help me figure out why my DonorHealth database has missing data

✔️ Awesome, I've got everything!

Great. Make sure you have saved your analysis queries. You will build on them in the next step.

ⓧ I'd like to double check the full code

Your analysis.sql file should contain these query sections in order. Scroll up and confirm each one is present:

  • Row counts per table (UNION ALL query).
  • Date range and fiscal year coverage.
  • Unique donor count.
  • Acquisition channel distribution.
  • Donor tier distribution.
  • Gift amount distribution (MIN, MAX, AVG, STDEV).
  • Null checks on critical fields.
  • Orphan check for donations-to-donors.
  • Orphan check for donations-to-campaigns.

Your data is loaded, profiled, and validated. You now know the scale, shape, and quality of this database. Next up, you will use this trusted data to compute the aggregate donor health metrics that leadership is waiting for.

Compute Aggregate Donor Health Metrics

In the last step, you explored the database like an analyst in their first hour with a new dataset. You know the schema, the data quality, and the relationships.

Now leadership is asking: "Is our donor program healthy? Where should we invest next year?" That question is too vague to answer directly. You need to define what "healthy" means in measurable terms and compute baseline metrics that tell the story.

In this step, get ready to:
  • Build the year-over-year donor retention query using the self-join pattern.
  • Classify donors as new vs. returning using CTE-based cohort identification.
  • Compute channel acquisition cost and review the aggregate picture.
Build the retention self-join

Donor retention rate is the single most important health metric for a nonprofit. It answers: "Of the people who gave last year, how many gave again this year?"

What is donor retention rate?

Donor retention rate is the percentage of donors from a prior year who gave again in the following year. The nonprofit sector average is approximately 43%.

A retention rate above this benchmark suggests a healthy program on the surface. Below it signals trouble.

  • In analysis.sql, scroll to the bottom of your Section 1 queries.
  • Add a section header comment and a quick baseline query to count active donors per year by pasting this code below your existing queries:
-- SECTION 2: AGGREGATE DONOR HEALTH METRICS (Step 3)
SELECT fiscal_year, COUNT(DISTINCT donor_id) AS active_donors
FROM donations
GROUP BY fiscal_year
ORDER BY fiscal_year;
  • Highlight the new query and press F5 to execute it.

You should see five rows showing the total unique donors who gave in each fiscal year from 2020 to 2024. The counts should look relatively stable.

Now for the core pattern. The self-join compares each donor's activity in one year against their activity in the next year.

  • Add the year-over-year retention query below your active donors query by pasting this code:
WITH donors_by_year AS (
    SELECT DISTINCT donor_id, fiscal_year
    FROM donations
)
SELECT
    all_prev.fiscal_year + 1 AS retention_year,
    COUNT(DISTINCT all_prev.donor_id) AS prior_year_donors,
    COUNT(DISTINCT curr.donor_id) AS retained_donors,
    CAST(
        ROUND(
            100.0 * COUNT(DISTINCT curr.donor_id) / NULLIF(COUNT(DISTINCT all_prev.donor_id), 0),
            1
        ) AS DECIMAL(5,1)
    ) AS retention_rate_pct
FROM donors_by_year all_prev
LEFT JOIN donors_by_year curr
    ON all_prev.donor_id = curr.donor_id
    AND all_prev.fiscal_year + 1 = curr.fiscal_year
WHERE all_prev.fiscal_year BETWEEN 2020 AND 2023
GROUP BY all_prev.fiscal_year
ORDER BY all_prev.fiscal_year;

What does this query do?

  • The donors_by_year CTE collapses multiple donations per donor per year into a single row using SELECT DISTINCT.
  • LEFT JOIN (not INNER JOIN) keeps all prior-year donors in the denominator, even those who did NOT return. INNER JOIN would exclude lapsed donors and inflate your rate.
  • COUNT(DISTINCT) handles donors who gave multiple times per year. Without it, you would double-count.
  • NULLIF(..., 0) in the denominator prevents a division-by-zero error if any year has zero donors.

Why LEFT JOIN instead of INNER JOIN?

This is a common interview question. If you INNER JOIN, you only see donors who appeared in BOTH years. Your denominator shrinks to only retained donors, giving you a meaningless 100% rate.

LEFT JOIN preserves all prior-year donors in the left table. When a donor did NOT give again, curr.donor_id is NULL. That NULL is what makes the denominator correct.

Before you run this, what retention rate do you expect to see? Higher than 43% (the sector average) would suggest a healthy program.

  • Highlight the retention query (from WITH to the final semicolon) and press F5.

You should see four rows (retention years 2021 through 2024) with a retention_rate_pct averaging approximately 44%. That is slightly above the sector average. On the surface, the program looks healthy.

Seeing a division by zero error?

Make sure you included NULLIF(COUNT(DISTINCT all_prev.donor_id), 0) in the denominator. Without NULLIF, SQL Server will throw an error if any year has zero prior donors.

Also confirm your WHERE clause filters all_prev.fiscal_year BETWEEN 2020 AND 2023 (not 2024, since there is no 2025 to compare against).

help me debug my retention self-join query

Classify donors as new vs. returning and compute growth

Retention alone does not tell you where your donor base is headed. You need to separate new donors (first-time givers) from returning donors (repeat givers) to understand whether growth is coming from acquisition or loyalty.

  • Add the donor classification query below your retention query by pasting this code:
WITH donors_by_year AS (
    SELECT DISTINCT donor_id, fiscal_year
    FROM donations
),
first_year AS (
    SELECT donor_id, MIN(fiscal_year) AS first_fy
    FROM donors_by_year
    GROUP BY donor_id
),
yearly_stats AS (
    SELECT
        dy.fiscal_year,
        COUNT(DISTINCT dy.donor_id) AS total_donors,
        COUNT(DISTINCT CASE WHEN fy.first_fy = dy.fiscal_year THEN dy.donor_id END) AS new_donors,
        COUNT(DISTINCT CASE WHEN fy.first_fy < dy.fiscal_year THEN dy.donor_id END) AS returning_donors
    FROM donors_by_year dy
    JOIN first_year fy ON dy.donor_id = fy.donor_id
    GROUP BY dy.fiscal_year
)
SELECT
    ys.fiscal_year,
    ys.total_donors,
    ys.new_donors,
    ys.returning_donors,
    LAG(ys.total_donors) OVER (ORDER BY ys.fiscal_year) AS prior_year_donors,
    ys.total_donors - LAG(ys.total_donors) OVER (ORDER BY ys.fiscal_year) AS net_change
FROM yearly_stats ys
ORDER BY ys.fiscal_year;

What does this query do?

  • The first_year CTE uses MIN(fiscal_year) to identify each donor's acquisition year from their actual giving history, not from a CRM field that might be stale.
  • CASE WHEN first_fy = fiscal_year inside COUNT(DISTINCT) counts only donors whose first gift year matches the current row's year. This is the CASE-inside-aggregate pattern.
  • LAG(ys.total_donors) OVER (ORDER BY ys.fiscal_year) pulls the previous year's donor count into the current row, so you can compute net change without a self-join.

What about reactivated donors?

A reactivated donor is someone who gave in 2020, skipped 2021-2022, then gave again in 2023. They are neither "new" (they have prior history) nor "retained" (they lapsed before returning). This query's returning_donors column captures anyone with a prior gift year, whether they were continuously active or reactivated after a gap.

  • Highlight the classification query and press F5.

You should see five rows showing total donors, new donors, and returning donors for each fiscal year. The net_change column shows whether the donor base grew or shrank year over year. The first row's prior_year_donors will be NULL because there is no year before 2020 in the data.

Seeing NULL for all LAG values?

Make sure your ORDER BY ys.fiscal_year is inside the OVER() clause, not as a trailing ORDER BY on the outer SELECT. The LAG window function needs an explicit ordering to know which row is "previous."

help me fix my LAG window function query

Compute channel acquisition cost and review the aggregate

The final dimension of donor health is acquisition efficiency. How much does it cost to bring in a new donor through each channel? Combined with the retention and growth data, this completes the aggregate picture.

  • Add the average gift size query below your classification query by pasting this code:
SELECT
    fiscal_year,
    ROUND(AVG(amount), 2) AS avg_gift,
    ROUND(SUM(amount), 2) AS total_revenue,
    COUNT(*) AS total_gifts
FROM donations
GROUP BY fiscal_year
ORDER BY fiscal_year;
  • Highlight the query and press F5.

You should see five rows with the average gift amount, total revenue, and gift count for each fiscal year. The average gift should look relatively steady across years.

What does this tell us?

Average gift and total revenue per year are the "value" dimension of donor health. If retention is stable AND average gift is stable, leadership might conclude the program is fine. Keep this number in mind for the next step.

  • Now add the channel acquisition cost query below by pasting this code:
SELECT
    c.channel,
    d.fiscal_year,
    COUNT(DISTINCT don.donor_id) AS new_donors,
    SUM(c.cost) / NULLIF(COUNT(DISTINCT don.donor_id), 0) AS cost_per_donor
FROM donations d
JOIN donors don ON d.donor_id = don.donor_id
JOIN campaigns c ON d.campaign_id = c.campaign_id
WHERE d.fiscal_year = YEAR(don.acquisition_date)
GROUP BY c.channel, d.fiscal_year
ORDER BY d.fiscal_year, c.channel;

What does this query do?

  • The three-way JOIN connects donations to their donor record and their campaign record, linking the cost data to the acquisition event.
  • WHERE d.fiscal_year = YEAR(don.acquisition_date) filters to only acquisition-year donations. This ensures you are counting new donors for each channel, not repeat donors.
  • SUM(c.cost) / NULLIF(..., 0) divides total campaign spend by new donors acquired, giving cost per acquisition. NULLIF again protects against division by zero.
  • Highlight the channel acquisition cost query and press F5.

You should see rows for each channel-year combination showing how many new donors were acquired and the cost per donor for each channel.

  • Save analysis.sql by pressing Ctrl+S.

Getting zero new donors for a channel?

Check your WHERE clause: d.fiscal_year = YEAR(don.acquisition_date) must reference the donors table's acquisition_date, not donation_date. These are different columns.

help me debug my channel acquisition cost query

Now step back and look at the aggregate picture you have assembled. Overall retention is approximately 44%. Donor count is relatively stable. Average gift is steady. Acquisition costs vary by channel but nothing alarming.

If you reported these numbers to leadership, they would conclude the program is healthy. But the aggregate is about to lie.

✔️ Awesome, I've got everything!

Great. Confirm you have saved analysis.sql with all five Section 2 queries in place.

ⓧ I'd like to double check the full code

Scroll through your analysis.sql file and confirm the following queries appear in order after your Section 1 exploration queries:

  • The -- SECTION 2: AGGREGATE DONOR HEALTH METRICS (Step 3) comment header.
  • Active donors per year query (COUNT(DISTINCT donor_id) grouped by fiscal_year).
  • Retention self-join (donors_by_year CTE with LEFT JOIN on fiscal_year + 1).
  • Donor classification query (three CTEs: donors_by_year, first_year, yearly_stats with LAG in the final SELECT).
  • Average gift size per year (AVG(amount), SUM(amount) grouped by fiscal_year).
  • Channel acquisition cost (three-way JOIN with WHERE d.fiscal_year = YEAR(don.acquisition_date)).

You have built the aggregate view of donor health. Retention at 44%, stable donor counts, steady gift sizes, and reasonable acquisition costs. Any analyst presenting only these numbers would say the program is on track. In the next step, you will segment this data and discover what the aggregate is hiding.

Segment the Data and Find the Hidden Problem

Your aggregate metrics from Step 3 paint a reassuring picture. Retention sits at ~44%, donor counts are stable, and average gift size is steady. If you handed this to a VP, they would nod and move on.

But aggregate metrics almost always lie. The most critical analytical skill in this role is knowing when to segment and how to uncover problems hiding behind healthy headline numbers. In this step, you will break the aggregate apart and find what it is hiding.

In this step, get ready to:
  • Segment retention by donor tier using CASE-inside-aggregate and expose a hidden churn problem.
  • Compute dollar retention alongside donor retention and see how the two metrics tell different stories.
  • Analyze channel retention and lifetime value to reframe acquisition cost as investment.
Segment retention by donor tier

Your overall retention rate of ~44% is a single number averaging across all donors. But what if different groups retain at wildly different rates?

The CASE-inside-aggregate pattern lets you break a single metric into segments without writing separate queries for each group. You will apply it to the same self-join pattern from Step 3, but now grouped by donor_tier.

Before you run this query, make a prediction: do you think Small, Mid-Level, and Major donors all retain at roughly the same ~44% rate? Or do you think one group is significantly different?

  • In analysis.sql, scroll to the bottom of your Section 2 queries and add the following Section 3 header comment and segmented retention query:
-- SECTION 3: SEGMENTED ANALYSIS (Step 4)
-- 3a. Retention rate by donor tier (CASE-INSIDE-AGGREGATE SEGMENTATION)
-- Interview defense: tier is assigned from the donors table (prior-year tier)
-- because we are measuring whether THAT SEGMENT retained, not what
-- the donor became after they were retained.
WITH donors_by_year AS (
    SELECT DISTINCT d.donor_id, d.fiscal_year, don.donor_tier
    FROM donations d
    JOIN donors don ON d.donor_id = don.donor_id
)
SELECT
    all_prev.donor_tier,
    all_prev.fiscal_year AS from_year,
    all_prev.fiscal_year + 1 AS to_year,
    COUNT(DISTINCT all_prev.donor_id) AS prior_year_donors,
    COUNT(DISTINCT curr.donor_id) AS retained_donors,
    CAST(
        ROUND(
            100.0 * COUNT(DISTINCT curr.donor_id) / NULLIF(COUNT(DISTINCT all_prev.donor_id), 0),
            1
        ) AS DECIMAL(5,1)
    ) AS retention_rate_pct
FROM donors_by_year all_prev
LEFT JOIN donors_by_year curr
    ON all_prev.donor_id = curr.donor_id
    AND all_prev.fiscal_year + 1 = curr.fiscal_year
WHERE all_prev.fiscal_year BETWEEN 2020 AND 2023
GROUP BY all_prev.donor_tier, all_prev.fiscal_year
ORDER BY all_prev.donor_tier, all_prev.fiscal_year;

What does this query do?

This is the same self-join retention pattern from Step 3, with one critical change: the CTE now joins donations to donors to pull in donor_tier.

  • GROUP BY all_prev.donor_tier breaks the single retention number into three separate rates: one per tier.
  • Tier is assigned from the prior year (the LEFT side of the join). You are measuring whether that segment retained, not what the donor became after they gave again.
  • NULLIF still protects each segment's denominator independently. A segment with zero prior-year donors returns NULL instead of a division error.
  • Highlight the query (from WITH through the semicolon) and click Execute.

Look at the retention_rate_pct column for each tier. The aggregate story just fell apart. Small donors retain at approximately 48%. Major donors retain at approximately 65%. But Mid-Level donors retain at only approximately 28%.

Why does the aggregate look healthy?

Small donors make up 60% of the donor base. Their ~48% retention rate dominates the overall average, pulling it up to ~44% and masking the mid-level problem entirely.

This is the analytical trap interviewers test for: a headline metric that looks fine while an important segment is in crisis. The ability to ask "what does this number look like when I break it by segment?" separates a senior analyst from a Director.

You have found the hidden problem. Now quantify why it matters. Mid-level donors ($100-$999) might contribute a disproportionate share of total revenue.

  • Add the revenue share query below your segmented retention query in analysis.sql:
-- 3c. Revenue share by donor tier (quantifies WHY mid-level churn matters)
-- Interview note: use SUM() OVER () window function for percentage of total
SELECT
    don.donor_tier,
    COUNT(DISTINCT d.donor_id) AS donor_count,
    ROUND(SUM(d.amount), 2) AS total_revenue,
    CAST(
        ROUND(
            100.0 * SUM(d.amount) / SUM(SUM(d.amount)) OVER (),
            1
        ) AS DECIMAL(5,1)
    ) AS revenue_share_pct
FROM donations d
JOIN donors don ON d.donor_id = don.donor_id
GROUP BY don.donor_tier
ORDER BY total_revenue DESC;

What does this query do?

  • SUM(SUM(d.amount)) OVER () is a window function nested inside an aggregate. The inner SUM computes revenue per tier (because of GROUP BY). The outer SUM() OVER () totals across all tiers.
  • Dividing one by the other gives each tier's percentage of total revenue without needing a subquery or a second pass.
  • Highlight and execute the revenue share query.

The results show that Mid-Level donors contribute a disproportionate share of revenue relative to their donor count. Their 28% retention rate is not just a people problem. It is a revenue problem.

Getting a syntax error?

  • Make sure you have USE DonorHealth; at the top of your file. If the database context changed, SSMS will look for tables in the wrong database.
  • Verify that you highlighted only the query you want to run (from the comment or WITH down to the semicolon). Running multiple queries at once can cause unexpected errors.

Why is my segmented retention query returning an error?

Compute dollar retention vs. donor retention

Donor retention counts people. But not all people represent equal revenue. Dollar retention asks a different question: what percentage of last year's renewable revenue came back this year?

When higher-value donors churn at a different rate than lower-value ones, the two metrics diverge. That divergence is the signal a budget conversation needs.

  • Add the dollar retention query below the revenue share query in analysis.sql:
-- 3b. Dollar retention vs donor retention (DUAL-METRIC COMPARISON)
-- Interview defense: dollar retention diverges from donor retention when
-- higher-value donors churn at a different rate than lower-value ones.
WITH yearly_giving AS (
    SELECT
        d.donor_id,
        don.donor_tier,
        d.fiscal_year,
        SUM(d.amount) AS total_given
    FROM donations d
    JOIN donors don ON d.donor_id = don.donor_id
    GROUP BY d.donor_id, don.donor_tier, d.fiscal_year
)
SELECT
    prev.fiscal_year AS from_year,
    prev.fiscal_year + 1 AS to_year,
    CAST(
        ROUND(
            100.0 * COUNT(DISTINCT curr.donor_id) / NULLIF(COUNT(DISTINCT prev.donor_id), 0),
            1
        ) AS DECIMAL(5,1)
    ) AS donor_retention_pct,
    CAST(
        ROUND(
            100.0 * SUM(CASE WHEN curr.donor_id IS NOT NULL THEN prev.total_given ELSE 0 END)
            / NULLIF(SUM(prev.total_given), 0),
            1
        ) AS DECIMAL(5,1)
    ) AS dollar_retention_pct,
    SUM(CASE WHEN curr.donor_id IS NULL THEN prev.total_given ELSE 0 END) AS revenue_lost_to_churn
FROM yearly_giving prev
LEFT JOIN yearly_giving curr
    ON prev.donor_id = curr.donor_id
    AND prev.fiscal_year + 1 = curr.fiscal_year
WHERE prev.fiscal_year BETWEEN 2020 AND 2023
GROUP BY prev.fiscal_year
ORDER BY prev.fiscal_year;

What does this query do?

  • The CTE yearly_giving aggregates each donor's total giving per fiscal year. This gives you a single row per donor per year with their dollar value.
  • donor_retention_pct counts how many people returned. dollar_retention_pct sums the prior-year giving of only those who returned, divided by total prior-year giving.
  • revenue_lost_to_churn sums what churned donors gave last year. This is the dollar amount that walked out the door.
  • Highlight and execute the dollar retention query.

Compare the donor_retention_pct and dollar_retention_pct columns. Dollar retention is significantly worse than donor retention because mid-level donors carry disproportionate revenue and they are the ones leaving.

When do donor retention and dollar retention diverge?

The two metrics diverge whenever higher-value donors churn at a different rate than lower-value ones. If all tiers churned equally, dollar retention would match donor retention exactly.

In an interview, explain which metric matters more for budget conversations: dollar retention. A board does not approve budgets based on people counts. They approve budgets based on revenue impact.

Seeing the same number for both retention columns?

  • Make sure your CTE aggregates at the donor + fiscal_year level with SUM(d.amount) AS total_given. If you accidentally used the per-transaction amount directly, the dollar calculation will be skewed.
  • Verify the JOIN condition uses prev.fiscal_year + 1 = curr.fiscal_year. A typo here breaks the year-over-year matching.

Help me debug my dollar retention query.

Analyze channel retention and lifetime value

You now know mid-level donors are churning and it is hurting revenue. The next question a Director would ask: which acquisition channels produce donors that stick around?

A channel with a high acquisition cost but strong retention can still be a better investment than a cheap channel that produces one-time donors. Lifetime value reframes the acquisition spend conversation entirely.

  • Add the channel retention query below your dollar retention query in analysis.sql:
-- 3d. Retention rate by acquisition channel
WITH donors_by_year AS (
    SELECT DISTINCT d.donor_id, d.fiscal_year, don.acquisition_channel
    FROM donations d
    JOIN donors don ON d.donor_id = don.donor_id
)
SELECT
    all_prev.acquisition_channel,
    COUNT(DISTINCT all_prev.donor_id) AS prior_year_donors,
    COUNT(DISTINCT curr.donor_id) AS retained_donors,
    CAST(
        ROUND(
            100.0 * COUNT(DISTINCT curr.donor_id) / NULLIF(COUNT(DISTINCT all_prev.donor_id), 0),
            1
        ) AS DECIMAL(5,1)
    ) AS retention_rate_pct
FROM donors_by_year all_prev
LEFT JOIN donors_by_year curr
    ON all_prev.donor_id = curr.donor_id
    AND all_prev.fiscal_year + 1 = curr.fiscal_year
WHERE all_prev.fiscal_year BETWEEN 2020 AND 2023
GROUP BY all_prev.acquisition_channel
ORDER BY retention_rate_pct DESC;

What does this query do?

  • This is the same self-join retention pattern again, now grouped by acquisition_channel instead of donor_tier.
  • The result shows which channels produce loyal donors and which produce one-time givers, regardless of how much the channel cost to run.
  • Highlight and execute the channel retention query.

Note which channels have the highest and lowest retention rates. Keep these in mind for the lifetime value comparison next.

  • Add the lifetime value query below the channel retention query:
-- 3e. Simplified lifetime value by channel
-- Interview defense: LTV reframes acquisition cost from "expense" to "investment"
WITH donor_stats AS (
    SELECT
        don.acquisition_channel,
        d.donor_id,
        COUNT(DISTINCT d.fiscal_year) AS years_active,
        SUM(d.amount) AS total_giving,
        AVG(d.amount) AS avg_gift,
        COUNT(*) AS gift_count
    FROM donations d
    JOIN donors don ON d.donor_id = don.donor_id
    GROUP BY don.acquisition_channel, d.donor_id
)
SELECT
    acquisition_channel,
    COUNT(*) AS total_donors,
    ROUND(AVG(total_giving), 2) AS avg_lifetime_giving,
    ROUND(AVG(avg_gift), 2) AS avg_gift_size,
    ROUND(AVG(CAST(years_active AS FLOAT)), 1) AS avg_years_active,
    ROUND(AVG(CAST(gift_count AS FLOAT)), 1) AS avg_gifts_per_donor
FROM donor_stats
GROUP BY acquisition_channel
ORDER BY avg_lifetime_giving DESC;

What does this query do?

  • The CTE donor_stats computes per-donor lifetime metrics: how many years they gave, their total giving, average gift, and gift count.
  • The outer query averages these per-donor stats by acquisition channel. This tells you which channels produce donors that stick around and give repeatedly vs. which produce one-and-done donors.
  • CAST(years_active AS FLOAT) ensures the AVG returns a decimal rather than integer division.
  • Highlight and execute the lifetime value query.
  • Save analysis.sql by pressing Ctrl+S.

Compare avg_lifetime_giving across channels. A channel with higher acquisition cost (from Step 3) but higher lifetime giving is the better investment. This is the interview defense point: acquisition cost alone is misleading without retention and LTV context.

Why LTV reframes acquisition spend

A channel with $80 acquisition cost and 60% retention beats a channel with $20 cost and 15% retention. The cheap channel looks efficient on a cost-per-donor spreadsheet. But most of those donors never give again, so the actual cost-per-retained-dollar is far higher.

In an interview, explain that you evaluate acquisition channels by LTV-to-cost ratio, not cost alone. This is the difference between reporting on spend and making strategic recommendations about spend.

Channel LTV numbers look identical?

  • Check that the CTE groups by both don.acquisition_channel and d.donor_id. Missing the donor_id grouping collapses all donors into one row per channel.
  • Verify the outer query groups only by acquisition_channel. Including donor_id in the outer GROUP BY would return one row per donor instead of one row per channel.

Help me fix my lifetime value query.

Now confirm the full picture by running your segmented retention query one more time. Before you do, predict: which tier's retention rate should concern you the most given the revenue share data?

  • Scroll up to the segmented retention query (3a) and execute it again.

Mid-Level donors retain at approximately 28%. That is dramatically below the overall 44%. Combined with your dollar retention results showing revenue loss is worse than headcount loss, you have a clear analytical finding: the donor program has a mid-level retention crisis hiding behind healthy aggregate numbers.

✔️ Awesome, I've got everything!

Double check you have saved analysis.sql. Your file should now have Section 1 (data exploration), Section 2 (aggregate metrics), and Section 3 (segmented analysis) with five queries: retention by tier, dollar retention, revenue share, channel retention, and channel LTV.

ⓧ I'd like to double check the full code

Your analysis.sql file is now over 30 queries long. Scroll through and confirm each section is present in order:

  • USE DonorHealth; and GO at the top.
  • Section 1 comment header followed by data exploration queries (row counts, date range, field profiling, null checks, orphan checks).
  • Section 2 comment header followed by aggregate metrics (active donors, retention self-join, new vs returning, avg gift, channel acquisition cost).
  • Section 3 comment header followed by segmented analysis: query 3a (retention by tier), 3c (revenue share), 3b (dollar retention), 3d (channel retention), 3e (channel LTV).

You have uncovered the hidden story in this data. The aggregate said "healthy." The segmented view says "mid-level donors are leaving and taking disproportionate revenue with them." Next up, you will turn this analytical finding into a visual dashboard that communicates the story to leadership.

Build the Executive Dashboard in Power BI

Your SQL analysis uncovered a critical finding hiding in the aggregate: mid-level donors are churning at nearly double the rate of other segments, and the overall 44% retention rate masks this problem. Now you need to communicate that finding in a format that drives a decision, not just displays data.

In this step, you will build an executive-ready dashboard in Power BI that connects directly to your SQL Server database and visualizes the donor health story. This produces the portfolio artifact that demonstrates your ability to translate analytical findings into strategic visual communication.

In this step, get ready to:
  • Connect Power BI Desktop to your local SQL Server and import the donor data.
  • Create DAX measures for the key KPIs and build a row of Card visuals.
  • Build the story visuals that reveal the mid-level churn problem and channel performance.
Connect Power BI to SQL Server and import data

Power BI needs a live connection to your database to pull in the donor tables you have been querying. You will use the native SQL Server connector, which works seamlessly with your local Express instance.

  • Switch to Power BI Desktop.
  • Click Get data on the Home ribbon tab.
  • Select SQL Server database from the list and click Connect.
  • In the Server field, enter:
localhost\SQLEXPRESS
  • In the Database (optional) field, enter:
DonorHealth
  • Leave Data Connectivity mode set to Import.
  • Click OK.

Why Import mode?

Import mode loads a snapshot of your data into the Power BI file itself. This means the dashboard works even when SQL Server is not running, making it portable for interviews and presentations.

  • In the Navigator window that appears, check the boxes next to all three tables: campaigns, donations, and donors.
  • Click Load.

Power BI imports the data. You should see the three table names appear in the Data pane on the right side of the screen.

  • Click the Model icon on the far left sidebar (three connected boxes) to open Model view.
  • Verify you see lines connecting donors to donations (via donor_id) and campaigns to donations (via campaign_id).

What am I seeing in Model view?

The relationship lines show Power BI detected the foreign keys you defined in your SQL schema. These relationships let Power BI propagate filters between tables automatically.

When you place donor_tier from the donors table on a chart axis, Power BI uses the relationship to filter donations to only that tier's transactions.

Don't see relationship lines?

If Power BI did not auto-detect the relationships, click Manage relationships on the Home ribbon in Model view. Click New, select donations and donors, and map donor_id to donor_id. Repeat for campaigns using campaign_id.

Help me set up table relationships in Power BI

Create DAX measures and build the KPI layer

DAX measures are formulas that compute values dynamically based on the filters applied by your visuals. You will create four measures that summarize donor health at a glance.

  • Click the Report icon on the far left sidebar (the bar chart icon) to return to Report view.
  • In the Data pane on the right, click on the donations table to select it.
  • Click New measure in the Home ribbon tab (in the Calculations group).
  • In the formula bar that appears at the top, replace the placeholder text with this DAX formula:
Total Donors = DISTINCTCOUNT(donations[donor_id])
  • Press Enter to confirm the measure.
  • With donations still selected in the Data pane, click New measure again and enter:
Total Revenue = SUM(donations[amount])
  • Press Enter.
  • Click New measure and enter:
Average Gift Size = AVERAGE(donations[amount])
  • Press Enter.
  • Click New measure one more time and paste this retention formula:
Overall Retention Rate = 
VAR LatestYear = MAX(donations[fiscal_year])
VAR PriorYearIDs = 
    CALCULATETABLE(
        VALUES(donations[donor_id]),
        REMOVEFILTERS(donations[fiscal_year]),
        donations[fiscal_year] = LatestYear - 1
    )
VAR CurrentYearIDs = 
    CALCULATETABLE(
        VALUES(donations[donor_id]),
        REMOVEFILTERS(donations[fiscal_year]),
        donations[fiscal_year] = LatestYear
    )
VAR RetainedCount =
    COUNTROWS(INTERSECT(CurrentYearIDs, PriorYearIDs))
VAR PriorCount = COUNTROWS(PriorYearIDs)
RETURN DIVIDE(RetainedCount, PriorCount, 0)
  • Press Enter to confirm.

What does this retention measure do?

This is the same self-join logic you wrote in SQL, translated into DAX. It finds all donors who gave in the prior year, all donors who gave in the current year, and uses INTERSECT to count how many appear in both.

REMOVEFILTERS(donations[fiscal_year]) clears any year filter coming from a visual before applying the explicit year. This makes the measure context-aware: when placed in a bar chart grouped by donor tier, it automatically computes retention for each tier.

Now add a Card visual for each KPI across the top of your canvas.

  • Click on an empty area of the report canvas.
  • In the Visualizations pane, click the Card icon (a rectangle with a large number).
  • From the Data pane, drag Total Donors into the card's Fields well.
  • Resize the card and position it in the top-left of the canvas.
  • Repeat this process three more times to create cards for Total Revenue, Overall Retention Rate, and Average Gift Size.
  • Arrange all four cards in a row across the top of the canvas.

You should see four KPI cards displayed across the top of your report. The Overall Retention Rate card should show a decimal value around 0.44 (44%).

Format the retention rate as a percentage

Click the Overall Retention Rate measure in the Data pane, then in the Measure tools ribbon tab, change the Format dropdown to Percentage. The card will update to show 44% instead of 0.44.

Card showing blank or an error?

Make sure you selected the donations table before creating each measure. If a measure ended up under the wrong table, right-click it in the Data pane, select Move to table, and choose donations.

Help me fix my DAX measure in Power BI

Build the story visuals

The KPI cards show the headline numbers. Now you need the visuals that tell the story: where the aggregate is masking a problem, how retention trends over time, and which channels deliver the best long-term donors.

  • Click on an empty area of the canvas below your KPI cards.
  • In the Visualizations pane, click Clustered bar chart.
  • From the Data pane, drag donor_tier (from the donors table) into the Y-axis well.
  • Drag Overall Retention Rate (from the donations table measures) into the X-axis well.

The bar chart should immediately reveal the problem you found in SQL: the Mid-Level bar is dramatically shorter than Small and Major. This is the key visual that makes the hidden churn problem impossible to ignore.

Why does this chart work automatically?

When donor_tier is on the axis, Power BI filters the data context for each bar. Your retention measure uses REMOVEFILTERS only on fiscal_year, so the tier filter from the relationship still applies. Each bar computes retention for just that tier's donors.

  • Click on an empty area of the canvas next to the bar chart.
  • In the Visualizations pane, click Line chart.
  • Drag fiscal_year (from donations) into the X-axis well.
  • Drag Overall Retention Rate into the Y-axis well.

You should see a line showing the retention trend across fiscal years 2021 through 2024 (retention requires a prior year, so 2020 has no value).

Right-click the X-axis to remove hierarchy

If Power BI expands fiscal_year into a date hierarchy (Year, Quarter, Month), right-click fiscal_year in the X-axis well and select the column name directly (not the hierarchy). This ensures each point on the line represents one fiscal year.

  • Click on an empty area below or beside your existing charts.
  • In the Visualizations pane, click Table (the grid icon).
  • Drag the following fields into the Columns well, in this order: acquisition_channel (from donors), Total Donors, Overall Retention Rate, Total Revenue, and Average Gift Size.

The table shows a channel performance scorecard. You can compare which channels produce donors that stick around versus which channels produce one-time donors.

  • Click the Insert tab on the ribbon and select Text box.
  • Type Donor Health Diagnostic: FY2020-FY2024 into the text box.
  • Increase the font size to 18 or larger and bold it.
  • Position the text box at the very top of the canvas, above your KPI cards.
  • Select each visual one at a time and adjust the colors for consistency. In the Format pane (paint roller icon), expand Data colors or Columns to apply a consistent color scheme across your charts.
  • Save your report by pressing Ctrl+S. Name the file DonorHealthDiagnostic.pbix and save it to your Desktop.

Before you look at the finished result, think about this: does the bar chart make the mid-level churn problem obvious at a glance, without needing to read any numbers?

Look at your completed dashboard. You should see a title across the top, four KPI cards in a row, a clustered bar chart where the Mid-Level bar is visibly shorter than the others, a line chart showing the retention trend, and a table showing channel performance metrics.

What should I see?

The clustered bar chart is the centrepiece. The Mid-Level bar should show roughly 28% retention compared to about 48% for Small and 65% for Major. That visual gap is what makes the executive brief landing: the audience immediately sees the problem without reading a table of numbers.

The line chart shows overall retention holding relatively steady year over year (around 44%), which illustrates how the aggregate hides the segment-level problem.

Bar chart not showing different values per tier?

Make sure donor_tier is coming from the donors table (not manually typed) and that the relationship between donors and donations exists in Model view. Without the relationship, Power BI cannot filter donations by tier.

Help me fix my Power BI bar chart that shows the same value for every tier

Your dashboard is complete. You now have a portfolio-ready artifact that tells a story: the headline numbers look fine, but the segmented bar chart reveals a critical problem hiding underneath. Next up, the Secret Mission challenges you to write the executive brief that turns this visual story into a strategic recommendation.

Secret mission

Write the Executive Brief

Your dashboard shows the data. But a VP of Development doesn't open Power BI files. They read one-page briefs that tell them what to do. Can you structure your analytical findings into a strategic recommendation that drives a decision?

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally using free tools, so there are no ongoing costs.

Resources you used:

  • DonorHealth database on localhost\SQLEXPRESS.
  • SQL files: setup.sql and analysis.sql.
  • Power BI report: DonorHealthDiagnostic.pbix.
  • Executive brief: executive_brief.txt.
  • SQL Server 2022 Express (installed in Step 1).
  • SSMS 22 (installed in Step 1).

Keep everything running

No action needed. Choose this if you plan to extend this analysis with additional queries, build more dashboard pages, or use the DonorHealth database as a practice environment for interview prep.

  • SQL Server Express continues running as a background service. It uses minimal resources when idle.
  • Your SQL files, Power BI report, and executive brief remain wherever you saved them.
  • SSMS 22 stays installed and ready for future queries.

Pause - I'll come back to this later

Stop the SQL Server service to free up memory. Your database and files stay intact for when you return.

  • Press the Windows key and type Services. Open the Services app.
  • Find SQL Server (SQLEXPRESS) in the list.
  • Right-click it and select Stop.
  • Close SSMS and Power BI Desktop.
  • To resume later, return to Services and right-click SQL Server (SQLEXPRESS), then select Start.

Delete - I don't want to use this again

Remove all project resources and the tools you installed. Follow these steps in order.

Drop the DonorHealth database
  • In SSMS, open a new query window and run this command:
USE master;
GO
DROP DATABASE DonorHealth;
GO
Delete your project files
  • Open File Explorer and navigate to the folder where you saved your project files.
  • Delete setup.sql, analysis.sql, DonorHealthDiagnostic.pbix, and executive_brief.txt.
Uninstall SQL Server 2022 Express
  • Open Settings from the Start menu.
  • Select Apps, then Installed apps.
  • Search for sql in the search box.
  • Find Microsoft SQL Server 2022 (64-bit) and select Uninstall.
  • In the SQL Server Setup wizard, select Remove.
  • On the Select Instance page, choose your SQLEXPRESS instance from the dropdown.
  • On the Select Features page, select all features to remove.
  • Click Remove to complete the uninstall.
  • After removal, refresh the Installed apps list and remove any remaining SQL Server 2022 components (such as SQL Server 2022 Setup or SQL Server Browser).
Uninstall SSMS 22
  • Press the Windows key and type Add or remove programs. Open the result.
  • Find SQL Server Management Studio in the list.
  • Select Uninstall and follow the prompts.
  • After SSMS is removed, find Microsoft Visual Studio Installer in the same list and select Uninstall.
  • Restart your computer to clear any lingering processes.

Nice Work!

Nice Work!

You just built a complete Donor Health Diagnostic from scratch. You started with a vague executive question, structured it into measurable dimensions, uncovered a critical insight hiding in aggregate data, and delivered it in a format that drives decisions.

You've learned how to:

  • Structure an ambiguous business question into a SQL-based analytical workflow, using self-joins for retention, CASE-inside-aggregate for segmentation, CTEs for cohort identification, and NULLIF for division safety.
  • Segment aggregate metrics by donor tier to expose a hidden churn problem, and compute dollar retention alongside donor retention to reveal when and why the two metrics diverge.
  • Build an executive-ready Power BI dashboard with KPI cards, retention trend charts, and a channel performance scorecard that communicates your findings visually.
  • Secret Mission: Wrote a strategic executive brief that synthesizes analytical findings into a one-page recommendation document, completing the full arc from ambiguous question to actionable insight.

Ready to quiz yourself?