Have you ever wondered how we can automatically set the shipping cost for your quote or order in Salesforce? Well, we know UPS does provide their own APIs for integration, but lets not get down to take path just yet because for first, we will need skilled developer to do integration and stuff, and secondly, we are talking about generating estimated shipping cost for the quote or order so in that case, shipping cost doesn't have to be very accurate as long as it is aligned with UPS calculated cost.

Sounds cool? Great! Let's get into this!

1. Define sender address

We will need to locate the warehouse as it will be used as the sender address and the shipping address on quote will be used as receipient address.

To make it simple, I am creating new object called "Warehouse" with only Name, Street__c, City__c, State__c, Postal_Code__c, and Country__c fields.

NOTE: you don't really need to create warehouse if you don't have multiple sender address location. The reason why I create warehouse object to store the address field is because we have multiple warehouses across entire United States. If that is the case, you will be better off using Formulas or Custom Metadata Types to store the location address.  

2. Get the distance between sender and receipient address location

Next, we would need to get the distance between sender and receipient address location. I am using Google Map Distance Matrix API (find out more here: https://developers.google.com/maps/documentation/distance-matrix/start) to get the distance in miles or kilometers back to the quote.

You can define the method to call for Google Map Distance Matrix API somewhere in your Apex Trigger or Apex Class.

Since you are creating HTTP request asynchronously, you will need to include @future(callout=true) above the method name.

@future(callout=true)
public static void calloutDistanceMatrixAPI(Id quoteId){
    Quote q = [select 
               Id,
               Distance__c,
               ShippingStreet,
               ShippingCity,
               ShippingState,
               ShippingPostalCode,
               ShippingCountry
               from Quote where Id = :quoteId];
   
    String receipientAddress = 
        (
            q.ShippingStreet + '+' + 
            q.ShippingCity + '+' + 
            q.ShippingState + '+' + 
            q.ShippingPostalCode + '+' + 
            q.ShippingCountry
        ).replaceAll('(\\s|\\r|\\n)+', '+');   

    receipientAddress = EncodingUtil.urlEncode(shippingAddress, 'UTF-8');
    
    String senderAddress = 'DEFINE_YOUR_OWN';
    String apiKey = 'GET_YOUR_OWN_GOOGLEAPI_KEY';
    
    String requestUrl =
         'https://maps.googleapis.com/maps/api/distancematrix/json?units=imperial' +
        '&origins=' + senderAddress + 
        '&destinations=' + receipientAddress +
        '&key=' + apiKey;   

    Http http = new Http();
    HttpRequest request = new HttpRequest();
    request.setEndpoint(requestUrl);
    request.setMethod('GET');
    HttpResponse response = http.send(request);
    
    if(response.getStatusCode() == 200){
        System.debug('Request API Success!');   
    } else {
        System.debug(response.getStatusCode() + 'Request API Failed!');   
    }
}

NOTE: you can store your API key in Custom Settings or Custom Metadata Types, whichever way you prefer.

Essentially, the code above is just to send a HTTP request to Google Map. If the response is succeeded, the response body will be something like this:

{
   "destination_addresses" : [ "New York, NY, USA" ],
   "origin_addresses" : [ "Washington, DC, USA" ],
   "rows" : [
      {
         "elements" : [
            {
               "distance" : {
                  "text" : "225 mi",
                  "value" : 361715
               },
               "duration" : {
                  "text" : "3 hours 49 mins",
                  "value" : 13725
               },
               "status" : "OK"
            }
         ]
      }
   ],
   "status" : "OK"
}

We need to extract the value '361715' out from distance properties. Luckily for us, there is a ready-made online tool that can automatically transform JSON structured text into Apex Class called JSON2Apex. You can visit this link https://json2apex.herokuapp.com/ to try it out. When you are done with that, you will be getting these two files accordingly.


// JSON2Apex.apxc
public class JSON2Apex {
    public List<String> destination_addresses;
    public List<String> origin_addresses;
    public List<Rows> rows;
    public String status;
    
    public class Elements {
        public Distance distance;
        public Distance duration;
        public String status;
    }
    
    public class Distance {
        public String text;
        public Decimal value;
    }
    
    public class Rows {
        public List<Elements> elements;
    }
    
    
    public static JSON2Apex parse(String json) {
        return (JSON2Apex) System.JSON.deserialize(json, JSON2Apex.class);
    }
}


// JSON2ApexTest.apxc
@IsTest
public class JSON2ApexTest {
	
	static testMethod void testParse() {
		String json = '{'+
		'   \"destination_addresses\" : [ \"New York, NY, USA\" ],'+
		'   \"origin_addresses\" : [ \"Washington, DC, USA\" ],'+
		'   \"rows\" : ['+
		'      {'+
		'         \"elements\" : ['+
		'            {'+
		'               \"distance\" : {'+
		'                  \"text\" : \"225 mi\",'+
		'                  \"value\" : 361715'+
		'               },'+
		'               \"duration\" : {'+
		'                  \"text\" : \"3 hours 49 mins\",'+
		'                  \"value\" : 13725'+
		'               },'+
		'               \"status\" : \"OK\"'+
		'            }'+
		'         ]'+
		'      }'+
		'   ],'+
		'   \"status\" : \"OK\"'+
		'}';
		JSON2Apex obj = JSON2Apex.parse(json);
		System.assert(obj != null);
	}
}

Then, you can simply parse in the response body into JSON2Apex class and extract the distance value in OOP way.

    ...
    JSON2Apex googleDistanceApex = JSON2Apex.parse( response.getBody());
    Decimal distanceValue = googleDistanceApex.rows[0].elements[0].distance.value;
    Decimal distanceValueInMiles = distanceValue / 1609.344; // meter to mile conversion    
    
    q.Distance__c = distanceValueInMiles;
    update q;

I hope this is not too overwhelming yet. Good thing is, we have the distance from receipient to sender address now. We are one step closer to getting shipping cost calculation formula part.

3. Estimate the UPS shipping cost

We have the distance between the receipient and sender address from previous part. Though the actual shipping calculation might not be exactly based on the distance, but like I said earlier, we would just want to get an estimated shipping cost so that we won't lose too much money on the shipping fee.

We are using Zenkraft for UPS integration. Even though they do have Calculate Rate Apex method to automatically calculate shipping cost, it is tied with their featured license and we really don't have the intention to get more featured licenses just to get the shipping rate for Sales users. That's why we would want to formulate the shipping cost calculation on our own in the first place.

Anyway, here's the plan. We are creating a couple of data sets with different parameters (distance and package weight for now) and plot them out in an Excel sheet. Based on the data sets, we can formulate an equation for calculating estimated shipping rate that is similar to UPS calculated one based on package weight and distance.

Shipping rate generated from Zenkraft App in Salesforce.

So first off, I am using receipient address that is in within certain mile range (100, 200, 300 miles and so on) from sender address with fixed product weights of 10, 20 and 30 lbs (can be broken into smaller group for higher accuracy). You can use Google Map to calculate the distance. Below is a sample list of data sets:

Package Weight (lbs)Distance (miles)UPS Shipping Cost (USD)
10100$20.00
20100$25.00
30100$27.00
10200$22.00
20200$28.00
30200$30.00
10300$23.00
20300$30.00
30300$35.00
.........
10500$25.00
20500$32.00
30500$37.00
.........
101000$28.00
201000$34.00
301000$40.00
.........
102000$35.00
202000$40.00
302000$45.00

You probably can sort of seeing the regular pattern by now: the heavier the product, the higher the cost; the further the distance, the higher the cost. You might also notice that the cost increment is not consistent too, and that's why we will need to plot it and formulate an equation to this.

Distance vs Cost for product weight 10 lbs.
Distance vs Cost for product weight 20 lbs.
Distance vs Cost for product weight 30 lbs.

In Excel, you can select the distance and cost column and insert a scatter chart. Then, you can right click on the dot and select Add Trendline and also remember to check "Display Equation on chart" in Trendline options.

So, how do I use the equation, you ask? Well, it is pretty simple and straightforward. If given a distance 1000 miles and the product weighs 10 lbs, then I will be takingx = 1000 and applying x into the first formula y = 0.0069x + 20.599, which will give us the shipping cost y of  around $21.28.

NOTE: this is intended for calculating shipping cost in US based location only, though I found shipping to other country generally costs 3x more than domestic shipments.

4. Calculating the shipping cost

Well, we are almost there now. Before we get to that, we need to make sure there is a weight field on product is defined so that we will know how heavy it is when we ship a particular package.

NOTE: our shipping cost calculation is not so accurate after all, not because of our shipping cost calculation formula but because of the nature of our product item. For example, we have product package that consists of few different hardware components and they are all shipped in multiple different weight packages.  Also, some items can be packed into same package if the package still has some more spaces left. In that case, we are only getting a rough estimated shipping cost.

Since I am using Quote in Salesforce, the product package is equivalent to Quote Line Item. I have created a flow to loop through Quote Line Items. For each item, I then calculate the package shipping cost based on the formula equation that we got in previous section, multiply it by the quantity count, and lastly add the individual calculated shipping cost into total shipping cost. Once we're done with the loop, we will update the total shipping cost into Quote shipping fee field. That will be all of it! Don't worry if you are not using UPS but other shipment providers such as Fedex or DHL, the logic behind is all the same!

I hope you enjoy reading it and find it useful! Let me know what you think! Thanks!

Post was published on , last updated on .

Like the content? Support the author by paypal.me!