Skip to content

Schema — recommendation / LTR

Tables an experiment of type=recommendation or type=ltr can publish (both share the recommendation profile). Query the data-kb name (RULE 1).

Contents

  • Fact (long): *__recommendations
  • Fact (rank-1 parity): *__predictions
  • Dimension: *__items
  • Publishing note
  • Example queries

Fact (long) — *__recommendations (composite PK (row_index, rank))

row_index (the source row the entity's list was scored from — on a long-grain run, the entity's first candidate row), the input's declared key column(s) (catalog role key — the entity id), rank (1-based position in the entity's top-N), item (FK → items.item_id), score (the item sub-model's calibrated propensity on a recommendation run; on an ltr run the ranking margin squashed to 0..1 — it orders the list, it is not a probability).

A recommendation run also says why each item, for this entity:

  • holding — held (already has it: a repeat, extra line or upgrade), previously_held (a win-back) or not_held (a cross-sell); null when the run has no holdings column.
  • reason_<j>_feature, reason_<j>_value, reason_<j>_direction (raises / lowers), reason_<j>_contribution for j = 1..3 (the scoring job's reasons_per_item, 0–5): the top drivers of the item's own sub-model at the entity's values — TreeSHAP local contributions in log-odds, engineered features rolled up to the input column, strongest first. Null when the item's sub-model has no tree to explain.
  • reason_text — a sentence or two to say out loud: the holding context and the drivers that raise the propensity.

An ltr run has one ranker over candidate pairs and no per-item model, so it publishes no holding or reason columns. There is no base_rate and no lift — see "Why there is no lift column" below.

Fact (rank-1 parity) — *__predictions (PK row_index)

row_index, the declared key column(s), predicted_item (FK → items). The flat "single best pick per entity" view, for parity with other prediction facts. No score column, and no row_role (the recommender splits per product sub-model, so a source row has no single role in the fit).

Dimension — *__items (PK item_id)

item_id. No category, no base_rate — nothing joins extra item attributes into the published star schema today.

Why there is no lift column

Do not compute score / base_rate and call it lift, and do not rank items by it. A served propensity and a class base rate are measured on different populations, so their ratio is a scale error rather than a lift — it is inflated before any signal is present, and it is largest for the rarest items. The backend (experiment_results_schema.py), the narrative builder (reco_results._recommend_narrative) and the UI tooltip (RecoInferencePanel) each refuse this ratio independently and for the same reason.

The comparable reading is score_percentile, available on the read API. When ranking items from SQL, rank by how often an item is recommended (rank = 1 counts) — that is what the published fact supports. score compares entities for one item; across items only the ranking (rank) is comparable.

Standard joins

recommendations ↔ input (entity) on the declared key column(s) (RULE 3); recommendations / predictions ↔ items on item / predicted_item = item_id.

Publishing note

Recommendation / LTR results publish at the end of the Run everything chain (train → explain → score → publish — the default for a launched plan), or with run_experiment(action="publish"): without a job_id it scores the run's dataset (or score_dataset_id) now; with one, it publishes that finished scoring job's lists. A refusal that says to score again means exactly that call without a job_id. The score stage scores every row of the dataset (event grain: each row is an entity's state — score a table of each entity's state now for the list to act on; long grain: one row per entity). If describe_catalog(workspace_id, experiment_id=…) 404s, the run hasn't been published yet.

Example queries

Placeholders <recommendations>, <items>, <input> are the data-kb names; <key> is the input's declared key column (catalog role key).

Most-recommended items (top-1): 

SELECT item, COUNT(*) AS times_top1
FROM "<recommendations>" WHERE rank = 1
GROUP BY item ORDER BY times_top1 DESC LIMIT 20;

Catalogue reach — how far down the list each item typically appears: 

SELECT item, COUNT(*) AS times_recommended, AVG(rank) AS avg_rank
FROM "<recommendations>"
GROUP BY item ORDER BY times_recommended DESC LIMIT 20;

What a cohort gets recommended: 

SELECT r.item, COUNT(*) AS n
FROM "<recommendations>" r JOIN "<input>" s USING (<key>)
WHERE s.income > 100000
GROUP BY r.item ORDER BY n DESC LIMIT 20;

A call-centre script — each entity's top 3 with the sentence to say: 

SELECT row_index, rank, item, holding, reason_text
FROM "<recommendations>" WHERE rank <= 3
ORDER BY row_index, rank;

Which traits most often push an item up (recommendation runs): 

SELECT item, reason_1_feature, COUNT(*) AS n
FROM "<recommendations>" WHERE reason_1_direction = 'raises'
GROUP BY item, reason_1_feature ORDER BY item, n DESC;

Recommendation mix by item category: 

SELECT i.category, COUNT(*) AS n
FROM "<recommendations>" r JOIN "<items>" i ON i.item_id = r.item
GROUP BY i.category ORDER BY n DESC;