Route4Me Address Merging Macro Tool
Table of Contents
The Route4Me Address Merging Macro Tool adds relevant Address Book information to spreadsheet files containing your customers’ shipping and delivery addresses, by appending latitude and longitude GPS coordinates (for addresses that are not geocoded) and visitation counters and timestamps (for the addresses that have been included in previously planned routes).
The Address Merging Macro Tool
The Address Book supports uploads of spreadsheet files with an unlimited number of custom columns. If the information in the custom columns is dynamic and must be updated on a regular basis, the Address Merging Macro Tool simplifies the information update process.
Examples:
-
You visit the same delivery addresses each week, but the orders and order value are different.
-
You need to add updated invoice numbers to customers each day.
-
You have a fixed customer base, but only certain customers get routed on certain days.
Instead of editing each contact manually, you can update your contacts in bulk in 6 easy steps:
-
Import your initial shipping address list to the address book;
-
Export your existing delivery address book (Previous file);
-
Prepare the file with the updated information such as geocoded GPS coordinates (Current file);
-
Merge the previous and current files;
-
Delete the existing Address Book;
-
Upload the merged file containing geocoded addresses to the Address Book.
Import the Initial Address List to the Address Book
Prepare a spreadsheet file with the delivery addresses you want to include in your initial upload, then upload the file to the Address Book.
Your initial import needs to include an ID column containing a unique identification code assigned to each address. This identification code will be used in the merging process.
Export the Existing Address Book
To download your existing address book containing all of your customers’ geocoded and not geocoded addresses (Previous file), select all your contacts either by checking them in the Address Book List or by drawing a shape around them on the Address Book Map.
Next, click File → Export Selected.
Prepare the File with the Updated Information
Prepare the Current file with the updated contact information and GPS coordinates (the Merging Macro Tool only supports .CSV files). The Current file must include the address ID column with the unique identification code for each contact. Overlapping contacts in the Previous and Current must have corresponding unique identification codes.
Merge the Previous and Current Files
Click Choose File in the Previous Day section and select the Previous file (address book export) from your computer.
Next, click Choose File in the Current Day section and select the Current file.
Click the Merge Now button to merge the two selected files.
The merged file will be downloaded to your computer. It will include all of the information from your Current file, plus the following additional columns:
-
Lat – address latitude;
-
Lng – address longitude;
-
Curbside_lat – address curbside latitude;
-
Curbside_lng – address curbside longitude;
-
Last_visited_timestamp – the date and time when the address was last marked as visited in a route (MM/DD/YYYY HH:MM);
-
Last_routed_timestamp – the date and time when the latest route to include the address was planned (MM/DD/YYYY HH:MM);
-
In_route_count – the total number of routes the address has been included in.
Note:
-
If an address is not present in the address book export / Previous file, then all above-mentioned fields will be blank;
-
If an address is present in the address book, but it has never been routed, the last_visited_timestamp and last_routed_timestamp columns will be blank.
Delete the Existing Address Book
If you don’t want to have any duplicate entries, you must delete your entire existing address book by selecting all contacts and delivery addresses either in the Address Book List or the Address Book Map and then removing them.
Upload the Merged File to Address Book
Open the Address Book List or the Address Book Map, select File → Upload Addresses, and finalize the upload process.