Skip to content

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

5.8 KB
Raw Blame
import { FilterParams } from "@rybbit/shared";
import { FastifyReply, FastifyRequest } from "fastify";
import { analyticsRoute, QuerySpec, runAnalyticsQuery } from "../utils/analyticsQuery.js";
import { getSessionFilterStatement } from "../utils/sessionFilters.js";
import { getTimeStatement, isValidTimeZone } from "../utils/timeWindow.js";
import { getSessionGoalMatcher, SessionGoalMatcher } from "./sessionGoals.js";
import {
  buildAggregatedSessionsCTE,
  buildRangeConditions,
  buildViewCondition,
  parseSessionListParams,
  sessionListParamsSchema,
  SessionView,
} from "./sessionQuery.js";

/** How many sessions each saved view holds. `converted` is null on a site with no goals. */
export interface SessionViewCounts {
  all: number;
  identified: number;
  replay: number;
  converted: number | null;
  bounced: number;
  errors: number;
}

export interface SessionsSummary {
  // The period at a glance: every session the filters allow, whatever the
  // pageview/event/duration ranges are set to.
  sessions: number;
  session_duration: number;
  pages_per_session: number;
  bounce_rate: number;
  converted: number | null;
  with_errors: number;
  // The same sessions narrowed by the ranges, per view: the tab counts and the
  // list's total.
  matching: SessionViewCounts;
  // `matching` split by the day each session started, in the request's
  // timezone, newest first.
  days: ({ day: string } & SessionViewCounts)[];
}

export interface GetSessionsSummaryRequest {
  Params: {
    siteId: string;
  };
  Querystring: FilterParams<{
    min_pageviews?: string;
    max_pageviews?: string;
    min_events?: string;
    max_events?: string;
    min_duration?: string;
    max_duration?: string;
  }>;
}

// A day row per day of the window; ten years of days is far past any real range.
const MAX_DAYS = 3660;

const COUNTED_VIEWS: Exclude<SessionView, "all">[] = ["identified", "replay", "converted", "bounced", "errors"];

/**
 * One pass over the period's sessions, grouped by the day they started. The
 * rollup row (empty `day`) carries the period totals, so totals and day rows
 * cannot disagree and no second scan is needed for the list's total.
 */
export const buildSessionsSummaryQuery = (
  query: GetSessionsSummaryRequest["Querystring"],
  siteId: number,
  matcher: SessionGoalMatcher
): QuerySpec => {
  const params = sessionListParamsSchema.parse(query);
  const timeStatement = getTimeStatement(query);
  const filterStatement = getSessionFilterStatement(query.filters, siteId, timeStatement);
  const ranges = buildRangeConditions(params);
  const inRange = ranges.conditions.length ? ranges.conditions.join(" AND ") : "1";

  const viewCounts = COUNTED_VIEWS.map(
    view => `countIf(${inRange} AND ${buildViewCondition(view, query)}) AS matching_${view}`
  ).join(",\n      ");

  const querySQL = `
  WITH ${buildAggregatedSessionsCTE(query, matcher)}
  SELECT
      toString(toDate(session_start, {timeZone:String})) AS day,
      count() AS sessions,
      -- Not aliased to the column names: the ranges below read those columns,
      -- and an alias that shadows one turns them into nested aggregates.
      avg(session_duration) AS avg_duration,
      avg(pageviews) AS avg_pageviews,
      countIf(pageviews = 1) AS bounced,
      countIf(notEmpty(converted_goal_ids)) AS converted,
      countIf(errors > 0) AS with_errors,
      countIf(${inRange}) AS matching_all,
      ${viewCounts}
  FROM AggregatedSessions
  WHERE 1 = 1 ${filterStatement}
  GROUP BY day WITH ROLLUP
  ORDER BY day = '' DESC, day DESC
  LIMIT {maxRows:Int32}
  `;

  return {
    query: querySQL,
    params: {
      siteId,
      timeZone: query.time_zone && isValidTimeZone(query.time_zone) ? query.time_zone : "UTC",
      maxRows: MAX_DAYS + 1,
      ...ranges.queryParams,
    },
  };
};

type SummaryRow = {
  day: string;
  sessions: number;
  avg_duration: number | null;
  avg_pageviews: number | null;
  bounced: number;
  converted: number;
  with_errors: number;
  matching_all: number;
  matching_identified: number;
  matching_replay: number;
  matching_converted: number;
  matching_bounced: number;
  matching_errors: number;
};

const viewCounts = (row: SummaryRow | undefined, hasGoals: boolean): SessionViewCounts => ({
  all: row?.matching_all ?? 0,
  identified: row?.matching_identified ?? 0,
  replay: row?.matching_replay ?? 0,
  converted: hasGoals ? (row?.matching_converted ?? 0) : null,
  bounced: row?.matching_bounced ?? 0,
  errors: row?.matching_errors ?? 0,
});

/** Rollup row to period totals, day rows to `days`. An empty period has no rollup row. */
export function toSessionsSummary(rows: SummaryRow[], hasGoals: boolean): SessionsSummary {
  const total = rows.find(row => row.day === "");
  const sessions = total?.sessions ?? 0;

  return {
    sessions,
    session_duration: total?.avg_duration ?? 0,
    pages_per_session: total?.avg_pageviews ?? 0,
    bounce_rate: sessions > 0 ? ((total?.bounced ?? 0) / sessions) * 100 : 0,
    converted: hasGoals ? (total?.converted ?? 0) : null,
    with_errors: total?.with_errors ?? 0,
    matching: viewCounts(total, hasGoals),
    days: rows.filter(row => row.day !== "").map(row => ({ day: row.day, ...viewCounts(row, hasGoals) })),
  };
}

export const getSessionsSummary = analyticsRoute<GetSessionsSummaryRequest>(
  "sessions summary",
  async (req: FastifyRequest<GetSessionsSummaryRequest>, res: FastifyReply) => {
    const siteId = Number(req.params.siteId);

    const parsed = parseSessionListParams(req.query);
    if (!parsed.ok) {
      return res.status(400).send({ error: parsed.error });
    }

    const matcher = await getSessionGoalMatcher(siteId, req);
    const rows = await runAnalyticsQuery<SummaryRow>(buildSessionsSummaryQuery(req.query, siteId, matcher));

    return res.send({ data: toSessionsSummary(rows, matcher.goals.length > 0) });
  }
);