This article is relevant if you are using NetSuite and you need to calculate the distance between two address points and/or you need to look up information between previous transportation jobs.
Background
We have a number of clients that use NetSuite to offer logistics services to their customers. Their main function is to broker transportation, coordinating with independent trucking carriers to pick up and deliver goods. These brokers are renowned for their dependable services across frequent routes within specific areas.
These brokers gradually gain deep market knowledge, particularly regarding customer concerns about the cost of transporting goods. While they can directly inquire with truckers for new job quotes, referencing similar past jobs can provide faster and more accurate pricing information.
The challenge lies in efficiently identifying past transportation jobs that closely resemble new ones needing pricing. How can brokers effectively find these comparable past jobs to assist in quoting new transportation tasks?
NetSuite Geocoding Address and Routes Fundamentals
To identify similar transportation jobs to the one that needs to be priced, it’s crucial to start with the basics. Every street address corresponds to a latitude and longitude (Long/Lat) coordinate. Knowing the Long/Lat of two addresses, we can apply trigonometry to calculate the distance between them. In my 2015 article, Solution: Route NetSuite Leads based on the Shortest Distance, I provided a Saved Search and SuiteScript solution for this concern.
Leveraging the Google Maps API, we can utilize the Routes API for distances that adhere to actual roads and viable travel routes rather than just straight-line distances.
Making Previous Transportation Jobs Become Pricing Insights
When transportation jobs are completed, we capture the distance traveled (in miles or kilometers), along with the associated costs and pricing. Recent jobs are generally more indicative of current market conditions and logistics considerations than older ones.
For each new transportation job, we start by identifying the pickup (source) and delivery (destination) addresses. We then decide on a mileage radius to search for similar past jobs near this new job’s route. This enables us to query NetSuite for relevant past jobs and provide this data to pricing planners. These planners can then rapidly estimate costs for new jobs, enhancing their ability to rapidly respond to customer inquiries with pricing quotes. Quick turnaround on these quotes gives our clients a competitive edge.
With NetSuite SuiteScript and enhancements for storing Long/Lat data within address records, we’re equipped to rapidly retrieve information about distances between addresses. Referencing the related image, we can see that Job # 196587 departs from zipcode 78620 (which is only 4 miles from the prospective job’s starting address) and delivers to zipcode 33122 (which is only 6 miles from the prospective job’s ending address). That previous job covered a total distance of 1,378 miles; and we charged the customer $500 while our cost was $200. This is shown in real time and includes the “most similar” jobs as the planner is crafting the customer quote.
Click images to see full-screen.
Demonstration: Inquiry Tool to Lookup Like-kind Address-Based Transportation Jobs
In addition to our automated quoting tool for gathering information during the creation of new jobs, we developed a separate general lookup tool to aid in transportation route market discovery. To provide a clearer understanding, we created a video demonstration (3:49), which can be viewed here.
Integrating SuiteScript 2.x with Google Maps API for Lookup Capabilities
For more technical-oriented readers, the following SuiteScript algorithm leverages the Google Maps and Route APIs. Here we demonstrate how to obtain address coordinates and subsequently calculate the distances between these points.
const getAddressCoordinates = (options) => {
let theAddress = options.address.replace(/\n/g," ").replace(/ /g,"+").replace(/#/g,""); /* or use url encode */
let apiURL = GOOGLE_LOCATION_URL + theAddress;
const apiResp = https.get({url: apiURL + "&key=" + GOOGLE_API_KEY});
const mapResp = JSON.parse(apiResp.body);
/* we only care about the first set of route results */
if (mapResp.results && mapResp.results.length > 0)
if (mapResp.results[0].geometry && mapResp.results[0].geometry.location)
return mapResp.results[0].geometry.location;
/* Sample Response ... we only want the location
"geometry" : {
"bounds" : {
"northeast" : {
"lat" : 34.383713,
"lng" : -118.504822
},
"southwest" : {
"lat" : 34.3833579,
"lng" : -118.5051138
}
},
"location" : {
"lat" : 34.3835526,
"lng" : -118.5049395
},
"location_type" : "ROOFTOP",
"viewport" : {
"northeast" : {
"lat" : 34.3848844302915,
"lng" : -118.5037708697085
},
"southwest" : {
"lat" : 34.3821864697085,
"lng" : -118.5064688302915
}
}
},
*/
}
const calculateDrivingDistanceFromCoordinates = (options) => {
let geoPayload = {
origin: {
location: {
latLng: {
latitude: options.from.lat,
longitude: options.from.lng
}
}
},
destination: {
location: {
latLng: {
latitude: options.to.lat,
longitude: options.to.lng
}
}
},
travelMode: "DRIVE",
computeAlternativeRoutes: false,
units: "IMPERIAL"
}
let headerObj = {};
headerObj['Content-Type'] = 'application/json';
headerObj['X-Goog-Api-Key'] = GOOGLE_API_KEY
headerObj['X-Goog-FieldMask'] = "routes.duration,routes.distanceMeters"
var response = https.post({
url: GOOGLE_ROUTES_URL,
body: JSON.stringify(geoPayload),
headers: headerObj
});
if (response.code != "200") {throw response.body;}
let route = JSON.parse(response.body).routes[0];
route.distanceMiles = route.distanceMeters * 0.000621371;
route.durationText = secondsToFormattedTime(route.duration);
return route;
}
Work with Highly Competent NetSuite Leadership
This article is an excellent example of the NetSuite leadership we offer our clients. As a NetSuite Systems Integrator, our focus is to help our clients ensure they get the promised value of the NetSuite vision — run your business on one platform. Our approach is to first listen carefully to business-driven concerns; next, we consider everything NetSuite gives us natively to model the challenge; and finally, only when it makes sense, we innovate on the NetSuite Platform to deliver seamless capacities.
I must offer a special thanks to Boban D., Senior Technical Analyst, for his invaluable leadership. Having worked with Boban since the late 1990s, I deeply appreciate his dedication to fostering our Practice’s growth through numerous client success stories.
If you found this article relevant, feel free to sign up for notifications to new articles as I post them. If you have a risky challenge that demands NetSuite expertise and leadership, let’s have a conversation.
Marty Zigman
Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.
More Posts - Website - Twitter - Facebook - LinkedIn - YouTube
