Skip to content

🐸 Rybbit - open-source and privacy-friendly alternative to Google Analytics that is 10x more intuitive.

4.9 KB
Raw Blame
import { FilterParams } from "@rybbit/shared";
import { FastifyReply, FastifyRequest } from "fastify";
import { analyticsRoute, runAnalyticsQuery } from "../utils/analyticsQuery.js";
import { effectiveUserId } from "../utils/effectiveUserId.js";
import { buildFilteredSessionsCTE } from "../utils/sessionFilters.js";
import { getTimeStatement } from "../utils/timeWindow.js";
import { buildSeenBeforeSubquery, NEW_USER_LOOKBACK_DAYS } from "./userScope.js";

export interface GetUsersSummaryRequest {
  Params: { siteId: string };
  Querystring: FilterParams;
}

export interface UsersSummary {
  users: number;
  identified_users: number;
  /** Sum of every user's session count, so it divides into sessions per user. */
  sessions: number;
  identified_sessions: number;
  /** Null for an all-time request: there is no earlier period to have been seen in. */
  new_users: number | null;
  returning_users: number | null;
  /** How far back "new" looks, in days. */
  lookback_days: number;
  /** Users with at least `power_min_sessions` sessions in the period. */
  power_users: number;
  power_min_sessions: number;
}

/** How many users had this many sessions, split by identified and new. */
export interface SessionHistogramRow {
  sessions: number;
  identified: number;
  is_new: number;
  users: number;
}

/**
 * Far more rows than a real site produces: `k` distinct session counts need at
 * least k(k+1)/2 sessions between them. The handler refuses a result that
 * reaches the limit rather than summing a truncated one.
 */
export const HISTOGRAM_ROW_LIMIT = 100_000;

/** Share of users a "power user" has to match or beat on sessions. */
const POWER_USER_QUANTILE = 0.95;
/** One session is a visit, not a habit, however the distribution falls. */
const POWER_USER_FLOOR = 2;

/**
 * The period's users as a histogram of sessions per user. Every stat-band
 * figure is a sum over it, so one pass over the period answers all of them.
 * Binds `{siteId:Int32}`.
 */
export function buildUsersSummaryQuery(query: FilterParams, siteId: number): string {
  const timeStatement = getTimeStatement(query);
  const filteredSessionsCTE = buildFilteredSessionsCTE(query.filters, siteId, timeStatement);
  const seenBefore = buildSeenBeforeSubquery(query);

  return `
WITH ${filteredSessionsCTE ? `${filteredSessionsCTE},` : ""}
UserStats AS (
    SELECT
        ${effectiveUserId("events")} AS effective_user_id,
        argMax(events.identified_user_id, events.timestamp) != '' AS identified,
        count(DISTINCT events.session_id) AS sessions
    FROM events
    ${filteredSessionsCTE ? "INNER JOIN FilteredSessions USING (session_id)" : ""}
    WHERE
        site_id = {siteId:Int32}
        ${timeStatement}
    GROUP BY effective_user_id
)
SELECT
    sessions,
    identified,
    ${seenBefore ? `effective_user_id NOT IN ${seenBefore}` : "1"} AS is_new,
    count() AS users
FROM UserStats
GROUP BY sessions, identified, is_new
ORDER BY sessions ASC
LIMIT ${HISTOGRAM_ROW_LIMIT}
  `;
}

/**
 * The smallest session count that at least `POWER_USER_QUANTILE` of users are
 * at or below, floored at two sessions.
 */
export function powerUserThreshold(rows: SessionHistogramRow[]): number {
  const total = rows.reduce((sum, row) => sum + row.users, 0);
  const sorted = [...rows].sort((a, b) => a.sessions - b.sessions);

  let seen = 0;
  for (const row of sorted) {
    seen += row.users;
    if (seen >= total * POWER_USER_QUANTILE) {
      return Math.max(POWER_USER_FLOOR, row.sessions);
    }
  }
  return POWER_USER_FLOOR;
}

export function summarizeUsers(rows: SessionHistogramRow[], hasLookback: boolean): UsersSummary {
  const sum = (pick: (row: SessionHistogramRow) => number) => rows.reduce((total, row) => total + pick(row), 0);

  const users = sum(row => row.users);
  const newUsers = sum(row => (row.is_new ? row.users : 0));
  const powerMinSessions = powerUserThreshold(rows);

  return {
    users,
    identified_users: sum(row => (row.identified ? row.users : 0)),
    sessions: sum(row => row.sessions * row.users),
    identified_sessions: sum(row => (row.identified ? row.sessions * row.users : 0)),
    new_users: hasLookback ? newUsers : null,
    returning_users: hasLookback ? users - newUsers : null,
    lookback_days: NEW_USER_LOOKBACK_DAYS,
    power_users: sum(row => (row.sessions >= powerMinSessions ? row.users : 0)),
    power_min_sessions: powerMinSessions,
  };
}

export const getUsersSummary = analyticsRoute<GetUsersSummaryRequest>(
  "users summary",
  async (req: FastifyRequest<GetUsersSummaryRequest>, res: FastifyReply) => {
    const siteId = Number(req.params.siteId);

    const rows = await runAnalyticsQuery<SessionHistogramRow>({
      query: buildUsersSummaryQuery(req.query, siteId),
      params: { siteId },
    });

    if (rows.length >= HISTOGRAM_ROW_LIMIT) {
      throw new Error("Users summary histogram exceeded its row limit");
    }

    return res.send({ data: summarizeUsers(rows, buildSeenBeforeSubquery(req.query) !== null) });
  }
);