Determine Customer Impact on Sales Volume

[DanielleR]

Author:

Cesar Robles

Role:

Sales Intelligence Manager

Company:

Bavaria S.A.

Determine Customer Impact on Sales Volume

Originally Published: 2016 Excellence Awards Entry 

Describe the problem you needed to solve:

In September 30^th 2015, a gossip widespread through whatsapp reduces our Pony Malta sales to 40% of normal levels. The social networks’ gossip that impacts a brand destroys brand equity and creates distrust in our customers. Our company executes a 1^st stage plan that helps to spread the gossip in the first weeks to more customers increasing the crisis. In Colombia no brand had suffered an attack like this before.

Describe the working solution:

The Alteryx solution was develop to design and decision tree that define which customers has an relevant impact in sales volume in 5 groups that allows define differentiated protocols to recover our sales in a healthy way. These 5 groups were:

1. Citizens: Actual Customers without any impact related to social network crisis.

2. Refugees: Customers that reduce significantly (<50%) his rate of sales related to social network crisis.

3. Deportees: Customers that didn’t bought our brand related to social network crisis.

4. Pilgrims: Customers with doubts about our products related to social network crisis.

5. Aliens: New customers without any impact related to social network crisis.

Our gap in crisis was 180k PoS (Point of Sales) impacting 92 KHl (Kilo-hecto-liters)

This workflow runs monthly and uses multiple sources of information in SQL server related to Customer properties and historic sales levels. We report some results in Excel and Java applications to define our performance in recovery actions. Actually we are migrating to in database process to optimize the algorithm performance and use Tableau to manage our visualization process.

Figure 1. Decision Tree description

Figure 2. 1st Quarter Deportees results

Figure 3. 1st Quarter Refugees results

Figure 4. 1st Quarter Citizens results

Figure 5. Numerical Distribution Initial and End State

Figure 6. Blending Workflow

Figure 7. Decision Tree workflow

Figure 8. Hierarchy and Priority workflow

Describe the benefits you have achieved:

The project defines a new way to customer segmentation in our company. We use the same algorithm to define not only crisis contingence, also we used to brand expansion and price control process including geographical variables and external info of our providers (Nielsen, YanHass, Millward Brown).

The solution had not been implemented before Alteryx. An estimated time saving show us that initial state needs 2 or 3 weeks to develop compared with 4 or 5 days that we used in Alteryx (We just used it 1 month ago in the firs solution). Right now our response time is less than 2 days in similar solutions.

In Business terms, we achieve to recover 100k PoS (approximately 25% of all Colombia Market) and increase our sales in 75% of normal levels in the first 3 months. In August 2016, we recover our normal levels of sales with the trade marketing actions focused support by Alteryx workflow.