Data preparation for automatic route planning: What to think of?

Here we go – the route planning project for your field sales force is about to start. In this article you will find information on how to prepare your customer data so that your project is a complete success right from the start. Take a look at these additional valuable tips on the successful introduction of portatour®.

The routes  generated by portatour® can only be as good as the data you import. If you do a good job here, you will benefit from the word go and your colleagues will be happy to work with portatour®. This means that the right customers are visited, no customer is ever forgotten, more customer visits per day are achieved and less time is spent in the car.

Where does the data come from?

Customer data is traditionally managed in a CRM or ERP system. The data source for portatour® is usually a data export from such a system in CSV or Excel format. In most companies, there is an IT colleague who is responsible for the system. It is always a good idea to involve this colleague in the project from the very beginning.

What does portatour® mean by “customers”?

For portatour®, every address where your field sales force should go is a “customer”. These include existing customers, as well as prospects and leads, in addition to former customers to be won back. In sum, import all addresses that you want to visit.

Mandatory fields

Your customer master data must consist of at least the following fields:

• Customer name
  
• Customer address: One field each for ZIP code, city and street with building number. Optionally, include the country if your customers are spread across several countries.
• Customer number or other unique identifier (e.g. record ID in your CRM/ERP system). This ID is defined as a key field during the import and is used to identify customers during subsequent updates.

Recommended fields for determining route planning

You can use the following fields to individually determine the planning of visits for each customer:

• Call interval: How often should the customer be visited? This value is specified either in days between visits or as the number of visits per year.
• Last call date: When was the last time the field rep visited this customer?
• Call duration: How long does a visit to this customer generally take in minutes?

Note the following for the call interval:

• If individual customers are not to be visited regularly, enter a “0” as the call interval value.
• To visit customers at different intervals depending on a classification (for example, A customers every 14 days, B customers every 30 days, C customers every 90 days), use calculation fields in your data source.
• For example, if your data is stored in Excel, insert a formula that determines the call interval based on the category.
• CRM and ERP systems also offer calculation fields or workflows. It is recommended that you implement these formulas directly at the source to reduce manual postprocessing.

For the last call date, there are three practical options for importing data:

1. The last call date is imported 1:1 in portatour® and is used to calculate routes accordingly. This has the advantage that customers who have recently been visited are not immediately suggested again. However, if the last call date was not well maintained in your system and there is outdated data, portatour® will first suggest these very overdue customers. This can also be a disadvantage.
2. The last call date is not imported. This has the advantage that portatour® can optimize geographically from day one and is not restricted by overdue customers in its planning. However, it is possible that customers who have recently been visited may also be scheduled. To avoid this, the field sales representatives could, for example, enter the visits of the previous week in portatour® manually.
3. The last call date is imported as of a specific date. This approach combines the advantages of the two approaches above. Only import the last call date for customers whose visit was not too long ago, for example, within the last 30 days. You delete obsolete data from the import file. If you are unsure which option is right for you, just contact us and we will be happy to help.

Note regarding customer urgency: portatour® calculates the urgency of a customer based on the last call date and the call interval. Due and overdue customers are automatically scheduled by portatour® for a visit and visualized respectively in yellow and red on the customer map.

Optional fields for managing route planning

These fields are optional and are not imported in most cases:

• Customers’ business hours (visiting hours): Two time slots are possible per weekday, e.g. Monday 08:00-12:00 and 14:00-17:00. In this case, your import file needs four columns for Monday, which contain the values 08:00, 12:00, 14:00 and 17:00.
• Geoposition: The GPS coordinates (longitude + latitude) of the customer on the map. If these are not specified, portatour® automatically determines the geoposition during customer import using the address as part of the automatic “geocoding” process.

Custom fields

Optionally, up to 100 custom fields can be freely created and imported in portatour®. Use these fields to provide sales reps with all the information they need to plan, contact, and prepare for a visit.

For example, import:

• Contact details such as telephone number, email address, contact person
• Customer classifications of such as A,B,C or industry
• Sales volume
• Date of last order

Custom fields must be created in portatour® before the first import to be available during the import process. This is done in Options -> Custom Fields -> Customers.

Tip: Click the question mark at the top left to access the context-sensitive online help, which is chock-full of useful information.

We recommend that when you export customer data from your CRM/ERP system, all potentially relevant information is outputted to the file. Experience has shown that removing columns that are not required is less time-consuming than obtaining and adding additional information later on.

What format should the data be in?

Good news: a fixed structure for the import file is not necessary. Simply make the customer data available as an Excel, CSV or ODS file. The assignment of the fields of your Excel file to the fields in portatour® is done in portatour® and needs only to be done once.

It is important that each customer is listed as a separate data record in their own row and that each information field (e.g. customer number) is stored as a separate column in your import file.

Usually, the data is stored as tables with a header row, whereby the header contains the field name (e.g. “Name” or “ZIP code”) to make it easier to understand. Pivot tables are not supported.

Avoid these 6 pitfalls

1. Check your file for duplicates in the customer numbers and remove them.
2. Note that fields can contain a leading zero (for example, in a ZIP code or telephone number). This leading zero can disappear in Excel if the cell is not formatted as text. As such, format the relevant fields as text.
3. To ensure exact geocoding of addresses, we recommend that the “Street” field contains only the street with the building number. Additional information such as “entrance via the courtyard” or the like should be omitted. Additional address details can be stored as notes in portatour® in the respective customer entry.
4. Ensure that the data is consistent. Do you want to save the ABC categorization as a selection field in portatour®? Check whether only the values “A”, “B” and “C” are really contained in the respective column. Use filters in Excel to check this.
5. Make sure that you have only one header row. Check the names of the individual fields.
6. Remove data fragments below the table (such as totals rows). No further data should follow after the customers.

One file with all customers or one file per rep

For the customer import in portatour® you can either use a single customer file for all sales representatives, or individual customer files – one per sales representative.

• A complete file with customers of all reps:
  With the company-wide import, the customers of all sales representatives in a portatour® company account can be uploaded via a single import operation. For this you need a file with all customers of all sales reps. This file requires a unique assignment for each customer to a user (column with employee number or e-mail address of the rep).
• One customer file per rep:
  Customer data can also be imported separately for each sales representative at the user level. To do this, prepare one individual import file for each sales representative, which contains only their respective data. To save time during the import or to automate the operation, the structure of the tables must be uniform.

Tip: In advance, send the import files containing the customer lists to be uploaded to portatour®. The reps should be informed regarding which data will be imported for them and should be able to contact you in case of questions or anomalies.

How much customer data can I import?

A maximum of 3,000 data records (customers) can be imported per field sales representative, whereby a maximum of 1,000 customers may have an assigned call interval.

If you need to visit more than 1,000 addresses, initially set a call interval only for those customers whom you want to visit first and have the highest priority. You can prioritize based on various criteria such as sales volume or potential or number of employees. Do not filter by geography – customers should be available for visits in every region of a territory.

Several addresses or contact persons per customer?

If you have several addresses per customer (delivery address, billing address, etc.), decide which of these addresses the field sales force should visit and import only that.

Are several contact persons visited separately, perhaps also at different intervals? Then create each contact person as a separate customer in portatour®. They can all have the same address. For example, you can create several doctors to be visited, all of whom work in the same hospital, as different customers.

If you want to know more about the relative advantages and disadvantages of each of the possibilities, please contact us.

Your reward for doing a good job here?

Invest time in data preparation now and you will benefit from good data quality in the long run. High data quality helps to improve your route optimization project right from the outset. Your colleagues in the field will be happy and motivated to work with portatour® and will not be annoyed by data errors. You too benefit from this: the right customers are visited at the right time, no customer is forgotten, fewer miles are driven, more customer visits per day are achieved and thus more turnover is generated.

How does the portatour® team help me with the import?

With a setup workshop! During this online workshop, portatour® is configured together with you and populated with customer data. We check the quality of the data, advise you on the selection of fields and upload the data together. You receive valuable tips and tricks, avoid configuration pitfalls and ensure a fast, productive start for your field sales force.

Learn more about the setup workshop here.

The data is imported. What happens next? Update, automate and save time!

To simplify future data synchronization, make sure that the first time the data is provided, it can be automatically exported from your CRM or ERP system at the push of a button. Reduce manual processing to a minimum. Above all, the field names in the header row should remain unchanged.

If you want to synchronize data from your CRM or ERP system regularly and automatically with portatour®, use the portatour® application programming interface (API). In this way, you no longer have to worry whether the data is up-to-date. Learn more about the API here.

We offer an API workshop where we show you how to set up the interface. Experience has shown that with the help of this workshop, an automatic data import and export, at least prototypically, is already possible after 2 hours.

Note: In order to update customer data via the API, a unique customer identification is required. Here you see how thorough preparatory work is already worthwhile.

The customer data is ready. What do I do with users, appointments and reports?

In addition to importing customer data, it is optionally possible to import and export the following data in portatour®:

• Users: The import of user data saves you having to manually create users in the user administration. This is useful if more than 10 users will be working with portatour®. The following information is required for the import: first name, last name, email address, home address (for field staff). Optionally, you can also import the employee number, the user group name and the authorization role (“User”, “Supervisor”, “Administrator”).
• Appointments: If your colleagues have already arranged many appointments in advance and they are available in electronic form, they can also be imported.
• Reports: If historical call reports are available digitally, these can also be imported.

The import and export for all data types is analogous to that of the customer data. Both the file format, the import and export process and the quality attributes are similar to the customer data import. Of course, we would be happy to support you with an exclusive and individual setup workshop.