How do I do market-basket or product-affinity analysis with iDBQuery?
Ask iDBQuery 'Which products are most often bought together in the same order?' It writes the self-join over your order-line table, returns the top product pairs with their co-occurrence counts and lift, and cites each pairing back to the orders that contain both items.
Market-basket analysis (also called product-affinity or 'people who bought X also bought Y') finds which items tend to appear in the same transaction. It powers cross-sell, bundling, and store layout, and iDBQuery builds it without you writing the tricky self-join.
- You'd ask iDBQuery: 'Show the top 20 product pairs bought together in the last 90 days, with how often each pair co-occurs and its lift versus buying them independently.'
- It joins the `order_lines` table to itself, counts co-occurrences, computes support and lift, and returns a ranked table plus a chart.
- Every pair is cited to its source orders, so 'these two SKUs appear together in 1,340 orders' can be audited against real baskets.
You can then narrow it: 'For customers who bought the espresso machine, what did they buy next within 30 days?' iDBQuery keeps the thread and reworks the query. Because catalogue names may live in one table and transactions in another, or even a separate spreadsheet, the one live model joins them so results show readable product names, not codes. Save the affinities as a live report for merchandising, and use the Analyst agent to surface the highest-lift bundles you are not yet promoting.
Updated 2026-06-22