* 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)
| 702 | * like every other rolled-up number on the page. |
| 703 | */ |
| 704 | async function activation(env: Env, url: URL, range: Range): Promise<ApiResult> { |
| 705 | const rawWindow = url.searchParams.get('window'); |
| 706 | const window = rawWindow === null ? DEFAULT_ACTIVATION_WINDOW : Number(rawWindow); |
| 707 | if (!Number.isInteger(window) || window < 1 || window > MAX_ACTIVATION_WINDOW) { |
| 708 | return fail(`window must be an integer between 1 and ${MAX_ACTIVATION_WINDOW}`); |
| 709 | } |
| 710 | |
| 711 | const batch = await env.DB.batch([ |
| 712 | env.DB.prepare( |
| 713 | `SELECT first_day AS day, |
| 714 | count(*) AS installs, |
| 715 | count(CASE WHEN first_index_day <= date(first_day, ?) THEN 1 END) AS activated |
| 716 | FROM machine_first_seen |
| 717 | WHERE first_day BETWEEN ? AND ? |
| 718 | GROUP BY first_day`, |
| 719 | // A bound modifier string, built from an integer this function validated — |
| 720 | // date() takes the modifier as data, so nothing is concatenated into SQL. |
| 721 | ).bind(`+${window} days`, range.from, range.to), |
| 722 | env.DB.prepare(COVERAGE_SQL), |
| 723 | ]); |
| 724 | |
| 725 | const rows = rowsOf<ActivationRow>(batch[0]); |
| 726 | const byDay = new Map(rows.map((r) => [r.day, r])); |
| 727 | const labels = dayList(range); |
| 728 | |
| 729 | // Cohorts younger than the window have not finished converting yet, so their |
| 730 | // rate is a floor, not a result. Marked rather than dropped: hiding the last |
| 731 | // week of a conversion chart is its own kind of lie. |
| 732 | const coveredThrough = firstOf<{ day: string | null }>(batch[1])?.day ?? null; |
| 733 | const incompleteFrom = addDays(coveredThrough ?? utcDay(Date.now()), -(window - 1)); |
| 734 | const counted = (day: string): boolean => coveredThrough !== null && day <= coveredThrough; |
| 735 | |
| 736 | let installs = 0; |
| 737 | let activated = 0; |
| 738 | const detail = labels.map((day) => { |
| 739 | const row = byDay.get(day); |
| 740 | const dayInstalls = row?.installs ?? 0; |
| 741 | if (!counted(day)) { |
| 742 | return { day, installs: dayInstalls, activated: null, rate: null, complete: false }; |
| 743 | } |
| 744 | const dayActivated = row?.activated ?? 0; |
| 745 | installs += dayInstalls; |
| 746 | activated += dayActivated; |
| 747 | return { |
| 748 | day, |
| 749 | installs: dayInstalls, |
| 750 | activated: dayActivated, |
| 751 | rate: dayInstalls > 0 ? dayActivated / dayInstalls : null, |
| 752 | complete: day < incompleteFrom, |
| 753 | }; |
| 754 | }); |
| 755 | |
| 756 | return { |
| 757 | body: { |
| 758 | range, |
| 759 | window_days: window, |
| 760 | installs, |
| 761 | activated, |
no test coverage detected