Activation & match rates

The diff

Send what changed, not what is

The same stable audience, the same nights, two modes. Night one is identical in both, because the list has to be loaded once. Every night after that is the argument.

• A full replace resets optimisation. Destinations treat a wholesale rewrite as a new population and re-enter their learning phase. You pay the exploration cost again every single night, and the campaign never gets past it.
• Quota is a shared, finite budget. Every audience on the brand draws from the same per-account allowance. Four hundred thousand rows spent at two in the morning are four hundred thousand you cannot spend on the urgent re-sync at four in the afternoon on the busiest day of the year.
• Replace is proportional to the audience; diff is proportional to the change. A stable four-hundred-thousand-member audience typically churns a few thousand a night. One of those numbers grows with your success and one does not.
• Replace destroys the why. After a wholesale rewrite, membership has no history — nobody entered and nobody left, the list simply is. The moment a marketer asks why this person is receiving this campaign, you have nothing to answer with.

So the sync engine writes a snapshot per run, diffs it against the previous run, and emits adds and removes.

-- One row per member per run. Not a replacing table: snapshots are history.
CREATE TABLE audience_snapshot (
  brand_id      LowCardinality(String),
  env           LowCardinality(String),
  audience_id   LowCardinality(String),
  snapshot_id   String,              -- "snap_2026_08_25"
  definition_v  UInt32,              -- the audience definition version compiled
  profile_id    String,
  entered_at    DateTime,            -- carried forward across runs, not recomputed
  reason        String,              -- "p_purchase_7d>=0.62 AND orders_lifetime>=1"
  captured_at   DateTime
) ENGINE = MergeTree
PARTITION BY (brand_id, env)
ORDER BY (brand_id, env, audience_id, snapshot_id, profile_id);

-- adds = in current, not in previous. removes = the mirror image.
SELECT
  coalesce(cur.profile_id, prev.profile_id) AS profile_id,
  if(prev.profile_id = '', 'add', 'remove')  AS op,
  cur.reason                                 AS reason,
  cur.definition_v                           AS definition_v
FROM (
  SELECT profile_id, reason, definition_v FROM audience_snapshot
  WHERE brand_id = {brand:String} AND env = {env:String}
    AND audience_id = {aud:String} AND snapshot_id = {cur:String}
) AS cur
FULL OUTER JOIN (
  SELECT profile_id FROM audience_snapshot
  WHERE brand_id = {brand:String} AND env = {env:String}
    AND audience_id = {aud:String} AND snapshot_id = {prev:String}
) AS prev USING (profile_id)
WHERE cur.profile_id = '' OR prev.profile_id = '';

The membership record carries the moment the person entered and the reason they qualified, and both survive re-entry: if someone leaves on the twelfth and returns on the nineteenth, the entry date is the nineteenth and the ledger still shows the exit. That is what turns “why is this person getting this email” from an archaeology project into a lookup.

The adapter

Every destination has its own shape, and none of it is your engine's business

The wrong shape here is a class per destination with the batching baked in — one syncer per platform, each with its own loop, its own retry, its own quiet divergence. Six months later one of them has a bug the others do not. Instead, every adapter declares a capability descriptor and one generic engine reads it.

interface DestinationCapabilities {
  id: "meta" | "google" | "klaviyo" | "webhook";
  acceptedKeys: ("email_sha256" | "phone_sha256" | "madid")[];
  maxBatchSize: number;
  supportsRemovals: boolean;
  rateLimitPerMinute: number;
  requiresConsent: (keyof ConsentState)[];
}

// The numbers below are placeholders. Read the real ones off each destination's
// current published limits and your own account tier — never hard-code a guess.
export const AD_PLATFORM: DestinationCapabilities = {
  id: "meta",
  acceptedKeys: ["email_sha256", "phone_sha256", "madid"],
  maxBatchSize: 10_000,             // batches in the low tens of thousands
  supportsRemovals: true,
  rateLimitPerMinute: 60,
  requiresConsent: ["marketing", "saleShare"],
};

export const EMAIL_PLATFORM: DestinationCapabilities = {
  id: "klaviyo",
  acceptedKeys: ["email_sha256"],   // email-only list semantics
  maxBatchSize: 1_000,              // smaller batches than the ad platforms
  supportsRemovals: true,
  rateLimitPerMinute: 600,
  requiresConsent: ["marketing"],
};

• Capabilities are data, not code. Adding a destination is a descriptor plus a transport function; the batching, pacing, checkpointing and dead-lettering are inherited.
• Accepted keys are a projection, not a validation. An email-only destination gets email only. A member with a phone hash and no address is not an error — they are a miss, counted against key coverage, which is a match-rate line item.
• Required consent is applied at compile, not at send. Post 4’s rule holds: consent is a join key. The descriptor tells the compiler which fields to join on, and different destinations legitimately require different ones.
• The rate limit paces the engine, not the retries. A token bucket sized from the descriptor means backoff is for genuine failures, not for a limit you knew about in advance.
• A destination that cannot remove gets a compensating path. Its removes route to a suppression list rather than being silently discarded — which is what a special-cased implementation does when nobody is watching.

import type { DestinationCapabilities } from "./capabilities.js";
import type { MemberRow, BatchResult } from "./types.js";
import { saveCheckpoint, lastCheckpoint } from "./checkpoint.js";
import { deadLetter } from "./deadletter.js";
import { suppress } from "./suppression.js";

export async function syncBatches(
  caps: DestinationCapabilities,
  syncId: string,
  rows: MemberRow[],
  op: "add" | "remove",
  send: (batch: MemberRow[]) => Promise<BatchResult>,
): Promise<void> {
  if (op === "remove" && !caps.supportsRemovals) return suppress(syncId, rows);

  const resume = (await lastCheckpoint(syncId, op))?.batchIndex ?? -1;
  for (let i = 0; i * caps.maxBatchSize < rows.length; i++) {
    if (i <= resume) continue;                       // already durably accepted
    const batch = rows.slice(i * caps.maxBatchSize, (i + 1) * caps.maxBatchSize);
    const res = await send(batch);                   // paced by rateLimitPerMinute
    for (const r of res.rejected) await deadLetter(syncId, r.profileId, r.reason);
    await saveCheckpoint({ syncId, op, batchIndex: i, accepted: res.accepted,
                           rejected: res.rejected.length, at: new Date().toISOString() });
  }
}

The checkpoint is written after a batch is accepted, so a crash between batch 41 and 42 resumes at 42 rather than replaying four hundred thousand rows. Idempotence is the destination’s job at the row level and the checkpoint’s job at the batch level; you need both.

Partial failure is the normal case, not the exception. A ten-thousand-row batch comes back accepted with thirty-seven rows rejected — a malformed hash, a key type the destination will not take today, a validation you will never see documented. There are two tempting responses and both are wrong. Failing the sync throws away 9,963 good rows over 37 bad ones. Dropping the 37 makes the dashboard green and the number wrong. You dead-letter each row with its reason, and you surface the dead-letter rate as a first-class metric next to the sync’s success flag.

export interface DeadLetterRow {
  syncId: string;          // "sync_7f31"
  brandId: string;         // "northwind"
  audienceId: string;      // "aud_high_intent_30d"
  destination: string;
  profileId: string;       // "prof_8812"
  keyType: "email_sha256" | "phone_sha256" | "madid";
  reason: string;          // destination-reported, verbatim, never rewritten
  batchIndex: number;
  at: string;
}

A sync that reports success while dropping rows is worse than one that fails, because it does not wake anyone up.

Match rate

The one number that tells you whether any of this worked

Match rate is keys submitted versus keys matched on the far side. It is the product metric, and it is also the series’ scoreboard: every earlier post shows up here as a line item. Read one night as a funnel.

A sloppy normaliser from post 2 costs you match rate. An over-merged component from post 2 costs you match rate — one profile, three people, one key submitted. A stale trait from post 3 puts the wrong people in the audience entirely. An unconsented row from post 4 is not a loss at all, it is a correct rejection, and confusing the two is how teams end up “fixing” their consent gate.

• Instrument per audience, per destination, per sync. A brand-level average hides the one audience that fell off a cliff.
• Alert on a relative drop against that pair’s own trailing baseline. A rate that is healthy for a cold prospecting audience is alarming for a repeat-purchaser one. An absolute floor either screams constantly or never fires.
• Baseline on a trailing median, not a mean. One catastrophic night should not lower the bar you compare tomorrow against.
• Report key coverage separately from match rate. One alert for both tells you nothing about which one moved.

import { client } from "../warehouse/client.js";

export async function checkMatchRate(
  brandId: string, audienceId: string, destination: string, observed: number,
): Promise<{ baseline: number; dropPct: number; alert: boolean }> {
  const rs = await client.query({
    query: `SELECT quantileExact(0.5)(match_rate) AS baseline
            FROM sync_audit
            WHERE brand_id = {brand:String} AND audience_id = {aud:String}
              AND destination = {dest:String}
              AND ts >= now() - INTERVAL 14 DAY`,
    query_params: { brand: brandId, aud: audienceId, dest: destination },
    format: "JSONEachRow",
  });
  const [{ baseline }] = await rs.json<{ baseline: number }>();
  const dropPct = baseline > 0 ? (baseline - observed) / baseline : 0;
  return { baseline, dropPct, alert: dropPct >= 0.15 };   // relative, never absolute
}

The silent killer

An audience that stopped refreshing three weeks ago

The access token expires on a Tuesday. The sync is rejected. The error is caught, logged at warning level, and the job exits successfully — because it did run, it just did not accomplish anything. The nightly dashboard is green. The audience sits frozen at whatever it was on the fourth while spend continues against a three-week-old list, and the only symptom is performance getting slowly, unremarkably worse. Somebody notices in a month, and by then the campaign’s optimisation has been trained against a stale population.

The fix is not a better try-catch. It is structural: every audience-and-destination pair carries a freshness target and a last-successful-sync timestamp, and the alert fires on the absence of a success rather than the presence of an error. A caught exception can be swallowed. A timestamp that fails to advance cannot be.

import { db } from "../config/store.js";

interface Binding {
  brandId: string; audienceId: string; destination: string;
  freshnessSlaMinutes: number;           // e.g. 1440 for a nightly audience
  lastSuccessfulSyncAt: string | null;   // advanced ONLY on a completed sync
}

export async function staleBindings(now = Date.now()): Promise<Binding[]> {
  const bindings = await db.collection<Binding>("destination_bindings").find({}).toArray();
  return bindings.filter((b) => {
    // Never-synced is stale, not absent. A null must page someone.
    const last = b.lastSuccessfulSyncAt ? Date.parse(b.lastSuccessfulSyncAt) : 0;
    return now - last > b.freshnessSlaMinutes * 60_000;
  });
}

• The timestamp advances on completion only. Not on start, not on “the job ran”. A partially failed sync does not advance it.
• Never-synced is stale, not exempt. The most common form of this bug is a binding that never worked once and was therefore never compared to anything.
• The freshness checker is a separate process. A checker that runs inside the sync job dies with the sync job.
• The alert names the binding, not the job. “This brand, this audience, this destination, twenty-one days stale” is actionable; “sync-engine warnings elevated” is not.

Monitor for the thing that should have happened, not just the thing that went wrong. The same table gives you the sync ledger — every profile, every destination, every sync id, appended as batches are accepted. It costs one table and a little write volume, and it is the thing that makes post 4’s deletion fan-out possible at all.

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

# The naive nightly sync: recompute and push everything.
pnpm activate sync aud_high_intent_30d --destination meta --mode replace

# The same audience, same night, diffed against the previous snapshot.
pnpm activate sync aud_high_intent_30d --destination meta --mode diff

# Where the members went.
pnpm activate matchrate aud_high_intent_30d --destination meta

Explain it back