Customer_Engagement_Analysis_SQL/customer_activity_queries.sql at main · Mary8052/Customer_Engagement_Analysis_SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
/* Необхідно зібрати дані, які допоможуть аналізувати динаміку створення акаунтів, активність користувачів за листами (відправлення, відкриття, переходи), а також оцінювати поведінку в категоріях, таких як інтервал відправлення, верифікація акаунтів і статус підписки. Дані дозволять порівнювати активність між країнами, визначати ключові ринки, сегментувати користувачів за різними параметрами. */


WITH


-- розрахунок категоріальних значень + метрик в розрізі перелічених полів по акаунтам та сесіям :
base_metrics AS (
SELECT
    session.date AS date,
    country,
    send_interval,
    is_verified,
    is_unsubscribed,
    COUNT (account.id) AS account_cnt,
    0 AS sent_msg,
    0 AS open_msg,
    0 AS visit_msg
FROM `data-analytics-mate.DA.account` account
    JOIN `data-analytics-mate.DA.account_session` account_session
    ON account.id = account_session.account_id
    JOIN `data-analytics-mate.DA.session` session
    ON account_session.ga_session_id = session.ga_session_id
    JOIN `data-analytics-mate.DA.session_params` session_params
    ON session.ga_session_id = session_params.ga_session_id
GROUP BY date, country, send_interval, is_verified, is_unsubscribed
UNION ALL


SELECT
    DATE_ADD(session.date, INTERVAL email_sent.sent_date DAY) AS date,
    country,
    send_interval,
    is_verified,
    is_unsubscribed,
    0 AS account_cnt,
    COUNT (DISTINCT email_sent.id_message) AS sent_msg,
    COUNT (DISTINCT email_open.id_message) AS open_msg,
    COUNT (DISTINCT email_visit.id_message) AS visit_msg
FROM `data-analytics-mate.DA.email_sent` email_sent
    LEFT JOIN `data-analytics-mate.DA.email_open` email_open
    ON email_sent.id_message = email_open.id_message
    LEFT JOIN `data-analytics-mate.DA.email_visit` email_visit
    ON email_sent.id_message = email_visit.id_message
    JOIN `data-analytics-mate.DA.account` account
    ON email_sent.id_account = account.id
    JOIN `data-analytics-mate.DA.account_session` account_session
    ON email_sent.id_account = account_session.account_id
    JOIN `data-analytics-mate.DA.session` session
    ON account_session.ga_session_id = session.ga_session_id
    JOIN `data-analytics-mate.DA.session_params` session_params
    ON session.ga_session_id = session_params.ga_session_id
GROUP BY date, country, send_interval, is_verified, is_unsubscribed
),


-- агрегація метрик, які розраховувалися у base_metrics:
aggregated_metrics AS (
SELECT
    date,
    country,
    send_interval,
    is_verified,
    is_unsubscribed,
    SUM(account_cnt) AS account_cnt,
    SUM(sent_msg) AS sent_msg,
    SUM(open_msg) AS open_msg,
    SUM(visit_msg) AS visit_msg,
FROM base_metrics
GROUP BY date, country, send_interval, is_verified, is_unsubscribed),


-- обчислення метрик в розрізі країн, які розраховуються на базі основних метрик у aggregated_metrics:
total_country AS (
SELECT
    date,
    country,
    send_interval,
    is_verified,
    is_unsubscribed,
    account_cnt,
    sent_msg,
    open_msg,
    visit_msg,
    SUM (account_cnt) OVER (PARTITION BY country) AS total_country_account_cnt,
    SUM (sent_msg) OVER (PARTITION BY country) AS total_country_sent_cnt
FROM aggregated_metrics
),


-- обчислення рангу в розрізі країн на базі total_country:
ranked_data AS (
SELECT
    date,
    country,
    send_interval,
    is_verified,
    is_unsubscribed,
    account_cnt,
    sent_msg,
    open_msg,
    visit_msg,
    total_country_account_cnt,
    total_country_sent_cnt,
    DENSE_RANK() OVER (ORDER BY total_country_account_cnt DESC) AS rank_total_country_account_cnt,
    DENSE_RANK () OVER (ORDER BY total_country_sent_cnt DESC) AS rank_total_country_sent_cnt
FROM total_country
)


--фінальний запит, вибрані всі указані в умові поля + WHERE фільтрація:
SELECT
    date,
    country,
    send_interval,
    is_verified,
    is_unsubscribed,
    account_cnt,
    sent_msg,
    open_msg,
    visit_msg,
    total_country_account_cnt,
    total_country_sent_cnt,
    rank_total_country_account_cnt,
    rank_total_country_sent_cnt
FROM ranked_data
WHERE rank_total_country_account_cnt <= 10 OR rank_total_country_sent_cnt <=10