2. Data Analysis Methods and Procedures
2-7. Handling Missing Data Items
1) Current Realities of Client Data
- A. Bloated Item Master:
Product masters typically contain 2 to 3 times more registered items than active shipping SKUs due to legacy or discontinued items. Filter and extract active items for the target period whenever possible.
- B. Incomplete Dimensional Master Data:
For facilities without active volumetric management, dimension data in item masters is often unmaintained or unreliable.
- C. Ordering vs. Shipping Destinations:
Bill-to customers in data do not always equal actual ship-to delivery destinations. Summarize metrics based on actual physical destinations.
- D. Industry-Specific Conventions:
Understand custom terminology (e.g., central purchasing orders labeled as "Outbound" when acting as center "Inbound") and cleanse data accordingly.
2) Patterns of Missing and Irregular Data
- A. Zero or Negative Quantities:
Occurs due to inter-warehouse transfers, order cancellations, or returns mixed into shipping datasets.
- B. Unfilled Attributes (Null Values):
Blank values in critical fields such as case pack sizes, volumes, or weights.
- C. Partial Data Omissions:
Missing timestamps, destination codes, or product category flags in specific records.
3) Action Plans for Data Cleansing
- Establish Rules with Clients:
Agree on rules for handling missing data (e.g., average value substitution, exclusion criteria) beforehand with client stakeholders.
- Recover Salvageable Data:
Process and complement salvageable data whenever missing values can be estimated from other fields.
- Control Total Reconciliation:
Reconcile processed dataset totals against original control totals to clearly account for any excluded records.
- Execute Analysis on Cleaned Dataset:
Perform core analytical processing solely on the cleansed, valid target dataset.