Precision Sales – Leveraging Alteryx to Decrease Drive Time and Distance

[AlteryxAdvocacy]

Author:

Victor Faria

Role:

Business Analyst

Company:

Gradus Consultoria de Gestão

Overview of Use Case 

The B2C market has been facing increasing competition and market share dispersion. In this context, companies need to optimize sales and distribution efforts to remain competitive. Taking advantage of the availability of market data and geolocation, the model developed in Alteryx supported large organizations operating in the Brazilian market to optimize sales, distribution and redirect investments to the most profitable channels.

Describe the business challenge or problem you needed to solve

The customer (consumer goods industry) was not defining the performance of each salesperson. Thus, there was a large overlap of region between different sellers, which leads to inefficient sales and distribution. Our project restructured the customer business processes, optimizing their sales and distribution teams.

We noticed that the customer also didn’t have a process in place to define their customer portfolio and the region of operation of each sales person. Gradus then analyzed their portfolio distribution and helped them to better allocate their sellers. 
As the client did not have any routing tools, we needed to find something for this type of analysis. The tool of choice was Alteryx, for its versatility, processability and high range of tools capable of doing geospatial data analysis, as well as allowing the development of a structured algorithm easily replicable in other scenarios.

We developed an optimization model to minimize the global distance, with boundary condition over the workload and distance limit of each area, using standard tools and the R-Prescriptive package.

Describe your working solution

The solution was entirely developed in Alteryx Designer, with a macro for easy replication. The algorithm is based on the optimization of the distance traveled based on geospatial data and visiting schedule provided by clients in Excel spreadsheets.

Model Overview

As a first step, the Find Nearest tool calculates the road distances from each point of sale to each of the defined area and using Alteryx’s geo data it allows you to quickly calculate the distances and travel times between two points, taking into account factors such as traffic and the means of transport used. Thus, it is possible to have a more assertive result than using the Distance tool.

Then, with the aid of Alteryx's R-Prescriptive package Optimization tool, the algorithm solves the objective function of minimizing total displacement, obeying the boundary conditions that each Point of Sale can only be served by a single area (vendor) and that each salesperson has a workload limit based on the planned visit time.

Thus, using the Spatial Match tool, we can obtain sales areas with better distributed work demand and shorter travel distances, reflecting in more time for customer service, prospecting and possible expansion of the sales portfolio.

The entire result can be viewed graphically both in Alteryx's own workflow, as well as in PowerBI dashboards that are built to make it easier for the Market Intelligence team to track sales teams.

Macro workflow

Pre modeling scenarioPost modeling scenario

Describe the benefits you have achieved

The tool was tested in the sales zone of Rio de Janeiro and it proved to be robust, broadening the analysis to cover the other highly complex sales zones (Greater SP, RJ Interior, etc.). The use of the tool drew attention within the client and therefore we also analyzed the distribution of visiting portfolios for merchandising promoters in Greater Sao Paulo.

With reduction of the total area from 13,000 km² to 9,000 km² (31% reduction) and Drive Time reduction of 7% and Drive Distance of 9%, the customer was satisfied with the result presented and they agreed that the proposed allocation will bring more efficiency to the sales force.

In addition, the tool proved to be simple enough to allow frequent updates whenever a redistribution is required after migrating customers from the indirect channel to the direct channel or when a new salesperson is added to the sales team.

Run time varies with how tight the constraints are, ranging from a few seconds (pre-calibrated areas and loose bend conditions - 8.3% load off) to 5 hours (narrow bend conditions - 3, 6% load off). The boundary condition may be looser in distance since it is the optimized parameter.

The same analysis procedure has already been applied to two other customers in different segments, generating satisfactory results again, bringing significant reductions in the distances traveled and areas covered by the sellers and, in some cases, even allowing us to work with a leaner team without losing quality of service.

How does Alteryx make you feel?
Alteryx enables the consultant team to perform more robust, easily replicable, modularizable analyzes with greater complexity and information. It also makes it possible to merge, in a single environment, analytical tools of different natures, as in the given example, where prescriptive and geospatial tools were used. In this way, Alteryx gives our consultants the agility and confidence to deliver value to our clients through sophisticated analysis with a high level of accuracy and replicability.

What are you most excited about when it comes to the future of analytics?
With the advancement of artificial intelligence and machine learning, it has become increasingly feasible to conduct more sophisticated analysis and generate more valuable insights. Moving from a scope of descriptive and diagnostic analysis to predictive and prescriptive analysis brings, in addition to value generation, an extremely motivating analytical challenge.

Moreover, the emergence of working tools for citizen data scientists to perform such analyzes without the need for in-depth knowledge of programming languages and algorithm structuring significantly broadens the horizon of possibilities for building smarter predictive and prescriptive models. The new features demonstrated by Alteryx during the Gartner D&A Summit are a prime example of this type of tool, which assists the user in designing the model to achieve the expected result in its analysis.

[Bryth]

GREAT WORK AND USE CASE. this is exactly the problem we need to solve