DAT 565 Week 1 Retrieving and Preparing Business Data Example

Before the Dashboard: Retrieving and Preparing Order Data at an Atlanta Fulfillment Warehouse

[Student Name]

University of Phoenix

DAT/565: Data Analysis and Business Analytics

Week 1 Assignment

[Instructor Name]

[Date]

Peachtree Fulfillment Partners, its systems, clients and figures are composites written for a model paper.

Peachtree Fulfillment Partners, a composite company, runs a 640,000-square-foot fulfillment center in Fayetteville, Georgia, south of Atlanta. It stores, picks, packs and ships products for 34 client brands, mostly online retailers of apparel, beauty products and home goods, shipping about 18,000 orders on an average day and more than 40,000 during holiday peaks. Clients pay by the order and the unit, and their contracts set targets for order accuracy and same-day shipping. Last quarter, three clients complained about accuracy, and one gave notice. The chief operating officer wants to know why performance differs so much across clients. This paper describes how the data to answer that question were retrieved and prepared.

The Business Question

The question shapes the data. Leaders want to compare order accuracy, the share of orders shipped without a picking or packing error reported by the client, and on-time shipping, the share of orders shipped by the carrier cutoff on the day they were released, across clients, and to understand what explains the differences: order profile, product types, staffing or process. That requires data at the level of individual orders, linked to the client, the items picked, the people who picked them and the carrier scan that confirms shipment.

Data Sources

Four systems hold the needed data. The warehouse management system records every order, its lines, the client, release and completion times and the pick location for each item. The shipping system records carrier labels and the first carrier scan. The labor management system records which associate picked each line and when. Client service agreements, with targets and special handling rules, live in a spreadsheet kept by the account management team. Client-reported errors arrive by email and are logged in a ticketing system.

Kandel et al. (2012) interviewed analysts in many enterprises and found that much of their time went to discovering, wrangling and profiling data scattered across systems, that data quality problems were common and that analysts often depended on colleagues who understood each system. That pattern describes Peachtree exactly: no single person knew all four systems, and the warehouse systems team, the shipping coordinator and an account manager each had to explain their data.

Extraction

The warehouse management system's reporting database allows read-only queries. A query extracted 12 months of order lines, about 2.6 million rows covering 5.9 million units, with order identifier, client code, line number, item, quantity, release time and completion time. The shipping system exported 6.4 million carrier scans, more than the number of orders because some orders ship in multiple boxes. The labor system exported pick records keyed by order line. The ticketing system exported 9,800 client error reports. Each extract was saved unchanged as a raw file with the date of extraction so that any later step could be traced back.

Problems Found

Profiling each extract turned up problems that would have distorted any report built directly on the raw data.

Duplicates. About 1.2 percent of order lines appeared twice, created when the warehouse system retried a transmission after a network timeout. Counting them would inflate volume and, for some clients, accuracy denominators.

Time zones. The warehouse system stores times in Eastern time; the shipping system stores carrier scans in Coordinated Universal Time. Without conversion, about 30 percent of evening orders would appear to ship the next day.

Changed client codes. Two clients merged during the year and were assigned a new code; their history sat under three codes.

Missing scans. About 0.8 percent of shipped orders had no carrier scan, mostly from one regional carrier whose data feed failed for nine days in March.

Error reports. Client error tickets referenced order numbers in three formats, some with a client prefix and some without, and about 6 percent could not be matched to an order at all.

Redman (1998) argued that poor data quality imposes costs throughout an enterprise, including lower customer satisfaction, higher operating costs and weaker decisions, and estimated that error rates in typical enterprise data are high enough to matter for operations and strategy. Peachtree's problems are ordinary, but each would have misled the chief operating officer if left in place.

A report built on the raw files would have shown thirty percent of evening orders shipping late; the warehouse was fine, the clocks disagreed.

Corrections

Each problem received a documented rule. Duplicate order lines were removed by keeping the first record with each order and line number. All times were converted to Eastern time. Old client codes were mapped to the merged client's current code through a lookup table. Orders with missing scans were flagged rather than counted as late, and the nine days of the carrier outage were marked so that on-time rates for affected clients could be shown with and without them. Error tickets were matched after standardizing order number formats; the unmatched 6 percent were reviewed with account managers, who identified most as tickets about inventory discrepancies rather than specific orders.

Joining and Checking

Joins are where errors hide. The order lines were first aggregated to one row per order, then joined to carrier scans by order identifier, keeping the earliest scan, and to error tickets. Row counts were checked at each step: the order table held 2,512,000 orders after removing duplicates and still held 2,512,000 after both joins, confirming that no orders were dropped or multiplied. A random sample of 200 orders was traced back to the source systems to confirm values.

The Analysis Table

Wickham (2014) described tidy data as tables laid out so that every column holds one variable, every row holds one case and every kind of case gets its own table, and showed that tidy structures make cleaning, analysis and visualization simpler. The final table has one row per order, with columns for client, release time, ship time, lines, units, product categories, pick zones visited, picker count, carrier, on-time flag, error flag and outage flag. A second table holds one row per client with contract targets and special handling rules.

What the First Look Shows

Even before formal analysis, simple counts from the prepared table hint at answers. Clients whose orders average more than four lines have lower accuracy than those averaging one or two, and clients with many small, similar-looking items, such as cosmetics in several shades, account for a disproportionate share of errors. These are patterns to test, not conclusions, but they show why careful preparation pays off: the questions are already sharper.

Documentation and Maintenance

A data log records every source, query, rule and row count. The tables will refresh weekly through scheduled queries, with automatic checks that alert the analyst if row counts change unexpectedly or time zones drift. Access is limited to operations leaders and analysts, since order data include customer addresses, which are excluded from the analysis tables entirely.

Conclusion

Answering a simple question about client performance required four systems, 2.6 million records and a dozen documented rules. Research on enterprise data work and data quality shows that this stage is where analytics succeeds or fails. Peachtree now has a tidy, documented and refreshable table on which Week 2's reports and descriptive statistics can be built with confidence.

References

Kandel, S., Paepcke, A., Hellerstein, J. M., & Heer, J. (2012). Enterprise data analysis and visualization: An interview study. IEEE Transactions on Visualization and Computer Graphics, 18(12), 2917-2926. https://doi.org/10.1109/TVCG.2012.219

Redman, T. C. (1998). The impact of poor data quality on the typical enterprise. Communications of the ACM, 41(2), 79-82. https://doi.org/10.1145/269012.269025

Wickham, H. (2014). Tidy data. Journal of Statistical Software, 59(10), 1-23. https://doi.org/10.18637/jss.v059.i10