CRM Sales Opportunities

B2B sales pipeline data from a fictitious company that sells computer hardware

CASE

After they made massive sales in 2016 - 2017, they wanted to analyze their sales results with the following questions:

  1. How is each sales team performing compared to the rest?
  2. Are any sales agents lagging behind?
  3. Is there any quarter-over-quarter Trend?
  4. Which Product was saled the most? Do any products have better win rates? Which product contribute the most at these sales?

To solve the problem above, 4 datasets created in Microsoft Excel will be used to provide concrete answers. Here MySQL and Tableau will also be used for data search and data visualization.

QUESTION 1 ~ How is each sales team performing compared to the rest?

Sales performance data will be sought which can be seen from the number of successful sales, lost sales and the total value of sales achieved 

											
select engage_date,
sales_agent,
count(case when deal_stage = 'Won' then 1 end) as total_won, 
count(case when deal_stage = 'Lost' then 1 end) as total_lost,
sum(close_value) as total_close_value
from sales_pipeline
group by engage_date,sales_agent
order by  sales_agent, total_close_value desc;

and here is the result

Here we can see that Darcell Schlecht is the best sales agent by having the highest number of successful sales and the highest income, while Violet Mclelland is the sales agent with a poorer performance than the others. Even though she has a number of successful sales that is quite competitive with others, her total sales are the least when compared to other sales agent

QUESTION 2 ~ Are any sales agents lagging behind?

We will identify whether there are sales that are still pending between 2016 - 2017. Here we will use data where the sales status is still at the "Engaging" level which indicates the sales process is still ongoing / pending 

											
select * from sales_pipeline
where deal_stage = 'Engaging';

Here is the result

Here we can see that there are still a lot of pending sales. And if we total all the pending sales and the estimated the total value of sales that could be achieved, we will get the following data 

											
select sp.product, 
sum(case when sp.deal_stage = 'Engaging' then 1 else 0 end) as total_engaging,
sum(case when sp.deal_stage = 'Engaging' then pr.sales_price else 0 end) as total_estimated_value
from sales_pipeline sp join products pr on sp.product = pr.product
group by sp.product;

Here is the result

As we can see, all the products on offer are still pending sales. This is very unfortunate because if we look at the table, these products would generate extraordinarily large income

QUESTION 3 ~ Is there any quarter-over-quarter Trend?

here we will identify whether there is a trend forming in each quarter from 2016 - 2017 and also see what the sales trend for each product is per quarter by considering the total products sold, the total products that failed to sell, the total sales that are still lagging and the total value of sales achieved 

											
select product,
engage_date,
SUM(CASE WHEN deal_stage = 'Won' THEN 1 else 0 END) AS total_winning_sales,
SUM(CASE WHEN deal_stage = 'Lost' THEN 1 else 0 END) AS total_losing_sales,
SUM(CASE WHEN deal_stage = 'Engaging' THEN 1 else 0 END) AS total_lagging_sales,
SUM(close_value) AS total_close_value
from sales_pipeline
where YEAR(engage_date) is not null
group by engage_date, product;

Here is the result

Overall, the product sales trend per quarter 2016-2017 shows a positive trend. Total Close Value experienced an upward trend in both years, although in 2017 there was a slight decline in quarter 3. Total Losing Sales experienced a downward trend in both years, indicating that the company is increasingly successful in achieving its sales targets. It is recommended to maintain the strategy that has been used and always pay attention to market trends and the behavior of similar competitors in order to get maximum profit

QUESTION 4 ~ Which Product was saled the most? Do any products have better win rates? Which product contribute the most at these sales?

in the analysis for this question, we will compare the performance of each product that has been sold. Using the syntax below, we can solve the questions presented above 

											
select 
product,
SUM(CASE WHEN deal_stage = 'Won' THEN 1 else 0 END) AS total_winning_sales
from sales_pipeline
group by product
order by total_winning_sales desc;

Then in the Tableau application, we will use the calculation feature to find the percentage contribution from each product and also find the percentage win rate for each product in this sale.

From the data presented, it can be seen that the GTX Basic product is the "Best of the Best", because it is the product with the most sales, is a big contributor to sales and has a very high win rate so it is very popular with customers. On the other hand, the GTK 500 product is a product that needs attention here considering that its performance during this sale was not good enough compared to the other 6 products. Meanwhile, MG Advanced Products are the most stable products in sales this time

Conclusion

From the analysis above, we can conclude that:
1. Sales Team Performance: Darcell Schlecht emerges as the top-performing sales agent with the highest number of successful sales and total sales revenue. Violet Mclelland lags behind in terms of total sales revenue despite having a competitive number of successful sales. This indicates a potential area for improvement or further investigation into factors affecting her total sales performance.
2. Pending Sales: A significant number of sales are still pending between 2016-2017, indicating potential missed opportunities for revenue generation. Despite the pending status, the estimated total value of these pending sales suggests the potential for substantial revenue if successfully closed.
3. Quarter-over-Quarter Trend: Overall, there is a positive trend in product sales per quarter from 2016-2017. Total close value exhibits an upward trend, with a slight decline in quarter 3 of 2017. However, total losing sales show a consistent downward trend, indicating improved sales performance over time. To sustain this positive trend, it is recommended to maintain current strategies while closely monitoring market trends and competitors' behavior.
4. Product Analysis: The GTX Basic product stands out as the top-performing product, leading in sales volume, contribution to total sales revenue, and boasting a high win rate, indicating strong customer demand. The GTK 500 product requires attention due to its underperformance compared to other products. Further analysis is needed to understand the reasons behind its lower sales performance. MG Advanced Products demonstrate stability in sales performance throughout the analyzed period. In conclusion, while the sales data from 2016-2017 highlights areas of success, such as Darcell Schlecht's performance and the positive quarter-over-quarter sales trend, it also identifies areas for improvement, such as addressing pending sales and optimizing the performance of underperforming products like GTK 500. Continuous analysis and adjustment of sales strategies based on insights from data will be crucial for maximizing sales performance and achieving sustainable growth in the future.


Check out my other interesting projects !!!

Great American Coffee Test

Around 4000 People with variable background were served 4 different Coffee

Learn more

Coming Soon

Will be out soon !!!

Coming Soon

Will be out soon !!!