Features and target timing

What you will be able to do

Build a modelling table in which every column was known at the moment the ticket arrived. A column from the future makes a model look excellent in a test and useless in real work, and it raises no error.

NotebookDo this lesson in a notebookColab · Kaggle · GitHub

The notebook has every script, task and check of this lesson, with the instructions. Its setup cells install pandas, download the practice data and rebuild the clean tables of Module 4, so it works without the local project. Read the explanations here, and run the code there.

Open in ColabOpen in KaggleView on GitHub

Colab and Kaggle open your own copy. They need a free Google or Kaggle account. Stuck? See the solution notebook.

Before you startWhat to know and to have readyAbout 50 minutes

What you need to know

• Reading and joining tables in pandas (read_csv with types, merge, groupby), from Transform with Python.
• What the cleaning step changed, and why a missing order_value has a meaning, from Handle messy values.
• Dates in SQL with datetime(…, '-90 days'), from Time and ranking.

What you need on your computer (only for the local route; the notebook needs none of it)

• The folder ~/projects/ticket-data with larkfield.db and q.py, from Set up a practice database: Build the database.
• The .venv environment with pandas 3.0.6, active in a terminal in ~/projects/ticket-data. You made it in Profile before changing: The same profile in pandas. If python -c "import pandas" fails, activate .venv again (source .venv/bin/activate on macOS and Linux, .venv\Scripts\Activate.ps1 on Windows).
• The clean tables data/clean/tickets.csv (1,398 rows) and data/clean/outcomes.csv (1,834 rows). You made them with python scripts/clean.py in Handle messy values: Guided practice: finish clean.py.
• If the clean tables are missing or wrong, use the course’s finished scripts in C04/pipeline. Download queries/extract_tickets.sql, queries/extract_outcomes.sql, scripts/extract.py and scripts/clean.py into the same folders of your project, for example curl -fsSL -o scripts/clean.py https://raw.githubusercontent.com/Nextia-Intelligence/nextia-learning-labs/main/C04/pipeline/scripts/clean.py (curl.exe on Windows). Then run python scripts/extract.py and python scripts/clean.py.
• This lesson adds scripts/build_features.py, data/model/ and queries/50-prior-tickets.sql.

Tested on macOS with Python 3.14.6 and pandas 3.0.6. The Windows commands are not tested. The notebook was tested in Jupyter.

The question Larkfield asks

Larkfield, the online shop for home and garden products, wants to send hard tickets to a senior agent early. When a ticket arrives, a model gives a score: how likely is it that this ticket will need a senior agent? This course prepares the data for that model. The data is made up for this course; it has no real people.

The important words are when a ticket arrives. That moment is the prediction time: the time at which the model must give its answer. Everything the model uses must exist at that moment.

One ticket, from start to end

Look at the life of ticket T-10033. Each event fills in more columns:

Time (9 January 2026)	What happens	Columns it fills in	Known when the ticket arrives?
08:31:54	The customer sends a web form about a payment.	created_at, channel = web_form, team = payment, priority = 3, word_count = 61, order_value = 66.25	Yes
09:24:54	An agent replies for the first time, after 53 minutes.	first_reply_minutes = 53	No
10:45:45	A senior agent takes the ticket (outcome event O-00026, escalated).	priority_now changes from 3 to 1	No
22:32:02	The ticket is resolved with a refund (O-00033).	closed_at, resolution_code, later perhaps csat	No

At 08:31:54, the model sees only the first row. It cannot know that the first reply will take 53 minutes, or that a senior agent will take the ticket two hours later. The help-desk export has all four rows, because the export ran on 2026-07-01, months later. That is the trap: the export knows the future of every ticket, and the model in real use does not.

Three words for the rest of the course:

• A feature is a column that the model uses as input. Here: facts about the ticket at prediction time, such as team and word_count.
• The target is the column that the model learns to predict. Some people call it the label. Here: did a senior agent take the ticket within 72 hours?
• Leakage is information in the features that the model would not have at prediction time, often information about the target itself. A leaky feature makes the evaluation score too high. In real use, the information is not there, so the model does worse than its score said.

Define the target exactly

“Escalated” is not precise enough. Escalated when? Mei, the data engineer, writes the target with Grace, the support lead:

escalated_72h = 1 if the ticket has an escalated outcome event within 72 hours after created_at; otherwise 0.

Grace explains the 72 hours: “A senior agent takes most hard tickets on the first day. An escalation after three days is usually a different problem, for example a customer who comes back angry.” For T-10033, the escalation came 2 hours and 14 minutes after creation, so escalated_72h = 1.

In the cleaned data, 224 tickets have an escalated event. 30 of them were escalated more than 72 hours after creation, so their target is 0. A precise definition changes real rows.

A target needs time to become final

The snapshot was taken on 2026-07-01 at 06:00. A ticket created on 30 June at 20:04 had only about 10 hours to escalate before the export. If it has no escalation event yet, its target is not 0. It is unknown: the 72 hours are not over.

So a ticket has a final target only if it was created at least 72 hours before the snapshot:

snapshot − 72 hours = 2026-07-01 06:00 − 72 hours = 2026-06-28 06:00

29 clean tickets were created after 2026-06-28 06:00. Six of them already have an escalation. The other 23 look like 0, but some of them may still escalate. Mei removes all 29 from the modelling table. If she kept them, the newest tickets would have too many zeros, and the model would learn that recent tickets escalate less often. That is not true; their story is only not finished.

What each column knows

Mei sorts every column of the clean tickets into three groups. For each column, she asks one question: when is this value written?

Column	When the value is written	Use
ticket_id, customer_id	At creation	Identifiers, not features. They name the row.
created_at	At creation	Used to make features and to split by time. Not a feature itself.
channel, team, priority, order_value, word_count	At creation	Features.
segment, region, joined_on	Before creation (from the customer table)	Features (joined_on becomes customer_tenure_days).
first_reply_minutes	After the first reply	Never a feature.
priority_now	Changes during the ticket’s life. Set to 1 when a ticket is escalated.	Never a feature.
closed_at	At resolution	Never a feature.
Outcome events: status, resolution_code, csat	After creation	Only for the target, never as features.

Look at priority and priority_now. They have almost the same name and the same values, 1 to 3. One is a fact at creation. The other one is a record of what happened later. A column name does not tell you when a value was written. Ask the person who knows the system, here Grace.

Customer history: from the past only

A customer’s history can help the model. A customer who wrote five times this month may be unhappy. But history is a place where leakage hides easily.

Customer C-0094 has six tickets in the modelling table. One of them, T-11014 in May, escalated. Compare two ways to describe this customer on each ticket:

Ticket	Created	prior_tickets_90d (tickets in the 90 days before)	All the customer’s tickets	The customer’s escalation rate, all tickets
T-10072	2026-01-14	0	6	1 of 6 = 0.17
T-10350	2026-02-19	1	6	0.17
T-10464	2026-03-04	2	6	0.17
T-11014	2026-05-13	2	6	0.17
T-11085	2026-05-22	2	6	0.17
T-11241	2026-06-11	2	6	0.17

The two right-hand columns are leaky. On 14 January, nobody knew that C-0094 would write six tickets, or that one of them would escalate in May. The value 0.17 on T-10072 contains the future. Worse, the escalation rate contains the target of T-11014 itself.

prior_tickets_90d counts only the same customer’s tickets created in the 90 days before this one. For T-11241 on 11 June, the 90 days start on 13 March, so T-11014 and T-11085 count, and the value is 2. Each value was known at the moment its ticket arrived. In Lesson 3, you measure how much the leaky escalation rate inflates a score.

Worked example: build_features.py

Mei puts these decisions into one script. It reads the clean files, makes the target, removes the tickets that are too recent and adds the features.

scripts/build_features.py

"""Build the modelling table: one row per ticket, with only the facts known
when the ticket is created, and the target, escalated_72h."""

import json
from pathlib import Path

import pandas as pd

IN = Path("data/clean")
OUT = Path("data/model")
SNAPSHOT = pd.Timestamp("2026-07-01 06:00:00")  # from data/extract/manifest.json
WINDOW = pd.Timedelta(hours=72)
TIME_FORMAT = "%Y-%m-%d %H:%M:%S"

# Columns filled in after the ticket is created. They describe the future of
# the ticket, so they must not be features.
AFTER_CREATION = ["priority_now", "first_reply_minutes", "closed_at"]
FEATURES = ["channel", "team", "priority", "segment", "region", "order_value", "word_count",
            "customer_tenure_days", "prior_tickets_90d", "created_hour"]


def main():
    tickets = pd.read_csv(IN / "tickets.csv", parse_dates=["created_at", "joined_on"],
                          dtype={"customer_id": "string"}, keep_default_na=False, na_values=[""])
    outcomes = pd.read_csv(IN / "outcomes.csv", parse_dates=["recorded_at"])

    # Target: an escalation within 72 hours of creation.
    first_escalation = outcomes[outcomes["status"] == "escalated"].groupby("ticket_id")["recorded_at"].min()
    tickets["escalated_at"] = tickets["ticket_id"].map(first_escalation)
    tickets["escalated_72h"] = (tickets["escalated_at"] <= tickets["created_at"] + WINDOW).astype(int)

    # A ticket's 72 hours must be over before the snapshot, or its label is
    # not final yet.
    eligible = tickets["created_at"] <= SNAPSHOT - WINDOW
    counts = {"clean_tickets": len(tickets), "too_recent_for_label": int((~eligible).sum())}
    df = tickets[eligible].copy()

    # Features known at creation time.
    df["customer_tenure_days"] = (df["created_at"] - df["joined_on"]).dt.days
    df["created_hour"] = df["created_at"].dt.hour
    df = df.sort_values(["created_at", "ticket_id"])
    df["prior_tickets_90d"] = prior_count(df, tickets)

    columns = ["ticket_id", "customer_id", "created_at"] + FEATURES + ["escalated_72h"]
    df = df[columns]
    OUT.mkdir(parents=True, exist_ok=True)
    df.to_csv(OUT / "tickets_model.csv", index=False, date_format=TIME_FORMAT)
    counts.update({"rows": len(df), "escalated": int(df["escalated_72h"].sum()),
                   "escalated_rate": round(df["escalated_72h"].mean(), 3)})
    (OUT / "build_summary.json").write_text(json.dumps(counts, indent=2) + "\n", encoding="utf-8")
    print(json.dumps(counts, indent=2))


def prior_count(df, all_tickets):
    """For each ticket: how many tickets the same customer created in the 90
    days before it. Only earlier tickets count, so the value is known at
    creation time. Guests have no history: 0."""
    history = all_tickets.dropna(subset=["customer_id"]).groupby("customer_id")["created_at"].apply(sorted)
    result = []
    for customer, created in zip(df["customer_id"], df["created_at"]):
        if pd.isna(customer):
            result.append(0)
            continue
        earlier = [t for t in history[customer] if created - pd.Timedelta(days=90) <= t < created]
        result.append(len(earlier))
    return result


if __name__ == "__main__":
    main()

The important lines:

Lines	Why
groupby("ticket_id")["recorded_at"].min() on the escalated events	The time of the first escalation of each ticket. A ticket with no escalation gets a missing value (NaT, “not a time”).
escalated_at <= created_at + WINDOW	1 if the first escalation came within 72 hours. A comparison with NaT is False, so a ticket with no escalation gets 0.
eligible = created_at <= SNAPSHOT - WINDOW	Keeps only tickets whose 72 hours ended before the snapshot. The count of removed rows goes into the summary, so nothing disappears silently.
columns = [...]	The output has an explicit list of columns. priority_now, first_reply_minutes and closed_at are not in it, so they cannot reach the model by accident.
prior_count(df, tickets)	Counts history in all clean tickets, including the 29 recent ones, because they are real past tickets for later rows. The condition t < created keeps only earlier tickets.
customer_tenure_days	Days between joined_on and creation. Guests and deleted customers have no joined_on, so the value is missing. Lesson 3 fills it.

Mei runs the script:

Terminal

python scripts/build_features.py

Output

{
  "clean_tickets": 1398,
  "too_recent_for_label": 29,
  "rows": 1369,
  "escalated": 188,
  "escalated_rate": 0.137
}

Count every row: 1,398 clean tickets − 29 too recent = 1,369 rows. 188 of them escalated within 72 hours, so the escalation rate is 188 ÷ 1,369 = 13.7%. The same numbers are saved in data/model/build_summary.json, next to tickets_model.csv.

Guided practice: build and check the table

1. Make the file scripts/build_features.py with the code above, and save it.

2. Run it from the project folder:

   Terminal

   python scripts/build_features.py

   You should see the same five numbers as Mei: 1398, 29, 1369, 188 and 0.137.

3. Look at one row. Make the file check_row.py in the project folder:

   check_row.py

   import pandas as pd
   
   model = pd.read_csv("data/model/tickets_model.csv")
   print(model.shape)
   print(model[model["ticket_id"] == "T-11241"].T)

4. Run python check_row.py. .T turns the row into a column, so it is easy to read:

   Output

   (1369, 14)
                                        1236
   ticket_id                         T-11241
   customer_id                        C-0094
   created_at            2026-06-11 17:53:05
   channel                             email
   team                             warranty
   priority                                3
   segment                              home
   region                              south
   order_value                         59.85
   word_count                             92
   customer_tenure_days               2506.0
   prior_tickets_90d                       2
   created_hour                           17
   escalated_72h                           0

   The table has 14 columns: three identifiers and times, 10 features and the target. priority_now, first_reply_minutes and closed_at are not there.

5. Check prior_tickets_90d = 2 with SQL, in a way that does not use pandas. Save this query as queries/50-prior-tickets.sql:

   queries/50-prior-tickets.sql

   SELECT ticket_id, created_at
   FROM tickets
   WHERE customer_id = 'C-0094'
     AND created_at >= datetime('2026-06-11 17:53:05', '-90 days')
     AND created_at < '2026-06-11 17:53:05'
   ORDER BY created_at;

6. Run it with python q.py queries/50-prior-tickets.sql:

   Output

   ticket_id  created_at
   ---------  -------------------
   T-11014    2026-05-13 11:25:10
   T-11085    2026-05-22 10:58:45
   
   2 rows

   Two earlier tickets in the 90 days, the same as the script. The query runs on larkfield.db, the raw export. That is fine for C-0094, who has no duplicate rows.

Why does the query use datetime(..., '-90 days') and <?

datetime('2026-06-11 17:53:05', '-90 days') returns 2026-03-13 17:53:05, the start of the 90 days. >= includes a ticket at that exact second, as the script does (created - 90 days <= t). < '2026-06-11 17:53:05' leaves out the ticket itself and everything later. If you write <=, T-11241 counts itself, and the value becomes 3.

Your turn: a 48-hour target

Omar, the product manager, asks: “What if we route tickets that escalate within 48 hours instead?” Change one thing and look at the result.

1. Copy the script to scripts/build_features_48h.py.
2. In the copy, set WINDOW = pd.Timedelta(hours=48).
3. In the copy, change the output names to tickets_model_48h.csv and build_summary_48h.json. The rest of the module needs the 72-hour files.
4. Predict: will more or fewer tickets be too recent? Will the escalated count go up or down?
5. Run the copy, and write down the five numbers.

Show the answer

The cut-off moves to 2026-06-29 06:00, so fewer tickets are too recent: 22 instead of 29. The output is:

Output

{
  "clean_tickets": 1398,
  "too_recent_for_label": 22,
  "rows": 1376,
  "escalated": 190,
  "escalated_rate": 0.138
}

The 48-hour target is a slightly different question. Some escalations between 48 and 72 hours now count as 0, but the table also has 7 more recent rows. So the escalated count goes up from 188 to 190, not down. The two changes push in opposite directions; only the run tells you the result. Delete the two _48h files and the copy of the script when you are done, so they do not confuse the next lesson.

Common mistake

A feature that works too well

Symptom: Tomás, the AI engineer, looks at the clean tickets. “Use priority_now. It works so well.” He is right that it works. Here are the 1,369 modelling rows, with priority_now joined back from the clean file:

check_priority_now.py

import pandas as pd

clean = pd.read_csv("data/clean/tickets.csv")
model = pd.read_csv("data/model/tickets_model.csv")
both = model.merge(clean[["ticket_id", "priority_now"]], on="ticket_id", validate="one_to_one")
print(pd.crosstab(both["priority_now"], both["escalated_72h"]))
print(pd.crosstab(both["priority"], both["escalated_72h"]))

Output

escalated_72h    0    1
priority_now
1              138  188
2              431    0
3              612    0
escalated_72h    0   1
priority
1              117  64
2              444  67
3              620  57

Every one of the 188 escalated tickets has priority_now = 1. No ticket with priority_now 2 or 3 escalated. The honest column, priority, is much weaker: escalated tickets appear at every priority.

Cause: priority_now is written after the escalation. The help desk sets it to 1 when a senior agent takes the ticket. So the column does not predict the escalation; it records it. At prediction time, every new ticket has priority_now equal to priority, and the strong pattern is not there.

Fix: ask when each value is written, and keep the answer in the code. Mei’s script lists the column in AFTER_CREATION and leaves it out of the output columns. A feature that separates the classes almost perfectly is a reason to check for leakage, not a reason to celebrate.

Consequence in AI work: with priority_now, a model scores very high in a test (Lesson 3 measures it). In real use, the column has no information about the future, and routing fails. The team trusts the model because of the high test score, so it can take weeks to find the problem.

See it end to endPrepare a leak-free table for city repair requestsCase study

The repair-requests case study labels a request only when its 14 days are over, and shows what goes wrong otherwise.

See it end to endPlan overnight bike rebalancingCase study

The bike case study predicts tomorrow morning’s target with features from earlier days only.

Knowledge check

Check your understanding

Which of these columns can be features for the escalation model: team, csat, word_count, closed_at, first_reply_minutes, segment? Give the reason for each one that cannot.

Show the answer

team, word_count and segment can be features: they are known when the ticket arrives. csat is recorded after the ticket is resolved. closed_at is written when the ticket is closed. first_reply_minutes is known only after the first reply. All three describe the future of the ticket, so they are leakage.

Check your understanding

Tomás says: 'Keep the 29 recent tickets. Those without an escalation event have escalated_72h = 0, so the target is correct.' Is he right?

Show the answer

No. Their 72 hours were not over at the snapshot, so a missing escalation means “not yet known”, not “no”. Some of the 23 zeros may become 1 after the snapshot. Kept in the table, they make the newest tickets look less likely to escalate, and the model learns a pattern that is not real. Remove them, and count them, as build_features.py does.

Check your understanding

Priya suggests a feature: the number of tickets the customer created in the 30 days around the ticket, 15 days before and 15 days after. Is it safe? How would you change it?

Show the answer

It is not safe. The 15 days after the ticket are in the future at prediction time, so the feature leaks. A customer with an escalated ticket may write again in the next days, so the leak is also linked to the target. Change it to the tickets in the 30 days before the ticket, with t < created, as prior_tickets_90d does.

Recap

• The prediction time is the moment the model must answer: here, when the ticket is created. Every feature must exist at that moment.
• The target escalated_72h is exact: an escalated event within 72 hours after creation. Tickets created in the last 72 hours before the snapshot do not have a final target yet, so 29 are removed and counted.
• Ask when each value is written. priority_now, first_reply_minutes, closed_at and everything from the outcome events are never features.
• Customer history must come from earlier tickets only. prior_tickets_90d does that; a total over all of a customer’s tickets does not.
• build_features.py writes 1,369 rows, 188 escalated (13.7%), with 10 features.

Next, you decide which rows the model learns from and which rows test it.

Next: Choose a split