Skip to main content

Splunk Lantern turns 5 on May 28th. Thank you for being one of our 750,000 annual users!
Click here to join our Slack channel to tell us what you love about the site or what content you'd like to see more of.
Lantern Home

 

Splunk Lantern

Carrier performance analysis

  1. Last updated
  2. Save as PDF

 

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.

  1. Set up EDI integration:
    1. Configure EDI 214 and EDI 210 message ingestion.
    2. Map key fields (for example, edi_carrier_id, edi_est_delivery_date, edi_act_delivery_date, edi_shipping_cost).
  2. Calculate delivery delays: Use eval to calculate delivery delays by comparing estimated and actual delivery dates.
  3. Assign on-time status: Use  eval to assign on-time status based on delivery delays.
  4. Aggregate carrier performance: Use stats to calculate average delivery delays, shipping costs, and on-time rates by carrier.
  5. Assign performance ratings: Use eval to assign performance ratings (Excellent, Good, Poor) based on average delivery delays.
  6. Visualize results:
    1. Create bar charts to compare carrier performance ratings.
    2. 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

index=supply_chain_edi sourcetype="edi:x12" edi_code IN (214 856) source=edi_quantumline_customer_full

Retrieves EDI transactions (shipment status 214 and shipment notifications 856) from the supply_chain_edi index, specifically from edi_quantumline_customer_full.

strcat "EDI:" edi_code " -- " _raw edi_code_raw

Creates a formatted string combining "EDI:", the edi_code, and _raw event data into edi_code_raw for better tracking.

| 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 

Aggregates data by edi_tr_id, capturing key transaction details:

  • edi_requestor: First recorded entity that initiated the transaction.
  • edi_responder: First recorded entity that responded.
  • edi_carr_id: List of carrier IDs.
  • evt_combined: Combined event details.
  • edi_code_dc: Distinct count of edi_code values per transaction.
  • time_took: Time range between the first and last event.
  • time_begin: Earliest timestamp of the transaction.
  • time_last: Latest timestamp of the transaction.
| 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:

  • ≤ 2 days → Excellent
  • ≤ 3 days → Good
  • ≤ 4 days → Poor
  • ≤ 6 days → Red (indicating significant delay).
| 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

clipboard_ef2035ee0f9ff457889c25cdd0bcd3421.png

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.

 

clipboard_e60118db780b14d3c65aae2c9ab7ea8f8.png

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.