Ссылка на репорт:
https://prod-uk-a.online.tableau.com/#/site/globalnet/workbooks/411422/views
Описание: Репорт для оценки эффективности бонусов по метрикам, которые представлены ниже в разрезе Брендов, Випов, Стран, Стратегий, Названий, Статуса, Тега, Сегмента, Дней недели, Партнеров.
Описание/рассчет метрик:
Result (24 after bonus) = deposits € (24 after bonus) - cashout € (24 after bonus)
Total Result = все депозиты за выбранный период - все кэшауты за выбранный период
% Result 24 /dep = Result (24 after bonus) / deposits € (24 after bonus)
users received bonus - количество игроков, которые использовали бонус/ы
% users used bonus - процент игроков, которые использовали бонус/ы от всех игроков за этот период
% of count used bonus - процент количества бонусов, которые были использованы
cost per bonus = bonus € / bonus #
deposit € per bonus = deposits € (24 after bonus) / bonus #
ROI dep 24 = deposits € (24 after bonus) / bonus €
ROI Result (24 after bonus) = Result (24 after bonus) / bonus €
bonus € per act user = bonus € / users received bonus
deposit € per act user = deposits € (24 after bonus) / users received bonus
bonus # per act user = bonus # / users received bonus
deposit # per act user = deposits # (after bonus) / users received bonus
Реализовано совмещением таблиц бонусов и пейментов, для возможности с помощью оконных функций отследить что конкретный депозит был после конкретного бонуса в рамках 24 часов.
Теги подтягиваются в BigQuery с таблицы https://docs.google.com/spreadsheets/d/1G-X4bfOF6kfBMrVw5lVNr20AavpC6ilZK83KR-4trqQ/edit#gid=0
SQL код:
WITH
paym_event AS (
SELECT
CAST(User AS STRING) AS User,
BrandName,
CONCAT(BrandName, '_', CAST(User AS STRING)) AS Brand_User,
CreatedAt,
"paym" AS Event,
CAST(NULL AS STRING) AS Stage,
CAST(NULL AS STRING) AS Title,
CAST(NULL AS STRING) AS Bonus,
CAST(NULL AS STRING) AS Strategy,
CAST(Payment AS STRING) AS Payment,
Action AS Bonus_tag_and_depcash,
Success,
Amount_EUR,
CAST(NULL AS STRING) AS Tag,
NULL AS newest_bonus
FROM g1-site.clean.payments
),
-- regs_event AS (
-- SELECT
-- 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 Stage,
-- CAST(NULL AS STRING) AS Title,
-- CAST(NULL AS STRING) AS Bonus,
-- CAST(NULL AS STRING) AS Strategy,
-- CAST(NULL AS STRING) AS Payment,
-- CAST(NULL AS STRING) AS Bonus_tag_and_depcash,
-- CAST(NULL AS STRING) AS Success,
-- NULL AS Amount_EUR,
-- CAST(NULL AS STRING) AS Tag
-- FROM g1-site.clean.users
-- ),
bonus_event AS (
SELECT
CAST(bi.UserId AS STRING) AS User,
bi.BrandName,
CONCAT(bi.BrandName, '_', CAST(UserId AS STRING)) AS Brand_User,
-- bi.DateReceived AS CreatedAt,
bi.UpdatedAt AS CreatedAt,
"bonus" AS Event,
bi.Stage,
bi.Title,
bi.Bonus,
bi.Strategy,
CAST(NULL AS STRING) AS Payment,
CONCAT('[', tt.Tag, '] - ', bi.Title) AS Bonus_tag_and_depcash,
CAST(NULL AS STRING) AS Success,
bi.Amount_EUR,
tt.Tag,
ROW_NUMBER() OVER(PARTITION BY Id, BrandName ORDER BY UpdatedAt DESC) AS newest_bonus
FROM g1-site.clean.bonus_issue AS bi
LEFT JOIN (
SELECT Bonus_name, Tag, CreatedAt AS tag_date
FROM g1-site.clean.bonus_tag
WHERE CreatedAt = (
SELECT MAX(CreatedAt) FROM g1-site.clean.bonus_tag
)
) AS tt
ON bi.Title = tt.Bonus_name
),
pre_table AS (
SELECT *
FROM paym_event
UNION ALL
-- SELECT *
-- FROM regs_event
-- UNION ALL
SELECT *
FROM bonus_event
WHERE newest_bonus = 1
),
event_bon AS (
SELECT *, IF(Bonus_tag_and_depcash IN ('Deposit', 'Cashout', 'Refund', 'Reversal', 'Chargeback'), Bonus_tag_and_depcash, 'Bonus')
AS event_bonus_and_depcash
FROM pre_table
),
bon1 AS (
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY Brand_User, event_bonus_and_depcash ORDER BY CreatedAt) AS event_number
FROM event_bon
),
bon15 AS (
SELECT
*,
LAST_VALUE(IF(event_bonus_and_depcash = "Bonus", event_number, NULL) IGNORE NULLS) OVER(PARTITION BY Brand_User ORDER BY CreatedAt) AS bonus_type
FROM bon1
),
bon2 AS (
SELECT
*,
MAX(CASE WHEN event_bonus_and_depcash = 'Bonus' THEN CreatedAt END) OVER (PARTITION BY Brand_User, bonus_type) AS last_bonus_date,
FROM bon15
),
bon3 AS (
SELECT *,
IF(event_bonus_and_depcash = 'Bonus',
TIMESTAMP_DIFF(LEAD(IF(event_bonus_and_depcash = 'Deposit' AND Success = 'Yes', CreatedAt, NULL)) OVER (PARTITION BY Brand_User, bonus_type ORDER BY CreatedAt), CreatedAt, HOUR),
NULL) AS hours_to_deposit
FROM bon2
),
Tags_vip_users AS (
SELECT DISTINCT CAST(Id AS STRING) AS User, BrandName, Tags as Tags_vip
FROM g1-site.task_temporary.users
WHERE DATE(UploadedAt) = (SELECT DATE(MAX(UploadedAt)) FROM g1-site.task_temporary.users)
),
total_table AS (
SELECT *,
SUM(IF(event_bonus_and_depcash = 'Deposit' AND Success = 'Yes' AND CreatedAt BETWEEN last_bonus_date AND TIMESTAMP_ADD(last_bonus_date, INTERVAL 24 HOUR), 1, 0)) OVER (PARTITION BY Brand_User, bonus_type) AS dep_count,
SUM(IF(event_bonus_and_depcash = 'Deposit' AND Success = 'Yes' AND CreatedAt BETWEEN last_bonus_date AND TIMESTAMP_ADD(last_bonus_date, INTERVAL 24 HOUR), Amount_EUR, 0)) OVER (PARTITION BY Brand_User, bonus_type) AS dep_sum,
SUM(IF(event_bonus_and_depcash = 'Cashout' AND Success = 'Yes' AND CreatedAt BETWEEN last_bonus_date AND TIMESTAMP_ADD(last_bonus_date, INTERVAL 24 HOUR), Amount_EUR, 0)) OVER (PARTITION BY Brand_User, bonus_type) AS cashout_sum
FROM bon3)
SELECT * FROM total_table
LEFT JOIN (SELECT DISTINCT CAST(Id AS STRING) AS User, BrandName, Country, SignUp
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)
LEFT JOIN (SELECT User, BrandName, Tags_vip
FROM Tags_vip_users
) USING(BrandName, User)