CRM Sales Pipeline Analysis Report
Tien Khoi Vu
2026-05-19
Executive Summary
Data collected by MavenData origin by Data.world
Business strategy of this porject is to analyze sales team performance and business objectivity overall during processes and KPI. Later on during analysis process can be brainstorm to discover insight.
Due to technical term and number is confusing, I’ll update at the very end bottom to “ANALYSIS DECISION” for ease of understanding for stakeholders to demonstrate the skills and personal portfolio.(So please scrolling down to bottom to read if you want conclusion (IT WILL BE CONTINUING UPDATE THROUGHOUT THE TIME DISCOVERY PROCESS PROGRESS))
Wider and boarder range of updating data combine into personal MBA into economics metric, competitor market and business core solution wont be able to conduct any further or justify future use due to the validation of data are being use to demonstrate personal portfolio what I can do with numbers.
Are sales agents converting opportunities effectively, or are some agents carrying high pipeline volume without strong win performance?
Business Key Questions
Which sales rep have high KPI but low/high conversion rate
Funnel leak during step, phrase has not done well
How are the big deal and low deal status condition
Industry/ products/ customer loyalty, YoY return
Is the sales reps need more training or to hire more to do the rep? support? - Is the issue caused by the salesperson, the product, pricing, customer fit, or CRM process quality? > By asking right question and theory statement can driven the data far more with the right mindset and understanding sales phrase they has taken and CRM data.
Data Analytics Process
Data Available
The dataset contains four CSV files:
accounts.csv— account/customer informationproducts.csv— product informationsales_pipeline.csv— sales opportunity pipelinesales_teams.csv— sales team information
Load Pipeline Data
library(tidyverse)
library(readr)
library(janitor)
pipeline <- read_csv("Data/sales_pipeline.csv") %>%
clean_names()Pipeline Data Structure
glimpse(pipeline)## Rows: 8,800
## Columns: 8
## $ opportunity_id <chr> "1C1I7A6R", "Z063OYW0", "EC4QE1BX", "MV1LWRNH", "PE84CX…
## $ sales_agent <chr> "Moses Frase", "Darcel Schlecht", "Darcel Schlecht", "M…
## $ product <chr> "GTX Plus Basic", "GTXPro", "MG Special", "GTX Basic", …
## $ account <chr> "Cancity", "Isdom", "Cancity", "Codehow", "Hatfan", "Ro…
## $ deal_stage <chr> "Won", "Won", "Won", "Won", "Won", "Won", "Won", "Won",…
## $ engage_date <date> 2016-10-20, 2016-10-25, 2016-10-25, 2016-10-25, 2016-1…
## $ close_date <date> 2017-03-01, 2017-03-11, 2017-03-07, 2017-03-09, 2017-0…
## $ close_value <dbl> 1054, 4514, 50, 588, 517, 49, 57, 601, 1026, NA, 53, 0,…First Rows of Pipeline Data
head(pipeline)## # A tibble: 6 × 8
## opportunity_id sales_agent product account deal_stage engage_date close_date
## <chr> <chr> <chr> <chr> <chr> <date> <date>
## 1 1C1I7A6R Moses Frase GTX Pl… Cancity Won 2016-10-20 2017-03-01
## 2 Z063OYW0 Darcel Schle… GTXPro Isdom Won 2016-10-25 2017-03-11
## 3 EC4QE1BX Darcel Schle… MG Spe… Cancity Won 2016-10-25 2017-03-07
## 4 MV1LWRNH Moses Frase GTX Ba… Codehow Won 2016-10-25 2017-03-09
## 5 PE84CX4O Zane Levy GTX Ba… Hatfan Won 2016-10-25 2017-03-02
## 6 ZNBS69V1 Anna Snelling MG Spe… Ron-te… Won 2016-10-29 2017-03-01
## # ℹ 1 more variable: close_value <dbl>Pipeline Summary
summary(pipeline)## opportunity_id sales_agent product account
## Length:8800 Length:8800 Length:8800 Length:8800
## Class :character Class :character Class :character Class :character
## Mode :character Mode :character Mode :character Mode :character
##
##
##
##
## deal_stage engage_date close_date close_value
## Length:8800 Min. :2016-10-20 Min. :2017-03-01 Min. : 0
## Class :character 1st Qu.:2017-04-04 1st Qu.:2017-05-18 1st Qu.: 0
## Mode :character Median :2017-06-24 Median :2017-08-02 Median : 472
## Mean :2017-06-14 Mean :2017-08-01 Mean : 1491
## 3rd Qu.:2017-08-27 3rd Qu.:2017-10-18 3rd Qu.: 3225
## Max. :2017-12-27 Max. :2017-12-31 Max. :30288
## NA's :500 NA's :2089 NA's :2089Analysis Overview
This analysis reviews CRM pipeline health to identify where sales performance is strong, where deals are underperforming, and where opportunities may be leaking from the funnel.
The goal is to separate confirmed revenue from open pipeline activity, then compare deal outcomes by sales agent. This helps management understand which agents are converting well, which agents may be carrying too many unresolved deals, and where coaching or process improvement may be needed.
AHA Finding
The strongest insight is that sales performance should not be judged only by the number of opportunities.
A sales agent with many opportunities may look productive, but if a large share of those opportunities are lost or still open, that agent may have a pipeline quality problem. Or data error has been occurs
The better business question is:
Who converts opportunities into won deals most efficiently?
This follows the logic of data-driven sales management: analytics should not only describe activity, but guide management action.
Sales Agent Deal Outcome Mix
This chart compares each sales agent by total opportunities and deal outcome.
Chart Interpretation
This chart shows workload and pipeline shape.
A high number of opportunities does not automatically mean strong performance. It may mean the sales agent is active, but activity alone does not prove effectiveness.
The important warning signs are:
- many opportunities but low
Wonshare - many
Lostopportunities - many open opportunities stuck in
EngagingorProspecting
If one agent has high total opportunities but weaker outcomes, management should check whether the issue is qualification, follow-up, pricing, product fit, or closing ability.
Sales Agent Outcome Percentage Analysis
Raw counts can be misleading because some agents may simply have more opportunities than others. Percentage analysis makes the comparison fairer.
## # A tibble: 97 × 5
## # Groups: sales_agent [30]
## sales_agent deal_stage total_opportunities agent_total_opportunities
## <chr> <chr> <int> <int>
## 1 Darcel Schlecht Engaging 83 747
## 2 Darcel Schlecht Lost 204 747
## 3 Darcel Schlecht Prospecting 111 747
## 4 Darcel Schlecht Won 349 747
## 5 Vicki Laflamme Engaging 104 451
## 6 Vicki Laflamme Lost 126 451
## 7 Vicki Laflamme Won 221 451
## 8 Anna Snelling Engaging 57 448
## 9 Anna Snelling Lost 128 448
## 10 Anna Snelling Prospecting 55 448
## # ℹ 87 more rows
## # ℹ 1 more variable: outcome_percentage <dbl>Sales Agent Outcome Mix by Percentage
This chart shows each agent’s deal outcome mix as a percentage of their own total opportunities.
Percentage-Based Insight
The percentage chart gives a clearer view of conversion quality.
A sales agent with fewer total opportunities may still be a strong
performer if their Won percentage is high. Another agent
may have many opportunities but weaker conversion, meaning they are busy
but not necessarily effective.
This is where the analysis becomes useful for management. It separates activity from performance.
Sales Agent Win / Loss / Open Rate
This table simplifies deal outcomes into three categories:
Won— successful dealsLost— failed dealsOpen— deals still in progress
## # A tibble: 30 × 8
## sales_agent Lost Open Won total_opportunities win_rate loss_rate
## <chr> <int> <int> <int> <int> <dbl> <dbl>
## 1 Reed Clapper 82 0 155 237 65.4 34.6
## 2 Garret Kinder 48 0 75 123 61 39
## 3 Donn Cantrell 117 0 158 275 57.5 42.5
## 4 Hayden Neloms 45 50 107 202 53 22.3
## 5 Cecily Lampkin 53 43 107 203 52.7 26.1
## 6 Maureen Marcano 64 72 149 285 52.3 22.5
## 7 James Ascencio 71 61 135 267 50.6 26.6
## 8 Wilburn Farren 24 31 55 110 50 21.8
## 9 Jonathan Berthelot 93 81 171 345 49.6 27
## 10 Moses Frase 66 65 129 260 49.6 25.4
## # ℹ 20 more rows
## # ℹ 1 more variable: open_rate <dbl>Insight From Win / Loss / Open Rate
This table is one of the most useful parts of the report because it converts raw CRM activity into performance signals.
- High
win_rate= strong conversion ability - High
loss_rate= possible qualification, pricing, product fit, or closing issue - High
open_rate= possible follow-up delay or pipeline blockage - High total opportunities + low win rate = busy but inefficient
- Low total opportunities + high win rate = possibly underused talent
The hidden insight is that the business should not only ask, “Who has the most opportunities?”
The stronger question is:
Who has the best balance between opportunity volume and win conversion?
Business Decision
1. High Win Rate, High Volume
These agents are likely strong performers. Management should study their sales behaviour, messaging, follow-up pattern, and customer handling.
Their process may be useful as a training model for the rest of the team.
2. High Volume, Low Win Rate
These agents may look productive but may be losing too many deals.
Possible causes include:
- weak qualification
- poor follow-up
- weak objection handling
- poor closing technique
- product mismatch
- pricing resistance
- bad-fit leads being pushed into the pipeline
Strong conclusion will be contiuing update and justify soon.
Management should not punish immediately. First, check whether the agent is receiving harder leads or weaker territories.
However, a high loss rate may also mean the agent is handling lower-quality leads. The dataset alone cannot prove the cause. This should be checked against territory, product, account type, and deal size.
- who is converting well performed
- who may need coaching
- where the pipeline is stuck
- where open deals may still be recovered
- whether the sales process is producing quality opportunities or just activity
Analysis Decision
The recommended management action is to review sales agents with high open rates and high loss rates first.Then analyze loss or during processes high loss identifying wether in engaging phrase or in closing prices.
The next version of this report should join the pipeline data with product, account, and sales team data. That would allow deeper analysis of whether performance differences are caused by the salesperson, product type, customer industry, territory, or deal size.
Portfolio Note
This report is original portfolio work created for learning and demonstration purposes. The dataset is sourced from Maven Analytics and Data.world. The analysis structure, business interpretation, and recommendations are my own work, developed to demonstrate applied CRM and sales analytics thinking.