We asked for a review the moment someone paid, which is the worst moment

CogniPrep asks customers for a public review. It is a full screen prompt on the dashboard, and it is asked once per account, ever. If you dismiss it, it never comes back.

That one property determines everything else about the feature. A recurring ask can afford to be badly timed, because there is another one next month. A one-shot ask spent at the wrong moment is spent for good, and the only thing a badly timed ask reliably produces is a reflexive dismissal.

Our first version measured lifetime activity. Play a couple of games on the free tier, buy something, and the very next page load handed you a full screen review request. Before you had opened the thing you had just paid for.

Nobody has an opinion about a product they have not used yet. The ask was being spent at the exact moment it was worth least.

Move the window, not the bar

The fix is one parameter: the activity that counts is activity since the purchase, not since signup.

lifetime:      [ signup ......... purchase ......... now ]
counted:                          [ ................ now ]

Requiring the same small amount of practice again, after the payment, moves the ask to the first point where the customer has actually seen what they bought. The bar did not change. The window did.

Accounts that paid before we started recording a purchase date need an answer too:

/**
 * The instant everything counts from when we have no purchase date.
 *
 * Used as the window start for an account that paid before `first_purchase_at`
 * existed, which makes "since the purchase" degrade to "ever" for them rather
 * than to "never".
 */
const BEGINNING_OF_TIME = new Date(0);

A missing timestamp could default two ways, and the choice is not arbitrary. Degrading to "never" would silently make an entire cohort of long standing customers permanently ineligible. Degrading to "ever" restores exactly the old behaviour for the people who, by definition, have had their purchase for a while. When you add a gate keyed on a column that did not always exist, decide explicitly which side of the gate the nulls fall on.

Not all activity is one unit of activity

The original bar was a single threshold over a summed total, which treated a five minute game and a full assessment centre exercise as one unit of experience each.

/**
 * How much of each activity type counts as "enough practice to have an opinion".
 *
 * This replaced a single threshold over a summed total, which treated a
 * five-minute game and a full assessment centre exercise as one unit of
 * experience each. They are not comparable.
 */
export const REVIEW_PROMPT_MINIMUMS = {
  games: 2,
  exercises: 1,
  interviews: 1,
} as const;

export function meetsReviewPromptActivityBar(counts: CompletedActivityCounts): boolean {
  return (
    counts.games >= REVIEW_PROMPT_MINIMUMS.games ||
    counts.exercises >= REVIEW_PROMPT_MINIMUMS.exercises ||
    counts.interviews >= REVIEW_PROMPT_MINIMUMS.interviews
  );
}

Each type carries its own bar and clearing any single one is enough. The counts are never added. Someone who has completed one recorded interview has seen far more of the product than someone three quick games in, and summing the two says the opposite.

This is a small change with a general shape: if your engagement metric adds up events of visibly different weight, the total is not measuring what you think, and splitting the threshold per type is usually easier than inventing weights.

One clause that is really a product decision

(SELECT count(*) FROM interviews
  WHERE user_id = $1 AND status = 'complete') AS interviews

Interviews match on status = 'complete' rather than on a non-null completed_at, and that is the one place the three tables differ. Our status updater stamps completed_at on the way into 'failed' as well, so the column marks the end of processing rather than a session the candidate got anything out of.

Asking someone for a public review off the back of an interview that failed to process is precisely the wrong moment. A column named completed_at that is also set on failure is a trap laid for every future query, and this is the kind of thing worth finding before it produces an embarrassing prompt rather than after.

There is a second guard in the same spirit:

needsReviewPrompt: eligible && !(await deletedDataRecently(userId)),

If someone has exercised data deletion in the last few days, they are not asked. Whatever they are feeling, a request to say something nice in public is not the right follow up.

Order the checks by cost and selectivity

const [profileRows, promptRows] = await Promise.all([...]);   // two indexed single-row reads

if (alreadyPrompted) return { needsReviewPrompt: false, ... };

const { hasPaid, firstPurchaseAt } = await readPurchaseStatus(userId);
if (!hasPaid) return { needsReviewPrompt: false, ... };

const { lifetime, sincePurchase } = await countCompletedActivities(userId, firstPurchaseAt ?? BEGINNING_OF_TIME);

This runs on every dashboard render, so the order matters more than it looks.

After the first prompt, "already prompted" is true for essentially everyone, and those users pay for two indexed single row lookups and nothing else. A free user is turned away by one more row read, before the activity aggregate. The aggregate, which is six subqueries across three tables, only runs for a paying customer who has never been asked, which is the rare case.

Cheapest and most selective first is standard advice. What makes it concrete here is knowing which branch is the common one after the feature has been live for a while, which is not the same as which branch you were thinking about while writing it.

The Date that took the dashboard down

The gate ships a window start into a raw SQL template. The first version passed a Date.

// Interpolate the window start as an ISO string, never as the Date itself.
// Drizzle's postgres-js driver replaces the client's date serializers with an
// identity function (it maps dates in its own column encoders, which a raw
// `sql` template bypasses), so a Date reaches the wire encoder unconverted and
// postgres.js throws `Buffer.byteLength(...) received an instance of Date`.
// That took /dashboard down for every paying customer who reached this gate.
const sinceIso = since.toISOString();

Worth unpacking, because it is a genuinely surprising interaction. The ORM handles dates correctly in its own query builder, by encoding them in its column encoders. To do that it installs an identity function as the driver's date serializer, since it has already done the work. A raw sql template bypasses the column encoders but not the driver, so the Date arrives at the wire encoder with nobody left to convert it, and the driver throws.

So the blast radius was exactly the intersection of "dashboard render" and "paying customer past the earlier gates". Every free user was fine. Every already-prompted user was fine. Our own test accounts were fine. The people who hit it were customers.

Two things follow. Pass primitives into raw SQL templates, always, even when your ORM handles the rich type elsewhere. And when you add a branch that only executes for paying users, notice that you have written code your own team will almost never run.

Where it shows up

The prompt itself is behind a purchase, so the honest demonstration is the rest of the surface: the pricing page is what you would be buying, and the help page documents what each purchase includes.

The transferable part needs no account. Go and find your own review, rating or NPS prompt and answer three questions about it. Is it one-shot? What window does its activity bar measure? And does that window start before or after the moment the customer's expectations were set?