Identifying Data Fields for Analytical Questions

Hello! In the previous lesson, you built a metric tree for two-day deliveries and checked that its arithmetic branches reconciled. The tree told you what to measure. This lesson addresses the next practical question: Which fields must exist in the data to answer a particular analytical question?

You will work from a defined question rather than browse every available column. That is a useful habit in both analytics and data science: it keeps the work tied to a decision while exposing missing data before you commit to an analysis.

Start with the answer you need to produce

Suppose an operations lead asks:

Over the last eight completed weeks, which fulfillment locations had a higher weekly late-delivery rate than the service overall, and how many eligible two-day orders were due at each location?

The intended output is one row per location and week, containing the eligible-order count, late-order count, location late rate, and overall late rate for that same week. A location comparison is useful only if those quantities share the definitions established earlier: genuine, dispatched two-day orders; assigned to a week by their promised deadline; and counted as late unless fully delivered by that deadline.

Before looking at a schema, translate the question into field roles:

1. Identify the unit: What distinguishes one order from another?
2. Define the eligible population: Which orders are genuine, dispatched, and two-day?
3. Place each unit in time: Which promised deadline determines its reporting week?
4. Determine the outcome: Was the entire order delivered by that deadline?
5. Assign the comparison group: Which location is responsible for the order?
6. Retain the comparison population: Can you calculate the service-wide rate from the same eligible orders?

This is more reliable than requesting “all order and delivery fields.” It also distinguishes a raw field, such as a promised-deadline timestamp, from a derived field, such as the week containing that deadline.

Watch the selected portion of End-to-End Data Analysis Project 2024 | SQL & Power BI | Beginner Friendly by Absent Data. Its example is bike sharing, not delivery, but notice the transferable move: the analyst identifies a field that connects tables, selects columns for the question, and calculates an output field from source columns.

A shared column name is not, by itself, proof that a join is safe. If a price table had several prices per year, joining on year alone could multiply rows or assign the wrong price. Field discovery must therefore include the table’s grain and the meaning of its keys, not just a list of column names.

Build a minimum field inventory

The names below are illustrative schema names, not claims about a database you already have. Each row states what the analytical question requires. The actual field might have another name or need to be derived from several tables.

The essential calculation is conceptually:

Calculate the service-wide rate from all eligible orders due in that week. Do not take an unweighted average of the location rates: a location with 10 orders should not have the same influence on the overall figure as one with 10,000.

Several fields in the table carry more meaning than their names suggest. service_tier_at_dispatch is preferable to a current service-tier label if an order’s classification can later change. promised_deadline_at must represent the promise used for this analysis; if promises can be revised, you need to agree whether the original or a permitted revised deadline counts. location_id_at_dispatch likewise needs a rule for orders handled by more than one location. The question cannot be answered consistently until those meanings are settled.

Notice what is not in the minimum inventory. customer_id was necessary for the previous lesson’s customers-per-week branch, but this question compares orders by location. Customer identity is unnecessary unless the scope expands. Carrier handoff time could help investigate why a location is late, but it is not needed to identify which locations have higher late rates. Keep such fields on a separate possible follow-up list.

Locate the fields in real data, not just on paper

A useful requirements inventory records where each field could come from and whether the source can support the calculation. Read the “Scope the Data Requirements” section of Enablerminds’ A Practical Guide to Scoping Business & Data Requirements for Analytics Projects. Focus on its distinction between a source system, a data object such as a table, the quality checks needed, and the span and refresh frequency of the data.

For the delivery question, your candidate sources might be an orders table with one row per order, a fulfillment table with one row per order, and a locations reference table with one row per location. Write down that expected grain alongside each source. Then verify it: a fulfillment system might instead contain one row per package or many delivery-status events per order.

If an order can contain multiple packages, delivered_at on one package does not mean the order was fully delivered. You would need a reliable package manifest, package-to-order identifiers, and delivery timestamps for all required packages to derive an order-level full-delivery time. Alternatively, use an authoritative fully_delivered_at field if its definition is verified. If a null timestamp can mean either “not delivered” or “delivery record missing,” the source alone does not yet support a trustworthy late count.

You can record the inventory compactly:

This is an inventory of requirements and checks, not a declaration that the data passes them.

Test whether the proposed fields can actually answer the question

Field availability is necessary but not sufficient. Walk through a few plausible records and ask whether your proposed logic produces an unambiguous result.

First, establish time rules. “Last eight completed weeks” needs a fixed reporting time zone and agreed week boundaries—for example, Monday through Sunday. Store or compare timestamps as actual instants, then assign deadlines to reporting weeks in the agreed time zone. Confirm the data extract includes all eight weeks and that its refresh schedule makes the most recent completed week usable. Late-arriving fulfillment records may cause previously reported weeks to be revised, so record the data-as-of time.

Second, establish join and counting rules. Begin with eligible orders, then connect fulfillment data without dropping orders that were never delivered. A join that retains only matched delivery records would quietly remove some late orders from both numerator and denominator. Check whether order_id is unique in each expected one-row-per-order table; if not, determine the source’s real grain before counting.

Third, establish classification rules. For an eligible order with a verified full-delivery timestamp, it is on time when that timestamp is no later than its promised deadline. A later timestamp is late. A still-undelivered order whose deadline has passed is also late—but only if the system lets you distinguish “not delivered” from “delivery information unavailable.” Flag unresolved data gaps rather than silently classifying them.

Finally, check coverage and attribution. If some orders have no location ID, do not simply remove them and call the remaining results service-wide. You might report an “unknown location” group while investigating the gap. If an order passed through two locations, agree which one the question means to hold responsible before producing location rankings.

These checks reveal a valuable distinction: a needed field is something the answer logically depends on; a trustworthy field is one whose definition, grain, coverage, and timing have been verified. An analyst should report when the first exists but the second does not.

The same method transfers to another sector. To ask which service queues missed their weekly case-resolution deadlines, you would seek a case identifier, eligibility or case-type fields, a promised resolution deadline, a verified full-resolution timestamp, and the queue assigned under an agreed rule. The labels change; the roles—unit, population, time, outcome, dimension, and join keys—remain.

Key takeaways

Work backward from the defined analytical question to an output grain and the raw fields needed for its population, time window, outcome, grouping, and joins. Separate minimum requirements from fields that might support later diagnosis. For every candidate field, verify its meaning, source grain, historical coverage, completeness, and relationship to other tables before treating the answer as feasible.

Next, you will turn an analytical question into testable hypotheses and success criteria. Today’s field inventory will help you judge whether the proposed evidence can actually support those tests.