Analyze a Sales Pipeline with AI

Use Claude and Excel to analyze 8,800 B2B sales opportunities.

Introduction

30 Second Summary

Every sales team generates mountains of data. The companies that win are the ones with people who can turn that data into clear recommendations for leadership.

In this project, you will analyze a real B2B sales dataset using Excel and Claude to identify top-performing teams, spot quarterly trends, and build a 1-page executive summary with actionable recommendations.

What You'll Build

You'll play the role of a business analyst at a fictional computer hardware company, using Claude and Excel to analyze 8,800 sales opportunities and deliver findings to sales leadership.

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

A structured Excel workbook with summary calculations including win rates, revenue totals, and average deal sizes.
A set of reusable analysis prompts that work on any sales dataset.
A polished 1-page executive summary with 3 key findings and data-backed recommendations.
Secret Mission: Build a "Problem Product" deep-dive that identifies why a specific product has high volume but a low win rate, and recommend a pricing change.

Are there any prerequisites?

None. You just need Microsoft Excel (or a free alternative like Google Sheets) and a free Claude account. This guide walks you through setting up everything from scratch.

Set Up Your Tools

Before you can analyze any data, you need two things: an AI assistant to help you spot patterns and a real dataset to explore.

In this step, you'll create a free Claude account for AI-powered analysis, download a real B2B sales dataset, and take a quick tour of the data in Excel.

In this step, get ready to:

Create a free Claude account and test it with a message.
Download a real sales pipeline dataset with 8,800 opportunities.
Open the data files in Excel and understand what each one contains.

Create your free Claude account

Claude is the AI assistant that will help you analyze data throughout this project. The free tier gives you access to Claude Sonnet 4.6, which includes file creation, web search, and extended thinking.

Go to claude.ai in your browser.
Click Sign Up.
Create an account using your email or Google account.
Check your inbox and verify your email address.

What is Claude?

Claude is an AI assistant made by Anthropic. On the free tier, you can chat with it, ask it to analyze data, search the web, and even create files. You'll use it as your analysis partner throughout this project.

Now let's confirm everything is working by sending a test message.

In the Claude chat window, type Hello, I'm learning business analysis and press Enter.
Wait a few seconds for Claude to respond.

You should see a friendly response from Claude. This confirms your account is set up and ready for analysis work.

Not seeing a response?

Make sure you verified your email address. Check your spam folder if you don't see the verification email.

If your message won't send, try refreshing the page and signing in again.

help me troubleshoot signing up for Claude

Download the CRM Sales Opportunities dataset

Your analysis needs real data. You'll use a CRM Sales Opportunities dataset from Maven Analytics. It contains 8,800 sales deals from a fictional computer hardware company.

Go to mavenanalytics.io/data-playground in your browser.
Search for or scroll to CRM Sales Opportunities.
Click Download to get the dataset.
Find the downloaded zip file and unzip it.

After unzipping, you should see 4 CSV files: sales_pipeline.csv, accounts.csv, products.csv, and sales_teams.csv.

Double-click sales_pipeline.csv to open it in Excel.
Confirm you see columns including opportunity_id, sales_agent, product, account, deal_stage, engage_date, close_date, and close_value.

Don't see the columns?

If Excel shows all data crammed into column A, the CSV delimiter wasn't recognized. Try using File > Open and selecting the file manually, then choosing "Delimited" and "Comma" in the import wizard.

help me open a CSV file properly in Excel

Quick orientation in Excel

Before diving into analysis, take a quick look at each file so you know what data you're working with. Think of this as flipping through a new textbook before class starts.

Open each of the 4 CSV files briefly in Excel by double-clicking them.
Scroll through a few rows in each file to get a feel for the data.

What does each file contain?

Here's a quick reference for the 4 files you just downloaded:

sales_pipeline.csv is the main file. It has 8,800 rows of individual sales deals with stages like Prospecting, Engaging, Won, and Lost.
accounts.csv has company details for each client account, including sector, revenue, and number of employees.
products.csv lists all 7 products with their names, series, and sale prices.
sales_teams.csv maps each sales agent to their manager and regional office.

Why 4 separate files?

Real CRM systems store data in separate tables that link together. For example, each deal in sales_pipeline.csv has a sales_agent column that connects to the agent's details in sales_teams.csv. Learning to work across multiple data tables is a core business analyst skill.

Your tools are ready and you know what data you're working with. Next up, you'll frame the business problem and decide exactly which questions to answer for sales leadership.

Frame the Business Problem

Your tools are ready and your data is loaded. Now comes the most important part of any analysis project: defining what you're actually looking for.

Jumping straight into numbers without a clear question is the number one mistake new analysts make. In this step, you'll define the business questions, choose the right KPIs to measure, and prepare your data for analysis.

In this step, get ready to:

Define the business questions sales leadership needs answered.
Select 3-4 KPIs to measure team and product performance.
Prepare a data summary to use in your Claude analysis.

Understand the business context

Here's your scenario: you are a business analyst at MavenTech, a B2B company selling computer hardware. Sales leadership has hired you to dig into their CRM data and answer four specific questions.

These four questions come directly from the dataset's business challenge:

How is each sales team performing compared to the rest?
Are any sales agents lagging behind?
Are there any quarter-over-quarter trends?
Do any products have better win rates?

Instead of guessing which metrics to calculate, you'll use Claude to help identify the most important KPIs for answering these questions.

Switch back to Claude in your browser.
Paste this prompt into the chat to frame your analysis:

I'm a business analyst at a B2B computer hardware company. I have CRM sales pipeline data with 8,800 opportunities across 4 deal stages (Prospecting, Engaging, Won, Lost). The data includes sales agents organized into teams by manager and regional office, 7 products across 3 series, and 85 client accounts. Help me identify the 3-4 most important KPIs I should calculate to answer these questions for sales leadership: 1) Which teams are performing best? 2) Which agents are underperforming? 3) Are there quarterly trends? 4) Which products have the best win rates?

What does this prompt do?

This prompt gives Claude the full context of your dataset (size, structure, deal stages) and asks it to recommend KPIs tailored to your specific leadership questions.

Providing context upfront is a critical prompt engineering skill. The more specific you are about your data and goals, the more useful Claude's suggestions become.

Press Enter to send the prompt.
Wait for Claude to generate its response. You should see a list of recommended KPIs with explanations for why each one matters.

Not seeing a response?

If the message field is greyed out, you may have hit the free tier's rate limit. Wait a few minutes and try again.

If Claude's response seems generic or off-topic, make sure you pasted the entire prompt including the numbered questions at the end.

Help me get a useful KPI recommendation from Claude for my sales analysis

Select your KPIs based on Claude's response

Claude will suggest several possible KPIs. Your job is to pick the ones that directly answer leadership's four questions. Not every metric Claude suggests will be useful for this analysis.

From Claude's suggestions, select these four core KPIs as your analysis framework:

Win rate — deals won divided by total closed deals (Won + Lost).
Total revenue — sum of close_value for Won deals.
Average deal size — average close_value for Won deals.
Pipeline velocity — average number of days from engage_date to close_date.

Why these four KPIs?

Win rate tells you who closes deals effectively. Total revenue tells you who brings in the most money. Average deal size reveals whether a team wins big deals or many small ones. Pipeline velocity shows how quickly deals move through the funnel.

Together, these four metrics answer all four of leadership's questions from different angles.

Write down your four selected KPIs in a note, document, or comment in your Excel file. This is your analysis framework for the rest of the project.

Prepare a data summary for Claude

Before you start your analysis in the next step, you need to know the shape of your data. Counting rows and deal stages confirms your dataset is complete and gives you baseline numbers to reference.

Why paste text instead of uploading files?

Claude's free tier lets you paste text directly into the chat. For a beginner, pasting summarized data is more accessible than dealing with file uploads. It also teaches you a critical analyst skill: preparing and structuring data before asking for insights.

Switch back to Excel with sales_pipeline.csv open.
Confirm the total row count is 8,800 by checking the row numbers in column A (the last row of data should be row 8,801, including the header).
Click into an empty cell and count how many deals are in each stage by entering these COUNTIF formulas one at a time:

=COUNTIF(E:E, "Prospecting")
=COUNTIF(E:E, "Engaging")
=COUNTIF(E:E, "Won")
=COUNTIF(E:E, "Lost")

What do these formulas do?

Each COUNTIF formula scans column E (deal_stage) and counts how many rows match the specified value. This gives you the breakdown of deals across all four pipeline stages: Prospecting, Engaging, Won, and Lost.

These counts confirm your data is intact and give you the denominator for calculating win rates later.

Enter each formula in a separate empty cell (for example, cells J2 through J5).
After pressing Enter on each formula, you should see a number appear. The four counts should add up to 8,800 total.
Note down these counts somewhere visible. You'll reference them in the next step when you paste data summaries into Claude.

Counts don't add up to 8,800?

Make sure your COUNTIF formulas reference column E (the deal_stage column). If your columns are in a different order, scroll to find which column contains values like "Won" and "Lost", and adjust the column letter.

Also check that the stage names are spelled exactly as they appear in the data: Prospecting, Engaging, Won, Lost. Capitalization matters in COUNTIF.

Help me fix my COUNTIF formulas for the sales pipeline deal stages

Your analysis framework is locked in. You have clear questions from leadership, four KPIs to measure against, and baseline data counts to work from. Next up, you'll start crunching the actual numbers and feeding results to Claude for deeper insights.

Analyze the Data with Claude

You've framed the business problem and selected your KPIs. Now it's time to put them to work.

In this step, you'll calculate your metrics in Excel, then paste the results into Claude for analysis. Claude excels at spotting patterns and explaining what the numbers mean in business terms. You'll cover three dimensions: team performance, time trends, and product performance.

In this step, get ready to:

Calculate win rates by sales team and get Claude's performance analysis.
Identify quarterly revenue trends with Claude's help.
Analyze product win rates and spot underperformers.

Calculate win rates by sales team

Your first analysis dimension answers the leadership question: how is each sales team performing compared to the rest? You'll calculate individual agent win rates in Excel, then group them by manager to see team-level patterns.

Switch to sales_pipeline.csv in Excel.
In an empty column next to your data, add a header called Won_Count.
Use a COUNTIFS formula to count Won deals for the first sales agent. Enter this formula in the cell below your header:

=COUNTIFS($B$2:$B$8801, B2, $E$2:$E$8801, "Won")

What does this formula do?

COUNTIFS counts rows that match multiple conditions. Here it counts rows where the sales_agent column matches the agent in the current row AND the deal_stage column equals "Won".

The dollar signs ($) lock the range so you can copy the formula down without the range shifting.

Add another column with the header Lost_Count and enter a similar formula:

=COUNTIFS($B$2:$B$8801, B2, $E$2:$E$8801, "Lost")

Add a third column with the header Win_Rate and calculate the win rate:

=I2/(I2+J2)

Why Won divided by Won plus Lost?

Win rate only considers closed deals. Deals still in Prospecting or Engaging haven't reached an outcome yet, so including them would skew your percentages.

The formula is: Won / (Won + Lost). This gives you the true conversion rate for completed opportunities.

Copy all three formulas down for every row in your data.
Now open sales_teams.csv to see which agents belong to which manager.
Create a summary in a new area of your spreadsheet (or a new sheet) that lists each manager, their agents, and each agent's win rate. Group agents under their manager to see team-level patterns.
Calculate an average win rate for each manager's team.

You now have a table showing win rates by team. Time to get Claude's analysis.

Switch to your Claude conversation at claude.ai.
Paste your team win rate summary into Claude along with this prompt:

Here are the win rates by sales team manager for our B2B hardware company. Analyze these results and tell me: which team is performing best, which is lagging, and what might explain the differences? [paste your data]

How should I paste my data?

Select your summary table in Excel (manager names, agent names, win rates), copy it, and paste directly into the Claude message box. Claude reads tabular text well. Replace [paste your data] with the actual numbers.

Press Enter to send the prompt and read Claude's response.
Note which team Claude identifies as the strongest and which is lagging.

Save Claude's responses

Copy Claude's key findings into a notes document as you go. You'll use these insights in Step 4 when you build your executive summary.

Claude's analysis seems too generic?

If Claude gives vague answers like "some teams are better than others," your pasted data may not have enough detail. Make sure you include the actual win rate percentages and team names. The more specific your input, the more specific Claude's analysis.

Help me get more specific analysis from Claude about my sales team data.

Identify quarterly trends

Your second analysis dimension answers the leadership question about quarter-over-quarter trends. You'll extract the quarter from each deal's close date, then summarize revenue and deal counts by quarter.

Switch back to sales_pipeline.csv in Excel.
Add a new column with the header Quarter.
Extract the quarter from the close_date column using this formula:

=IF(G2="", "", "Q" & ROUNDUP(MONTH(G2)/3, 0))

What does this formula do?

This formula checks if the close_date cell is empty (deals still in progress have no close date). If it has a date, it divides the month number by 3 and rounds up to get the quarter number (1-4), then adds "Q" in front.

For example, a March close date gives month 3 / 3 = 1, so Q1. A July close date gives month 7 / 3 = 2.33, rounded up to 3, so Q3.

Copy the formula down for all rows.
Create a quarterly summary table in a new area. For each quarter (Q1 through Q4), calculate:
Total revenue from Won deals using SUMIFS:

=SUMIFS($H$2:$H$8801, $E$2:$E$8801, "Won", $L$2:$L$8801, "Q1")

Count of Won deals per quarter using COUNTIFS:

=COUNTIFS($E$2:$E$8801, "Won", $L$2:$L$8801, "Q1")

Repeat these formulas for Q2, Q3, and Q4 by changing the quarter reference.
You should now have a small summary table with 4 rows (one per quarter) and 2 columns (revenue and deal count).

Now paste your quarterly summary into Claude for trend analysis.

Return to your Claude conversation.
Paste your quarterly data along with this prompt:

Here is our quarterly sales performance (revenue and deal count by quarter). Identify any trends, seasonality, or concerning patterns. What should leadership watch for? [paste your data]

Replace [paste your data] with your actual quarterly summary table and send the message.
Read Claude's response and note any seasonal patterns or concerning trends it identifies.

What patterns should I expect?

This dataset has real seasonal variation. Q1 tends to be the weakest quarter, while Q2 is typically the strongest. Claude should be able to identify this pattern and suggest possible business explanations (budget cycles, fiscal year planning, seasonal demand).

Quarter formula returning errors?

If you see #VALUE! errors, your close_date column may not be formatted as a date. Select the column, right-click, choose Format Cells, and set it to Date format. If some cells are empty (deals still open), the IF check in the formula handles those.

Help me fix my Excel quarter formula.

Analyze product win rates

Your third analysis dimension answers the leadership question about product performance. Some products might have high volume but low win rates, which signals a pricing or positioning problem.

In sales_pipeline.csv, create a product summary table in a new area.
List all 7 product names from the product column (you can check products.csv for the full list).
For each product, count Won deals:

=COUNTIFS($D$2:$D$8801, "GTX Basic", $E$2:$E$8801, "Won")

For each product, count Lost deals:

=COUNTIFS($D$2:$D$8801, "GTX Basic", $E$2:$E$8801, "Lost")

Calculate win rate for each product (Won / (Won + Lost)).
Calculate total revenue per product by summing close_value for Won deals:

=SUMIFS($H$2:$H$8801, $D$2:$D$8801, "GTX Basic", $E$2:$E$8801, "Won")

What does this formula do?

SUMIFS adds up values (close_value) only where multiple conditions are met. Here it sums revenue for rows where the product matches AND the deal was Won. This gives you total revenue generated by each product.

Repeat the Won count, Lost count, win rate, and revenue formulas for all 7 products.
Your product summary table should now have columns for: product name, Won count, Lost count, win rate, and total revenue.

Now submit your product data to Claude for the final analysis dimension.

Return to your Claude conversation.
Paste your product summary table along with this prompt:

Here are the win rates and total revenue for each of our 7 products. Which products are performing well and which need attention? Are there any products with high volume but low win rates that might indicate a pricing or positioning problem? [paste your data]

Replace [paste your data] with your product summary table and send the message.
Read Claude's response. Pay attention to any products it flags as having a mismatch between volume and win rate.

Why does a volume-versus-win-rate mismatch matter?

A product with lots of deals but a low win rate means your sales team is spending time pitching it, but customers aren't buying. This could signal a pricing problem, a competitive weakness, or a misalignment between what's being pitched and what customers need.

Identifying this pattern is exactly what leadership needs to make strategic decisions about product positioning or discount policies.

Product names not matching between files?

If your COUNTIFS returns 0 for a product you know exists, check for spacing differences between sales_pipeline.csv and products.csv. Use the exact product names as they appear in the product column of sales_pipeline.csv for your formulas.

Help me troubleshoot my product COUNTIFS formula.

You now have Claude's analysis across all three dimensions: team performance, quarterly trends, and product win rates. Next up, you'll synthesize these findings into a polished executive summary that's ready for leadership.

Build Your Executive Summary

You now have Claude's analysis across three dimensions: team performance, quarterly trends, and product win rates. The hard part of the analysis is done.

Now comes the skill that separates analysts who influence decisions from those who just crunch numbers: communicating findings clearly. In this step, you'll turn your analysis into a 1-page executive summary that sales leadership can read and act on in under two minutes.

In this step, get ready to:

Synthesize your three analyses into a structured narrative using Claude.
Format a 1-page executive summary with findings and recommendations.
Refine your summary using Claude as a VP of Sales reviewer.

Synthesize findings with Claude

You have raw analysis across three dimensions, but leadership does not want three separate data dumps. They want one cohesive story with clear takeaways. Claude can help you structure the narrative.

Switch back to your Claude conversation.
Ask Claude to structure your findings into an executive summary by pasting this prompt:

Based on our analysis, help me write a 1-page executive summary for sales leadership. Structure it as: 1) Situation (1-2 sentences about the business context), 2) Key Findings (3 bullet points with specific numbers), 3) Recommendations (3 actionable next steps). Keep it concise and focused on what leadership should DO, not just what the data shows.

What does this prompt do?

This prompt gives Claude a specific structure to follow (Situation, Findings, Recommendations) so the output matches a professional executive summary format.

The instruction to focus on what leadership should DO pushes Claude toward prescriptive recommendations rather than just restating numbers. That distinction is what makes an executive summary actionable.

Claude should respond with a structured summary that includes specific numbers from your earlier analyses. Review the response and note which findings feel most compelling.

Claude's response seems too generic?

If Claude's findings lack specific numbers, scroll up in your conversation to confirm your earlier data summaries are visible. Claude uses your conversation history as context.

If your earlier messages are too far back, paste your key numbers (top team win rate, quarterly revenue figures, best/worst product win rates) directly into a follow-up message and ask Claude to revise.

Why is Claude's summary missing my specific numbers?

Format your executive summary

Claude gave you a draft, but the final document is yours. You will shape it, verify the numbers, and save it as a standalone document that works without the conversation context.

Open a new document in Word, Google Docs, or a new Excel sheet.
Add a title at the top of your document using this format:

MavenTech Q[[QUARTER="the quarter number from your data, e.g. 2"]] Sales Pipeline Analysis

Pick your strongest quarter

Use the quarter with the most interesting story from your analysis. If Q2 was the strongest quarter or Q1 showed a notable dip, that gives you something specific to write about.

Write the Situation section: 1-2 sentences describing the business context. For example: "MavenTech's sales team of [X] agents across [Y] regional offices generated [Z] in total revenue this quarter across 7 hardware products."
Write the Key Findings section: 3 bullet points, each with a specific number from your Claude analyses.
Write the Recommendations section: 3 actionable steps with expected impact. Each recommendation should connect directly to a finding.
Add an appendix reference at the bottom: "Full data analysis available upon request."

What makes a strong Key Finding?

A strong finding pairs a what with a so what. Compare these two versions:

Weak: "Team A had a 68% win rate."
Strong: "Team A leads with a 68% win rate, 12 points above average, suggesting their approach to client engagement could be replicated."

The strong version tells leadership both the data point and why it matters.

Review your full document. Confirm that it fits on a single page.
Verify that every number in your findings matches the calculations in your Excel workbook.

Why the 1-page constraint?

Executives rarely read past page one. A 1-page limit forces you to prioritize what matters most. If a finding does not drive a recommendation, cut it.

Summary is running over one page?

Cut the Situation section to exactly one sentence. Remove any finding that does not directly connect to a recommendation. Shorten recommendations to one sentence each with the expected impact in parentheses.

How do I shorten my executive summary to one page?

Review and refine with Claude

A good analyst gets a second opinion before sending work to leadership. You will use Claude as your reviewer, asking it to read your summary from the perspective of a VP of Sales.

Copy the full text of your executive summary document.
Return to your Claude conversation and paste your draft along with this review prompt:

[[YOUR_DRAFT="paste your full executive summary here"]]

Review this executive summary as if you were a VP of Sales. Is anything unclear? Are the recommendations specific enough to act on? Does anything need stronger data support?

Why ask Claude to role-play as a VP?

Giving Claude a specific role (VP of Sales) changes the kind of feedback it gives. Instead of general writing advice, it focuses on whether the content is actionable for someone making sales team decisions.

This technique of assigning a reviewer persona is a prompt engineering pattern you can reuse any time you want targeted feedback on a deliverable.

Read Claude's feedback. It may suggest making recommendations more specific, adding data support to a finding, or clarifying jargon.
Apply the edits you agree with to your document. You do not need to accept every suggestion.
Save your final document.

Your executive summary is now a polished, 1-page document with data-backed findings and actionable recommendations. This is the deliverable you would hand to a hiring manager or include in your portfolio.

Not sure which edits to keep?

Focus on edits that add specificity. If Claude suggests changing "improve team performance" to "replicate Team A's prospecting cadence across underperforming teams," that is more actionable. Accept those.

Skip edits that just rephrase without adding clarity. The goal is a document that tells leadership exactly what to do next.

Help me decide which of Claude's suggestions to apply.

Secret mission

The Problem Product Deep-Dive

Your executive summary told leadership what happened. Now tell them what to change. Identify a product with a pricing or positioning problem, calculate the revenue it's costing the company, and build a data-backed recommendation brief.

Clean Up Your Resources

This project runs entirely locally with a free Claude account, so there are no ongoing costs. Decide whether to keep your files for your portfolio or delete them entirely.

Resources you used:

Claude account (free tier, no charges).
Downloaded dataset CSV files (sales_pipeline.csv, accounts.csv, products.csv, sales_teams.csv).
Executive summary and Problem Product Brief document (local file).

Keep everything running

No action needed. Choose this if you plan to continue building on your analysis or want to keep everything accessible for your portfolio.

Your Claude account is free and useful for future analysis work. No charges will appear.
Your Excel files and executive summary are portfolio pieces. Save them somewhere accessible like Google Drive or GitHub.
The CSV dataset files take up minimal space and can be re-downloaded anytime from Maven Analytics.

Pause - I'll come back to this later

Since everything is local files and a free account, there is nothing to pause. Your Claude account stays active at no cost, and your files stay on your computer until you decide to move or delete them.

Move your executive summary and Problem Product Brief to a cloud folder (Google Drive, OneDrive, or Dropbox) so you can find them later.
Keep the CSV files in your Downloads folder or move them alongside your analysis documents.

Delete - I don't want to use this again

Remove all project files from your computer. Your Claude account remains free, so there is no financial reason to delete it.

Delete the 4 CSV dataset files (sales_pipeline.csv, accounts.csv, products.csv, sales_teams.csv) from your Downloads folder.
Delete any unzipped dataset folders that were created when you extracted the download.
Delete your executive summary and Problem Product Brief document.
If you want to delete your Claude account, go to claude.ai, click your profile icon in the bottom-left corner, select Settings, then scroll to the bottom and select Delete Account. This is optional since the account is free.

Nice Work!

You just completed a full sales pipeline analysis using AI as your analysis partner. You now have a portfolio-ready project that demonstrates exactly what hiring managers look for in entry-level business analyst candidates.

You've learned how to:

Frame a business problem using KPIs that sales leadership cares about, then analyze 8,800 sales opportunities across team performance, quarterly trends, and product win rates.
Use Claude as an AI analysis partner by crafting structured prompts that turn raw data summaries into actionable business insights.
Build a 1-page executive summary with data-backed findings and specific recommendations that leadership can act on.
Secret Mission: Created a Problem Product Brief with financial projections and a pricing recommendation that shifts your analysis from descriptive to prescriptive.

Ready to quiz yourself?