Skip to content

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

7.6 KB
Raw Blame
import { FastifyReply, FastifyRequest } from "fastify";
import { db } from "../../../db/postgres/postgres.js";
import { goals } from "../../../db/postgres/schema.js";
import { asc, count, desc, eq } from "drizzle-orm";
import { getTimeStatement } from "../utils/timeWindow.js";
import SqlString from "sqlstring";
import { FilterParams } from "@rybbit/shared";
import { analyticsRoute, runAnalyticsQuery } from "../utils/analyticsQuery.js";
import { buildFilteredSessionsCTE } from "../utils/sessionFilters.js";
import { buildGoalCondition } from "./goalConditions.js";

// Types for the response
interface GoalWithConversions {
  goalId: number;
  name: string | null;
  goalType: string;
  config: any;
  createdAt: string | null;
  total_conversions: number;
  total_sessions: number;
  conversion_rate: number;
}

interface GetGoalsResponse {
  data: GoalWithConversions[];
  meta: {
    total: number;
    page: number;
    pageSize: number;
    totalPages: number;
  };
}

interface GetGoalsRequest {
  Params: {
    siteId: string;
  };
  Querystring: FilterParams<{
    page?: string;
    page_size?: string;
    sort?: string;
    order?: "asc" | "desc";
  }>;
}

export const buildGoalsTotalSessionsQuery = (query: GetGoalsRequest["Querystring"], siteId: number) => {
  const timeStatement = getTimeStatement(query);
  const filteredSessionsCTE = buildFilteredSessionsCTE(query.filters, siteId, timeStatement);

  if (filteredSessionsCTE) {
    return `
      WITH ${filteredSessionsCTE}
      SELECT COUNT(*) AS total_sessions
      FROM FilteredSessions
    `;
  }

  return `
      SELECT COUNT(DISTINCT session_id) AS total_sessions
      FROM events
      WHERE site_id = ${SqlString.escape(siteId)}
      ${timeStatement}
    `;
};

/**
 * Returns null when none of the goals produces a valid conversion condition —
 * the handler responds with zero-conversion goals in that case.
 */
export const buildGoalsConversionsQuery = (
  query: GetGoalsRequest["Querystring"],
  siteId: number,
  siteGoals: (typeof goals.$inferSelect)[]
): string | null => {
  const timeStatement = getTimeStatement(query);
  const filteredSessionsCTE = buildFilteredSessionsCTE(query.filters, siteId, timeStatement);

  // Build a single query that calculates all goal conversions at once using conditional aggregation
  // This is more efficient than separate queries for each goal
  let conditionalClauses: string[] = [];

  for (const goal of siteGoals) {
    const goalCondition = buildGoalCondition(goal);
    if (!goalCondition) continue;

    conditionalClauses.push(`
        COUNT(DISTINCT IF(
          ${goalCondition},
          session_id,
          NULL
        )) AS goal_${goal.goalId}_conversions
      `);
  }

  if (conditionalClauses.length === 0) {
    return null;
  }

  return `
      ${filteredSessionsCTE ? `WITH ${filteredSessionsCTE}` : ""}
      SELECT
        ${conditionalClauses.join(", ")}
      FROM events
      ${filteredSessionsCTE ? "INNER JOIN FilteredSessions USING (session_id)" : ""}
      WHERE site_id = ${SqlString.escape(siteId)}
      ${timeStatement}
    `;
};

export const getGoals = analyticsRoute<GetGoalsRequest>(
  "goals data",
  async (request: FastifyRequest<GetGoalsRequest>, reply: FastifyReply) => {
    const { siteId } = request.params;
    const { page = "1", page_size: pageSize = "10", sort = "createdAt", order = "desc" } = request.query;

    const pageNumber = parseInt(page, 10);
    const pageSizeNumber = parseInt(pageSize, 10);

    // Validate page and pageSize
    if (isNaN(pageNumber) || pageNumber < 1) {
      return reply.status(400).send({ error: "Invalid page number" });
    }

    if (isNaN(pageSizeNumber) || pageSizeNumber < 1 || pageSizeNumber > 100) {
      return reply.status(400).send({ error: "Invalid page size, must be between 1 and 100" });
    }

    // Count total goals for pagination metadata. Drizzle's count() maps the
    // Postgres bigint to a number; a raw sql`count(*)` comes back as a string.
    const totalGoalsResult = await db
      .select({ count: count() })
      .from(goals)
      .where(eq(goals.siteId, Number(siteId)));

    const totalGoals = Number(totalGoalsResult[0]?.count ?? 0);
    const totalPages = Math.ceil(totalGoals / pageSizeNumber);

    // If no goals exist, return early with empty data
    if (totalGoals === 0) {
      return reply.send({
        data: [],
        meta: {
          total: 0,
          page: pageNumber,
          pageSize: pageSizeNumber,
          totalPages: 0,
        },
      });
    }

    // Apply sorting
    let orderBy;
    // Only allow sorting by valid columns
    const validSortColumns = ["goalId", "name", "goalType", "createdAt"];
    const sortColumn = validSortColumns.includes(sort) ? sort : "createdAt";

    if (order === "asc") {
      if (sortColumn === "goalId") orderBy = asc(goals.goalId);
      else if (sortColumn === "name") orderBy = asc(goals.name);
      else if (sortColumn === "goalType") orderBy = asc(goals.goalType);
      else orderBy = asc(goals.createdAt);
    } else {
      if (sortColumn === "goalId") orderBy = desc(goals.goalId);
      else if (sortColumn === "name") orderBy = desc(goals.name);
      else if (sortColumn === "goalType") orderBy = desc(goals.goalType);
      else orderBy = desc(goals.createdAt);
    }

    // Fetch paginated goals for this site from PostgreSQL
    const siteGoals = await db
      .select()
      .from(goals)
      .where(eq(goals.siteId, Number(siteId)))
      .orderBy(orderBy)
      .limit(pageSizeNumber)
      .offset((pageNumber - 1) * pageSizeNumber);

    if (siteGoals.length === 0) {
      // If no goals for this page, return empty data
      return reply.send({
        data: [],
        meta: {
          total: totalGoals,
          page: pageNumber,
          pageSize: pageSizeNumber,
          totalPages,
        },
      });
    }

    // First, get the total number of unique sessions (denominator for conversion rate)
    const totalSessionsData = await runAnalyticsQuery<{ total_sessions: number }>({
      query: buildGoalsTotalSessionsQuery(request.query, Number(siteId)),
    });
    const totalSessions = totalSessionsData[0]?.total_sessions || 0;

    const conversionQuery = buildGoalsConversionsQuery(request.query, Number(siteId), siteGoals);

    if (conversionQuery === null) {
      // If no valid goals to calculate, return the goals without conversion data
      const goalsWithZeroConversions = siteGoals.map(goal => ({
        ...goal,
        total_conversions: 0,
        total_sessions: totalSessions,
        conversion_rate: 0,
      }));

      return reply.send({
        data: goalsWithZeroConversions,
        meta: {
          total: totalGoals,
          page: pageNumber,
          pageSize: pageSizeNumber,
          totalPages,
        },
      });
    }

    // Execute the comprehensive query
    const conversionData = await runAnalyticsQuery<Record<string, number>>({ query: conversionQuery });

    // If we didn't get any results, use zeros
    const conversions = conversionData[0] || {};

    // Combine goals data with conversion metrics
    const goalsWithConversions: GoalWithConversions[] = siteGoals.map(goal => {
      const totalConversions = conversions[`goal_${goal.goalId}_conversions`] || 0;
      const conversionRate = totalSessions > 0 ? totalConversions / totalSessions : 0;

      return {
        ...goal,
        total_conversions: totalConversions,
        total_sessions: totalSessions,
        conversion_rate: conversionRate,
      };
    });

    return reply.send({
      data: goalsWithConversions,
      meta: {
        total: totalGoals,
        page: pageNumber,
        pageSize: pageSizeNumber,
        totalPages,
      },
    });
  }
);