In this project, we aim to develop an interactive dashboard using Google Sheets for Sales Managers at a fictitious company specializing in computer hardware. The objective is to provide a tool for tracking quarterly sales performance across various regions and by individual sales agents. By leveraging CRM sales data, our goal is to empower managers with insights to optimize sales strategies and enhance team productivity.
-
Data Preparation and Integration: We begin by exploring and cleaning the CRM sales pipeline dataset to ensure data quality and completeness. This involved identifying missing values, understanding the sales timeline, and integrating additional information such as regional offices and sales managers from the sales teams dataset.
-
Exploratory Data Analysis with Pivot Tables: Using pivot tables, we sliced and diced the sales data to uncover quarterly trends and individual sales agent performance. Specifically,
- Visualizing opportunities won by quarter
- Analyzing the percentage of won and lost opportunities
- Ranking sales agents based on opportunities won per quarter
-
Building the dashboard: The final phase focused on visualizing insights through interactive charts and adding filters for regional offices and sales managers.
Detailed analysis and the dashboard can be viewed here!
-
Opportunities Won in Each Quarter: The highest number of opportunities won was in 2017-Q4 (1196), followed by 2017-Q3 (1257), 2017-Q2 (1254), and 2017-Q1 (531). There is a clear increasing trend in the number of opportunities won from Q1 to Q4 of 2017.
-
Opportunities Won and Lost in Each Quarter: Across all quarters, the percentage of opportunities lost consistently outweighed those won. This shows a potential area of improvement in deal conversion rates, particularly in reducing lost opportunities
- Opportunities Won in Each Quarter by Each Sales Agent: Sales agents like Darcel Schlecht consistently outperformed others in terms of opportunities won across all quarters, with a total of 349 opportunities won. This indicates high variability in performance among sales agents, highlighting potential for sharing best practices or providing additional support to lower-performing agents. Few of the top sales agent include:
-
Sales Strategy Enhancement: Characterisitcs of successful sales engagements in Q4 should be focused and analyzed, where the highest number of opportunities were won, to replicate strategies in other quarters. Targeted training programs can be implemented to improve negotiation skills and objection handling to reduce the percentage of lost opportunities.
-
Performance Benchmarking and Coaching: Benchmarks can be established based on top-performing agents like Darcel Schlecht and identify actionable insights to help other agents improve their performance. Personalized coaching sessions or mentorship programs can be provided to support agents who are lagging in performance, based on identified strengths and weaknesses.
-
Dashboard Iteration and Continuous Improvement: Continuously updating and refining the interactive dashboard to include real-time data and additional metrics such as lead source effectiveness or customer segmentation. Feedback from sales managers and agents on the usability and effectiveness of the dashboard can be analyzed to ensure it meets evolving needs and drives actionable insights.
By leveraging these insights and recommendations, the sales team can potentially enhance their overall performance, optimize their sales strategies, and achieve better outcomes in future quarters.