MavenTech Sales Dashboard

1.0 Overview:

MavenTECH is a B2B computer hardware business that has recently implemented a CRM system to manage their sales activity, however the system provides limited functionality for performance monitoring of the sales teams. The aim of the project is to develop an interactive dashboard that enables sales managers to track their team's quarterly performance.

2.0 Design Approach:

Thinking like the end user, if I am a Sales Manager, my focus is on revenue generation and I want to monitor my team's performance in 3 key areas:

1. How are we performing compared to the rest of the organization?
2. How are my individual team members performing compared to each other?
3. How is my product portfolio performing?

These 3 questions were the guiding framework when developing the dashboard.

3.0 Data Preparation & QA:

Data was loaded into the Power Query editor and checked using the column quality, distribution, and profiling tools. The data was pretty clean, with only one error that needed to be corrected: The product GTX Pro was listed without the space (GTXPro) in the fact table, so I used the replace values to fix it. I added a calculated "cycle_time" column too, which converted the time delta between engage_date and close_date into a numeric value of days.

There were 4 .csv files supplied for the project; 1 fact table (sales_pipeline) and 3 dimension tables (accounts, products, sales_teams). Data modelling was quite straightforward, with obvious primary-foreign keys, resulting in a simple star schema. A separate calendar table was added, with an active relationship to the "close_date" column, and inactive relationship to the "engage_date" column.

4.0 Exploratory Data Analysis:

I spent a lot of time on EDA for this project: 1. to really understand the data, and 2. to justify my selection of KPIs and visuals for the dashboard.

4.1 Understanding the Data:

I created some quick column charts to visualize the sales generated by different agents, Darcel Schlecht stood out as an outlier, and I wanted to check this wasn't due to a data quality issue so I created a histogram to check the distribution of sales for Darcel and the other agents:

The distribution suggests that no single transaction was causing the high total, the high value transactions correspond with the high value MG Special products, and that Darcel's great performance appears genuine. The other agents' transaction all appear genuine too.

I also created a line chart to visualize the trends in revenue over time. There was quite clearly a 3 month seasonality to the data, which supports the use of quarterly metrics as suggested in the brief.

4.2 Justifying KPIs:

The data allowed for various different KPIs to be calculated, including:

• Total Deals: the number of contract negotiations entered.
• Wins: the number of deals won.
• Losses: the number of deals lost.
• Close %: the percentage of deals won or closed.
• Ave Sales Cycle: the average time taken to close a deal.
• RRP Variance: the average deviation in selling price away from the recommended retail price.
• Ave Sales Price: the average value of a closed deal.

This seemed like a lot to me, and rather than risking KPI overload, I wanted to determine which are the most important for monitoring agent performance. I started by creating a summary table of the sales agent's QTD performance for all the KPIs using CALCULATEDTABLE(SUMMARIZE(sales_teams, sales_agents ... ), then visualized the data with some simple scatter plots:

I expected No. of Wins to correlate with revenue, but was surprised that No. of Losses had a strong relationship, and therefore so did Total Deals. This led me to believe that Total Deals is the more powerful predictor of revenue, regardless of the outcome. The more chances you take the more money you make! It looked like there might be some weak relationships between Sales Cycle and Sales Price, but nothing for Close % or RRP Variance.

To investigate further, I performed some regression analyses offline in a tool called SigmaXL (www.sigmaxl.com). I checked for correlation using Pearson and Spearman regression techniques:

The Pearson results show a significant positive linear relationship between Total Deals and $ YTD; as Total Deals increase, revenue increases.

The Spearman results show a significant negative monotonic relationship between Ave Sales Cycle and $ YTD; as the Sales Cycle goes down, revenue increases.

Both methods highlight relationships between Ave Sales Price and $ YTD; as average sales price increases, revenue increases; and both show a relationship between Sales Cycle and Sales Price; as the price increase the cycle speeds up.

There are no significant relationships between Close % or RRP Variance and $ YTD.

4.3 EDA Conclusions:

The regression analysis shows that Total Deals entered is the strongest predictor of revenue and should be the secondary performance metric, behind revenue itself. Average Sales Cycle is a moderate predictor with a monotonic relationship to revenue, so this should be included as a metric too.

Ave Sales Price influences revenue directly and indirectly through Ave Sales Cycle, however this is a function of the product mix rather than agent performance - you could sell lost of low value products and not create much revenue! Thus the product mix offered is an important factor in how well an agent performs but not necessarily a metric (NOTE: we have no info on product lifecycles or marketing strategy, it could be that low performing products are new to market, or mature and declining).

Close % and RRP Variance have negligible effect on revenue, therefore shouldn't be included in the main KPIs.

5.0 Dashboard Development:

The dashboard was created with 3 pages, based on the 3 performance criteria: team vs team comparison, agents vs agent comparison, and product vs product comparison. Scalability was a major consideration, the clients CRM system is new, the data will grow over time.

5.1 Team vs Team Comparison:

I wanted this to be the landing page for the dashboard, and the first place a manager would check to see how their team is doing:

KPI cards give a quick overview of quarterly performance, compared to the previous quarter. The league table shows the selected teams ranked position based on YTD revenue, and the line chart shows their relative performance over time. The bar and doughnut charts show the teams proportional contribution to company revenue for the selected period.

Challenges on this page were mostly around selective filtering of visuals. I wanted the team manager filter to highlight which team was selected, without filtering out the others. I achieved this with a combination of calculated tables that were unrelated to the primary model, the selected value function, and IF statements.

I used the Gestalt principle of similarity, and consistent colors to tie the selected teams data together.

5.2 Agent vs Agent Comparison:

This page is a deep dive into sales agent performance, across the 3 main KPIs: Revenue, Total Deals, and Sales Cycle:

I used color formatting and reference lines here to show the agent's performance relative to the rest of the organization. I also built in a little prescriptive analytics logic; recommending the top performers for a bonus (90pth+) and the poor performers for additional training and support (25pth-).

One of the challenges on this page was the conditional color formatting based on percentile brackets. To solve this I created a calculated table, summarized for each sales agent for the various KPIs. Then I used nested IF statements with the percentile function, to hard code in the relevant hex color codes.

I use the Gestalt principle of proximity to group the visuals into the 3 KPI categories, and the principle of continuity to relate the product category labels on the first chart to the corresponding bars on the others.

Product vs Product Comparison:

This page shows the revenue per product for the current/selected period and the composition trend over time:

The slicer at the top of the page allows you to track product performance for the whole team, or individual agents. The bar charts show revenue by product, average sales price, RRP variance*, and cycle time per product. The stacked area chart shows the composition of revenue, and trends over time for either products, or the market sectors they are sold too. Finally, the heatmap shows the geographic regions that generate the most revenue, and whilst the data is not very rich right now, this could change over time as international sales grow.

The intention for this page is to provide insight into how and why products perform. RRP variance is not strongly correlated to agent revenue, but could still be useful for corporate pricing strategy if certain products regularly sell under/over price.