Customer, Territory Mapping and Market Area Routing to Support Effective Business Decision-Making

By Robert van Wyngaarden

Superior Propane Inc.(SPI) acquired ICG from Petro Canada in 1998.The goals of the company after merger included:
  • 1.Achieve synergies to meet distributable cash targets for Income Fund Unit Holders
  • 2.Improve customer service utilizing best practises of both companies
  • 3.Work towards operational excellence...a strategic style (standard process, measure results, drive out inefficiencies, competitive price, etc.)
  • 4.Retain and grow customer base
  • 5.People development (i.e.entry level management positions...the Operations Coordinator)
In 2001, SPI and ICG were given the regulatory approval to complete their merger creating Canada's largest propane seller and distributor.To assist in the integration process an Integration Team was formed with members from both organizations to manage merging the two separate corporate entities.

GIS was recognized early on as being a technology and a tool that could help to understand the geographic nature of their combined business enterprise which encompasses 11 operating regions across all 10 provinces and 2 of the 3 territories of Canada.Currently there are 67 market areas across Canada contained in these operating regions.With a short mandated time-frame and significant demands for GIS output, the entire GIS data integration, mapping and analysis were outsourced to Golder Associates to provide strategic product to the Integration Team.

There were three stages of involvement for GIS which produced a diverse set of mapping products:

  • 1.Product and customer count mapping including market locations
  • 2.Creation of a national delivery grid and market area mapping
  • 3.Volume and drop count mapping
The work involved in each stage is described below.

1.Product and Customer Count Mapping Including Dealer Locations

This was the initial stage of involvement of GIS in the business analysis process that was undertaken by the integration team.The first step involved the development of a geocoded customer database from the separate customer databases of SPI and ICG.The many individual customer locations were geocoded through the six digit postal codes which brings the resulting geocode to the level of the block face in urban areas.The existing market locations for both companies were geocoded at the same time and also using postal codes.

To present the product volume and customer count summary maps, a polygonal framework was needed to summarize thousands of client locations.The purpose for these maps was to identify new market boundaries for the combined business entities.Adding to the complexity was the fact that SPI's business was unlike many types of retail business, in that, their customers were not just concentrated in major urban centres but almost equally distributed in urban core and rural areas throughout the country.Initially, a variety of census and postal-based geographic boundary files were used.Customer locations and attribute data were summarized to these boundaries but the spatial portrayal of these data in both cases were biased, with small-sized polygons in urban areas and large-sized polygons in rural areas.This tended to concentrate detail in core urban centres and or have very large units in rural areas.In either case the result was that the geographic data summaries weren't characteristic of the natural geographic trends or distributions inherent in the data itself.

As a result of these limiting factors, it was decided to develop a regular grid network that would help to overcome these presentation biases. Regular grids were created at either 10 by 10 kilometre or 30 by 30 kilometre grid sizes.After an evaluation of the results, the grid values the provinces were then used to generate customer count and total product volume maps. These grids were created for all of the land-base of the provinces and two territories of Canada in which SPI operates.

Early on in the map creation and review process, it was suggested that creating an extranet site would serve to facilitate the access and preview of plot files prior to the final poster map production.This web site which was password protected, allowed for Superior's staff across the country to access the PDF map library in a single location rather than have an ad-hoc and unstructured e-mail distribution mechanism.The extranet site has a total of over 70 maps which also serves as an archive to check on the date and version of the most currently produced maps from Golder.

2.Creation of a National Delivery Grid and Market Area Mapping

SPI has an enterprise-wide software package (i.e., based on J.D.Edwards with some in-house enhancements) that is used by the dispatchers to help create delivery schedules for all of the truck drivers within their market areas.This tool allows for a text based sequencing and scheduling which works extremely effectively, but could benefit from the integration with GIS to help in the delivery effort.In this case GIS is used to establish zones which lead to the creation of delivery territories.

After some consideration, it was decided that a grid-based routing approach would be adopted within the company and is a popular approach that many industries have taken in the geographical routing of trucks. Rather than adopting a detailed turn-by-turn routing and scheduling system, geography is handled in a grid-cell context in which customers are grouped into grids and the sequence of driver-drops are handled at the discretion of the drivers or dispatchers.From a cost and implementation speed perspective, grid-based routing is a fraction of the software and data expenses as compared to a full GIS-based routing software solution as well as being quick to implement.

The grid is based on a national standard size of 10 by 10 kilometres (Figure 2) and required an extensive thought process in regards to the assignment of unique provincial grid values.Once the grids for each province had been generated, a centroid coordinate file was generated out of the GIS which was then imported into the customer tracking system whereby each customer had a grid code attached.
The mapping goal of this grid effort was to have wall-maps prepared for each of the market areas that the dispatchers could use in their control centres.Based on discussions with the dispatchers and other local staff, the map products had a stringent requirement to be as cartographically accurate as possible from the perspective of being as effective as a common road map to help dispatchers locate themselves.With the added challenge of being as effective in rural areas as well as urban areas, the maps had to be supplemented with detailed topographic themes such as parks, hydrography, and more importantly, extremely detailed populated place names.For this later theme, a variety of data sources were evaluated and the choice was made to use the Natural Resources Canada names file since it represented the best list of all incorporated and unincorporated places.In some of the rural areas of Canada, there can be an extensive amount of very small communities, and their locations are very important aids in establishing the proper location for deliveries.

3.Volume and Drop Count Mapping

The third stage in the application of GIS which is currently being undertaken is the mapping of total product volume and number of drops, this time by the grids that were developed in stage 2.To facilitate this process a summary report was generated from the customer tracking software to provide the required total product volume and number of drops by grid. These data were then imported back into GIS where maps were generated for the market areas of interest.The resulting maps were basically "colorations" and labelling of the previously generated grids.The purpose of these maps is to provide management with a view of their current business and provides a context to realize optimization efficiencies, as well as an aid to dispatchers in helping the dynamic routing process.Conclusions

The GIS effort has proved to be an integral part in supporting the efforts of SPI's Integration Team and now in the Operational Excellence group's efforts.At any given time, SPI can look at a representation of their current customer base and volume of product geographically, which provides a uniquely visual perspective and supports decision making.

The outsourcing of the mapping and GIS work to Golder has represented a significant competitive advantage to Superior in its own business integration and efficiency drives.Faced with significant time and resource pressures, Superior engaged Golder, rather than acquiring new GIS software and data sets themselves and face a daunting training and skills enhancement challenge. SPI continues the outsourcing approach to help and meet their operational and business decision-support needs.After sorting-out some initial logistical irregularities between the two organizations, roles and responsibilities were clearly defined that have served to make the later stages of the project such a success.

SPI has indicated that one of the future areas for development in GIS is in regards to implementing turn-by-turn routing and scheduling software, however, this will come after implementing remote tank sensors and on-board truck computers.There are clear benefits from having a GIS-based system such as this working over and above the current grid-based approach, however, the cost-benefit will have to be carefully weighed before embarking on this effort since the operating area in question is literally almost all of the land-mass of the world's second largest country.

Some of the future plans that SPI has in regards to technology that might be implemented in their business includes the use of tank sensors to help improve SPI's forecasting ability, as well as having in-truck computers that can improve the data capture process.Once these technologies have been implemented then SPI will be considering GIS-based routing and scheduling tools.

Quote from Greg McGinley (Team Leader, Operational Excellence)
"Combining the benefits of the GIS grid system with dynamic routing processes, SPI is expecting to reduce same customer drops by 10%.This efficiency has a significant impact on distribution expenditures, and will improve our ability to service customers."

Authors:
Robert van Wyngaarden
Rowena Punzalan
Golder Associates Ltd.
GeoGraphic Information Services (GGIS)
940 - 6th Avenue S.W.
Calgary, Alberta
T2P 3T1

And

Greg McGinley
Robert Miller
Superior Propane Inc.
Operational Excellence
1111 - 49 Avenue N.E.
Calgary, Alberta
T2E 8V2


Published Thursday, July 3rd, 2003

Written by Robert van Wyngaarden



If you liked this article subscribe to our newsletter...stay informed on the latest geospatial technology

© 2016 Directions Media. All Rights Reserved.