with
webuids as (
select
distinct webuid
from reports.ga4_traffic_202308
where true
and date = '2023-08-10'
and event_name in ('page_view')
)
, pv as (
select
date
, event_name
, webuid
, country
, budget_group as product_group
, locale
, channel_group
, operating_system
, to_timestamp(timestamp / 1000 / 1000) ts
from reports.ga4_traffic_202308
where true
and date = '2023-08-10'
and event_name in ('page_view')
and webuid in (select webuid from webuids)
group by
1, 2, 3, 4, 5, 6, 7, 8, 9
)
,downloads as (
select
distinct webuid
, to_timestamp(timestamp / 1000 / 1000) ts
, substring(link_url, '[^?]*') link_url
, substring(link_url, 'download-(\w+)') link_name
, lower(locale) locale
, budget_group as product_group
from reports.ga4_traffic_202308
where true
and date between '2023-08-10' and '2023-08-11'
and (event_name in ('download')
and link_url is not null)
and webuid in (select webuid from webuids)
)
, down_success_mapp AS (
SELECT
t.webuid
,ts
,request_completion
,product_group
FROM
downloads t
LEFT JOIN
public.download_link dl
ON t.link_name = dl.name
LEFT JOIN
public.product_download_link pdl
ON dl.id = pdl.download_link_id AND
pdl.locale = t.locale
LEFT JOIN
public.product_download_link pdl1
ON dl.id = pdl1.download_link_id AND
pdl1.locale IS NULL AND
pdl.id IS NULL
LEFT JOIN
public.vector_downloads vdt
ON substring(coalesce(pdl.url, pdl1.url), '/?([^\/]*)$') = substring(vdt.request_filename, '/?([^\/]*)$') AND
t.webuid = vdt.webuid AND
vdt.time BETWEEN ts AND ts + '1hour'
where t.webuid in (select webuid from webuids)
)
,inst_start as (
select
distinct id_webuid
, c_date_time_stamp as ts
from product_stat.install i
where true
and date between '2023-08-10' and '2023-08-11'
and coalesce(c_param_trial
, c_activation_status
, '') <> 'HACKED'
and id_device not in
(
select
c_huid
from
product_stat.product_desktop_blacklist)
and c_event_name in ('INSTALLER_OPENED','BUNDLE_INSTALL_STARTED')
and id_webuid in (select webuid from webuids)
)
----------------------------------------------------
/*, BUNDLE_EULA_ACCEPTED as (
SELECT
id_webuid
,c_date_time_stamp as ts
FROM product_stat.install i
WHERE date between '2023-08-10' and '2023-08-11' and
c_event_name IN ('BUNDLE_EULA_ACCEPTED')
and id_webuid in (select webuid from webuids)
)
, BUNDLE_DOWNLOAD_STARTED as (
SELECT
id_webuid
,c_date_time_stamp as ts
FROM product_stat.install i
WHERE date between '2023-08-10' and '2023-08-11' and
c_event_name IN ('BUNDLE_DOWNLOAD_STARTED')
and id_webuid in (select webuid from webuids)
)
, BUNDLE_DOWNLOAD_FINISHED as (
SELECT
id_webuid
,c_date_time_stamp as ts
FROM product_stat.install i
WHERE date between '2023-08-10' and '2023-08-11' and
c_event_name IN ('BUNDLE_DOWNLOAD_FINISHED')
and id_webuid in (select webuid from webuids)
)
, PRODUCT_INSTALL_STARTED as (
SELECT
id_webuid
,c_date_time_stamp as ts
FROM product_stat.install i
WHERE date between '2023-08-10' and '2023-08-11' and
c_event_name IN ('PRODUCT_INSTALL_STARTED')
and id_webuid in (select webuid from webuids)
)
, PRODUCT_INSTALL_FINISHED as (
SELECT
id_webuid
,c_date_time_stamp as ts
FROM product_stat.install i
WHERE date between '2023-08-10' and '2023-08-11' and
c_event_name IN ('PRODUCT_INSTALL_FINISHED')
and id_webuid in (select webuid from webuids)
) */
, PRODUCT_STARTING as (
SELECT
id_webuid
,c_date_time_stamp as ts
FROM product_stat.install i
WHERE date between '2023-08-10' and '2023-08-11' and
c_event_name IN ('PRODUCT_STARTING')
and id_webuid in (select webuid from webuids)
)
----------------------------------------------------
,inst_finish as (
select
distinct
id_webuid
, c_date_time_stamp as ts
from
product_stat.install i
where
true
and date between '2023-08-10' and '2023-08-11'
and id_webuid in (select webuid from webuids)
and coalesce(c_param_trial
, c_activation_status
, '') <> 'HACKED'
and id_device not in
(
select
c_huid
from
product_stat.product_desktop_blacklist)
and c_event_name in ('PRODUCT_START_SUCCEEDED') --'BUNDLE_INSTALL_FINISHED',
)
, afs as (
select
distinct id_webuid
, c_date_time_stamp as ts
, pd.date
, g.product_group
from product_stat.product_desktop pd
LEFT JOIN
product_groups g
ON lower(pd.c_product_name) = lower(g.product)
where true
and date between '2023-08-10' and '2023-08-11'
and coalesce(c_param_trial
, c_activation_status
, '') <> 'HACKED'
and id_device not in
(
select
c_huid
from
product_stat.product_desktop_blacklist)
and c_event_name in ('APP_FIRST_START')
and id_webuid in (select webuid from webuids)
)
, tyfi as (
SELECT
distinct webuid
, to_timestamp(timestamp / 1000 / 1000) as ts
, event_name
FROM reports.ga4_traffic_202308 gt
WHERE TRUE
and date between '2023-08-10' and '2023-08-11' AND
(
(
event_name IN ('download') AND
link_url IS NOT NULL
) OR
event_name IN ('page_view_tyfi')
)
and webuid in (select webuid from webuids)
)
,buynow as (
select
date
, webuid
, to_timestamp(timestamp / 1000 / 1000) ts
, budget_group as product_group
from reports.ga4_traffic_202308
where true
and date between '2023-08-10' and '2023-08-11'
and event_name in ('page_view_buynow')
and webuid in (select webuid from webuids)
group by
1, 2,3,4
)
,buy as (
select
date
, webuid
, to_timestamp(timestamp / 1000 / 1000) ts
, budget_group as product_group
from
reports.ga4_traffic_202308
where true
and date between '2023-08-10' and '2023-08-11'
and event_name in ('buy')
and webuid in (select webuid from webuids)
group by
1, 2,3,4
)
, nag_show_list as (
select
id_webuid
, date
from product_stat.product_desktop
where date between '2023-08-10' and '2023-08-11'
and c_event_name = 'NAG_SHOW'
and param_nag_name in ('before_export', 'app_start')
and id_webuid in (select webuid from webuids)
group by 1,2
)
, nag_link_click_list as (
select
id_webuid
, date
from product_stat.product_desktop
where date between '2023-08-10' and '2023-08-11'
and c_event_name = 'NAG_LINK_CLICK'
and param_nag_link_id in ('BuyNowUrl')
and id_webuid in (select webuid from webuids)
group by 1,2
)
,checkout as (
select
distinct
webuid
, to_timestamp(timestamp / 1000 / 1000) ts
from reports.ga4_traffic_202308
where true
and date between '2023-08-10' and '2023-08-11'
and event_name in ('begin_checkout')
and webuid in (select webuid from webuids)
)
,"transaction" as (
select
distinct
webuid
, to_timestamp(timestamp / 1000 / 1000) ts
from reports.ga4_traffic_202308
where true
and date between '2023-08-10' and '2023-08-11'
and event_name in ('purchase')
and webuid in (select webuid from webuids)
)
select
pv.date
, pv.country
, pv.product_group
, pv.locale
, pv.channel_group
, pv.operating_system
, count(distinct pv.webuid) as Users
, count(distinct down_success_mapp.webuid) as DownloadClick
, count(distinct case when request_completion = 'OK' then down_success_mapp.webuid end) as DownloadWISuccess
, count(distinct inst_start.id_webuid) as InstallStarted
, count(distinct inst_finish.id_webuid) as InstallFinished
--, count(distinct bea.id_webuid) as BUNDLE_EULA_ACCEPTED
--, count(distinct bds.id_webuid) as BUNDLE_DOWNLOAD_STARTED
--, count(distinct bdf.id_webuid) as BUNDLE_DOWNLOAD_FINISHED
--, count(distinct pis.id_webuid) as PRODUCT_INSTALL_STARTED
--, count(distinct pif.id_webuid) as PRODUCT_INSTALL_FINISHED
, count(distinct prst.id_webuid) as PRODUCT_STARTING
, count(distinct afs.id_webuid) as Afs
, count(distinct nsl.id_webuid) as NagShow
, count(distinct ncl.id_webuid) as NagClick
, count(distinct buynow.webuid) as Buynow
, count(distinct buy.webuid) as Buy
, count(distinct checkout.webuid) as Checkout
, count(distinct transaction.webuid) as transaction
, count(distinct tyfi.webuid) as tyfi_count
from pv
left join down_success_mapp on
pv.webuid = down_success_mapp.webuid and down_success_mapp.ts BETWEEN pv.ts AND pv.ts + '3hour'
left join inst_start on
down_success_mapp.webuid = inst_start.id_webuid and inst_start.ts BETWEEN down_success_mapp.ts AND down_success_mapp.ts + '3hour' AND down_success_mapp.request_completion = 'OK'
-------------------------
/*left join BUNDLE_EULA_ACCEPTED bea ON bea.id_webuid = inst_start.id_webuid
AND bea.ts BETWEEN inst_start.ts AND inst_start.ts + '1hour'
left join BUNDLE_DOWNLOAD_STARTED bds ON bds.id_webuid = inst_start.id_webuid
AND bds.ts BETWEEN inst_start.ts AND inst_start.ts + '1hour'
left join BUNDLE_DOWNLOAD_FINISHED bdf ON bdf.id_webuid = inst_start.id_webuid
AND bdf.ts BETWEEN inst_start.ts AND inst_start.ts + '1hour'
left join PRODUCT_INSTALL_STARTED pis ON inst_start.id_webuid = pis.id_webuid
AND pis.ts BETWEEN inst_start.ts AND inst_start.ts + '1hour'
left join PRODUCT_INSTALL_FINISHED pif ON pif.id_webuid = inst_start.id_webuid
AND pif.ts BETWEEN inst_start.ts AND inst_start.ts + '1hour'
*/
left join PRODUCT_STARTING prst ON inst_start.id_webuid = prst.id_webuid
AND prst.ts BETWEEN inst_start.ts AND inst_start.ts + '1hour'
-------------------------
left join inst_finish on
inst_finish.id_webuid = inst_start.id_webuid and inst_finish.ts BETWEEN inst_start.ts AND inst_start.ts + '3hour'
left join afs on
afs.id_webuid = inst_finish.id_webuid and afs.ts BETWEEN inst_finish.ts AND inst_finish.ts + '1hour'
left join buynow on
afs.id_webuid = buynow.webuid and buynow.date BETWEEN afs.date AND afs.date + 1
left join buy on buynow.webuid = buy.webuid and buy.ts BETWEEN buynow.ts AND buynow.ts + '2hour'
left join tyfi on tyfi.webuid = inst_finish.id_webuid and tyfi.ts BETWEEN inst_finish.ts AND buynow.ts + '2hour'
left join nag_show_list nsl on nsl.id_webuid = afs.id_webuid and nsl.date between afs.date and afs.date+1
left join nag_link_click_list ncl on ncl.id_webuid = nsl.id_webuid and ncl.date = afs.date
left join checkout on
buy.webuid = checkout.webuid and checkout.ts BETWEEN buy.ts AND buy.ts + '3hour'
left join "transaction" on
transaction.webuid = checkout.webuid and transaction.ts BETWEEN afs.ts AND afs.ts + '2day'
group by 1, 2, 3, 4, 5, 6
Telegram: DB developers channel