Product Analytics: Conversion, Segments and Substitutes

A product page in a merchandising tool answers a handful of questions: how often the product is bought when shown, how that compares with similar products, who buys it, what it is bought with, and how demand moves over time. Usually each answer is a separate report on a separate pipeline. In Aito v2 each is one query over the raw event tables, so the page can be built from the current data on request.

The queries below run against the live v2 sandbox, for one product: Pirkka lactose-free semi-skimmed milk drink (6410405216120). impressions records each time a product was shown and whether it was bought; visits records each shopping trip with its purchases and a user link. Figures in the responses are rounded.

Conversion rate

_aggregate computes statistics over the rows that match the where. On a Boolean column, $sum counts the true values and $mean is the rate:

{
  "from": "impressions",
  "where": { "product": "6410405216120" },
  "aggregate": ["purchase.$sum", "purchase.$mean"]
}

{ "kind": "aggregate", "data": {
  "purchase.$sum": 97, "purchase.$sum.samples": 2195,
  "purchase.$mean": 0.0442, "purchase.$mean.standardError": 0.0044 } }

The product was shown 2,195 times and bought 97 times, a conversion rate of 4.4%. Without the where, the same query gives the rate across all products: 5.2%.

In SQL, count the rows:

SELECT count(*) FROM impressions WHERE product = '6410405216120' AND purchase = true

Compare it with its category

Open the products as candidates with get and give each one its own conversion rate with a per-candidate aggregate:

{
  "from": "impressions",
  "where": { "product.category": "104" },
  "get": "product",
  "orderBy": { "$mean": { "$context": "purchase" } },
  "select": ["name", "$f", { "conversion": { "$mean": { "$context": "purchase" } } }],
  "limit": 5
}

{ "total": 42, "hits": [
  { "name": "Pirkka Finnish nonfat milk 1l", "$f": 2221, "conversion": 0.0504 },
  { "name": "Valio semi-skimmed milk 1l", "$f": 2106, "conversion": 0.0461 },
  { "name": "Pirkka Finnish semi-skimmed milk 1l", "$f": 2236, "conversion": 0.0447 },
  { "name": "Pirkka lactose-free semi-skimmed milk drink 1l", "$f": 2195, "conversion": 0.0442 },
  { "name": "Valio eila™ Lactose-free semi-skimmed milk drink 1l", "$f": 2115, "conversion": 0.0312 } ] }

Category 104 has five products. total still counts every product, because get opens all values of the link; the ones outside the category have $f of 0 and sort after these five. Within the milks, the lactose-free drink is fourth of five.

Who buys it

Relate the buyers' user tags. user.tags is read through the visits.user link:

{
  "from": "visits",
  "where": { "purchases": { "$has": "6410405216120" } },
  "relate": ["user.tags"],
  "select": ["related", "lift", "n"]
}

{ "total": 4, "hits": [
  { "related": { "user.tags": { "$has": "male" } }, "lift": 1.03, "n": 733 },
  { "related": { "user.tags": { "$has": "female" } }, "lift": 0.95, "n": 733 },
  { "related": { "user.tags": { "$has": "club-member" } }, "lift": 0.97, "n": 733 },
  { "related": { "user.tags": { "$has": "young" } }, "lift": 0.99, "n": 733 } ] }

Every lift is close to 1: no segment buys this product markedly more or less than average. That is a useful answer too. For a product with a segment signal, such as HK Amerikan bacon (6409100046286), the same query gives young a lift of 1.32.

SELECT * FROM relate('visits', fields => 'user.tags',
  to => 'purchases = ''6410405216120''', k => 5)

What it is bought with, and instead of

Relate the other products in the same baskets:

{
  "from": "visits",
  "where": { "purchases": { "$has": "6410405216120" } },
  "relate": ["purchases"],
  "select": ["related", "lift"],
  "limit": 5
}

{ "total": 5, "hits": [
  { "related": { "purchases": { "$has": "6410405216120" } }, "lift": 7.41 },
  { "related": { "purchases": { "$has": "6408430000128" } }, "lift": 0.43 },
  { "related": { "purchases": { "$has": "6415600501811" } }, "lift": 0.56 },
  { "related": { "purchases": { "$has": "6411401028373" } }, "lift": 1.35 },
  { "related": { "purchases": { "$has": "6410405082657" } }, "lift": 0.57 } ] }

The first hit is the product itself, which is in every basket that matches the condition. The next is the interesting one: Valio semi-skimmed milk (6408430000128) appears in these baskets at 0.43 times its usual rate, and Pirkka semi-skimmed milk (6410405082657) at 0.57. Shoppers who buy the lactose-free drink tend not to buy regular milk on the same trip: they are substitutes. Tyrkisk Peber chocolate (6411401028373, lift 1.35) is bought with it more often than chance. The hits are not ordered by lift, so sort by lift on the client if that is the order you want.

Which properties drive purchase

relate with $props asks about specific propositions rather than every value of a field. Here: across all impressions, do the product's category and its lactose-free tag raise or lower the chance of a purchase?

{
  "from": "impressions",
  "where": { "purchase": true },
  "relate": { "$props": { "product.category": "104", "product.tags": "lactose-free" } },
  "select": ["related", "lift", "n"]
}

Both lifts are below 1 (0.91 for the category, 0.87 for the tag): milk, and lactose-free milk in particular, converts slightly less often than the average product in this shop.

Demand over time

get on a column of the queried table counts rows per value. Here, the number of visits per week that included the product:

{
  "from": "visits",
  "where": { "purchases": { "$has": "6410405216120" } },
  "get": "week",
  "select": ["$value", "$f"],
  "limit": 20
}

The ten weeks in the data give 6, 12, 13, 10, 12, 4, 12, 11, 11 and 6 visits. Week 5 stands out: across all products there were 80 visits that week, a normal week, but only 4 of them included this product. The SQL form is a GROUP BY:

SELECT week, count(*) AS visits FROM visits
WHERE purchases = '6410405216120' GROUP BY week ORDER BY week

Why Aito for this

  • One query per question, on raw events. Conversion, segments, substitutes and trends come from impressions and visits as they are, with no aggregation tables to maintain.
  • Lift, not only counts. relate reports how much more or less often something happens than chance, which is what separates a substitute from a merely popular product.
  • One request for the whole page. The queries are independent, so a product page can send them together with /api/v2/_batch.

Related: Behavioural analytics · Market basket analysis · Price and demand · Query Reference

← All v2 use cases