Price and Demand Analytics

Pricing decisions rest on two estimates: what a product sells for in a given situation, and how many units it sells at a given price. Both depend on context such as the weekday, the shelf placement and the season, and the history that answers them is usually already in a sales table. Aito v2 estimates numeric fields directly from that table with _estimate, explains the estimate, and relates outcomes to their conditions with _relate.

The queries below run against the live v2 sandbox, whose price_history table has one row per product per day (180 days for each of 42 products) with the sale_price, the units_sold and the conditions of that day. The examples follow one product: Juhla Mokka coffee 500g (6411300000494). Figures in the responses are rounded.

Estimate the price in a situation

{
  "from": "price_history",
  "where": {
    "product_id": "6411300000494",
    "day_of_week": "Friday",
    "promotional_placement": "normal"
  },
  "estimate": "sale_price"
}

{ "kind": "estimate", "data": { "value": 3.837 } }

With "promotional_placement": "featured" the estimate is 3.150: on its featured days this product has sold at a lower price. The default estimator (knn) finds the most similar past days and adjusts their values to the conditions you asked about. It needs plain equality conditions.

In SQL:

SELECT * FROM estimate('price_history', 'sale_price',
  given => 'product_id = ''6411300000494'' AND day_of_week = ''Friday'' AND promotional_placement = ''normal''')

Estimate demand

The same call estimates units_sold. On a Friday, the product sells an estimated 122.8 units with normal placement and 286.6 units when featured:

{
  "from": "price_history",
  "where": {
    "product_id": "6411300000494",
    "day_of_week": "Friday",
    "promotional_placement": "featured"
  },
  "estimate": "units_sold"
}

{ "kind": "estimate", "data": { "value": 286.623 } }

SELECT * FROM estimate('price_history', 'units_sold',
  given => 'product_id = ''6411300000494'' AND day_of_week = ''Friday'' AND promotional_placement = ''featured''')

Compare the estimates with what the history holds before relying on them. An _aggregate over the same product gives a mean of 143.4 units over 174 normal days and 388.5 units over only 6 featured days:

{
  "from": "price_history",
  "where": { "product_id": "6411300000494", "promotional_placement": "featured" },
  "aggregate": ["sale_price.$mean", "units_sold.$mean"]
}

Six featured days are thin evidence. The estimate for a featured Friday (286.6) sits between the featured mean and the normal mean; with so few featured days, look at the neighbouring days in why (below) before relying on it.

Trace a demand curve

Put the price in the evidence and vary it. With normal placement, the estimated units sold are 290.9 at a price of 3.0, 174.2 at 3.5 and 121.5 at 3.9:

{
  "from": "price_history",
  "where": {
    "product_id": "6411300000494",
    "promotional_placement": "normal",
    "sale_price": 3.5
  },
  "estimate": "units_sold"
}

Multiplying each price by its estimated units gives the revenue curve, and subtracting purchase_cost gives the margin; both are client-side arithmetic on the estimates.

Explain an estimate

model: "regression" estimates with a log-linear regression and returns the estimate as a sum of terms, which is easier to read than a list of neighbours:

{
  "from": "price_history",
  "where": {
    "product_id": "6411300000494",
    "day_of_week": "Friday",
    "promotional_placement": "featured"
  },
  "estimate": "units_sold",
  "model": "regression",
  "select": ["value", "why"]
}

The estimate is 295.7, and why is an exponent of a sum: a mean of 4.945, plus 0.848 for promotional_placement: "featured", minus 0.042 for Friday and minus 0.062 for the product. The placement term is by far the largest, so the placement, not the weekday, drives the difference. The default knn estimate accepts "select": ["value", "why"] too, and its why lists the neighbouring days it used, each with its adjustments.

What goes with a big sales day

_relate turns the question around: given an outcome, which conditions are over-represented? For the days on which the coffee sold 250 units or more:

{
  "from": "price_history",
  "where": { "product_id": "6411300000494", "units_sold": { "$gte": 250 } },
  "relate": ["promotional_placement", "is_weekend", "day_of_week"],
  "select": ["related", "lift", "n"],
  "limit": 5
}

{ "total": 5, "hits": [
  { "related": { "promotional_placement": "normal" }, "lift": 0.88, "n": 7560 },
  { "related": { "promotional_placement": "featured" }, "lift": 2.58, "n": 7560 },
  { "related": { "day_of_week": "Thursday" }, "lift": 0.78, "n": 7560 },
  { "related": { "day_of_week": "Friday" }, "lift": 0.78, "n": 7560 },
  { "related": { "is_weekend": true }, "lift": 1.08, "n": 7560 } ] }

Featured placement is 2.6 times as common on these 24 days as in the table as a whole, and weekends are 1.08 times as common. n is the size of the whole table, against which the lifts are computed.

SELECT * FROM relate('price_history', fields => 'promotional_placement, is_weekend, day_of_week',
  to => 'product_id = ''6411300000494'' AND units_sold >= 250', k => 5)

Why Aito for this

Estimates from the history you have. No model to fit per product; the estimate is computed from the matching rows at query time.
Explained in the terms of the data. The regression terms and the neighbouring days show where a number came from.
Checked against the raw rows. _aggregate on the same where shows how much history an estimate rests on, so a thin estimate can be flagged.

← All v2 use cases