Skip to content

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

5.1 KB
Raw Blame
import { FastifyReply, FastifyRequest } from "fastify";
import { db } from "../../../db/postgres/postgres.js";
import { goals } from "../../../db/postgres/schema.js";
import { eq } from "drizzle-orm";
import { getTimeStatement } from "../utils/timeWindow.js";
import { FilterParams } from "@rybbit/shared";
import { GetSessionsResponse } from "../sessions/getSessions.js";
import { analyticsRoute, runAnalyticsQuery } from "../utils/analyticsQuery.js";
import { goalSessionUtmAgg } from "../utils/sessionAttribution.js";
import { buildFilteredSessionsCTE } from "../utils/sessionFilters.js";
import { buildGoalCondition } from "./goalConditions.js";

export interface GetGoalSessionsRequest {
  Params: {
    siteId: string;
    goalId: string;
  };
  Querystring: FilterParams<{
    limit?: number;
    page: number;
  }>;
}

export const buildGoalSessionsQuery = (
  query: GetGoalSessionsRequest["Querystring"],
  siteId: number,
  goalCondition: string
) => {
  const timeStatement = getTimeStatement(query);
  const filteredSessionsCTE = buildFilteredSessionsCTE(query.filters, siteId, timeStatement);

  // Build query to find sessions that match the goal
  // First, find all session_ids that have at least one event matching the goal
  return `
    WITH ${filteredSessionsCTE ? `${filteredSessionsCTE},` : ""}
    GoalSessions AS (
      SELECT DISTINCT session_id
      FROM events
      ${filteredSessionsCTE ? "INNER JOIN FilteredSessions USING (session_id)" : ""}
      WHERE
        site_id = {siteId:Int32}
        AND (${goalCondition})
        ${timeStatement}
    ),
    AggregatedSessions AS (
      SELECT
        e.session_id,
        e.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,
        argMin(e.referrer, e.timestamp) AS referrer,
        argMin(e.channel, e.timestamp) 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,
        ${goalSessionUtmAgg("utm_source")} AS utm_source,
        ${goalSessionUtmAgg("utm_medium")} AS utm_medium,
        ${goalSessionUtmAgg("utm_campaign")} AS utm_campaign,
        ${goalSessionUtmAgg("utm_term")} AS utm_term,
        ${goalSessionUtmAgg("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 GoalSessions gs ON e.session_id = gs.session_id
      WHERE
        e.site_id = {siteId:Int32}
        ${timeStatement}
      GROUP BY
        e.session_id,
        e.user_id
      ORDER BY session_end DESC
    )
    SELECT *
    FROM AggregatedSessions
    LIMIT {limit:Int32} OFFSET {offset:Int32}
    `;
};

export const getGoalSessions = analyticsRoute<GetGoalSessionsRequest>(
  "goal sessions",
  async (req: FastifyRequest<GetGoalSessionsRequest>, res: FastifyReply) => {
    const { goalId, siteId } = req.params;
    const { page, limit } = req.query;

    // Fetch the goal from PostgreSQL
    const goal = await db
      .select()
      .from(goals)
      .where(eq(goals.goalId, Number(goalId)))
      .limit(1);

    if (!goal || goal.length === 0) {
      return res.status(404).send({ error: "Goal not found" });
    }

    const goalData = goal[0];

    // Verify the goal belongs to the site
    if (goalData.siteId !== Number(siteId)) {
      return res.status(403).send({ error: "Goal does not belong to this site" });
    }

    // Build the goal matching condition
    const goalCondition = buildGoalCondition(goalData);
    if (!goalCondition) {
      return res.status(400).send({ error: "Invalid goal configuration" });
    }

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

    return res.send({ data });
  }
);