Concentrix case study

Data Cleaning

1-Checked for duplicate values in the Contract Number column using column distribution in Power BI (Not found)

2-Checked for null values at important columns using column quality (Contract Number, Account Name, Sales Representative, Contract Status, Expiration date and Previous Contract Amount) and there weren’t any nulls

3-Found nulls in the New Contract Amount column (which was a logical and expected as there must be no values if the contract status was cancelled)

4-Unified the data format in all dates columns

5-Checked all the data types to ensure they’re correct

6-Verified that each contract status “Cancelled” corresponds to a null value in the New Contract Amount Column

Data Transformation

1-Replaced the nulls with zero in New Contract Amount column in order not to affect the consistency of the data

2-Trimmed all the text columns in order to use any of them as a filter in DAX calculations

3-In New Contract Bucket column, replace the nulls to be Not Renewed (to be easier to read and understand)

4-Couldn’t replace the nulls in renewal date because of the data type mismatch

5-Managed to neglect the nulls in all the visuals related to the Renewal Date

6-Created a measure to get the average of sales

7-Created a measure to get the average value per contract

8-Created 2 measures for calculating the expiring contracts amount and renewed contracts amount to be able to calculate IQRR

9-Created a measure to get number of contracts

10-Created a measure to get number of contracts by quarter

11-Created 2 measures to get total of new contracts amount and previous contracts amount

Visualization and Analysis

1-Created the 3 main performance visuals to compare the performance by region which were the no. of contracts, total sales (new contract amount) and average sales which insighted that APAC region was the highest (with big difference than the others) but when looking at the average sales per region, NALA was greater which might indicates one of the following :

- You might be targeting a budget-conscious customer base in APAC

- You might be selling lower-priced products or smaller packages in APAC

- You might be targeting a premium customer base in NALA

- You might be selling higher-priced products or larger packages in NALA

- Your sales team might be more skilled at closing high-value deals.

2-Based on the total and average amount of the previous and new contracts amount, we detected who was the best/worst sales representative and partner

3-In historical performance, You experienced significant fluctuations in both total sales and number of contracts throughout the period. Quarters with high sales often coincided with high contract volume, suggesting a connection between sales volume and the number of deals closed. The sharp drop in total sales and number of contracts in the last quarter of 2022 is a cause for concern.

4-The cause of the dropping in Q4 is not something that happens seasonally as in the previous years, Q4 was making a good performance so I suggest External factors as events in Q4 2022 (economic downturns, competitor actions) that might have impacted sales?

Internal factors: Did your company experience any internal changes (marketing strategy shifts, sales team turnover) that could explain the decline?

Average Contract Value:

•The peak in average contract value in Q1 2020 suggests you were either:

oSelling more high-value products or packages.

oTargeting a premium customer segment during that period.

•The subsequent drop and plateau in average contract value could indicate:

oA shift in focus towards volume over value.

oChanges in product mix or pricing strategies

Recommendations:

•Investigate Q4 2022 drop: Analyze the reasons behind the decline in total sales and number of contracts.

•Compare sales cycles: Look at the average sales cycle length in each quarter. Did longer sales cycles contribute to the Q4 2022 drop?

•Segment customer data: Analyze customer demographics and buying behaviors by quarter. This might reveal insights into fluctuations in average contract value.

•Consider seasonality: Plan marketing and sales strategies to address potential seasonal dips.

Strategies recommended:

•Smooth out sales fluctuations: Implement targeted campaigns or promotions to maintain sales momentum throughout the year.

•Increase average contract value: Explore upselling, cross-selling, or introducing higher-value products/packages.

•Optimize sales efforts: Focus resources on strategies that maximize both sales volume and value based on market conditions.