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) %}
{% if defined(tenantIdOperator) and String(tenantIdOperator) == 'NOT IN' %}
AND tenant_id NOT IN {{ Array(tenantId, 'String') }}
{% else %} AND tenant_id IN {{ Array(tenantId, 'String') }}
{% end %}
{% end %}
{% if defined(domain) %}
{% if defined(domainOperator) and String(domainOperator) == 'NOT IN' %}
AND domain NOT IN {{ Array(domain, 'String') }}
{% else %} AND domain IN {{ Array(domain, 'String') }}
{% end %}
{% end %}
{% if defined(tagId) %}
{% if defined(tagIdOperator) and String(tagIdOperator) == 'NOT IN' %}
AND length(arrayFilter(x -> x NOT IN {{ Array(tagId, 'String') }}, tag_ids))
= length(tag_ids)
{% else %} AND arrayIntersect(tag_ids, {{ Array(tagId, 'String') }}) != []
{% end %}
{% end %}
{% if defined(folderId) %}
{% if defined(folderIdOperator) and String(folderIdOperator) == 'NOT IN' %}
AND folder_id NOT IN {{ Array(folderId, 'String') }}
{% else %} AND folder_id IN {{ Array(folderId, 'String') }}
{% end %}
{% 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(linkId) %}
{% if defined(linkIdOperator) and String(linkIdOperator) == 'NOT IN' %}
AND link_id NOT IN {{ Array(linkId, 'String') }}
{% else %}
AND link_id IN {{ Array(linkId, 'String') }}
{% end %}
{% elif defined(
tenantId
) or defined(folderId) or defined(domain) or defined(
tagId
) 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 %}
{% if defined(filters) %}
{% for item in JSON(filters, '[]') %}
{% if item.get('field', '') %}
{% set field = item.get('field', '') %}
{% set operator = item.get('operator', 'IN') %}
{% set values = item.get('values', []) %}
{% if field == 'country' %}
{% if operator == 'IN' %} AND country IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND country NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'city' %}
{% if operator == 'IN' %} AND city IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND city NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'continent' %}
{% if operator == 'IN' %} AND continent IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND continent NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'device' %}
{% if operator == 'IN' %} AND device IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND device NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'browser' %}
{% if operator == 'IN' %} AND browser IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND browser NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'os' %}
{% if operator == 'IN' %} AND os IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND os NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'trigger' %}
{% if operator == 'IN' %} AND trigger IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND trigger NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'referer' %}
{% if operator == 'IN' %} AND referer IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND referer NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'refererUrl' %}
{% if operator == 'IN' %} AND splitByString('?', referer_url)[1] IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND splitByString('?', referer_url)[1] NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'url' %}
{% if operator == 'IN' %} AND splitByString('?', url)[1] IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND splitByString('?', url)[1] NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'utm_source' %}
{% if operator == 'IN' %}
AND (0{% for val in values %} OR url LIKE concat('%', 'utm_source=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% elif operator == 'NOT IN' %}
AND NOT (0{% for val in values %} OR url LIKE concat('%', 'utm_source=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% end %}
{% elif field == 'utm_medium' %}
{% if operator == 'IN' %}
AND (0{% for val in values %} OR url LIKE concat('%', 'utm_medium=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% elif operator == 'NOT IN' %}
AND NOT (0{% for val in values %} OR url LIKE concat('%', 'utm_medium=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% end %}
{% elif field == 'utm_campaign' %}
{% if operator == 'IN' %}
AND (0{% for val in values %} OR url LIKE concat('%', 'utm_campaign=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% elif operator == 'NOT IN' %}
AND NOT (0{% for val in values %} OR url LIKE concat('%', 'utm_campaign=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% end %}
{% elif field == 'utm_term' %}
{% if operator == 'IN' %}
AND (0{% for val in values %} OR url LIKE concat('%', 'utm_term=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% elif operator == 'NOT IN' %}
AND NOT (0{% for val in values %} OR url LIKE concat('%', 'utm_term=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% end %}
{% elif field == 'utm_content' %}
{% if operator == 'IN' %}
AND (0{% for val in values %} OR url LIKE concat('%', 'utm_content=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% elif operator == 'NOT IN' %}
AND NOT (0{% for val in values %} OR url LIKE concat('%', 'utm_content=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% end %}
{% elif field == 'domain' %}
{% if operator == 'IN' %} AND link_id IN (SELECT link_id FROM workspace_links WHERE domain IN {{ Array(values, 'String') }})
{% elif operator == 'NOT IN' %} AND link_id NOT IN (SELECT link_id FROM workspace_links WHERE domain IN {{ Array(values, 'String') }})
{% end %}
{% elif field == 'tagId' %}
{% if operator == 'IN' %} AND link_id IN (SELECT link_id FROM workspace_links WHERE arrayIntersect(tag_ids, {{ Array(values, 'String') }}) != [])
{% elif operator == 'NOT IN' %} AND link_id NOT IN (SELECT link_id FROM workspace_links WHERE arrayIntersect(tag_ids, {{ Array(values, 'String') }}) != [])
{% end %}
{% elif field == 'folderId' %}
{% if operator == 'IN' %} AND link_id IN (SELECT link_id FROM workspace_links WHERE folder_id IN {{ Array(values, 'String') }})
{% elif operator == 'NOT IN' %} AND link_id NOT IN (SELECT link_id FROM workspace_links WHERE folder_id IN {{ Array(values, 'String') }})
{% end %}
{% end %}
{% end %}
{% 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(linkId) %}
{% if defined(linkIdOperator) and String(linkIdOperator) == 'NOT IN' %}
AND link_id NOT IN {{ Array(linkId, 'String') }}
{% else %}
AND link_id IN {{ Array(linkId, 'String') }}
{% end %}
{% elif defined(
tenantId
) or defined(folderId) or defined(domain) or defined(
tagId
) 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(eventName) %} AND event_name = {{ eventName }} {% 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 toFloat64OrNull(JSONExtractString(metadata, {{ metadataKey }})) > toFloat64OrNull({{ value }})
{% elif operator == 'lessThan' %}
AND toFloat64OrNull(JSONExtractString(metadata, {{ metadataKey }})) < toFloat64OrNull({{ value }})
{% elif operator == 'greaterThanOrEqual' %}
AND toFloat64OrNull(JSONExtractString(metadata, {{ metadataKey }})) >= toFloat64OrNull({{ value }})
{% elif operator == 'lessThanOrEqual' %}
AND toFloat64OrNull(JSONExtractString(metadata, {{ metadataKey }})) <= toFloat64OrNull({{ value }})
{% end %}
{% elif item.get('field', '') %}
{% set field = item.get('field', '') %}
{% set operator = item.get('operator', 'IN') %}
{% set values = item.get('values', []) %}
{% if field == 'country' %}
{% if operator == 'IN' %} AND country IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND country NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'city' %}
{% if operator == 'IN' %} AND city IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND city NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'continent' %}
{% if operator == 'IN' %} AND continent IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND continent NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'device' %}
{% if operator == 'IN' %} AND device IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND device NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'browser' %}
{% if operator == 'IN' %} AND browser IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND browser NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'os' %}
{% if operator == 'IN' %} AND os IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND os NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'trigger' %}
{% if operator == 'IN' %} AND trigger IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND trigger NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'eventName' %}
{% if operator == 'IN' %} AND event_name IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND event_name NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'referer' %}
{% if operator == 'IN' %} AND referer IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND referer NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'refererUrl' %}
{% if operator == 'IN' %} AND splitByString('?', referer_url)[1] IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND splitByString('?', referer_url)[1] NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'url' %}
{% if operator == 'IN' %} AND splitByString('?', url)[1] IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND splitByString('?', url)[1] NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'utm_source' %}
{% if operator == 'IN' %}
AND (0{% for val in values %} OR url LIKE concat('%', 'utm_source=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% elif operator == 'NOT IN' %}
AND NOT (0{% for val in values %} OR url LIKE concat('%', 'utm_source=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% end %}
{% elif field == 'utm_medium' %}
{% if operator == 'IN' %}
AND (0{% for val in values %} OR url LIKE concat('%', 'utm_medium=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% elif operator == 'NOT IN' %}
AND NOT (0{% for val in values %} OR url LIKE concat('%', 'utm_medium=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% end %}
{% elif field == 'utm_campaign' %}
{% if operator == 'IN' %}
AND (0{% for val in values %} OR url LIKE concat('%', 'utm_campaign=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% elif operator == 'NOT IN' %}
AND NOT (0{% for val in values %} OR url LIKE concat('%', 'utm_campaign=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% end %}
{% elif field == 'utm_term' %}
{% if operator == 'IN' %}
AND (0{% for val in values %} OR url LIKE concat('%', 'utm_term=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% elif operator == 'NOT IN' %}
AND NOT (0{% for val in values %} OR url LIKE concat('%', 'utm_term=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% end %}
{% elif field == 'utm_content' %}
{% if operator == 'IN' %}
AND (0{% for val in values %} OR url LIKE concat('%', 'utm_content=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% elif operator == 'NOT IN' %}
AND NOT (0{% for val in values %} OR url LIKE concat('%', 'utm_content=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% end %}
{% elif field == 'domain' %}
{% if operator == 'IN' %} AND link_id IN (SELECT link_id FROM workspace_links WHERE domain IN {{ Array(values, 'String') }})
{% elif operator == 'NOT IN' %} AND link_id NOT IN (SELECT link_id FROM workspace_links WHERE domain IN {{ Array(values, 'String') }})
{% end %}
{% elif field == 'tagId' %}
{% if operator == 'IN' %} AND link_id IN (SELECT link_id FROM workspace_links WHERE arrayIntersect(tag_ids, {{ Array(values, 'String') }}) != [])
{% elif operator == 'NOT IN' %} AND link_id NOT IN (SELECT link_id FROM workspace_links WHERE arrayIntersect(tag_ids, {{ Array(values, 'String') }}) != [])
{% end %}
{% elif field == 'folderId' %}
{% if operator == 'IN' %} AND link_id IN (SELECT link_id FROM workspace_links WHERE folder_id IN {{ Array(values, 'String') }})
{% elif operator == 'NOT IN' %} AND link_id NOT IN (SELECT link_id FROM workspace_links WHERE folder_id IN {{ Array(values, 'String') }})
{% end %}
{% 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(linkId) %}
{% if defined(linkIdOperator) and String(linkIdOperator) == 'NOT IN' %}
AND se.link_id NOT IN {{ Array(linkId, 'String') }}
{% else %}
AND se.link_id IN {{ Array(linkId, 'String') }}
{% end %}
{% elif defined(
tenantId
) or defined(folderId) or defined(domain) or defined(
tagId
) 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(eventName) %} AND se.event_name = {{ eventName }} {% 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 toFloat64OrNull(JSONExtractString(se.metadata, {{ metadataKey }})) > toFloat64OrNull({{ value }})
{% elif operator == 'lessThan' %}
AND toFloat64OrNull(JSONExtractString(se.metadata, {{ metadataKey }})) < toFloat64OrNull({{ value }})
{% elif operator == 'greaterThanOrEqual' %}
AND toFloat64OrNull(JSONExtractString(se.metadata, {{ metadataKey }})) >= toFloat64OrNull({{ value }})
{% elif operator == 'lessThanOrEqual' %}
AND toFloat64OrNull(JSONExtractString(se.metadata, {{ metadataKey }})) <= toFloat64OrNull({{ value }})
{% end %}
{% elif item.get('field', '') %}
{% set field = item.get('field', '') %}
{% set operator = item.get('operator', 'IN') %}
{% set values = item.get('values', []) %}
{% if field == 'country' %}
{% if operator == 'IN' %} AND se.country IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND se.country NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'city' %}
{% if operator == 'IN' %} AND se.city IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND se.city NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'continent' %}
{% if operator == 'IN' %} AND se.continent IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND se.continent NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'device' %}
{% if operator == 'IN' %} AND se.device IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND se.device NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'browser' %}
{% if operator == 'IN' %} AND se.browser IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND se.browser NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'os' %}
{% if operator == 'IN' %} AND se.os IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND se.os NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'trigger' %}
{% if operator == 'IN' %} AND se.trigger IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND se.trigger NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'eventName' %}
{% if operator == 'IN' %} AND se.event_name IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND se.event_name NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'referer' %}
{% if operator == 'IN' %} AND se.referer IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND se.referer NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'refererUrl' %}
{% if operator == 'IN' %} AND splitByString('?', se.referer_url)[1] IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND splitByString('?', se.referer_url)[1] NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'url' %}
{% if operator == 'IN' %} AND splitByString('?', se.url)[1] IN {{ Array(values, 'String') }}
{% elif operator == 'NOT IN' %} AND splitByString('?', se.url)[1] NOT IN {{ Array(values, 'String') }}
{% end %}
{% elif field == 'utm_source' %}
{% if operator == 'IN' %}
AND (0{% for val in values %} OR se.url LIKE concat('%', 'utm_source=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% elif operator == 'NOT IN' %}
AND NOT (0{% for val in values %} OR se.url LIKE concat('%', 'utm_source=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% end %}
{% elif field == 'utm_medium' %}
{% if operator == 'IN' %}
AND (0{% for val in values %} OR se.url LIKE concat('%', 'utm_medium=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% elif operator == 'NOT IN' %}
AND NOT (0{% for val in values %} OR se.url LIKE concat('%', 'utm_medium=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% end %}
{% elif field == 'utm_campaign' %}
{% if operator == 'IN' %}
AND (0{% for val in values %} OR se.url LIKE concat('%', 'utm_campaign=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% elif operator == 'NOT IN' %}
AND NOT (0{% for val in values %} OR se.url LIKE concat('%', 'utm_campaign=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% end %}
{% elif field == 'utm_term' %}
{% if operator == 'IN' %}
AND (0{% for val in values %} OR se.url LIKE concat('%', 'utm_term=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% elif operator == 'NOT IN' %}
AND NOT (0{% for val in values %} OR se.url LIKE concat('%', 'utm_term=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% end %}
{% elif field == 'utm_content' %}
{% if operator == 'IN' %}
AND (0{% for val in values %} OR se.url LIKE concat('%', 'utm_content=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% elif operator == 'NOT IN' %}
AND NOT (0{% for val in values %} OR se.url LIKE concat('%', 'utm_content=', encodeURLFormComponent({{ String(val) }}), '%'){% end %})
{% end %}
{% elif field == 'domain' %}
{% if operator == 'IN' %} AND se.link_id IN (SELECT link_id FROM workspace_links WHERE domain IN {{ Array(values, 'String') }})
{% elif operator == 'NOT IN' %} AND se.link_id NOT IN (SELECT link_id FROM workspace_links WHERE domain IN {{ Array(values, 'String') }})
{% end %}
{% elif field == 'tagId' %}
{% if operator == 'IN' %} AND se.link_id IN (SELECT link_id FROM workspace_links WHERE arrayIntersect(tag_ids, {{ Array(values, 'String') }}) != [])
{% elif operator == 'NOT IN' %} AND se.link_id NOT IN (SELECT link_id FROM workspace_links WHERE arrayIntersect(tag_ids, {{ Array(values, 'String') }}) != [])
{% end %}
{% elif field == 'folderId' %}
{% if operator == 'IN' %} AND se.link_id IN (SELECT link_id FROM workspace_links WHERE folder_id IN {{ Array(values, 'String') }})
{% elif operator == 'NOT IN' %} AND se.link_id NOT IN (SELECT link_id FROM workspace_links WHERE folder_id IN {{ Array(values, 'String') }})
{% end %}
{% 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