Profiles & propensity

The bug, first

The offline number goes up when the model gets worse

Here is the whole post in one control. You want to predict “buys in the next seven days”. The label window opens on the first of September. The question is only ever this: at what moment did you read the features?

That is the failure the rest of this post is arranged around. Everything before it exists so the fix is even possible, and everything after it exists so the score reaches an audience without going stale.

Traits

A profile is a query, not a document

Post 2 left you with one canonical profile id per connected component. The traits layer does not store facts about that profile; it derives them from the event log on a schedule, writes the result into the warehouse, and stamps a version. Nothing mutates. The document version of this loses on four counts.

• Concurrent writers corrupt silently. The order consumer and the session consumer both touch the same profile; one read-modify-write clobbers the other and no error is raised anywhere.
• A document has no history. “What was days-since-last-order on the twelfth of March” is unanswerable — which, as the interactive above shows, makes correct model training impossible.
• Replays double-count. A consumer restart replays an order, and an incrementing lifetime counter is now permanently wrong with no way to detect it.
• Recomputation is the fix for everything. A derived trait table can be rebuilt from events after a bug. A mutated document cannot be un-mutated.

Sessionization comes first, because a session is the unit almost every behavioural trait counts. The rule is thirty minutes of inactivity, computed with a window function over each profile’s ordered event stream rather than trusting the session id the client supplied.

-- A new session starts when the gap since the previous event exceeds 30 minutes.
-- Client-supplied session ids are recorded but never trusted; ts is the server stamp.
CREATE OR REPLACE VIEW event_sessions AS
SELECT
    brand_id,
    env,
    profile_id,
    ts,
    name,
    props,
    -- running count of gaps = a stable per-profile session ordinal
    sum(is_new_session) OVER (
        PARTITION BY brand_id, env, profile_id
        ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session_seq
FROM (
    SELECT
        *,
        if(
            neighbor(ts, -1) IS NULL
            OR profile_id != neighbor(profile_id, -1)
            OR dateDiff('minute', neighbor(ts, -1), ts) > 30,
            1, 0
        ) AS is_new_session
    FROM events
    ORDER BY brand_id, env, profile_id, ts
);

The roll-up is then a plain aggregate over that view plus orders. It runs on a cadence, computes every trait from scratch for the profiles that had activity, and appends.

CREATE TABLE IF NOT EXISTS profile_traits (
    brand_id      LowCardinality(String),
    env           LowCardinality(String),
    profile_id    String,
    computed_at   DateTime,          -- the moment these values were valid
    first_seen_at DateTime,
    sessions_30d  UInt32,
    orders_lifetime UInt32,
    revenue_lifetime Decimal(18, 2),
    aov           Decimal(18, 2),
    days_since_last_order Nullable(UInt16),
    first_touch_channel LowCardinality(String),
    last_touch_channel  LowCardinality(String),
    p_purchase_7d Nullable(Float32),
    scored_at     Nullable(DateTime),
    model_version Nullable(String),
    version       UInt64             -- monotonic; the highest wins at merge time
) ENGINE = ReplacingMergeTree(version)
PARTITION BY brand_id
ORDER BY (brand_id, env, profile_id);

The feature store

One definition, two paths

Training reads history in batch. Scoring reads one profile in milliseconds. Those are genuinely different queries — and the moment you write them as two hand-maintained SQL strings, they start to drift. Someone fixes a null-handling bug in the online path, or changes a lookback from thirty days to twenty-eight, and only one side gets it. Nothing errors. The model just quietly gets worse over a quarter.

• A feature is an object, not a string. It owns a name, a type, and one expression; the offline and online queries are both compiled from it.
• The compiler is the only place SQL is written. No feature-specific SQL lives anywhere else, so there is nowhere for a divergent edit to hide.
• Parity is a test, not a convention. Sample profiles, run both paths, assert equality.
• Drift is otherwise invisible. There is no exception, no alarm and no log line — only a slow decline in a number nobody watches daily.

export interface FeatureDef {
  readonly name: string;
  readonly type: "float" | "int" | "string";
  /** Warehouse expression over the aliased trait row `t`. */
  readonly expr: string;
}

export const FEATURES: readonly FeatureDef[] = [
  { name: "sessions_30d",  type: "int",    expr: "toInt32(t.sessions_30d)" },
  { name: "revenue_ltv",   type: "float",  expr: "toFloat64(t.revenue_lifetime)" },
  { name: "aov",           type: "float",  expr: "toFloat64(t.aov)" },
  { name: "recency_days",  type: "int",    expr: "toInt32(coalesce(t.days_since_last_order, 999))" },
  { name: "last_channel",  type: "string", expr: "toString(t.last_touch_channel)" },
] as const;

const select = (): string => FEATURES.map((f) => `${f.expr} AS ${f.name}`).join(",\n  ");

/** Offline: every profile, as of a point in time. */
export const offlineQuery = (): string =>
  `SELECT t.profile_id, t.computed_at,\n  ${select()}\nFROM profile_traits_history AS t\nWHERE t.brand_id = {brandId:String} AND t.env = {env:String}`;

/** Online: one profile, current values. */
export const onlineQuery = (): string =>
  `SELECT t.profile_id,\n  ${select()}\nFROM profile_traits AS t FINAL\nWHERE t.brand_id = {brandId:String} AND t.env = {env:String} AND t.profile_id = {profileId:String}`;

import { describe, expect, it } from "vitest";
import { readOffline, readOnline } from "./read.js";
import { FEATURES } from "./registry.js";

describe("offline/online feature parity", () => {
  it("returns identical values for a sampled set of profiles", async () => {
    const scope = { brandId: "northwind", env: "prod" };
    const sample = await readOffline(scope, { asOf: "now", limit: 500 });

    for (const row of sample) {
      const live = await readOnline(scope, row.profile_id);
      for (const f of FEATURES) {
        expect(live[f.name], `${f.name} drifted for ${row.profile_id}`).toEqual(row[f.name]);
      }
    }
  });
});

That test is the entire discipline in one file. It fails loudly the day someone edits one path, which is the only moment the failure is cheap to fix.

Point-in-time

Compute the feature as of the moment you would have had to

Now the mechanics behind the interactive at the top. You take a label window and build the training set from the trait table as it is today. Lifetime revenue already contains the purchase in the label window. Recency is measured from it. You have handed the model the answer inside the question.

The offline score comes back beautiful. Everyone is delighted. Online lift is zero, because at scoring time the model sees a profile before the purchase, and every feature it learned to lean on is in a completely different regime. The model learned “recently bought” and you asked it “will buy”.

• The leak is in the features, not the label. The label window is correct; the features are read from after it started.
• Every feature row carries the timestamp it was valid at. That is what the computed-at column is for, and why traits are appended rather than updated.
• Training joins on that timestamp, never on today. Each label row picks the most recent trait row at or before its own boundary.
• An as-of join is the right tool. The warehouse does this natively and efficiently; the hand-rolled subquery version is slower and easier to get subtly wrong.

-- One row per (profile, as_of). Features are strictly <= as_of; the label is strictly after it.
WITH labels AS (
    SELECT
        brand_id, env, profile_id,
        toDateTime('2026-09-01 00:00:00') AS as_of,
        countIf(name = 'purchase'
                AND ts >  toDateTime('2026-09-01 00:00:00')
                AND ts <= toDateTime('2026-09-08 00:00:00')) > 0 AS y
    FROM events
    WHERE brand_id = {brandId:String} AND env = {env:String}
    GROUP BY brand_id, env, profile_id
)
SELECT
    l.profile_id, l.as_of, l.y,
    t.sessions_30d, t.revenue_lifetime, t.aov,
    coalesce(t.days_since_last_order, 999) AS recency_days
FROM labels AS l
ASOF LEFT JOIN profile_traits_history AS t
  ON  l.brand_id   = t.brand_id
  AND l.env        = t.env
  AND l.profile_id = t.profile_id
  AND t.computed_at <= l.as_of;   -- the inequality: latest snapshot at or before as_of

The history table is the same roll-up written without replacement — one row per profile per run, kept for a retention window. It costs storage. It is the only thing that makes a correct training set possible, so it is not optional.

There is a second, subtler leak in the same neighbourhood, and the as-of join does not fix it. If your feature window and your label window overlap even by a day, you have reintroduced the same problem in miniature, and the offline metric moves just enough to look like a real improvement. Worse is survivorship in the sampled population: if you build the training set from profiles that exist in the trait table because they converted, your negatives are not a sample of non-buyers — they are a sample of people who nearly bought. The fix for both is the same as the fix for the first: define the population and the feature cutoff from the boundary alone, and let never-converted profiles into the negative class.

Scoring

A score is only useful if the audience can see it

The model output is not a file in a bucket. It lands back beside the traits, with the time it was scored and the model version next to it, so a marketer can build an audience on it and an investigation six weeks later can ask which model produced a given membership.

• Write back as a version bump. Same key, higher version, replacement at merge time.
• The model version is not decoration. Without it a bad audience cannot be traced to a bad model, and a rollback is guesswork.
• The scored-at timestamp gates staleness. Audiences filter on it; a score nobody refreshed is a score nobody should target on.
• Thresholds live in the audience, not the model. The model emits a probability; the business decides where the line is.

import type { ClickHouseClient } from "@clickhouse/client";

export interface Score { profileId: string; p: number; }

export async function writeScores(
  ch: ClickHouseClient,
  scope: { brandId: string; env: string },
  modelVersion: string,
  scores: readonly Score[],
): Promise<void> {
  const version = Date.now();
  await ch.insert({
    table: "profile_traits",
    format: "JSONEachRow",
    // Only the key + the scored columns; the highest version per key wins at merge time.
    values: scores.map((s) => ({
      brand_id: scope.brandId, env: scope.env, profile_id: s.profileId,
      p_purchase_7d: s.p, scored_at: new Date().toISOString(), model_version: modelVersion,
      version,
    })),
  });
}

SELECT profile_id
FROM profile_traits FINAL
WHERE brand_id = {brandId:String} AND env = {env:String}
  AND p_purchase_7d >= 0.62
  AND scored_at > now() - INTERVAL 2 DAY   -- never target on a stale score
  AND sessions_30d >= 1;

git clone https://github.com/xaviramirezcom/open-activation-platform
cd open-activation-platform
git checkout v3-profiles
pnpm install
pnpm activate up            # streaming + warehouse + config store, via docker compose
pnpm activate seed          # synthetic multi-brand traffic, orders and spend

pnpm activate traits --brand northwind --env prod       # sessionize + roll up + history

pnpm activate train --brand northwind --point-in-time off   # features read as of "today"
pnpm activate train --brand northwind --point-in-time on    # joined at the label boundary

pnpm activate evaluate --brand northwind --holdout 0.2      # offline score + holdout lift, both

Explain it back