Web Gis for geomarketing and mobile mapping: Peroni beer company case study

Salvatore AmaduzziUniversity of Udine

(Italy)

Goal of the project

Peroni, the first Italian producer of beer, wanted to:• balance and optimize the salesmen areas • organize the everyday agenda in order to visit the

customer in the right time window, the correct number of times per month, ....

• optimize the routes of the salesman• support the salesman on the field.

Peroni sales networkPeroni sales network is divided into two networks called ASH (Account Supervisor Horeca) and ASW (Account Supervisor Wholesalers).

ASH serves end distributors such as hotels, restaurants, café, etc., while ASW target are the wholesalers.

The aim of the project was shared by the two networks because it consisted in optimizing the everyday agenda for each salesman.

Why GIS

Peroni have heard about location intelligence and GIS but never experienced it.

We got in contact with them because of a student stage and thesis.

They accepted to test GIS tools

WEBGis Lab of Udine University

Project startup

After some meetings we agreed that the optimization of everyday work should obtained through the reduction of the driving time from one store to another and the increase of the presence in the customer store.

The analysis of sales network and the determination of the best agenda for staff has been done respecting specific constraints established by Peroni itself.

Project startup

• redraw of salesman areas fitting the constraints of Peroni

• normalization and geocoding of the database of the stores and of the salesman starting points

The activities defined in order to reach the goals have been:

• optimization of the routes• test a WFM solution for field support

• creation of everyday agenda

WEB Gis used toolsOur GIS lab used MAPfinder a WebGis infrastructure made available by Tellus SPA, a private GIS company. Used MAPfinder modules are:

• ADDRESSfinder, a WebGIS for normalization and geocoding;

• SITEfinder, a WebGIS that automatically optimizes/balances the zones allowing parametric definition of constraints;

• PATHfinder a WebGIS routing application that optimizes the routes considering time windows, costs, ...;

• MARKETfinder a WebGIS geomarketing application allowing analysis, creation of thematic maps, redistricting, ….;

• PALMfinder, a tool on PDA to support the field activities of salesmen

Normalizing and geocodingAll the data bases have been normalized and geocoded using ADDRESSfinder. This powerful tool geocoded 98% of the records automatically.

Some more man hours of work completed the work.

Normalizing and geocodingresults

ASH customers and offices ASW customers and offices

ASH data and constraintsThe ASH network of Peroni in Italy consists of 14,936 points of sale (PoS) and the goal was to balance according to the following constraints:•areas with no more than 120 PoS; •tripartite division of PoS in Gold, Silver and Bronze depending on the frequency assigned to each quarterly visit, 6 for Gold, 3 for Silver and 1 for Bronze; •the duration of each visit to PoS is set at 35 minutes; • each salesman should not drive for more than 200 km

per day; • the monthly working days for each salesman is 14 with

8-hour workday, one hour lunch break and exclusion of the trip from house to work;

• minimum number of daily visits 8.

ASW data and constraints

• creation of areas of approximately 20 PoS; • the length of the visit is 90 minutes; • maximum of 3 visits per day; • each salesman should drive maximum of

300 km per day.

The ASW network of Peroni in Italy consists of 669 distributors/wholesalers and the goal was to balance according to the following constraints:

Areas optimisationSITEfinder, a genetic application, allowing to specify the constraints and automatically

optimizes and balances the salesman areas. Millions of operation in few minutes to obtain the best solution of the problem.

Areas optimisationUsing the redistricting function of MARKETfinder, the geomarketing tool, we manually modified the areas that showed some problemsMost of the problems were due to the fact that the density of the PoS is very different and the genetic software has not been able to respect correctly the constraints in those areas.

Areas optimisation resultsAfter the process the results where the following: •125 areas were assumed in accordance with the following calculation: (14,936 total PoS/120 PoS per salesman). At the same time it was considered important to respect the time parameter calculated in a range between 11,000 and 12,500 minutes

• this last parameter was determined by taking the 35-minute visit, multiplied by 42 working days (14 days per month for 3 months) and multiplied by the number of visits per day per salesman measured in the quarterly period.

• the areas have been balanced considering this parameter in time and according to that defined by Peroni of 120 to PoS per salesman.

Agenda optimisationPATHfinder, a routing application, was used to generate for each of 120 areas the best visiting routes for the 42 working days in the quarterly period taking care of:

It finds the cheapest routes respecting all the constraints.

• activities to be done• costs of vehicles and personnel•best time for roads transit (schools, market, …)•…..

Agenda optimisation results

Agenda optimisation resultsDue to the different density some day of some salesman was going

in overtime. Weekday

Driving distance

Total time

Time window

Starting time

Arrival Time

Number visits

Sett1_gg1 318,129 11:09 09:00-18:00 09:00 20:09 3Sett1_gg2 148,124 07:19 09:00-18:00 09:00 16:19 3Sett1_gg3 222,341 08:22 09:00-18:00 09:00 17:22 3Sett1_gg4 162,917 07:39 09:00-18:00 09:00 16:39 3Sett1_gg5 299,673 08:51 09:00-18:00 09:00 17:51 3Sett1_gg6 249,465 09:00 09:00-18:00 09:00 18:00 3Sett1_gg7 222,363 08:25 09:00-18:00 09:00 17:25 3Total 1623,012 60:49 63:00 123:49 21

Weekday

Driving distance

Total time

Time window

Starting time

Arrival time

Number visits

Sett1_gg1 217,750 08:28 09:00-18:00 09:00 17:28 3Sett1_gg2 161,587 08:47 09:00-18:00 09:00 17:47 4Sett1_gg3 292,989 09:22 09:00-18:00 09:00 18:22 2Sett1_gg4 253,707 08:55 09:00-18:00 09:00 17:55 3Sett1_gg5 251,360 09:09 09:00-18:00 09:00 18:09 3Sett1_gg6 165,646 07:33 09:00-18:00 09:00 16:33 3Sett1_gg7 222,363 08:25 09:00-18:00 09:00 17:25 3

Total 1565,402 60:42 63:00 123:42 21

same number of visits done during the week but shortest distance, less working hours, less overtime if we don’t constraint the total number of visits per day.

One working week with maximum number of visits

Same working week without maximum number of visits

Salesman work support

This object is loaded with the agenda and, using a GPS navigation system, drives to each customer.

The same tool is used to insert information and photos for each visit, to update the company in real time regarding the activities done and to update the salesman with customer data, ...

The salesman were given a PDA to support their daily activities.

Tracking salesman

• verify delays and early arrivals and reschedule the agenda• reschedule the agenda in case of customer requests• see routes and stops on the map• …

Peroni tested the real time tracking in order to:• compare the activities done with the planned ones (PATHfinder agenda) and verify the differences

Geomarketing analysisPrimary goal was the optimization of sale networks using the described tools.

The potential of geomarketing is not exhausted in the optimization of logistics aspects.

Being Peroni data ready to be analyzed they asked us to do a further effort to better understand the advantages of geomarketing applied to the company.

Geomarketing is also very useful for thematic analysis on business data to highlight aspects of the market that are not easily detectable with ordinary tools (data mining, data warehouse, …).

They concentrated on the market share.

Geomarketing analysis

To highlight the market share the thematic map represents the percentage of active over potential customers.

To determine the potential customers we have uploaded a database of potential customers with bars, cafes, restaurants, hotels, fast food, pubs, nightclubs and piano bar.

Geomarketing analysis

  ZonaNumeroElementi Volume

Numero Famiglie

Numero Componenti

_»_ 1A 6.814 433.017 2.068 5.025

_»_ 2B 7.588 493.114 2.341 5.462

_»_ 3C 7.629 605.514 2.385 5.034

_»_ 4D 7.589 504.641 2.488 6.313

_»_ 5E 8.005 450.567 2.268 5.765

_»_ 6F 7.531 542.134 2.266 5.921

ConclusionsThis project gave to Peroni a real feeling of how the Location Intelligence can affect their work.

The main result of the test has been the approximately 17% of costs reduction in the salesman logistic mostly obtained with reduction of driving time and increasing of number visits per day.

The case study once more demonstrated the importance of WEBGIS tools which are a great opportunity for private and public companies aiming to improve their services to reduce the gap over competitors.

The whole project has been carried out in one month by two technicians; the important fact that reduced so much the times is that all the applications used were WebGIS easy to use and allowing a easy sharing of data, maps, simulation and analysis.This has not only allowed a continuous updating of Peroni on the project updates but also a constant dialogue with quick solution of the issues that were arising during the various stages of work by comparison and not by unilateral decisions.

Thanks

for any further information you are welcome

salvatore.amaduzzi@uniud.it

or here in the next days