Skip to content

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

8.3 KB
Raw Blame
import { FilterParams } from "@rybbit/shared";
import { FastifyReply, FastifyRequest } from "fastify";
import { z } from "zod";
import { enrichWithTraits } from "../utils/utils.js";
import { getTimeStatement } from "../utils/timeWindow.js";
import { GetSessionsResponse } from "../sessions/getSessions.js";
import { analyticsRoute, runAnalyticsQuery } from "../utils/analyticsQuery.js";
import { SESSION_CHANNEL_AGG, SESSION_REFERRER_AGG } from "../utils/sessionAttribution.js";
import { buildFilteredSessionsCTE } from "../utils/sessionFilters.js";
import { buildFunnelStepCondition, FunnelStep } from "./funnelSteps.js";

type Funnel = {
  steps: FunnelStep[];
};

export interface GetFunnelStepSessionsRequest {
  Body: Funnel;
  Params: {
    siteId: string;
    stepNumber: string;
  };
  Querystring: FilterParams<{
    mode: "reached" | "dropped";
    limit?: number;
    page: number;
    /** "true" lists only the sessions that have a replay. */
    replays_only?: string;
  }>;
}

const replaysOnlySchema = z.enum(["true", "false"]).optional();

export const buildFunnelStepSessionsQuery = (
  query: GetFunnelStepSessionsRequest["Querystring"],
  siteId: number,
  steps: FunnelStep[],
  stepNumber: number
) => {
  const { mode } = query;
  const replaysOnly = query.replays_only === "true";
  const timeStatement = getTimeStatement(query);
  const filteredSessionsCTE = buildFilteredSessionsCTE(query.filters, siteId, timeStatement);

  // Build conditional statements for each step we need
  const stepsToCheck = mode === "reached" ? stepNumber : stepNumber + 1;
  const stepConditions = steps.slice(0, stepsToCheck).map(step => buildFunnelStepCondition(step));

  // Build CTEs for each funnel step to identify qualifying sessions. Steps are
  // ordered by the millisecond timestamp, as in the analyze and summary queries,
  // so a session counted as continuing is never listed as dropped (a pageview
  // and the event it fires often share a second).
  const stepCTEs = [
    `
    ${filteredSessionsCTE ? `${filteredSessionsCTE},` : ""}
    SessionActions AS (
      SELECT
        session_id,
        timestamp_ms,
        pathname,
        event_name,
        type,
        props,
        hostname,
        url_parameters,
        tag
      FROM events
      ${filteredSessionsCTE ? "INNER JOIN FilteredSessions USING (session_id)" : ""}
      WHERE
        site_id = {siteId:Int32}
        ${timeStatement}
    ),
    Step1 AS (
      SELECT DISTINCT
        session_id,
        min(timestamp_ms) as step_time
      FROM SessionActions
      WHERE ${stepConditions[0]}
      GROUP BY session_id
    )`,
  ];

  // Add CTEs for steps 2 through stepNumber (and +1 for dropped mode)
  for (let i = 1; i < stepsToCheck; i++) {
    stepCTEs.push(`
    Step${i + 1} AS (
      SELECT DISTINCT
        s${i}.session_id,
        min(sa.timestamp_ms) as step_time
      FROM Step${i} s${i}
      JOIN SessionActions sa ON s${i}.session_id = sa.session_id
      WHERE
        sa.timestamp_ms > s${i}.step_time
        AND ${stepConditions[i]}
      GROUP BY s${i}.session_id
    )`);
  }

  // Determine which sessions to retrieve
  const replayCondition = replaysOnly ? "session_id IN (SELECT session_id FROM ReplaySessions)" : "";
  let targetSessionsCTE = "";
  if (mode === "reached") {
    // Sessions that completed step N
    targetSessionsCTE = `
    TargetSessions AS (
      SELECT session_id
      FROM Step${stepNumber}
      ${replayCondition ? `WHERE ${replayCondition}` : ""}
    )`;
  } else {
    // Sessions that completed step N but NOT step N+1
    targetSessionsCTE = `
    TargetSessions AS (
      SELECT session_id
      FROM Step${stepNumber}
      WHERE session_id NOT IN (
        SELECT session_id
        FROM Step${stepNumber + 1}
      )
      ${replayCondition ? `AND ${replayCondition}` : ""}
    )`;
  }

  // Build main query to aggregate session data
  return `
    WITH
    ${stepCTEs.join(",\n")}
    ,
    -- Same rule as the sessions list: a replay with at least two recorded events
    ReplaySessions AS (
      SELECT DISTINCT session_id
      FROM session_replay_metadata_v2
      FINAL
      WHERE site_id = {siteId:Int32}
        AND event_count >= 2
    )
    ,
    ${targetSessionsCTE}
    ,
    AggregatedSessions AS (
      SELECT
        e.session_id,
        e.user_id,
        argMax(e.identified_user_id, e.timestamp) AS identified_user_id,
        argMax(e.country, e.timestamp) AS country,
        argMax(e.region, e.timestamp) AS region,
        argMax(e.city, e.timestamp) AS city,
        argMax(e.language, e.timestamp) AS language,
        argMax(e.device_type, e.timestamp) AS device_type,
        argMax(e.browser, e.timestamp) AS browser,
        argMax(e.browser_version, e.timestamp) AS browser_version,
        argMax(e.operating_system, e.timestamp) AS operating_system,
        argMax(e.operating_system_version, e.timestamp) AS operating_system_version,
        argMax(e.screen_width, e.timestamp) AS screen_width,
        argMax(e.screen_height, e.timestamp) AS screen_height,
        ${SESSION_REFERRER_AGG} AS referrer,
        ${SESSION_CHANNEL_AGG} AS channel,
        argMin(e.hostname, e.timestamp) AS hostname,
        argMin(e.page_title, e.timestamp) AS page_title,
        argMin(e.querystring, e.timestamp) AS querystring,
        argMin(e.url_parameters, e.timestamp)['utm_source'] AS utm_source,
        argMin(e.url_parameters, e.timestamp)['utm_medium'] AS utm_medium,
        argMin(e.url_parameters, e.timestamp)['utm_campaign'] AS utm_campaign,
        argMin(e.url_parameters, e.timestamp)['utm_term'] AS utm_term,
        argMin(e.url_parameters, e.timestamp)['utm_content'] AS utm_content,
        MAX(e.timestamp) AS session_end,
        MIN(e.timestamp) AS session_start,
        dateDiff('second', MIN(e.timestamp), MAX(e.timestamp)) AS session_duration,
        argMinIf(e.pathname, e.timestamp_ms, e.type = 'pageview') AS entry_page,
        argMaxIf(e.pathname, e.timestamp_ms, e.type = 'pageview') AS exit_page,
        countIf(e.type = 'pageview') AS pageviews,
        countIf(e.type = 'custom_event') AS events,
        countIf(e.type = 'error') AS errors,
        countIf(e.type = 'outbound') AS outbound,
        argMax(e.ip, e.timestamp) AS ip,
        argMax(e.lat, e.timestamp) AS lat,
        argMax(e.lon, e.timestamp) AS lon,
        argMax(e.tag, e.timestamp) AS tag
      FROM events e
      INNER JOIN TargetSessions ts ON e.session_id = ts.session_id
      WHERE
        e.site_id = {siteId:Int32}
        ${timeStatement}
      GROUP BY
        e.session_id,
        e.user_id
    )
    SELECT
      a.*,
      if(r.session_id != '', 1, 0) AS has_replay
    FROM AggregatedSessions a
    LEFT JOIN ReplaySessions r ON a.session_id = r.session_id
    ORDER BY a.session_end DESC
    LIMIT {limit:Int32} OFFSET {offset:Int32}
    `;
};

export const getFunnelStepSessions = analyticsRoute<GetFunnelStepSessionsRequest>(
  "funnel step sessions",
  async (req: FastifyRequest<GetFunnelStepSessionsRequest>, res: FastifyReply) => {
    const { steps } = req.body;
    const { stepNumber: stepNumberStr, siteId } = req.params;
    const { mode, page, limit } = req.query;

    const stepNumber = parseInt(stepNumberStr, 10);

    // Validate request
    if (!steps || steps.length < 2) {
      return res.status(400).send({ error: "At least 2 steps are required for a funnel" });
    }

    if (isNaN(stepNumber) || stepNumber < 1 || stepNumber > steps.length) {
      return res.status(400).send({ error: "Invalid step number" });
    }

    if (mode !== "reached" && mode !== "dropped") {
      return res.status(400).send({ error: "Mode must be 'reached' or 'dropped'" });
    }

    if (!replaysOnlySchema.safeParse(req.query.replays_only).success) {
      return res.status(400).send({ error: "replays_only must be 'true' or 'false'" });
    }

    // For final step in "dropped" mode, return empty results (no drop-off possible)
    if (mode === "dropped" && stepNumber === steps.length) {
      return res.send({ data: [] });
    }

    const data = await runAnalyticsQuery<Omit<GetSessionsResponse[number], "traits">>({
      query: buildFunnelStepSessionsQuery(req.query, Number(siteId), steps, stepNumber),
      params: {
        siteId: Number(siteId),
        limit: limit || 25,
        offset: ((page || 1) - 1) * (limit || 25),
      },
    });

    const dataWithTraits = await enrichWithTraits(data, Number(siteId));
    return res.send({ data: dataWithTraits });
  }
);