Carrier performance analysis
This article is designed to show you how to analyze carrier performance by leveraging EDI transactions to measure on-time delivery rates, shipping costs, and overall reliability, which is important for any organization looking to optimize their inventory. By leveraging EDI 214 (Transportation carrier shipment status message) and EDI 210 (Freight invoice), this article enables businesses to track carrier performance, identify inefficiencies, and make data-driven decisions to optimize shipping operations.
Key features of the procedures in this article include:
- On-time delivery tracking: Monitor on-time delivery performance using EDI 214.
- Shipping cost analysis: Analyze shipping costs using EDI 210.
- Carrier reliability metrics: Measure carrier reliability based on historical performance.
- Proactive alerts: Send notifications when carriers fail to meet performance thresholds.
Benefits of these procedures include:
- Improved carrier accountability: Track carrier performance to ensure timely deliveries.
- Reduced shipping costs: Identify cost-effective carriers and optimizes shipping strategies.
- Enhanced delivery reliability: Minimize delays and improves customer satisfaction.
- Data-driven decisions: Provide insights into carrier performance for better logistics planning.
Data sources
- Primary data sources:
- EDI 214 (Transportation carrier shipment status message) for shipment status updates
- EDI 210 (Freight invoice) for shipping cost data
- Supporting data sources:
- EDI 856 (Advance ship notice) for shipment details
- EDI 850 (Purchase order) for order details
Sample EDI events
EDI 856 (ASN)
BSN*00*SHIP456*20231001 DTM*011*20231005 (Promised delivery: 2023-10-05) MAN*CARRIER_X
EDI 214 (On-time delivery)
BSN*00*SHIP456*20231005 STC*D1*DELIVERED DTM*011*20231005
EDI 214 (Delayed delivery)
BSN*00*SHIP789*20231010 STC*DL*DELAYED DTM*011*20231012 (Actual delivery: 2023-10-12)
Step-by-step analysis instructions
This section provides a quick overview of the process you'll use to ingest EDI data, process it, and analyze carrier performance in the Splunk platform.
- Set up EDI integration:
- Configure EDI 214 and EDI 210 message ingestion.
- Map key fields (for example,
edi_carrier_id
,edi_est_delivery_date
,edi_act_delivery_date
,edi_shipping_cost
).
- Calculate delivery delays: Use
eval
to calculate delivery delays by comparing estimated and actual delivery dates. - Assign on-time status: Use
eval
to assign on-time status based on delivery delays. - Aggregate carrier performance: Use
stats
to calculate average delivery delays, shipping costs, and on-time rates by carrier. - Assign performance ratings: Use
eval
to assign performance ratings (Excellent, Good, Poor) based on average delivery delays. - Visualize results:
- Create bar charts to compare carrier performance ratings.
- Build timecharts to monitor delivery trends and shipping costs over time.
Base search
index=supply_chain_edi sourcetype="edi:x12" edi_code IN (214 856) source=edi_quantumline_customer_full | strcat "EDI:" edi_code " -- " _raw edi_code_raw | stats first(edi_requestor) AS edi_requestor, first(edi_responder) AS edi_responder, values(edi_carr_id) AS edi_carr_id values(edi_code_raw) AS evt_combined, dc(edi_code) AS edi_code_dc, range(_time) AS time_took, earliest(_time) AS time_begin, latest(_time) AS time_last BY edi_tr_id | eval _time=time_last | eval time_took_days=time_took/86400 | search edi_code_dc>1 | eval performance=case(time_took_days <= 2, "Excellent", time_took_days <= 3, "Good", time_took_days <= 4, "Poor", time_took_days <= 6, "Red") | table _time *
Search explanation
Splunk search | Explanation |
---|---|
|
Retrieves EDI transactions (shipment status 214 and shipment notifications 856) from the supply_chain_edi index, specifically from |
|
Creates a formatted string combining |
|
Aggregates data by
|
| eval _time=time_last |
Sets _time to the last recorded event (time_last ) for chronological sorting. |
eval time_took_days=time_took/86400 |
Converts the transaction duration (time_took ) from seconds to days for performance evaluation. |
search edi_code_dc>1 |
Filters results to only include transactions with more than one distinct edi_code (indicating multiple transaction steps). |
eval performance=case(time_took_days <= 2, "Excellent", time_took_days <= 3, "Good", time_took_days <= 4, "Poor", time_took_days <= 6, "Red") |
Assigns a performance rating based on transaction duration:
|
| table _time * |
Displays all fields in a structured table, with _time as the first column for chronological sorting. |
Extending the base search
The following search shows an example of further carrier performance analysis that you can perform by using the base search.
Carrier performance statistics
This search aggregates each carrier partner's performance along with the volume of business they handle.
... Base search omitted (Refer to the base search provided above and add here). | stats avg(time_took_days) AS time_took_days_avg, max(time_took_days) AS time_took_days_max, count AS ship_count BY edi_carr_id | eval time_took_days_avg=round(time_took_days_avg,2), time_took_days_max=round(time_took_days_max,2) | search ship_count>10 | fields - ship_count
We can further visualize this data using an intuitive chart that displays both the average and maximum delivery times, along with the total number of deliveries on top. Using Splunk's data overlay capabilities, we've layered the delivery volume as a bar chart, while presenting the average and maximum delivery times on a separate axis for better clarity with a legend.
The result provides a powerful visualization that helps understand each carrier's delivery performance and its impact in relation to the volume of activity with that particular carrier. These are just a few examples of KPI analysis, but there are countless other creative analyses that can be performed, depending on the specific requirements of the business.
Next steps
- Pilot implementation: Test the solution with a subset of carriers.
- Refine performance metrics: Adjust thresholds and scoring criteria based on pilot results.
- Scale up: Expand the solution to all carriers.
- Integrate with analytics: Combine with other EDI data (for example, EDI 856, EDI 850) for deeper insights.