avia_main_metrics_by_markets_platforms_day
sql
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 }}');