priv/data_migrations/AcquisitionChannel/sql/acquisition_channel_functions.sql.eex

CREATE TABLE IF NOT EXISTS acquisition_channel_source_category
<%= @on_cluster_statement %>
(
    referrer_source String,
    category LowCardinality(String)
)
<%= if @on_cluster_statement != "" do %>
ENGINE = ReplicatedMergeTree('/clickhouse/{cluster}/tables/{shard}/plausible_prod/acquisition_channel_source_category', '{replica}')
<% else %>
ENGINE = MergeTree()
<% end %>
ORDER BY referrer_source
<%= @table_settings %>;

TRUNCATE TABLE acquisition_channel_source_category SETTINGS alter_sync=2;

INSERT INTO acquisition_channel_source_category(referrer_source, category)
SELECT t.1 AS referrer_source, t.2 AS category
FROM (
    SELECT arrayJoin({source_categories:Array(Tuple(String, String))}) AS t
)
SETTINGS insert_quorum = <%= @insert_quorum %>;

CREATE OR REPLACE DICTIONARY acquisition_channel_source_category_dict
<%= @on_cluster_statement %>
(
    `referrer_source` String,
    `category` String
)
PRIMARY KEY referrer_source
SOURCE(CLICKHOUSE(TABLE acquisition_channel_source_category <%= @dictionary_connection_params %>))
LIFETIME(0)
LAYOUT(hashed());

CREATE TABLE IF NOT EXISTS acquisition_channel_paid_sources
<%= @on_cluster_statement %>
(
    referrer_source String
)
<%= if @on_cluster_statement != "" do %>
ENGINE = ReplicatedMergeTree('/clickhouse/{cluster}/tables/{shard}/plausible_prod/acquisition_channel_paid_sources', '{replica}')
<% else %>
ENGINE = MergeTree()
<% end %>
ORDER BY referrer_source
<%= @table_settings %>;

TRUNCATE TABLE acquisition_channel_paid_sources SETTINGS alter_sync=2;

INSERT INTO acquisition_channel_paid_sources(referrer_source)
SELECT arrayJoin({paid_sources:Array(String)}) AS referrer_source
SETTINGS insert_quorum = <%= @insert_quorum %>;

CREATE OR REPLACE DICTIONARY acquisition_channel_paid_sources_dict
<%= @on_cluster_statement %>
(
    `referrer_source` String
)
PRIMARY KEY referrer_source
SOURCE(CLICKHOUSE(TABLE acquisition_channel_paid_sources <%= @dictionary_connection_params %>))
LIFETIME(0)
LAYOUT(hashed());

CREATE OR REPLACE FUNCTION acquisition_channel_has_category_shopping <%= @on_cluster_statement %> AS
(referrer_source) ->
    dictGet('acquisition_channel_source_category_dict', 'category', referrer_source) = 'SOURCE_CATEGORY_SHOPPING';

CREATE OR REPLACE FUNCTION acquisition_channel_has_category_social <%= @on_cluster_statement %> AS
(referrer_source) ->
    dictGet('acquisition_channel_source_category_dict', 'category', referrer_source) = 'SOURCE_CATEGORY_SOCIAL';

CREATE OR REPLACE FUNCTION acquisition_channel_has_category_video <%= @on_cluster_statement %> AS
(referrer_source) ->
    dictGet('acquisition_channel_source_category_dict', 'category', referrer_source) = 'SOURCE_CATEGORY_VIDEO';

CREATE OR REPLACE FUNCTION acquisition_channel_has_category_search <%= @on_cluster_statement %> AS
(referrer_source) ->
    dictGet('acquisition_channel_source_category_dict', 'category', referrer_source) = 'SOURCE_CATEGORY_SEARCH';

CREATE OR REPLACE FUNCTION acquisition_channel_has_category_email <%= @on_cluster_statement %> AS
(referrer_source) ->
    dictGet('acquisition_channel_source_category_dict', 'category', referrer_source) = 'SOURCE_CATEGORY_EMAIL';

CREATE OR REPLACE FUNCTION acquisition_channel_has_category_ai_assistants <%= @on_cluster_statement %> AS
(referrer_source) ->
    dictGet('acquisition_channel_source_category_dict', 'category', referrer_source) = 'SOURCE_CATEGORY_AI_ASSISTANTS';

CREATE OR REPLACE FUNCTION acquisition_channel_paid_utm_source <%= @on_cluster_statement %> AS
(referrer_source) ->
    dictHas('acquisition_channel_paid_sources_dict', referrer_source);

CREATE OR REPLACE FUNCTION acquisition_channel_cross_network <%= @on_cluster_statement %> AS
(utm_campaign) ->
    position(utm_campaign, 'cross-network') > 0;

CREATE OR REPLACE FUNCTION acquisition_channel_paid_shopping <%= @on_cluster_statement %> AS
(referrer_source, utm_medium, utm_campaign) ->
    acquisition_channel_paid_medium(utm_medium) AND
    (
        acquisition_channel_has_category_shopping(referrer_source)
        OR acquisition_channel_shopping_campaign(utm_campaign)
    );

CREATE OR REPLACE FUNCTION acquisition_channel_paid_search <%= @on_cluster_statement %> AS
(referrer_source, utm_medium, utm_source, click_id_param) ->
    (
        acquisition_channel_has_category_search(referrer_source)
        AND (
            acquisition_channel_paid_medium(utm_medium)
            OR acquisition_channel_paid_utm_source(utm_source)
        )
    ) OR (
        referrer_source == 'google'
        AND click_id_param == 'gclid'
    ) OR (
        referrer_source == 'bing'
        AND click_id_param == 'msclkid'
    );

CREATE OR REPLACE FUNCTION acquisition_channel_paid_social <%= @on_cluster_statement %> AS
(referrer_source, utm_medium, utm_source) ->
    acquisition_channel_has_category_social(referrer_source)
    AND (
        acquisition_channel_paid_medium(utm_medium)
        OR acquisition_channel_paid_utm_source(utm_source)
    );

CREATE OR REPLACE FUNCTION acquisition_channel_paid_video <%= @on_cluster_statement %> AS
(referrer_source, utm_medium, utm_source) ->
    acquisition_channel_has_category_video(referrer_source)
    AND (
        acquisition_channel_paid_medium(utm_medium)
        OR acquisition_channel_paid_utm_source(utm_source)
    );

CREATE OR REPLACE FUNCTION acquisition_channel_display <%= @on_cluster_statement %> AS
(utm_medium) ->
    utm_medium IN ('display', 'banner', 'expandable', 'interstitial', 'cpm');

CREATE OR REPLACE FUNCTION acquisition_channel_paid_medium <%= @on_cluster_statement %> AS
(utm_medium) ->
    match(utm_medium, '^(.*cp.*|ppc|retargeting|paid.*)$');

CREATE OR REPLACE FUNCTION acquisition_channel_shopping_campaign <%= @on_cluster_statement %> AS
(utm_campaign) ->
    match(utm_campaign, '^(.*(([^a-df-z]|^)shop|shopping).*)$');

CREATE OR REPLACE FUNCTION acquisition_channel_organic_shopping <%= @on_cluster_statement %> AS
(referrer_source, utm_campaign) ->
    acquisition_channel_has_category_shopping(referrer_source)
    OR acquisition_channel_shopping_campaign(utm_campaign);

CREATE OR REPLACE FUNCTION acquisition_channel_organic_social <%= @on_cluster_statement %> AS
(referrer_source, utm_medium) ->
    acquisition_channel_has_category_social(referrer_source)
    OR utm_medium IN (
        'social',
        'social-network',
        'social-media',
        'sm',
        'social network',
        'social media'
    );

CREATE OR REPLACE FUNCTION acquisition_channel_organic_video <%= @on_cluster_statement %> AS
(referrer_source, utm_medium) ->
    acquisition_channel_has_category_video(referrer_source) OR position(utm_medium, 'video') > 0;

CREATE OR REPLACE FUNCTION acquisition_channel_email <%= @on_cluster_statement %> AS
(referrer_source, utm_source, utm_medium) ->
   acquisition_channel_has_category_email(referrer_source)
   OR acquisition_channel_contains_email(utm_source)
   OR acquisition_channel_contains_email(utm_medium);

CREATE OR REPLACE FUNCTION acquisition_channel_affiliates <%= @on_cluster_statement %> AS
(utm_medium) ->
    utm_medium == 'affiliate';

CREATE OR REPLACE FUNCTION acquisition_channel_audio <%= @on_cluster_statement %> AS
(utm_medium) ->
    utm_medium == 'audio';

CREATE OR REPLACE FUNCTION acquisition_channel_sms <%= @on_cluster_statement %> AS
(column) ->
    column == 'sms';

CREATE OR REPLACE FUNCTION acquisition_channel_mobile_push_notifications <%= @on_cluster_statement %> AS
(utm_medium, referrer_source) ->
    endsWith(utm_medium, 'push') OR
    multiSearchAny(utm_medium, ['mobile', 'notification']) OR
    referrer_source == 'firebase';

CREATE OR REPLACE FUNCTION acquisition_channel_referral <%= @on_cluster_statement %> AS
(utm_medium, referrer_source) ->
    utm_medium IN ('referral', 'app', 'link') OR
    not empty(referrer_source);

CREATE OR REPLACE FUNCTION acquisition_channel_contains_email <%= @on_cluster_statement %> AS
(column) ->
    match(column, 'e[-_ ]?mail|newsletter');

CREATE OR REPLACE FUNCTION acquisition_channel <%= @on_cluster_statement %> AS
(referrer_source, utm_medium, utm_campaign, utm_source, click_id_param) ->
    acquisition_channel_lowered(
        lower(referrer_source),
        lower(utm_medium),
        lower(utm_campaign),
        lower(utm_source),
        click_id_param
    );

CREATE OR REPLACE FUNCTION acquisition_channel_lowered <%= @on_cluster_statement %> AS
(referrer_source, utm_medium, utm_campaign, utm_source, click_id_param) ->
    multiIf(
        acquisition_channel_cross_network(utm_campaign), 'Cross-network',
        acquisition_channel_display(utm_medium), 'Display',
        acquisition_channel_paid_shopping(referrer_source, utm_medium, utm_campaign), 'Paid Shopping',
        acquisition_channel_paid_search(referrer_source, utm_medium, utm_source, click_id_param), 'Paid Search',
        acquisition_channel_paid_social(referrer_source, utm_medium, utm_source), 'Paid Social',
        acquisition_channel_paid_video(referrer_source, utm_medium, utm_source), 'Paid Video',
        acquisition_channel_paid_medium(utm_medium), 'Paid Other',
        acquisition_channel_organic_shopping(referrer_source, utm_campaign), 'Organic Shopping',
        acquisition_channel_organic_social(referrer_source, utm_medium), 'Organic Social',
        acquisition_channel_organic_video(referrer_source, utm_medium), 'Organic Video',
        acquisition_channel_has_category_ai_assistants(referrer_source), 'AI Assistants',
        acquisition_channel_has_category_search(referrer_source), 'Organic Search',
        acquisition_channel_email(referrer_source, utm_source, utm_medium), 'Email',
        acquisition_channel_affiliates(utm_medium), 'Affiliates',
        acquisition_channel_audio(utm_medium), 'Audio',
        acquisition_channel_sms(utm_source), 'SMS',
        acquisition_channel_sms(utm_medium), 'SMS',
        acquisition_channel_mobile_push_notifications(utm_medium, referrer_source), 'Mobile Push Notifications',
        acquisition_channel_referral(utm_medium, referrer_source), 'Referral',
        'Direct'
    );