🐸 Rybbit - open-source and privacy-friendly alternative to Google Analytics that is 10x more intuitive.
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 });
}
);