Data validation checklist for iQ Benchmark

This checklist provides a structured approach to validating data in the IQ Benchmark Application. Use these steps as a first-time user and periodically as regular practice to ensure your data remains accurate and reliable.

Data Validation Checklist

Overall data tests

• Total data check
• Recent data, within a week
• Missing historical data gaps
• Spot Contract mix
• Sell Rates

Data field tests

• Zip codes in Lanes table
• Carrier Name and SCAC in Carrier table
• Filters
  • Equipment Type
  • Office Number
  • Planner
  • Customer Name
  • Hazmat
  • Service Level
  • Drops
  • Commodity

Overall Data Tests

1. Total Data Check

• Ensure the data includes all expected divisions and periods.
  • Review the total freight spend and load counts. For example, a year’s worth of data might show $48 million in freight spend and 32,000 loads. This serves as a good initial “gut check” for data completeness.

2. Recent Data (within a Week)

• Confirm that recent data is available up to the current week. Ideally, data should be updated within three to five days. For example, if today is October 2nd, the most recent data should be from October 1st.

3. Missing Historical Data Gaps

• Check for gaps in the trend line for metrics like “rate per load.” Missing data will show as breaks in the trend line, which could indicate incomplete periods or missing data.

4. Spot vs. Contract Mix

• Ensure that your contract rates are flagged as "contract" and spot rates as "spot" in the data. A solid yellow bar in the application indicates missing contract rates.

Data Field Tests

1. Zip Codes in Lanes Table

• Zip codes should appear in the Lanes table, which is defaulted to a five-digit aggregation level. Missing zip codes may indicate incomplete submissions, impacting future map features and analytics.

2. Carrier Name and SCAC in Carrier Table

• The Carrier table should display carrier names alongside SCAC codes. If either value is missing, the data will aggregate to a single row. Ensure both fields are submitted for detailed reporting.

Filters for Specific Data Analysis

Check that the following fields are available and populated in your data to improve analytical insights:

• Equipment Type: Ensure your data includes all applicable equipment types (e.g., van, flatbed, reefer).

• Office Number: This field can represent regions or divisions, helping to segment data by location.

• Load Planner: Use this to filter data by employee name or code for planning insights.

• Customer Name: Ensure customer names are accurate to maintain clear customer segmentation.

• Hazmat: Verify that the Hazmat flag is present if applicable.

• Service Level: Use to separate data by service types like "expedited" or "team."

• Drops: The number of drops can aid in analyzing load complexities.

• Commodity Type: Provides an additional layer of segmentation based on product type.