One of the most difficult aspects of any delivery operation is keeping track of the sheer volume of activity. Who, what, where, when, how and WHY THE HECK does that delivery always seem to give us issues?
With the Onfleet Export Feature, admins and dispatchers can access 40+ individual data points on every task completed. You can also export unassigned and assigned task data through this tool, but this article will focus on completed task data, which will include many more data points. With a little bit of Excel wizardry, this amount of information can paint an incredibly vivid picture of what’s actually happening out in the field.
To help you get the most out of this information, this article will cover the following areas:
- Exporting your Onfleet data and an overview of what data points are available
- Working with the rich metadata bundle within Excel
- Using this export to do basic driver reconciliation
1. Exporting your delivery data
Actually accessing your delivery data is incredibly easy and can be done right in your Onfleet dashboard. Simply select a few parameters (status, time, teams) to formulate your export and a .csv file will be created for you to dive into.
After downloading the .csv, simply open the file and let your Excel wizardry loose on the trove of data now at your finger tips!
The delivery data available within the export
This export will generate 40+ data points on each delivery that was completed through your dashboard. Now, a massive excel file can be daunting, but it’s all about finding the pieces of information that are most important to the task at hand.
This (somewhat long) list will explain some of the most useful pieces of information within the export:
Pre-delivery data
shortId: the short version of the Onfleet identification number that can be used to identify that specific task throughout our system
status: the state of the task at the time of export (the options are unassigned, assigned, completed). didSucceed attribute (described below) will be updated if task is completed, with the completion status
creatorOrganization: the organization that originally created the task (applies to Connections)
executorOrganization: the Onfleet organization that completed (executed) the actual delivery (applies to Connections)
workerName: which driver, biker, walker, (scooter-er?) completed the task
destinationAddress: the delivery address that was input upon task creation
destinationLonLat: the corresponding Longitude and Latitude for the geocoded delivery address
recipientsNames: the name of the customer or recipient within the Onfleet task
taskType: whether a task was a pickup or dropoff
dependencies: whether a task was linked to any other tasks within the dashboard
During delivery
startTime: when the driver started the actual delivery task within their driver app
startLonLat: the actual recorded Longitude and Latitude when the driver started the delivery task
arrivalTime: when the driver came within 150 meters of the delivery address; note: this is updated if the driver exits and re-enters the geofence, so it represents the last time they came within 150 meters of the delivery address before completing the task
completionTime: when the driver completed the actual delivery task within their driver app
didSucceed: whether the delivery task was completed as a successful delivery or a failed delivery by the driver
completionNotes: notes recorded by the driver upon completing the delivery task within the driver app
completionLonLat: the actual Longitude and Latitude recorded when the driver completed the task within their driver app
distance: how far the driver traveled between starting and completing that task
Post-delivery data
signatureUrl: a hosted url of any signatures captured by the driver upon completion of the delivery task
signatureText: the text field recorded by the driver upon capturing a signature from the recipient
photoUrl: a hosted url of any photos captured by the driver upon completion of the delivery task
barcodesCaptured: the text string of any barcodes captured by driver upon completion of the delivery task
failureReason: the reason selected by the driver as to why a task was failed
trackingViewed: whether the recipient viewed the real-time driver tracking page when the delivery was in-transit
feedbackRating: rating provided by the recipient (one to five stars range)
feedbackComments: any comments left by the recipient in the feedback survey
2. Using the Metadata Column Within the Export
One of the most valuable columns within our Export Feature is the metadatathat is currently exported to column AM within the csv. Here you will find a bundle of any metadata properties that have been added to the task through the Onfleet API. An example of this might look something like this:
[{“name”:”order_id””type”:”number””value”:”1447"”_id”:”5b012b3ab9fb43237150624c””visibility”:[“api”]}]
One of the trickiest parts of this bundle is separating this information into a format that can help you find the answers that you’re looking for. Don’t worry, we export this in a standardized way, no matter how many pieces of metadata you’ve managed to cram into your tasks! For starters, there are two very basic Excel commands that can be used to parse this data.
Filter the metadata column
The easiest tool to use for this job is the Excel Filter. Simply click on the metadata column header and type: cmd + shift+ F . This will pull up this filtering menu:
Here you can filter through the deliveries that contain certain pieces of metadata and separate them from the deliveries that don’t have the information you’re looking for. Once you have this filter in place, you should be able to find only the tasks that you’re trying to track.
Use Text to Columns tool within excel
The second way to manipulate this bundle of metadata is to simply separate all of the fields into their own cell. This can be done very quickly by utilizing the Text to Columns tool already built into Excel.
Simply start by highlighting the entire metadata column. Then in the Data tab within Excel, you should see the tool to move text to columns, click that. You will then see a wizard appear to select your separation parameters. That wizard should look like this:
In Step 1, select Delimited. Now select Next and you should see this next step:
In step 2, select Other for your Delimiters and in the text box type “. Now click Next and you should see this final step:
In step 3, simply click General and Finish. The tool should now take you back to your original spreadsheet, but with the metadata values separated into their own cells. From there, you can access, sort or filter the tasks/metadata properties that you’re looking for.
3. Basic driver reconciliation
With the trove of information available in the export, a simple system can create a relatively robust driver reconciliation process. While manual, the following payment systems can be automated with the use of Excel templates and/or the ever useful Zapier.
Paying drivers per stop and mile driven
If your operations have reoccurring or scheduled routes, it might be easier to pay drivers for the number of successful deliveries they do and mileage driven during those deliveries. Utilizing the following pieces of data within the export will allow you to quickly get this type of report:
workerName: will allow you to sort all of the deliveries by the driver that performed that deliverytaskType: this will allow you to determine whether a driver’s task was a pickup or dropoff and pay accordinglydidSucceed: will allow you to know whether that driver’s delivery was successful or not and pay accordinglydistance: this will allow you to determine how far the driver’s vehicle travelled while in-transit or actively performing that specific deliveryworkerName: this will allow you to sort your driversstartTime: this will allow you to determine when your driver started the first task of the daycompletionTime: this will allow you to determine when each task was completed, specifically you can use the completionTime of his/her final task to create a window of when that worker was actively working
Phew.. that was a lot. If you want to learn more about the Onfleet Export feature, check out our support center or Microsoft Excel’s very robust support center!