An Interactive Dashboard to Unlock Sales Potential

Download PDF

The Case

MavenTech (a fictitious company) sells computer hardware to large businesses. They have a new CRM system that tracks their sales opportunities, but outside of the platform, they have no visibility of the data.

In order to become a more data-driven organization, they would like a set of data visualizations that allows sales managers to track their team’s quarterly performance.

The CRM Dataset

  • Structure: 4 tables (sales pipeline, accounts, products, and sales teams)
  • File type: CSV
  • # of Records: 8800
  • # of Fields: 18
  • Source: Maven Sales Challenge

Solution Overview

Considering the outlined scenario, I approached the project as the team’s BI Developer and created an interactive dashboard to help sales managers track team performance.

Taking into account that Sales is a fast-paced environment, I designed the dashboard to show the overall team performance at the top so managers can access high-level information quickly. The bottom section contains a selection of the metrics used at the top, but these show the performance of team members.

To complete this project, I achieved the following objectives:

  1. Prepared the data with Power Query and created a relational model with Power Pivot.
  2. Identified sales metrics.
  3. Built the dashboard using pivot tables, pivot charts, and slicers.

Identifying the Sales Metrics

After completing some exploratory data analysis, metrics related to revenue and the sales pipeline were identified to provide visibility of various elements of sales performance.

Financial Health &
Product Performance

  • Quarterly Revenue
  • Quarterly Revenue by Product Line
  • Average Deal Value

Productivity &
Pipeline Management

  • Deals Open
  • Deals Won
  • Deals Lost

Sales Effectiveness
& Efficiency

  • Win Rate
  • Close Rate
  • Average Deal Length

Creating the Dashboard

At the top of the dashboard, managers may filter the data by year, region, and regional manager. At the team level, the dashboard provides an at-a-glance view of quarterly trends and YTD performance. At the agent level, it provides YTD performance with the option to select a specific quarter for a point-in-time view.

Team-level Visualizations

Quarterly trends

Quarterly trend & proportions

YTD metrics

Agent-level Visualizations

Except for the quarterly activity, the same team-level metrics are used to show agents’ performance with the option to filter by quarter or YTD.

Tech Spotlight

Data Preparation

To prepare the data, a connection to the four tables was setup in Power Query. Some data cleaning was done and then, custom columns were added.

To the sales pipeline table, a column was added to tally the days between the engage date and the close date. Because several dates fall on nontraditional working days, weekends and holidays weren’t excluded. A second column was added to have a single date for each opportunity. If the deal closed, the close date is used; if not, the engage date is used. This allows the data to be filtered by quarters using just one slicer.

In the products table, the series column was edited to group products by product line.

To the sales team table, two custom columns were added to have just the initial for employees’ last names. This helped reduce the space needed for two of the dashboard slicers.

Data Model

With the data preparation step done, the tables were added to Power Pivot and a one-to-many relational model was created from the accounts, products and sales teams tables to the sales pipeline table.

In addition, DAX was used to create measures for calculating the win rate and close rate.