CRM Sales Opportunities

Maven Tech's CRM Sales Opportunities Dashboard

Objective

As the BI Developer for MavenTech a computer hardware vendor to large businesses. My task was to create an interactive dashboard to enable the sales managers to track their team's quarterly performance. I also provided for executives to see an overview of company performance. We retrieved the information from the CRM system and there is no visibility of the data outside of the platform.

KPIs Analysed

Revenue

• Total revenue. The dashboard shows the total revenue from won deals.
• Revenue per quarter. The report shows the revenue for each quarter and allows drilling down to the month level for further information.
• Manager revenue. You can see the revenue for each manager.
• Region revenue. You can see the revenue from each regional office.

Products

• Revenue from series. The revenue raised from each product series is shown.
• Deals for each product. We can determine which products are popular by examining the number of deals for each product.
• Sales Current Quarter vs Previous Quarter. This compares the sales of the last two quarters for each product.
• Total Sales vs Expected Sales. This compares the closed deal value against the product selling price for each deal. This shows if MavenTech are offering substantial discounts to close a deal.

Accounts

• Customer revenue. This shows the revenue from each customer.
• Parent company revenue. This shows the revenue from the parent company of customers if the have one. This could be useful to help with the sales teams customer focus.
• Country Revenue. This shows the revenue from each country. Customer product revenue. This shows the revenue from each product purchased by customers. This is useful for reviewing the product line.
• Deals won and lost. This shows the win rate for deals.

Sales Teams

• Manager revenue. You can see the revenue for each manager.
• Deals stage. This shows the number of deals won, lost, engaging and prospecting for each sales person and is a good indicator of the performance of each agent.
• Sales Funnel. Here, you can see the number of deals at each deal stage.
• Sales agent product sales. This shows the sales for each product for each sales agent and is a guide to show the performance of each salesperson. It is useful for product line review.

About the Dataset

The dataset provided for analysis was a set of csv files exported from the CRM system. The files were

• accounts.csv
• products.csv
• sales_pipeline.csv
• sales_team.csv

A data dictionary was provided to explain each field in the dataset.

The data provided was very clean the only change to the data required was to change "GTXPro" to "GTX Pro" in the product column of the sales_pipeline table.

A data table was created to allow the use of time-intelligence functions.

Following best practices. The accounts, products and sales_team tables were made to be dimension tables by adding a primary key to each table. Then merging that key into the sales_pipeline fact table. We removed the relevant column from the sales_pipeline table. leaving only the added foreign key. We linked the date table to the fact table by using the close_date column. I also established the relationship between the engage_date and the date table, but we made it inactive.I created a manager dimension table from the sales_team table and linked it to the sales_team table. We created another dimension table called parent_dim from the account table. They established a link between this and the account table. These two extra dim tables have made the data model a snowflake schema. Creating the dimension tables has little effect on the current dataset size. However, as more records are added in the future, creating the dimension tables will help keep the size of the dataset smaller.

This image shows the data model for the report.

Report Creation Process

The process for creating the report followed the following steps:

Scoping the project: The project was scoped from the project brief. The targe audience was determined to be sales managers and executives.

Gathering data: The files were downloaded and placed in a file repository.

Cleaning data: The files were imported into Power BI and Power Query was used to prepare the data for analysis. As the data quite clean there was not much need for data cleaning only "GTXPro" was changed to "GTX Pro" in the product column of the sales_pipeline table. To assist with analysis dimension tables and a date table were created.

Exploring Data: Some basic EDA of the dataset was undertaken using Power BI visuals. This process helped explain the data. No outliers that could be removed were detected.

Modelling Data: The data model used was a snowflake schemas due to breaking out parent companies and sales managers from their dimension tables into their own dimension tables. One-to-many single direction relationships were used between all tables.

Creating Visuals: In Power BI separate pages were created for each area of analysis and a home page containing summary information were created. The analysis pages created were revenue, product, account and sales team. Suitable visuals were added to each page. Static cards were added to the top of each page that are not affected by filtering on visuals. However, the Manager and Quarter slicers will affect these.

If you find this dashboards is useful then please leave me a comment. If there are any problems with the dashboard then please let me know in the comments and I will take the comment into consideration and will update the report if the change is suitable. I read every comment left and I look forward to hearing from you.