Something went wrong. Try again.
a tool for shared writing and social publishing
Something went wrong. Try again.
TypeScript
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340/** * Tinybird Definitions * * Datasource matching the Vercel Web Analytics drain schema, * endpoint pipes for publication analytics, and typed client. * * Column names use camelCase to match the JSON keys sent by * Vercel's analytics drain (NDJSON format). */
import { defineDatasource, defineEndpoint, Tinybird, node, t, p, engine, type InferRow, type InferParams, type InferOutputRow, TokenDefinition,} from "@tinybirdco/sdk";
const PROD_READ_TOKEN = { name: "prod_read_token_v1", scopes: ["READ"] };
// ============================================================================// Datasources// ============================================================================
/** * Vercel Web Analytics drain events. * Column names match the Vercel drain JSON keys exactly. * `timestamp` is stored as UInt64 (Unix millis) as sent by Vercel. */export const analyticsEvents = defineDatasource("analytics_events", { description: "Vercel Web Analytics drain events", schema: { timestamp: t.uint64(), eventType: t.string().lowCardinality(), eventName: t.string().default(""), eventData: t.string().default(""), sessionId: t.uint64().nullable(), deviceId: t.uint64(), origin: t.string(), path: t.string(), referrer: t.string().default(""), queryParams: t.string().default(""), route: t.string().default(""), country: t.string().lowCardinality().default(""), region: t.string().default(""), city: t.string().default(""), osName: t.string().lowCardinality().default(""), osVersion: t.string().default(""), clientName: t.string().lowCardinality().default(""), clientType: t.string().lowCardinality().default(""), clientVersion: t.string().default(""), deviceType: t.string().lowCardinality().default(""), deviceBrand: t.string().default(""), deviceModel: t.string().default(""), browserEngine: t.string().default(""), browserEngineVersion: t.string().default(""), sdkVersion: t.string().default(""), sdkName: t.string().default(""), sdkVersionFull: t.string().default(""), vercelEnvironment: t.string().lowCardinality().default(""), vercelUrl: t.string().default(""), flags: t.string().default(""), deployment: t.string().default(""), schema: t.string().default(""), projectId: t.string().default(""), ownerId: t.string().default(""), dataSourceName: t.string().default(""), }, engine: engine.mergeTree({ sortingKey: ["origin", "timestamp"], partitionKey: "toYYYYMM(fromUnixTimestamp64Milli(timestamp))", }),});
export type AnalyticsEventsRow = InferRow<typeof analyticsEvents>;
// ============================================================================// Endpoints// ============================================================================
/** * publication_traffic – daily pageview time series for a publication domain. */export const publicationTraffic = defineEndpoint("publication_traffic", { description: "Daily pageview time series for a publication domain", params: { domains: p.string(), date_from: p.string().optional(), date_to: p.string().optional(), path: p.string().optional(), referrer_host: p.string().optional(), bsky_post: p.string().optional(), }, tokens: [PROD_READ_TOKEN], nodes: [ node({ name: "endpoint", sql: ` SELECT toDate(fromUnixTimestamp64Milli(timestamp)) AS day, count() AS pageviews, uniq(deviceId) AS visitors FROM analytics_events WHERE eventType = 'pageview' AND domain(origin) IN splitByChar(',', {{String(domains)}}) {% if defined(date_from) %} AND fromUnixTimestamp64Milli(timestamp) >= parseDateTimeBestEffort({{String(date_from)}}) {% end %} {% if defined(date_to) %} AND fromUnixTimestamp64Milli(timestamp) <= parseDateTimeBestEffort({{String(date_to)}}) {% end %} {% if defined(path) %} AND path = {{String(path)}} {% end %} {% if defined(referrer_host) %} AND domain(referrer) = {{String(referrer_host)}} {% end %} {% if defined(bsky_post) %} AND JSONExtractString(queryParams, 'utm_source') = 'bluesky' AND JSONExtractString(queryParams, 'utm_content') = {{String(bsky_post)}} {% end %} GROUP BY day ORDER BY day ASC `, }), ], output: { day: t.date(), pageviews: t.uint64(), visitors: t.uint64(), },});
export type PublicationTrafficParams = InferParams<typeof publicationTraffic>;export type PublicationTrafficOutput = InferOutputRow< typeof publicationTraffic>;
/** * publication_top_referrers – top referring domains for a publication. */export const publicationTopReferrers = defineEndpoint( "publication_top_referrers", { tokens: [PROD_READ_TOKEN], description: "Top referrers for a publication domain", params: { domains: p.string(), date_from: p.string().optional(), date_to: p.string().optional(), path: p.string().optional(), referrer_host: p.string().optional(), bsky_post: p.string().optional(), limit: p.int32().optional(10), }, nodes: [ node({ name: "endpoint", sql: ` SELECT domain(referrer) AS referrer_host, count() AS pageviews FROM analytics_events WHERE eventType = 'pageview' AND domain(origin) IN splitByChar(',', {{String(domains)}}) AND referrer != '' AND domain(referrer) NOT IN splitByChar(',', {{String(domains)}}) {% if defined(date_from) %} AND fromUnixTimestamp64Milli(timestamp) >= parseDateTimeBestEffort({{String(date_from)}}) {% end %} {% if defined(date_to) %} AND fromUnixTimestamp64Milli(timestamp) <= parseDateTimeBestEffort({{String(date_to)}}) {% end %} {% if defined(path) %} AND path = {{String(path)}} {% end %} {% if defined(referrer_host) %} AND domain(referrer) = {{String(referrer_host)}} {% end %} {% if defined(bsky_post) %} AND JSONExtractString(queryParams, 'utm_source') = 'bluesky' AND JSONExtractString(queryParams, 'utm_content') = {{String(bsky_post)}} {% end %} GROUP BY referrer_host ORDER BY pageviews DESC LIMIT {{Int32(limit, 10)}} `, }), ], output: { referrer_host: t.string(), pageviews: t.uint64(), }, },);
export type PublicationTopReferrersParams = InferParams< typeof publicationTopReferrers>;export type PublicationTopReferrersOutput = InferOutputRow< typeof publicationTopReferrers>;
/** * publication_top_pages – top pages by pageviews for a publication. */export const publicationTopPages = defineEndpoint("publication_top_pages", { description: "Top pages for a publication domain", tokens: [PROD_READ_TOKEN], params: { domains: p.string(), date_from: p.string().optional(), date_to: p.string().optional(), referrer_host: p.string().optional(), bsky_post: p.string().optional(), limit: p.int32().optional(10), }, nodes: [ node({ name: "endpoint", sql: ` SELECT path, count() AS pageviews FROM analytics_events WHERE eventType = 'pageview' AND domain(origin) IN splitByChar(',', {{String(domains)}}) {% if defined(date_from) %} AND fromUnixTimestamp64Milli(timestamp) >= parseDateTimeBestEffort({{String(date_from)}}) {% end %} {% if defined(date_to) %} AND fromUnixTimestamp64Milli(timestamp) <= parseDateTimeBestEffort({{String(date_to)}}) {% end %} {% if defined(referrer_host) %} AND domain(referrer) = {{String(referrer_host)}} {% end %} {% if defined(bsky_post) %} AND JSONExtractString(queryParams, 'utm_source') = 'bluesky' AND JSONExtractString(queryParams, 'utm_content') = {{String(bsky_post)}} {% end %} GROUP BY path ORDER BY pageviews DESC LIMIT {{Int32(limit, 10)}} `, }), ], output: { path: t.string(), pageviews: t.uint64(), },});
export type PublicationTopPagesParams = InferParams<typeof publicationTopPages>;export type PublicationTopPagesOutput = InferOutputRow< typeof publicationTopPages>;
/** * publication_bsky_traffic – pageviews attributed to specific Bluesky posts. * * Links we embed in Bluesky posts carry utm_source=bluesky and * utm_content=<did>/<rkey> (see src/utils/bskyPostUtm.ts), so grouping by * utm_content yields per-post traffic. queryParams is a JSON-encoded object. */export const publicationBskyTraffic = defineEndpoint( "publication_bsky_traffic", { description: "Pageviews per referring Bluesky post for a publication", tokens: [PROD_READ_TOKEN], params: { domains: p.string(), date_from: p.string().optional(), date_to: p.string().optional(), path: p.string().optional(), limit: p.int32().optional(25), }, nodes: [ node({ name: "endpoint", sql: ` SELECT JSONExtractString(queryParams, 'utm_content') AS post_ref, count() AS pageviews, uniq(deviceId) AS visitors FROM analytics_events WHERE eventType = 'pageview' AND domain(origin) IN splitByChar(',', {{String(domains)}}) AND JSONExtractString(queryParams, 'utm_source') = 'bluesky' AND JSONExtractString(queryParams, 'utm_content') != '' {% if defined(date_from) %} AND fromUnixTimestamp64Milli(timestamp) >= parseDateTimeBestEffort({{String(date_from)}}) {% end %} {% if defined(date_to) %} AND fromUnixTimestamp64Milli(timestamp) <= parseDateTimeBestEffort({{String(date_to)}}) {% end %} {% if defined(path) %} AND path = {{String(path)}} {% end %} GROUP BY post_ref ORDER BY pageviews DESC LIMIT {{Int32(limit, 25)}} `, }), ], output: { post_ref: t.string(), pageviews: t.uint64(), visitors: t.uint64(), }, },);
export type PublicationBskyTrafficParams = InferParams< typeof publicationBskyTraffic>;export type PublicationBskyTrafficOutput = InferOutputRow< typeof publicationBskyTraffic>;
// ============================================================================// Client// ============================================================================
export const tinybird = new Tinybird({ datasources: { analyticsEvents }, pipes: { publicationTraffic, publicationTopReferrers, publicationTopPages, publicationBskyTraffic, }, devMode: false,});