Friday, October 16, 2020

Consolidate Power BI Sales Reports to Effectively Track Global Performance


Business Case:

Our client, the sales team at a multinational software company, needed to track product performance. The sales team, which operates across multiple geographies, previously used several Power BI reports to track the effects of global marketing. Each role within the sales team had its own Power BI report, but the team was unable to view the reports collectively. In addition, our client’s existing system operated with multiple upstream sources. Tracking information across reports was a timely and complicated process.

Our client needed a way to track global product performance and to identify customer accounts with upsell opportunities.

Key Challenges:

  Create a unified Power BI sales report  
  Provide up-to-date product information across accounts  
  Identify upsell opportunities within accounts  
  Implement role-based security  

Our Solution:

We consolidated our client’s seven Power BI reports into two sales and marketing reports. One Power BI report targets leadership and management while the other is for sellers and team members. The reports offer a customized view for each user based on role and access details.  

Figure 1: Solution Architecture

Our Power BI report can capture and display data at various levels in less than 10 seconds. We replaced the existing live connection system with an import data model that is able to pull data from multiple sources with a faster load time. 

To consolidate our client’s reports, we examined their existing seven reports for common data points. We performed a gap analysis to determine which reports were critical, which were redundant, and which could be combined. By reducing the quantity of reports from seven to two, we reduced maintenance efforts. With numerous upstream data points, our client struggled to ensure consistency across reports. Our solution resolves this challenge by checking the health status of the upstream server and pulling data into different tables.  

To ensure continued security, we implemented different access levels based on the report user. Our leadership report enables the leadership team to access data at an area level, enabling a complete view of product marketing numbers. The seller report uses row-level security (RLS) to ensure users can only access their own data and the data of their direct reports. The seller report requires users to select their name from a dropdown, ensuring they receive the correct security access. Both reports consolidate relevant product information, including sales stage, engagement details, relevant milestones, and close rates. 

The Power BI report can be filtered by region, product, manager, and more. The report also offers insights into future upsell opportunities based on an account’s performance in the last month.  

Business Outcomes:

Our consolidated Power BI report enabled our client to gain quick access to product marketing and sales insights. Our solution enables sellers and managers to target key accounts, presenting detailed and up-to-date data in a single location. Users can check for relevant key performance indicators (KPIs) based on their role. We also added a KPI that enabled our client to gain insight into accounts that offer an upsell opportunity based on the last month’s performance.  

By using an import data model, we reduced page load time (PLT) from 25 to less than 10 seconds, enabling speedy access to critical data. We increased time savings for users who now only open one report to view all relevant data. At the time of writing, our solution has 200 weekly users. 

Highlights:

    Reduced the number of Power BI sales reports from seven to two by integrating key performance indicators (KPIs) into a single role-based view
    Reduced page load time (PLT) from 25 seconds to under 10
    Enabled daily data refresh
    Developed a key Power BI report for our client, with 200+ weekly users