trakki
Get your Excel customer list onto a map
By Manuel Ober · · 5 min read
The list everybody has
Almost every field sales rep has one: an Excel file with all the customers. Name, address, phone number, somewhere a column with the last call, somewhere one with notes. Grown over years, inherited from a predecessor, added to in hotel rooms.
That list is valuable. But it has a blind spot: it does not know where the customers are. Row 47 and row 48 sit one under the other whether they are 500 metres or 80 kilometres apart. You cannot see it.
The moment the list sits on a map, the planning changes. This guide shows how to get it there, and where it usually goes wrong.
Step 1: Put the addresses in order
Maps need addresses a machine can read. That is the part where most lists get stuck. Three rules:
One entry per column. Street and house number in one column, postcode in one, town in one. A single column reading “12 Mill Road, 3300 Newport” usually works. One reading “Newport, Mill Rd. 12 (rear entrance)” does not.
No extras in the address. “Second floor”, “ask for Dr Brown”, “entrance from the car park” belong in a notes column. In the address they confuse the conversion.
Always include the postcode. Most countries have more than one town of the same name. With a postcode the address is unambiguous; without one the customer ends up in the wrong region.
For a list of 300 customers this clean-up takes about an hour. It is worth it, because you only do it once.
Step 2: Duplicates and dead entries
They turn up while you clean: the same customer twice, once as “Eagle Pharmacy” and once as “Pharmacy at the Eagle”. A customer that closed three years ago. A contact who retired long ago.
Now is the moment to sort that out. Not because the map demands it, but because a list of 300 customers, 40 of which no longer exist, distorts every plan you make. Work out the number of customers due from that list and you are counting ghosts.
Step 3: Turn addresses into coordinates
Turning addresses into points on a map is called geocoding. You do not have to know the word, but you should know its three quirks:
- It does not work for every address. New housing, addresses with an extra such as a unit number, or firms that are only reachable through a post box are not always found. With 300 addresses, reckon on ten to fifteen you have to place by hand.
- Roughly is dangerous. Some tools drop a point in the middle of the town when they cannot find the street. The customer then sits three kilometres away from his own building. You only notice when you are standing in front of it.
- Some countries run an official address register. Austria's survey authority, for instance, knows every house number in the country. Tools that use it place Austrian addresses more accurately than worldwide map services do.
The result of the conversion is one point per customer. Only with those points can a route planner work out drive times.
Step 4: What you see on the map for the first time
Anyone who sees their list on a map for the first time usually gets three surprises.
The territory is not round. It has a dense core and long outrunners. The outrunners cost the most kilometres and bring in the fewest calls.
There are clusters of customers that were never visited together. Three customers in the same town, sitting 200 rows apart in the list because they came along in different years.
Some customers are closer to the neighbouring territory than to your own. That is a question for how territories are divided, not for route planning, but the map makes it visible for the first time.
Step 5: Colour the map by due date
A map with 300 identical dots does not help much. It gets interesting when the dots tell you who is up: red for overdue, amber for due within the next two weeks, green for done.
For that, every customer needs the last call and the intended gap. Both are usually somewhere in the Excel list, often in a column of free text. That too is worth cleaning up once, and then the map works out the due date on its own.
How a coloured map turns into a week is in the guide Route planning in field sales.
Where it usually goes wrong
- The list gets copied instead of cleaned. Then the mistakes sit in two files.
- A tool wants the data in its cloud. Before you upload the customer list of a pharmaceutical company, you should know where it lands and who sees it. A customer list is company property, often covered by a confidentiality agreement.
- The map does not replace the list. The two belong together: the list carries the history, the map carries the location.
How trakki does it
trakki reads Excel lists directly, converts the addresses and marks the ones it could not find, so you can place them yourself. For Austrian addresses it uses the official address register. The map colours every customer by due date, and everything stays on your device, with no account and no cloud of ours.
Trying it costs nothing, and you can take your list away again at any time. More on the home page.
Frequently asked questions
Do I have to rebuild my Excel list?
Only the address columns. Everything else can stay. What matters is that street, postcode and town are separate, or at least unambiguous, and that there are no extras inside the address.
What about customers without a proper address?
Farms, farm shops or building sites sometimes have no street address. For those you can place a point on the map by hand. That takes a minute per customer and only has to be done once.
Can my manager see the map?
Only if you show it to him. A map that sits on your device is yours. What you hand out, a call report as an Excel file for example, is your decision.