🐸 Rybbit - open-source and privacy-friendly alternative to Google Analytics that is 10x more intuitive.
import { FilterParams } from "@rybbit/shared";
import { FastifyReply, FastifyRequest } from "fastify";
import { and, inArray, eq } from "drizzle-orm";
import SqlString from "sqlstring";
import { db } from "../../../db/postgres/postgres.js";
import { goals } from "../../../db/postgres/schema.js";
import { TimeBucket } from "../types.js";
import { buildFilteredSessionsCTE } from "../utils/sessionFilters.js";
import { getTimeStatement, TimeBucketToFn } from "../utils/timeWindow.js";
import { analyticsRoute, runAnalyticsQuery } from "../utils/analyticsQuery.js";
import { buildGoalCondition } from "./goalConditions.js";
type GoalTimeSeriesPoint = {
time: string;
goal_id: number;
conversions: number;
total_sessions: number;
conversion_rate: number;
};
type GetGoalTimeSeriesRequest = {
Params: {
siteId: string;
};
Querystring: FilterParams<{
bucket: TimeBucket;
goal_ids?: string | number[];
}>;
};
const parseGoalIds = (value: string | number[] | undefined) => {
if (!value) return [];
if (Array.isArray(value)) {
return value.map(Number).filter(Number.isFinite);
}
try {
const parsed = JSON.parse(value);
if (Array.isArray(parsed)) {
return parsed.map(Number).filter(Number.isFinite);
}
} catch {
// Fall back to comma-separated values.
}
return value.split(",").map(Number).filter(Number.isFinite);
};
/**
* Returns null when none of the site goals produces a valid conversion
* condition — the handler responds with empty data in that case.
*/
export const buildGoalTimeSeriesQuery = (
query: GetGoalTimeSeriesRequest["Querystring"],
siteId: number,
siteGoals: (typeof goals.$inferSelect)[]
): string | null => {
const { bucket = "hour" } = query;
const timeStatement = getTimeStatement(query);
const filteredSessionsCTE = buildFilteredSessionsCTE(query.filters, siteId, timeStatement);
const filteredSessionsJoin = filteredSessionsCTE ? "INNER JOIN FilteredSessions USING (session_id)" : "";
const bucketFn = TimeBucketToFn[bucket];
const conversionQueries = siteGoals
.map(goal => {
const goalCondition = buildGoalCondition(goal);
if (!goalCondition) return null;
return `
SELECT
toDateTime(${bucketFn}(toTimeZone(timestamp, {timeZone:String}))) AS time,
${SqlString.escape(goal.goalId)} AS goal_id,
COUNT(DISTINCT session_id) AS conversions
FROM events
${filteredSessionsJoin}
WHERE
site_id = {siteId:Int32}
AND (${goalCondition})
${timeStatement}
GROUP BY time, goal_id
`;
})
.filter(Boolean);
if (conversionQueries.length === 0) {
return null;
}
const validGoalIds = siteGoals.map(goal => goal.goalId).join(", ");
return `
WITH
${filteredSessionsCTE ? `${filteredSessionsCTE},` : ""}
sessions_by_bucket AS (
SELECT
toDateTime(${bucketFn}(toTimeZone(timestamp, {timeZone:String}))) AS time,
COUNT(DISTINCT session_id) AS total_sessions
FROM events
${filteredSessionsJoin}
WHERE
site_id = {siteId:Int32}
${timeStatement}
GROUP BY time
),
goal_ids AS (
SELECT arrayJoin([${validGoalIds}]) AS goal_id
),
conversions_by_goal AS (
${conversionQueries.join("\nUNION ALL\n")}
)
SELECT
s.time AS time,
g.goal_id AS goal_id,
ifNull(c.conversions, 0) AS conversions,
s.total_sessions AS total_sessions,
if(s.total_sessions > 0, ifNull(c.conversions, 0) / s.total_sessions, 0) AS conversion_rate
FROM sessions_by_bucket s
CROSS JOIN goal_ids g
LEFT JOIN conversions_by_goal c ON c.time = s.time AND c.goal_id = g.goal_id
ORDER BY s.time ASC, g.goal_id ASC
`;
};
export const getGoalTimeSeries = analyticsRoute<GetGoalTimeSeriesRequest>(
"goal time series",
async (req: FastifyRequest<GetGoalTimeSeriesRequest>, res: FastifyReply) => {
const siteId = Number(req.params.siteId);
const { bucket = "hour" } = req.query;
const timeZone = req.query.time_zone || "UTC";
if (!TimeBucketToFn[bucket]) {
return res.status(400).send({ error: `Invalid bucket value: ${bucket}` });
}
const goalIds = [...new Set(parseGoalIds(req.query.goal_ids))];
if (goalIds.length === 0) {
return res.send({ data: [] });
}
const siteGoals = await db
.select()
.from(goals)
.where(and(eq(goals.siteId, siteId), inArray(goals.goalId, goalIds)));
if (siteGoals.length === 0) {
return res.send({ data: [] });
}
const query = buildGoalTimeSeriesQuery(req.query, siteId, siteGoals);
if (query === null) {
return res.send({ data: [] });
}
const data = await runAnalyticsQuery<GoalTimeSeriesPoint>({
query,
params: {
siteId,
timeZone,
},
});
return res.send({ data });
}
);