Shipping Data Is Worth Dollars

Whenever I assist a shipper with trying to reduce his or her costs, the first thing I ask is: “Where is the data?” Usually I get a stare followed by, “I’m not sure, I think accounting,” or “we use a freight bill audit and payment company and I’m sure they have it.”

You should care about the types of services you buy from a carrier, the relative size of that usage by service type, the distribution of your business by zone and the spread of the weights shipped within those zones. I can tell you that the carriers know this about your business.

Without that information, you won’t know if a carrier has given you a good deal or a bad deal because you don’t have the tools to make sense of a carrier offer. Of course, most shippers assume that the base rates of parcel carriers are the same, and that a 40% discount from one carrier is obviously better than a 35% discount from another carrier.

But what if the carriers offering the 40% discount have a base rate that is 15% higher than its competitor? See the flaw in the logic?

Now for a consultant’s secret.

Turning data into that distribution of your business by service type, by zone, and by weight takes about two seconds in Microsoft Excel. How you ask?

It’s done by using a data tool in Excel called a pivot table. The raw data is extremely easy to request from the carriers and you can usually do it online and define how large a timetable you would like.

For example, you can go to the FedEx reports screen and find a three-month history of your billings at the shipment level with the level of service purchased, the weight you were billed for and the zone the shipment was rated for. With the pivot tool you can immediately take thousands of records and have in your hands a profile of your actual business—not an educated guess.

You can do some amazing things with sorts and find those shipments with no discounts at all. You can see if the carrier is, in fact, giving you the degree of discounting you thought you should be enjoying. What good is a great discount off of your packages 25 lbs. and above if 80% of your shipments are less than 10 lbs.?

Now, the next critical piece that you most likely don’t have inhouse is a rating tool that takes this profile of your business and rates it against the carrier’s rate from last year and the carrier rate from this year (net of your discount percentage). This lets you see how that carrier’s annual increase impacted your actual book of business.

That tool is available FOR FREE (you can request it from It allows you to select a carrier and a carrier’s level of service, to select the tariff year, to insert the current fuel surcharge (or a lower one if you have negotiated a discount on that accessorial), if you ship to consumers you can insert what the carrier wants to get from you for residential charges, and you can put in your discount, and it will display the calculated net after discount charge.

You can then pull down another carrier and its equal level of service and have it compute that carrier’s net. Or if you are doing the carrier impact of its annual rate increase, you can pull down the same carrier and the different tariff year.

Then you can paste it in your book with the business profile you extracted from your shipping history, using the pivot table, and then you have the end result: To the right it has multiplied the shipments against the computed net rates to come up with real dollars on your book of business, and then farther to the right, it calculates for you the difference between the two offers or the two years.

Now you can have an educated conversation with your carrier about what you need in the form of concessions that coincides with your particular shipping spend. And you can quickly determine the value of revisions to an offer based on the negotiations with that carrier.

Gerard Hempstead, a retired vice president for DHL, is president of shipping consultancy Hempstead Consulting (

Partner Content

Hincapie Sportswear Finds Omnichannel Success in the Cloud - Netsuite
For more and more companies, a cloud-based unified data solution is the way to make this happen. Custom cycling apparel maker Hincapie Sportswear has leveraged this capability to gain greater visibility into revenue streams, turning opportunities into sales more quickly while gaining overall operating efficiency. Download this ecommerce special report from Multichannel Merchant to more.
The Gift of Wow: Preparing your store for the holiday season - Netsuite
Being prepared for the holiday rush used to mean stocking shelves and making sure your associates were ready for the long hours. But the digital revolution has changed everything, most importantly, customer expectations. Retailers with a physical store presence should be asking themselves—what am I doing to wow the customer?
3 Critical Components to Achieving the Perfect Order - NetSuite
Explore the 3 critical components to delivering the perfect order.
Streamlining Unified Commerce Complexity - NetSuite
Explore how consolidating multiple systems through a cloud-based commerce platform provides a seamless experience for both you, and your customer.