Problem Statement

For the Maven Sales Challenge, you'll play the role of a BI Developer for MavenTech, a company that specializes in selling computer hardware to large businesses. They've been using a new CRM system to track their sales opportunities but have no visibility of the data outside of the platform. In an effort to become a data-driven organization, you've been asked to create an interactive dashboard that enables sales managers to track their team's quarterly performance.

Report Overview

This report offers a comprehensive analysis of sales performance across sales agents, products, and geographic regions. It highlights top-performing sales agents, product trends, and geographic market dynamics, providing valuable insights for strategic decision-making. By examining key metrics such as revenue, wins, win percentage, and average sales cycle length, the report facilitates informed strategies to optimize sales effectiveness, capitalize on market opportunities, and drive overall company growth.

Important documents on GitHub: click here

Video Presentation of the report: click here

Understanding Deal Stages

"Deal prospecting" involves identifying and evaluating potential opportunities or leads before any formal engagement or negotiation occurs. It's about finding potential clients or partners who may be interested in doing business with you.

"Deal engaging," on the other hand, involves actively interacting with those identified prospects to negotiate and finalize a business agreement. It's about initiating and maintaining communication, presenting proposals, addressing concerns, and ultimately reaching a mutually beneficial agreement.

"Deal won" refers to successfully closing a business agreement or transaction with a client or partner. It means that the negotiations were successful, terms were agreed upon, and the deal was finalized, resulting in a positive outcome for the company.

On the other hand, "deal lost" refers to a situation where a business agreement or transaction fails to materialize despite efforts to negotiate and finalize terms. It means that the company was unable to secure the deal, either due to competing offers, disagreement on terms, or other factors.

Data Transformation

1. Data Extraction: Extracted data from four Excel files containing sales information. The data includes four files- accounts gives details of the each company, product gives details of each product such as price and series, sales teams gives details of sales agent, manager and region of each, sales pipeline includes detail of each deal happened in 2017 such as revenue, time to close a deal, stage is deal, etc.

2. Data Transformation: Cleaned and shaped the data using Power Query. Applied transformations like formatting data types to prepare the data for analysis. Conducted operations such as removing duplicates, renaming columns and replacing values. Created a separate date table.

3. Data Modelling: Created relationships between related columns. Defined calculated columns and measures using DAX for additional insights into the data.

Data Model View

4. Report Designing and Visualization: Selected appropriate visualization types from the Visualizations pane. Designed the layout of the report, arranging visuals in a logical and intuitive manner. Added titles, subtitles, and other text elements to provide context and guidance. Ensured consistency in formatting and styling throughout the report for a professional look.

5. Adding user-friendly features in report: Implemented dynamic tooltips to provide additional context and information to users on hover. Configured tooltips to display relevant details based on the data being visualized, enhancing user understanding and interaction. Created page navigation to facilitate seamless exploration across different sections of the report. Added buttons or links to navigate between pages, allowing users to easily access specific insights or analysis.

Important Metrices

• Deals Win: This measure calculates the total number of deals that have been won by the company. It counts the number of deals where the outcome is marked as "won". This measure helps track the success rate of closing deals. Knowing the total number of deals won provides a clear indication of the company's ability to convert leads into closed deals. It is a fundamental metric for assessing overall sales performance and evaluating the effectiveness of sales strategies.

• Win Percentage: This measure calculates the percentage of deals won out of the total number of deals pursued (won/lost). I haven't included deals which are engaging or prospecting. It offers insights into the efficiency and effectiveness of the sales process. A high win percentage indicates strong sales performance and efficient use of resources, while a low win percentage may indicate areas for improvement or inefficiencies in the sales process.

• Average SCL (Sales Cycle Length): This measure calculates the average length of time it takes to close a deal, also known as the sales cycle length. It is measured in days and is calculated by averaging the sales cycle length for won closed deals. Here I have calculated SCL with respect to won deals. A shorter sales cycle length typically indicates faster deal closures, which can lead to improved cash flow, increased productivity, and better customer satisfaction. Monitoring the average sales cycle length helps identify bottlenecks, streamline processes, and optimize resource allocation.

• Revenue from Each Deal: This measure calculates the total revenue generated from each individual deal. It sums up the revenue associated with each closed deal. This measure provides insights into the financial impact of individual deals. Knowing the revenue generated from each deal helps assess the value and profitability of different sales opportunities. It enables sales teams to prioritize high-value deals, allocate resources effectively, and focus on opportunities that contribute the most to the company's bottom line.

• Average Revenue: This measure calculates the average revenue generated per deal. It is calculated by dividing the total revenue from all deals by the total number of deals. Average revenue per deal is a key indicator of deal profitability and overall sales performance. It represents the average value of each closed deal and provides insights into the company's revenue generation capabilities. Monitoring average revenue per deal over time helps identify trends, track performance against targets, and make informed decisions about pricing, product offerings, and sales strategies.

Insights

Top 10 Sales Agent by wins in 2017

Darcel Schecht has highest deals won in 2017. He has 63.1 win percentage with average sales cycle length 49.4 and generated $11,53,214 revenue.

Revenue by product series

There is a high jump in sales in Q2 2017 in sale of each product. In Q3 in remains still and same in quarter 4. GTX product is generating highest revenue among all products. GTK product is costly among all products hence it’s bought by very few companies for specific purpose.

Categorizing revenue in three parts

I have categorized products in three parts according to the price of the products to analyze the impact on revenue by price. Three parts are:

1. Basic which includes products with less price ( GTX Basic - price 550 and MG Special - 55 )
2. Standard includes products with medium price range ( GTX Pro, MG Advanced, GTX Plus Pro and GTX Plus Basic )
3. Premium includes high price products ( GTK 500 )

Which product is generating highest revenue?

Below chart shows total revenue generated from each product in 2017. In the report view you can filter it by quarter. By hovering over to each bar in chart you can see top 10 sales agents who sold that product the most. It is clear here that GTX series is generating highest revenue and it is widely used product in different sectors. Although MG Special product is generating low revenue, it is very popular product because of its low cost.

Which country is dominating in generating revenue in computer hardware market in MavenTech company?

The donut chart illustrates MavenTech US's commanding market position, comprising approximately 85% of the total market share. This substantial portion underscores the company's significant dominance compared to other countries. The remaining segments of the chart represent the market share of other countries. Overall, it portrays MavenTech US's overwhelming presence in the market and highlights its pivotal role within the industry landscape.

Which sectors in US are using MavenTech computer hardware?

The bar chart depicting MavenTech computer hardware usage across different sectors in the US reveals several key insights.

Firstly, it's evident that the retail, medical, software, and technology sectors stand out as the highest revenue generators. This suggests that MavenTech's computer hardware solutions are particularly well-suited or in high demand within these industries, possibly due to factors such as technological requirements, performance standards, or specific applications.

Conversely, the employment and services sectors appear to be at the bottom in terms of both revenue generation and presumably, adoption of MavenTech's hardware solutions. This could indicate either a lower demand for computer hardware within these sectors, perhaps due to reliance on software-based solutions or outsourcing of hardware needs, or it could highlight an opportunity for MavenTech to target these sectors with tailored offerings or marketing strategies.

In which other countries does the company sell its products besides the US?

This table provides a comparative analysis of key metrics across countries besides US. Notably, Korea, Panama, and Belgium emerge as frontrunners in terms of both the number of deals ("Wins") and their respective win percentages.

However, Panama stands out with the highest deal-winning percentage, indicating exceptional success in converting opportunities into revenue.

Germany and China also feature prominently in deal counts, although their revenue figures are comparatively moderate. Additionally, the average sales cycle length ("Avg SCL") appears relatively stable across regions, suggesting a consistent timeline for deal closure.

Sectors in other countries besides US MavenTech selling products

Below chart shows MavenTech computer hardware usage across sectors in other countries reveals insightful patterns. Notably, the technology and retail sectors emerge as top revenue generators, suggesting strong demand and suitability for MavenTech's solutions, likely driven by technological advancements and consumer trends.

In contrast, the medical and services sectors lag behind, indicating lower adoption rates possibly due to regulatory constraints or reliance on alternative solutions. Understanding these sector-specific dynamics is crucial for MavenTech in tailoring its marketing strategies, product development, and resource allocation to capitalize on lucrative opportunities and address challenges within each industry segment effectively.

US is the only country where costliest product GTK 500 is sold

The table highlights US as the exclusive market for MavenTech's costliest product, GTK 500. This unique positioning underscores a strategic focus on catering to a specific segment within the US market. Notably, GTK 500 enjoys high demand across multiple sectors including entertainment, employment, finance, medical, and retail. This broad appeal suggests that the product offers versatile functionalities or addresses critical needs across diverse industries, contributing significantly to its market success and revenue generation within the US market.

Sales agents who sold GTK 500 product

The table presents the sales performance metrics for three sales agents Elease Gluck, Rosalina Dieter, and Markita Hansen, who sold the GTK 500 product.

Agent Elease emerges as the top performer, having achieved the highest number of wins and highest revenue generated.

Agent Rosalina has the highest win percentage 83.3%, indicating a remarkable success rate in closing deals for the GTK 500 product.

Elease's superior performance in revenue generation and win percentage underscores their effectiveness in selling the high-value GTK 500 product, making them a key contributor to MavenTech's sales success.

Thank You for your time ❤️

Please share your thoughts and recommendations 🤝

Like and comment what you like in the report 👍💬