---
title: "Tinybird Analytics"
description: "Dub leverages Tinybird as its underlying real-time analytics and event streaming backbone to ingest, process, and query millions of click, lead, sale, webhook, and visit telemetry events. By utiliz..."
last_updated: "2026-10-12T07:00:10.087014+00:00"
canonical_url: "https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/technical/analytics-pipeline/tinybird-analytics"
---
Relevant source files
The following files were used as context for generating this wiki page:
- [apps/web/lib/analytics/get-analytics.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/analytics/get-analytics.ts)
- [apps/web/lib/postback/record-postback-event.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/postback/record-postback-event.ts)
- [apps/web/app/ee/api/track/visit/route.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/api/track/visit/route.ts)
- [apps/web/lib/tinybird/record-click.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/record-click.ts)
- [apps/web/app/api/analytics/route.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/api/analytics/route.ts)
- [apps/web/app/ee/api/track/application/route.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/api/track/application/route.ts)
- [apps/web/lib/tinybird/record-webhook-event.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/record-webhook-event.ts)
- [apps/web/scripts/tinybird/get-sale-events.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/tinybird/get-sale-events.ts)
- [apps/web/lib/postback/get-postback-events.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/postback/get-postback-events.ts)
- [apps/web/app/api/og/analytics/route.tsx](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/api/og/analytics/route.tsx)
- [apps/web/lib/tinybird/get-customer-events-tb.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/get-customer-events-tb.ts)
- [apps/web/app/ee/api/cron/streams/update-click-stats/route.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/api/cron/streams/update-click-stats/route.ts)
- [apps/web/lib/tinybird/record-lead.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/record-lead.ts)
- [apps/web/lib/tinybird/get-lead-events.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/get-lead-events.ts)
- [apps/web/lib/tinybird/record-click-zod.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/record-click-zod.ts)
- [apps/web/lib/tinybird/record-sale.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/record-sale.ts)
- [apps/web/lib/tinybird/record-link.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/record-link.ts)
- [apps/web/lib/dub.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/dub.ts)
- [apps/web/lib/analytics/get-events.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/analytics/get-events.ts)
- [apps/web/lib/tinybird/client.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/client.ts)
- [apps/web/lib/tinybird/log-import-error.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/log-import-error.ts)
- [apps/web/scripts/dub-wrapped.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/dub-wrapped.ts)
- [apps/web/lib/tinybird/index.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/index.ts)
- [apps/web/scripts/tinybird/delete-links.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/tinybird/delete-links.ts)
- [apps/web/lib/tinybird/get-click-event.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/get-click-event.ts)
- [apps/web/ui/analytics/events/example-data.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/ui/analytics/events/example-data.ts)
- [apps/web/lib/tinybird/get-webhook-events.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/get-webhook-events.ts)
- [apps/web/lib/tinybird/get-top-links-by-countries.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/get-top-links-by-countries.ts)
- [apps/web/lib/upstash/redis-streams/workspace-click-events.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/upstash/redis-streams/workspace-click-events.ts)
- [apps/web/scripts/tinybird/update-click-event.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/tinybird/update-click-event.ts)
## Overview
Dub leverages Tinybird as its underlying real-time analytics and event streaming backbone to ingest, process, and query millions of click, lead, sale, webhook, and visit telemetry events. By utilizing the `@chronark/zod-bird` client library, the system coordinates low-latency data pipelines that feed high-performance parameterized pipe endpoints. These endpoints power dynamic analytical reporting, workspace metrics aggregations, and raw event lookups across distributed workloads.
Sources: [apps/web/lib/tinybird/client.ts:1-6](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/client.ts#L1-L6), [apps/web/lib/tinybird/record-click-zod.ts:1-42](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/record-click-zod.ts#L1-L42)
## Tinybird Client Setup and Ingestion Endpoints
### Client Instantiation and Configuration
The Tinybird client is initialized using the `@chronark/zod-bird` package. The client instance `tb` requires two environment variables for authentication and connection targeting: `TINYBIRD_API_KEY` for authorization and `TINYBIRD_API_URL` for the API base URL.
```typescript
import { Tinybird } from "@chronark/zod-bird";
export const tb = new Tinybird({
token: process.env.TINYBIRD_API_KEY as string,
baseUrl: process.env.TINYBIRD_API_URL as string,
});
```
Sources: [apps/web/lib/tinybird/client.ts:1-6](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/client.ts#L1-L6)
> [!NOTE]
> The `tb` client export serves as the foundation for building ingest endpoints and pipes across all data streams, including click tracking, lead submissions, and link synchronizations.
Sources: [apps/web/lib/tinybird/client.ts:3-5](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/client.ts#L3-L5), [apps/web/lib/tinybird/index.ts:1-8](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/index.ts#L1-L8)
### Ingestion Endpoints and Schemas
Ingestion pipelines define schemas using `zod/v4` to validate event payloads before pushing them to Tinybird data sources. For example, the click recording endpoint validates input objects against `recordClickZodSchema` and targets the `dub_click_events` datasource with `wait: true`.
```typescript
export const recordClickZod = tb.buildIngestEndpoint({
datasource: "dub_click_events",
event: recordClickZodSchema,
wait: true,
});
```
Sources: [apps/web/lib/tinybird/record-click-zod.ts:39-42](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/record-click-zod.ts#L39-L42)
| Endpoint Constant | Tinybird Datasource | Description |
| :--- | :--- | :--- |
| `recordClickZod` | `dub_click_events` | Ingests validated click events with `wait: true` |
| `recordPostbackEvent` | `dub_postback_events` | Ingests postback event telemetry |
| `recordWebhookEvent` | `dub_webhook_events` | Ingests webhook delivery event logs |
| `recordLead` / `recordLeadWithTimestamp` | `dub_lead_events` | Ingests conversion lead events |
| `recordSale` / `recordSaleWithTimestamp` | `dub_sale_events` | Ingests conversion sale events |
| `logImportError` | `dub_import_error_logs` | Logs data import errors |
Sources: [apps/web/lib/postback/record-postback-event.ts:4-7](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/postback/record-postback-event.ts#L4-L7), [apps/web/lib/tinybird/record-webhook-event.ts:4-7](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/record-webhook-event.ts#L4-L7), [apps/web/lib/tinybird/record-lead.ts:5-14](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/record-lead.ts#L5-L14), [apps/web/lib/tinybird/record-sale.ts:5-14](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/record-sale.ts#L5-L14), [apps/web/lib/tinybird/log-import-error.ts:4-7](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/log-import-error.ts#L4-L7)
## Click and Interaction Ingestion Pipeline
### Request Validation and Filtering
Incoming requests to record a click pass through `recordClick`, which verifies the presence of a `clickId` and checks request headers or query parameters for `dub-no-track`. Except when the trigger is set to `"deeplink"`, bot detection executes via `detectBot(req)`; requests identified as bots are immediately discarded.
```typescript
export async function recordClick({
req,
clickId,
workspaceId,
linkId,
domain,
key,
url,
programId,
partnerId,
skipRatelimit,
timestamp,
referrer,
trigger = "link",
shouldCacheClickId,
}: {
req: Request;
clickId?: string;
linkId: string;
workspaceId?: string;
domain: string;
key: string;
url?: string;
programId?: string;
partnerId?: string;
skipRatelimit?: boolean;
timestamp?: string;
referrer?: string;
trigger?: string;
shouldCacheClickId?: boolean;
}) {
if (!clickId) {
return null;
}
const searchParams = new URL(req.url).searchParams;
if (req.headers.has("dub-no-track") || searchParams.has("dub-no-track")) {
return null;
}
const ua = userAgent(req);
if (trigger !== "deeplink") {
const isBot = detectBot(req);
if (isBot) {
console.log(`Click not recorded ❌ – Bot detected.`, {
ua,
isBot,
});
return null;
}
}
```
Sources: [apps/web/lib/tinybird/record-click.ts:22-78](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/record-click.ts#L22-L78)
> [!WARNING]
> Bot checks are deliberately bypassed when `trigger` evaluates to `"deeplink"`, allowing deep link interactions to bypass standard client filtering rules.
### Metadata Enrichment and Deduplication
After passing initial filters, the pipeline computes an identity hash. Unless `skipRatelimit` is enabled, it queries `recordClickCache` to prevent duplicate clicks from the same IP address and domain/key pair within a one-hour window. QR code detection runs via `detectQr(req)`, shifting the trigger type to `"qr"` if matched. Geolocation extracts continent and country-region headers on Vercel deployments, while falling back to `LOCALHOST_GEO_DATA` and `LOCALHOST_IP` locally. IP addresses belonging to EU country codes (`EU_COUNTRY_CODES`) are scrubbed to preserve privacy compliance.
```typescript
const identityHash = await getIdentityHash(req);
if (!skipRatelimit) {
try {
const cachedClickId = await recordClickCache.get({
domain,
key,
identityHash,
});
if (cachedClickId) {
return null;
}
} catch (error) {
console.error(`[recordClickCache error]: ${error}`);
return null;
}
}
const isQr = detectQr(req);
if (isQr) {
trigger = "qr";
}
const { continent, region } =
process.env.VERCEL === "1"
? {
continent: req.headers.get("x-vercel-ip-continent"),
region: req.headers.get("x-vercel-ip-country-region"),
}
: LOCALHOST_GEO_DATA;
const geo =
process.env.VERCEL === "1" ? geolocation(req) : LOCALHOST_GEO_DATA;
const ip = process.env.VERCEL === "1" ? ipAddress(req) : LOCALHOST_IP;
const isEuCountry = geo.country && EU_COUNTRY_CODES.includes(geo.country);
```
Sources: [apps/web/lib/tinybird/record-click.ts:80-123](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/record-click.ts#L80-L123)
### Dual Redis Stream Fanout and Tinybird Ingestion
Asynchronously via `waitUntil`, the constructed click payload is sent to Tinybird's event ingestion endpoint (`dub_click_events`), recorded in the rate limit cache for one hour, and fanned out concurrently to two distinct Upstash Redis streams: `publishLinkClickEvent` and `publishWorkspaceClickEvent`.
```typescript
fetchWithRetry(
`${process.env.TINYBIRD_API_URL}/v0/events?name=dub_click_events&wait=true`,
{
method: "POST",
headers: {
Authorization: `Bearer ${process.env.TINYBIRD_API_KEY}`,
},
body: JSON.stringify(clickData),
},
).then((res) => res.json()),
recordClickCache.set({
domain,
key,
identityHash,
clickId,
}),
publishLinkClickEvent({
linkId,
timestamp: clickData.timestamp,
...(workspaceId && url && { workspaceId }),
...(programId && partnerId && { programId, partnerId }),
}),
publishWorkspaceClickEvent(clickData),
```
Sources: [apps/web/lib/tinybird/record-click.ts:172-199](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/record-click.ts#L172-L199)
Workspace click events undergo schema validation via `clickEventSchemaTB`, converting boolean flags `qr` and `bot` into numeric representations before pushing to the `workspace:click:events` stream if the workspace has active link-click webhooks configured.
```typescript
export const workspaceClickEventStream = new RedisStream(STREAM_KEY);
export const publishWorkspaceClickEvent = async (event) => {
try {
const parsedEvent = clickEventSchemaTB.parse({
...event,
qr: Number(event.qr),
bot: Number(event.bot),
});
const hasLinkClickedWebhooks = await clickWebhookWorkspaces.has(
parsedEvent.workspace_id,
);
if (!hasLinkClickedWebhooks) {
return;
}
return await redis.xadd(STREAM_KEY, "*", parsedEvent);
} catch (error) {
logger.error("stream.publish_failed", {
service: "upstash",
streamKey: STREAM_KEY,
error: toErrorFields(error),
correlation: {
workspaceId: event.workspace_id,
clickId: event.click_id,
linkId: event.link_id,
},
});
await logger.flush();
throw error;
}
};
```
Sources: [apps/web/lib/upstash/redis-streams/workspace-click-events.ts:9-44](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/upstash/redis-streams/workspace-click-events.ts#L9-L44)
> [!NOTE]
> If `shouldCacheClickId` is enabled during ingestion, the click data is explicitly stored under `clickIdCache:${clickId}` in Redis with a 5-minute expiration to bridge the latency gap before ingested rows become available directly in Tinybird queries.
## Link Metadata Synchronization
### Payload Transformation and Ingestion
The link metadata synchronization subsystem records and updates link dimensions, casing normalization, and domain parameters into Tinybird datasources. Link synchronization is handled by the `recordLink` utility function, which transforms expanded link objects and pushes them into the `dub_links_metadata` Tinybird datasource.
Before records are dispatched to Tinybird, the `transformLinkTB` function maps an `ExpandedLink` entity into the schema shape required by `dub_links_metadata`. It extracts tag associations into flat string arrays, maps program enrollments to group identifiers and partner tag collections, and binds project identifiers to `workspace_id`.
```typescript
const transformLinkTB = (link: ExpandedLink) => {
const key = decodeKeyIfCaseSensitive({
domain: link.domain,
key: link.key,
});
return {
link_id: link.id,
domain: link.domain,
key,
url: link.url,
tag_ids: link.tags?.map(({ tag }) => tag.id) ?? [],
folder_id: link.folderId ?? "",
tenant_id: link.tenantId ?? "",
program_id: link.programId ?? "",
partner_id: link.partnerId ?? "",
partner_group_id: link.programEnrollment?.groupId ?? "",
partner_tag_ids:
link.programEnrollment?.programPartnerTags?.map(
({ partnerTagId }) => partnerTagId,
) ?? [],
workspace_id: link.projectId,
created_at: link.createdAt,
};
};
```
Sources: [apps/web/lib/tinybird/record-link.ts:52-76](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/record-link.ts#L52-L76)
The top-level `recordLink` function accepts either a single `ExpandedLink` or an array of links. When an array is provided, it maps each item through `transformLinkTB` and appends the `deleted` flag before invoking the ingestion endpoint with `wait: true`.
```typescript
export const recordLink = async (
payload: ExpandedLink | ExpandedLink[],
{ deleted }: { deleted?: boolean } = {},
) => {
if (Array.isArray(payload)) {
return await recordLinkTB(
payload.map(transformLinkTB).map((p) => ({ ...p, deleted })),
);
} else {
return await recordLinkTB({ ...transformLinkTB(payload), deleted });
}
};
```
Sources: [apps/web/lib/tinybird/record-link.ts:78-89](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/record-link.ts#L78-L89)
> [!NOTE]
> The `key` field undergoes case-sensitivity checks via `decodeKeyIfCaseSensitive` during transformation to preserve correct link routing rules across case-sensitive domains.
### Domain Transfer Deletion Scripts
Administrative maintenance tasks, such as handling domain transfers or purging metadata associated with specific domains, invoke Tinybird REST deletion endpoints via authenticated HTTP POST requests. The deletion script targets multiple datasources simultaneously using `Promise.allSettled`.
| Target Tinybird Datasource | Deletion Condition Source | HTTP Method & Auth |
| :--- | :--- | :--- |
| `dub_links_metadata` | `domain = 'xyz'` | POST, Bearer Token (`TINYBIRD_API_KEY`) |
| `dub_links_metadata_latest` | `domain = 'xyz'` | POST, Bearer Token (`TINYBIRD_API_KEY`) |
| `dub_regular_links_metadata_latest` | `domain = 'xyz'` | POST, Bearer Token (`TINYBIRD_API_KEY`) |
```typescript
const deleteCondition = "domain = 'xyz'";
// delete data from tinybird
const deleteRes = await Promise.allSettled([
deleteData({
dataSource: "dub_links_metadata",
deleteCondition,
}),
deleteData({
dataSource: "dub_links_metadata_latest",
deleteCondition,
}),
deleteData({
dataSource: "dub_regular_links_metadata_latest",
deleteCondition,
}),
]);
console.log(deleteRes);
}
```
Sources: [apps/web/scripts/tinybird/delete-links.ts:5-23](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/tinybird/delete-links.ts#L5-L23)
The underlying `deleteData` utility constructs URL-encoded bodies containing `delete_condition` parameters and communicates with Tinybird's US-East API region.
```typescript
const deleteData = async ({
dataSource,
deleteCondition,
}: {
dataSource: string;
deleteCondition: string;
}) => {
return fetch(
`https://api.us-east.tinybird.co/v0/datasources/${dataSource}/delete`,
{
method: "POST",
headers: {
Authorization: `Bearer ${process.env.TINYBIRD_API_KEY}`,
"Content-Type": "application/x-www-form-urlencoded",
},
body: `delete_condition=${deleteCondition}`,
},
).then((res) => res.json());
};
```
Sources: [apps/web/scripts/tinybird/delete-links.ts:25-43](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/tinybird/delete-links.ts#L25-L43)
> [!CAUTION]
> Deletion operations executed against Tinybird datasources via `delete_condition` permanently remove matching rows across the specified metadata views and cannot be undone.
## Analytics Pipe Query Processing
### Parameterized Analytics Pipelines
Query execution for timeseries metrics, aggregated link counts, and country statistics relies on parameterized Tinybird pipe endpoints built dynamically via `@chronark/zod-bird`. The `getAnalytics` function inspects input criteria such as grouping parameters and event types to select the matching version 4 pipe (`v4_count`, `v4_timeseries`, `v4_group_by_link_metadata`, or `v4_group_by`). Pipe inputs are validated against `analyticsFilterTB` parameters, returning structured analytics records containing dynamic groupBy fields, clicks, leads, sales, and sale amounts.
```typescript
const pipe = tb.buildPipe({
pipe: ["count", "timeseries"].includes(groupBy!)
? `v4_${groupBy}`
: [
"top_folders",
"top_link_tags",
"top_domains",
"top_partners",
"top_partner_tags",
"top_groups",
].includes(groupBy!)
? "v4_group_by_link_metadata"
: "v4_group_by",
parameters: analyticsFilterTB,
data: z.object({
groupByField: z.string(),
clicks: z.number().default(0),
leads: z.number().default(0),
sales: z.number().default(0),
saleAmount: z.number().default(0),
// only for cities and regions groupBy
country: z.string().optional(),
region: z.string().optional(),
}),
});
```
Sources: [apps/web/lib/analytics/get-analytics.ts:98-123](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/analytics/get-analytics.ts#L98-L123)
When querying country metrics for specific link sets, dedicated helper pipes such as `v3_group_by_link_country` process array filters for `linkIds` alongside formatted date ranges to return granular click distributions per country code.
```typescript
const getTopLinksByCountriesTB = tb.buildPipe({
pipe: "v3_group_by_link_country",
parameters: inputSchema,
data: responseSchema,
});
```
Sources: [apps/web/lib/tinybird/get-top-links-by-countries.ts:18-22](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/get-top-links-by-countries.ts#L18-L22)
> [!NOTE]
> Queries requesting all-time click metrics with a count interval and no supplemental dimensional filters bypass Tinybird pipes entirely, querying relational tables directly via PlanetScale SQL execution.
### Filter Assembly and Transformation
Parameters collected from API requests are transformed into serialized query requirements before hitting Tinybird endpoints. Advanced filter predicates, metadata query strings, and date boundaries formatted for ClickHouse are bundled into the `tinybirdParams` payload alongside explicit operator flags.
| Tinybird Parameter | Source Property | Description |
| :--- | :--- | :--- |
| `workspaceId` | `params.workspaceId` | Target workspace identifier for isolation |
| `groupBy` | `groupBy` | Dimension used to aggregate click and conversion metrics |
| `eventType` | `event` | Metric category (`clicks`, `leads`, `sales`, `saleAmount`, `composite`) |
| `start` | `startDate` | Start timestamp formatted via `formatUTCDateTimeClickhouse` |
| `end` | `endDate` | End timestamp formatted via `formatUTCDateTimeClickhouse` |
| `granularity` | `granularity` | Time bucket resolution (`minute`, `hour`, `day`, `month`) |
| `timezone` | `timezone` | Target timezone string defaulting to `"UTC"` |
| `filters` | `allFilters` | Serialized JSON array combining metadata and advanced criteria |
```typescript
const tinybirdParams: any = {
workspaceId,
customerId: params.customerId,
programId: params.programId,
partnerId: partnerIdParam,
partnerIdOperator,
partnerTagId: partnerTagIdParam,
partnerTagIdOperator,
tenantId: tenantIdParam,
tenantIdOperator,
groupId: groupIdParam,
groupIdOperator,
linkId: linkIdParam,
linkIdOperator,
domain: domainParam,
domainOperator,
folderId: folderIdParam,
folderIdOperator,
tagId: tagIdParam,
tagIdOperator,
groupBy,
eventType: event,
start: formatUTCDateTimeClickhouse(startDate),
end: formatUTCDateTimeClickhouse(endDate),
granularity,
timezone,
region: typeof regionForPipe === "string" ? regionForPipe : undefined,
root: params.root,
saleType: params.saleType,
filters: allFilters.length > 0 ? JSON.stringify(allFilters) : undefined,
};
```
Sources: [apps/web/lib/analytics/get-analytics.ts:164-194](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/analytics/get-analytics.ts#L164-L194)
> [!TIP]
> When executing batch analytical scripts such as `dub-wrapped.ts`, parallelized helper calls to `getAnalytics` aggregate top links and country breakdowns simultaneously across year-to-date intervals without blocking execution flow.
```typescript
const payloads = await Promise.all(
data.map(async ({ projectId, _count, _sum }) => {
const [topLinks, topCountries] = (await Promise.all([
getAnalytics({
workspaceId: projectId!,
event: "clicks",
groupBy: "top_links",
interval: "ytd",
root: false,
}),
getAnalytics({
workspaceId: projectId!,
event: "clicks",
groupBy: "countries",
interval: "ytd",
}),
])) as [AnalyticsTopLinks[], AnalyticsCountries[]];
return {
year: 2024,
workspaceId: projectId!,
totalLinks: _count.id,
totalClicks: _sum.clicks ?? 0,
topLinks: topLinks.slice(0, 5).map(({ domain, key, clicks }) => ({
item: `${domain}/${key}`,
count: clicks,
})),
topCountries: topCountries.slice(0, 5).map(({ country, clicks }) => ({
item: country,
count: clicks,
})),
};
}),
);
```
Sources: [apps/web/scripts/dub-wrapped.ts:56-89](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/dub-wrapped.ts#L56-L89)
## Raw Event Queries and Lookups
### Event Retrieval Pipes and Functions
Dub queries raw, individual records by leveraging specialized Tinybird pipe wrappers and direct data source operations. Individual click records are fetched via `getClickEvent`, which checks a Redis cache first under `clickIdCache:${clickId}` before falling back to the `get_click_event` Tinybird pipe. Conversion and telemetry events use similar parameterized pipes: `getLeadEvents` queries lead records filtered by an array of customer identifiers, `getSaleEvents` targets internal sale records for a specific customer ID, `getPostbackEvents` retrieves postback logs by postback ID, and `getWebhookEvents` queries webhook dispatch history by webhook identifier.
```typescript
const getClickEventTB = tb.buildPipe({
pipe: "get_click_event",
parameters: z.object({
clickId: z.string(),
}),
data: clickEventSchemaTB,
});
```
Sources: [apps/web/lib/tinybird/get-click-event.ts:7-13](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/get-click-event.ts#L7-L13)
| Pipe Function | Tinybird Pipe Name | Parameter Schema | Data Output Schema |
| :--- | :--- | :--- | :--- |
| `getClickEventTB` | `get_click_event` | `clickId: z.string()` | `clickEventSchemaTB` |
| `getLeadEvents` | `get_lead_events` | `customerIds: z.string().array()` | `leadEventSchemaTB` |
| `getSaleEvents` | `internal_get_sale_events` | `customerId: z.string()` | `z.any()` |
| `getPostbackEvents` | `get_postback_events` | `postbackId: z.string()` | `postbackEventOutputSchemaTB` |
| `getWebhookEvents` | `get_webhook_events` | `webhookId: z.string()` | `webhookEventSchemaTB` |
| `getCustomerEventsTB` | `v2_customer_events` | `z.any()` | `z.any()` |
Sources: [apps/web/scripts/tinybird/get-sale-events.ts:4-10](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/tinybird/get-sale-events.ts#L4-L10), [apps/web/lib/postback/get-postback-events.ts:5-11](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/postback/get-postback-events.ts#L5-L11), [apps/web/lib/tinybird/get-customer-events-tb.ts:4-8](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/get-customer-events-tb.ts#L4-L8), [apps/web/lib/tinybird/get-lead-events.ts:5-10](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/get-lead-events.ts#L5-L10), [apps/web/lib/tinybird/get-click-event.ts:7-13](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/get-click-event.ts#L7-L13), [apps/web/lib/tinybird/get-webhook-events.ts:5-11](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/get-webhook-events.ts#L5-L11)
> [!NOTE]
> The `getCustomerEventsTB` wrapper delegates directly to the `v2_customer_events` pipe, dynamically appending `linkIds` and `limit` parameters when provided alongside the required `customerId`.
```typescript
export const getCustomerEventsTB = async ({
customerId,
linkIds,
limit,
}: {
customerId: string;
linkIds?: string[];
limit?: number;
}) => {
return await pipe({
customerId,
...(linkIds ? { linkIds } : {}),
...(limit ? { limit } : {}),
});
};
```
Sources: [apps/web/lib/tinybird/get-customer-events-tb.ts:10-23](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tinybird/get-customer-events-tb.ts#L10-L23)
### Unified Event Feeds and Modification Scripts
The `getEvents` function queries the `v4_events` pipe to unify retrieval across click, lead, and sale event types. It builds complex criteria arrays by parsing metadata queries and advanced filters, mapping workspace link filters with operators, and resolving relational data from Prisma for associated links and customers.
```typescript
const pipe = tb.buildPipe({
pipe: "v4_events",
parameters: eventsFilterTB,
data:
{
clicks: clickEventSchemaTBEndpoint,
leads: leadEventSchemaTBEndpoint,
sales: saleEventSchemaTBEndpoint,
}[eventType] ?? clickEventSchemaTBEndpoint,
});
```
Sources: [apps/web/lib/analytics/get-events.ts:77-86](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/analytics/get-events.ts#L77-L86)
> [!CAUTION]
> When updating raw click event properties via administrative scripts, modifications require pushing the amended JSON payload to the `dub_click_events` data source with `wait=true` while concurrently executing targeted deletions against both `dub_click_events_mv` and `dub_click_events_id` datasources using explicit `delete_condition` parameters.
```typescript
const deleteData = async ({
dataSource,
clickId,
columnName,
oldValue,
}: {
dataSource: string;
clickId: string;
columnName: string;
oldValue: string;
}) => {
return fetch(
`https://api.us-east.tinybird.co/v0/datasources/${dataSource}/delete`,
{
method: "POST",
headers: {
Authorization: `Bearer ${process.env.TINYBIRD_API_KEY}`,
"Content-Type": "application/x-www-form-urlencoded",
},
body: `delete_condition=click_id='${clickId}' and ${columnName}='${oldValue}'`,
},
).then((res) => res.json());
};
```
Sources: [apps/web/scripts/tinybird/update-click-event.ts:53-75](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/tinybird/update-click-event.ts#L53-L75)
## Stream Consumption and Aggregation Cron
### Batch Processing and Aggregation Constants
The background cron route defined in `apps/web/app/(ee)/api/cron/streams/update-click-stats/route.ts` manages stream ingestion from Upstash Redis streams into relational storage. Constants govern batch sizes, concurrency thresholds, and distributed execution protection.
| Constant Name | Value | Purpose |
| :--- | :--- | :--- |
| `BATCH_SIZE` | `10_000` | Maximum stream entries to consume per cron run |
| `SUB_BATCH_SIZE` | `50` | Database updates to run in parallel within each batch |
| `BACKLOG_ALERT_THRESHOLD` | `50_000` | Alert threshold when stream length exceeds this count |
| `BACKLOG_AGE_ALERT_MS` | `300_000` (5 mins) | Alert threshold when oldest pending entry exceeds age |
| `LOCK_KEY` | `lock:update-click-stats` | Redis key preventing concurrent GET/POST double-counting |
| `LOCK_TTL_SECONDS` | `600` | Lock TTL matching cron maxDuration to outlive execution |
Sources: [apps/web/app/ee/api/cron/streams/update-click-stats/route.ts:15-20](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/api/cron/streams/update-click-stats/route.ts#L15-L20)
> [!WARNING]
> The distributed `LOCK_KEY` uses a TTL of `600` seconds to match the maximum cron duration, ensuring that overlapping HTTP requests do not invoke duplicate batch processing cycles that would double-count click stats.
Sources: [apps/web/app/ee/api/cron/streams/update-click-stats/route.ts:19-20](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/api/cron/streams/update-click-stats/route.ts#L19-L20)
### Aggregation and Sub-Batching Architecture
The `aggregateClickStats` function iterates through incoming `RedisStreamEntry[]` objects, isolating metadata to construct grouped maps for links, workspaces, and partner program enrollments. Timestamps are parsed via `Date.parse`, defaulting to `Date.now()` if the value is invalid or non-finite.
```typescript
const aggregateClickStats = (
entries: RedisStreamEntry[],
): {
linkUpdates: LinkAggregate[];
workspaceUpdates: WorkspaceAggregate[];
enrollmentUpdates: EnrollmentAggregate[];
} => {
const links = new Map();
const workspaces = new Map();
const enrollments = new Map();
for (const entry of entries) {
const { linkId, workspaceId, programId, partnerId, timestamp } = entry.data;
if (!linkId) {
continue;
}
const parsedTimestamp = Date.parse(timestamp);
const lastClicked = Number.isFinite(parsedTimestamp)
? parsedTimestamp
: Date.now();
const existingLink = links.get(linkId);
if (existingLink) {
existingLink.clicks += 1;
existingLink.lastClicked = Math.max(
existingLink.lastClicked,
lastClicked,
);
existingLink.entryIds.push(entry.id);
} else {
links.set(linkId, {
linkId,
clicks: 1,
lastClicked,
entryIds: [entry.id],
});
}
if (workspaceId) {
const existingWorkspace = workspaces.get(workspaceId);
if (existingWorkspace) {
existingWorkspace.clicks += 1;
} else {
workspaces.set(workspaceId, {
workspaceId,
clicks: 1,
});
}
}
if (programId && partnerId) {
const key = `${programId}:${partnerId}`;
const existingEnrollment = enrollments.get(key);
if (existingEnrollment) {
existingEnrollment.clicks += 1;
} else {
enrollments.set(key, {
programId,
partnerId,
clicks: 1,
});
}
}
}
return {
linkUpdates: Array.from(links.values()),
workspaceUpdates: Array.from(workspaces.values()),
enrollmentUpdates: Array.from(enrollments.values()),
};
};
```
Sources: [apps/web/app/ee/api/cron/streams/update-click-stats/route.ts:40-112](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/api/cron/streams/update-click-stats/route.ts#L40-L112)
> [!TIP]
> The `processInSubBatches` utility slices accumulated update arrays into chunks defined by `SUB_BATCH_SIZE` and evaluates them using `Promise.allSettled`, tracking successes and isolating errors into a returned failure collection without aborting the broader cron job.
```typescript
const processInSubBatches = async (
items: T[],
handler: (item: T) => Promise<{ success: boolean; error?: unknown }>,
) => {
const errors: unknown[] = [];
let totalProcessed = 0;
for (let i = 0; i < items.length; i += SUB_BATCH_SIZE) {
const batch = items.slice(i, i + SUB_BATCH_SIZE);
const results = await Promise.allSettled(batch.map(handler));
for (const result of results) {
if (result.status === "fulfilled" && result.value.success) {
totalProcessed++;
} else if (result.status === "fulfilled" && result.value.error) {
errors.push(result.value.error);
} else if (result.status === "rejected") {
errors.push(result.reason);
}
}
}
return { totalProcessed, errors };
};
```
Sources: [apps/web/app/ee/api/cron/streams/update-click-stats/route.ts:114-137](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/api/cron/streams/update-click-stats/route.ts#L114-L137)
## Related
- [Conversion Tracking](https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/technical/analytics-pipeline/conversion-tracking)
- [Analytics Dashboard](https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/technical/analytics-pipeline/analytics-dashboard)
## Sitemap
See the full [sitemap](https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/llms.txt) for all pages in this wiki.