The modern link attribution platform. Loved by world-class engineering teams at Twilio, Zillow, Jobber, Vercel, Raycast, and more.
TypeScript clickhousemysqlnextjsopen-sourceplanetscaleprismatailwindcsstinybirdtypescriptupstashvercel
NODE day_intervals
SQL >
%
WITH
toStartOfDay(
toDateTime64({{ DateTime64(start, '2025-09-03 00:00:00.000') }}, 3),
{{ String(timezone, 'UTC') }}
) AS start,
toStartOfDay(
toDateTime64({{ DateTime64(end, '2025-10-03 00:00:00.000') }}, 3),
{{ String(timezone, 'UTC') }}
) AS
end
SELECT
arrayJoin(
arrayMap(
x -> toDateTime64(toStartOfDay(toDateTime64(x, 3), {{ String(timezone, 'UTC') }}), 3),
range(toUInt32(start + 86400), toUInt32(end + 86400),
86400
)
)
) as interval
NODE usage_events_data
SQL >
%
SELECT
toDateTime64(toStartOfDay(timestamp, {{ String(timezone, 'UTC') }}), 3) AS interval,
{% if defined(groupBy) %} {% if groupBy == 'folder_id' %} folder_id {% else %} domain {% end %}, {% end %}
count(*) AS events
FROM
(
-- Click events
SELECT e.timestamp{% if defined(groupBy) %}, {% if groupBy == 'folder_id' %} m.folder_id {% else %} e.domain {% end %}, {% end %}
FROM dub_click_events_mv e
{% if groupBy == 'folder_id' or defined(folderId) %} LEFT JOIN dub_links_metadata_latest m ON m.link_id = e.link_id {% end %}
WHERE
workspace_id
= {{
String(
workspaceId,
'cl7pj5kq4006835rbjlt2ofka',
description="The unique ID for the workspace",
required=True,
)
}}
AND timestamp >= {{ DateTime(start, '2025-09-03 00:00:00') }}
AND timestamp < {{ DateTime(end, '2025-10-03 00:00:00') }}
{% if defined(folderId) %} AND folder_id = {{ folderId }} {% end %}
{% if defined(domain) %} AND domain IN {{ Array(domain, 'String') }} {% end %}
UNION ALL
-- Lead events
SELECT e.timestamp{% if defined(groupBy) %}, {% if groupBy == 'folder_id' %} m.folder_id {% else %} e.domain {% end %}, {% end %}
FROM dub_lead_events_mv e
{% if groupBy == 'folder_id' or defined(folderId) %} LEFT JOIN dub_links_metadata_latest m ON m.link_id = e.link_id {% end %}
WHERE
workspace_id = {{ String(workspaceId, 'cl7pj5kq4006835rbjlt2ofka') }}
AND timestamp >= {{ DateTime(start, '2025-09-03 00:00:00') }}
AND timestamp < {{ DateTime(end, '2025-10-03 00:00:00') }}
{% if defined(folderId) %} AND folder_id = {{ folderId }} {% end %}
{% if defined(domain) %} AND domain IN {{ Array(domain, 'String') }} {% end %}
UNION ALL
-- Sale events
SELECT e.timestamp{% if defined(groupBy) %}, {% if groupBy == 'folder_id' %} m.folder_id {% else %} e.domain {% end %}, {% end %}
FROM dub_sale_events_mv e
{% if groupBy == 'folder_id' or defined(folderId) %} LEFT JOIN dub_links_metadata_latest m ON m.link_id = e.link_id {% end %}
WHERE
workspace_id = {{ String(workspaceId, 'cl7pj5kq4006835rbjlt2ofka') }}
AND timestamp >= {{ DateTime(start, '2025-09-03 00:00:00') }}
AND timestamp < {{ DateTime(end, '2025-10-03 00:00:00') }}
{% if defined(folderId) %} AND folder_id = {{ folderId }} {% end %}
{% if defined(domain) %} AND domain IN {{ Array(domain, 'String') }} {% end %}
)
GROUP BY interval{% if defined(groupBy) %}, {% if groupBy == 'folder_id' %} folder_id {% else %} domain {% end %} {% end %}
ORDER BY interval
NODE usage_events
SQL >
%
SELECT
formatDateTime(interval, '%FT%T.000%z') as date,
{% if defined(groupBy) %} {% if groupBy == 'folder_id' %} folder_id {% else %} domain {% end %}, {% end %}
events as value
FROM day_intervals
LEFT JOIN usage_events_data USING interval
NODE usage_links_data
SQL >
%
SELECT
toDateTime64(toStartOfDay(timestamp, {{ String(timezone, 'UTC') }}), 3) AS interval,
{% if defined(groupBy) %} {% if groupBy == 'folder_id' %} folder_id {% else %} domain {% end %}, {% end %}
count(*) as links
FROM dub_links_metadata_latest FINAL
WHERE
workspace_id
= {{
String(
workspaceId,
'clrei1gld0002vs9mzn93p8ik',
description="The ID of the workspace",
required=True,
)
}}
AND toDateTime64(timestamp, 3) >= {{ DateTime(start, '2025-09-03 00:00:00') }}
AND toDateTime64(timestamp, 3) < {{ DateTime(end, '2025-10-03 00:00:00') }}
{% if defined(folderId) %} AND folder_id = {{ folderId }}
{% end %}
{% if defined(domain) %} AND domain IN {{ Array(domain, 'String') }} {% end %}
GROUP BY interval{% if defined(groupBy) %}, {% if groupBy == 'folder_id' %} folder_id {% else %} domain {% end %} {% end %}
ORDER BY interval
NODE usage_links
SQL >
%
SELECT
formatDateTime(interval, '%FT%T.000%z') as date,
{% if defined(groupBy) %} {% if groupBy == 'folder_id' %} folder_id {% else %} domain {% end %}, {% end %}
links as value
FROM day_intervals
LEFT JOIN usage_links_data USING interval
NODE endpoint
SQL >
%
SELECT * FROM {% if resource == 'events' %} usage_events {% else %} usage_links {% end %}
TYPE ENDPOINT