__STYLES__
The data is based on a fictional company that sells computer hardware, and includes data on accounts, products, and sales activities.
Goal
To develop two interactive dashboards:
The first interactive dashboard is geared towards sales performances which would allow managers to track metrics related to their team sales.
The second interactive dashboard provides an overview of sales performances throughout the company for key stakeholders and executives.
Description of dataset
Four data files in CSV format were loaded in Power BI:
The data_dictionary file was used to understand the data available. Data cleaning and formatting were conducted prior to data analysis.
Data Modeling
All tables in the model was connected to a central table in order follow best practices of a star schema design. I created a date table to enable consistent and centralized management of date-related calculations and filters across data sources. I connected the date_table to the sales_pipeline table using the common column between both tables.
Connection between both tables. (sales_pipeline(close_date) <-----> date_table(Date))
First dashboard elements
In the table, "KPI Overview per Agent", blue indicates positive outcomes in KPIs, whereas pink highlights areas where outcomes are less favorable.
Metrics added on first dashboard
Total wins - Shows how many deals the agents converted into a win.
Total lost - Shows how many deals the agents were unable to convert into a win.
Win % - Shows the percentage of deals that an agent successfully converted into wins.
Lost % - Shows the percentage of deals that an agent was unable to successfully convert into wins.
Avg Days to close - Shows the total amount of days it took an agent to engage with an opportunity until closed.
QoQ % - Shows the win percentage from quarter to quarter.
MoM % - Shows the win percentage from month to month.
Revenue – Revenue generated from wins.
Filters added on first dashboard
Products - can filter by a specific product
Agents - can filter by a specific agent
Quarter - can filter by specific quarter
Manager - can filter by a specific manager
Second dashboard elements
Metrics added on second dashboard
Revenue – Revenue generated from wins.
Wins - Shows how many deals were converted into a win.
Total deals - Shows total amount of deal opportunities.
Manager(s) Win % - Shows the manager's win % out of all other managers.
Region's Win % - Shows the total win % that the region has generated.
Manager(s) Win % in region- Shows the manager's win % compared to other managers in that region.
Key revenue drivers- Shows the top performing factors based on revenue generated, including:
Account, Agent, Sector, and Product
Filters added on second dashboard
Sector - can filter by a specific sector
Region - can filter by a specific region
Quarter - can filter by specific quarter
Manager - can filter by a specific manager
Insights
The dashboards are set on the manager (Melvin Marxen) for Quarter 4 in 2017.
Quarter 4 had a total of 437 deals resulting in a 56% win rate, generating a total of 670k in revenue. There was a steady increase in wins throughout the quarter. October and November had more losses, whereas December had more wins. The most revenue generated in quarter 4 was in December (256k), and the lowest revenue generated was in November (200k).
The GTX Pro product generated the most revenue in all sectors totalling 356k. The technology sector generated the most revenue (117k). Overall, out of all products, GTX Plus Basic is the only product that had more losses then wins, whereas GTX Pro had the most wins (74).
The average amount of days to close deals was 43.73 days in quarter 4. The agent who has generated the most revenue is Darcel Schlecht (356k), making half of the total revenue for quarter 4. Darcel is also the only agent who generated the most revenue in each sector. Marty Freudenburg is the only agent to make more wins in this quarter compared to the previous quarter. Melvin Maren generated 20,3 % of wins from the entire company, and contributed to half of the wins (53.3%) in his region (Central). Out of the 3 regions, Central region generated the most amount of wins.
Conclusion
These interactive dashboards provide key performance indicators that are tailored towards managers and executives who want to understand how their teams are performing on a quarterly basis.