CRM Data Analysis
What is CRM Data?
Customer Relationship Management (CRM) data is the backbone of effective sales and marketing strategies in today’s competitive business environment. By capturing and organizing detailed information about customers, prospects, and sales interactions, CRM systems offer businesses invaluable insights into consumer behavior, preferences, and purchasing patterns.
Effective utilization of CRM data allows companies to enhance customer engagement through personalized marketing campaigns. By analyzing data points such as purchase history, communication preferences, and feedback, businesses can tailor marketing campaigns to meet each customer's unique needs.
Moreover, CRM data is critical for optimizing sales strategies. Sales teams can use this data to identify high-value prospects, track the progress of ongoing deals, and streamline the sales process. By understanding which strategies lead to successful conversions, companies can refine their sales tactics to focus on the most effective approaches, ultimately increasing efficiency and maximizing sales outcomes.
In essence, CRM data not only helps in building stronger relationships with customers by providing personalized experiences but also empowers sales and marketing teams with the data-driven insights needed to make informed decisions. This strategic advantage is essential for sustaining growth and maintaining competitiveness in any industry.
The Scenario
A B2B company that sells computer hardware has been collecting sales pipeline data for a year. However, due to a lack of data expertise, they have been unable to extract valuable insights from the data. The company has hired us as data consultants to analyze the CRM data, highlight trends in their sales process, establish metrics and benchmarks, and finally, work with their sales and marketing leaders to optimize their sales strategies.
The data for this project comes from Maven Analytics. The following section will provide an overview of the data.
Data Overview
The CRM database contains the following tables:
1. Accounts: Contains information about the companies that have purchased products from the company.
2. Products: Contains information about the products sold by the company.
3. Sales Team: Contains information about the sales team members.
4. Sales Pipeline: Contains information about the sales opportunities and deals in the pipeline.
Note that this analysis will focus on the CRM data for the United States, as all other countries in the dataset have only one account (customer) each.
When merged, we get a better picture of the dataset. The following columns are added to the merged dataset:
- Engage Month: The month in which the account was last engaged with.
- Close Month: The month in which the deal was closed.
- Employee_Size: The size of the company in terms of number of employees.
- Time to Close: The number of days from engage_date to close_date.
- Time to Close Bin: A categorical variable that bins the time_to_close into 4 categories: 0–30 days, 31–60 days, 61–90 days, and >90 days.
The image below summarizes the dataset after adding the new columns and filtering for the United States.
Additionally, the full data field descriptions are listed below:
- opportunity_id: A unique identifier for each sales deal.
- sales_agent: The name of the sales agent handling the deal.
- product: The name of the product involved in the sales deal.
- account: The company name or account name associated with the deal.
- deal_stage: The current stage of the deal (e.g., Prospecting, Engaging, Won, Lost).
- engage_date: The date when the deal moved into the engaging stage.
- close_date: The date when the deal was either won or lost.
- engage_month: The month extracted from engage_date.
- close_month: The month extracted from the close_date.
- close_value: The monetary value of the deal if it was closed-won.
- manager: The sales manager overseeing the sales agent.
- regional_office: The regional office location responsible for the deal.
- sector: The industry sector of the account.
- year_established: The year the account company was founded.
- revenue: The annual revenue of the account company (in millions of USD).
- employees: The total number of employees working in the account company.
- office_location: The primary office location of the account.
- employee_size: A categorization of the account based on the number of employees (e.g., Small, Medium, Large).
- time_to_close: The number of days from the engage_date to the close_date.
A brief note on terminology: Some terms may be used interchangeably for this analysis. For example, Sales Agent and Sales Rep are used interchangeably. Similarly, Account and Customer are used interchangeably also.
Analysis Limitations
Several limitations may influence the accuracy and applicability of our findings:
- Lack of supplementary data fields such as customer interactions and touchpoints, therefore limiting our ability to ability to conduct nuanced analysis essential for understanding the full customer journey and sales cycle.
- Limited contextual information on external market conditions, competitor actions, or macroeconomic factors that undoubtedly influence sales dynamics and customer decisions in the real world. Without this context, our analysis might not fully capture the factors driving or inhibiting sales success, making it difficult to generalize our findings beyond the existing dataset.
Given these constraints, we’ll make several assumptions and hypotheses as we go along, to fill gaps in data and context. For example, we’ll assume consistent market conditions over the analyzed period and that the data accurately reflects customer interactions without external distortions such as marketing campaigns or economic downturns.
Customer Segmentation
In the initial phase of CRM data analysis, selecting the right features for customer segmentation is crucial as it lays the foundation for tailored marketing strategies and enhanced sales targeting. For this dataset, Sector, Regional Office, and Employee Size make sense to use as segmentation features for the following reasons:
- Segmenting customers by Sector allows the company to understand industry-specific needs and tailor products accordingly.
- Segmenting customers by Regional Office helps in recognizing geographical trends and local market dynamics, which can significantly influence sales strategies and product offerings.
- Segmenting by Employee Size provides insights into the organizational scale of potential clients, enabling the company to customize its approach based on the size and capabilities of each customer.
By focusing on these features, we can effectively group similar accounts, paving the way for more focused and strategic decision-making that aligns with the unique characteristics and needs of each segment.
Customer Segmentation Analysis
The following visualizations provide insights into the distribution of accounts, deals, and revenue across different sectors, regional offices, and employee size categories.
Our CRM data analysis reveals that accounts in the Retail sector represent the largest share of both accounts and deals. These Retail accounts report an average annual revenue of approximately $1.5 billion. In contrast, although they account for only about 10% of our total accounts and 12% of deals, Software sector companies show a higher average revenue of about $4.4 billion.
Note: The revenue figures mentioned refer to the annual revenue generated by the companies within these sectors themselves, not to the revenue these accounts contribute directly to our company.
Together, the Retail, Medical, and Software sectors constitute approximately 44% of all deals in our CRM system. Given their substantial revenues and their prominence in our deal activities, these sectors should be given priority in our sales and marketing strategies.
Looking at employee size. We’ll notice that our computer hardware products are mostly geared towards large companies. Almost half (50%) of total accounts and deals come from companies with 0–1000, and 2051–5000 employees. Given the high revenue from 10000 + employee companies, we can infer that these companies are more likely to be Software companies.
Looking at regional offices. The number of accounts and deals is almost evenly distributed. The East office however generates a lower percentage of deals (28.20%) compared with the Central office at 36.11% and the West office at 35.69%. Just like other segments, we can infer that the East and West office locations probably cater to more accounts in the Software sector than the Central office.
Key Metrics
Given the data available in our CRM, what metrics would be most relevant to track and analyze to measure and improve sales performance? We need metrics that offer clear insights into our sales process and help identify areas for improvement. Here are some key metrics that would be relevant for our analysis:
- Deal Close Rate: This metric shows the percentage of deals that successfully close compared to the total deals initiated. It’s a direct indicator of how effectively our sales team converts opportunities into sales, and tracking this can highlight where we might need to improve our sales tactics or training.
- Average Deal Value: Sometimes called ASP (Average Selling Price). Understanding the average revenue from each closed deal helps us gauge the economic impact of our sales efforts. This metric is essential for planning and forecasting, as it tells us how much revenue we can expect on average from each transaction.
- Average Deal Event Value: This metric is calculated by multiplying the Average Deal Value by the Deal Close Rate, providing a measure of the expected value of a deal even before it is closed. By accounting for the probability of closing a deal, it offers a realistic assessment of potential revenue from specific sales events or campaigns. This approach helps identify which activities are most effective at driving sales, guiding more informed strategic decisions.
- Average Time to Close: This metric measures the average number of days it takes to close a deal from the time it was initiated. A shorter time to close indicates a more efficient sales process, while a longer time might suggest bottlenecks or inefficiencies that need to be addressed. Longer time to close may also just be a result of high-value deals that require more time to finalize due to complexity.
Analysis of Key Metrics
In this section, we’ll take a deeper look into out key metrics.
Average Time to Close
The Average Time to Close metric is an indicator of the efficiency of the sales process across different segments of the company. This metric measures the average number of days it takes to close a deal from the initial contact. Analysis shows variability in closure times across regional offices, employee sizes, and sectors. The East regional office reports an average time of 47 days, slightly quicker than the Central and West offices, which average 48 and 48 days, respectively. Similarly, larger companies with over 10,000 employees tend to close deals faster, averaging 46 days, compared to smaller companies, which can take up to 49 days for those with 7501 to 10,000 employees. In sector-wise analysis, Technology firms close deals the quickest, averaging 45 days, whereas Software companies show a longer average closure time at 49 days. Understanding these variations helps pinpoint areas where the sales process can be optimized and indicates how different factors such as regional dynamics, company size, and industry type influence sales efficiency.
Typically in industry, companies use different time frames to measure the time to close, such as month 0 (initial contact and deal close within the same calendar month), 0–30 days, 0–60 days, 0–90 days, etc. What you’ll normally find is that while the volume of closed deals will higher for larger time frames like 0–90 days, the overall trends and seasonality will be the same across all time frames.
For this analysis, we can also look at various time frames (from initial contact to deal close) to see if there are any trends or patterns that can be identified. Note that the overall Average Time to Close in days is **48** days while the median is 45 days.
The charts below show the distribution of time to close in days across different time frames by sector:
A few observations:
1. The majority of deals (about 46%) close within 0–30 days, and 61–90 days (about 23%). fewer deals close within 31–60 days. This supports the data we saw earlier that the Average Time to Close is 48 days.
2. The technology sector has the highest number of deals that close within 0–30 days at 50%, while the Employment sector has the lowest at 45%.
3. The telecommunications sector has the highest proportion of deals that take 90 days or more to close at 22%, while the Marketing sector has the lowest at 19%.
Deal Close Rate, Average Deal Value & Deal Event Value
Let’s analyze these other metrics together as they are all related, once again focusing on our key segments — sector, employee size, and regional office. The tables below show the Deal Close Rate and Average Deal Value for each segment, sorted in descending order by Deal Event Value. We’ll use the overall company metrics as a benchmark for comparison.
By sector, we can make a few observations:
- Marketing has the highest close rate at 60%, which is also better than the overall company close rate by +3.82%. This sector however, also has the lowest Average Deal Value ($2,123) and Deal Event Value ($1,266), which are -11.41% and -8.02% compared to the overall company metrics. This suggests strong effectiveness in securing deals, although the value of those deals is much lower than in other sectors.
- Entertainment however, with Deal Close Rate of 58% has the highest Average Deal Value ($2,650) and Deal Event Value ($1,528), which are +10.50% and +10.95% compared to the overall company metrics. This sector may be a source of more lucrative deals that contribute more significantly to the company’s revenue despite a slightly lower frequency of closure.
The 10000 + and 5001–7500 employee sizes stand out for having the highest close rates, (58.6% and 59.7% respectively) and Average Deal Values ($2,640 and $2,492 respectively). These employee size companies also outperform the overall company benchmarks.
By regional office, the Central office may have some areas of opportunity as it has the lowest close rates, Average Deal Values, and Deal Event Values. This is also the only office that underperforms the overall company metrics across all metrics.
Trend Analysis of Key Metrics
We have analyzed key metrics Deal Close Rate, Average Deal Value, and Average Deal Event Value by segments such as sector, employee size, and regional office for the entire time frame under study. To gain better insights, we can also examine these metrics over time to identify trends and patterns.
Deal Close Rates Trend (Overall Company)
Starting with the Deal Close Rate, the chart below illustrates its progression over time. For this trend analysis, we use a 60-day window from the engage date to the close date to calculate the close rate. It’s important to note that in the final 2 months (November and December), deals closed within the last 60 days may not be fully accounted for yet.
Looking at the bars (total deals), there appears to be a general upward trend in the total number of deals from January to October, with some fluctuations. The highest number of deals occurred in July, indicating peak deal activity in that month. Deal activity drops off in November and December, which could be due to seasonal factors such as holidays or year-end closures. Unfortunately, we only have 1 year of data, so we can’t confirm if this is a consistent trend.
Deal Close Rate (line chart on secondary axis) shows significant variability, starting from a lower rate in January, there is an initial increase until April, followed by a decline and then a steady increase from July onwards. Notably, the Deal Close Rate accelerates dramatically from August to October, reaching its peak in December, despite the low deal activity. As mentioned earlier, the total number of deals closed in the last 2 months may not be fully realized yet.
These trends suggest a few things:
- Seasonal or Cyclical Trends: The data may suggest a seasonal or cyclical pattern in both the total number of deals and the close rates. This could be influenced by market conditions, sales cycles, or operational changes throughout the year.
- Efficiency Increase: The sharp increase in the close rate towards the end of the year, despite the already high volume of deals, could indicate improved efficiency in sales processes or perhaps the introduction of new strategies or tools that enhanced closing capabilities.
- Potential Strategy Adjustments: The periods of lower efficiency (e.g., the dip around June and July for close rates) might be focal points for reviewing sales strategies or operational tactics to ensure steadier performance throughout the year.
- Resource Allocation: The increase in deals and close rates later in the year might require adjustments in resource allocation, such as staffing, budgeting, and marketing efforts, to capitalize on potential high-performance periods.
Deal Close Rates (by Sector)
Let’s now look at the trend of the key metrics (Deal Close Rate, Average Deal Value, average Deal Event Value) by sector over time using the chart below. Note that all the subplots share the same primary axis (bars — total deals closed) and secondary axis (line chart — Deal Close Rate), and also share the same x-axis (dates).
For Total Deals, we can see all sectors follow the same trend we saw earlier in the overall company chart, with the count of total deals peaking around July. Retail and Medical sectors generate the highest number of deals.
For Deal Close Rate (line chart on secondary axis), once again, all sectors show a similar pattern to the overall company trend. We see the Deal Close Rate first peak around April, then fluctuate until July, and then increase steadily afterward. Entertainment, Medical, and Telecommunication sectors saw a dip in close rate in the last month. Once again this could be because deals closed in the last 2 months may not be fully realized yet.
Deal Close Rates (by Employee Size)
All employee sizes show a similar trend as the overall company in total deals and Deal Close Rate. Only the 5001–7500 employee size shows a slight dip in the Deal Close Rate in the last month.
Finally, while not shown here, the Total Deals and Deal Close Rate by Regional Office also show similar trends to the overall company.
Average Deal Value (by Sector)
The chart below shows the Average Deal Value by sector. This metric is important as it helps us understand the revenue our business generates from each sector.
We notice there is considerable volatility in Average Deal Value across all sectors throughout the year. This volatility could be indicative of market dynamics, customer behavior changes, or internal strategic shifts within the sectors.
Finance, Telecommunications, and Entertainment sectors show significant upward trends in Average Deal Value. Marketing, Retail, Software, and Technology also appear to be on an upward trend, though not as pronounced as the first three sectors. Medical, Services, and Employment sectors show a downward trend in Average Deal Value. These last 3 sectors may require additional attention to understand the factors driving the decline in Average Deal Value.
Understanding trends in Average Deal Value can help allocate resources more efficiently and prepare for demand surges.
Average Deal Value by Employee Size
Average Deal Value by Regional Office
The East office is the only location showing a consistent upward trend in Average Deal Value, with an increase of 200% from January to December 2017. Sales leaders will need to look within this office location to understand what factors are driving this increase in Average Deal Value and what learnings can be applied to other office locations.
Deal Event Value (by Sector)
The chart below shows the Average Deal Value by sector. This metric is important as it helps us understand the revenue our business generates from each sector.
We notice there is considerable volatility in Average Deal Value across all sectors throughout the year. This volatility could be indicative of market dynamics, customer behavior changes, or internal strategic shifts within the sectors.
Finance, Telecommunications, and Entertainment sectors show significant upward trends in Average Deal Value. Marketing, Retail, Software, and Technology also appear to be on an upward trend, though not as pronounced as the first three sectors. Medical, Services, and Employment sectors show a downward trend in Average Deal Value. These last 3 sectors may require additional attention to understand the factors driving the decline in Average Deal Value.
Understanding trends in Average Deal Value can help allocate resources more efficiently and prepare for demand surges.
Deal Event Value Trend by Employee Size
By employee size, the 10000+ employee size appears to be breaking the \$2,000 mark starting in September 2017. Closely following is the 0–1000 employee size. All other employee sizes also show an upward trend in Deal Event Value, though not as pronounced as the first two.
Deal Event Value Trend by Regional Office
By Regional office, once again, the East shows the most pronounced upward trend in Deal Event Value, also crossing the \$2,000 mark around November 2017.
Analyzing Sales Agents Performance
To gauge the performance of sales reps effectively, we can formulate
an approach that integrates our key metrics; Deal Close Rate, Average Deal Value, Deal Event Value, and Average Time to Close. Here’s how we could analyze and categorize the performance of each sales rep:
1. Computation of Key Metrics: Initially, we compute out metrics for each sales rep.
2. Quantile Classification: Each metric is then segmented into quantiles to show how each rep’s performance stacks up against their peers.
3. Combined Score Calculation: After segmenting each metric into quantiles, we can sum up the quantile scores for each metric for each sales rep. This combined quantile score provides a holistic view of a rep’s overall performance, factoring in various aspects of their sales efficiency and effectiveness.
4. Performance Categorization: Based on the combined scores, we can divide the sales reps into three performance categories:
- Top Performers (66th percentile and above): These are the reps whose combined metric scores place them in the top third of the distribution. Their consistent high performance across multiple metrics indicates their critical role in driving sales and revenue.
- Mid Performers (33rd to 66th percentile): Representatives in this group show competency in their roles but may benefit from targeted improvements in specific areas.
- Low Performers (below 33rd percentile): Sales reps in this category are those who scored the lowest in their combined metrics, highlighting areas where significant enhancements may be needed to meet expected sales targets.
This approach not only pinpoints where each sales rep stands in terms of performance but also uncovers deeper insights into their strengths and potential areas for improvement. By quantifying performance through a combination of critical sales metrics, the management team can tailor development programs, optimize sales strategies, and better allocate resources. Moreover, this analysis fosters a more objective, transparent, and motivating environment for sales reps to understand and enhance their performance.
Using this strategy to gauge sales reps’ performance, it shows that we have 10 top performers (33% of sales reps), 9 mid performers (30% of sales reps),
and 11 low performing sales reps (37% of sales reps):
Hypothesizing Drivers of Sales Agents Performance
Going a step further it would help to understand the drivers of performance for each sales rep. Several factors that could influence sales reps’ performance include things like tenure, training, manager, regional office, etc. We can explore some of these factors and see how they may correlate with sales reps’ performance.
Tenure
Tenure is an important factor that can influence sales performance. Sales reps with longer tenure may have built stronger relationships with clients, developed better sales strategies, and acquired more experience in handling various sales scenarios. While our dataset does not include any information on tenure, we can use the earliest deal date as a proxy for tenure. We’ll use the max deal date as the latest date and calculate the tenure in days.
Based on the table above, it appears that tenure might not significantly influence sales performance. The average tenure for top, mid, and low performers is quite similar, showing no significant variation across these categories. Notably, the average tenure for top performers (412 days) is almost the same as that for low performers (415 days).
Manager
The manager a sales rep reports to can also have a significant impact on their performance. Managers play a crucial role in providing guidance, support, and training to their team members, which can directly influence sales outcomes.
All 6 managers have 5 sales agents each. Rocco Neubert has 3 top performers, 2 mid performers, and low low performers. Someone like this may be considered a top performing manager. Cara Losch has 3 top performers, 2 mid performers, and 1 low performer. Dustin Brinkman however, has 1 mid performer and 4 low performers. Sales leadership may want to look into what managers like Rocco Neubert and Cara Losch are doing differently and see if those strategies can be applied to other managers.
Regional Office
Earlier we saw the East office had the highest Average Deal Value and Deal Event Value. This office also has the most amount of top performers:
Once again, sales leadership may want to determine if there are office-specific strategies that are driving the performance of the East office and see if those can be applied to other office locations.
Product
Finally, we can analyze the performance of sales reps by product. Are top performers closing more deals on products with better conversion rates and Deal Event Values? To try and answer this question, let’s look at our key metrics by product.
The table above is sorted in descending order by Deal Event Value. We can observe a few things:
- In terms of Deal Event Value, the GTK 500, GTX Plus Pro, and GTXPro have the highest values.
- In terms of conversion rates, the GTK Plus and GTXPro have the highest rates.
Next let’s analyze the top 3 and bottom 3 performing sales reps, based on the combined score.
The top 3 performing sales reps appear to be closing about 35% of deals on GTX Plus Pro, GTXPro, and MG Advanced products, which have better conversion rates, Deal Event Values, and lower Average Time to Close.
The bottom 3 performing sales reps on the other hand are closing majority of their deals on GTX Basic, GTX Plus Basic, and MG Special, which have the lowest Deal Event Values and take more time to close.
Of course, these are just hypotheses of what could be driving sales reps performance. In reality, what products sales reps are closing deals on may depend on multiple factors such as expertise, office location, or just overall strategy. Given the limitations of the dataset, it would be difficult to draw any concrete conclusions without further context.
Conclusion
This project has centered around a comprehensive analysis of CRM data to determine key metrics, understand trends, set performance benchmarks, and effectively segment customers. By examining metrics such as Deal Close Rate, Average Deal Value, and Average Deal Event Value across various segments — sector, employee size, and regional office, we have gained significant insights that can be shared with senior leadership to help guide strategic decisions in sales and marketing.
However, it’s important to acknowledge the limitations of the data utilized in this analysis. The absence of more granular details may affect the completeness of our findings. Moreover, the lack of additional context on the broader business strategy and market conditions means that while our conclusions make sense within the confines of the available data, they might not fully capture all potential variables that could influence sales performance. In real-world scenarios, access to a broader range of contextual information — including ongoing business strategies and market dynamics would enable even more nuanced and accurate analyses.
Links to Resources
- Raw Dataset: Maven Analytics.
- Dataset Prepared Version: Github.
- Reproducible Code: Github.