Using AI to analyze the data mapping

[samshdn]

I am currently trying to use AI to analyze the data mapping, the following is what I did.

Concept:

1. Set the LLM model as a professional logistics data analyst role.
2. Include the candidate's status/what list, source data, and converted data as part of the prompt.
3. Instruct the AI model in the prompt to analyze the data and provide recommendations.

Execution steps:

1. Load status/what meta data from file system
2. Retrieve event records from the database using a specified tracking number, then construct JSON data from these records. The JSON data should include source data and converted data, such as mapped status and what fields.
3. Use the provided data to construct the prompt, then invoke the LLM to analyze the data.

Pseudo code

const systemMessage = "You are a professional logistics data analyst. " +
  "I am currently standardizing logistics data, converting routing data from different external logistics " +
  "providers into a standardized data format according to specific rules. " +
  "I will provide you with two sets of data: one is a standardized status & what list, and the other is shipment data containing the original data and the transformed status & what." +
  "The shipment data includes multiple routing data points for the transportation process. " +
  "Each routing data point contains an input and an output. " +
  "The input is the raw data provided by the external logistics provider, " +
  'and the output is the converted data presented in the form of "status & what." ' +
  'Please refer to the provided standardized "status & what" and analyze potential issues or inconsistencies in this standardization process.';
    
    const jsonStatusCode = load from file system;
    const data = load event records from database;
    const userPrompt = `The standardized status & what list is: ${JSON.stringify(jsonStatusCode)}` +
      `The shipment data is: ${JSON.stringify(data)},`;
    const messages: [] = [
      { role: "system", content: systemMessage },
      { role: "user", content: userPrompt },
    ];
    // call LLM 
    const completion = await getCompletion(
      "anthropic/claude-3-5-haiku-latest",
      messages,
    );
    console.log("Result:", completion);

JSON data input to LLM

[
  {
    "input": {
      "derivedStatus": "Label created",
      "eventDescription": "Shipment information sent to FedEx",
      "date": "2025-05-23T20:01:17-05:00"
    },
    "output": {
      "status": 3000,
      "what": "Transport Bill Created"
    }
  },
  {
    "input": {
      "derivedStatus": "Picked up",
      "eventDescription": "Picked up",
      "date": "2025-05-27T14:40:00-07:00"
    },
    "output": {
      "status": 3050,
      "what": "Picked Up"
    }
  },
  {
    "input": {
      "derivedStatus": "In transit",
      "eventDescription": "Shipment arriving On-Time",
      "date": "2025-05-27T14:41:00-07:00"
    },
    "output": {
      "status": 3001,
      "what": "Logistics In-Progress"
    }
  },
  {
    "input": {
      "derivedStatus": "In transit",
      "eventDescription": "Arrived at FedEx hub",
      "date": "2025-05-27T23:10:00-07:00"
    },
    "output": {
      "status": 3002,
      "what": "Arrived, In-Transit"
    }
  },
  {
    "input": {
      "derivedStatus": "In transit",
      "eventDescription": "Departed FedEx hub",
      "date": "2025-05-28T07:26:00-07:00"
    },
    "output": {
      "status": 3250,
      "what": "In-Transit"
    }
  },
  {
    "input": {
      "derivedStatus": "In transit",
      "eventDescription": "Arrived at FedEx hub",
      "date": "2025-05-28T13:09:00-05:00"
    },
    "output": {
      "status": 3002,
      "what": "Arrived, In-Transit"
    }
  },
  {
    "input": {
      "derivedStatus": "In transit",
      "eventDescription": "On the way",
      "date": "2025-05-28T17:02:00-05:00"
    },
    "output": {
      "status": 3001,
      "what": "Logistics In-Progress"
    }
  },
  {
    "input": {
      "derivedStatus": "In transit",
      "eventDescription": "On the way",
      "date": "2025-05-29T03:29:00-05:00"
    },
    "output": {
      "status": 3001,
      "what": "Logistics In-Progress"
    }
  },
  {
    "input": {
      "derivedStatus": "In transit",
      "eventDescription": "Departed FedEx hub",
      "date": "2025-05-29T03:37:00-05:00"
    },
    "output": {
      "status": 3250,
      "what": "In-Transit"
    }
  },
  {
    "input": {
      "derivedStatus": "In transit",
      "eventDescription": "At destination sort facility",
      "date": "2025-05-29T08:17:00-04:00"
    },
    "output": {
      "status": 3300,
      "what": "Arrived At Destination"
    }
  },
  {
    "input": {
      "derivedStatus": "In transit",
      "eventDescription": "On the way",
      "date": "2025-05-29T13:38:00-04:00"
    },
    "output": {
      "status": 3001,
      "what": "Logistics In-Progress"
    }
  },
  {
    "input": {
      "derivedStatus": "In transit",
      "eventDescription": "At local FedEx facility",
      "date": "2025-05-29T13:39:00-04:00"
    },
    "output": {
      "status": 3001,
      "what": "Logistics In-Progress"
    }
  },
  {
    "input": {
      "derivedStatus": "In transit",
      "eventDescription": "At local FedEx facility",
      "date": "2025-05-30T11:12:00-04:00"
    },
    "output": {
      "status": 3300,
      "what": "Arrived At Destination"
    }
  },
  {
    "input": {
      "derivedStatus": "In transit",
      "eventDescription": "On FedEx vehicle for delivery",
      "date": "2025-05-30T11:30:00-04:00"
    },
    "output": {
      "status": 3450,
      "what": "Final Delivery In-Progress"
    }
  },
  {
    "input": {
      "derivedStatus": "Delivered",
      "eventDescription": "Delivered",
      "date": "2025-05-30T19:43:00-04:00"
    },
    "output": {
      "status": 3500,
      "what": "Delivered"
    }
  }
]

Output from LLM

Result: After analyzing the shipment data and comparing it with the standardized status & what list, I've identified several observations and potential inconsistencies:

1. Status Progression Irregularities:

• Multiple status codes seem to be used interchangeably or inconsistently:
  • Status 3001 (Logistics In-Progress) is repeated multiple times during transit
  • Status 3250 (In-Transit) appears alongside other status codes
  • Status 3002 (Arrived, In-Transit) is used multiple times at different locations

2. Potential Redundant Status Entries:

• Some entries appear to represent similar states:
  • Multiple "Logistics In-Progress" (3001) entries
  • Multiple "Arrived At Destination" (3300) entries
  • Repeated "In-Transit" (3250) status

3. Logical Flow Observations:

• The progression seems mostly logical but with some non-linear transitions:
  3000 (Transport Bill Created) → 3050 (Picked Up) → 3001 (Logistics In-Progress)
  Then alternating between 3002, 3250, 3001, 3300 before final delivery

4. Recommendation for Standardization:

• Consider creating more precise rules for status transitions
• Define clear criteria for when to use each status code
• Potentially reduce redundant status entries
• Establish a more linear and predictable status progression

5. Specific Improvement Suggestions:

• Clarify the distinction between 3001 (Logistics In-Progress) and 3250 (In-Transit)
• Create clearer guidelines for when to use 3002 (Arrived, In-Transit)
• Ensure consistent use of destination-related statuses (3300 vs. 3450)

Would you like me to elaborate on any of these observations or provide more detailed recommendations for improving the standardization process?

[jduan00]

A good start!

My thoughts

1. We need more data to process using LLM.
2. The major and minor events are most important for a developer.