avia_main_metrics_by_markets_platforms_day
unknownsql
2 months ago
46 kB
3
Indexable
Never
create schema if not exists {{ params.schema }};
create table if not exists {{ params.schema }}.{{ params.relation }} (
platform_type string,
platform string,
brand string,
market string,
source string,
searches int,
clicks int,
bookings int,
profit decimal(38, 2),
profit_usd decimal(38, 2),
price decimal(38, 2),
price_usd decimal(38, 2),
passengers int,
cpc_profit decimal(38, 2),
cpc_profit_usd decimal(38, 2),
cpc_clicks int,
sessions int,
sessions_with_click int,
sessions_with_search int,
installs int,
searches_domestic int,
searches_international int,
clicks_domestic int,
clicks_international int,
bookings_domestic int,
bookings_international int,
profit_domestic decimal(38, 2),
profit_international decimal(38, 2),
price_domestic decimal(38, 2),
price_international decimal(38, 2),
profit_usd_domestic decimal(38, 2),
profit_usd_international decimal(38, 2),
price_usd_domestic decimal(38, 2),
price_usd_international decimal(38, 2),
passengers_domestic int,
passengers_international int,
cpc_profit_domestic decimal(38, 2),
cpc_profit_international decimal(38, 2),
cpc_profit_usd_domestic decimal(38, 2),
cpc_profit_usd_international decimal(38, 2),
cpc_clicks_domestic int,
cpc_clicks_international int
)
partitioned by (pdate string)
stored as parquet;
alter table {{ params.schema }}.{{ params.relation }}
add column if not exists total_ses_length_min decimal(38, 2);
alter table {{ params.schema }}.{{ params.relation }}
add column if not exists searches_round_trip decimal(38, 2);
alter table {{ params.schema }}.{{ params.relation }}
add column if not exists searches_with_children decimal(38, 2);
alter table {{ params.schema }}.{{ params.relation }}
add column if not exists searches_with_auth decimal(38, 2);
with
searches_last_market as (
select *
from
(
select
market,
coalesce(mobile_token, auid) as user_id,
row_number() over (
partition by coalesce(mobile_token, auid)
order by created_at desc
) = 1 as latest
from avia.searches
where
pdate
between '{{ macros.ds_add(ds, params.partition_lookback) }}'
and '{{ ds }}'
and coalesce(mobile_token, auid) is not null
and coalesce(mobile_token, auid) != ''
) as t
where latest
),
sess_web_last_market as (
select *
from
(
select
user_id,
market,
row_number() over (
partition by user_id order by start_ts desc
) = 1 as latest
from analytics.sessions_web
where
pdate between
'{{ macros.ds_add(ds, params.partition_lookback) }}'
and '{{ ds }}'
) as t
where latest
),
sess_mobile_last_market as (
select *
from
(
select
user_id,
lower(ip_country_iata) as market,
row_number() over (
partition by user_id order by start_dts desc
) = 1 as latest
from analytics.sessions_mobile
where
pdate between
'{{ macros.ds_add(ds, params.partition_lookback) }}'
and '{{ ds }}'
) as t
where latest
),
sess_web as (
select
pdate,
user_id,
decode(
platform,
'android_web',
'mobile_web',
'ios_web',
'mobile_web',
'desktop',
'web',
'unknown',
'other',
platform
) as platform_type,
case
when user_agent
iregexp '(macintosh|win64|windows|x86_64|i686|amd64)'
then 'desktop'
when user_agent iregexp '(ipad|iphone)'
then 'ios_web'
when user_agent iregexp 'android'
then 'android_web'
else 'other'
end as platform,
case
when brand = 'WA' or page_url iregexp 'wayaway'
then 'wayaway'
when page_url iregexp 'aviasales'
then 'aviasales'
when page_url iregexp 'jetradar'
then 'jetradar'
else 'other'
end as brand,
case
when
source in (
'affiliate',
'ppc_stoyan',
'direct',
'organic',
'ppc_nm',
'ppc_no_marker'
)
then source
else 'other'
end as source,
searches > 0 is_session_with_search,
clicks > 0 is_session_with_click,
start_ts,
end_ts,
ses_length_sec / 60 ses_length_min
from analytics.sessions_web
where
pdate between '{{ macros.ds_add(ds, params.partition_lookback) }}'
and '{{ ds }}'
),
sess_mobile as (
select
pdate,
user_id,
'mobile' as platform_type,
case
when app_id iregexp 'ios'
then 'ios'
when app_id iregexp 'android'
then 'android'
else 'other'
end as platform,
case
when brand = 'WA'
then 'wayaway'
when app_id iregexp 'aviasales'
then 'aviasales'
when app_id iregexp 'jetradar'
then 'jetradar'
else 'other'
end as brand,
clicks > 0 is_session_with_click,
searches > 0 is_session_with_search,
ses_length_sec / 60 ses_length_min
from analytics.sessions_mobile
where
pdate between '{{ macros.ds_add(ds, params.partition_lookback) }}'
and '{{ ds }}'
),
sess_agg as (
select
pdate,
sess_web.platform_type,
sess_web.platform,
sess_web.brand,
coalesce(
searches_last_market.market, sess_web_last_market.market, 'N/D'
) as market,
source,
count(*) as sessions,
sum(sess_web.is_session_with_click) as sessions_with_clicks,
sum(sess_web.is_session_with_search) as sessions_with_searches,
sum(ses_length_min) as total_ses_length_min
from sess_web
left outer join
searches_last_market on sess_web.user_id = searches_last_market.user_id
left outer join
sess_web_last_market on sess_web.user_id = sess_web_last_market.user_id
group by
pdate,
platform_type,
platform,
brand,
market,
source
union all
select
pdate,
sess_mobile.platform_type,
sess_mobile.platform,
sess_mobile.brand,
coalesce(
searches_last_market.market, sess_mobile_last_market.market, 'N/D'
) as market,
'mobile' as source,
count(*) as sessions,
sum(sess_mobile.is_session_with_click) as sessions_with_clicks,
sum(sess_mobile.is_session_with_search) as sessions_with_searches,
sum(ses_length_min) as total_ses_length_min
from sess_mobile
left outer join
searches_last_market
on sess_mobile.user_id = searches_last_market.user_id
left outer join
sess_mobile_last_market
on sess_mobile_last_market.user_id = sess_mobile.user_id
group by
pdate,
platform_type,
platform,
brand,
market,
source
),
searches_agg as (
select
avs.pdate,
avs.platform_type,
avs.platform,
avs.brand,
coalesce(searches_last_market.market, avs.market, 'N/D') as market,
if(
avs.platform in ('ios', 'android'),
'mobile',
coalesce(sess_web.source, avs.source)
) as source,
count(*) as searches,
sum(if(avs.is_domestic, 1, 0)) as searches_domestic,
sum(if(avs.is_domestic, 0, 1)) as searches_international,
sum(if(avs.round_trip, 1, 0)) as searches_round_trip,
sum(if(avs.with_children, 1, 0)) as searches_with_children,
sum(if(avs.with_auth, 1, 0)) as searches_with_auth
from
(
select pdate,
case
when
referrer_host iregexp 'sdk'
or coalesce(source_kind, '') = 'white_label'
then 'other'
when source_kind in ('web', 'mobile_web', 'mobile')
then source_kind
when
referrer_host iregexp '^(iphone|ipad|phone\\.android|tablet\\.android)\\.(aviasales|jetradar|jetradar-arabic|wayaway)'
then 'mobile'
when
user_agent iregexp '(macintosh|win64|windows|x86_64|i686|amd64)'
then 'web'
when user_agent iregexp '(mobi(le)?|iphone|ipad|android)'
then 'mobile_web'
else 'other'
end as platform_type,
if(to_date(pdate)>='2023-06-01', user_platform,
case
when referrer_host iregexp '(ipad|iphone)'
and referrer_host iregexp '(aviasales|jetradar|wayaway)'
and referrer_host not iregexp 'sdk'
then 'ios'
when referrer_host iregexp '(android)'
and referrer_host iregexp '(aviasales|jetradar|wayaway)'
and referrer_host not iregexp 'sdk'
then 'android'
when referrer_host not iregexp '(ipad|iphone|android)'
and referrer_host not iregexp '^(m.aviasales)'
and referrer_host iregexp '(aviasales|jetradar|wayaway)'
then 'desktop'
when referrer_host iregexp '^(m.aviasales)'
and user_agent iregexp '(ipad|iphone)'
then 'ios_web'
when referrer_host iregexp '^(m.aviasales)'
and user_agent iregexp '(android)'
then 'android_web'
else 'other'
end) as platform,
case
when brand = 'WA' or referrer_host iregexp 'wayaway'
then 'wayaway'
when referrer_host iregexp 'aviasales'
then 'aviasales'
when referrer_host iregexp 'jetradar'
then 'jetradar'
else 'other'
end as brand,
coalesce(mobile_token, auid) user_id,
round_trip,
children + infants > 0 with_children,
user_id is not null with_auth,
ifnull(
if(
origin_country_iata in ('KX', 'RU'),
'RU',
origin_country_iata
),
''
) = ifnull(
if(
destination_country_iata in ('KX', 'RU'),
'RU',
destination_country_iata
),
''
) is_domestic,
market,
case
when referrer_host iregexp '(phone|ipad|android)'
or marker iregexp '^(mobile_app)'
then 'mobile'
when affiliate_marker in (29086,15468)
or (searches.marker iregexp '^(29086|15468)' and searches.market = 'ru')
then 'ppc_stoyan'
when affiliate_marker = 186703
or (searches.marker iregexp '^(186703)' and searches.market != 'ru')
then 'ppc_nm'
when tp_external.tp_marker is not null then 'affiliate'
when searches.marker_type in ('marketing', 'direct') then searches.marker_type
when marker iregexp '(google|yandex|rambler|mail|yahoo|bing|duckduckgo|vkontakte|facebook|instagram|telegram|twitter|odnoklassniki|vk.com)'
then 'organic'
when searches.referrer_host iregexp '(aviasales|jetradar|wayaway)'
and referrer_host iregexp '(sdk)'
then 'sdk'
when searches.referrer_host not iregexp '(aviasales|jetradar|wayaway)'
then 'wl'
else 'other'
end source,
created_at
from avia.searches
left outer join
(
select marker as tp_marker
from tp.affiliates_info
where user_type != 'internal'
) as tp_external
on avia.searches.affiliate_marker = tp_external.tp_marker
where
pdate between
'{{ macros.ds_add(ds, params.partition_lookback) }}'
and '{{ ds }}'
and coalesce(marker, '') != 'internal-dshaker'
and not coalesce(marked_as_bot, false)
and referrer_host is not null
) as avs
left outer join
searches_last_market on avs.user_id = searches_last_market.user_id
left outer join
sess_web
on
sess_web.user_id = avs.user_id
and avs.created_at > sess_web.start_ts
and avs.created_at <= sess_web.end_ts
group by
pdate,
platform_type,
platform,
brand,
market,
source
),
clicks_agg as (
select
avc.pdate,
avc.platform_type,
avc.platform,
avc.brand,
coalesce(searches_last_market.market, avc.market, 'N/D') as market,
if(
avc.platform in ('ios', 'android'),
'mobile',
coalesce(sess_web.source, avc.source)
) as source,
count(*) as clicks,
sum(
case
when clickmeter_ok.click_id is not null
then cpc_profit
else 0
end
) as cpc_profit,
sum(
case
when clickmeter_ok.click_id is not null
then cpc_profit * cur.rate
else 0
end
) as cpc_profit_usd,
sum(
case
when clickmeter_ok.click_id is not null
then 1
else 0
end
) as cpc_clicks,
-- domestic vs international --
sum(
if(is_domestic, 1, 0)
) as clicks_domestic,
sum(
if(is_domestic, 0, 1)
) as clicks_international,
sum(
case
when clickmeter_ok.click_id is not null
then if(is_domestic, cpc_profit, 0)
else 0
end
) as cpc_profit_domestic,
sum(
case
when clickmeter_ok.click_id is not null
then if(is_domestic, 0, cpc_profit)
else 0
end
) as cpc_profit_international,
sum(
case
when clickmeter_ok.click_id is not null
then if(is_domestic, cpc_profit * cur.rate, 0)
else 0
end
) as cpc_profit_usd_domestic,
sum(
case
when clickmeter_ok.click_id is not null
then if(is_domestic, 0, cpc_profit * cur.rate)
else 0
end
) as cpc_profit_usd_international,
sum(
case
when clickmeter_ok.click_id is not null
then if(is_domestic, 1, 0)
else 0
end
) as cpc_clicks_domestic,
sum(
case
when clickmeter_ok.click_id is not null
then if(is_domestic, 0, 1)
else 0
end
) as cpc_clicks_international
from
(
select pdate,
case
when
coalesce(search_referrer_host, referrer_host) iregexp 'sdk'
or coalesce(source_kind, '') = 'white_label'
then 'other'
when source_kind in ('web', 'mobile_web', 'mobile')
then source_kind
when
coalesce(search_referrer_host, referrer_host) iregexp '^(iphone|ipad|phone\\.android|tablet\\.android)\\.(aviasales|jetradar|jetradar-arabic|wayaway)'
then 'mobile'
when
user_agent iregexp '(macintosh|win64|windows|x86_64|i686|amd64)'
then 'web'
when user_agent iregexp '(mobi(le)?|iphone|ipad|android)'
then 'mobile_web'
else 'other'
end as platform_type,
if(to_date(pdate)>='2023-06-01', user_platform,
case
when coalesce(search_referrer_host, referrer_host) iregexp '(ipad|iphone)'
and coalesce(search_referrer_host, referrer_host) iregexp '(aviasales|jetradar|wayaway)'
and coalesce(search_referrer_host, referrer_host) not iregexp 'sdk' then 'ios'
when coalesce(search_referrer_host, referrer_host) iregexp '(android)'
and coalesce(search_referrer_host, referrer_host) iregexp '(aviasales|jetradar|wayaway)'
and coalesce(search_referrer_host, referrer_host) not iregexp 'sdk' then 'android'
when coalesce(search_referrer_host, referrer_host) not iregexp '(ipad|iphone|android)'
and coalesce(search_referrer_host, referrer_host) not iregexp '^(m.aviasales)'
and coalesce(search_referrer_host, referrer_host) iregexp '(aviasales|jetradar|wayaway)' then 'desktop'
when coalesce(search_referrer_host, referrer_host) iregexp '^(m.aviasales)'
and user_agent iregexp '(ipad|iphone)' then 'ios_web'
when coalesce(search_referrer_host, referrer_host) iregexp '^(m.aviasales)'
and user_agent iregexp '(android)' then 'android_web'
else 'other'
end
) as platform,
case
when brand = 'WA' or coalesce(search_referrer_host, referrer_host) iregexp 'wayaway' then 'wayaway'
when coalesce(search_referrer_host, referrer_host) iregexp 'aviasales' then 'aviasales'
when coalesce(search_referrer_host, referrer_host) iregexp 'jetradar' then 'jetradar'
else 'other'
end as brand,
coalesce(mobile_token, auid) user_id,
click_id,
affiliate_marker,
ifnull(if(origin_country_iata in ('KX', 'RU'), 'RU', origin_country_iata), '') = ifnull(if(destination_country_iata in ('KX', 'RU'), 'RU', destination_country_iata), '') is_domestic,
cpc_profit,
adults + infants + children passengers,
market,
case
when coalesce(search_referrer_host, referrer_host) iregexp '(phone|ipad|android)' OR marker iregexp '^(mobile_app)' then 'mobile'
when affiliate_marker in (29086,15468) or (marker iregexp '^(29086|15468)' and market = 'ru') then 'ppc_stoyan'
when affiliate_marker = 186703 or (marker iregexp '^(186703)' and market != 'ru') then 'ppc_nm'
when tp_external.tp_marker is not null then 'affiliate'
when marker_type in ('marketing', 'direct') then marker_type
when marker iregexp '(google|yandex|rambler|mail|yahoo|bing|duckduckgo|vkontakte|facebook|instagram|telegram|twitter|odnoklassniki|vk.com)' then 'organic'
when coalesce(search_referrer_host, referrer_host) iregexp '(aviasales|jetradar|wayaway)' and coalesce(search_referrer_host, referrer_host) iregexp '(sdk)' then 'sdk'
when coalesce(search_referrer_host, referrer_host) not iregexp '(aviasales|jetradar|wayaway)' then 'wl'
else 'other'
end source,
created_at
from avia.clicks
left outer join
(
select marker as tp_marker
from tp.affiliates_info
where user_type != 'internal'
) as tp_external
on affiliate_marker = tp_external.tp_marker
where
pdate between
'{{ macros.ds_add(ds, params.partition_lookback) }}'
and '{{ ds }}'
) as avc
left outer join searches_last_market
on avc.user_id = searches_last_market.user_id
left outer join
sess_web
on
sess_web.user_id = avc.user_id
and avc.created_at > sess_web.start_ts
and avc.created_at <= sess_web.end_ts
left outer join
(
select
click_id,
max(suspicions_click_flag) as suspicions_click_flag
from
(
select
click_id,
max(if(is_spider, 1, 0)) as suspicions_click_flag
from avia.clickmeter_clicks
where
pdate between '{{ macros.ds_add(ds, params.partition_lookback) }}'
and '{{ ds }}'
group by
click_id
union all
select
click_id,
max(if(is_bot, 1, 0)) as suspicions_click_flag
from avia.scaleo_clicks
where
pdate between '{{ macros.ds_add(ds, params.partition_lookback) }}'
and '{{ ds }}'
group by 1
) as scf
group by click_id
having max(suspicions_click_flag) = 0
) as clickmeter_ok
on avc.click_id = clickmeter_ok.click_id
left outer join
(
select
pdate,
rate
from dictionary.currency_rates
where src = 'RUB' and dst = 'USD'
) as cur
on cur.pdate = avc.pdate
group by
pdate,
platform_type,
platform,
brand,
market,
source
),
bookings_agg as (
select
avb.pdate,
avb.platform_type,
avb.platform,
avb.brand,
coalesce(searches_last_market.market, avb.market, 'N/D') as market,
if(
avb.platform in ('ios', 'android'),
'mobile',
coalesce(sess_web.source, avb.source)
) as source,
count(*) as bookings,
coalesce(sum(profit_amount), 0) as profit,
coalesce(sum(price_amount), 0) as price,
coalesce(sum(profit_amount_usd), 0) as profit_usd,
coalesce(sum(price_amount_usd), 0) as price_usd,
sum(
case
when avb.click_id is not null
then passengers
else 0
end
) as passengers,
-- domestic vs international --
coalesce(sum(if(is_domestic, 1, 0)), 0) as bookings_domestic,
coalesce(sum(if(is_domestic, 0, 1)), 0) as bookings_international,
coalesce(sum(if(is_domestic, profit_amount, 0)), 0) as profit_domestic,
coalesce(sum(if(is_domestic, 0, profit_amount)), 0)
as profit_international,
coalesce(sum(if(is_domestic, price_amount, 0)), 0) as price_domestic,
coalesce(sum(if(is_domestic, 0, price_amount)), 0)
as price_international,
coalesce(sum(if(is_domestic, profit_amount_usd, 0)), 0)
as profit_usd_domestic,
coalesce(sum(if(is_domestic, 0, profit_amount_usd)), 0)
as profit_usd_international,
coalesce(sum(if(is_domestic, price_amount_usd, 0)), 0)
as price_usd_domestic,
coalesce(sum(if(is_domestic, 0, price_amount_usd)), 0)
as price_usd_international,
sum(
case
when avb.click_id is not null
then if(is_domestic, passengers, 0)
else 0
end
) as passengers_domestic,
sum(
case
when avb.click_id is not null
then if(is_domestic, 0, passengers)
else 0
end
) as passengers_international
from (
select to_date(booked_at) as pdate,
case
when
coalesce(search_referrer_host, click_referrer_host) iregexp 'sdk'
or coalesce(search_source_kind, click_source_kind, '') = 'white_label'
then 'other'
when coalesce(search_source_kind, click_source_kind) in ('web', 'mobile_web', 'mobile')
then coalesce(search_source_kind, click_source_kind)
when
coalesce(search_referrer_host, click_referrer_host) iregexp '^(iphone|ipad|phone\\.android|tablet\\.android)\\.(aviasales|jetradar|jetradar-arabic|wayaway)'
then 'mobile'
when
user_agent iregexp '(macintosh|win64|windows|x86_64|i686|amd64)'
then 'web'
when user_agent iregexp '(mobi(le)?|iphone|ipad|android)'
then 'mobile_web'
else 'other'
end as platform_type,
if(to_date(booked_at)>='2023-06-01', user_platform,
case
when coalesce(search_referrer_host, click_referrer_host) iregexp '(ipad|iphone)'
and coalesce(search_referrer_host, click_referrer_host) iregexp '(aviasales|jetradar|wayaway)'
and coalesce(search_referrer_host, click_referrer_host) not iregexp 'sdk' then 'ios'
when coalesce(search_referrer_host, click_referrer_host) iregexp '(android)'
and coalesce(search_referrer_host, click_referrer_host) iregexp '(aviasales|jetradar|wayaway)'
and coalesce(search_referrer_host, click_referrer_host) not iregexp 'sdk' then 'android'
when coalesce(search_referrer_host, click_referrer_host) not iregexp '(ipad|iphone|android)'
and coalesce(search_referrer_host, click_referrer_host) not iregexp '^(m.aviasales)'
and coalesce(search_referrer_host, click_referrer_host) iregexp '(aviasales|jetradar|wayaway)' then 'desktop'
when coalesce(search_referrer_host, click_referrer_host) iregexp '^(m.aviasales)'
and user_agent iregexp '(ipad|iphone)' then 'ios_web'
when coalesce(search_referrer_host, click_referrer_host) iregexp '^(m.aviasales)'
and user_agent iregexp '(android)' then 'android_web'
else 'other'
end
) as platform,
case
when brand = 'WA' or coalesce(search_referrer_host, click_referrer_host) iregexp 'wayaway' then 'wayaway'
when coalesce(search_referrer_host, click_referrer_host) iregexp 'aviasales' then 'aviasales'
when coalesce(search_referrer_host, click_referrer_host) iregexp 'jetradar' then 'jetradar'
else 'other'
end as brand,
coalesce(mobile_token, search_auid, click_auid) user_id,
ifnull(if(origin_country_iata in ('KX', 'RU'), 'RU', origin_country_iata), '') = ifnull(if(destination_country_iata in ('KX', 'RU'), 'RU', destination_country_iata), '') is_domestic,
adults + infants + children passengers,
market,
case
when coalesce(search_referrer_host, click_referrer_host) iregexp '(phone|ipad|android)'
or coalesce(click_marker, search_marker) iregexp '^(mobile_app)' then 'mobile'
when click_affiliate_marker in (29086,15468) or (coalesce(click_marker, search_marker) iregexp '^(29086|15468)' and market = 'ru') then 'ppc_stoyan'
when click_affiliate_marker = 186703 or (coalesce(click_marker, search_marker) iregexp '^(186703)' and market != 'ru') then 'ppc_nm'
when tp_external.tp_marker is not null then 'affiliate'
when marker_type in ('marketing', 'direct') then marker_type
when coalesce(click_marker, search_marker) iregexp '(google|yandex|rambler|mail|yahoo|bing|duckduckgo|vkontakte|facebook|instagram|telegram|twitter|odnoklassniki|vk.com)' then 'organic'
when coalesce(search_referrer_host, click_referrer_host) iregexp '(aviasales|jetradar|wayaway)' and coalesce(search_referrer_host, click_referrer_host) iregexp '(sdk)' then 'sdk'
when coalesce(search_referrer_host, click_referrer_host) not iregexp '(aviasales|jetradar|wayaway)' then 'wl'
else 'other'
end source,
coalesce(created_at, click_created_at, search_created_at) created_at,
click_id,
booking_id,
profit_amount,
price_amount,
profit_amount_usd,
price_amount_usd
from integrations.bookings
left outer join
(
select marker as tp_marker
from tp.affiliates_info
where user_type != 'internal'
) as tp_external
on
integrations.bookings.click_affiliate_marker
= tp_external.tp_marker
where
to_date(booked_at)
between '{{ macros.ds_add(ds, params.partition_lookback) }}'
and '{{ ds }}'
and state = 'paid'
) as avb
left outer join
searches_last_market
on avb.user_id = searches_last_market.user_id
left outer join
sess_web
on
sess_web.user_id = avb.user_id
and avb.created_at > sess_web.start_ts
and avb.created_at <= sess_web.end_ts
group by
pdate,
platform_type,
platform,
brand,
market,
source
),
scb_agg as (
select
coalesce(
searches_agg.pdate,
clicks_agg.pdate,
bookings_agg.pdate
) as pdate,
coalesce(
searches_agg.platform_type,
clicks_agg.platform_type,
bookings_agg.platform_type
) as platform_type,
coalesce(
searches_agg.platform,
clicks_agg.platform,
bookings_agg.platform
) as platform,
coalesce(searches_agg.brand, clicks_agg.brand, bookings_agg.brand)
as brand,
coalesce(
searches_agg.market,
clicks_agg.market,
bookings_agg.market
) as market,
coalesce(searches_agg.source, clicks_agg.source, bookings_agg.source)
as source,
coalesce(searches_agg.searches, 0) as searches,
coalesce(searches_agg.searches_round_trip, 0) as searches_round_trip,
coalesce(searches_agg.searches_with_children, 0)
as searches_with_children,
coalesce(searches_agg.searches_with_auth, 0) as searches_with_auth,
coalesce(clicks_agg.clicks, 0) as clicks,
coalesce(bookings_agg.bookings, 0) as bookings,
coalesce(bookings_agg.profit, 0) as profit,
coalesce(bookings_agg.profit_usd, 0) as profit_usd,
coalesce(bookings_agg.price, 0) as price,
coalesce(bookings_agg.price_usd, 0) as price_usd,
coalesce(bookings_agg.passengers, 0) as passengers,
coalesce(clicks_agg.cpc_profit, 0) as cpc_profit,
coalesce(clicks_agg.cpc_profit_usd, 0) as cpc_profit_usd,
coalesce(clicks_agg.cpc_clicks, 0) as cpc_clicks,
-- domestic vs international
coalesce(searches_agg.searches_domestic, 0) as searches_domestic,
coalesce(searches_agg.searches_international, 0)
as searches_international,
coalesce(clicks_agg.clicks_domestic, 0) as clicks_domestic,
coalesce(clicks_agg.clicks_international, 0) as clicks_international,
coalesce(bookings_agg.bookings_domestic, 0) as bookings_domestic,
coalesce(bookings_agg.bookings_international, 0)
as bookings_international,
coalesce(bookings_agg.profit_domestic, 0) as profit_domestic,
coalesce(bookings_agg.profit_international, 0) as profit_international,
coalesce(bookings_agg.price_domestic, 0) as price_domestic,
coalesce(bookings_agg.price_international, 0) as price_international,
coalesce(bookings_agg.profit_usd_domestic, 0)
as profit_usd_domestic,
coalesce(bookings_agg.profit_usd_international, 0)
as profit_usd_international,
coalesce(bookings_agg.price_usd_domestic, 0)
as price_usd_domestic,
coalesce(bookings_agg.price_usd_international, 0)
as price_usd_international,
coalesce(bookings_agg.passengers_domestic, 0)
as passengers_domestic,
coalesce(bookings_agg.passengers_international, 0)
as passengers_international,
coalesce(clicks_agg.cpc_profit_domestic, 0)
as cpc_profit_domestic,
coalesce(clicks_agg.cpc_profit_international, 0)
as cpc_profit_international,
coalesce(clicks_agg.cpc_profit_usd_domestic, 0)
as cpc_profit_usd_domestic,
coalesce(clicks_agg.cpc_profit_usd_international, 0)
as cpc_profit_usd_international,
coalesce(clicks_agg.cpc_clicks_domestic, 0) as cpc_clicks_domestic,
coalesce(clicks_agg.cpc_clicks_international, 0)
as cpc_clicks_international
from searches_agg
full outer join clicks_agg
on
clicks_agg.platform_type = searches_agg.platform_type
and clicks_agg.platform = searches_agg.platform
and clicks_agg.brand = searches_agg.brand
and clicks_agg.market = searches_agg.market
and clicks_agg.source = searches_agg.source
and clicks_agg.pdate = searches_agg.pdate
full outer join bookings_agg
on
bookings_agg.platform_type = searches_agg.platform_type
and bookings_agg.platform = searches_agg.platform
and bookings_agg.brand = searches_agg.brand
and bookings_agg.market = searches_agg.market
and bookings_agg.source = searches_agg.source
and bookings_agg.pdate = searches_agg.pdate
),
installs_agg as (
select
pdate,
'mobile' as platform_type,
case
when app_id in ('id498958864', 'id606870241', 'id1112584191')
then 'ios'
when app_id in ('ru.aviasales', 'com.jetradar')
then 'android'
else 'other'
end as platform,
case
when app_name iregexp 'wayaway'
then 'wayaway'
when app_id = 'com.jetradar' and i.pdate >= '2022-06-27'
then 'wayaway'
-- from release (27 Jun?) to 3rd Aug Android WayAway has wrong name
-- (Aviasales) in AppsFlyer data
when app_id in ('id498958864', 'ru.aviasales')
then 'aviasales'
when app_id in ('com.jetradar', 'id606870241', 'id1112584191')
then 'jetradar'
when app_id = 'id1112584191'
then 'jetradar'
else 'other'
end as brand,
coalesce(searches_last_market.market, country_code, 'N/D') market,
'mobile' source,
count(distinct appsflyer_device_id) installs
from
( -- installs new--
select
appsflyer_id as appsflyer_device_id,
customer_user_id as token,
case
when
city in (
'Sevastopol',
"Sevastopol'",
'Simferopol',
'Yalta',
'Ialta',
'Sudak',
'Feodosiya',
'Feodosiia',
'Yevpatoriia',
'Fruktove',
'Bakhchisaray',
'Bakhchisaray',
'Kerch',
'Shchyolkino',
'Saki',
'Alushta',
'Dzhankoy',
'Gurzuf',
'Hurzuf',
'Bilohirsk',
'Ayvazovskoye',
'Saky',
'Vidradne',
'Staryi Krym',
'Simeiz',
'Alupka',
'Novomykolaivka',
'Vynohradne',
'Molodizhne',
'Bakhchysarai',
'Koreiz',
'Crimea',
'Masandra',
"Armians'K",
'Vesele',
'Haspra',
'Dzhankoy',
'Livadiia',
'Livadiya'
)
then 'ru'
else lower(country_code)
end as country_code,
to_date(install_time) as pdate,
app_id,
app_name
from appsflyer.avia_installs
where
to_date(install_time) between
'{{ macros.ds_add(ds, params.partition_lookback) }}'
and '{{ ds }}'
and app_id in (
'id606870241',
'com.jetradar',
'id1112584191',
'id498958864',
'ru.aviasales'
)
and event_name = 'install'
and customer_user_id is not null
) as i
left outer join searches_last_market
on searches_last_market.user_id = i.token
group by
pdate,
platform_type,
platform,
brand,
market,
source
)
insert overwrite table {{ params.schema }}.{{ params.relation }}
partition (pdate)
select
coalesce(sess_agg.platform_type, scb_agg.platform_type, installs_agg.platform_type) as platform_type,
coalesce(sess_agg.platform, scb_agg.platform, installs_agg.platform) as platform,
coalesce(sess_agg.brand, scb_agg.brand, installs_agg.brand) as brand,
coalesce(sess_agg.market, scb_agg.market, installs_agg.market) as market,
coalesce(sess_agg.source, scb_agg.source, installs_agg.source) as source,
cast(coalesce(scb_agg.searches, 0) as int) as searches,
cast(coalesce(scb_agg.clicks, 0) as int) as clicks,
cast(coalesce(scb_agg.bookings, 0) as int) as bookings,
cast(coalesce(scb_agg.profit, 0) as decimal(38, 2)) as profit,
cast(coalesce(scb_agg.profit_usd, 0) as decimal(38, 2)) as profit_usd,
cast(coalesce(scb_agg.price, 0) as decimal(38, 2)) as price,
cast(coalesce(scb_agg.price_usd, 0) as decimal(38, 2)) as price_usd,
cast(coalesce(scb_agg.passengers, 0) as int) as passengers,
cast(coalesce(scb_agg.cpc_profit, 0) as decimal(38, 2)) as cpc_profit,
cast(coalesce(scb_agg.cpc_profit_usd, 0) as decimal(38, 2)) as cpc_profit_usd,
cast(coalesce(scb_agg.cpc_clicks, 0) as int) as cpc_clicks,
cast(coalesce(sessions, 0) as int) as sessions,
cast(coalesce(sessions_with_clicks, 0) as int) as sessions_with_click,
cast(coalesce(sessions_with_searches, 0) as int) as sessions_with_search,
cast(coalesce(installs, 0) as int) as installs,
-- domestic vs international
cast(coalesce(searches_domestic, 0) as int) as searches_domestic,
cast(coalesce(searches_international, 0) as int) as searches_international,
cast(coalesce(clicks_domestic, 0) as int) as clicks_domestic,
cast(coalesce(clicks_international, 0) as int) as clicks_international,
cast(coalesce(bookings_domestic, 0) as int) as bookings_domestic,
cast(coalesce(bookings_international, 0) as int) as bookings_international,
cast(coalesce(profit_domestic, 0) as decimal(38, 2)) as profit_domestic,
cast(coalesce(profit_international, 0) as decimal(38, 2)) as profit_international,
cast(coalesce(price_domestic, 0) as decimal(38, 2)) as price_domestic,
cast(coalesce(price_international, 0) as decimal(38, 2)) as price_international,
cast(coalesce(profit_usd_domestic, 0) as decimal(38, 2)) as profit_usd_domestic,
cast(coalesce(profit_usd_international, 0) as decimal(38, 2)) as profit_usd_international,
cast(coalesce(price_usd_domestic, 0) as decimal(38, 2)) as price_usd_domestic,
cast(coalesce(price_usd_international, 0) as decimal(38, 2)) as price_usd_international,
cast(coalesce(passengers_domestic, 0) as int) as passengers_domestic,
cast(coalesce(passengers_international, 0) as int) as passengers_international,
cast(coalesce(cpc_profit_domestic, 0) as decimal(38, 2)) as cpc_profit_domestic,
cast(coalesce(cpc_profit_international, 0) as decimal(38, 2)) as cpc_profit_international,
cast(coalesce(cpc_profit_usd_domestic, 0) as decimal(38, 2)) as cpc_profit_usd_domestic,
cast(coalesce(cpc_profit_usd_international, 0) as decimal(38, 2)) as cpc_profit_usd_internation,
cast(coalesce(cpc_clicks_domestic, 0) as int) as cpc_clicks_domestic,
cast(coalesce(cpc_clicks_international, 0) as int) as cpc_clicks_international,
cast(coalesce(total_ses_length_min, 0) as decimal(38, 2)) as total_ses_length_min,
cast(coalesce(scb_agg.searches_round_trip, 0) as int) as searches_round_trip,
cast(coalesce(scb_agg.searches_with_children, 0) as int) as searches_with_children,
cast(coalesce(scb_agg.searches_with_auth, 0) as int) as searches_with_auth,
coalesce(sess_agg.pdate, scb_agg.pdate, installs_agg.pdate) as pdate
from sess_agg
full outer join scb_agg
on
sess_agg.platform = scb_agg.platform
and sess_agg.platform_type = scb_agg.platform_type
and sess_agg.brand = scb_agg.brand
and sess_agg.market = scb_agg.market
and sess_agg.source = scb_agg.source
and sess_agg.pdate = scb_agg.pdate
full outer join installs_agg
on
coalesce(sess_agg.platform_type, scb_agg.platform_type)
= installs_agg.platform_type
and coalesce(sess_agg.platform, scb_agg.platform)
= installs_agg.platform
and coalesce(sess_agg.brand, scb_agg.brand) = installs_agg.brand
and coalesce(sess_agg.market, scb_agg.market) = installs_agg.market
and coalesce(sess_agg.source, scb_agg.source) = installs_agg.source
and coalesce(sess_agg.pdate, scb_agg.pdate) = installs_agg.pdate
order by
pdate,
platform_type,
platform,
brand,
market,
source;
compute incremental stats {{ params.schema }}.{{ params.relation }}
partition (pdate BETWEEN '{{ macros.ds_add(ds, params.boundary) }}' AND '{{ ds }}');