Exploring a Dataset and Choosing What to Measure

Start from a decision, not from the table

“Customers are waiting too long for support” is a feeling, and feelings are where most analysis starts. It becomes a question when someone says which customers, waiting for what, compared with when, and what will be decided once the answer is known. A fictional online shop’s support team leader wants to decide whether to change how incoming tickets are routed. The question is therefore: which kinds of tickets wait longest for a first human reply, and what should the team measure each week to know whether a change helped? Everything below, the tickets, the times, the survey, and the people, is invented for this exercise.

The same move from feeling to measurement is familiar from public life. Prices “feel higher,” and a statistical agency answers with a defined measure: the consumer price index tracks the average change over time in prices paid for a fixed basket of goods and services, with a stated base period and adjustments for quality changes. The agency itself notes that the index does not necessarily describe any one household’s experience, because each household buys a different basket. A shopper who compares this month’s receipt with last year’s has a real observation, but it mixes price changes with changes in what and how much they bought, and it is not the national inflation rate. Keep that distinction in mind: a personal impression can be true and still not be the measurement the decision needs.

Before opening any file, write five things down. The decision: change routing or not. The population: tickets created by customers during a normal week. The unit of observation, the thing one row will describe: it might be a ticket, a reply, or a customer, and the difference matters. The period and the comparison: this week against the same measure in later weeks. And the outcome the decision is meant to improve: less waiting for a first human reply, without more tickets being reopened. Those five decisions are the specification the data will be checked against, and they are cheaper to write now than to reconstruct after the numbers disagree.

Read the data dictionary before reading the numbers

Data arrives from somewhere, and where it came from limits what it can say. The ticket system writes a row automatically whenever a ticket is created or reopened, with timestamps set by the server. That is generated data: which events are logged, and when, was decided once by whoever designed the system, so the log covers those events and only those, and it is silent about everything else, such as a customer who gave up before writing. Logging can also fail, duplicate, or be cut short by an extract, so a log is not complete merely because it is automatic; this exercise states as an assumption that the extract matches the source system’s count of eleven tickets for the week. The team’s survey of its agents, by contrast, is collected data: someone chose the questions, the wording, the timing, and who was asked, and every one of those choices shapes the answers. A shop’s own ticket log is first-party data; a study bought from a research firm would be second-party; a dataset that passed through several hands before reaching you is third-party and needs its provenance checked. Origin, not format, is what determines how much a source can be trusted for a given question.

The data dictionary states what each field means, its unit, its type, its allowed values, when it is recorded, and by what. The extract below comes with this dictionary: ticket_id identifies a ticket; a reopened ticket gets a second row with the same identifier. channel is chat or email. category_code is 1 for billing, 2 for delivery, 3 for account. created and first_reply are server timestamps in the shop’s local time; first_reply records the first message sent to the customer from the support system, including automatic acknowledgements. status is open or closed at the time of the extract, which was taken at 18:00 on the Monday after the week, one business day after it ended, so that late replies such as the one for T1011 are included. Support hours are 09:00 to 18:00, Monday to Friday; all times are in the shop’s single local time zone, and there were no public holidays in the period. Two of those sentences will turn out to matter more than the numbers.

Separate what was observed from what was derived, assumed, or estimated. The timestamps are observations, with the ordinary limits of measurement: a server clock can drift, a value can be rounded to the minute, an agent can click “reply” before writing. Reply time in minutes is derived from two timestamps by a rule the analyst chooses. The mapping from code 1 to “billing” is a definition someone wrote. A survey answer of 2 on a five-point scale is an observation of a choice, not a measurement of a quantity. Keeping these apart is the first protection against treating a rule as a fact.

Explore the rows: counts, keys, blanks, zeros, and ranges

Here is the whole extract for one week: twelve rows. In a real system you would look at a sample of a much larger file, but the checks are the same.

row	ticket_id	channel	category_code	created	first_reply	status
1	T1001	chat	2	Mon 09:10	Mon 09:14	closed
2	T1002	email	1	Mon 09:30	Mon 10:45	closed
3	T1003	email	3	Mon 11:00	Mon 11:20	closed
4	T1004	chat	2	Tue 14:05	Tue 14:07	closed
5	T1005	email	1	Tue 15:00	Tue 16:55	closed
6	T1006	email	1	Wed 10:15	(blank)	open
7	T1007	email	3	Wed 13:40	Wed 14:10	closed
8	T1007	email	3	Thu 09:00 (reopened)	Thu 09:50	closed
9	T1008	chat	2	Thu 11:30	Thu 11:33	closed
10	T1009	email	1	Thu 16:20	Thu 17:58	closed
11	T1010	chat	1	Fri 09:45	Fri 09:45	closed
12	T1011	email	2	Fri 17:50	Mon 09:05 (next week)	closed

Count first, and count the right thing. Twelve rows is not twelve tickets: T1007 appears twice because it was reopened, so the extract describes eleven tickets. The grain of this table is one ticket episode, not one ticket and not one customer; counting rows as customers would be wrong twice over. Then check the key. If ticket_id were assumed unique, an average per ticket would silently weight T1007 double. The dictionary said reopening creates a row, so this is expected structure rather than a defect, and the analyst has to decide how to treat it: for first-reply time, use the original creation, and count the reopening separately as a signal about reply quality.

Blank and zero are different facts. T1006 has no first_reply because nobody has replied yet: at the Monday extract it has been waiting since Wednesday morning: 2,085 business minutes, 465 on Wednesday and 540 on each of Thursday, Friday, and Monday. Dropping the row would hide the worst case in the week. This blank is not a lost record but an event that has not yet happened; what is observed is the elapsed wait, and that is enough to judge a 60-minute test. T1010 shows a reply at the same minute it was created, zero minutes. That is not a fast agent; the dictionary said first_reply includes automatic acknowledgements, and this chat received a bot message. The human reply came at 10:25, forty minutes later, which the extract does not contain and has to be looked up. A zero that means “the bot answered” and a blank that means “nobody has answered” are both real values, and each would corrupt an average if taken at face value.

Ranges expose definitions. T1011 was created at 17:50 on Friday and answered at 09:05 on Monday: 3,795 minutes on the clock. On a clock-time scale it is an outlier, a value far from the others, and it is not a recording error. Support hours end at 18:00, so in business minutes the wait was 10 minutes on Friday plus 5 on Monday, 15 minutes in total; both figures are minutes, and the difference is which hours the calculation includes. The question was never “how many clock minutes,” it was “how long did a customer wait while the team was working,” and the extreme value is what forces that definition into the open. Do not delete a value because its shape is odd; find out what produced it. An outlier is a value far from the rest; it may be a recording error or a genuine rare event, and only checking what produced it tells which.

Check the time range and the groups too. All eleven creation times fall inside one Monday-to-Friday week; that shows the range of the extract, not that every ticket of the week is present, which is why the assumption about matching the source count was stated. The reply for T1011 arrived after the week, which a cutoff rule must handle: this exercise measures at a cutoff one business day after the week ends, so Monday’s reply counts. The groups are small: four chat tickets, seven email tickets, five billing tickets. Any rate computed on five tickets moves by twenty percentage points when one ticket changes, which is a limit to state, not a reason to stop.

What the exploration found	Decision for this analysis	Reason
T1007 has two rows	First-reply time uses the original creation (30 min); the reopening is counted as one reopened ticket	The dictionary defines a row as an episode; reopening is a quality signal, not a second ticket
T1006 has a blank reply	Kept, reported as “no reply at cutoff,” counted as not meeting the target	Excluding it would remove the longest wait from the measure it is meant to detect
T1010 shows zero minutes	Replaced by the first human reply, 40 min, from the message log	The metric is about human replies; the automatic acknowledgement is not one
T1011 shows 3,795 minutes	Converted to 15 business minutes	The definition counts support hours; the record is correct, but clock time was a different time basis from the one the question asks about
category_code is numeric	Treated as a label, never averaged or summed	1, 2, 3 are names for billing, delivery, account, not quantities

Every one of these is a choice, and each has a reason next to it so that another person can disagree with the choice rather than with an unexplained number. This is exploratory data analysis in its plain form: looking at the data to learn its structure, its gaps, and its oddities before deciding what to compute. Its outputs include summaries, plots of distributions and relationships, flagged observations, and tentative hypotheses; in this exercise the output that matters most, and the one most often left unwritten, is the list of decisions in this table.

Numbers that are not quantities, and words that are not just feelings

A common summary says quantitative data is objective and qualitative data is subjective. Neither half survives contact with this extract. category_code is stored as a number and is not a quantity: “average category 1.8” means nothing, and a chart that sorts categories by their code sorts them by an accident of a configuration screen. The timestamps are numeric and objective in one sense, yet whether zero minutes counts as a reply was a human decision written into the dictionary. Numbers carry the choices of whoever defined, recorded, and derived them, which is why the dictionary came first.

Qualitative data is not merely opinion, either. The team ran an anonymous pulse survey of its twelve agents; eight responded. One item asked agreement with “I usually have enough time to answer billing questions properly” on a scale from 1 (strongly disagree) to 5 (strongly agree). The eight answers were 1, 2, 2, 2, 2, 3, 3, 4. It is tempting to report a mean of 2.4, but the scale is a set of ordered labels, not a measurement with equal steps: the distance from 1 to 2 is not known to equal the distance from 3 to 4, and 4 is not twice 2. Report the distribution instead: five of the eight respondents chose 1 or 2. Even that sentence stops at the respondents. Four agents did not answer, and if the busiest agents were the ones without time to fill in a survey, the answers understate the problem; if the most frustrated agents were the keenest to respond, they overstate it. Nonresponse is not a smaller sample of the same thing; it can be a different group.

The survey also had a free-text question. Three of the eight comments mention waiting for the finance team to confirm refunds. That is useful, and it is three comments, not a frequency for the team: comments were coded by one analyst using a rule written in advance, agents who did not mention finance were not asked about it, and eight people are not twelve. Used correctly, the comments supply a hypothesis for the ticket data to test, which is exactly the role qualitative evidence plays best. Used incorrectly, a vivid quotation becomes a statistic that nobody measured.

Choose what to measure, and write down the denominator

A measurement is only as clear as its definition: the numerator, the denominator, the unit of aggregation, the period, the exclusions, and the reference it is compared against. Here is the team’s choice for the decision at hand.

Role	Metric	Definition for this exercise	Value this week
Primary outcome	Share of tickets with a first human reply within 60 business minutes	Numerator: tickets created Mon–Fri whose first human reply came within 60 business minutes of creation. Denominator: all tickets created Mon–Fri. Measured at a cutoff one business day after the week; tickets still unanswered at the cutoff count as not met. One row per ticket; reopenings excluded from this measure.	7 of 11 = 63.6%
Guardrail	Reopened tickets	Tickets first closed this week that the customer reopened within five business days of that first closure, as a share of tickets first closed this week. Needs closure timestamps and a five-business-day follow-up for every ticket in the cohort.	1 reopening observed by the cutoff; the five-business-day rate cannot be determined yet, because closure times are not in this extract and the follow-up window has not ended for tickets closed late in the week
Guardrail	Agent workload	Overtime hours and active handling time relative to scheduled hours, per agent group, from the scheduling and time records; tickets per agent-hour is kept only as a supporting diagnostic, because the same throughput can hide different ticket complexity, overtime, and unfinished work. Owner: team leader; baseline and stop rule to be set before the routing change.	Not assessable from this extract; guardrail unevaluated
Diagnostic	Median first-reply minutes among replied tickets	Median of business minutes for the ten tickets with a human reply; the open ticket is reported beside it, not inside it	25 minutes
Quality check	Substantive first replies	A sample of first human replies each week reviewed against a written rule for whether the reply addressed the customer’s question, not merely acknowledged it. The primary metric measures first human contact, not resolution.	Not yet reviewed

The primary metric answers the decision’s question and includes the ticket nobody has answered, because a routing change that leaves tickets unanswered has failed even if every answered ticket was fast. The guardrails protect against the obvious ways to improve the primary number badly: closing tickets with hasty replies that get reopened, or overloading agents, and the workload guardrail needs overtime and handling-time evidence, because throughput alone cannot show overload. The share and the median share a blind spot: a quick holding message such as “we are looking into it” counts as a first human reply, improves both numbers, and leaves the customer waiting, and a ticket left open never gets reopened either. That is why the quality check exists: the primary metric measures first human contact, and whether replies were substantive has to be checked separately by reviewing a sample. Rates are stated with both parts: 7 of 11, not “64%.” The median is a proxy, a stand-in that is easier to track than the outcome the team actually cares about, customers not waiting; it is useful for diagnosis, but a falling median does not by itself show that customers wait less, and throughput, tickets handled per hour, is activity, not quality.

Why the median and not the mean? The ten replied tickets, in business minutes, sorted: 2, 3, 4, 15, 20, 30, 40, 75, 98, 115. Their mean is 402 / 10 = 40.2 minutes; their median, the average of the fifth and sixth values, is (20 + 30) / 2 = 25 minutes. The mean sits above seven of the ten tickets because three long email waits pull it up, so “average wait 40 minutes” describes almost nobody. Split by channel and the picture sharpens: the four chat tickets average 12.25 minutes and all four met the target; the six replied email tickets average 58.8 minutes, and only three of the seven email tickets met it, counting the unanswered one. Split by category, one of the five billing tickets met the target; all four delivery tickets and both account tickets did. A single overall average hid a tail, a channel difference, and a category difference at once. A percentile such as the 90th would locate the tail more directly, but with ten values it is little more than the second-largest observation, and that small-sample limit should be said out loud.

# ten tickets with a human first reply, in business minutes
first_reply_minutes = [4, 75, 20, 2, 115, 30, 3, 98, 40, 15]
# tickets created this week, including T1006, still unanswered at the cutoff
tickets_created = 11
values = sorted(first_reply_minutes)
mean = sum(values) / len(values)
median = (values[4] + values[5]) / 2
within_60 = sum(1 for v in values if v <= 60)
print(len(values), mean, median)
# 10 40.2 25.0
print(within_60, round(within_60 / tickets_created * 100, 1))
# 7 63.6

The code repeats the hand calculation so the two can be checked against each other; it does not decide anything the definitions did not already decide. A metric definition that names the cutoff, the exclusion of reopenings, and the treatment of the automatic acknowledgement is what makes next week’s 63.6% comparable with this week’s. Change any of those silently and the trend line measures the change in definition, not the change in service.

A pattern is a hypothesis, not a cause

Billing tickets waited longest, and three agents mentioned waiting for finance. That is a strong lead and a weak proof. The pattern is consistent with billing questions needing a finance lookup; it is also consistent with billing tickets arriving at the busiest hours, with one agent group handling most of them, or with five tickets being too few to show anything. Exploration generated the hypothesis; it cannot confirm it, because the same data that suggested it was used to find it. Confirming it needs something the exploration did not use: the message log showing finance waits, a later week, or a deliberate change to the routing of billing tickets followed by measurement under the definition already written.

Two habits protect against fooling yourself here. First, if you change the metric or the grouping after seeing the result, write that down as an exploratory choice, and do not present a pattern found by searching the same week as if it had been predicted. Had the team split by day of week, by agent, and by hour as well as by channel and category, some split would look striking by chance alone. Second, a before-and-after change is not an effect. If the team reroutes billing tickets and next week’s share rises to 80%, that is consistent with the change working and also with a quieter week, a new agent, or the finance team clearing a backlog. Comparing with the previous weeks, keeping the definition fixed, and looking at the guardrails narrows the alternatives; only a comparison group, or a change applied to some tickets and not others, would support a causal claim, and even then within limits. The same caution applies to studies you read: a report that employees with slow hiring processes left sooner shows an association in that data; it does not show that speeding up hiring would have kept them.

Context and bias enter before any statistic is computed. Ask who collected the data, what a value represents, where and when it was collected, how, and why. The ticket system was built to run support, not to measure it, so its timestamps mean what its engineers made them mean. The survey was written by the team leader, whose expectations shaped the questions. The analyst has a preferred explanation before opening the file. None of this is disqualifying; it is the reason to write definitions first, to report what was excluded and why, and to invite someone with a different expectation to check the reasoning.

When the data is not enough

Insufficient data is rarely a matter of too few rows. This extract has enough rows to compute a share and too little of almost everything else: one week cannot show whether billing waits are seasonal, the log lacks a field saying whether an agent was waiting on finance, the survey reached eight of twelve agents, the automatic acknowledgement contaminated the reply field, and there is no earlier week to compare against. Each is a different kind of shortage, and each has a different remedy.

What is missing	Option	Why the team chose or rejected it
Only one week	Extend the period before deciding; use the same definition for the previous four weeks	Chosen: the log exists; four more weeks show whether the billing gap is stable
No “waiting on finance” field	Add a reason code when an agent pauses a ticket; or read message logs for a sample	Both: the new field for the future, a sample of 20 billing tickets now, reported as a sample
Four agents did not respond	Ask the non-respondents directly; or report results as “eight respondents”	Report as eight respondents; a follow-up that identifies agents would break the survey’s anonymity
Reply field includes bot messages	Estimate human reply times; or fix the definition and re-extract	Fix and re-extract; an estimate would invent the very quantity being measured
No comparison week	Wait; or report this week as a descriptive result only	Report as descriptive, with the target not yet assessable; hold the routing decision until the four-week view exists

Some remedies look like data and are not. Filling T1006’s blank with the average would erase the longest wait; estimating T1010’s human reply from other chats would replace an unknown with a guess and then treat the guess as an observation; joining an external benchmark of “typical support reply times” would compare this team’s definition with somebody else’s. Interpolation, imputation, and outside data can be legitimate tools with their own methods, but none of them creates the observation that was not made. When the honest report is “seven of eleven tickets, one still unanswered, one week only, hypothesis about billing not yet tested,” that report is the deliverable, and holding the decision until the four-week view exists is a result, not a failure.

Read a data source with the same scepticism as this extract. A recorded value can be wrong because of the instrument, the person recording it, the definition, or the processing, which is measurement error; a sample can differ from its population by chance, which is sampling error, a separate thing; a value that is missing may be missing for a reason connected to its value, as when the busiest agents skip the survey, which is why the reason for a gap has to be considered before the gap is filled; and a dataset that contains only the people who chose to be in it can carry selection and nonresponse bias when the chance of being included is related to what is being measured, and arithmetic on the rows alone does not remove it. Statistical agencies list coverage, nonresponse, measurement, and processing errors as the non-sampling errors that every survey carries, and they note that a large sample does not shrink the systematic ones. A support team’s survey is no exception.

The order of work in this exercise was decision, dictionary, exploration, measurement, hypothesis, and limits. Frameworks describe similar sequences with different names and different numbers of steps, and a real analysis loops back: the exploration changed the metric definition, and the metric will change what the team collects next. What matters is that each step leaves a written trace that someone else can check, not that the steps were followed in a fixed order.

1. A colleague reports “average first-reply time this week: 40 minutes, so we are within the one-hour target.” What is wrong with the sentence?

Solution

Three things. The target is a share of tickets answered within 60 business minutes, not an average; seven of eleven met it, which is 63.6%, and the target for that share was never stated. The mean of 40.2 minutes describes the ten replied tickets and excludes T1006, which has waited longest; the median of those ten is 25 minutes, and three tickets waited 75 minutes or more. And an average across chat and email hides that every chat ticket met the target while most email tickets did not. Report the defined share with its denominator, the unanswered ticket, and the split by channel.

2. The team leader wants to add a metric: “tickets closed per agent per day,” and to publish it by agent name. Assess the proposal.

Solution

As a team-level throughput diagnostic it can help, but it cannot by itself show overload: the same throughput can hide different ticket complexity, overtime, and unfinished work, so the workload guardrail needs overtime and handling-time evidence as well. As a per-agent ranking it measures activity rather than the outcome the decision is about, rewards closing tickets quickly regardless of whether they reopen, and ignores that billing tickets take longer than delivery tickets, so agents handling billing would rank lower for reasons outside their control. If it is kept, define the denominator (agent-hours on duty, not calendar days), pair it with the reopen guardrail, report it at group level, and state that it is not a quality measure.

3. Next week the share within 60 minutes is 9 of 11, and billing tickets were rerouted on Monday. May the team conclude that rerouting caused the improvement?

Solution

Not from that comparison alone. Two weeks of eleven tickets each differ by chance, the mix of channels and categories may differ, and anything else that changed that week, a quieter period, staffing, finance clearing its backlog, is an equally available explanation. Check that the definition was unchanged, look at the previous four weeks, inspect the reopen guardrail, and compare billing against non-billing tickets, which were not rerouted, within the same week. Even a consistent picture across those checks supports “consistent with the change helping,” not proof; a change applied to some tickets and not others, decided in advance, would support more.