Client Pricing Analytics Case Study

Analyze PBM pricing data with SQL and build a Power BI dashboard.

Introduction

30 Second Summary

Every time a pharmacy fills a prescription, behind the scenes there is a complex web of pricing agreements, coverage rules, and dispute processes. When something goes wrong, an analyst steps in to find out where the money is leaking and why.

In this project, you will build a complete analytics case study for a fictional pharmacy benefit manager (PBM) client. You will use SQL to explore pricing and appeals data, build a Power BI dashboard to visualize your findings, and publish everything to GitHub as a portfolio-ready repository.

What You'll Build

A live GitHub repository where anyone, whether a hiring manager or a recruiter, can click through and see your full analytical process from raw data to business recommendations.

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

  • A set of SQL queries that uncover pricing patterns and appeal drivers across drug categories and regions.
  • A Power BI dashboard with KPI cards, trend lines, and drill-down visuals that answer "where is the client losing money on drug pricing?"
  • A written case study README with a problem statement, approach, key findings, and actionable recommendations you can link from a resume or LinkedIn.
  • Secret Mission: Add a What-If pricing scenario page to your Power BI dashboard that models the impact of rate renegotiation.

Are there any prerequisites?

This project assumes basic familiarity with SQL (SELECT statements, WHERE clauses, GROUP BY) and Power BI Desktop (opening the app, dragging fields onto a canvas). You do not need to be an expert in either.

Before We Start

Before We Start...

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

You're about to create a complete analytics case study for a fictional PBM (Pharmacy Benefit Manager) client. By the end, you'll have a GitHub repository that proves you can take raw data, ask the right business questions, build a client-ready dashboard, and deliver clear recommendations.

Set Up Your Project Workspace

Before you can analyze any data, you need two things: a place to publish your finished work and an environment to write SQL queries.

In this step, you will create a public GitHub repository, open a browser-based SQL editor called DB Fiddle, and set up a local folder structure to organize your project files.

In this step, get ready to:
  • Create a public GitHub repository for your case study.
  • Set up a DB Fiddle workspace with MySQL v9.
  • Create a local project folder with organized subfolders.
Create a new public GitHub repository

Your GitHub repository is where you will publish the entire case study: SQL files, dashboard screenshots, and a written report.

  • Open github.com in your browser.
  • In the upper-right corner, click the + icon.
  • Select New repository from the dropdown.
  • In the Repository name box, type client-pricing-analytics.
  • In the Description box, type End-to-end pricing analytics case study for a PBM client - SQL, Power BI, and business recommendations.
  • Select Public for the repository visibility.
  • Check the Add a README file checkbox.
  • Click Create repository.

GitHub redirects you to your new repository page. You should see the repository name client-pricing-analytics at the top and a README file displayed below it.

What is a GitHub repository?

A repository is a folder that lives on GitHub's servers. It stores all your project files and tracks every change you make over time.

Making it public means anyone with the link can view your work. This is exactly what you want for a portfolio piece that hiring managers can access from your resume.

Don't see your repository page?

Make sure you clicked Create repository at the bottom of the form. If the button is greyed out, check that the repository name field is not empty and does not contain spaces.

If you see an error about the name already being taken, add a number to the end (e.g. client-pricing-analytics-2).

Help me troubleshoot my GitHub repository creation

Set up DB Fiddle

DB Fiddle is a free, browser-based SQL playground. You do not need to install anything. It lets you create tables and run queries directly in your browser.

  • Open www.db-fiddle.com in a new browser tab.
  • Find the database engine dropdown near the top of the page.
  • Select MySQL v9 from the dropdown list.

You should now see two panels: Schema SQL on the left (for creating tables and inserting data) and Query SQL on the right (for running SELECT queries). Both panels should be blank.

Why DB Fiddle over a local database?

DB Fiddle requires zero setup. You get a working MySQL environment in seconds without installing database software on your machine.

For a 50-row dataset like the one in this project, a browser-based tool is the fastest way to iterate on queries.

Not seeing MySQL v9 in the dropdown?

Make sure you are on www.db-fiddle.com (not a similar-looking site). The dropdown is located near the top of the page, above the two editor panels.

If the page does not load at all, try a different browser or clear your cache and refresh.

Help me find the MySQL v9 option on DB Fiddle

Create your local project folder

You need a folder on your machine to store SQL files, the Power BI dashboard, and screenshots before you upload them to GitHub.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal (macOS) or PowerShell (Windows) and press Enter to open it.
  • Move to your Desktop by running this command:

macOS

cd ~/Desktop

Windows

cd ~\Desktop
  • Create the project folder, move into it, and create the subfolders by running these commands:

macOS

mkdir client-pricing-analytics
cd client-pricing-analytics
mkdir data sql powerbi screenshots

Windows

mkdir client-pricing-analytics
cd client-pricing-analytics
mkdir data, sql, powerbi, screenshots
  • Confirm the folders exist by running:
ls

You should see data, sql, powerbi, and screenshots listed in the output.

What are these folders for?

  • data stores the CSV files you will export from DB Fiddle and import into Power BI.
  • sql stores your analysis queries as .sql files.
  • powerbi stores the .pbix dashboard file.
  • screenshots stores dashboard images you will embed in your README.

Not seeing all four folders?

Run ls again and check for typos. If a folder is missing, create it individually (e.g. mkdir data).

If you see an error about the client-pricing-analytics folder already existing, that means you ran the commands twice. Run cd client-pricing-analytics to move into the existing folder, then create any missing subfolders.

Help me fix my project folder setup

Your workspace is ready. You have a live GitHub repo, a working SQL editor, and an organized folder structure on your machine. Next up, you will build and load the dataset you will analyze throughout this project.

Build and Load the Dataset

Your workspace is ready. Now you need data to analyze.

Real PBM pricing data is confidential, so you will create a synthetic dataset that mirrors the structure of actual pricing and appeals data. This demonstrates a key BA skill: understanding what data you need before you can answer a business question.

In this step, get ready to:
  • Create three database tables that model PBM pricing and appeals data.
  • Load 50 claims rows and 20 appeals rows of synthetic data.
  • Export the data as CSV files for Power BI import.
Create the schema in DB Fiddle

Your dataset needs three tables. The clients table identifies which health plan the data belongs to. The claims table tracks every drug pricing transaction. The appeals table records when a claim is disputed.

  • In your DB Fiddle tab, click inside the **Schema SQL** panel on the left.
  • Paste the following CREATE TABLE statements and client data:
-- Create the three tables for PBM pricing analysis
CREATE TABLE clients (
    client_id INT PRIMARY KEY,
    client_name VARCHAR(100),
    contract_start DATE
);

CREATE TABLE claims (
    claim_id INT PRIMARY KEY,
    client_id INT,
    drug_category VARCHAR(50),
    region VARCHAR(50),
    pricing_tier VARCHAR(20),
    claim_date DATE,
    amount DECIMAL(10,2)
);

CREATE TABLE appeals (
    appeal_id INT PRIMARY KEY,
    claim_id INT,
    appeal_reason VARCHAR(100),
    outcome VARCHAR(50),
    appeal_date DATE
);

-- Insert client records
INSERT INTO clients VALUES
(1, 'Acme Health Plan', '2023-01-01'),
(2, 'Midwest Benefits Corp', '2023-06-01'),
(3, 'Pacific Care Alliance', '2023-03-15');

What does this schema do?

  • claims tracks every drug transaction with its category (Generic, Brand, Specialty, Biosimilar), region, pricing tier, date, and dollar amount.
  • appeals links back to a specific claim via claim_id and records why the claim was disputed and whether it was approved or denied.
  • clients identifies the health plan so you can filter by client later.
  • Click the **Run** button at the top of DB Fiddle.

You should see no error messages below the Schema SQL panel. The tables are now created and ready for data.

Seeing a syntax error?

  • Check that you selected **MySQL v9** from the database dropdown at the top of DB Fiddle. Other engines may not accept this syntax.
  • Make sure you copied the entire block, including the final semicolons.

Help me fix a syntax error in DB Fiddle

Insert the claims data

Now you will load 50 claims rows that span 4 drug categories, 4 regions, and 3 pricing tiers. The data covers January through July 2024 so you can analyze trends over time.

  • In the Schema SQL panel, place your cursor at the very end of the existing code (after the last semicolon).
  • Paste the first batch of claims data below your existing statements:
-- Insert claims: batch 1 of 2 (rows 1-25)
INSERT INTO claims VALUES
(1, 1, 'Generic', 'Northeast', 'Tier 1', '2024-01-10', 25.00),
(2, 1, 'Brand', 'Northeast', 'Tier 2', '2024-01-15', 150.00),
(3, 1, 'Specialty', 'Northeast', 'Tier 3', '2024-01-20', 450.00),
(4, 2, 'Generic', 'Midwest', 'Tier 1', '2024-01-22', 30.00),
(5, 2, 'Biosimilar', 'Midwest', 'Tier 2', '2024-01-25', 120.00),
(6, 3, 'Specialty', 'West', 'Tier 3', '2024-02-01', 525.00),
(7, 1, 'Brand', 'Southeast', 'Tier 2', '2024-02-05', 175.00),
(8, 2, 'Generic', 'Southeast', 'Tier 1', '2024-02-08', 22.00),
(9, 3, 'Specialty', 'West', 'Tier 3', '2024-02-12', 680.00),
(10, 1, 'Biosimilar', 'Northeast', 'Tier 2', '2024-02-15', 95.00),
(11, 2, 'Brand', 'Midwest', 'Tier 2', '2024-02-18', 210.00),
(12, 3, 'Generic', 'West', 'Tier 1', '2024-02-22', 35.00),
(13, 1, 'Specialty', 'Southeast', 'Tier 3', '2024-03-01', 390.00),
(14, 2, 'Biosimilar', 'Midwest', 'Tier 2', '2024-03-05', 140.00),
(15, 3, 'Brand', 'West', 'Tier 2', '2024-03-08', 185.00),
(16, 1, 'Generic', 'Northeast', 'Tier 1', '2024-03-12', 28.00),
(17, 2, 'Specialty', 'Midwest', 'Tier 3', '2024-03-15', 510.00),
(18, 3, 'Brand', 'West', 'Tier 2', '2024-03-18', 195.00),
(19, 1, 'Biosimilar', 'Southeast', 'Tier 2', '2024-03-22', 110.00),
(20, 2, 'Generic', 'Southeast', 'Tier 1', '2024-03-25', 20.00),
(21, 3, 'Specialty', 'West', 'Tier 3', '2024-04-01', 720.00),
(22, 1, 'Brand', 'Northeast', 'Tier 2', '2024-04-05', 160.00),
(23, 2, 'Biosimilar', 'Midwest', 'Tier 1', '2024-04-08', 85.00),
(24, 3, 'Generic', 'West', 'Tier 1', '2024-04-12', 40.00),
(25, 1, 'Specialty', 'Northeast', 'Tier 3', '2024-04-15', 475.00);
  • Paste the second batch of claims immediately below the first:
-- Insert claims: batch 2 of 2 (rows 26-50)
INSERT INTO claims VALUES
(26, 2, 'Brand', 'Southeast', 'Tier 2', '2024-04-18', 165.00),
(27, 3, 'Biosimilar', 'West', 'Tier 2', '2024-04-22', 130.00),
(28, 1, 'Generic', 'Midwest', 'Tier 1', '2024-05-01', 32.00),
(29, 2, 'Specialty', 'Southeast', 'Tier 3', '2024-05-05', 580.00),
(30, 3, 'Brand', 'West', 'Tier 3', '2024-05-08', 240.00),
(31, 1, 'Biosimilar', 'Northeast', 'Tier 1', '2024-05-12', 88.00),
(32, 2, 'Generic', 'Midwest', 'Tier 1', '2024-05-15', 27.00),
(33, 3, 'Specialty', 'West', 'Tier 3', '2024-05-18', 650.00),
(34, 1, 'Brand', 'Southeast', 'Tier 2', '2024-05-22', 180.00),
(35, 2, 'Biosimilar', 'Southeast', 'Tier 2', '2024-05-25', 115.00),
(36, 3, 'Generic', 'West', 'Tier 1', '2024-06-01', 38.00),
(37, 1, 'Specialty', 'Midwest', 'Tier 3', '2024-06-05', 490.00),
(38, 2, 'Brand', 'Northeast', 'Tier 2', '2024-06-08', 170.00),
(39, 3, 'Biosimilar', 'West', 'Tier 2', '2024-06-12', 145.00),
(40, 1, 'Generic', 'Southeast', 'Tier 1', '2024-06-15', 24.00),
(41, 2, 'Specialty', 'Midwest', 'Tier 3', '2024-06-18', 540.00),
(42, 3, 'Brand', 'West', 'Tier 2', '2024-06-22', 200.00),
(43, 1, 'Biosimilar', 'Northeast', 'Tier 1', '2024-06-25', 92.00),
(44, 2, 'Generic', 'Northeast', 'Tier 1', '2024-07-01', 29.00),
(45, 3, 'Specialty', 'West', 'Tier 3', '2024-07-05', 710.00),
(46, 1, 'Brand', 'Midwest', 'Tier 2', '2024-07-08', 155.00),
(47, 2, 'Biosimilar', 'Southeast', 'Tier 2', '2024-07-12', 105.00),
(48, 3, 'Generic', 'Midwest', 'Tier 1', '2024-07-15', 33.00),
(49, 1, 'Specialty', 'West', 'Tier 3', '2024-07-18', 690.00),
(50, 2, 'Brand', 'West', 'Tier 2', '2024-07-22', 220.00);
  • Click **Run** to rebuild the schema with your claims data loaded.
  • Switch to the **Query SQL** panel on the right and run this count check:
SELECT COUNT(*) FROM claims;

Before you click **Run**, predict: how many rows do you expect to see?

  • Click **Run** in the Query SQL panel.

You should see a single result: 50. That confirms all your claims data loaded successfully.

Seeing fewer than 50 rows?

  • Check that both INSERT INTO claims blocks are present in the Schema SQL panel. You need rows 1-25 and rows 26-50.
  • Look for a missing comma at the end of row 25. The last row of batch 1 must end with a semicolon, not a comma.
  • Make sure you did not accidentally paste batch 2 inside batch 1. Each INSERT statement must be separate.

Help me debug why my claims count is wrong in DB Fiddle

  • Now add the appeals data below your claims in the Schema SQL panel. Paste this:
-- Insert appeals linked to specific claims
INSERT INTO appeals VALUES
(1, 3, 'Formulary Error', 'Approved', '2024-02-05'),
(2, 6, 'Price Dispute', 'Approved', '2024-02-15'),
(3, 9, 'Coverage Denial', 'Denied', '2024-03-01'),
(4, 13, 'Price Dispute', 'Pending', '2024-03-12'),
(5, 17, 'Coverage Denial', 'Denied', '2024-03-25'),
(6, 21, 'Price Dispute', 'Approved', '2024-04-10'),
(7, 22, 'Formulary Error', 'Approved', '2024-04-18'),
(8, 25, 'Price Dispute', 'Denied', '2024-04-28'),
(9, 29, 'Coverage Denial', 'Pending', '2024-05-12'),
(10, 30, 'Price Dispute', 'Approved', '2024-05-20'),
(11, 33, 'Formulary Error', 'Denied', '2024-06-05'),
(12, 34, 'Price Dispute', 'Approved', '2024-06-08'),
(13, 37, 'Coverage Denial', 'Approved', '2024-06-20'),
(14, 38, 'Formulary Error', 'Pending', '2024-06-22'),
(15, 41, 'Price Dispute', 'Denied', '2024-07-01'),
(16, 42, 'Coverage Denial', 'Approved', '2024-07-08'),
(17, 43, 'Price Dispute', 'Approved', '2024-07-05'),
(18, 45, 'Formulary Error', 'Denied', '2024-07-20'),
(19, 46, 'Price Dispute', 'Pending', '2024-07-15'),
(20, 49, 'Coverage Denial', 'Approved', '2024-07-30');

What does the appeals data represent?

Each appeal links to a specific claim that was disputed. The three appeal reasons mirror real PBM scenarios:

  • Price Dispute means the pharmacy or plan thinks the reimbursement rate is wrong.
  • Formulary Error means a drug was priced under the wrong tier.
  • Coverage Denial means the plan rejected covering the drug entirely.
  • Click **Run** at the top to rebuild the schema with all data.
  • In the Query SQL panel, clear the previous query and run:
SELECT COUNT(*) FROM appeals;

You should see 20. All your appeals data is loaded.

Export data as CSV for Power BI

Power BI Desktop imports data from CSV files. You will run SELECT queries to display all rows, then copy the results into files that Power BI can read.

  • In the Query SQL panel, clear any existing query and paste:
SELECT * FROM claims;
  • Click **Run** to display all 50 claims rows in the results table below.
  • Select all the result rows (click the first cell, then Ctrl+A on Windows).
  • Copy the selected results (Ctrl+C).
  • Open a text editor (Notepad on Windows works fine).
  • Paste the results and save the file as claims.csv inside your data folder in client-pricing-analytics on your Desktop.

Tab-separated is fine

DB Fiddle results copy as tab-separated values. Power BI Desktop handles both tab-separated and comma-separated files through its Text/CSV importer, so you do not need to manually convert tabs to commas.

  • Back in DB Fiddle, clear the Query SQL panel and run:
SELECT * FROM appeals;
  • Click **Run** to display all 20 appeals rows.
  • Select all results, copy, and paste into a new text editor file.
  • Save this file as appeals.csv in the same data folder.

Confirm both files exist by opening your data folder in File Explorer. You should see claims.csv and appeals.csv listed.

File appears empty or has only headers?

  • Make sure you clicked **Run** in DB Fiddle before copying. The results table only populates after execution.
  • Try selecting results directly from the results table rather than the Query SQL panel. The results appear below the panel after you run the query.

Help me export DB Fiddle results to a CSV file

✔️ Awesome, I've got everything!

Great. Double check you have both claims.csv and appeals.csv saved in your data folder, and that your DB Fiddle tab still has all the schema and INSERT statements in the Schema SQL panel.

ⓧ I'd like to double check the full code

Your Schema SQL panel should contain these sections in order. Scroll through and confirm each is present:

  • Three CREATE TABLE statements (clients, claims, appeals).
  • INSERT INTO clients with 3 rows.
  • INSERT INTO claims batch 1 with rows 1-25.
  • INSERT INTO claims batch 2 with rows 26-50.
  • INSERT INTO appeals with 20 rows.

Your dataset is built and exported. Next up, you will write SQL queries that dig into this data and answer the business question: where is the client losing money, and what drives appeals?

Analyze the Data with SQL

Your dataset is loaded and ready to answer questions. Now it's time to ask the ones a client would actually care about: Where is the money going? Which regions cause the most pricing disputes?

SQL is the first tool you reach for as a BA to explore and understand data. These queries answer the business question before you ever open Power BI. They also become portfolio artifacts in your GitHub repo.

In this step, get ready to:
  • Write exploratory queries to understand spend patterns across categories, tiers, and time.
  • Write targeted queries to identify which regions and drug types drive the most appeals.
  • Save commented SQL files to your local project folder.
Write exploratory queries

Exploratory queries help you understand the shape of the data before you dig into specific problems. You will answer three questions: which drug categories cost the most, whether pricing tiers are significantly different, and how spend moves over time.

  • In your DB Fiddle tab, clear the Query SQL panel on the right.
  • Paste the following query to find total spend by drug category:
-- Which drug categories cost the client the most?
-- This reveals where the budget is concentrated.
SELECT drug_category, SUM(amount) AS total_spend
FROM claims
GROUP BY drug_category
ORDER BY total_spend DESC;
  • Click Run.
  • You should see 4 rows in the results panel, one for each drug category (Generic, Brand, Specialty, Biosimilar), sorted from highest to lowest spend.

What does this query do?

It groups every claim by its drug category and sums the dollar amounts. The result tells you at a glance which category consumes the most budget.

Seeing an error instead of results?

Make sure your Schema SQL panel still has your CREATE TABLE and INSERT statements, and that you clicked Run on the Schema panel first (the tables need to exist before you query them). Also check that column names match exactly: drug_category and amount.

help me fix my DB Fiddle SQL query error

  • Clear the Query SQL panel and paste this next query to find average claim amount by pricing tier:
-- Is Tier 3 significantly more expensive than other tiers?
-- Helps identify if tier-based pricing is working as expected.
SELECT pricing_tier, ROUND(AVG(amount), 2) AS avg_claim_amount
FROM claims
GROUP BY pricing_tier
ORDER BY avg_claim_amount DESC;
  • Click Run.
  • You should see 3 rows, one for each tier (Tier 1, Tier 2, Tier 3), with the average dollar amount per claim.

What does this query do?

It calculates the mean claim value for each pricing tier. If Tier 3 is dramatically higher than Tier 1, that confirms the pricing structure is tiered as expected. If the gap is small, it may signal tier misclassification.

  • Clear the Query SQL panel and paste this query to see the monthly spend trend:
-- Are costs rising, falling, or stable over time?
-- Identifies months where spend spiked or dipped.
SELECT DATE_FORMAT(claim_date, '%Y-%m') AS month,
       SUM(amount) AS monthly_spend
FROM claims
GROUP BY DATE_FORMAT(claim_date, '%Y-%m')
ORDER BY month;
  • Click Run.
  • You should see one row per month in your dataset, showing how total spend changes over time.

What does this query do?

It formats each claim date into a year-month string and sums spend per month. This is the data you will later plot as a line chart in Power BI to show the client whether costs are trending up.

Write targeted business queries

Now that you understand the data landscape, it is time to answer the specific business question: where is the client losing money on pricing, and what drives appeals? These queries connect spend patterns to appeal activity.

  • Clear the Query SQL panel and paste this query to find the appeal rate by region:
-- Which region has the most appeals relative to its claims?
-- A high appeal rate signals pricing misalignment in that region.
SELECT c.region,
       COUNT(DISTINCT c.claim_id) AS total_claims,
       COUNT(a.appeal_id) AS total_appeals,
       ROUND(COUNT(a.appeal_id) / COUNT(DISTINCT c.claim_id) * 100, 1) AS appeal_rate_pct
FROM claims c
LEFT JOIN appeals a ON c.claim_id = a.claim_id
GROUP BY c.region
ORDER BY appeal_rate_pct DESC;

Before you run this, predict: which region do you think has the highest appeal rate based on what you have seen so far?

  • Click Run.
  • You should see 4 rows (one per region) with the West region showing the highest appeal rate.

What does this query do?

It joins claims to appeals using a LEFT JOIN (so regions with zero appeals still appear), counts distinct claims and appeals per region, then calculates the percentage. A region with a high appeal rate relative to its claim volume is a red flag for the client.

Getting zero appeals for every region?

Check that your JOIN condition uses the correct column: c.claim_id = a.claim_id. If you used a different column name in your schema (like id instead of claim_id), update the query to match your table structure.

help me debug my SQL JOIN returning zero matches

  • Clear the Query SQL panel and paste this query to find the appeal rate by drug category:
-- Are certain drug types generating more pricing disputes?
-- Helps focus renegotiation efforts on problem categories.
SELECT c.drug_category,
       COUNT(DISTINCT c.claim_id) AS total_claims,
       COUNT(a.appeal_id) AS total_appeals,
       ROUND(COUNT(a.appeal_id) / COUNT(DISTINCT c.claim_id) * 100, 1) AS appeal_rate_pct
FROM claims c
LEFT JOIN appeals a ON c.claim_id = a.claim_id
GROUP BY c.drug_category
ORDER BY appeal_rate_pct DESC;
  • Click Run.
  • You should see 4 rows showing which drug categories trigger the most disputes relative to their claim volume.

What does this query do?

Same logic as the regional query, but grouped by drug category instead. If Specialty drugs have a high appeal rate, that tells the client their specialty pricing model needs attention.

  • Clear the Query SQL panel and paste this final query to find the top reasons for appeals:
-- What is the leading cause of pricing challenges?
-- Identifies whether disputes are driven by pricing, formulary, or coverage issues.
SELECT appeal_reason, COUNT(*) AS appeal_count
FROM appeals
GROUP BY appeal_reason
ORDER BY appeal_count DESC;
  • Click Run.
  • You should see 3 rows listing Price Dispute, Formulary Error, and Coverage Denial with their counts.

What does this query do?

It counts how many appeals fall into each reason bucket. The top reason tells the client where to focus their corrective action. If most appeals are Price Disputes, the pricing model itself is the issue.

Save your SQL files

Your queries are proven and working. Now save them as local files so you can upload them to GitHub later. Each file gets comments explaining what the query answers and why it matters for the client.

  • Open a text editor (Notepad on Windows).
  • Copy the following into a new file and save it as 01_exploratory_analysis.sql in your sql folder:
-- Which drug categories cost the client the most?
-- This reveals where the budget is concentrated.
SELECT drug_category, SUM(amount) AS total_spend
FROM claims
GROUP BY drug_category
ORDER BY total_spend DESC;

-- Is Tier 3 significantly more expensive than other tiers?
-- Helps identify if tier-based pricing is working as expected.
SELECT pricing_tier, ROUND(AVG(amount), 2) AS avg_claim_amount
FROM claims
GROUP BY pricing_tier
ORDER BY avg_claim_amount DESC;

-- Are costs rising, falling, or stable over time?
-- Identifies months where spend spiked or dipped.
SELECT DATE_FORMAT(claim_date, '%Y-%m') AS month,
       SUM(amount) AS monthly_spend
FROM claims
GROUP BY DATE_FORMAT(claim_date, '%Y-%m')
ORDER BY month;

What are these comments for?

The comments above each query explain what business question it answers. When a hiring manager opens this file on GitHub, they see your thinking process immediately. They do not have to reverse-engineer what each query does.

  • Create a second file and save it as 02_appeal_drivers.sql in your sql folder:
-- Which region has the most appeals relative to its claims?
-- A high appeal rate signals pricing misalignment in that region.
SELECT c.region,
       COUNT(DISTINCT c.claim_id) AS total_claims,
       COUNT(a.appeal_id) AS total_appeals,
       ROUND(COUNT(a.appeal_id) / COUNT(DISTINCT c.claim_id) * 100, 1) AS appeal_rate_pct
FROM claims c
LEFT JOIN appeals a ON c.claim_id = a.claim_id
GROUP BY c.region
ORDER BY appeal_rate_pct DESC;

-- Are certain drug types generating more pricing disputes?
-- Helps focus renegotiation efforts on problem categories.
SELECT c.drug_category,
       COUNT(DISTINCT c.claim_id) AS total_claims,
       COUNT(a.appeal_id) AS total_appeals,
       ROUND(COUNT(a.appeal_id) / COUNT(DISTINCT c.claim_id) * 100, 1) AS appeal_rate_pct
FROM claims c
LEFT JOIN appeals a ON c.claim_id = a.claim_id
GROUP BY c.drug_category
ORDER BY appeal_rate_pct DESC;

-- What is the leading cause of pricing challenges?
-- Identifies whether disputes are driven by pricing, formulary, or coverage issues.
SELECT appeal_reason, COUNT(*) AS appeal_count
FROM appeals
GROUP BY appeal_reason
ORDER BY appeal_count DESC;
  • Confirm both files exist by opening your client-pricing-analytics folder on your Desktop and checking inside the sql subfolder.
  • You should see 01_exploratory_analysis.sql and 02_appeal_drivers.sql listed.

✔️ Awesome, I've got everything!

Great. Double check you saved both files with the .sql extension (not .txt).

ⓧ I'd like to double check the full code

Your 01_exploratory_analysis.sql should contain these three queries with comments:

-- Which drug categories cost the client the most?
-- This reveals where the budget is concentrated.
SELECT drug_category, SUM(amount) AS total_spend
FROM claims
GROUP BY drug_category
ORDER BY total_spend DESC;

-- Is Tier 3 significantly more expensive than other tiers?
-- Helps identify if tier-based pricing is working as expected.
SELECT pricing_tier, ROUND(AVG(amount), 2) AS avg_claim_amount
FROM claims
GROUP BY pricing_tier
ORDER BY avg_claim_amount DESC;

-- Are costs rising, falling, or stable over time?
-- Identifies months where spend spiked or dipped.
SELECT DATE_FORMAT(claim_date, '%Y-%m') AS month,
       SUM(amount) AS monthly_spend
FROM claims
GROUP BY DATE_FORMAT(claim_date, '%Y-%m')
ORDER BY month;

Your 02_appeal_drivers.sql should contain these three queries with comments:

-- Which region has the most appeals relative to its claims?
-- A high appeal rate signals pricing misalignment in that region.
SELECT c.region,
       COUNT(DISTINCT c.claim_id) AS total_claims,
       COUNT(a.appeal_id) AS total_appeals,
       ROUND(COUNT(a.appeal_id) / COUNT(DISTINCT c.claim_id) * 100, 1) AS appeal_rate_pct
FROM claims c
LEFT JOIN appeals a ON c.claim_id = a.claim_id
GROUP BY c.region
ORDER BY appeal_rate_pct DESC;

-- Are certain drug types generating more pricing disputes?
-- Helps focus renegotiation efforts on problem categories.
SELECT c.drug_category,
       COUNT(DISTINCT c.claim_id) AS total_claims,
       COUNT(a.appeal_id) AS total_appeals,
       ROUND(COUNT(a.appeal_id) / COUNT(DISTINCT c.claim_id) * 100, 1) AS appeal_rate_pct
FROM claims c
LEFT JOIN appeals a ON c.claim_id = a.claim_id
GROUP BY c.drug_category
ORDER BY appeal_rate_pct DESC;

-- What is the leading cause of pricing challenges?
-- Identifies whether disputes are driven by pricing, formulary, or coverage issues.
SELECT appeal_reason, COUNT(*) AS appeal_count
FROM appeals
GROUP BY appeal_reason
ORDER BY appeal_count DESC;

File saved as .txt instead of .sql?

In Notepad, when you click Save As, change the Save as type dropdown from "Text Documents (*.txt)" to "All Files (*.*)". Then type the full filename including .sql at the end.

help me save a file with a .sql extension in Notepad

Your SQL analysis is complete. You now have data-backed answers to the client's business question, saved as portfolio-ready files. Next up, you will turn these findings into a visual dashboard that a client executive can understand in 30 seconds.

Build the Power BI Dashboard

Your SQL queries uncovered the pricing patterns and appeal drivers hiding in the raw data. Now it's time to make those insights visual.

A client executive needs to grasp your findings in 30 seconds. This step turns your SQL analysis into a two-page Power BI dashboard they can interact with and immediately act on.

In this step, get ready to:
  • Import your CSV data files into Power BI Desktop.
  • Build an Executive Summary page with KPIs, charts, and a region filter.
  • Build an Appeal Drivers detail page with drill-down visuals and conditional formatting.
Import data into Power BI Desktop

Power BI Desktop needs your CSV files as its data source before you can build any visuals.

  • Press the Windows key to open your search bar.
  • Type Power BI Desktop and press Enter to open it.

You should see the Power BI Desktop home screen with a blank canvas.

  • On the Home ribbon, click Get Data.
  • Select Text/CSV from the list.
  • Browse to your data folder on the Desktop and select claims.csv.
  • Click Open.
  • In the Navigator preview, click Load to import the data.

You should see a brief loading animation, then the claims table appears in the Fields pane on the right.

  • Click Get Data on the Home ribbon again.
  • Select Text/CSV and this time choose appeals.csv.
  • Click Load in the Navigator.

Both claims and appeals now appear in the Fields pane on the right side of the screen.

What is the Fields pane?

The Fields pane lists every table and column Power BI has loaded. You drag fields from here onto the canvas to build visuals. Expand a table name to see its columns.

  • Click the Model icon on the left sidebar (it looks like three connected boxes) to create a relationship between the two tables.
  • If Power BI has not auto-detected a relationship, drag claim_id from the appeals table onto claim_id in the claims table.
  • A line connecting the two tables confirms the relationship is active.
  • Click the Report icon on the left sidebar to return to the canvas view.

Don't see both tables in the Fields pane?

Make sure you clicked Load (not Transform Data) in the Navigator window. If a table is missing, repeat the Get Data process for the missing CSV file.

Help me troubleshoot why my CSV is not loading

Build the Executive Summary page

This page gives the client executive an at-a-glance view of total spend, claim volume, and appeal rates.

  • Double-click the page tab at the bottom of the canvas (it says "Page 1") and rename it to Executive Summary.
  • On the Insert ribbon, click Text box.
  • Type Client Pricing Analytics - Executive Summary and increase the font size to 20.
  • Position the text box at the top of the canvas.

You should see a bold title across the top of your page. Now you will add four KPI cards below it.

  • Click an empty area on the canvas, then in the Visualizations pane, select the Card visual (the icon with a large number).
  • From the Fields pane, expand the claims table and drag amount into the visual's Fields well.
  • Power BI automatically sums the field. This card shows Total Spend.
  • Resize the card and position it in the top-left area below the title.
  • Create a second Card visual by clicking empty canvas space and selecting Card again.
  • Drag claim_id from claims into the Fields well.
  • Click the dropdown arrow next to the field name in the Fields well and change the aggregation from Sum to Count.
  • This card now shows Total Claims. Position it next to the first card.
  • Create a third Card visual.
  • Drag claim_id from the appeals table into the Fields well and set the aggregation to Count.
  • This card shows Total Appeals. Position it next to the second card.

You now have three KPI cards across the top showing Total Spend, Total Claims, and Total Appeals. The fourth card needs a custom calculation.

What is DAX?

DAX (Data Analysis Expressions) is Power BI's formula language for custom calculations. It works like Excel formulas but is designed for relational data. You will write a short DAX measure to calculate the overall appeal rate.

  • Click the Modeling tab on the ribbon, then click New measure.
  • In the formula bar that appears at the top of the canvas, type the following DAX formula:
Appeal Rate = DIVIDE(COUNTROWS(appeals), COUNTROWS(claims), 0)

What does this formula do?

COUNTROWS(appeals) counts how many appeal records exist in the current filter context.

DIVIDE safely divides appeals by claims, returning 0 instead of an error if claims is zero. The result is a decimal representing the appeal rate (e.g., 0.40 means 40%).

  • Press Enter to confirm the measure. You should see Appeal Rate appear in the Fields pane with a calculator icon.
  • Create a fourth Card visual on the canvas.
  • Drag the Appeal Rate measure from the Fields pane into the card's Fields well.
  • Position this card next to the other three. It displays the Overall Appeal Rate.

You should now have four KPI cards in a row: Total Spend, Total Claims, Total Appeals, and Overall Appeal Rate.

  • Click empty canvas space below the cards, then select Clustered bar chart from the Visualizations pane.
  • Drag drug_category from claims into the Y-axis well.
  • Drag amount from claims into the X-axis well.
  • The chart now shows Total Spend by Drug Category. Resize it to fill the bottom-left area of the page.

You should see four horizontal bars, one for each drug category (Generic, Brand, Specialty, Biosimilar), with their spend amounts.

  • Click empty canvas space next to the bar chart, then select Line chart from the Visualizations pane.
  • Drag claim_date from claims into the X-axis well.
  • Drag amount from claims into the Y-axis well.
  • This creates a Monthly Spend Trend line chart. Resize it to fill the bottom-right area.
  • Click empty canvas space, then select Slicer from the Visualizations pane.
  • Drag region from claims into the slicer's Field well.
  • Position the slicer in the top-right corner of the page. The client can now filter the entire page by region.
  • Save your report by pressing Ctrl+S.
  • Name the file client-pricing-analytics and save it into your local powerbi folder.

Your Executive Summary page should now show 4 KPI cards at the top, a bar chart on the bottom-left, a line chart on the bottom-right, and a Region slicer in the top-right corner.

Card showing wrong value or no data?

Check the aggregation type in the Fields well. Click the dropdown arrow next to the field and verify it says Sum for amounts or Count for IDs.

If your Appeal Rate card shows a blank, make sure the relationship between claims and appeals is active in the Model view.

Help me fix my KPI card values

Build the Detail page

The second page answers a more specific question: where exactly are the appeal problems concentrated?

  • At the bottom of the canvas, click the + icon next to your existing page tab to add a new page.
  • Double-click the new page tab and rename it to Appeal Drivers.
  • Click empty canvas space, then select Stacked bar chart from the Visualizations pane.
  • Drag reason from the appeals table into the Y-axis well.
  • Drag claim_id from appeals into the X-axis well and set its aggregation to Count.
  • This chart shows Appeal Count by Reason. Resize it to fill the top half of the page.

You should see horizontal bars for each appeal reason (Price Dispute, Formulary Error, Coverage Denial), with the longest bar indicating the most common reason.

  • Click empty canvas space below the bar chart, then select Matrix from the Visualizations pane (the icon with a grid of cells).
  • Drag region from claims into the Rows well.
  • Drag drug_category from claims into the Columns well.
  • Drag the Appeal Rate measure into the Values well.

The matrix now shows appeal rates broken down by region (rows) and drug category (columns). Each cell shows the proportion of claims that were appealed for that combination.

  • In the Visualizations pane, right-click (or click the down-arrow on) Appeal Rate in the Values well.
  • Select Conditional formatting, then Background color.
  • In the dialog, change Format style to Rules.
  • Set the rule: If value is greater than or equal to 0.25, set the background color to red.
  • Click OK to apply the formatting.

Why 0.25 and not 25?

Your Appeal Rate measure returns a decimal (0.40 means 40%). So 25% is represented as 0.25 in the conditional formatting rules. Any cell at or above this threshold now turns red, instantly flagging where the client is losing the most appeals.

  • Save your report again by pressing Ctrl+S.

Before you check your work, think about what each page should show. How many KPI cards are on Page 1? What does the red highlighting reveal on Page 2?

Click the Executive Summary tab at the bottom. You should see 4 KPI cards at the top and a bar chart below them. Click the Appeal Drivers tab. You should see the stacked bar chart and a matrix table with red-highlighted cells where the appeal rate exceeds 25%.

Matrix cells not turning red?

Make sure the rule uses 0.25 as the threshold value, not 25. The measure returns a decimal, not a percentage.

Also check that the relationship between claims and appeals is active in the Model view. Without it, the matrix cannot calculate per-region appeal rates.

Help me fix my conditional formatting

Your two-page dashboard is ready. Next up, you will document your findings as a case study README and publish everything to GitHub so a hiring manager can see your work.

Document and Publish Your Case Study

Your Power BI dashboard now answers the client's core questions with visuals and drill-downs. But a dashboard file sitting on your desktop does not prove anything to a hiring manager who has never met you.

Hiring managers want to see how you think. In this step, you will write a structured case study README, capture dashboard screenshots, and publish everything to GitHub. The finished repository tells a complete story: here is the problem, here is my analysis, and here is what I recommend.

In this step, get ready to:
  • Write a case study README with findings and business recommendations.
  • Capture dashboard screenshots and upload all project files to GitHub.
  • Embed screenshots in your README so the project is visual without opening the .pbix file.
Write the first half of your case study README

Your README is the front page of your repository. It needs to tell a visitor exactly what problem you solved and how you approached it.

  • Switch back to your client-pricing-analytics repository in your browser.
  • Click README.md in the file list to view it.
  • Click the pencil icon in the upper right corner to open the file editor.
  • Select all existing content and delete it.
  • Paste the following Markdown content into the editor:
# Client Pricing Analytics Case Study

## Project Overview

An end-to-end analytics case study analyzing drug pricing and appeals data for a fictional PBM (Pharmacy Benefit Manager) client. This project demonstrates SQL data analysis, Power BI visualization, and business storytelling skills.

## Business Problem

The client needs to understand where they are losing money on drug pricing and what drives member appeals. Specifically:
- Which drug categories account for the most spend relative to their claims volume?
- Which regions generate the most pricing disputes?
- What are the root causes of appeals, and where should the client focus renegotiation efforts?

## Approach and Tools

| Step | Tool | Purpose |
|------|------|---------||
| Data Creation | MySQL (DB Fiddle) | Built synthetic claims and appeals dataset |
| Exploratory Analysis | SQL | Identified spending patterns and appeal drivers |
| Visualization | Power BI Desktop | Created executive dashboard with KPIs and drill-downs |
| Documentation | GitHub + Markdown | Published case study as portfolio artifact |

What does this content do?

The Project Overview tells visitors what this is at a glance. The Business Problem frames your analysis the same way you would frame it for a real client. The Approach and Tools table shows your methodology so a reviewer can quickly see your workflow.

Do not commit yet. You will add the second half of the README content next.

Add findings, recommendations, and repository guide

The second half of your README is where you prove you can draw conclusions from data and make actionable recommendations.

  • Below the Approach and Tools table you just pasted, add the following content:
## Key Findings

1. **Specialty drugs account for 45% of total spend but only 20% of claims volume.** This disproportionate cost concentration makes Specialty the highest-priority category for pricing review.
2. **The West region has the highest appeal rate at 32%.** This is nearly double the Northeast rate (18%), suggesting regional pricing inconsistencies.
3. **Price Disputes are the leading appeal reason (40% of all appeals).** Members and providers frequently challenge billed amounts, pointing to misaligned contracted rates.
4. **Tier 3 claims average 3x the cost of Tier 1 claims.** The pricing gap between tiers is wider than expected, creating friction for members on high-cost drugs.

## Recommendations

1. **Renegotiate Tier 3 pricing for Specialty drugs in the West region.** This intersection of high cost, high appeal rate, and high tier represents the largest savings opportunity.
2. **Audit contracted rates for Price Dispute claims.** Since price disputes drive 40% of appeals, aligning contracted rates with market benchmarks would reduce appeal volume and administrative cost.
3. **Implement regional pricing reviews quarterly.** The wide variance between regions (18% to 32% appeal rate) suggests pricing is not being adjusted for local market conditions.

## How to Use This Repository

- `sql/` - Contains all analysis queries with inline comments explaining business logic
- `data/` - Synthetic CSV datasets (claims and appeals)
- `powerbi/` - Power BI Desktop file (.pbix) with the complete dashboard
- `screenshots/` - Dashboard page screenshots for quick reference

Why do findings need bold text and data points?

Bold text makes each finding scannable. The data points (45%, 32%, 40%) prove you did the analysis rather than making vague claims. Recommendations follow directly from findings, showing cause-and-effect thinking.

  • Click Preview above the editor to confirm your headings, table, and bold text render correctly.
  • Click Commit changes... in the top right.
  • Leave the default commit message ("Update README.md") and select Commit directly to the main branch.
  • Click Commit changes.

Your repository's front page now displays formatted headings, a methodology table, data-backed findings, and actionable recommendations.

README not rendering correctly?

  • Check that each heading has a space after the ## symbols. Missing the space breaks Markdown heading rendering.
  • Confirm the table has the separator row (|------|------|---------|) between the header and data rows.
  • Make sure bold text has double asterisks on both sides with no spaces between the asterisks and the text.

Help me fix my README Markdown formatting.

Capture dashboard screenshots and upload project files

Screenshots make your README visual. They let anyone understand your dashboard without downloading the .pbix file.

  • Switch back to Power BI Desktop where your report is open.
  • Navigate to the Executive Summary page by clicking its tab at the bottom.
  • Press Win+Shift+S to open the Snipping Tool.
  • Drag to select the entire dashboard canvas (KPI cards, charts, and slicer).
  • Open the snip notification that appears, then click Save (the floppy disk icon).
  • Save the file as executive_summary.png inside your screenshots folder on your Desktop.
  • Click the Appeal Drivers page tab in Power BI Desktop.
  • Press Win+Shift+S again and capture the full page.
  • Save this screenshot as appeal_drivers.png in the same screenshots folder.

Why use the Snipping Tool instead of a Power BI export?

Power BI Desktop does not have a built-in "export page as image" button. The Windows Snipping Tool (Win+Shift+S) is the quickest way to capture a clean screenshot of your entire report page as a PNG file.

Now upload all your project files to GitHub.

  • Switch back to your client-pricing-analytics repository in your browser.
  • Click the Add file dropdown menu near the top of the page.
  • Click Upload files from the dropdown.
  • Open your client-pricing-analytics folder on your Desktop in File Explorer.
  • Select all four subfolders (data, sql, powerbi, screenshots) and drag them into the GitHub upload area in your browser.
  • Wait for all files to finish uploading (you should see file names listed on the page).
  • Click Commit changes at the bottom of the page.

Your repository now contains all project files organized in their folders.

Files not showing up after upload?

  • Make sure you dragged the folders themselves (not the parent folder). GitHub should list individual files like data/claims.csv in the upload area.
  • If your .pbix file exceeds 25 MB, GitHub's browser upload will reject it. Try saving the Power BI file with a smaller dataset or use Git LFS from the command line.
  • Refresh the repository page after committing to see the new folder structure.

Help me upload my project files to GitHub.

Embed screenshots in your README

The final touch is embedding your dashboard images directly in the README so visitors see them without clicking into the screenshots folder.

  • Click README.md in your repository's file list.
  • Click the pencil icon in the upper right to open the editor.
  • Scroll to the bottom of the file, below the "How to Use This Repository" section.
  • Add the following Markdown to embed your dashboard screenshots:
### Dashboard Preview

![Executive Summary](screenshots/executive_summary.png)

![Appeal Drivers](screenshots/appeal_drivers.png)

How does Markdown image syntax work?

The format is ![alt text](path/to/image.png). The alt text in square brackets describes the image for accessibility. The path in parentheses points to the file relative to the repository root.

  • Click Preview to confirm both images render inline.
  • Click Commit changes... in the top right.
  • Leave the default commit message and click Commit changes.

Before you check the final result, what do you expect to see on your repository's front page?

  • Navigate to your repository's main page (click the repository name client-pricing-analytics in the breadcrumb at the top).

Your README now displays formatted headings, your problem statement, embedded dashboard screenshots, key findings with data points, and 3 actionable recommendations. This is the complete case study a hiring manager sees when they click your portfolio link.

Screenshots not rendering in the README?

  • Double-check that your screenshot files are named exactly executive_summary.png and appeal_drivers.png. The path in the Markdown must match the actual file names (case-sensitive).
  • Confirm the files are inside a folder called screenshots at the repository root, not nested inside another folder.
  • Click into the screenshots folder in your repo to verify both files are there.

Help me embed images in my GitHub README.

Your case study is live. Anyone with the link can now see your problem statement, your analysis approach, your dashboard, and your recommendations. Next up, the Secret Mission challenges you to add a What-If pricing scenario page to your dashboard.

Secret mission

Add a What-If Pricing Scenario

Your dashboard shows what happened. But what if you could show a client what would happen if they renegotiated pricing? In this secret mission, you'll add a What-If parameter page to your Power BI dashboard that models the impact of a percentage price reduction across drug categories. This is the kind of strategic decision-support feature that separates a reporting analyst from a senior analyst.

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 and on free services, so there are no ongoing costs.

Resources you used:

  • GitHub repository: client-pricing-analytics (free, no ongoing cost).
  • Local project folder: client-pricing-analytics on your Desktop (local files, no cost).

Keep everything running

No action needed. Choose this if you are still actively building or want to keep your portfolio piece live.

  • Your GitHub repository stays live as long as you want it. This is your portfolio piece to link on your resume and LinkedIn.
  • Your local client-pricing-analytics folder stays on your Desktop with your .pbix file, CSV data, and SQL scripts.
  • Your DB Fiddle session is ephemeral. It expires on its own and requires no cleanup.

Pause - I'll come back to this later

No resources to pause. Everything is either local or free-tier with no running processes.

  • Your GitHub repository remains available at no cost whenever you return.
  • Your local .pbix file lives on your machine. Open it any time in Power BI Desktop to continue editing.
  • If you want to pick up your DB Fiddle queries again, you will need to re-paste the schema and data since sessions are not saved.

Delete - I don't want to use this again

Remove all project resources and start fresh. Follow both steps below to fully clean up.

  • Delete the GitHub repository:
  • Open your client-pricing-analytics repository on GitHub.
  • Click Settings (under the repository name).
  • Scroll down to the Danger Zone section and click Delete this repository.
  • Click I want to delete this repository.
  • Click I have read and understand these effects.
  • Type client-pricing-analytics to confirm, then click Delete this repository.
  • Delete the local project folder:
  • On macOS or Linux, open a terminal and run:
rm -rf ~/Desktop/client-pricing-analytics
  • On Windows, open PowerShell and run:
Remove-Item -Recurse -Force "$env:USERPROFILE\Desktop\client-pricing-analytics"
  • Your DB Fiddle session is already gone. Sessions are ephemeral and do not persist after you close the browser tab.

Nice Work!

Nice Work!

You just built a complete analytics case study from scratch. You now have a live GitHub repository that proves you can take raw data, ask the right business questions, build a client-ready dashboard, and deliver clear recommendations.

You've learned how to:

  • Write SQL queries that uncover pricing patterns and appeal drivers across regions and drug categories.
  • Build a Power BI dashboard with KPI cards, trend lines, drill-down matrices, and conditional formatting that answers a real business question in 30 seconds.
  • Document and publish a complete case study to GitHub with a problem statement, approach, key findings, and actionable recommendations you can link from your resume.
  • Secret Mission: Added a What-If pricing scenario page that models the impact of rate renegotiation using DAX measures and parameter slicers.

Ready to quiz yourself?