Schema — association rules (market basket)

Tables an experiment of type=association publishes (profile association). Query the data-kb name (RULE 1). Unlike the prediction facts, these tables describe the dataset, not individual rows — there is no row_index.

Association rules are co-occurrence statistics, not causal claims. A rule A → C says baskets containing A also contain C more often than chance — it does not say A causes, drives, or leads to C. Phrase findings as "tend to appear together", never as "drives". The two items may share a cause, be bought in one trip, or be shelved side by side.

Contents

• Fact: *__assoc_rules
• Dimension: *__assoc_items
• Bridge: *__assoc_rule_items
• Fact: *__assoc_itemsets
• Fact: *__assoc_holdings
• Reading the quality flags
• Example queries
• Opportunity audiences (rule → targeting list)

Fact — *__assoc_rules (PK rule_id)

One row per mined rule "antecedent → consequent". rule_id (stable hash), antecedent_text / consequent_text (" + "-joined item names), antecedent_len / consequent_len, support (share of baskets with the whole rule), antecedent_support / consequent_support, confidence (P(consequent | antecedent)), lift (confidence over the consequent's base rate), lift_breadth_adjusted? (the lift among baskets holding the same number of items besides the consequent — broad baskets hold more of everything, so on holdings most of a lift can be breadth; near 1 here means the rule describes broad baskets, not the pair), leverage, conviction? (null when infinite — which happens for every confidence = 1 rule, i.e. the strongest ones), zhangs_metric, jaccard, certainty, kulczynski, p_value / q_value, significant, fwer_significant, support_holdout? / confidence_holdout? / lift_holdout? / lift_breadth_adjusted_holdout? / holds_up (out-of-sample re-measurement; null unless the run withheld baskets), pruned_by?, rank? (1-based; null for flagged rows — insignificant or redundant rules kept for inspection), shortlisted? (the shortlist a person approved at release — WHERE shortlisted is the approved deliverable; the column is absent on a run with no approved shortlist).

Dimension — *__assoc_items (PK item_id)

Every observed item, before exclusions. item_id (SQL-safe slug), item_name (raw, for display), category?, support (share of baskets containing the item), n_baskets.

Bridge — *__assoc_rule_items (PK (rule_id, item_id, side))

Rule ↔ item membership: rule_id (FK → assoc_rules), item_id (FK → assoc_items), side (antecedent | consequent). This is the join that answers "all rules involving item X".

Fact — *__assoc_itemsets (PK itemset_id)

Every frequent itemset above the resolved support floor — the second deliverable beside the rules. itemset_id (positional — it is not stable across reruns; join on items/itemset_text, never on the id), itemset_text (" + "-joined), items (the structured member list — prefer it over splitting the text, which is wrong for any item whose own name contains " + "), length, support.

items is a DuckDB list (VARCHAR[]) of raw item names: test membership with list_contains(items, 'guacamole'), or list_has_all(items, ['chips', 'salsa']) for several at once. query_sql returns it as a JSON array — a singleton too (["chips"]) — and describe_catalog samples it in that same form.

Singletons are included (length = 1). Filter WHERE length >= 2 when counting bundles, or the vocabulary size inflates the answer.

Fact — *__assoc_holdings (PK (entity_id, item_id))

Per-entity item holdings — the "who holds what" fact behind rule → audience activation. One row per (entity, held item): entity_id (raw id, the targeting-list key) + item_id (FK → assoc_items, slugged).

Two caveats that change answers:

• Holdings are melted after item exclusion, while assoc_items is pre-exclusion. An item removed by exclude_items or by the ubiquitous-item guard appears in assoc_items with a high support but has zero rows in assoc_holdings. "How many entities hold X" will read 0 for exactly the most common items.
• Present only when an entity id was resolvable at mine time (the long / event grains); a basket-grain run publishes no holdings, so this table is absent — check describe_catalog(workspace_id, experiment_id=…) before querying it.

Reading the quality flags

The shipped list is WHERE rank IS NOT NULL. Rows with rank IS NULL were kept deliberately, for one of two different reasons — and significant alone cannot tell them apart:

• significant = false — failed the multiple-testing gate. A candidate, not a finding.
• pruned_by IS NOT NULL — unranked for a stated reason: a rule_id (redundant against a shorter rule it failed to beat — an extra item that adds nothing, on confidence or among baskets of equal breadth — or against the ranked rule over the same items, i.e. the other direction of a pair), 'baseline' (no better than its consequent's own base rate), or 'max_rules' (passed every gate but ranked beyond the cap — a runner-up). Such a rule can still have significant = true, so WHERE significant returns redundant rules as findings. Filter on rank IS NOT NULL unless the question is about the flagged rows themselves.

What q_value does and does not mean. p_value is a one-sided Fisher exact test per rule and is sound. q_value is a Benjamini–Yekutieli adjustment (valid under arbitrary dependence, unlike Benjamini–Hochberg) — but it is computed across the rules that survived the support / confidence / lift filters, not across every rule the search considered. Because those filters select on the same counts the test measures, q_value understates the true false-discovery rate, most for rules sitting near the confidence and lift floors. Treat it as a relative ranking of evidence strength within this list, not as a calibrated error rate.

fwer_significant is the assumption-free companion: p_value × <every rule the search space contains> ≤ alpha. It is conservative, but it holds under any dependence and any selection — a rule flagged fwer_significant = true is defensible without caveat. Prefer it when the question is "which of these can I state as a finding?".

holds_up beats both. When the run was configured with a holdout, a random share of baskets was withheld before mining and every rule re-measured on them: lift_holdout is the only metric here not computed on the baskets that selected the rule. holds_up = true means the rule came back above the run's own min_lift — the bar it cleared to be shipped, not the weaker "better than independence" — on data that had no say in choosing it. The strongest evidence this workload produces.

Null lift_holdout means one of three things, and they are not interchangeable:

1. The run had no holdout at all — check holdout_fraction on the summary. Not a failure; nothing was tested.
2. The antecedent was too rare in the holdout to measure. Not a failure either: neither confirmed nor refuted.
3. The consequent never appears in the holdout, so confidence_holdout = 0 and the lift is 0/0. This is a refutation — the rule made no correct prediction on data it had not seen. Distinguish it from case 2 by confidence_holdout IS NOT NULL.

So holds_up = false on a null lift means "not shown" in cases 1–2, but "refuted" in case 3. The summary's n_rules_refuted_absent_consequent counts case 3, and those rules stay in holdout_held_up_share's denominator.

Prefer, in order: holds_up (validated out of sample) → fwer_significant (assumption-free correction) → significant (understates the FDR).

Example queries

Placeholders <assoc_rules>, <assoc_items>, <assoc_rule_items>, <assoc_itemsets> are the data-kb names.

Top rules by lift (the shipped list):

SELECT antecedent_text, consequent_text, support, confidence, lift, q_value
FROM "<assoc_rules>" WHERE rank IS NOT NULL
ORDER BY rank LIMIT 20;

The rules you can state without caveat (family-wide correction):

SELECT antecedent_text, consequent_text, support, confidence, lift, p_value
FROM "<assoc_rules>" WHERE rank IS NOT NULL AND fwer_significant
ORDER BY lift DESC LIMIT 20;

All rules involving one item, either side (the bridge join):

SELECT r.antecedent_text, r.consequent_text, r.lift, ri.side
FROM "<assoc_rules>" r
JOIN "<assoc_rule_items>" ri ON ri.rule_id = r.rule_id
JOIN "<assoc_items>" i ON i.item_id = ri.item_id
WHERE i.item_name = 'guacamole' AND r.rank IS NOT NULL
ORDER BY r.lift DESC;

Rules whose consequent is a given item — high-lift attach candidates (baskets holding the antecedent tend to hold this item too; this is not evidence that the antecedent causes the purchase):

SELECT r.antecedent_text, r.confidence, r.lift
FROM "<assoc_rules>" r
JOIN "<assoc_rule_items>" ri
  ON ri.rule_id = r.rule_id AND ri.side = 'consequent'
JOIN "<assoc_items>" i ON i.item_id = ri.item_id
WHERE i.item_name = 'guacamole' AND r.lift > 3 AND r.rank IS NOT NULL
ORDER BY r.lift DESC;

Cross-category rules (bundle discovery across departments):

SELECT r.antecedent_text, r.consequent_text, r.lift,
       COUNT(DISTINCT i.category) AS n_categories
FROM "<assoc_rules>" r
JOIN "<assoc_rule_items>" ri ON ri.rule_id = r.rule_id
JOIN "<assoc_items>" i ON i.item_id = ri.item_id
WHERE r.rank IS NOT NULL
GROUP BY r.rule_id, r.antecedent_text, r.consequent_text, r.lift
HAVING COUNT(DISTINCT i.category) > 1
ORDER BY r.lift DESC LIMIT 20;

Most rule-connected items (cross-sell anchors):

SELECT i.item_name, COUNT(DISTINCT ri.rule_id) AS n_rules, MAX(r.lift) AS best_lift
FROM "<assoc_rule_items>" ri
JOIN "<assoc_items>" i ON i.item_id = ri.item_id
JOIN "<assoc_rules>" r ON r.rule_id = ri.rule_id AND r.rank IS NOT NULL
GROUP BY i.item_name ORDER BY n_rules DESC LIMIT 20;

Frequent bundles containing an item (the itemsets fact, singletons excluded):

SELECT itemset_text, length, support
FROM "<assoc_itemsets>"
WHERE list_contains(items, 'guacamole') AND length >= 2
ORDER BY support DESC LIMIT 20;

Opportunity audiences (rule → targeting list)

For a rule {A, B} → C, the opportunity audience is the entities holding all antecedent items but not the full consequent set — the entities the rule describes that do not yet hold the consequent. It is a pure set operation over *__assoc_holdings + the rule's items (from the bridge), so agents get it for free once the fact is published.

What this is and is not. It is a descriptive segment: these entities look like the ones the rule was measured on. It is not a predicted response list, and the rule's confidence is not an expected conversion rate — confidence is a historical co-occurrence share in baskets, not the effect of an intervention. Any yield number derived from it is an order-of-magnitude reference; measuring incremental effect needs an uplift experiment.

Grain caveat. On the event grain an entity owns several baskets, and holdings are deduplicated across all of them. So an entity that bought A in January and B in June satisfies "holds all antecedents" even though A and B never co-occurred in any basket. The audience is therefore a superset of the population the rule was measured on. On the long grain (one entity = one basket) the two coincide.

Semantics: the antecedent side is "holds every antecedent item" (HAVING COUNT(DISTINCT item_id) = <n antecedents>); the consequent side is "holds the full consequent set", and the audience excludes only those — a customer holding one but not all consequent items is kept. Use a NOT EXISTS anti-join, never NOT IN (a NULL in the subquery would silently zero the result).

"Who are the entities matching rule R's antecedents but missing the consequent?" — look the rule's item_ids up in the bridge for the rule_id, then inline them as quoted literals. query_sql takes {sql, limit} and has no bind-parameter channel, so a :name placeholder reaches DuckDB verbatim and fails with a parse error. Substitute the counts too — they are just the number of items on each side:

-- antecedents = ('guacamole', 'chips'), consequents = ('salsa')
WITH ant AS (
  SELECT entity_id FROM "<assoc_holdings>" WHERE item_id IN ('guacamole', 'chips')
  GROUP BY entity_id HAVING COUNT(DISTINCT item_id) = 2
),
has_full_consequent AS (
  SELECT entity_id FROM "<assoc_holdings>" WHERE item_id IN ('salsa')
  GROUP BY entity_id HAVING COUNT(DISTINCT item_id) = 1
)
SELECT entity_id FROM ant
WHERE NOT EXISTS (
  SELECT 1 FROM has_full_consequent h WHERE h.entity_id = ant.entity_id
);

Audience size + the consequent's base rate (descriptive — never a forecast):

SELECT
  (SELECT COUNT(DISTINCT entity_id) FROM "<assoc_holdings>") AS total_entities,
  (SELECT COUNT(*) FROM has_full_consequent)                 AS consequent_holders
-- (wrap with the two CTEs above)

Note total_entities counts only entities that survived cleaning and retained at least one post-exclusion item, so it is smaller than the source population.

The backend exposes the same audience as GET /api/experiments/{id}/association/rules/{rule_id}/audience (+ .csv for the complete list). It computes the identical set operation, but joins on the raw item names in memory rather than on the slugged item_id — so the two agree except where two distinct item names slug to the same item_id.