Skip to content

armgit / dub

Public Imported from GitHub
0

The modern link attribution platform. Loved by world-class engineering teams at Twilio, Zillow, Jobber, Vercel, Raycast, and more.

36.7 KB
Raw Blame
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