Something went wrong. Try again.
a tool for shared writing and social publishing
Something went wrong. Try again.
TypeScript
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238/** * 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,} from "@tinybirdco/sdk";
// ============================================================================// 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(), 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: { domain: p.string(), date_from: p.string().optional(), date_to: p.string().optional(), path: p.string().optional(), }, 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) = {{String(domain)}} {% 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 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", { description: "Top referrers for a publication domain", params: { domain: p.string(), date_from: p.string().optional(), date_to: p.string().optional(), path: 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) = {{String(domain)}} AND referrer != '' AND domain(referrer) != {{String(domain)}} {% 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 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", params: { domain: p.string(), date_from: p.string().optional(), date_to: 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) = {{String(domain)}} {% if defined(date_from) %} AND fromUnixTimestamp64Milli(timestamp) >= parseDateTimeBestEffort({{String(date_from)}}) {% end %} {% if defined(date_to) %} AND fromUnixTimestamp64Milli(timestamp) <= parseDateTimeBestEffort({{String(date_to)}}) {% 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>;
// ============================================================================// Client// ============================================================================
export const tinybird = new Tinybird({ datasources: { analyticsEvents }, pipes: { publicationTraffic, publicationTopReferrers, publicationTopPages }, devMode: false,});