Skip to content
Français

Meet the catalogue and the query log

  • Lesson 3
  • 40 min
  • Module 1: The search problem

Where we are

In the last two lessons, we saw how Larkfield's search fails, and the kinds of search that it must serve. So far, we only read results that someone else ran. In this lesson, we set up the project, open the catalogue and the query log ourselves, and run Larkfield's search and a first better search. A new shop assistant starts the same way, on the first day.

What you will be able to do

Set up the course project on your computer, and check that it works. Then read Larkfield’s three kinds of data yourself: what it sells, which answers are right, and what people searched.

NotebookDo the data part of this lesson in a notebookColab · Kaggle · GitHub

The notebook reads the catalogue, the queries with their judgments and the query log with pandas. It finds the most common queries and the long tail, and you compute the share of empty pages yourself. Its setup cells download the data, so it needs no local setup. About 15 minutes. The project setup below needs your own computer.

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 40 minutes

What you need to know

  • Larkfield’s search today (“every word must match”) and the head and the tail of the query log, from A shop where search fails.
  • The kinds of search (known item, specific, broad), from Kinds of search.
  • How to open a terminal and run a command. A virtual environment is a folder with its own Python packages for one project. If this is new for you, read Python for Practical AI Engineering first.

What you need on your computer

  • Python 3.12. Check it with python3.12 --version on macOS and Linux, or py -3.12 --version on Windows.
  • A code editor, such as VS Code, and a terminal.
  • Git, to download the course files. Without Git, you can download a ZIP file instead (step 1).
  • About 300 MB of free disk space for the project, plus the course files.

Tested on macOS with Python 3.12.13. The Windows (PowerShell) and Linux commands are written the same way, but we did not test them.

Think of a new assistant in the furniture shop. On the first day, nobody asks him to serve customers yet. First, he learns three things.

He walks through the shop and learns what it sells: where the sofas are, and what a chaise lounge is. Then an experienced colleague gives him a list of real customer questions with the best answers. “For teal chair, these are good, and this rug is wrong.” Last, he reads the shop’s notebook of past requests: what people asked this year, which requests came every day, and which came once.

Without these three things, he cannot learn, and nobody can check his work. A search team needs the same three things. Without them, every change to the search is a guess.

Larkfield’s search course has three kinds of data, in five files. The diagram shows how they connect.

Five boxes joined by arrows labelled with their keys. queries.csv, 624 queries with judgments, key query_id, leads by query_id to judgments.csv.gz, 291,230 grades, which leads by product_id to catalogue.csv.gz, 40,066 products, key product_id. log/searches.csv.gz, 20,000 searches (synthetic), key search_id, leads by search_id to log/impressions.csv.gz, one row per product shown, which leads by product_id to the catalogue. A dotted arrow labelled query text goes from the searches to the queries.
The five data files and the keys that join them. The catalogue is in the middle: the judgments and the log both point to its products.
Text description of the diagram

The diagram has five boxes. The catalogue, catalogue.csv.gz, has 40,066 products, and each one has a product_id. The queries, queries.csv, have 624 queries, each with a query_id. The judgments, judgments.csv.gz, have 291,230 grades. Each grade joins a query_id to a product_id. The log has two files. log/searches.csv.gz has 20,000 searches, each with a search_id. log/impressions.csv.gz has one row for each product shown in a search, with its search_id and product_id. A dotted arrow shows that a search’s text can match a query in queries.csv.

  1. The catalogue is what Larkfield sells: 40,066 products in 548 product classes, such as “Accent Chairs” or “Faux Florals”. Each product has a title, a description, a category and attributes. 7,597 products also have a French title.
  2. The queries and their judgments are the colleague’s list. A judgment is a person’s grade for one product and one query: exact, partial or irrelevant. The 624 queries are split into three sets: train (260), validation (121) and test (243). Module 2 explains why.
  3. The query log is the shop’s notebook: 20,000 searches. For each search, it also keeps the products that the page showed (the impressions), with their positions and clicks.

Where each part comes from:

Part Where it comes from Label in the course
Product titles, descriptions, classes, ratings WANDS (Wayfair), MIT licence real data
English queries and their judgments WANDS: Wayfair’s judges graded the products real data
French titles and French queries written by a language model from the English machine-translated
Spelling mistakes, product codes, “sofa”, “couch”, “canapé” our code a practice problem we made on purpose
Prices, stock, sale flags, dates added our code synthetic
The query log, its clicks and baskets our code, with a click model synthetic

WANDS means Wayfair ANnotation DataSet. It is a public dataset of product search, under the MIT licence: “Copyright (c) 2021 ecir2022”. We renamed the shop to Larkfield, kept the home and garden products, and gave every product a new ID. The full data card, data/dataset-card.md, gives the source, the licence, the columns, how the log was made, and the known gaps.

One planted problem is worth knowing now. Sale items are out of stock far more often than other items: 39.6% against 8.2%. This is a practice problem we made on purpose, and Module 7 uses it.

A tiny example: one search, through the files

Section titled “A tiny example: one search, through the files”

Follow one real row of the log through the files. Search S-00149 in the log is “sunflower”, on 30 September 2025:

search_id session_id date time query query_id results clicks added_to_cart reformulated
S-00149 V-00123 2025-09-30 12:39:23 sunflower W-025 182 2 1 False

The search found 182 products, and the page showed the top 10. Its first three impressions are:

search_id position product_id clicked added_to_cart
S-00149 1 LK-77381 1 0
S-00149 2 LK-39101 0 0
S-00149 3 LK-62538 1 1

The person clicked the products at positions 1 and 3, and added the one at position 3 to the basket. Now join on the IDs. LK-77381 in the catalogue is “sunflower sprays with 36 ‘’ sunflower stem”, in the class “Faux Florals”. In the judgments, query W-025 and product LK-77381 have grade 2: exact. In this search, all 10 shown products have grade 2.

So one search links all five files. Every measurement in this course is a join like this one, done for thousands of rows.

The project is larkfield-search: Larkfield’s product search as a small Python program, with the search command. You copy its first version, the snapshot start, from the course’s labs repository. Later modules add files to it, one module at a time.

  1. Get the course files. If you have Git, open a terminal and clone the labs repository once, in a folder such as ~/projects. If you have the clone already, run git -C nextia-learning-labs pull instead.

    Terminal window
    cd ~/projects
    git clone https://github.com/Nextia-Intelligence/nextia-learning-labs.git

    Without Git, download the ZIP file of the repository, and unzip it in ~/projects. Its folder is then nextia-learning-labs-main. Use that name in the next steps.

  2. Copy the snapshot start to a new folder larkfield-search. Then copy the course data into it, as data:

    Terminal window
    cp -R nextia-learning-labs/C38/snapshots/start larkfield-search
    cp -R nextia-learning-labs/C38/data larkfield-search/data
    cd larkfield-search
  3. Open the folder larkfield-search in VS Code. You see larkfield_search/ (the code), tests/, data/, README.md and requirements.txt.

  4. Make the virtual environment and install the packages. The project needs four packages and pytest: NumPy, pandas, SciPy and bm25s.

    Terminal window
    python3.12 -m venv .venv
    source .venv/bin/activate
    python -m pip install -r requirements.txt
    python -m pip install -e . --no-deps

    The last line installs the project itself, so the search command works in this terminal. On Windows, if PowerShell says that running scripts is disabled, run Set-ExecutionPolicy -Scope CurrentUser RemoteSigned once. Then activate again.

  5. Run the setup check:

    Terminal window
    search check
    Data: 15 files, checksums right (data)
    Index: built (40,066 products, 1.8 s)
    Query "garden hose": 10 results; first: LK-74728 anand self watering vinyl window box planter
    BM25 on the validation queries: NDCG@10 0.432 (expected 0.432)
    Setup check passed.

    The check compares every data file with its checksum, a short code computed from the file’s bytes. A changed file gives a different checksum. Then the check builds the search index, runs one query and measures the search on the validation queries. Your time can differ; the other numbers must be the same.

  6. Run the tests. They use a tiny fake data folder, so they take less than a second:

    Terminal window
    python -m pytest -q
    ............ [100%]
    12 passed in 0.11s

If a step fails, read When the setup check fails below.

Start with search info, which counts what is in the data:

Terminal window
search info
Catalogue: 40,066 products in 548 product classes; 7,597 with a French title (machine-translated)
In stock: 87.1%; on sale: 14.9% (synthetic)
Queries with judgments: 624 (260 train, 121 validation, 243 test); English 493, French 131
Judgments: 291,230 (exact, partial or irrelevant)
Query log (synthetic): 20,000 searches, 1,903 different queries, 2025-09-01 to 2025-10-26

search log reads the query log. With --top 10, it lists the 10 most common query texts:

Terminal window
search log --top 10
20,000 searches (synthetic), 1,903 different queries; 876 queries were searched only once
Zero-result searches: 6,188 (30.9%); with a click: 45.7% of searches
The 10 most common queries make 20.4% of searches:
1157 bee
606 rack glass
442 dumbbells
377 sunflower
313 industrial
293 canadian
258 rack
226 pasta tools
216 hibou
189 meditation

These are the head queries. Most of them are one or two words, and many are broad: “bee”, “sunflower”, “industrial”. “hibou” is French. Some look strange, such as “rack glass” or “canadian”. They are real WANDS queries, and real people type strange things.

Sort the 1,903 query texts from the most common to the least common. Then add up their searches, one text at a time. The curve shows how quickly the share of all searches grows:

The share of all searches that the most common query texts make, in the synthetic log. The x-axis is a log scale: each step is 10 times more texts.
Text description of the chart

The x-axis is the number of different query texts, sorted from the most common. It uses a log scale from 1 to 1,903: each labelled step is ten times more texts. The y-axis is the share of all 20,000 searches, from 0% to 100%. The most common text alone, “bee”, makes 5.8% of the searches. The top 10 texts make 20.4%, the top 40 make 37.1%, and the top 100 make 51.1%. The curve then rises slowly: the top 500 make 84.5%, and the top 1,000 make 95.4%. The last 903 texts add the final 4.6%.

Half of the searches come from only 100 texts. The other half is spread over 1,803 texts. This is the long tail: each text is rare, but together they are a large part of the work. The axis uses a log scale, where each step is ten times larger, so that the head and the tail both fit on one chart.

Add --zero to list the most common searches that found nothing:

Terminal window
search log --zero --top 5
20,000 searches (synthetic), 1,903 different queries; 876 queries were searched only once
Zero-result searches: 6,188 (30.9%); with a click: 45.7% of searches
Most common zero-result queries:
216 hibou
139 ligth bulb
119 ampoule
79 canapé
78 industriel

This is the hook number of the module: 30.9% of searches find nothing. Four of the five are French words: “hibou” (owl), “ampoule” (bulb), “canapé” (sofa) and “industriel” (industrial). “ligth bulb” is a real WANDS query with a spelling mistake.

The project has two ways to search, called runs. A run is one complete search method with a name. search eval --list shows them:

bm25 BM25 (k1 1.2, b 0.75) on the title and the description as one field; any word may match
current Larkfield's search today: SQLite FTS5, every word must match, title and description
  • current is Larkfield’s search today. It uses SQLite’s full-text search, FTS5, which is part of Python. It keeps the rule “every word must match”.
  • bm25 is the first better search. BM25 is a classic formula for ranking by words. Any word of the query may match, and a rare word counts more than a common word. Module 3 explains every part of it.

Both runs need an index: a table, built once, that lists for each word the products that contain it. It makes a search fast, because the program does not read 40,066 descriptions for each query. search check built the index already. To build it again, run search index:

Indexed 40,066 products in 1.8 s: SQLite FTS5 (title, description) and BM25 (title and description). Index size 54.0 MB.

The diagram shows what each command reads and writes:

data/, 15 files checked by their checksums, leads to search index, which writes outputs/index/ with SQLite FTS5 and BM25. outputs/index/ leads to search query, the top 10 of one query, and to search eval, NDCG@10 on the test queries. data/ also leads to search eval, with the judgments. search reset points with a dotted arrow to outputs/index/: it deletes outputs/ and checks data/.
What the commands read and write. The data is never changed; everything the project makes goes to outputs/, which search reset deletes.
Text description of the diagram

On the left is the folder data/, with 15 files that the project checks by their checksums. An arrow goes to search index, which builds the indexes and writes them to outputs/index/: one for SQLite FTS5 and one for BM25. From outputs/index/, one arrow goes to search query, which prints the top 10 for one query. Another arrow goes to search eval, which measures a run on the test queries. search eval also reads the judgments from data/. A dotted arrow goes from search reset to outputs/index/: reset deletes outputs/ and checks data/.

Try a query that works, then the same query with a spelling mistake:

Terminal window
search query "outdoor cushion" --run current -k 5
search query "grey outdor cushion" --run current
search query "grey outdor cushion" --run bm25 -k 5
current: "outdoor cushion" -> 5 results
1. LK-72281 indoor/outdoor seat/back cushion Furniture Cushions 37.99
2. LK-83044 make it rain cerulean indoor/outdoor loveseat cushion Furniture Cushions 9.99
3. LK-22523 rossmere indoor/outdoor bench cushion Furniture Cushions 13.99 [out of stock]
4. LK-84297 make it rain cerulean indoor/outdoor chaise lounge cushion Furniture Cushions 21.99
5. LK-37853 replacement pillows outdoor lounge chair cushion Furniture Cushions 44.99
current: "grey outdor cushion" -> 0 results
bm25: "grey outdor cushion" -> 5 results
1. LK-97544 grey owl painting print on wrapped canvas Wall Art 278.99
2. LK-65711 mesmerize grey throw pillow Accent Pillows 148.99
3. LK-94838 velvet task chair grey 119.99
4. LK-53055 mittler grey/white abstract rug Area Rugs 167.99
5. LK-61655 botti 33 '' wide armchair Reception Seating Chairs 964.99

Each line shows the position, the product ID, the title, the product class and the price. -k 5 shows 5 results instead of 10. These queries are not judged, so the results show no grades.

Read the two failures side by side. current finds nothing, because “outdor” matches no product. bm25 finds products, because any word may match. But “outdor” still matches nothing, so BM25 ranks by “grey” and “cushion” alone. A grey owl painting comes first. So BM25 removes many empty pages, but it does not understand the query. Module 4 fixes the spelling.

One query is a story. search eval measures a run on all 243 test queries, with their judgments:

Terminal window
search eval --run current
search eval --run bm25
current on 243 test queries: NDCG@10 0.195 (95% interval 0.155 to 0.237)
recall@100 0.146, MRR 0.312, zero results 66.7%, judged in the top 10 83.7%; 0.1 ms a query (p50), 0.6 ms (p95)
bm25 on 243 test queries: NDCG@10 0.429 (95% interval 0.381 to 0.476)
recall@100 0.388, MRR 0.438, zero results 12.8%, judged in the top 10 64.6%; 0.1 ms a query (p50), 0.3 ms (p95)

NDCG@10 is a score from 0 to 1 for the order of the top 10 results. 1 means the best possible order, and higher is better. Module 2 builds it by hand. For now, read three things:

  • BM25 scores 0.429, and Larkfield’s search scores 0.195: more than twice as high.
  • The 95% interval shows how much the score could move with other test queries. The two intervals do not overlap, so the difference is not luck.
  • On the test queries, current finds nothing for 66.7% of them, and bm25 for 12.8%. The test set has many French, misspelt and code queries, so it fails more often than the log (30.9%).
  • Leave the other numbers (recall@100, MRR, “judged in the top 10”) for now. Ranking measures explains each one.

These are the first two rows of the course’s comparison table. Every later module adds rows to it, measured on the same 243 test queries in the same way. The milliseconds that search eval prints are measured inside its loop; Module 8 measures the real time of each search.

Run search log --zero --top 5 again. Search each of the five texts with --run bm25. Which empty pages does BM25 fix, and which does it not? Write down why, before you read on.

What we got: BM25 finds light bulbs for “ligth bulb”, because “bulb” matches. It still finds nothing for “hibou”, “ampoule” and “canapé”. No English product text contains these French words.

Two commands keep the project in a known state:

  • search check checks the data, builds the index if it is missing, and measures BM25 on the validation queries.
  • search reset deletes outputs/ (the index and, later, trained models) and checks the data files. Use it to start again from a clean state.
Terminal window
search reset
Reset: removed 8 files from outputs/; data: 15 files, all files are right.

The project never changes the files in data/. If a data file is changed by mistake, copy the course data again from the labs repository.

search check ends with “Setup check failed: see the lesson’s troubleshooting list” when its numbers are wrong. Read its last line, and find it here:

Message Cause Fix
No course data found. Put the course data folder next to the project as data/, or set LARKFIELD_DATA to its path. data/ is missing, or you are in the wrong folder cd into larkfield-search, and copy the data again (step 2)
These data files are missing or changed: queries.csv. Download the course data again. a data file was changed or deleted copy that file again from nextia-learning-labs/C38/data/, then run search reset
command not found: search the virtual environment is not active in this terminal activate it again (step 4)
ModuleNotFoundError: No module named 'bm25s' the packages are not installed in this environment run python -m pip install -r requirements.txt again
NDCG@10 0.xxx (expected 0.432) with another number a changed index or code run search reset, then search check

Common mistake

Edit a data file to look at it

Symptom: you open data/queries.csv in a spreadsheet program to look at it, and save it by mistake. Later, search check stops:

These data files are missing or changed: queries.csv. Download the course data again.

Cause: saving rewrites the file, even with no visible change. The spreadsheet can change the line endings, the quotes or a number’s format. The checksum no longer matches, and the project refuses to measure with data that may be different. A score on changed data cannot be compared with anyone else’s score.

Fix: copy the original file from nextia-learning-labs/C38/data/ back to data/. Then run search reset. It must end with “all files are right”. To look at the data, use search info, search log or the notebook. They read the files and never write them.

Consequence in AI work: a measurement is only comparable when everybody uses the same data. Checksums turn “I think it is the same file” into a check that fails loudly.

Check your understanding

Follow search S-00149 through the files. Which file tells you that the person added a product to the basket, which file tells you what the product is, and which file tells you that it was a good result?

Show the answer
  • log/searches.csv.gz says that search S-00149 was “sunflower”, with 2 clicks and 1 product added to the basket.
  • log/impressions.csv.gz says which product: position 3, LK-62538, with clicked 1 and added_to_cart 1.
  • catalogue.csv.gz says what LK-62538 is: a salad plate with sunflowers.
  • judgments.csv.gz says that it was a good result: query W-025 and product LK-62538 have grade 2, exact. The IDs search_id, product_id and query_id join the files.

Check your understanding

BM25 scores 0.429 on the test queries and Larkfield's search 0.195. Tomás says: “BM25 also puts a grey owl painting first for ‘grey outdor cushion’. So the score is wrong.” What do you answer?

Show the answer

Both statements are true. One query is one example, and BM25 does fail on it: “outdor” matches nothing, so it ranks by “grey” and “cushion” alone. The score is an average over 241 of the 243 test queries (two have no right product). On that average, BM25 is more than twice as good. Its 95% interval (0.381 to 0.476) does not overlap with that of Larkfield’s search (0.155 to 0.237). The failure tells us what to fix next: spelling, in Module 4. A change must then be measured on all the test queries again.

Check your understanding

Which parts of the data are synthetic, and what does that mean for the click numbers in this module?

Show the answer

The query log (searches, impressions, clicks and baskets), the prices, the stock, the sale flags and the dates added are synthetic: our code made them. The French titles and queries are machine-translated, and the spelling mistakes, codes and “sofa”, “couch”, “canapé” queries are planted. So the click numbers show how the course’s click model behaves, for example that position 1 gets most clicks. They are not measurements of real Larkfield customers. The products, the English queries and the judgments are real data from WANDS.

Recap

  • The project larkfield-search is set up: the snapshot start, the data in data/, a virtual environment, and search check ends with “Setup check passed.”
  • The data has three parts. The catalogue has 40,066 products. The queries with judgments have 624 queries and 291,230 grades. The query log has 20,000 synthetic searches. The IDs product_id, query_id and search_id join them.
  • In the log, the top 100 query texts make half of all searches, and 876 texts were searched only once. 30.9% of searches find nothing, and the most common empty searches are French words and spelling mistakes.
  • current (every word must match) scores NDCG@10 0.195 on the test queries; bm25 (any word may match) scores 0.429. These are the first rows of the comparison table.
  • search reset deletes outputs/ and checks the data. The project never changes data/.

You can now run Larkfield’s search and BM25, and read their scores. But a score is only as good as the judgments behind it, and you have not seen how a judgment is made. Next, you judge products for a query yourself, and see where people disagree.

Next: Relevance judgments