Ссылка на репорт:
https://prod-uk-a.online.tableau.com/#/site/globalnet/workbooks/777765/views
Описание:
Собраны основные метрики и представлены в виде заполненных диаграмм и графиков с возможностью брейкдаунов по фильтрам бренд, страна, сегмент, с-таг.
С визитами моменты описаны в комментариях к sql коду:
-- собираем все визиты по vid
with users_with_vid_st AS (
select SAFE_CAST(REGEXP_EXTRACT(UID, "([^:]+)$") AS NUMERIC) AS User, "Staycasino" as BrandName, vid as visit_id, "vid" as visit_id_type from `g1-site.STAY_EVENTS.sign_up`
where vid is not null and DATE(`Date`) >= DATE("2022-12-01")
),
--собираем все визиты по gaClientId
users_with_gaClientId_st AS (
select SAFE_CAST(REGEXP_EXTRACT(UID, "([^:]+)$") AS NUMERIC) AS User, "Staycasino" as BrandName, gaClientId as visit_id, "gaClientId" as visit_id_type from `g1-site.STAY_EVENTS.sign_up`
where gaClientId is not null and vid is null and DATE(`Date`) >= DATE("2022-12-01")
),
-- все пользователи с vid или gaClientId
users_with_visit_id_st AS (
select * from users_with_vid_st
union all
select * from users_with_gaClientId_st
),
--временная таблица с добавлением инфы про пользователей с таблицы users + те, у которых нет visit_id
temp_users_st AS (
SELECT * FROM users_with_visit_id_st
FULL JOIN (select BrandName, Id as User from `g1-site.clean.users` where BrandName = "Staycasino" AND DATE(SignUp) >= DATE("2022-12-01"))
USING(User, Brandname)
),
--визиты с таблицы page_view, где User и visit_id не null
visits_pw_st AS (
select
SAFE_CAST(REGEXP_EXTRACT(UID, "([^:]+)$") AS NUMERIC) AS User,
"Staycasino" as BrandName,
vid,
gaClientId,
IF(vid is not null, vid, gaClientId) as visit_id,
URL,
`Date`,
row_number() over(partition by UID order by `Date` ASC) AS number
from `g1-site.STAY_EVENTS.page_view`
where SAFE_CAST(REGEXP_EXTRACT(UID, "([^:]+)$") AS NUMERIC) is not null and IF(vid is not null, vid, gaClientId) is not null
),
-- зарегистрированные пользователи, которые не светились под visit_id во время регистрации
visits_w_st AS (
select visits_pw_st.*, row_number() over(partition by visits_pw_st.BrandName, visits_pw_st.User order by `Date` asc) as first_vid
from visits_pw_st
left join (select * from temp_users_st where visit_id is null) as temp_t on visits_pw_st.BrandName = temp_t.BrandName and visits_pw_st.User = temp_t.User
where visits_pw_st.number = 1 and temp_t.User IS NOT NULL
),
-- все пользователи с visit_id, которых можно идентифицировать
users_with_visit_id_st_all AS (
select distinct * from users_with_visit_id_st
union all
select distinct * from (select distinct User, Brandname, vid AS visit_id, "vid_other" AS visit_id_type from visits_w_st where first_vid = 1)
),
-- список всех уникальных visit_id
visit_id_table_st AS (
select distinct visit_id from visits_pw_st where User IS NOT NULL and visit_id is not null
union distinct
select distinct visit_id from users_with_visit_id_st_all where User IS NOT NULL and visit_id is not null
),
-- визиты с таблицы page_view, где visit_id не null
visits_pw_st_2 AS (
select
SAFE_CAST(REGEXP_EXTRACT(UID, "([^:]+)$") AS NUMERIC) AS User,
"Staycasino" as BrandName,
vid,
gaClientId,
IF(vid is not null, vid, gaClientId) as visit_id,
URL,
`Date`,
row_number() over(partition by UID order by `Date` ASC) AS number
from `g1-site.STAY_EVENTS.page_view`
where IF(vid is not null, vid, gaClientId) is not null
),
-- добавление списка уникальных visit_id и пометки о пользователях
visit_users_st AS (
select visits_pw_st_2.*, IF(visit_id_table_st.visit_id is null, "No user", "With user") as user_s,
if(visit_id_table_st.visit_id is not null, "Yes", NULL) as users_firsts_visit_id,
row_number() over(partition by visits_pw_st_2.visit_id order by Date ASC) AS first_visit,
from visits_pw_st_2
left join visit_id_table_st on visits_pw_st_2.visit_id = visit_id_table_st.visit_id
),
-- финальная таблица с добавлением столбца является ли визит новым или нет
finall_visits_st as (select *,
if((first_visit = 1 and user_s = 'No user') or (first_visit = 1 and user_s = 'With user' and users_firsts_visit_id = 'Yes'), 'Yes', 'No') as is_first
from visit_users_st
),
--------------------------------------------------------------------------------------------------------------------------
-- собираем все визиты по vid (gaClientId в sign_up нет)
users_with_visit_id_sl AS (
select SAFE_CAST(REGEXP_EXTRACT(UID, "([^:]+)$") AS NUMERIC) AS User, "Slotozen.com" as BrandName, vid as visit_id, "vid" as visit_id_type from `g1-site.SLOTO_EVENTS_US.sign_up`
where vid is not null and DATE(`Date`) >= DATE("2022-12-07")
),
--временная таблица с добавлением инфы про пользователей с таблицы users + те, у которых нет visit_id
temp_users_sl AS (
SELECT * FROM users_with_visit_id_sl
FULL JOIN (select BrandName, Id as User from `g1-site.clean.users` where BrandName = "Slotozen.com" AND DATE(SignUp) >= DATE("2022-12-07"))
USING(User, Brandname)
),
--визиты с таблицы page_view, где User и visit_id не null
visits_pw_sl AS (
select
SAFE_CAST(REGEXP_EXTRACT(UID, "([^:]+)$") AS NUMERIC) AS User,
"Slotozen.com" as BrandName,
vid,
gaClientId,
IF(vid is not null, vid, gaClientId) as visit_id,
URL,
`Date`,
row_number() over(partition by UID order by `Date` ASC) AS number
from `g1-site.SLOTO_EVENTS_US.page_view`
where SAFE_CAST(REGEXP_EXTRACT(UID, "([^:]+)$") AS NUMERIC) is not null and IF(vid is not null, vid, gaClientId) is not null
),
-- зарегистрированные пользователи, которые не светились под visit_id во время регистрации
visits_w_sl AS (
select visits_pw_sl.*, row_number() over(partition by visits_pw_sl.BrandName, visits_pw_sl.User order by `Date` asc) as first_vid
from visits_pw_sl
left join (select * from temp_users_sl where visit_id is null) as temp_t on visits_pw_sl.BrandName = temp_t.BrandName and visits_pw_sl.User = temp_t.User
where visits_pw_sl.number = 1 and temp_t.User IS NOT NULL
),
-- все пользователи с visit_id, которых можно идентифицировать
users_with_visit_id_sl_all AS (
select distinct * from users_with_visit_id_sl
union all
select distinct * from (select distinct User, Brandname, vid AS visit_id, "vid_other" AS visit_id_type from visits_w_sl where first_vid = 1)
),
-- список всех уникальных visit_id
visit_id_table_sl AS (
select distinct visit_id from visits_pw_sl where User IS NOT NULL and visit_id is not null
union distinct
select distinct visit_id from users_with_visit_id_sl_all where User IS NOT NULL and visit_id is not null
),
-- визиты с таблицы page_view, где visit_id не null
visits_pw_sl_2 AS (
select
SAFE_CAST(REGEXP_EXTRACT(UID, "([^:]+)$") AS NUMERIC) AS User,
"Slotozen.com" as BrandName,
vid,
gaClientId,
IF(vid is not null, vid, gaClientId) as visit_id,
URL,
`Date`,
row_number() over(partition by UID order by `Date` ASC) AS number
from `g1-site.SLOTO_EVENTS_US.page_view`
where IF(vid is not null, vid, gaClientId) is not null
),
-- добавление списка уникальных visit_id и пометки о пользователях
visit_users_sl AS (
select visits_pw_sl_2.*, IF(visit_id_table_sl.visit_id is null, "No user", "With user") as user_s,
if(visit_id_table_sl.visit_id is not null, "Yes", NULL) as users_firsts_visit_id,
row_number() over(partition by visits_pw_sl_2.visit_id order by Date ASC) AS first_visit,
from visits_pw_sl_2
left join visit_id_table_sl on visits_pw_sl_2.visit_id = visit_id_table_sl.visit_id
),
-- финальная таблица с добавлением столбца является ли визит новым или нет
finall_visits_sl as (select *,
if((first_visit = 1 and user_s = 'No user') or (first_visit = 1 and user_s = 'With user' and users_firsts_visit_id = 'Yes'), 'Yes', 'No') as is_first
from visit_users_sl
),
-----------------------------------------------------------------------------------------
-- собираем все визиты по vid (gaClientId в sign_up нет)
users_with_visit_id_ri AS (
select SAFE_CAST(REGEXP_EXTRACT(UID, "([^:]+)$") AS NUMERIC) AS User, "RichardCasino" as BrandName, vid as visit_id, "vid" as visit_id_type from `g1-site.RICHARD_EVENTS.sign_up`
where vid is not null and DATE(`Date`) >= DATE("2023-04-18")
),
--временная таблица с добавлением инфы про пользователей с таблицы users + те, у которых нет visit_id
temp_users_ri AS (
SELECT * FROM users_with_visit_id_ri
FULL JOIN (select BrandName, Id as User from `g1-site.clean.users` where BrandName = "RichardCasino" AND DATE(SignUp) >= DATE("2023-04-18"))
USING(User, Brandname)
),
--визиты с таблицы page_view, где User и visit_id не null
visits_pw_ri AS (
select
SAFE_CAST(REGEXP_EXTRACT(UID, "([^:]+)$") AS NUMERIC) AS User,
"RichardCasino" as BrandName,
vid,
gaClientId,
IF(vid is not null, vid, gaClientId) as visit_id,
URL,
`Date`,
row_number() over(partition by UID order by `Date` ASC) AS number
from `g1-site.RICHARD_EVENTS.page_view`
where SAFE_CAST(REGEXP_EXTRACT(UID, "([^:]+)$") AS NUMERIC) is not null and IF(vid is not null, vid, gaClientId) is not null
),
-- зарегистрированные пользователи, которые не светились под visit_id во время регистрации
visits_w_ri AS (
select visits_pw_ri.*, row_number() over(partition by visits_pw_ri.BrandName, visits_pw_ri.User order by `Date` asc) as first_vid
from visits_pw_ri
left join (select * from temp_users_ri where visit_id is null) as temp_t on visits_pw_ri.BrandName = temp_t.BrandName and visits_pw_ri.User = temp_t.User
where visits_pw_ri.number = 1 and temp_t.User IS NOT NULL
),
-- все пользователи с visit_id, которых можно идентифицировать
users_with_visit_id_ri_all AS (
select distinct * from users_with_visit_id_ri
union all
select distinct * from (select distinct User, Brandname, vid AS visit_id, "vid_other" AS visit_id_type from visits_w_ri where first_vid = 1)
),
-- список всех уникальных visit_id
visit_id_table_ri AS (
select distinct visit_id from visits_pw_ri where User IS NOT NULL and visit_id is not null
union distinct
select distinct visit_id from users_with_visit_id_ri_all where User IS NOT NULL and visit_id is not null
),
-- визиты с таблицы page_view, где visit_id не null
visits_pw_ri_2 AS (
select
SAFE_CAST(REGEXP_EXTRACT(UID, "([^:]+)$") AS NUMERIC) AS User,
"RichardCasino" as BrandName,
vid,
gaClientId,
IF(vid is not null, vid, gaClientId) as visit_id,
URL,
`Date`,
row_number() over(partition by UID order by `Date` ASC) AS number
from `g1-site.RICHARD_EVENTS.page_view`
where IF(vid is not null, vid, gaClientId) is not null
),
-- добавление списка уникальных visit_id и пометки о пользователях
visit_users_ri AS (
select visits_pw_ri_2.*, IF(visit_id_table_ri.visit_id is null, "No user", "With user") as user_s,
if(visit_id_table_ri.visit_id is not null, "Yes", NULL) as users_firsts_visit_id,
row_number() over(partition by visits_pw_ri_2.visit_id order by Date ASC) AS first_visit,
from visits_pw_ri_2
left join visit_id_table_ri on visits_pw_ri_2.visit_id = visit_id_table_ri.visit_id
),
-- финальная таблица с добавлением столбца является ли визит новым или нет
finall_visits_ri as (select *,
if((first_visit = 1 and user_s = 'No user') or (first_visit = 1 and user_s = 'With user' and users_firsts_visit_id = 'Yes'), 'Yes', 'No') as is_first
from visit_users_ri
),
--------------------------------------------------------------------------------------------------------------------------
-- собираем все визиты по vid (gaClientId в sign_up нет)
users_with_visit_id_ww AS (
select SAFE_CAST(REGEXP_EXTRACT(UID, "([^:]+)$") AS NUMERIC) AS User, "Wantedwin" as BrandName, vid as visit_id, "vid" as visit_id_type from `g1-site.WANTEDWIN_EVENTS.sign_up`
where vid is not null and DATE(`Date`) >= DATE("2023-04-18")
),
--временная таблица с добавлением инфы про пользователей с таблицы users + те, у которых нет visit_id
temp_users_ww AS (
SELECT * FROM users_with_visit_id_ww
FULL JOIN (select BrandName, Id as User from `g1-site.clean.users` where BrandName = "Wantedwin" AND DATE(SignUp) >= DATE("2023-04-18"))
USING(User, Brandname)
),
--визиты с таблицы page_view, где User и visit_id не null
visits_pw_ww AS (
select
SAFE_CAST(REGEXP_EXTRACT(UID, "([^:]+)$") AS NUMERIC) AS User,
"Wantedwin" as BrandName,
vid,
gaClientId,
IF(vid is not null, vid, gaClientId) as visit_id,
URL,
`Date`,
row_number() over(partition by UID order by `Date` ASC) AS number
from `g1-site.WANTEDWIN_EVENTS.page_view`
where SAFE_CAST(REGEXP_EXTRACT(UID, "([^:]+)$") AS NUMERIC) is not null and IF(vid is not null, vid, gaClientId) is not null
),
-- зарегистрированные пользователи, которые не светились под visit_id во время регистрации
visits_w_ww AS (
select visits_pw_ww.*, row_number() over(partition by visits_pw_ww.BrandName, visits_pw_ww.User order by `Date` asc) as first_vid
from visits_pw_ww
left join (select * from temp_users_ww where visit_id is null) as temp_t on visits_pw_ww.BrandName = temp_t.BrandName and visits_pw_ww.User = temp_t.User
where visits_pw_ww.number = 1 and temp_t.User IS NOT NULL
),
-- все пользователи с visit_id, которых можно идентифицировать
users_with_visit_id_ww_all AS (
select distinct * from users_with_visit_id_ww
union all
select distinct * from (select distinct User, Brandname, vid AS visit_id, "vid_other" AS visit_id_type from visits_w_ww where first_vid = 1)
),
-- список всех уникальных visit_id
visit_id_table_ww AS (
select distinct visit_id from visits_pw_ww where User IS NOT NULL and visit_id is not null
union distinct
select distinct visit_id from users_with_visit_id_ww_all where User IS NOT NULL and visit_id is not null
),
-- визиты с таблицы page_view, где visit_id не null
visits_pw_ww_2 AS (
select
SAFE_CAST(REGEXP_EXTRACT(UID, "([^:]+)$") AS NUMERIC) AS User,
"Wantedwin" as BrandName,
vid,
gaClientId,
IF(vid is not null, vid, gaClientId) as visit_id,
URL,
`Date`,
row_number() over(partition by UID order by `Date` ASC) AS number
from `g1-site.WANTEDWIN_EVENTS.page_view`
where IF(vid is not null, vid, gaClientId) is not null
),
-- добавление списка уникальных visit_id и пометки о пользователях
visit_users_ww AS (
select visits_pw_ww_2.*, IF(visit_id_table_ww.visit_id is null, "No user", "With user") as user_s,
if(visit_id_table_ww.visit_id is not null, "Yes", NULL) as users_firsts_visit_id,
row_number() over(partition by visits_pw_ww_2.visit_id order by Date ASC) AS first_visit,
from visits_pw_ww_2
left join visit_id_table_ww on visits_pw_ww_2.visit_id = visit_id_table_ww.visit_id
),
-- финальная таблица с добавлением столбца является ли визит новым или нет
finall_visits_ww as (select *,
if((first_visit = 1 and user_s = 'No user') or (first_visit = 1 and user_s = 'With user' and users_firsts_visit_id = 'Yes'), 'Yes', 'No') as is_first
from visit_users_ww
),
--------------------------------------------------------------------------------------------------------------------------
all_users_visits_total AS (
select * from finall_visits_st
union all
select * from finall_visits_sl
union all
select * from finall_visits_ri
union all
select * from finall_visits_ww
),
----------------------------------------------------------------------------------------------------------------------------
regs_event AS (
SELECT
CAST(NULL AS INT64) AS Bet_Id,
CAST(Id AS STRING) AS User,
BrandName,
CONCAT(BrandName, '_', CAST(Id AS STRING)) AS Brand_User,
SignUp AS CreatedAt,
"reg" AS Event,
CAST(NULL AS STRING) AS visit_id,
CAST(NULL AS STRING) AS Domain,
NULL AS Payment,
CAST(NULL AS STRING) AS Action,
CAST(NULL AS STRING) AS Success,
NULL AS Amount_EUR_SS,
NULL AS user_action_success_number,
CAST(NULL AS STRING) AS is_first
FROM `g1-site.clean.users`
),
paym_event AS (
SELECT
CAST(NULL AS INT64) AS Bet_Id,
CAST(User AS STRING),
BrandName,
CONCAT(BrandName, '_', CAST(User AS STRING)) AS Brand_User,
CreatedAt,
"paym" AS Event,
CAST(NULL AS STRING) AS visit_id,
CAST(NULL AS STRING) AS Domain,
Payment,
Action,
Success,
Amount_EUR_SS,
ROW_NUMBER() OVER(PARTITION BY User, BrandName, Action, Success ORDER BY CreatedAt) AS user_action_success_number,
CAST(NULL AS STRING) is_first
FROM `g1-site.clean.payments`
WHERE Action = "Deposit"
),
visit_event AS (
SELECT
CAST(NULL AS INT64) AS Bet_Id,
CAST(User AS STRING),
BrandName,
CONCAT(BrandName, '_', User) AS Brand_User,
Date AS CreatedAt,
"view" AS Event,
visit_id,
CAST(REGEXP_EXTRACT(URL, r'https?://([^/]+)/') AS STRING) AS Domain,
NULL AS Payment,
CAST(NULL AS STRING) AS Action,
CAST(NULL AS STRING) AS Success,
NULL AS Amount_EUR_SS,
NULL AS user_action_success_number,
is_first
FROM all_users_visits_total
WHERE CAST(REGEXP_EXTRACT(URL, r'https?://([^/]+)/') AS STRING) IN ('www.richardcasino.com','www.richardcasino3.com','www.slotozen.bet','www.slotozen.com','www.slotozen5.com','www.staycasino.bet','www.staycasino.com','www.staycasino5.com', 'www.wantedwin.com', 'www.wantedwin7.com', 'www.wantedwin5.com')
)
,
bet_event AS (
SELECT
Id AS Bet_Id,
CAST(User AS STRING),
BrandName,
CONCAT(BrandName, '_', CAST(User AS STRING)) AS Brand_User,
CreatedAt,
"bets" AS Event,
CAST(NULL AS STRING) AS visit_id,
CAST(NULL AS STRING) AS Domain,
NULL AS Payment,
CAST(NULL AS STRING) AS Action,
CAST(NULL AS STRING) AS Success,
BetsSum_EUR AS Amount_EUR_SS, -- BetsSum_EUR
NULL AS user_action_success_number,
CAST(NULL AS STRING) AS is_first
FROM `g1-site.clean.bets`
WHERE DATE(CreatedAt) >= DATE("2024-01-01")
)
SELECT *
FROM (
SELECT *
FROM regs_event
UNION ALL
SELECT *
FROM paym_event
UNION ALL
SELECT *
FROM visit_event
UNION ALL
SELECT *
FROM bet_event
) AS total_tab
LEFT JOIN (
SELECT DISTINCT CAST(Id AS STRING) AS User, BrandName, Country
FROM `g1-site.clean.users`
) USING(BrandName, User)
LEFT JOIN (
SELECT DISTINCT CONCAT(BrandName, '_', UserIdInCasino) AS Brand_User, PartnerId
FROM `g1-site.clean.aff_users`
) USING(Brand_User)