Function activation

telemetry-dashboard/src/api.ts:609–685

* Of the machines whose FIRST day falls in the range, how many ran an index * within `window` days of it. * * The cohort key is `machine_first_seen`, not install events: a machine that * reinstalls does not re-enter the funnel, which is what makes this a * conversion rate rather than an install

(env: Env, url: URL, range: Range)

 * says so rather than drawing a cliff and calling it a drop in conversion.
 */
async function activation(env: Env, url: URL, range: Range): Promise<ApiResult> {
  const rawWindow = url.searchParams.get('window');
  const window = rawWindow === null ? DEFAULT_ACTIVATION_WINDOW : Number(rawWindow);
  if (!Number.isInteger(window) || window < 1 || window > MAX_ACTIVATION_WINDOW) {
    return fail(`window must be an integer between 1 and ${MAX_ACTIVATION_WINDOW}`);
  }

  const batch = await env.DB.batch([
    env.DB.prepare(
      `SELECT f.first_day AS day,
              count(DISTINCT f.machine_id) AS installs,
              count(DISTINCT CASE WHEN e.machine_id IS NOT NULL THEN f.machine_id END) AS activated
         FROM machine_first_seen f
         LEFT JOIN events e
                ON e.machine_id = f.machine_id
               AND e.event = 'index'
               AND e.day >= f.first_day
               AND e.day <= date(f.first_day, ?)
        WHERE f.first_day BETWEEN ? AND ?
        GROUP BY f.first_day`,
      // A bound modifier string, built from an integer this function validated —
      // date() takes the modifier as data, so nothing is concatenated into SQL.
    ).bind(`+${window} days`, range.from, range.to),
    env.DB.prepare(`SELECT min(day) AS raw_from, max(day) AS raw_to FROM events`),
  ]);

  const rows = rowsOf<ActivationRow>(batch[0]);
  const byDay = new Map(rows.map((r) => [r.day, r]));
  const labels = dayList(range);

  const installs = rows.reduce((n, r) => n + (r.installs ?? 0), 0);
  const activated = rows.reduce((n, r) => n + (r.activated ?? 0), 0);

  // Cohorts younger than the window have not finished converting yet, so their
  // rate is a floor, not a result. Marked rather than dropped: hiding the last
  // week of a conversion chart is its own kind of lie.
  const boundsRow = firstOf<{ raw_from: string | null; raw_to: string | null }>(batch[1]);
  const latestRaw = boundsRow?.raw_to ?? utcDay(Date.now());
  const incompleteFrom = addDays(latestRaw, -(window - 1));

  const detail = labels.map((day) => {
    const row = byDay.get(day);
    const dayInstalls = row?.installs ?? 0;
    const dayActivated = row?.activated ?? 0;
    return {
      day,
      installs: dayInstalls,
      activated: dayActivated,
      rate: dayInstalls > 0 ? dayActivated / dayInstalls : null,
      complete: day < incompleteFrom,
    };
  });

  return {
    body: {
      range,
      window_days: window,
      installs,