A Strategic Analysis for Growth

Introduction & Challenge Objective:

A company aims to evaluate its CRM data and sales pipeline for leads registered over the last five months. The task is to build a comprehensive analytical report that provides insights into lead distribution across countries, industries, and organization sizes. The report will assess the health of the sales pipeline, forecast potential income over the next few months, and compare sales agent performance. The dashboard answers some key questions on performance metrics and sales pipeline.

About the Dataset:

The Data includes 3000 records containing CRM data spanning over 5 months along with the data dictionary.

Tools Used:

Power BI

Data Preparation & Modelling:

The data was pretty much clean but it required little changes.

Data Analysis and Visualization:

Dashboard Designing - I went to create 2 pages - one showcasing Sales performance and one for Other metrics (Sales forecast, pipeline health, etc)

Insights & Analysis:

KPIs:

Total Leads - 3K

Total Deals - 867

Deal Value (Total Deals) - $2.30M

Average Deal Value (Total Deals) - $2.65K

Closed Deals - 171

Closed Deal Value - $425.39K

Average Closed Deal Value - $2.49K

Conversion Rate - 5.70%

Average Sales Cycle Duration (Days) - 63.17

Sales Performance by Agents

• Laura Thompson is the top performer in generating leads, finalizing deals and, generating deal value, demonstrating efficiency and consistent performance. Jessica Martinez and Michael Brown also show strong performances.
• Kevin Anderson and David Wilson handle the highest average deal values, suggesting a focus on high-value opportunities.
• Laura Thompson leads in closed deals and generates high deal value, indicating strong closing skills. Michael Brown and Jessica Martinez also show strong performances.
• Michael Brown and Emily Johnson achieve the highest average closed deal values, reflecting effectiveness in high-value deal closures.
• Jessica Martinez and Michael Brown have the longest sales cycles, potentially indicating more complex sales processes. (maybe, but not conclusive)
• Kevin Anderson shows the highest conversion rate, indicating efficiency in converting leads to deals.
• Laura Thompson and Michael Brown also lead in lost opportunities, suggesting a need to address specific challenges in their sales processes, given they are also the leading agents in terms of generating and finalizing deals.
• Emily Johnson has the highest churn rate, indicating potential issues in customer retention.

Sales Performance by Country

• Italy generates the most leads, leads in deals closure and generates highest deal value, indicating strong market presence, prospecting efforts and effective sales processes. Followed by France and Germany which also show strong market presence.
• Switzerland leads in average deal value, suggesting high-value opportunities. France and Portugal also show strong performance.
• Italy leads in closed deals and generated high deal value, reflecting strong closing capabilities and effective sales strategies. Portugal and France also perform well.
• Switzerland leads in average closed deal value, suggesting high-value deals. Belgium and Germany also perform well.
• Switzerland has the longest sales cycle.
• Netherlands shows the highest conversion rate, indicating efficient sales processes. Belgium and Portugal also perform well.
• Italy and Germany lead in lost opportunities, suggesting areas for improvement in sales processes, given they are also the top-performing Countries in lead generation and finalization.
• Spain has the highest churn rate, indicating potential issues in customer retention.

Sales Performance by Industry

• Transportation generates the most leads, leads in deals closure and generates highest deal value, indicating strong market presence, prospecting efforts and effective sales processes. indicating strong market presence. Banking and Finance, and Government Administration also perform well.
• Construction & Engineering leads in average deal value, suggesting high-value opportunities. Manufacturing and Government Administration also show strong performance.
• Transportation leads in closed deals and generating high deal value, reflecting strong closing capabilities. IT & IT Services, Banking and Finance, and Government Administration also perform well.
• Banking and Finance leads in average closed deal value, suggesting high-value deals. Entertainment and Media, and Education & Science also perform well.
• Energy & Utilities has the longest sales cycle, indicating more complex sales processes.
• Manufacturing shows the highest conversion rate, indicating efficient sales processes. Entertainment, Media and Hospitality, and Professional Services also perform well.
• Transporation and Government administration leads in lost opportunities, suggesting areas for improvement in sales processes, given they are also the top-performing ones in lead generation and finalization.
• Education & Science has the highest churn rate, indicating potential issues in customer retention.

Sales Performance by Products

• SAAS generates the most leads, leads in deals closure and generates highest deal value, indicating strong market presence, prospecting efforts and effective sales processes. indicating strong market presence followed by Services.
• Custom Solutions leads in average deal value, suggesting high-value opportunities.
• SAAS and Services leads in closed deals and generating high deal value, reflecting strong closing capabilities.
• Custom Solutions leads in average closed deal value, suggesting high-value deals.
• Custom Solutions has the longest sales cycle, indicating more complex sales processes.
• All 3 shows almost equal conversion rate, indicating efficient sales processes.
• SAAS leads in lost opportunities, suggesting areas for improvement in sales processes, given they are also the top-performing ones in lead generation and finalization.
• Custom Solutions & SAAS has the highest churn rate, indicating potential issues in customer retention.

Sales Pipeline

Pipeline Stages:

• Initial contact: 298 leads
• Nurturing: 220 leads
• Proposal sent: 140 leads
• Won: 83 deals
• Opened: 65 leads
• Lost: 61 leads

Sales Forecast - Sales forecast shows a steady rise and it peaks high in May & June 2024 followed by a decline, suggesting a potential seasonal pattern or market saturation. The forecasted decline after July 2024 indicates potential challenges/problems in maintaining sales momentum.

Avg. Closed Deal Value vs. Avg. Sales Cycle Duration (By Organizational Size) - Large and enterprise organizations have the highest average closed deal values, but enterprise organizations have a significantly longer sales cycle duration. Smaller organizations have lower average closed deal values but relatively shorter sales cycle durations.

Churn Analysis (Total vs. Churned) - A high churn rate of 50.86% suggests significant customer retention challenges. Monthly trends indicate varying churn rates.

Expected vs. Actual Close Date

Country - Germany, France and Netherlands show a significant number of deals closed early than expected close date, which might indicate an efficient sales process.

Industry - Industries such as Energy & Utilities, Education & Science, and Entertainment & Media show a significant number of deals closed early than expected close date, which might indicate an efficient sales process.

Product - SAAS products have the highest average days closed early than expected close date, suggesting a strong demand and efficient sales process for these offerings. Custom solutions and services also show significant early closures, indicating effective sales strategies for these products.

Monthly Trend by Total Deals

March has the highest total deals, correlating with the peak in total leads. This suggests that the conversion efforts in March were particularly successful.

Deal values steadily increased from January to April, indicating an upward trend.

The average deal value peaks in April, aligning with the overall increase in deal value.

The conversion rate spikes in February**** and a decline thereafter.

Monthly Trend by Closed Deals

Steady increase in closed deals from January to May, peaking in May.

Correlating with the closed deals count, it is no surprise there is a steady increase in closed deal value from January to May, peaking in May.

Fluctuations in average closed deal value with peaks in February and April.

Fluctuating trends in conversion rate.

Increasing trend in average sales cycle duration throughout the months.

Recommendations:

• Enhance Customer Retention: Implement comprehensive post-sale support and follow-up programs to reduce churn rates. Introduce loyalty programs, and proactive customer support to address high churn rates identified across various segments.
• Leverage Best Practices: Identify successful strategies and techniques used by top performers in generating leads, closing deals, and managing high-value deals. Conduct training sessions to disseminate these best practices across the entire sales team.
• Optimize Sales Processes: Streamline sales processes to shorten sales cycle durations, particularly for high-value and complex deals. Utilize CRM tools effectively to automate follow-ups, manage leads efficiently, and ensure timely interventions at critical stages of the sales funnel.
• Data-Driven Decision-Making: Regularly analyze sales data to identify trends, bottlenecks, and opportunities. Use these insights to make informed decisions about resource allocation, strategy adjustments, and process improvements.
• Improve Conversion Rates: Focus on strategies to boost conversion rates by enhancing lead qualification processes, and ensuring effective communication of value propositions to potential customers.
• Target High-Value Opportunities: Prioritize efforts on high-value deals and high-potential market segments. Tailor marketing and sales strategies to address the specific needs of these segments, ensuring a higher return on investment.
• Resource Allocation: Allocate resources strategically to high-performing regions, industries, and products while also investing in areas with growth potential but currently underperforming. Ensure balanced workload distribution among sales agents to prevent burnout and optimize performance.

Conclusion:

This project was a great experience in terms of learning about the CRM Sales and the related KPIs. There is even more analysis that can be done with the provided data and I will still keep working on it.