Module 9: Machine learning on auction data

View study sheet (PDF) View SQL cheat sheet (PDF)

Teaching this module? A facilitator guide adds a preparation checklist, timing cues, discussion guidance, slides, and a printable PDF.

This session introduces machine learning through two concrete tasks built on auction and resale records: predicting a realized price and classifying whether a lot will sell. Both tasks look approachable at first glance and both contain a trap that catches working analysts as often as students, using information that would not have been available at the moment of prediction to predict an outcome that has already happened. The session spends real time on that trap before it spends any time on a model, because a model trained on leaked information will report excellent accuracy and will be worthless in practice. Students use the sandbox to explore the documented fields available before a sale (designer, high estimate, vendor, category, and the date itself) and export a training extract, and the class works through, conceptually and in an optional Python extension, how a regression model would predict usd_price_decimal and how a classification model would predict sold or unsold, always splitting training and test data by sale_date so that the model is evaluated the way it would actually be used, on lots it has not seen from a time period it has not seen.

Target course(s) and level

Introductory machine learning, applied data science, or a quantitative methods course with a project component. Suitable for advanced undergraduates and graduate students who have taken or are concurrently taking a first course in statistics or machine learning. The primary session requires no coding; the optional extension assumes working familiarity with Python and a standard machine learning library.

Learning objectives

By the end of this session, students will be able to:

  1. Frame a realized-price prediction task as regression and a sold-versus-unsold task as classification, and identify which documented fields are legitimate predictive features for each.
  2. Explain target leakage in plain terms and identify at least two ways it could enter a model built on this dataset, including using realized price to predict realized price.
  3. Explain why a train/test split for this data must be made by sale_date rather than at random, and describe what a random split would hide about a model's real-world performance.
  4. Use the sandbox to explore candidate features and export a training extract limited to fields that would be known before a sale.
  5. Interpret a simple accuracy or error metric for each task and identify at least one reason the metric could look good while the model is still unfit for use.
  6. (Extension) Load an exported training extract in Python, fit a baseline regression model and a baseline classification model, and evaluate each on a time-based holdout.

Prerequisites

An introductory statistics course covering regression and basic classification concepts, or a first course in machine learning. No prior exposure to this dataset is assumed. The optional code extension assumes basic Python familiarity and exposure to a standard machine learning library such as scikit-learn.

Materials and access needed

  • Sandbox access at sandbox.altfndata.com, self-registered with a work or school email, auto-approved.
  • Projector or screen share for the instructor demo.
  • The coverage browser tab and the schema tab, used before the demo to confirm the documented fields available for the chosen category table.
  • A simple diagram or handout distinguishing features known before a sale from outcomes known only after a sale, for use during the leakage discussion.
  • For the optional extension only: an instructor class API key requested from info@altfndata.com, and the downloadable Python client (altfndata_client.py) or tutorials notebook (altfndata_tutorials.ipynb) at docs.altfndata.com, along with a standard Python data science environment (pandas and a machine learning library such as scikit-learn).

Session outline (90 minutes)

  • 0 to 15 min: Introduce the two tasks, price prediction and sold/unsold classification, and preview the leakage trap that sits inside both.
  • 15 to 30 min: Feature framing. As a class, sort the documented fields into "known before the sale" and "known only after the sale," and build the before/after diagram on the board.
  • 30 to 45 min: The leakage and point-in-time discussion, anchored on why a random train/test split would overstate performance and why a split by sale_date is required instead.
  • 45 to 60 min: Guided demo, use the sandbox to explore candidate features for one category and export a training extract limited to pre-sale fields.
  • 60 to 75 min: Small-group exercise, groups sketch a feature list and a train/test split plan for a category or brand of their choosing, and identify one leakage risk specific to their plan.
  • 75 to 85 min: Class discussion, groups present their feature lists and leakage risks for critique.
  • 85 to 90 min: Wrap-up and homework assignment, including a note on the optional Python extension for students who want to fit an actual model.

In-class demo (sandbox-first, no code)

  1. Open sandbox.altfndata.com, sign in, and select the watches data table from the SQL editor dropdown.
  2. Open the schema tab and list the documented fields together as a class: designer, model, item_title, sale_date, usd_price_decimal, sale_estimates_high_usd_price, status, vendor, stock_ticker.
  3. Sort those fields on the board into two columns, known before the sale (designer, model, item_title, sale_estimates_high_usd_price, vendor, sale_date, category) and known only after the sale (usd_price_decimal, status). Point out that sale_estimates_high_usd_price belongs in the "before" column because a house sets its estimate ahead of the sale, while usd_price_decimal and status are outcomes.
  4. Run the first guided query below, which pulls a training-shaped extract using only pre-sale fields plus the two possible targets, and explain that the two targets, usd_price_decimal for regression and status for classification, must never also appear among the features for their own task.
  5. Introduce the point-in-time rule directly: split the exported rows so that everything before a chosen cutoff date is training data and everything on or after that date is test data, and explain that a model can only ever use information that existed at the time it would have made its prediction.
  6. Run the second guided query, which counts rows before and after a candidate cutoff date, and use the counts to choose a cutoff that leaves a reasonably sized test set.
  7. Walk through why a random 80/20 split, instead of a date-based split, would let the model see the future relative to some of its own test rows and would report performance that will not hold up once the model is actually used going forward.
  8. Export the training extract and show students where the export lives, for use in the small-group exercise, the homework assignment, and the optional Python extension.

Datasets and queries used

Dataset: watches data (documented fields: designer, model, item_title, sale_date, usd_price_decimal, sale_estimates_high_usd_price, status, vendor, stock_ticker).

Query 1, training-shaped extract with pre-sale features and both possible targets, sold lots only for the regression view:

SELECT designer, model, item_title, vendor, sale_date,
       sale_estimates_high_usd_price,
       usd_price_decimal,
       status
FROM all_watches_data
WHERE sale_estimates_high_usd_price > 0
ORDER BY sale_date;

Query 2, row counts before and after a candidate cutoff date, to size a point-in-time train/test split:

SELECT CASE WHEN sale_date < '2026-01-01' THEN 'train' ELSE 'test' END AS split,
       COUNT(*) AS row_count
FROM all_watches_data
WHERE sale_estimates_high_usd_price > 0
GROUP BY CASE WHEN sale_date < '2026-01-01' THEN 'train' ELSE 'test' END;

Optional API extension, equivalent request body to query 1 for a training extract, exported for the optional Python track (POST /v1/tables/all_watches_data/query, header X-API-Key):

{
  "fields": ["designer", "model", "item_title", "vendor", "sale_date", "sale_estimates_high_usd_price", "usd_price_decimal", "status"],
  "filters": [
    {"field": "sale_estimates_high_usd_price", "op": "gt", "value": 0}
  ],
  "sort": [{"field": "sale_date", "direction": "asc"}],
  "limit": 1000
}

Optional Python extension, a minimal point-in-time split and two baseline models built on the exported extract:

import pandas as pd
from sklearn.linear_model import LinearRegression
from sklearn.linear_model import LogisticRegression

df = pd.read_json("watches_training_extract.json")
df["sale_date"] = pd.to_datetime(df["sale_date"])
cutoff = "2026-01-01"
train = df[df["sale_date"] < cutoff]
test = df[df["sale_date"] >= cutoff]

# Regression: predict usd_price_decimal for sold lots only, using pre-sale features
train_sold = train[train["status"] == "sold"]
test_sold = test[test["status"] == "sold"]
features = ["sale_estimates_high_usd_price"]  # extend with encoded designer, vendor, etc.
reg = LinearRegression().fit(train_sold[features], train_sold["usd_price_decimal"])
price_predictions = reg.predict(test_sold[features])

# Classification: predict sold vs. unsold using only pre-sale features
clf = LogisticRegression().fit(train[features], train["status"] == "sold")
sold_predictions = clf.predict(test[features])

Discussion questions

  1. Why does using usd_price_decimal as a feature to predict usd_price_decimal count as leakage, even though it feels like an obviously circular mistake to name that way?
  2. sale_estimates_high_usd_price is set by the auction house before the sale. Is it a legitimate feature for both tasks, and what would make it less reliable as a predictor for a lot type the house rarely handles?
  3. What specifically does a random train/test split hide about a model's performance that a date-based split reveals?
  4. If a classification model achieves 90 percent accuracy predicting sold versus unsold, what question would you ask before trusting that number, given that most lots in many categories do sell?
  5. How might designer or vendor identity leak information about the outcome in a subtler way than an obvious target field, and how would you check for that?
  6. What would change about your feature list and your leakage risks if the task were reframed from predicting a single lot's price to predicting a category's median price for a future quarter?
  7. What real-world decision would a price-prediction model actually support at an auction house or a dealer, and what would happen if that decision were made using a model trained on leaked data?
  8. Why is it important to fix the train/test cutoff date before looking at test-set performance, rather than choosing it afterward to make the result look better?

Homework assignment

Each student selects one category table and drafts a one-page modeling plan covering both tasks: predicting usd_price_decimal for sold lots and classifying status as sold or unsold. The plan must list the exact features to be used for each task, drawn only from documented fields known before a sale, state the train/test cutoff date chosen and the row counts on each side of it (using a query like query 2), and include a short paragraph identifying one specific leakage risk in the plan and how it was avoided. Students who complete the optional Python extension may additionally submit a notebook that fits the two baseline models on an exported extract and reports a basic error or accuracy metric on the time-based test set; this is optional and not required for full credit. Grading criteria: correct separation of pre-sale features from outcome fields (30 percent), a defensible point-in-time train/test cutoff with supporting counts (25 percent), a specific and correctly explained leakage risk (30 percent), and clarity of the written plan (15 percent).

Going deeper

Key terms

  • Target leakage: using information that would not have been available at prediction time as a feature, which inflates apparent model performance.
  • Point-in-time split: dividing training and test data by date so that all test rows occur after all training rows, matching how the model would actually be used.
  • Regression: a model that predicts a continuous number, here the realized price, usd_price_decimal.
  • Classification: a model that predicts a category, here whether a lot's status is sold or unsold.
  • Baseline model: a simple model, such as linear or logistic regression, used as a floor of comparison before trying anything more complex.
  • Feature: an input variable available to a model, here drawn only from documented pre-sale fields such as designer, sale_estimates_high_usd_price, vendor, and sale_date-derived values.
  • Holdout set: the portion of data set aside and not used in training, reserved to evaluate how the model performs on data it has not seen.
  • Class imbalance: a condition where one classification outcome, such as sold, is much more common than the other, which can make a naive accuracy metric misleading.

Common pitfalls

  • Including usd_price_decimal, or any field derived from it, as a feature when the task is to predict usd_price_decimal itself.
  • Splitting train and test data at random instead of by sale_date, which lets the model implicitly see the future during training.
  • Reporting classification accuracy on an imbalanced sold/unsold split without also reporting sell-through, so a model that always predicts "sold" can look deceptively strong.
  • Treating designer or vendor as a free-text field without exact-match verification against the coverage browser, which silently drops or fragments rows that should have been grouped together.
  • Choosing a train/test cutoff date after looking at test-set results, rather than fixing it in advance, which quietly reintroduces the same overstatement problem a date-based split was meant to prevent.
  • Forgetting to filter out rows with a zero or missing high estimate before dividing by it anywhere in a feature or a target derivation.

Additional queries to explore

-- Class balance of sold vs. unsold, to check before trusting a classification accuracy figure
SELECT status, COUNT(*) AS lot_count
FROM all_watches_data
GROUP BY status;

-- Feature exploration: how sale_estimates_high_usd_price relates to realized price by vendor
SELECT vendor,
       approx_percentile(sale_estimates_high_usd_price, 0.5) AS median_high_estimate,
       approx_percentile(usd_price_decimal, 0.5) AS median_realized
FROM all_watches_data
WHERE status = 'sold'
  AND sale_estimates_high_usd_price > 0
GROUP BY vendor
HAVING COUNT(*) >= 200
ORDER BY median_realized DESC
LIMIT 20;

-- A second point-in-time cutoff candidate, to compare train/test sizing before committing
SELECT CASE WHEN sale_date < '2025-07-01' THEN 'train' ELSE 'test' END AS split,
       COUNT(*) AS row_count
FROM all_watches_data
WHERE sale_estimates_high_usd_price > 0
GROUP BY CASE WHEN sale_date < '2025-07-01' THEN 'train' ELSE 'test' END;

Extension activities

  1. Fit the two baseline models from the optional Python extension on a second category table and compare which pre-sale features carry the most weight, discussing whether that ranking makes intuitive sense.
  2. Design and justify a small set of engineered features derived only from sale_date, such as month or quarter, that could help a model without introducing leakage, and explain why a raw sale_date value itself is a poor feature for a model meant to generalize beyond the dates it was trained on.
  3. Take the class-balance query from the additional queries section and use it to argue, in writing, why accuracy alone is an insufficient metric for the sold/unsold classification task, proposing an alternative metric.

Connections to other modules

This module depends on the SQL and data-handling foundation built in Module 2, data science and SQL, and it reuses the pricing power and sell-through concepts introduced in Module 7, the business of the art market, as candidate features and framing devices. It pairs closely with Module 10, time series and market indices, since both modules require the same point-in-time discipline, splitting or bucketing strictly by sale_date, and both repeat the recency caveat as a reason to distrust results built on the newest available records.