pcLTV — Event & User Table Requirements

The Predicted Customer Lifetime Value (pcLTV) model estimates the future economic value a customer will generate over a defined forecast window (e.g. 3, 6, or 12 months). It relies on past transactions, behavioural patterns, and customer-level attributes.

---

Data model overview

To activate a pcLTV model, BPP requires:

1. A transactional event table — one row per transaction.
2. A user table — one row per user with optional demographic and behavioural features.

Field names are examples, not mandatory. Identity resolution handles user stitching across tables.

---

1. Transaction event table (required)

Each row represents one transaction. This is the core input for pcLTV.

Minimum required fields

Column name	Type	Description
event_timestamp	DATETIME	When the transaction occurred (UTC, mandatory)
user_id	STRING	A user identifier (mandatory)
monetary	FLOAT	Total value of the transaction (mandatory)
basket	JSON	List of products purchased (mandatory, see format below)
transaction_id	STRING	Not required for modelling, but strongly recommended for event reconciliation and value bidding — ensures transaction-level traceability

Basket JSON — required format

Field name	Type	Description
product_id	STRING	Unique identifier (e.g. SKU) (mandatory)
product_price	FLOAT	Price per unit (mandatory)
product_quantity	INTEGER	Quantity purchased (mandatory)
product_category	STRING	Product category (optional but highly recommended)
product_name	STRING	Name of the product (optional)
brand	STRING	Brand (optional)

[
  {
    "product_id": "14892189254007",
    "product_name": "Borraccia Porta Oggetti Z Box L - UNI",
    "brand": "ZEFAL",
    "product_category": "Borracce",
    "product_quantity": 1,
    "product_price": 7.95
  },
  {
    "product_id": "7091627262012",
    "product_name": "Pedali M540 Spd - UNI",
    "brand": "SHIMANO",
    "product_category": "Pedali e Tacchette",
    "product_quantity": 1,
    "product_price": 79.99
  }
]

---

2. User table (optional but recommended)

One row per user, with optional enrichment features that improve accuracy.

A. Demographic & static attributes

Example field	Type	Description
gender	STRING	User's gender
age	INTEGER	User's age
country	STRING	Country of residence
client_type	STRING	e.g. new vs returning customer
payment_system	STRING	Most-used payment method

B. Aggregated behavioural features

Behaviour-based aggregations can be computed with the Field Builder and stored in a dedicated user table. Recommended features:

Feature name	Description
last_country	Country from last known session
last_browser	Browser used in last session
avg_days_between_purchases	Average time between orders
conversion_time_cart_to_checkout	Time delta between cart and checkout events
pageviews_last_30_days	Activity volume metric
days_since_last_transaction	Recency metric
distinct_categories_bought	Purchase diversity metric
failed_checkouts	Drop-off behaviour indicator

These features capture purchase patterns, engagement levels, and friction points in the user journey.

---

Best practices

• Forecast horizon vs history depth — use the Rule of Three: to predict 3 months, provide ≥9 months of history; to predict 6 months, provide ≥18 months.
• User coverage — at least 20% of users should have 2+ transactions in the observation window.
• Minimum volume — at least 2,000 users with transactions.
• Data consistency — UTC timestamps, no duplicate transaction IDs, cleaned/standardized monetary.
• Feature quality — more descriptive, consistent, predictive features → better output.

---

Summary

Area	Must-have
Event table	1 row = 1 transaction, includes user ID + basket
Basket JSON	Product ID, quantity, price (category recommended)
User table	Optional, 1 row per user with demographic or behavioural data
Field Builder	Recommended for behaviour-based feature aggregation
Forecast window	Requires 3× historical data (e.g. 9 months to predict 3)
Transaction coverage	≥20% of users with at least 2 purchases
Minimum volume	2,000 users with transactions
transaction_id	Recommended for traceability and advanced use cases