Reports: Retention

Relevant source files

The following files were used as context for generating this wiki page:

• src/app/(main)/websites/[websiteId]/(reports)/retention/RetentionPage.tsx
• src/queries/sql/reports/getRetention.ts
• src/app/(main)/websites/[websiteId]/(reports)/retention/Retention.tsx
• src/app/(main)/websites/[websiteId]/(reports)/retention/page.tsx
• prisma/schema.prisma
• src/components/messages.ts

The Retention report is a feature that measures website "stickiness" by tracking how often cohorts of users return to the site over a period of time. It presents this data in a grid format, showing the percentage of users from an initial cohort who came back on subsequent days. This helps in understanding user engagement and the long-term value of user acquisition efforts.

The feature is composed of frontend React components for rendering the report, a backend API endpoint for data retrieval, and a complex SQL query that calculates the retention metrics from raw event data.

Sources: src/components/messages.ts:211-214

Architecture Overview

The Retention report follows a standard client-server architecture. The user interaction on the frontend triggers a request to a backend API, which in turn queries the database to compute and return the retention data. The system is designed to work with both PostgreSQL (via Prisma) and ClickHouse databases.

This diagram illustrates the high-level data flow from the user's browser to the database and back.

Frontend Components

The user interface for the Retention report is built with several React components.

Sources: src/app/(main)/websites/[websiteId]/(reports)/retention/page.tsx, src/app/(main)/websites/[websiteId]/(reports)/retention/RetentionPage.tsx, src/app/(main)/websites/[websiteId]/(reports)/retention/Retention.tsx

Component Hierarchy

The components are structured as follows:

RetentionPage.tsx

This is the main page component. Its primary responsibilities are:

• Managing the date range for the report. It defaults to the start and end of the current month based on the application's date range context.
• Rendering the WebsiteControls for filtering and the main Retention component.
• It notably disables the standard date filter and enables a month-based filter.

Sources: src/app/(main)/websites/[websiteId]/(reports)/retention/RetentionPage.tsx:7-17

Retention.tsx

This component is responsible for fetching and displaying the retention data grid.

• Data Fetching: It uses the useResultQuery('retention', ...) hook to fetch data from the API. The query is triggered with the websiteId, startDate, and endDate.
• Data Processing: It processes the flat array of data returned from the API into a nested structure suitable for rendering the cohort grid. It groups the data by the cohort date.
• Rendering: It renders a grid with dates as rows and days (1, 2, 3, 7, etc.) as columns. Each cell shows the percentage of users from the cohort who returned on that specific day.

Sources: src/app/(main)/websites/[websiteId]/(reports)/retention/Retention.tsx:19-45

Component Properties

Prop	Type	Default	Description
websiteId	string	-	The ID of the website to fetch the report for.
startDate	Date	-	The start date of the reporting period.
endDate	Date	-	The end date of the reporting period.
days	number[]	[1, 2, 3, 4, 5, 6, 7, 14, 21, 28]	The specific days to show as columns in the grid.

Sources: src/app/(main)/websites/[websiteId]/(reports)/retention/Retention.tsx:12-16

Backend Data Query

The backend logic is encapsulated in the getRetention function, which is designed to be database-agnostic.

Sources: src/queries/sql/reports/getRetention.ts

getRetention.ts

This module exports the main getRetention function which acts as a dispatcher, calling either relationalQuery for PostgreSQL or clickhouseQuery for ClickHouse based on the environment configuration.

Key Interfaces

Interface	Property	Type	Description
RetentionParameters	startDate	Date	The start of the date range for the query.
	endDate	Date	The end of the date range for the query.
	timezone	string?	The timezone to use for date calculations.
RetentionResult	date	string	The cohort date.
	day	number	The day number (0, 1, 2, ...) relative to the cohort date.
	visitors	number	The total number of visitors in the cohort on day 0.
	returnVisitors	number	The number of visitors from the cohort who returned on this day.
	percentage	number	The retention percentage (returnVisitors / visitors).

Sources: src/queries/sql/reports/getRetention.ts:8-19

SQL Query Logic

The core of the feature is the SQL query, which uses several Common Table Expressions (CTEs) to build the retention data.

The following diagram shows the logic flow of the relationalQuery for PostgreSQL.

1. cohort_items: This CTE identifies the initial group of users (a cohort). It finds the first event time (cohort_date) for each session_id within the given date range. This establishes the starting point for each cohort.
2. user_activities: This CTE tracks all activities for the sessions identified in cohort_items. It calculates day_number, which is the number of days that have passed since the user's cohort_date.
3. cohort_size: This calculates the total number of unique visitors in each cohort by counting the sessions from cohort_items for each cohort_date.
4. cohort_date: This aggregates the user_activities to count how many visitors from a specific cohort were active on each subsequent day_number.
5. Final SELECT: This query joins cohort_size and cohort_date to bring all the data together. It calculates the final retention percentage by dividing the returning visitors by the total cohort size. The results are limited to the first 31 days.

Sources: src/queries/sql/reports/getRetention.ts:39-93

Database Schema

The retention query primarily relies on the website_event table. The relationship with the session and website tables is also important for context.

This ER diagram shows that website_event records are central, linking to both a session and a website. The retention query heavily uses website_id, session_id, and created_at from the website_event table.

Sources: prisma/schema.prisma:35-71, 94-138

Summary

The Retention report is a powerful tool for analyzing user engagement over time. It leverages a sophisticated SQL query to process raw event data into daily user cohorts and their return rates. The frontend is built to present this complex data in an intuitive grid, allowing developers and marketers to quickly assess how well their website retains its users. The backend is flexibly designed to support both PostgreSQL and ClickHouse, ensuring scalability.