Skip to content

OpenPanel is an open-source web and product analytics platform, an open-source alternative to Mixpanel with optional self-hosting.

12.2 KB
Raw Blame
import { afterAll, beforeAll, describe, expect, it } from 'bun:test';
import { chQuery, TABLE_NAMES } from '@openpanel/db/src/clickhouse/client';
import { round } from '@openpanel/shared';
import {
  RETENTION_BLUEPRINT,
  RETENTION_FIXTURE,
  setupRetentionFixtures,
  teardownRetentionFixtures,
} from '../../../../../test/retention-fixtures';
import { testServiceDeps } from '../../../test/service-deps';
import type { ServiceDeps } from '../../services';
import {
  getRetentionCohort,
  getRetentionLastSeenSeries,
  getRetentionSeries,
  type IRetentionCohortRow,
  processCohortData,
} from './retention.service';

const PROJECT_ID = 'test-retention-cohort';

// Retention reaches ClickHouse as `deps.ch`, so the suite builds the same
// `ServiceDeps` main.ts does (pointed at `openpanel_test` by preload.ts).
let deps: ServiceDeps;
const { day, week } = RETENTION_FIXTURE;
/** RU1-RU5 and WU1-WU2, per the dataset table in retention-fixtures.ts. */
const DAY_SCENARIO_USERS = 5;
const WEEK_SCENARIO_USERS = 2;

// bun:test's `toEqual` overloads (unlike vitest's) reject a `readonly` actual
// against a mutable expected type — the blueprint fixture is `as const`. Pure
// type-level erasure, no runtime effect.
type DeepMutable<T> = T extends readonly (infer U)[]
  ? DeepMutable<U>[]
  : T extends object
    ? { -readonly [K in keyof T]: DeepMutable<T[K]> }
    : T;
function mutable<T>(value: T): DeepMutable<T> {
  return value as DeepMutable<T>;
}

// Keep only the per-cohort rows (drop the leading "Weighted Average" row) and
// the fields the blueprint pins down.
function cohortsOnly(rows: IRetentionCohortRow[]) {
  return rows
    .filter((row) => row.cohort_interval !== 'Weighted Average')
    .map(({ cohort_interval, sum, values }) => ({
      cohort_interval,
      sum,
      values,
    }));
}

describe('getRetentionCohort', () => {
  beforeAll(async () => {
    deps = await testServiceDeps();
    await setupRetentionFixtures(PROJECT_ID);
  });

  afterAll(async () => {
    await teardownRetentionFixtures(PROJECT_ID);
  });

  it('matches the blueprint for day interval, criteria "on"', async () => {
    const rows = await getRetentionCohort(deps, {
      projectId: PROJECT_ID,
      firstEvent: ['app_open'],
      secondEvent: ['app_open'],
      criteria: 'on',
      interval: 'day',
      startDate: day.start,
      endDate: day.end,
    });

    expect(cohortsOnly(rows)).toEqual(mutable(RETENTION_BLUEPRINT.dayOn));
  });

  it('matches the blueprint for day interval, criteria "on_or_after"', async () => {
    const rows = await getRetentionCohort(deps, {
      projectId: PROJECT_ID,
      firstEvent: ['app_open'],
      secondEvent: ['app_open'],
      criteria: 'on_or_after',
      interval: 'day',
      startDate: day.start,
      endDate: day.end,
    });

    expect(cohortsOnly(rows)).toEqual(
      mutable(RETENTION_BLUEPRINT.dayOnOrAfter)
    );
  });

  it('counts retention across the year boundary (week interval)', async () => {
    // Week-of-year resets each Jan, so a 2024-W52 -> 2025-W01 return must still
    // count (year-aware diff).
    const rows = await getRetentionCohort(deps, {
      projectId: PROJECT_ID,
      firstEvent: ['app_open'],
      secondEvent: ['app_open'],
      criteria: 'on',
      interval: 'week',
      startDate: week.start,
      endDate: week.end,
    });

    expect(cohortsOnly(rows)).toEqual(mutable(RETENTION_BLUEPRINT.weekOn));
  });

  it('prepends a weighted-average row that is 100% retained at interval 0', async () => {
    const rows = await getRetentionCohort(deps, {
      projectId: PROJECT_ID,
      firstEvent: ['app_open'],
      secondEvent: ['app_open'],
      criteria: 'on',
      interval: 'day',
      startDate: day.start,
      endDate: day.end,
    });

    expect(rows[0]?.cohort_interval).toBe('Weighted Average');
    expect(rows[0]?.percentages[0]).toBe(1);
  });

  it('assigns each user to a single first-touch cohort (no double-counting)', async () => {
    const rows = cohortsOnly(
      await getRetentionCohort(deps, {
        projectId: PROJECT_ID,
        firstEvent: ['app_open'],
        secondEvent: ['app_open'],
        criteria: 'on',
        interval: 'day',
        startDate: day.start,
        endDate: day.end,
      })
    );

    // Only D0 and D1 are real cohorts. There is NO D2 cohort: every user who
    // fired app_open on D2 (RU1, RU2, RU4) already belongs to an earlier cohort.
    expect(rows.map((row) => row.cohort_interval)).toEqual([day.d0, day.d1]);

    // Without the GROUP BY profile_id dedup, every-occurrence cohorting places
    // recurring users in every day they were active, producing a spurious D2
    // cohort and inflated sizes.
    const legacy = await chQuery<{ cohort_interval: string; size: number }>(`
      SELECT toDate(created_at) AS cohort_interval, COUNT(DISTINCT profile_id) AS size
      FROM ${TABLE_NAMES.cohort_events_mv}
      WHERE project_id = '${PROJECT_ID}'
        AND name = 'app_open'
        AND created_at BETWEEN toDate('${day.start}') AND toDate('${day.end}')
      GROUP BY cohort_interval
      ORDER BY cohort_interval ASC
    `);

    // 3 day-buckets, each with 3 distinct users => sizes sum to 9 for only 5
    // real users. First-touch collapses this to 3 + 2 = 5.
    expect(legacy.map((r) => r.size)).toEqual([3, 3, 3]);
    const firstTouchTotal = rows.reduce((acc, row) => acc + row.sum, 0);
    expect(firstTouchTotal).toBe(5);
  });

  it('supports any-event (active-user) retention when no event names given', async () => {
    const named = await getRetentionCohort(deps, {
      projectId: PROJECT_ID,
      firstEvent: ['app_open'],
      secondEvent: ['app_open'],
      criteria: 'on',
      interval: 'day',
      startDate: day.start,
      endDate: day.end,
    });
    const anyEvent = await getRetentionCohort(deps, {
      projectId: PROJECT_ID,
      criteria: 'on',
      interval: 'day',
      startDate: day.start,
      endDate: day.end,
    });

    // In the day window the only non-app_open events are RU1's purchase (D1)
    // and RU2's purchase (D2). Those users are already active via app_open on
    // those days, so "any event" yields the same cohort sizes as app_open.
    expect(cohortsOnly(anyEvent).map((r) => r.sum)).toEqual(
      cohortsOnly(named).map((r) => r.sum)
    );
  });

  it('applies a property filter via the raw-events fallback path', async () => {
    // country = US drops RU3 (SE) from the D0 cohort. This filter references an
    // event column not present in cohort_events_mv, so the engine must fall
    // back to the raw events table.
    const rows = await getRetentionCohort(deps, {
      projectId: PROJECT_ID,
      firstEvent: ['app_open'],
      secondEvent: ['app_open'],
      criteria: 'on',
      interval: 'day',
      startDate: day.start,
      endDate: day.end,
      filters: [{ name: 'country', operator: 'is', value: ['US'] }],
    });

    expect(cohortsOnly(rows)).toEqual(mutable(RETENTION_BLUEPRINT.countryUsOn));
  });

  it('scopes to a saved cohort via the fast MV path (inCohort)', async () => {
    // inCohort only needs profile_id, so this stays on cohort_events_mv. The
    // cohort contains RU1 and RU4, leaving one user in each of D0 and D1.
    const rows = await getRetentionCohort(deps, {
      projectId: PROJECT_ID,
      firstEvent: ['app_open'],
      secondEvent: ['app_open'],
      criteria: 'on',
      interval: 'day',
      startDate: day.start,
      endDate: day.end,
      filters: [
        {
          name: 'cohort',
          operator: 'inCohort',
          value: [],
          cohortIds: [RETENTION_FIXTURE.cohort.id],
        },
      ],
    });

    expect(cohortsOnly(rows)).toEqual(mutable(RETENTION_BLUEPRINT.cohortOn));
  });

  it('applies a group filter through the groups join', async () => {
    const rows = await getRetentionCohort(deps, {
      projectId: PROJECT_ID,
      firstEvent: ['app_open'],
      secondEvent: ['app_open'],
      criteria: 'on',
      interval: 'day',
      startDate: day.start,
      endDate: day.end,
      filters: [
        {
          name: 'group.name',
          operator: 'is',
          value: [RETENTION_FIXTURE.group.name],
        },
      ],
    });

    expect(cohortsOnly(rows)).toEqual(mutable(RETENTION_BLUEPRINT.groupNameOn));
  });
});

describe('getRetentionSeries', () => {
  beforeAll(async () => {
    deps = await testServiceDeps();
    await setupRetentionFixtures(PROJECT_ID);
  });

  afterAll(async () => {
    await teardownRetentionFixtures(PROJECT_ID);
  });

  it('computes week-over-week active-user retention', async () => {
    const rows = await getRetentionSeries(deps, { projectId: PROJECT_ID });
    expect(rows).toEqual(mutable(RETENTION_BLUEPRINT.weeklySeries));
  });

  it('only reads events inside the requested window', async () => {
    const rows = await getRetentionSeries(deps, {
      projectId: PROJECT_ID,
      startDate: week.start,
      endDate: week.end,
    });
    expect(rows).toEqual(mutable(RETENTION_BLUEPRINT.weeklySeries.slice(1)));
  });

  it('reads the same series through a window spanning every event', async () => {
    const rows = await getRetentionSeries(deps, {
      projectId: PROJECT_ID,
      startDate: day.start,
      endDate: week.end,
    });
    expect(rows).toEqual(mutable(RETENTION_BLUEPRINT.weeklySeries));
  });
});

describe('getRetentionLastSeenSeries', () => {
  beforeAll(async () => {
    deps = await testServiceDeps();
    await setupRetentionFixtures(PROJECT_ID);
  });

  afterAll(async () => {
    await teardownRetentionFixtures(PROJECT_ID);
  });

  async function usersSeen(window: { startDate?: string; endDate?: string }) {
    const rows = await getRetentionLastSeenSeries(deps, {
      projectId: PROJECT_ID,
      ...window,
    });
    return rows.reduce((total, row) => total + row.users, 0);
  }

  it('counts every identified profile when no window is given', async () => {
    expect(await usersSeen({})).toBe(DAY_SCENARIO_USERS + WEEK_SCENARIO_USERS);
  });

  it('counts only profiles seen inside the requested window', async () => {
    expect(await usersSeen({ startDate: day.start, endDate: day.end })).toBe(
      DAY_SCENARIO_USERS
    );
    expect(await usersSeen({ startDate: week.start, endDate: week.end })).toBe(
      WEEK_SCENARIO_USERS
    );
  });
});

describe('processCohortData weighted average (pure)', () => {
  // Three weekly cohorts; reference date is 3 weeks after the oldest, so the
  // newer cohorts have not yet reached the later periods.
  const data = [
    {
      cohort_interval: '2024-01-07',
      total_first_event_count: 100,
      interval_0_user_count: 100,
      interval_1_user_count: 50,
      interval_2_user_count: 25,
      interval_3_user_count: 10,
    },
    {
      cohort_interval: '2024-01-14',
      total_first_event_count: 80,
      interval_0_user_count: 80,
      interval_1_user_count: 40,
      interval_2_user_count: 0, // genuine zero (mature) — must count
      interval_3_user_count: 0,
    },
    {
      cohort_interval: '2024-01-21',
      total_first_event_count: 60,
      interval_0_user_count: 60,
      interval_1_user_count: 30,
      interval_2_user_count: 0, // not yet mature — must be excluded
      interval_3_user_count: 0,
    },
  ];

  it('pools only mature cohorts and is internally consistent', () => {
    const rows = processCohortData(data, 3, 'week', '2024-01-28 00:00:00');
    const avg = rows[0]!;

    expect(avg.cohort_interval).toBe('Weighted Average');
    // Period 0 equals Total profiles and the curve starts at 100%.
    expect(avg.sum).toBe(80);
    expect(avg.values[0]).toBe(80);
    expect(avg.percentages[0]).toBe(1);
    // Period 2 pools C0+C1 only (C2 is immature). C1's genuine zero is included,
    // so it's 25/180 = 0.14, not 25/100 = 0.25.
    expect(avg.percentages[2]).toBe(0.14);
    // Period 3 pools only C0 (C1, C2 immature): 10/100 = 0.10.
    expect(avg.percentages[3]).toBe(0.1);

    expect(avg.values).toEqual([80, 40, 11, 8]);
    expect(avg.percentages).toEqual([1, 0.5, 0.14, 0.1]);
  });

  it('treats all periods as mature when no reference date is given', () => {
    const rows = processCohortData(data, 3, 'week');
    const avg = rows[0]!;
    // Every cohort counts in every column: period 2 = 25/240, period 3 = 10/240.
    expect(avg.percentages[2]).toBe(round(25 / 240, 2));
    expect(avg.percentages[3]).toBe(round(10 / 240, 2));
  });
});