Skip to main content

Omnichannel Retailer Example

In an Omnichannel Retail setup, BPP integrates in-store sales, e-commerce transactions, loyalty CRM data, and web/app interactions to power:

  • Action Prediction: likelihood to purchase online or offline, likelihood to churn or return.
  • pcLTV: predicted lifetime value combining e-commerce and store channels.
  • RFM Segmentation: recency, frequency, and monetary across all channels.
  • Interest Analysis: product affinity based on web/app browsing and store categories.
  • Signals & Audiences: activation of loyalty IDs or hashed PII in ad platforms.

IDs & identity resolution​

Omnichannel requires merging offline + online IDs. BPP reconciles them under a Bytek ID.

  • Loyalty card ID (offline anchor).
  • Hashed email (SHA-256 normalized).
  • Hashed phone (SHA-256 E.164).
  • E-commerce user ID (site login).
  • First-party cookie / app instance ID (online behaviour).
  • POS transaction ID (for reconciliation).

See Identifiers & Hashing for normalization rules.

Core tables​

1. User table — Loyalty Customers​

CREATE TABLE my_dataset.loyalty_customers (
  loyalty_id        STRING,   -- loyalty card ID
  hem               STRING,   -- hashed email
  hphone            STRING,   -- hashed phone
  ecommerce_user_id STRING,   -- e-commerce platform ID
  fp_cookie_id      STRING,   -- GA4 user_pseudo_id or app instance
  first_name        STRING,   -- optional cleartext (hashed if flagged)
  last_name         STRING,
  gender            STRING,
  age               INT64,
  signup_date       DATETIME,
  preferred_store   STRING,
  consent_marketing BOOL,
  consent_timestamp DATETIME,
  dominant_device_class STRING, -- online behaviour
  dominant_browser  STRING,
  last_purchase_date DATETIME,
  total_spent_12m   FLOAT64,
  orders_count_12m  INT64,
  store_visits_12m  INT64,
  online_sessions_90d INT64,
  created_at        DATETIME,
  updated_at        DATETIME
);

2. Event table — E-commerce Orders​

CREATE TABLE my_dataset.orders_completed (
  order_id           STRING,
  event_timestamp    DATETIME, -- order completion time (UTC)
  ecommerce_user_id  STRING,
  hem                STRING,
  loyalty_id         STRING,
  monetary           FLOAT64,  -- total order value
  currency           STRING,
  basket             JSON,     -- JSON array of SKUs/categories
  payment_method     STRING,
  shipping_country   STRING,
  transaction_status STRING    -- completed, refunded
);

3. Event table — Store POS Transactions​

CREATE TABLE my_dataset.pos_transactions (
  transaction_id     STRING,
  event_timestamp    DATETIME,
  loyalty_id         STRING,   -- main link to online IDs
  store_id           STRING,
  monetary           FLOAT64,
  currency           STRING,
  basket             JSON,     -- products purchased
  payment_method     STRING,
  transaction_status STRING,   -- completed, returned
  cashier_id         STRING,
  created_at         DATETIME
);

4. Event table — Web / App Events​

CREATE TABLE my_dataset.web_events (
  event_id          STRING,
  event_timestamp   DATETIME,
  fp_cookie_id      STRING,
  ecommerce_user_id STRING,
  hem               STRING,
  event_type        STRING,   -- pageview, add_to_cart, checkout, store_locator
  page_url          STRING,
  utm_source        STRING,
  utm_medium        STRING,
  utm_campaign      STRING,
  gclid             STRING,
  gbraid            STRING,
  wbraid            STRING,
  device_class      STRING,
  browser           STRING,
  country           STRING,
  city              STRING,
  created_at        DATETIME
);

5. Event table — Returns & Refunds (optional)​

CREATE TABLE my_dataset.refunds (
  refund_id       STRING,
  event_timestamp DATETIME,
  order_id        STRING,
  loyalty_id      STRING,
  hem             STRING,
  refund_value    FLOAT64,
  currency        STRING,
  refund_reason   STRING
);

Basket JSON example​

[
  {
    "product_id": "SKU_12345",
    "product_name": "Denim Jeans",
    "brand": "Levi's",
    "product_category": "Apparel||Bottoms||Jeans",
    "product_quantity": 1,
    "product_price": 59.99
  },
  {
    "product_id": "SKU_67890",
    "product_name": "Sneakers",
    "brand": "Nike",
    "product_category": "Footwear||Sneakers",
    "product_quantity": 1,
    "product_price": 89.99
  }
]

Do & don't summary​

✅ Do❌ Don't
Keep loyalty_id as anchor across offline + onlineRely on only email for omnichannel matching
Use immutable order facts or status historyOverwrite order_status on the same row
Capture absolute URLs + click IDs in web eventsTruncate URLs or strip attribution
Store POS transactions as events with a timestampKeep only aggregated monthly sales
Maintain basket detail (SKU, qty, price, category)Store basket as an opaque blob
Hash emails/phones consistently (SHA-256)Hash without normalization

How BPP uses these tables​

  • Action Prediction — Target: purchase within a horizon (online or in-store). Features: demographics, loyalty engagement, online sessions, store visits, basket diversity.
  • pcLTV — Transactions: orders_completed + pos_transactions. Value: monetary per transaction. Categories: basket.product_category.
  • RFM — Recency = last transaction (online or store). Frequency = number of purchases in window. Monetary = total spent in window.
  • Interest — Product categories from baskets (product_category); IAB/Custom interest via page_url classification on web events.

Why this design works​

  • Loyalty ID bridges offline and online.
  • Transactions modelled as immutable events guarantee accurate pcLTV & RFM.
  • Basket detail supports Interest modelling & value bidding.
  • Unified behaviour (web + store) feeds robust Action Prediction models.
Last updated on