PetNord — Aito Predictive E-commerce Tour

A guided, runnable tour of the predictive capabilities behind the PetNord demo: predictive re-ranking, cross-sell discovery, churn prediction, product filling and price intelligence. Every query below is a verified shape running live against the aito-ecommerce-demo data.

Database: aito-ecommerce-demo

Start here

What this workbook is

PetNord — Aito Predictive E-commerce Tour

PetNord is a fictional pet-supplies retailer. This workbook walks through the Aito queries that power its demo app — each one is runnable: hit Run on any query widget, then edit the query and run again to explore.

The data is a single denormalised e-commerce dataset:

Table Rows about
products catalog (sku, brand, category, pet_type, price)
customers / customer_months customer profiles + monthly snapshots
orders / order_lines transactions (line-level, with denormalised customer context)
monthly_sales / price_history demand & pricing time series
reviews review text + sentiment + churn label

No model training, no feature engineering — Aito predicts directly from the rows already in the database.

Tour stops: Explore the data → Predictive re-ranking → Bought Together → Churn prediction → Product Filling → Price Intelligence → Winback (conversion KPI).

Instance activity

Real-time metrics: status, lastRequest, requests5min, requests1hr, memory, disk.

1 · Explore the data

Plain queries to get a feel for the tables. Edit the `from` and `where` and re-run.

Browse the product catalog

Interactive query — results displayed as table.

Customers in one region

Interactive query — results displayed as table.

Average order value across all orders

Interactive aggregate — results displayed as table.

2 · Predictive re-ranking (Smart Search)

Rank products by P(customer-segment | line containing this product). Products a segment historically buys float to the top.

How it works

A plain text search for food returns hundreds of matches in no useful order. Aito re-ranks them for this customer using _recommend:

  • where filters the order lines (products whose name matches food, for a small-pet household)
  • recommend: "product_sku" asks Aito to rank candidate SKUs
  • goal: { customer_segment: "dog_owner" } ranks by P(dog_owner | this product) — products dog owners actually buy rise to the top
  • basedOn curates which product features feed the prior, trimming ~13% off latency

Change customer_segment to cat_owner and re-run — the ranking flips to cat products.

Why segment, not a purchase/click KPI? order_lines records only purchases — there's no negative class to learn a P(purchase) from, and its one outcome field (returned) is uniform at ~3%, so a goal: { returned: false } collapses to baseline. Here, segment affinity is the purchase-propensity signal: by Bayes, P(dog_owner | product) is highest for the products dog owners disproportionately buy. For a recommendation conditioned on a true conversion KPI, see the Winback section below — it has a real responded outcome to rank on.

Re-rank 'food' results for a dog-owner household

Interactive recommend — results displayed as table.

3 · Bought Together (cross-sell)

Statistical co-occurrence between order contents, surfaced with `_relate`.

How it works

orders.line_categories is a denormalised text column listing each order's lines as <pet>_<category> tokens. _relate returns the lift of token co-occurrence within an order:

  • lift > 1 → bought together more often than chance (cross-sell)
  • lift < 1 → rarely bought together (protective / substitute)

Read the related.line_categories.$has column against lift. Swap dog_dryfood for cat_wetfood to explore a different basket.

What gets bought with dog dry-food?

Interactive relate — results displayed as table.

4 · Churn prediction

Predict whether a customer will churn in the next 3 months — and measure how accurate the prediction is.

How it works

customer_months holds a monthly snapshot per customer, labelled churned_in_3_months (true iff they placed no order in the following 3 months). _predict returns P(true) and P(false) for a customer described by where — no model training needed.

The profile below describes a small-dog owner in Helsinki who just left a negative, low-rated review — a classic at-risk signal. Edit the fields and re-run to see the probability move.

The accuracy widget below holds out a random sample, hides the label, predicts it from the same features, and compares against ground truth — Aito's built-in cross-validation.

Will this customer churn?

Interactive predict — results displayed as table.

How accurate is the churn prediction?

ML evaluation for predict queries.

5 · Product Filling

Predict missing product attributes from the product name and brand.

How it works

A new product arrives with only a name and brand. Aito fills the rest of the catalog record by predicting each field from the products it already knows. Here we predict category from the name token food + brand — change predict to pet_type, dietary, or tax_class to fill a different field, or edit the $match name to describe a different product.

Predict a product's category from its name

Interactive predict — results displayed as table.

6 · Price Intelligence

Server-side price statistics per SKU with `_aggregate`.

How it works

_aggregate computes mean / min / max over price_history for one SKU, server-side. $mean also returns variance, standard deviation and standard error in the same response — useful for spotting price outliers and discount depth. Swap the product_sku to inspect a different product's price band.

Price band for one SKU

Interactive aggregate — results displayed as table.

7 · Winback — recommend by conversion probability

The textbook recommendation shape: rank products by the probability the customer actually converts.

How it works

Unlike order_lines, the winback_campaigns table records a real outcome: each row is a campaign send with responded: true|false. That gives Aito a conversion KPI to optimise directly.

  • recommend: "product_sku" over winback_campaigns
  • goal: { responded: true } ranks candidates by P(responded = true | this product, this customer's context)
  • where pins the customer's profile (segment, lifestyle, recency since last order)

The app's Winback view then multiplies this response probability by a predicted order_value_eur to estimate €-recoverable revenue per send. The _predict widget below reads the absolute response probability for one product context (_recommend's $p is normalised against goal-positives, so use _predict when you need the calibrated number).

Best winback products for a lapsed premium dog-owner

Interactive recommend — results displayed as table.

Calibrated response probability for one product context

Interactive predict — results displayed as table.