If you use expedited shipping services like 2nd-Day Air, you should audit shipments for on-time delivery. Expedited services cost multiple times more than standard ground because they come with delivery guarantees. In this blog post, I'll show you how to find and get refunds for shipments delivered outside of the guaranteed window.
Tyler Douglas
Welcome back to my blog series on optimizing shipping costs! For those who don’t know me, I’m Tyler, and I founded String to help eCommerce businesses save time and money on order fulfillment.
Today we’re going to focus on a topic that carriers don’t want you to know about – Refunds for shipments that break contractual agreements.
If you operate a business that uses shipping services like 2-day or next-day delivery, the services you’re using likely have contractual obligations around what time the package must arrive to the delivery address.
If the package is not delivered within the contracted time frame, you are entitled to a refund. This is crucial because expedited delivery services are multiple times as expensive as ground delivery rates. So, if you’re paying handover foot to get orders to customers faster, you want to know they will get therein time!
If you use these services, you could be losing tens of thousands of dollars a year by not auditing your shipping data and requesting refunds for late deliveries.
The challenging part about getting refunds is that it takes some effort because carriers don’t alert you that a delivery was late. It’s your job to audit deliveries and request refunds. To hopefully make your life ~5%easier, I’m going to show you how to do this!
In the following example, I’m going to use ShipStation as our demonstration interface to collect the relevant data and set up your audit process. If you use another shipping platform, know that the steps are similar but the specific buttons you’ll need to press will be different.
When exporting data from ShipStation, we’re going to want to make the data we export as succinct as possible so you can clearly communicate precise information to your carriers. With that in mind, I recommend you follow the process below to export data for every contracted service level individually rather than doing them all together in bulk.
1. Navigate to the “shipments” tab in your ShipStation dashboard.
2. In the left-hand menu dropdown, select the “Delivered”tab to show orders that have been successfully delivered to customers.
3. Set filters to only display orders shipped using a specific carrier and service level. In ShipStation, select the “Carrier” dropdown and toggle the specific carrier you want to export data for. Then, under the “Other” dropdown, select the “Service”filter and toggle the service level you want to export orders from. Finally, select the date range you want to see.
4. Now that we have relevant orders available to us, we want to create an export format that will present us with the necessary data we need to tag orders as breaching contract. To do this click on the “ExportShipments” button and you’ll see a pop-up window appear.
5. In the pop-up window, you’ll want to create anew export format by clicking the “Create New Format” button.
6. We only need a few fields to run our audit, toggle to following options by selecting them from the left hand menu and clicking the “right arrow” button to move the selected fields over to your new export format: “Date – Shipped Date”, “Delivery – Date”, “Shipment – Service”, “Shipment– Tracking Number”, and then select all options for the “Ship to – {delivery address line} field”.
7. After you’ve created an export format, give it a name and click the “export” button to have all of these shipments exported into a .csv file.
8. Repeat this process for every contracted service level you ship with.
Great, we have the data we need! But how do we audit it? You’ll need to write a function in excel to compare the shipment date with the delivery date. If you’re thinking this is going to be hard, don’t worry. I’m going to write it for you! If use something other than excel, the function you write will be different but will likely use similar syntax.
1. Open the file you just exported in excel and check to make sure all relevant fields are there.
2. The sample formula I’m going to give you is to check if a two-day shipment is late. If you’re using 3-day or overnight, change the function respectively. If the service you used has a time-of-day guarantee like, “before 10am”, I’ll show you how to do that next.
3. To compare the actual delivery date to the guaranteed delivery date use this function: =IF(B1-A1>2, "late",""). Paste this function in an empty cell/column in the row you want to compare the delivery date for. If done properly, the cell will say “late”for every order not delivered in time. Make sure to substitute the actual cells you have data in. The value on the left hand side of the “-“ operator should be the shipping date cell. The value on the right-hand side of the operator should be the delivery date cell.
4. If your guaranteed delivery has a time window i.e.,“before 10am”, use this function to compare the delivery date and substitute the time for your guaranteed window. =IF(AND(B1-A1>2,TIME(HOUR(B1),MINUTE(B1),SECOND(B1))>TIME(8,0,0)), "late",""). In this case, the function is checking to see if the delivery was made within two days and before 8am.
5. Keep in mind that if your shipment occurred over the weekend, this could impact the guaranteed delivery window. Make sure to check the service level contract and your carrier contract for more details on this.
6. Also be sure to check any exception rules for service guarantees such as exceptions for residential deliveries with 2nd day AM.
Finally, you’re going to submit a refund request for all shipments that were delivered late and include the tracking number, guaranteed delivery time, and actual delivery time. We’ll use UPS as our example for how to do this.
1. You’ll navigate to https://www.ups.com/us/en/support/file-a-claim.pagewhere you can submit your claim.
2. Login to your business account.
3. Choose “Late Delivery” for the reason of the claim.
4. Add all the relevant information and submit the claim.
This can be a long process but if your business ships many orders with expedited shipping, it’s usually worth it. Alternatively, there are service providers who offer invoice audit-as-a-service and typically get paid as a percentage of refunds paid out.
If you have any questions about this process or want help finding an audit firm who can help you do this, feel free to reach out to me at tyler@meetstring.com. Otherwise, have a great week and tune back in next week for more ways to save money on order fulfillment and shipping!