The modern link attribution platform. Loved by world-class engineering teams at Twilio, Zillow, Jobber, Vercel, Raycast, and more.
TypeScript clickhousemysqlnextjsopen-sourceplanetscaleprismatailwindcsstinybirdtypescriptupstashvercel
DESCRIPTION >
Top countries
TAGS "Dub Endpoints"
NODE workspace_links
SQL >
%
SELECT link_id
FROM dub_links_metadata_latest FINAL
WHERE
workspace_id
= {{
String(
workspaceId,
'cl7pj5kq4006835rbjlt2ofka',
description="The unique ID for the workspace",
required=True,
)
}}
AND deleted == 0
{% if defined(tenantId) %} AND tenant_id = {{ tenantId }} {% end %}
{% if defined(folderIds) %} AND folder_id IN {{ Array(folderIds, 'String') }}
{% elif defined(folderId) %} AND folder_id = {{ folderId }}
{% end %}
{% if defined(domain) %} AND domain IN {{ Array(domain, 'String') }} {% end %}
{% if defined(tagIds) %}
AND arrayIntersect(tag_ids, {{ Array(tagIds, 'String') }}) != []
{% end %}
{% if defined(root) %}
{% if Boolean(root) == 1 %} AND key = '_root' {% else %} AND key != '_root' {% end %}
{% end %}
NODE count_clicks
SQL >
%
SELECT 'count' as groupByField, COUNT(*) as clicks
FROM
{% if defined(customerId) %} dub_click_events_id
{% else %} dub_click_events_mv
{% end %}
{% if defined(customerId) %}
PREWHERE click_id IN (
SELECT DISTINCT click_id
FROM dub_lead_events_mv
WHERE customer_id = {{ String(customerId) }}
)
{% end %}
WHERE
true
{% if defined(workspaceId) %} AND workspace_id = {{ workspaceId }} {% end %}
{% if defined(linkIds) %} AND link_id IN {{ Array(linkIds, 'String') }}
{% elif defined(linkId) %} AND link_id = {{ linkId }}
{% elif defined(
tenantId
) or defined(folderIds) or defined(folderId) or defined(domain) or defined(
tagIds
) or defined(
root
) %} AND link_id in (SELECT link_id from workspace_links)
{% end %}
{% if defined(continent) %} AND continent = {{ continent }} {% end %}
{% if defined(country) %} AND country = {{ country }} {% end %}
{% if defined(region) %} AND region = {{ region }} {% end %}
{% if defined(city) %} AND city = {{ city }} {% end %}
{% if defined(device) %} AND device = {{ device }} {% end %}
{% if defined(browser) %} AND browser = {{ browser }} {% end %}
{% if defined(os) %} AND os = {{ os }} {% end %}
{% if defined(trigger) %} AND trigger = {{ trigger }} {% end %}
{% if defined(referer) %} AND referer = {{ referer }} {% end %}
{% if defined(refererUrl) %} AND splitByString('?', referer_url)[1] = {{ refererUrl }} {% end %}
{% if defined(utm_source) %}
AND url LIKE concat('%utm_source=', encodeURLFormComponent({{ String(utm_source) }}), '%')
{% end %}
{% if defined(utm_medium) %}
AND url LIKE concat('%utm_medium=', encodeURLFormComponent({{ String(utm_medium) }}), '%')
{% end %}
{% if defined(utm_campaign) %}
AND url
LIKE concat('%utm_campaign=', encodeURLFormComponent({{ String(utm_campaign) }}), '%')
{% end %}
{% if defined(utm_term) %}
AND url LIKE concat('%utm_term=', encodeURLFormComponent({{ String(utm_term) }}), '%')
{% end %}
{% if defined(utm_content) %}
AND url LIKE concat('%utm_content=', encodeURLFormComponent({{ String(utm_content) }}), '%')
{% end %}
{% if defined(url) %} AND splitByString('?', url)[1] = {{ url }} {% end %}
{% if defined(start) %} AND timestamp >= {{ DateTime64(start) }} {% end %}
{% if defined(end) %} AND timestamp <= {{ DateTime64(end) }} {% end %}
NODE count_leads
SQL >
%
SELECT 'count' as groupByField, COUNT(*) as leads
FROM dub_lead_events_mv
WHERE
true
{% if defined(workspaceId) %} AND workspace_id = {{ workspaceId }} {% end %}
{% if defined(linkIds) %} AND link_id IN {{ Array(linkIds, 'String') }}
{% elif defined(linkId) %} AND link_id = {{ linkId }}
{% elif defined(
tenantId
) or defined(folderIds) or defined(folderId) or defined(domain) or defined(
tagIds
) or defined(
root
) %} AND link_id in (SELECT link_id from workspace_links)
{% end %}
{% if defined(customerId) %} AND customer_id = {{ String(customerId) }} {% end %}
{% if defined(continent) %} AND continent = {{ continent }} {% end %}
{% if defined(country) %} AND country = {{ country }} {% end %}
{% if defined(region) %} AND region = {{ region }} {% end %}
{% if defined(city) %} AND city = {{ city }} {% end %}
{% if defined(device) %} AND device = {{ device }} {% end %}
{% if defined(browser) %} AND browser = {{ browser }} {% end %}
{% if defined(os) %} AND os = {{ os }} {% end %}
{% if defined(referer) %} AND referer = {{ referer }} {% end %}
{% if defined(refererUrl) %} AND splitByString('?', referer_url)[1] = {{ refererUrl }} {% end %}
{% if defined(utm_source) %}
AND url LIKE concat('%utm_source=', encodeURLFormComponent({{ String(utm_source) }}), '%')
{% end %}
{% if defined(utm_medium) %}
AND url LIKE concat('%utm_medium=', encodeURLFormComponent({{ String(utm_medium) }}), '%')
{% end %}
{% if defined(utm_campaign) %}
AND url
LIKE concat('%utm_campaign=', encodeURLFormComponent({{ String(utm_campaign) }}), '%')
{% end %}
{% if defined(utm_term) %}
AND url LIKE concat('%utm_term=', encodeURLFormComponent({{ String(utm_term) }}), '%')
{% end %}
{% if defined(utm_content) %}
AND url LIKE concat('%utm_content=', encodeURLFormComponent({{ String(utm_content) }}), '%')
{% end %}
{% if defined(url) %} AND splitByString('?', url)[1] = {{ url }} {% end %}
{% if defined(start) %} AND timestamp >= {{ DateTime(start) }} {% end %}
{% if defined(end) %} AND timestamp <= {{ DateTime(end) }} {% end %}
{% if defined(filters) %}
{% for item in JSON(filters, '[]') %}
{% if item.get('operand', '').startswith('metadata.') %}
{% set metadataKey = item.get('operand', '').split('.')[1] %}
{% set operator = item.get('operator', 'equals') %}
{% set value = item.get('value', '') %}
{% if operator == 'equals' %}
AND JSONExtractString(metadata, {{ metadataKey }}) = {{ value }}
{% elif operator == 'notEquals' %}
AND JSONExtractString(metadata, {{ metadataKey }}) != {{ value }}
{% elif operator == 'greaterThan' %}
AND JSONExtractString(metadata, {{ metadataKey }}) > {{ value }}
{% elif operator == 'lessThan' %}
AND JSONExtractString(metadata, {{ metadataKey }}) < {{ value }}
{% elif operator == 'greaterThanOrEqual' %}
AND JSONExtractString(metadata, {{ metadataKey }}) >= {{ value }}
{% elif operator == 'lessThanOrEqual' %}
AND JSONExtractString(metadata, {{ metadataKey }}) <= {{ value }}
{% end %}
{% end %}
{% end %}
{% end %}
NODE first_sale_by_link_customer
SQL >
%
SELECT workspace_id, link_id, customer_id, minMerge(first_sale_state) AS first_sale_ts
FROM dub_first_sale_mv
WHERE
workspace_id
= {{
String(
workspaceId,
'cl7pj5kq4006835rbjlt2ofka',
description="The unique ID for the workspace",
required=True,
)
}}
GROUP BY workspace_id, link_id, customer_id
NODE count_sales
SQL >
%
SELECT 'count' as groupByField, sales, amount, amount AS saleAmount
FROM
(
SELECT
count() AS sales,
sum(amount) AS amount
FROM
(
SELECT
se.amount,
if(
isNull(fs.first_sale_ts),
'new',
if(
toUnixTimestamp64Milli(se.timestamp) = toUnixTimestamp64Milli(fs.first_sale_ts),
'new',
'recurring'
)
) AS sale_type
FROM dub_sale_events_mv AS se
LEFT JOIN first_sale_by_link_customer AS fs
USING (workspace_id, link_id, customer_id)
WHERE
true
{% if defined(workspaceId) %} AND se.workspace_id = {{ workspaceId }} {% end %}
{% if defined(linkIds) %} AND se.link_id IN {{ Array(linkIds, 'String') }}
{% elif defined(linkId) %} AND se.link_id = {{ linkId }}
{% elif defined(
tenantId
) or defined(folderIds) or defined(folderId) or defined(domain) or defined(
tagIds
) or defined(
root
) %} AND se.link_id in (SELECT link_id from workspace_links)
{% end %}
{% if defined(customerId) %} AND se.customer_id = {{ String(customerId) }} {% end %}
{% if defined(continent) %} AND se.continent = {{ continent }} {% end %}
{% if defined(country) %} AND se.country = {{ country }} {% end %}
{% if defined(region) %} AND se.region = {{ region }} {% end %}
{% if defined(city) %} AND se.city = {{ city }} {% end %}
{% if defined(device) %} AND se.device = {{ device }} {% end %}
{% if defined(browser) %} AND se.browser = {{ browser }} {% end %}
{% if defined(os) %} AND se.os = {{ os }} {% end %}
{% if defined(referer) %} AND se.referer = {{ referer }} {% end %}
{% if defined(refererUrl) %}
AND splitByString('?', se.referer_url)[1] = {{ refererUrl }}
{% end %}
{% if defined(utm_source) %}
AND se.url
LIKE concat('%utm_source=', encodeURLFormComponent({{ String(utm_source) }}), '%')
{% end %}
{% if defined(utm_medium) %}
AND se.url
LIKE concat('%utm_medium=', encodeURLFormComponent({{ String(utm_medium) }}), '%')
{% end %}
{% if defined(utm_campaign) %}
AND se.url LIKE concat(
'%utm_campaign=', encodeURLFormComponent({{ String(utm_campaign) }}), '%'
)
{% end %}
{% if defined(utm_term) %}
AND se.url
LIKE concat('%utm_term=', encodeURLFormComponent({{ String(utm_term) }}), '%')
{% end %}
{% if defined(utm_content) %}
AND se.url
LIKE concat('%utm_content=', encodeURLFormComponent({{ String(utm_content) }}), '%')
{% end %}
{% if defined(url) %} AND splitByString('?', se.url)[1] = {{ url }} {% end %}
{% if defined(start) %} AND se.timestamp >= {{ DateTime(start) }} {% end %}
{% if defined(end) %} AND se.timestamp <= {{ DateTime(end) }} {% end %}
{% if defined(filters) %}
{% for item in JSON(filters, '[]') %}
{% if item.get('operand', '').startswith('metadata.') %}
{% set metadataKey = item.get('operand', '').split('.')[1] %}
{% set operator = item.get('operator', 'equals') %}
{% set value = item.get('value', '') %}
{% if operator == 'equals' %}
AND JSONExtractString(se.metadata, {{ metadataKey }}) = {{ value }}
{% elif operator == 'notEquals' %}
AND JSONExtractString(se.metadata, {{ metadataKey }}) != {{ value }}
{% elif operator == 'greaterThan' %}
AND JSONExtractString(se.metadata, {{ metadataKey }}) > {{ value }}
{% elif operator == 'lessThan' %}
AND JSONExtractString(se.metadata, {{ metadataKey }}) < {{ value }}
{% elif operator == 'greaterThanOrEqual' %}
AND JSONExtractString(se.metadata, {{ metadataKey }}) >= {{ value }}
{% elif operator == 'lessThanOrEqual' %}
AND JSONExtractString(se.metadata, {{ metadataKey }}) <= {{ value }}
{% end %}
{% end %}
{% end %}
{% end %}
) AS typed
WHERE
true
{% if defined(saleType) %} AND typed.sale_type = {{ String(saleType) }} {% end %}
) AS subquery
NODE count_composite
SQL >
%
SELECT 'count' as groupByField, clicks, leads, sales, amount, saleAmount
FROM count_clicks, count_leads, count_sales
NODE endpoint
SQL >
%
SELECT *
FROM
{% if eventType == 'clicks' %} count_clicks
{% elif eventType == 'leads' %} count_leads
{% elif eventType == 'sales' %} count_sales
{% elif eventType == 'composite' %} count_composite
{% else %} count_clicks
{% end %}
TYPE ENDPOINT