CRM Pipeline Analysis [Zoom Charts - Winner]

Objective: A company aims to evaluate its CRM data and sales pipeline for leads registered over the last five months. The task is to build a comprehensive analytical report that provides insights into lead distribution across countries, industries, and organization sizes. The report will assess the health of the sales pipeline, and compare sales agent performance.

The report needs to provide solutions for ad-hoc requests:

Who are the top-performing sales agents?

What are the conversion rates from leads to paying customers in different countries?

How does sales performance vary across different industries?

How healthy is the sales pipeline month-over-month, considering the number of leads at each stage of the sales funnel?

What is the average time taken by sales agents to respond to new leads, and how does it impact conversion rates?

What are the trends in average deal values? Are there noticeable fluctuations?

Does the size of the organization affect the sales cycle duration and deal value?

How effective are sales strategies for different products offered (e.g., SAAS vs. other product types)? Compare the deal closure rates and values between products.

Assess the time taken from lead acquisition to deal closure. Which factors (country, industry, agent) influence the duration of the sales cycle?

Investigate the common characteristics of lost opportunities. What are the primary reasons for deals not closing, and how do these reasons vary by industry or country?

Process:

1. Cleaning the data: Utilized Power Query for data cleaning.
2. Creating a DAX-based date table: Developed a date table using DAX.
3. Creating the data model: Constructed the data model incorporating cleaned data and the DAX date table.

Before diving into analysis, it's essential to establish boundaries.

Assumptions: Assuming monthly performance discussions occur in the first week, focusing on metric review.

Limitations: Due to limited information, I decided to prioritize a few requirements and skip forecasting. Therefore, my focus was on recent pipeline performance.

I concentrated on two key issues: Closed deals and Churned customers.

After completing data modeling and exploratory data analysis (EDA), I found that most metrics have underperformed in recent weeks.

I curated the MTD Performance report based on KPIs, This approach helps us understand selected metrics at a granular level, enabling each team to focus effectively.

Taking inspiration from the recent SQL BI 3-30-300 rule video (big shout out to Kurt Buhler), and incorporating insights from various YouTube resources (Thanks to Injae Park and Bas Dohmen for their awesome PBI content).

My analysis revolves around these metrics.

Metrics:

• Closed Deals: Number of deals successfully closed
• Closed Deals Value: Total amount from closed deals
• Avg Deal Value: Average closed deal value
• Churned Customers: Customers who left after using our service
• Conversion: Total deals to closed deals ratio
• Duration: Number of days to resolve issues

The Report contains 3 pages:

Overview Page:

• Provides an overall context of current activities.
• Shows status pipeline of all leads.
• Displays closed deals trend on a weekly basis.
• Analyzes agent performance by breakdown.
• Evaluates the influence of closure days on agents and categories.
• Breakdown by each category based on the metrics.

MTD Deals Page:

• Highlights performance variances of selected metrics across different categories.
• Compares agent performance month-on-month and against the previous month.
• Offers a status pipeline view for leads.
• Provides daily granularity of current month's selected metric performance.
• Breakdown of selected metrics by categories.

Details Page:

• Features a table based on closed deals, providing basic context for remaining leads.
• Allows for deeper analysis through data slicing for more granularity.

Finally Suggestions:

• Employee performance appears to be declining; consider reviewing employee satisfaction scores.
• The number of churned customers is alarming and requires attention. Implement proactive measures to enhance customer retention and satisfaction.
• There is observed high volatility in the average closed deal value. Implementing strategies to stabilize this could significantly impact overall revenue and profitability.