22  Аналіз продуктових метрик

Минулого разу ми навчилися будувати повноцінні дашборди і представляти результати аналізу у зрозумілій бізнесі формі — це дуже важлива навичка, необхідна будь-якому аналітику.

Цього разу ми трохи докладніше поговоримо про аналіз найважливіших метрик, які дозволяють з різних боків оцінити те, наскільки добре працює наш продукт.

Насправді раніше ми вже рахували окремі показники — наприклад, метрики залучення користувачів DAU, WAU та MAU, кількість замовлень, частку скасованих замовлень тощо. Це дійсно важливі показники, але часто представники бізнесу в першу чергу звертають увагу на більш зрозумілі показники, виражені в грошовій формі. Це можуть бути виручка, витрати чи рентабельність, а також відносні показники на кшталт доходу на одного користувача та середнього чека – все це теж важливо вміти розраховувати, чим ми з вами й займемося.

Крім того, наприкінці ми обговоримо методику розрахунку ще одного важливого показника – Retention rate.

На цей раз вам знову буде запропоновано написати кілька SQL-запитів і візуалізувати їх результат за допомогою графіків.

22.1 Виручка

Почнемо з виручки - найбільш загального показника, який покаже, який дохід приносить наш сервіс.

Завдання 22.1
Для кожного дня в таблиці orders розрахуйте такі показники:

1. Виручку, одержану в цей день.
2. Сумарний виторг на поточний день.
3. Приріст виручки, отриманої цього дня, щодо значення виручки за попередній день.

Колонки з показниками назвіть відповідно revenue, total_revenue, revenue_change. Колонку з датами назвіть date.

Приріст виручки розрахуйте у відсотках та округліть значення до двох знаків після коми.

Результат має бути відсортований за зростанням дати.

Поля в результуючій таблиці: date, revenue, total_revenue, revenue_change

Пояснення:

Вважатимемо, що оплата за замовлення надходить відразу після його оформлення, тобто випадки, коли замовлення було оформлено в один день, а оплата отримана наступного, виникнути не можуть.

Сумарна виручка на поточний день – це результат складання виручки, отриманої поточного дня, зі значеннями аналогічного показника всіх попередніх днів.

При розрахунку виручки пам’ятайте, що не всі замовлення були сплачені, деякі були скасовані користувачами.

Не забувайте при діленні заздалегідь переводити значення до потрібного типу даних. Пропущені значення приросту для першої дати не заповнюйте - просто залиште поля в цьому рядку порожніми.

Підказка на випадок, якщо зовсім не виходить

Для вирішення завдання вам знадобиться інформація про замовлення з таблиці orders та ціни на товари з таблиці products. Щоб порахувати виторг для кожного дня, спочатку необхідно порахувати вартість кожного замовлення. Це можна зробити, склавши ціни товарів, що входять на замовлення. Щоб правильно приєднати дані про ціни на товари, списки із вмістом замовлень потрібно попередньо розширити за допомогою функції unnest. Після того як для кожного дня буде порахована сумарна вартість усіх замовлень (виручка), за допомогою віконних функцій можна порахувати суму наростаючим підсумком (загальну виручку) та приріст виручки (різницю між виручкою в поточний день та виручкою в попередній день, поділену на виручку в попередній день).

%%sql
SELECT date,
       revenue,
       sum(revenue) OVER (ORDER BY date) as total_revenue,
       round(100 * (revenue - lag(revenue, 1) OVER (ORDER BY date))::decimal / lag(revenue, 1) OVER (ORDER BY date),
             2) as revenue_change
FROM   (SELECT creation_time::date as date,
               sum(price) as revenue
        FROM   (SELECT creation_time,
                       unnest(product_ids) as product_id
                FROM   orders
                WHERE  order_id not in (SELECT order_id
                                        FROM   user_actions
                                        WHERE  action = 'cancel_order')) t1
            LEFT JOIN products using (product_id)
        GROUP BY date) t2

	date	revenue	total_revenue	revenue_change
0	2022-08-24	49924.0	49924.0	NaN
1	2022-08-25	430860.0	480784.0	763.03
2	2022-08-26	534766.0	1015550.0	24.12
3	2022-08-27	817053.0	1832603.0	52.79
4	2022-08-28	1133370.0	2965973.0	38.71
5	2022-08-29	1279891.0	4245864.0	12.93
6	2022-08-30	1279377.0	5525241.0	-0.04
7	2022-08-31	1312720.0	6837961.0	2.61
8	2022-09-01	1406101.0	8244062.0	7.11
9	2022-09-02	1907107.0	10151169.0	35.63
10	2022-09-03	2210988.0	12362157.0	15.93
11	2022-09-04	2294009.0	14656166.0	3.75
12	2022-09-05	1784690.0	16440856.0	-22.20
13	2022-09-06	1330931.0	17771787.0	-25.43
14	2022-09-07	1807800.0	19579587.0	35.83
15	2022-09-08	2099508.0	21679095.0	16.14

Запишемо результат запиту у змінну results_1.

%%sql
results_1 << SELECT date,
       revenue,
       sum(revenue) OVER (ORDER BY date) as total_revenue,
       round(100 * (revenue - lag(revenue, 1) OVER (ORDER BY date))::decimal / lag(revenue, 1) OVER (ORDER BY date),
             2) as revenue_change
FROM   (SELECT creation_time::date as date,
               sum(price) as revenue
        FROM   (SELECT creation_time,
                       unnest(product_ids) as product_id
                FROM   orders
                WHERE  order_id not in (SELECT order_id
                                        FROM   user_actions
                                        WHERE  action = 'cancel_order')) t1
            LEFT JOIN products using (product_id)
        GROUP BY date) t2

Побудуємо візуалізацію за отриманими даними:

import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots

fig = make_subplots(specs=[[{"secondary_y": True}]])
fig.add_trace(
    go.Bar(
        name="revenue_change",
        x=results_1.date,
        y=results_1.revenue_change,
        offsetgroup=1,
    ),
    secondary_y=False,
)

fig.add_trace(
    go.Scatter(name="revenue", x=results_1.date, y=results_1.revenue, offsetgroup=2),
    secondary_y=True,
)
fig.update_yaxes(rangemode="tozero", secondary_y=True)
fig.update_layout(
    legend=dict(orientation="h", yanchor="bottom", y=1.02, xanchor="right", x=1),
)

fig.show()

fig = go.Figure()
fig.add_trace(
    go.Scatter(
        x=results_1.date,
        y=results_1.total_revenue,
        mode="lines+markers",
        name="total_revenue",
    )
)
fig.update_layout(
    legend=dict(orientation="h", yanchor="bottom", y=1.02, xanchor="right", x=1)
)
fig.show()

(a) Динаміка щоденної виручки

(b) Динаміка загальної виручки

Рисунок 22.1: Графік за результатами SQL-запиту

Проаналізуйте побудовані графіки та спробуйте відповісти на такі питання:

1. Які дні спостерігалося помітне зниження щоденної виручки?
2. Із чим це могло бути пов’язане?

22.2 ARPU, ARPPU та AOV

Тепер на основі даних про виторг розрахуємо кілька відносних показників, які покажуть, скільки в середньому споживачі готові платити за послуги нашого сервісу доставки. Зупинимося на наступних метриках:

1. ARPU (Average Revenue Per User) - середня виручка на одного користувача за певний період.
2. ARPPU (Average Revenue Per Paying User) - середня виручка на одного користувача, що оплачує замовлення за певний період.
3. AOV (Average Order Value) - середній чек або відношення виручки за певний період до загальної кількості замовлень за цей час.

Якщо за період, що розглядається, сервіс заробив 100 000 грошових одиниць і при цьому ним користувалися 500 унікальних користувачів, з яких 400 зробили загалом 650 замовлень, тоді метрики будуть мати наступні значення:

\[ARPU = 100000/500 = 200\]

\[ARPPU = 100 000/400 = 250\]

\[AOV=100000/650≈153,85\]

Завдання 22.2
Для кожного дня в таблицях orders та user_actions розрахуйте такі показники:

1. Виручку користувача (ARPU) за поточний день.
2. Виручку на користувача, що платить (ARPPU) за поточний день.
3. Виручку із замовлення, або середній чек (AOV) за поточний день.

Колонки з показниками назвіть відповідно arpu, arppu, aov. Колонку з датами назвіть date.

Під час розрахунку всіх показників округляйте значення до двох знаків після коми.

Результат має бути відсортований за зростанням дати.

Поля в результуючій таблиці: date, arpu, arppu, aov

Пояснення:

Вважатимемо, що оплата за замовлення надходить відразу після його оформлення, тобто випадки, коли замовлення було оформлено в один день, а оплата отримана наступного, виникнути не можуть.

Користувачами, що оплатили замовлення будемо вважати тих, які в даний день оформили хоча б одне замовлення, яке надалі не було скасовано.

При розрахунку виручки пам’ятайте, що не всі замовлення були сплачені, деякі були скасовані користувачами.

Не забувайте при діленні заздалегідь приводити значення до потрібного типу даних.

Підказка на випадок, якщо зовсім не виходить

Для вирішення завдання необхідно спочатку для кожного дня порахувати виручку, кількість всіх користувачів, кількість користувачів, що сплатили замовлення і кількість замовлень. Потім необхідно об’єднати отримані таблиці та розрахувати всі необхідні відносні показники. Виручку ми вже рахували у минулому завданні.

%%sql
SELECT date,
       round(revenue::decimal / users, 2) as arpu,
       round(revenue::decimal / paying_users, 2) as arppu,
       round(revenue::decimal / orders, 2) as aov
FROM   (SELECT creation_time::date as date,
               count(distinct order_id) as orders,
               sum(price) as revenue
        FROM   (SELECT order_id,
                       creation_time,
                       unnest(product_ids) as product_id
                FROM   orders
                WHERE  order_id not in (SELECT order_id
                                        FROM   user_actions
                                        WHERE  action = 'cancel_order')) t1
            LEFT JOIN products using(product_id)
        GROUP BY date) t2
    LEFT JOIN (SELECT time::date as date,
                      count(distinct user_id) as users
               FROM   user_actions
               GROUP BY date) t3 using (date)
    LEFT JOIN (SELECT time::date as date,
                      count(distinct user_id) as paying_users
               FROM   user_actions
               WHERE  order_id not in (SELECT order_id
                                       FROM   user_actions
                                       WHERE  action = 'cancel_order')
               GROUP BY date) t4 using (date)
ORDER BY date

	date	arpu	arppu	aov
0	2022-08-24	372.57	393.10	361.77
1	2022-08-25	508.09	525.44	406.86
2	2022-08-26	452.04	470.33	369.57
3	2022-08-27	509.38	527.81	381.62
4	2022-08-28	528.38	544.10	378.04
5	2022-08-29	559.15	581.24	391.76
6	2022-08-30	546.74	567.85	379.52
7	2022-08-31	517.63	540.21	384.96
8	2022-09-01	499.33	518.86	381.26
9	2022-09-02	537.67	556.17	381.35
10	2022-09-03	565.90	582.76	387.28
11	2022-09-04	541.55	558.97	381.70
12	2022-09-05	512.84	530.84	381.75
13	2022-09-06	475.33	492.75	385.67
14	2022-09-07	496.65	514.02	378.44
15	2022-09-08	521.10	536.68	383.54

Збережемо результат запиту у змінну results_2.

%%sql
results_2 << SELECT date,
       round(revenue::decimal / users, 2) as arpu,
       round(revenue::decimal / paying_users, 2) as arppu,
       round(revenue::decimal / orders, 2) as aov
FROM   (SELECT creation_time::date as date,
               count(distinct order_id) as orders,
               sum(price) as revenue
        FROM   (SELECT order_id,
                       creation_time,
                       unnest(product_ids) as product_id
                FROM   orders
                WHERE  order_id not in (SELECT order_id
                                        FROM   user_actions
                                        WHERE  action = 'cancel_order')) t1
            LEFT JOIN products using(product_id)
        GROUP BY date) t2
    LEFT JOIN (SELECT time::date as date,
                      count(distinct user_id) as users
               FROM   user_actions
               GROUP BY date) t3 using (date)
    LEFT JOIN (SELECT time::date as date,
                      count(distinct user_id) as paying_users
               FROM   user_actions
               WHERE  order_id not in (SELECT order_id
                                       FROM   user_actions
                                       WHERE  action = 'cancel_order')
               GROUP BY date) t4 using (date)
ORDER BY date

Візуалізуємо отримані дані:

import plotly.graph_objects as go

fig = go.Figure()

traces = [("arpu", results_2.arpu), ("arppu", results_2.arppu), ("aov", results_2.aov)]

for trace_name, trace_values in traces:
    fig.add_trace(
        go.Scatter(
            x=results_2.date, y=trace_values, mode="lines+markers", name=trace_name
        )
    )

fig.update_layout(
    legend=dict(orientation="h", yanchor="bottom", y=1.02, xanchor="right", x=1)
)

fig.show()

Рисунок 22.2: Динаміка ARPU, ARPPU та AOV

Проаналізуйте побудований графік та спробуйте відповісти на такі питання:

1. Які метрики мають більший розкид значень протягом періоду, що розглядається?
2. Чи можна сказати, що окремі метрики мають аномально високі чи аномально низькі значення окремими днями?
3. Який висновок можна зробити про співвідношення числа користувачів, що платять, і всіх користувачів сервісу в розглянуті дні?

Доповнимо наш аналіз ще більш цікавими розрахунками — обчислимо ті самі метрики, але для кожного дня враховуватимемо накопичену виручку і всі наявні на даний момент дані про кількість користувачів і замовлень. Таким чином, отримаємо динамічний ARPU, ARPPU і AOV і зможемо простежити, як він змінювався протягом часу з урахуванням даних, що надходять нам.

Завдання 22.3
За таблицями orders та user_actions для кожного дня розрахуйте такі показники:

1. Накопичений виторг на користувача (Running ARPU).
2. Нагромаджений виторг на платить користувача (Running ARPPU).
3. Накопичений виторг із замовлення, або середній чек (Running AOV).

Колонки з показниками назвіть running_arpu, running_arppu, running_aov. Колонку з датами назвіть date.

Під час розрахунку всіх показників округляйте значення до двох знаків після коми.

Результат має бути відсортований за зростанням дати.

Поля в результуючій таблиці: date, running_arpu, running_arppu, running_aov

Пояснення:

При розрахунку числа користувачів та користувачів, що оплатили замовлення на поточну дату, враховуйте відповідних користувачів за всі попередні дні, включаючи поточний.

Користувачами, що оплатили замовлення вважатимемо тих, які на поточний день оформили хоча б одне замовлення, яке надалі не було скасовано.

Вважатимемо, що оплата за замовлення надходить відразу після його оформлення, тобто. Випадки, коли замовлення було оформлено в один день, а оплата отримана наступного, виникнути не можуть.

При розрахунку виручки пам’ятайте, що не всі замовлення були сплачені, деякі були скасовані користувачами.

Не забувайте при діленні заздалегідь приводити значення до потрібного типу даних.

Підказка на випадок, якщо зовсім не виходить

Для вирішення цього завдання необхідно доповнити запит з попереднього завдання та для кожного дня додатково розрахувати накопичений виторг, а також накопичену кількість усіх користувачів та оркмо користувачів, які оплатили замовлення. Для розрахунку кількості користувачів з накопиченням буде потрібна інформація про нових користувачів і нових користувачів, які оплатили замовлення у кожен із розглянутих днів.

%%sql
SELECT date, 
        ROUND(SUM(revenue) OVER (ORDER BY date)::decimal / SUM(new_users) OVER (ORDER BY date), 2) AS running_arpu, 
        ROUND(SUM(revenue) OVER (ORDER BY date)::decimal / SUM(new_paying_users) OVER (ORDER BY date), 2) AS running_arppu, 
        ROUND(SUM(revenue) OVER (ORDER BY date)::decimal / SUM(orders) OVER (ORDER BY date), 2) AS running_aov 
    FROM ( 
        SELECT creation_time::date AS date,  
                COUNT(DISTINCT order_id) AS orders,  
                SUM(price) AS revenue 
        FROM ( 
            SELECT order_id, 
                creation_time, 
                UNNEST(product_ids) AS product_id 
            FROM orders 
            WHERE order_id NOT IN (SELECT order_id FROM user_actions WHERE action='cancel_order') 
            ) t1 
        LEFT JOIN products  
        USING(product_id) 
        GROUP BY date 
    ) t2 
    LEFT JOIN ( 
        SELECT time::date AS date, COUNT(DISTINCT user_id) AS users 
        FROM user_actions 
        GROUP BY date 
    ) t3 
    USING (date) 
    LEFT JOIN ( 
        SELECT time::date AS date, COUNT(DISTINCT user_id) AS paying_users 
        FROM user_actions 
        WHERE order_id NOT IN (SELECT order_id FROM user_actions WHERE action='cancel_order') 
        GROUP BY date 
    ) t4 
    USING (date) 
    LEFT JOIN ( 
        SELECT date, COUNT(user_id) AS new_users 
        FROM ( 
            SELECT user_id, MIN(time::date) AS date 
            FROM user_actions 
            GROUP BY user_id 
        ) t5 
        GROUP BY date 
    ) t6 
    USING (date) 
    LEFT JOIN ( 
        SELECT date, COUNT(user_id) AS new_paying_users 
        FROM ( 
            SELECT user_id, MIN(time::date) AS date 
            FROM user_actions 
            WHERE order_id NOT IN (SELECT order_id FROM user_actions WHERE action='cancel_order') 
            GROUP BY user_id 
        ) t7 
        GROUP BY date 
    ) t8 
    USING (date)

	date	running_arpu	running_arppu	running_aov
0	2022-08-24	372.57	393.10	361.77
1	2022-08-25	499.26	517.53	401.66
2	2022-08-26	512.90	530.87	384.10
3	2022-08-27	571.80	590.21	382.99
4	2022-08-28	632.13	649.72	381.08
5	2022-08-29	707.53	726.29	384.24
6	2022-08-30	766.86	786.40	383.14
7	2022-08-31	792.81	813.46	383.49
8	2022-09-01	813.18	832.90	383.11
9	2022-09-02	844.17	863.05	382.77
10	2022-09-03	886.24	904.39	383.57
11	2022-09-04	921.71	938.78	383.28
12	2022-09-05	950.45	967.17	383.11
13	2022-09-06	970.18	986.72	383.30
14	2022-09-07	992.38	1007.85	382.85
15	2022-09-08	1012.99	1028.03	382.91

Збережемо результат запиту у змінну results_3:

%%sql
results_3 << SELECT date, 
        ROUND(SUM(revenue) OVER (ORDER BY date)::decimal / SUM(new_users) OVER (ORDER BY date), 2) AS running_arpu, 
        ROUND(SUM(revenue) OVER (ORDER BY date)::decimal / SUM(new_paying_users) OVER (ORDER BY date), 2) AS running_arppu, 
        ROUND(SUM(revenue) OVER (ORDER BY date)::decimal / SUM(orders) OVER (ORDER BY date), 2) AS running_aov 
    FROM ( 
        SELECT creation_time::date AS date,  
                COUNT(DISTINCT order_id) AS orders,  
                SUM(price) AS revenue 
        FROM ( 
            SELECT order_id, 
                creation_time, 
                UNNEST(product_ids) AS product_id 
            FROM orders 
            WHERE order_id NOT IN (SELECT order_id FROM user_actions WHERE action='cancel_order') 
            ) t1 
        LEFT JOIN products  
        USING(product_id) 
        GROUP BY date 
    ) t2 
    LEFT JOIN ( 
        SELECT time::date AS date, COUNT(DISTINCT user_id) AS users 
        FROM user_actions 
        GROUP BY date 
    ) t3 
    USING (date) 
    LEFT JOIN ( 
        SELECT time::date AS date, COUNT(DISTINCT user_id) AS paying_users 
        FROM user_actions 
        WHERE order_id NOT IN (SELECT order_id FROM user_actions WHERE action='cancel_order') 
        GROUP BY date 
    ) t4 
    USING (date) 
    LEFT JOIN ( 
        SELECT date, COUNT(user_id) AS new_users 
        FROM ( 
            SELECT user_id, MIN(time::date) AS date 
            FROM user_actions 
            GROUP BY user_id 
        ) t5 
        GROUP BY date 
    ) t6 
    USING (date) 
    LEFT JOIN ( 
        SELECT date, COUNT(user_id) AS new_paying_users 
        FROM ( 
            SELECT user_id, MIN(time::date) AS date 
            FROM user_actions 
            WHERE order_id NOT IN (SELECT order_id FROM user_actions WHERE action='cancel_order') 
            GROUP BY user_id 
        ) t7 
        GROUP BY date 
    ) t8 
    USING (date)

Візуалізуємо отримані дані:

import plotly.graph_objects as go

fig = go.Figure()

traces = [
    ("running_arpu", results_3.running_arpu),
    ("running_arppu", results_3.running_arppu),
    ("running_aov", results_3.running_aov),
]

for trace_name, trace_values in traces:
    fig.add_trace(
        go.Scatter(
            x=results_3.date, y=trace_values, mode="lines+markers", name=trace_name
        )
    )

fig.update_layout(
    legend=dict(orientation="h", yanchor="bottom", y=1.02, xanchor="right", x=1)
)

fig.show()

Рисунок 22.3: Динаміка Running ARPU, Running ARPPU, Running AOV

Проаналізуйте побудований графік та спробуйте відповісти на такі питання:

1. Яка загалом динаміка у розрахованих метрик? Вони ростуть, падають чи мають приблизно однакове значення у кожен із днів?
2. Чи можна з огляду на динаміку розрахованих метрик припустити, що з часом зростає кількість замовлень на одного користувача?

Давайте порахуємо ті ж показники, але в іншому розрізі — не просто щодня, а щодня тижня.

Для виділення порядкового номера тижня можна використовувати функцію DATE_PART з параметром 'isodow'.

Завдання 22.4
Для кожного дня тижня в таблицях orders та user_actions розрахуйте такі показники:

1. Виручку користувача (ARPU).
2. Виручку на користувача, що платить (ARPPU).
3. Виторг на замовлення (AOV).

При розрахунках враховуйте дані лише за період з 26 серпня 2022 року до 8 вересня 2022 року включно — так, щоб до аналізу потрапила однакова кількість усіх днів тижня (рівно по два дні).

У результуючу таблицю включіть як найменування днів тижня (наприклад, Monday), так і порядковий номер дня тижня (від 1 до 7, де 1 Monday, 7 Sunday).

Колонки з показниками назвіть відповідно arpu, arppu, aov. Назвіть колонку з найменуванням дня тижня weekday, а колонку з порядковим номером дня тижня weekday_number.

Під час розрахунку всіх показників округляйте значення до двох знаків після коми.

Результат має бути відсортований за зростанням порядкового номера дня тижня.

Поля в результуючій таблиці: weekday, weekday_number, arpu, arppu, aov

Пояснення:

У цій задачі порядковий номер дня тижня необхідний для того, щоб дні тижня були розташовані на графіці зліва направо в правильному порядку — не за найменуванням, а за зростанням порядкового номера.

Підказка на випадок, якщо зовсім не виходить

Для розв’язання задачі необхідно виконати ті самі операції, що й у попередньому завданні, тільки цього разу для днів тижня. Додатково необхідно правильно задати фільтрацію за датою, щоб у аналіз потрапило рівно два однакові дні тижня.

%%sql
SELECT weekday, t1.weekday_number AS weekday_number, 
        ROUND(revenue::decimal / users, 2) AS arpu, 
        ROUND(revenue::decimal / paying_users, 2) AS arppu, 
        ROUND(revenue::decimal / orders, 2) AS aov 
    FROM ( 
        SELECT DATE_PART('isodow', creation_time) AS weekday, 
                MAX(DATE_PART('isodow', creation_time)) AS weekday_number, 
                COUNT(DISTINCT order_id) AS orders,  
                SUM(price) AS revenue 
        FROM ( 
            SELECT order_id, 
                creation_time, 
                UNNEST(product_ids) AS product_id 
            FROM orders 
            WHERE order_id NOT IN (SELECT order_id FROM user_actions WHERE action='cancel_order') 
                AND creation_time >= '2022-08-26' AND creation_time < '2022-09-09' 
            ) t4 
        LEFT JOIN products  
        USING(product_id) 
        GROUP BY weekday 
    ) t1 
    LEFT JOIN ( 
        SELECT DATE_PART('isodow', time) AS weekday, 
            MAX(DATE_PART('isodow', time)) AS weekday_number, 
            COUNT(DISTINCT user_id) AS users 
        FROM user_actions 
        WHERE time >= '2022-08-26' AND time < '2022-09-09' 
        GROUP BY weekday 
    ) t2 
    USING (weekday) 
    LEFT JOIN ( 
        SELECT DATE_PART('isodow', time) AS weekday,  
            MAX(DATE_PART('isodow', time)) AS weekday_number, 
            COUNT(DISTINCT user_id) AS paying_users 
        FROM user_actions 
        WHERE order_id NOT IN (SELECT order_id FROM user_actions WHERE action='cancel_order') 
            AND time >= '2022-08-26' AND time < '2022-09-09' 
        GROUP BY weekday 
    ) t3 
    USING (weekday) 
    ORDER BY weekday_number

	weekday	weekday_number	arpu	arppu	aov
0	1	1	555.98	575.18	385.87
1	2	2	528.94	548.04	382.63
2	3	3	528.90	548.33	381.16
3	4	4	533.98	551.37	382.62
4	5	5	534.79	553.21	378.70
5	6	6	578.53	595.48	385.74
6	7	7	566.23	583.38	380.48

Збережемо результат запиту у змінну results_4:

%%sql
results_4 << SELECT weekday, t1.weekday_number AS weekday_number, 
        ROUND(revenue::decimal / users, 2) AS arpu, 
        ROUND(revenue::decimal / paying_users, 2) AS arppu, 
        ROUND(revenue::decimal / orders, 2) AS aov 
    FROM ( 
        SELECT DATE_PART('isodow', creation_time) AS weekday, 
                MAX(DATE_PART('isodow', creation_time)) AS weekday_number, 
                COUNT(DISTINCT order_id) AS orders,  
                SUM(price) AS revenue 
        FROM ( 
            SELECT order_id, 
                creation_time, 
                UNNEST(product_ids) AS product_id 
            FROM orders 
            WHERE order_id NOT IN (SELECT order_id FROM user_actions WHERE action='cancel_order') 
                AND creation_time >= '2022-08-26' AND creation_time < '2022-09-09' 
            ) t4 
        LEFT JOIN products  
        USING(product_id) 
        GROUP BY weekday 
    ) t1 
    LEFT JOIN ( 
        SELECT DATE_PART('isodow', time) AS weekday, 
            MAX(DATE_PART('isodow', time)) AS weekday_number, 
            COUNT(DISTINCT user_id) AS users 
        FROM user_actions 
        WHERE time >= '2022-08-26' AND time < '2022-09-09' 
        GROUP BY weekday 
    ) t2 
    USING (weekday) 
    LEFT JOIN ( 
        SELECT DATE_PART('isodow', time) AS weekday,  
            MAX(DATE_PART('isodow', time)) AS weekday_number, 
            COUNT(DISTINCT user_id) AS paying_users 
        FROM user_actions 
        WHERE order_id NOT IN (SELECT order_id FROM user_actions WHERE action='cancel_order') 
            AND time >= '2022-08-26' AND time < '2022-09-09' 
        GROUP BY weekday 
    ) t3 
    USING (weekday) 
    ORDER BY weekday_number

Візуалізуємо отримані дані:

import plotly.graph_objects as go

days = [
    "Monday",
    "Tuesday",
    "Wednesday",
    "Thursday",
    "Friday",
    "Saturday",
    "Sunday",
]

fig = go.Figure()

traces = [("arpu", results_4.arpu), ("arppu", results_4.arppu), ("aov", results_4.aov)]

for trace_name, trace_values in traces:
    fig.add_trace(
        go.Scatter(x=days, y=trace_values, mode="lines+markers", name=trace_name)
    )

fig.update_layout(
    legend=dict(orientation="h", yanchor="bottom", y=1.02, xanchor="right", x=1)
)

fig.show()

Рисунок 22.4: Динаміка ARPU, ARPPU та AOV за днями тижня

Проаналізуйте побудований графік та спробуйте відповісти на такі питання:

1. У які дні тижня метрики ARPU та ARPPU набували найбільших значень? Як ви вважаєте, чи це узгоджується в цілому зі стандартною поведінкою користувачів сервісу доставки їжі?
2. Як ви вважаєте, чому в ті дні, коли метрики ARPU та ARPPU набували найбільших значень, метрика AOV залишалася приблизно на тому ж рівні? За якого сценарію таке можливе?

22.3 Виручка нових користувачів

Трохи ускладнимо наш початковий запит і окремо порахуємо щоденну виручку із замовлень нових користувачів нашого сервісу. Подивимося, яку частку вона становить у загальному виторгу із замовлень всіх користувачів — і нових, і старих.

Завдання 22.5
Для кожного дня в таблицях orders та user_actions розрахуйте такі показники:

1. Виручку, одержану в цей день.
2. Виручку із замовлень нових користувачів, отриману цього дня.
3. Частку виручки із замовлень нових користувачів у загальній виручці, отриманої за цей день.
4. Частку виручки із замовлень інших користувачів у загальній виручці, отриманої за цей день.

Колонки з показниками назвіть відповідно revenue, new_users_revenue, new_users_revenue_share, old_users_revenue_share. Колонку з датами назвіть date.

Усі показники часток необхідно виразити у відсотках. Під час їх розрахунку округляйте значення до двох знаків після коми.

Результат має бути відсортований за зростанням дати.

Поля в результуючій таблиці: date, revenue, new_users_revenue, new_users_revenue_share, old_users_revenue_share

Підказка на випадок, якщо зовсім не виходить

Для вирішення завдання необхідно для кожного дня порахувати виручку з нових користувачів. Щоб це зробити, спочатку необхідно для кожного користувача визначити мінімальну дату, тобто дату першої дії в нашому сервісі, а також порахувати вартість кожного замовлення в таблиці orders. Потім, об’єднавши дані про вартість замовлень з даними про дії користувачів у таблиці user_actions, можна для кожного користувача порахувати сумарну вартість його замовлень на кожну дату (групування за двома полями) та об’єднати ці дані з таблицею з датами початку використання додатку у кожного користувача (порахованою раніше). Таким чином, можна отримати таблицю з виручкою, одержаною з кожного користувача у його перший день. Далі залишається зробити групування за датою та порахувати сумарний виторг з нових користувачів за кожен день.

%%sql
SELECT date,
       revenue,
       new_users_revenue,
       round(new_users_revenue / revenue * 100, 2) as new_users_revenue_share,
       100 - round(new_users_revenue / revenue * 100, 2) as old_users_revenue_share
FROM   (SELECT creation_time::date as date,
               sum(price) as revenue
        FROM   (SELECT order_id,
                       creation_time,
                       unnest(product_ids) as product_id
                FROM   orders
                WHERE  order_id not in (SELECT order_id
                                        FROM   user_actions
                                        WHERE  action = 'cancel_order')) t3
            LEFT JOIN products using (product_id)
        GROUP BY date) t1
    LEFT JOIN (SELECT start_date as date,
                      sum(revenue) as new_users_revenue
               FROM   (SELECT t5.user_id,
                              t5.start_date,
                              coalesce(t6.revenue, 0) as revenue
                       FROM   (SELECT user_id,
                                      min(time::date) as start_date
                               FROM   user_actions
                               GROUP BY user_id) t5
                           LEFT JOIN (SELECT user_id,
                                             date,
                                             sum(order_price) as revenue
                                      FROM   (SELECT user_id,
                                                     time::date as date,
                                                     order_id
                                              FROM   user_actions
                                              WHERE  order_id not in (SELECT order_id
                                                                      FROM   user_actions
                                                                      WHERE  action = 'cancel_order')) t7
                                          LEFT JOIN (SELECT order_id,
                                                            sum(price) as order_price
                                                     FROM   (SELECT order_id,
                                                                    unnest(product_ids) as product_id
                                                             FROM   orders
                                                             WHERE  order_id not in (SELECT order_id
                                                                                     FROM   user_actions
                                                                                     WHERE  action = 'cancel_order')) t9
                                                         LEFT JOIN products using (product_id)
                                                     GROUP BY order_id) t8 using (order_id)
                                      GROUP BY user_id, date) t6
                               ON t5.user_id = t6.user_id and
                                  t5.start_date = t6.date) t4
               GROUP BY start_date) t2 using (date)

	date	revenue	new_users_revenue	new_users_revenue_share	old_users_revenue_share
0	2022-08-24	49924.0	49924.0	100.00	0.00
1	2022-08-25	430860.0	417333.0	96.86	3.14
2	2022-08-26	534766.0	463326.0	86.64	13.36
3	2022-08-27	817053.0	619318.0	75.80	24.20
4	2022-08-28	1133370.0	801162.0	70.69	29.31
5	2022-08-29	1279891.0	717374.0	56.05	43.95
6	2022-08-30	1279377.0	656429.0	51.31	48.69
7	2022-08-31	1312720.0	720381.0	54.88	45.12
8	2022-09-01	1406101.0	757287.0	53.86	46.14
9	2022-09-02	1907107.0	1017824.0	53.37	46.63
10	2022-09-03	2210988.0	1079256.0	48.81	51.19
11	2022-09-04	2294009.0	1063997.0	46.38	53.62
12	2022-09-05	1784690.0	714459.0	40.03	59.97
13	2022-09-06	1330931.0	495058.0	37.20	62.80
14	2022-09-07	1807800.0	710154.0	39.28	60.72
15	2022-09-08	2099508.0	887959.0	42.29	57.71

Збережемо результат запиту у змінну results_5:

%%sql
results_5 << SELECT date,
       revenue,
       new_users_revenue,
       round(new_users_revenue / revenue * 100, 2) as new_users_revenue_share,
       100 - round(new_users_revenue / revenue * 100, 2) as old_users_revenue_share
FROM   (SELECT creation_time::date as date,
               sum(price) as revenue
        FROM   (SELECT order_id,
                       creation_time,
                       unnest(product_ids) as product_id
                FROM   orders
                WHERE  order_id not in (SELECT order_id
                                        FROM   user_actions
                                        WHERE  action = 'cancel_order')) t3
            LEFT JOIN products using (product_id)
        GROUP BY date) t1
    LEFT JOIN (SELECT start_date as date,
                      sum(revenue) as new_users_revenue
               FROM   (SELECT t5.user_id,
                              t5.start_date,
                              coalesce(t6.revenue, 0) as revenue
                       FROM   (SELECT user_id,
                                      min(time::date) as start_date
                               FROM   user_actions
                               GROUP BY user_id) t5
                           LEFT JOIN (SELECT user_id,
                                             date,
                                             sum(order_price) as revenue
                                      FROM   (SELECT user_id,
                                                     time::date as date,
                                                     order_id
                                              FROM   user_actions
                                              WHERE  order_id not in (SELECT order_id
                                                                      FROM   user_actions
                                                                      WHERE  action = 'cancel_order')) t7
                                          LEFT JOIN (SELECT order_id,
                                                            sum(price) as order_price
                                                     FROM   (SELECT order_id,
                                                                    unnest(product_ids) as product_id
                                                             FROM   orders
                                                             WHERE  order_id not in (SELECT order_id
                                                                                     FROM   user_actions
                                                                                     WHERE  action = 'cancel_order')) t9
                                                         LEFT JOIN products using (product_id)
                                                     GROUP BY order_id) t8 using (order_id)
                                      GROUP BY user_id, date) t6
                               ON t5.user_id = t6.user_id and
                                  t5.start_date = t6.date) t4
               GROUP BY start_date) t2 using (date)

Візуалізуємо отримані дані:

fig = go.Figure(
    data=[
        go.Bar(
            name="new_users_revenue_share",
            x=results_5.date,
            y=results_5.new_users_revenue_share,
        ),
        go.Bar(
            name="old_users_revenue_share",
            x=results_5.date,
            y=results_5.old_users_revenue_share,
        ),
    ]
)
fig.update_layout(
    barmode="stack",
    legend=dict(orientation="h", yanchor="bottom", y=1.02, xanchor="right", x=1),
)
fig.show()

Рисунок 22.5: Динаміка виручки нових та старих користувачів

Чи можна сказати, що через два тижні після запуску нашого сервісу показник виручки від нових користувачів, як і раніше, на досить високому рівні?

22.4 Попит на товари

Також було б цікаво подивитися, які товари мають найбільший попит і приносять нам основний дохід.

Завдання 22.6
Для кожного товару, представленого в таблиці products, за весь період часу в таблиці orders розрахуйте наступні показники:

1. Сумарний виторг, отриманий від продажу цього товару за весь період.
2. Частку виручки від цього товару у загальної виручці, отриманої протягом період.

Колонки з показниками назвіть відповідно revenue та share_in_revenue. Колонку із найменуваннями товарів назвіть product_name.

Частку виручки з кожного товару необхідно виразити у відсотках. При її розрахунку округляйте значення до двох знаків після коми.

Товари, заокруглена частка яких у виручці становить менше 0.5%, об’єднайте в загальну групу під назвою “OTHER” (без лапок), підсумувавши заокруглені частки цих товарів.

Результат має бути відсортований за зменшенням виручки від продажу товару.

Поля в результуючій таблиці: product_name, revenue, share_in_revenue

Пояснення:

Товари з невеликою часткою у виручці необхідно об’єднати в одну групу, щоб не виводити на графіку всі товари з таблиці products.

Підказка на випадок, якщо зовсім не виходить

Для вирішення завдання необхідно згадати, як працює конструкція CASE, і порахувати виторг у розрізі найменувань товарів, представлених у таблиці products. Загальний алгоритм розрахунку виручки той самий, що у попередніх завданнях.

%%sql
SELECT product_name,
       sum(revenue) as revenue,
       sum(share_in_revenue) as share_in_revenue
FROM   (SELECT case when round(100 * revenue / sum(revenue) OVER (), 2) >= 0.5 then name
                    else 'OTHER' end as product_name,
               revenue,
               round(100 * revenue / sum(revenue) OVER (), 2) as share_in_revenue
        FROM   (SELECT name,
                       sum(price) as revenue
                FROM   (SELECT order_id,
                               unnest(product_ids) as product_id
                        FROM   orders
                        WHERE  order_id not in (SELECT order_id
                                                FROM   user_actions
                                                WHERE  action = 'cancel_order')) t1
                    LEFT JOIN products using(product_id)
                GROUP BY name) t2) t3
GROUP BY product_name
ORDER BY revenue desc

	product_name	revenue	share_in_revenue
0	pork	1353600.0	6.24
1	OTHER	1225387.0	5.64
2	chicken	1171140.0	5.40
3	olive oil	1163250.0	5.37
4	beef	977170.0	4.51
...	...	...	...
63	rice	118930.0	0.55
64	herbal tea bags	112580.0	0.52
65	tea mushroom	112448.0	0.52
66	sesame oil	109750.0	0.51
67	chewing gum	109650.0	0.51

68 rows × 3 columns

Збережемо результат запиту у змінну results_6:

%%sql
results_6 << SELECT product_name,
       sum(revenue) as revenue,
       sum(share_in_revenue) as share_in_revenue
FROM   (SELECT case when round(100 * revenue / sum(revenue) OVER (), 2) >= 0.5 then name
                    else 'OTHER' end as product_name,
               revenue,
               round(100 * revenue / sum(revenue) OVER (), 2) as share_in_revenue
        FROM   (SELECT name,
                       sum(price) as revenue
                FROM   (SELECT order_id,
                               unnest(product_ids) as product_id
                        FROM   orders
                        WHERE  order_id not in (SELECT order_id
                                                FROM   user_actions
                                                WHERE  action = 'cancel_order')) t1
                    LEFT JOIN products using(product_id)
                GROUP BY name) t2) t3
GROUP BY product_name
ORDER BY revenue desc

Візуалізуємо отримані дані:

fig = go.Figure()
fig.add_trace(
    go.Bar(
        x=results_6.product_name, y=results_6.share_in_revenue, name="share_in_revenue"
    )
)

fig.update_layout(
    xaxis_tickangle=-45,
    legend=dict(orientation="h", yanchor="bottom", y=1.02, xanchor="right", x=1),
)
fig.show()

Рисунок 22.6: Частка виручки за продуктами

Подивіться на побудований графік та визначте товари з найбільшою часткою у виручці. Якби ми об’єднали товари в ширші групи, то яка, на вашу думку, опинилася б на першому місці за часткою у виручці?

22.5 Валовий прибуток

Тепер спробуємо врахувати в наших розрахунках витрати з податками і порахуємо валовий прибуток, тобто ту суму, яку ми фактично отримали в результаті реалізації товарів за розглянутий період.

Завдання 22.7
Для кожного дня в таблицях orders та courier_actions розрахуйте такі показники:

1. Виручку, одержану в цей день.
2. Витрати цього дня.
3. Суму ПДВ із продажу товарів у цей день.
4. Валовий прибуток у цей день (виручка за вирахуванням витрат та ПДВ).
5. Сумарний виторг на поточний день.
6. Сумарні витрати на цей день.
7. Сумарний ПДВ на сьогодні.
8. Сумарний валовий прибуток на поточний день.
9. Частку валового прибутку у виручці цей день (частку п.4 в п.1).
10. Частку сумарного валового прибутку в сумарній виручці на поточний день (частку п.8 п.5).

Колонки з показниками назвіть відповідно revenue, costs, tax, gross_profit, total_revenue, total_costs, total_tax, total_gross_profit, gross_profit_ratio, total_gross_profit_ratio

Колонку з датами назвіть date.

Частку валового прибутку у виручці необхідно виразити у відсотках, округливши значення до двох знаків після коми.

Результат має бути відсортований за зростанням дати.

Поля в результуючій таблиці: date, revenue, costs, tax, gross_profit, total_revenue, total_costs, total_tax, total_gross_profit, gross_profit_ratio, total_gross_profit_ratio

Щоб порахувати витрати, в цій задачі введемо додаткові умови.

У спрощеному вигляді витрати нашого сервісу рахуватимемо як суму постійних і змінних витрат. До постійних витрат віднесемо оренду складських приміщень, а до змінних - вартість збирання та доставки замовлення. Таким чином, змінні витрати безпосередньо залежатимуть від кількості замовлень.

З даних, які нам надав фінансовий відділ, відомо, що у серпні 2022 року постійні витрати становили 120 000 грошових одиниць на день. Проте вже у вересні нашому сервісу були потрібні додаткові приміщення, і тому постійні витрати зросли до 150 000 грошових одиниць на день.

Також відомо, що в серпні 2022 року складання одного замовлення обходилася нам в 140 грошових одиниць, при цьому кур’єрам ми платили по 150 грошових одиниць за одне доставлене замовлення і ще 400 грошових одиниць щодня як бонус, якщо кур’єр доставляв не менше 5 замовлень на день. У вересні продакт-менеджерам вдалося знизити витрати на складання замовлення до 115 грошових одиниць, але при цьому довелося підвищити бонусну виплату за доставку 5 і більше замовлень до 500 грошових одиниць, щоб забезпечити конкурентоспроможніші умови праці. При цьому у вересні виплата кур’єрам за одне доставлене замовлення залишилася незмінною.

Пояснення:

При розрахунку змінних витрат враховуйте такі умови:

1. Витрати на складання враховуються в тому ж дні, коли було оформлено замовлення. Складання скасованих замовлень не проводиться.
2. Виплата кур’єрам за доставлене замовлення нараховується відразу після його доставки, тому якщо кур’єр доставить замовлення наступного дня, то й виплата буде врахована наступного дня.
3. Для отримання бонусної виплати кур’єрам необхідно доставити не менше ніж 5 замовлень протягом одного дня, тому якщо кур’єр прийме 5 замовлень протягом дня, але останній з них доставить після опівночі, бонусну виплату він не отримає.

При розрахунку ПДВ враховуйте, що з деяких товарів податок становить не 10%, а 20%. Список товарів зі зниженим ПДВ:

'sugar', 'crackers', 'drying', 'seeds',
'linseed oil', 'grapes', 'olive oil',
'watermelon', 'baton', 'yogurt', 'cream', 'buckwheat',
'oatmeal', 'pasta', 'mutton', 'oranges',
'bagels', 'bread', 'peas', 'sour cream', 'smoked fish',
'flour', 'sprats', 'sausages', 'pork', 'rice',
'sesame oil', 'condensed milk', 'pineapple', 'beef',
'salt', 'dried fish', 'sunflower oil', 'apples',
'pears', 'flatbread', 'milk', 'chicken', 'lavash', 'waffles', 'tangerines'

Також при розрахунку величини ПДВ за кожним товаром округляйте значення до двох знаків після коми.

Підказка на випадок, якщо зовсім не виходить

Для вирішення завдання необхідно для кожного дня окремо порахувати виручку, витрати та величину ПДВ, а потім об’єднати отримані таблиці для подальших розрахунків за допомогою віконних функцій. Виручку ми рахували в минулих завданнях, величину ПДВ ми також рахували на перших уроках - тепер це необхідно зробити для всіх товарів, куплених протягом кожного дня. Найскладніше у цьому - правильно обчислити витрати. Для цього необхідно для кожного дня порахувати кількість оформлених замовлень, кількість доставлених замовлень та кількість кур’єрів, що доставили 5 та більше замовлень. Потім необхідно кожного дня порахувати загальні витрати. Це можна зробити за допомогою конструкції CASE, підставивши постійні та змінні витрати на нескладну формулу, описану в умові.

%%sql
SELECT date,
       revenue,
       costs,
       tax,
       gross_profit,
       total_revenue,
       total_costs,
       total_tax,
       total_gross_profit,
       round(gross_profit / revenue * 100, 2) as gross_profit_ratio,
       round(total_gross_profit / total_revenue * 100, 2) as total_gross_profit_ratio
FROM   (SELECT date,
               revenue,
               costs,
               tax,
               revenue - costs - tax as gross_profit,
               sum(revenue) OVER (ORDER BY date) as total_revenue,
               sum(costs) OVER (ORDER BY date) as total_costs,
               sum(tax) OVER (ORDER BY date) as total_tax,
               sum(revenue - costs - tax) OVER (ORDER BY date) as total_gross_profit
        FROM   (SELECT date,
                       orders_packed,
                       orders_delivered,
                       couriers_count,
                       revenue,
                       case when date_part('month',
                                                                                                                                                                      date) = 8 then 120000.0 + 140 * coalesce(orders_packed, 0) + 150 * coalesce(orders_delivered, 0) + 400 * coalesce(couriers_count, 0)
                            when date_part('month',
                                                                                                                                                                      date) = 9 then 150000.0 + 115 * coalesce(orders_packed, 0) + 150 * coalesce(orders_delivered, 0) + 500 * coalesce(couriers_count, 0) end as costs,
                       tax
                FROM   (SELECT creation_time::date as date,
                               count(distinct order_id) as orders_packed,
                               sum(price) as revenue,
                               sum(tax) as tax
                        FROM   (SELECT order_id,
                                       creation_time,
                                       product_id,
                                       name,
                                       price,
                                       case when name in ('sugar', 'crackers', 'drying', 'seeds',
                                                          'linseed oil', 'grapes', 'olive oil',
                                                          'watermelon', 'baton', 'yogurt', 'cream', 'buckwheat',
                                                          'oatmeal', 'pasta', 'mutton', 'oranges',
                                                          'bagels', 'bread', 'peas', 'sour cream', 'smoked fish',
                                                          'flour', 'sprats', 'sausages', 'pork', 'rice',
                                                          'sesame oil', 'condensed milk', 'pineapple', 'beef',
                                                          'salt', 'dried fish', 'sunflower oil', 'apples',
                                                          'pears', 'flatbread', 'milk', 'chicken', 'lavash', 'waffles', 'tangerines') then round(price/110*10,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         2)
                                            else round(price/120*20, 2) end as tax
                                FROM   (SELECT order_id,
                                               creation_time,
                                               unnest(product_ids) as product_id
                                        FROM   orders
                                        WHERE  order_id not in (SELECT order_id
                                                                FROM   user_actions
                                                                WHERE  action = 'cancel_order')) t1
                                    LEFT JOIN products using (product_id)) t2
                        GROUP BY date) t3
                    LEFT JOIN (SELECT time::date as date,
                                      count(distinct order_id) as orders_delivered
                               FROM   courier_actions
                               WHERE  order_id not in (SELECT order_id
                                                       FROM   user_actions
                                                       WHERE  action = 'cancel_order')
                                  and action = 'deliver_order'
                               GROUP BY date) t4 using (date)
                    LEFT JOIN (SELECT date,
                                      count(courier_id) as couriers_count
                               FROM   (SELECT time::date as date,
                                              courier_id,
                                              count(distinct order_id) as orders_delivered
                                       FROM   courier_actions
                                       WHERE  order_id not in (SELECT order_id
                                                               FROM   user_actions
                                                               WHERE  action = 'cancel_order')
                                          and action = 'deliver_order'
                                       GROUP BY date, courier_id having count(distinct order_id) >= 5) t5
                               GROUP BY date) t6 using (date)) t7) t8

	date	revenue	costs	tax	gross_profit	total_revenue	total_costs	total_tax	total_gross_profit	gross_profit_ratio	total_gross_profit_ratio
0	2022-08-24	49924.0	159120.0	6459.77	-115655.77	49924.0	159120.0	6459.77	-115655.77	-231.66	-231.66
1	2022-08-25	430860.0	447560.0	54458.71	-71158.71	480784.0	606680.0	60918.48	-186814.48	-16.52	-38.86
2	2022-08-26	534766.0	565680.0	67272.24	-98186.24	1015550.0	1172360.0	128190.72	-285000.72	-18.36	-28.06
3	2022-08-27	817053.0	781040.0	104010.94	-67997.94	1832603.0	1953400.0	232201.66	-352998.66	-8.32	-19.26
4	2022-08-28	1133370.0	1055870.0	143194.74	-65694.74	2965973.0	3009270.0	375396.40	-418693.40	-5.80	-14.12
5	2022-08-29	1279891.0	1144280.0	163192.37	-27581.37	4245864.0	4153550.0	538588.77	-446274.77	-2.15	-10.51
6	2022-08-30	1279377.0	1169140.0	161750.92	-51513.92	5525241.0	5322690.0	700339.69	-497788.69	-4.03	-9.01
7	2022-08-31	1312720.0	1159250.0	165617.03	-12147.03	6837961.0	6481940.0	865956.72	-509935.72	-0.93	-7.46
8	2022-09-01	1406101.0	1180320.0	178047.87	47733.13	8244062.0	7662260.0	1044004.59	-462202.59	3.39	-5.61
9	2022-09-02	1907107.0	1590965.0	241912.13	74229.87	10151169.0	9253225.0	1285916.72	-387972.72	3.89	-3.82
10	2022-09-03	2210988.0	1813235.0	280998.88	116754.12	12362157.0	11066460.0	1566915.60	-271218.60	5.28	-2.19
11	2022-09-04	2294009.0	1885300.0	293110.13	115598.87	14656166.0	12951760.0	1860025.73	-155619.73	5.04	-1.06
12	2022-09-05	1784690.0	1456825.0	226580.62	101284.38	16440856.0	14408585.0	2086606.35	-54335.35	5.68	-0.33
13	2022-09-06	1330931.0	1077765.0	168922.71	84243.29	17771787.0	15486350.0	2255529.06	29907.94	6.33	0.17
14	2022-09-07	1807800.0	1452755.0	230027.23	125017.77	19579587.0	16939105.0	2485556.29	154925.71	6.92	0.79
15	2022-09-08	2099508.0	1669410.0	268110.26	161987.74	21679095.0	18608515.0	2753666.55	316913.45	7.72	1.46

Збережемо результат запиту у змінну results_7:

%%sql
results_7 << SELECT date,
       revenue,
       costs,
       tax,
       gross_profit,
       total_revenue,
       total_costs,
       total_tax,
       total_gross_profit,
       round(gross_profit / revenue * 100, 2) as gross_profit_ratio,
       round(total_gross_profit / total_revenue * 100, 2) as total_gross_profit_ratio
FROM   (SELECT date,
               revenue,
               costs,
               tax,
               revenue - costs - tax as gross_profit,
               sum(revenue) OVER (ORDER BY date) as total_revenue,
               sum(costs) OVER (ORDER BY date) as total_costs,
               sum(tax) OVER (ORDER BY date) as total_tax,
               sum(revenue - costs - tax) OVER (ORDER BY date) as total_gross_profit
        FROM   (SELECT date,
                       orders_packed,
                       orders_delivered,
                       couriers_count,
                       revenue,
                       case when date_part('month', date) = 8 then 120000.0 + 140 * coalesce(orders_packed, 0) + 150 * coalesce(orders_delivered, 0) + 400 * coalesce(couriers_count, 0)
                            when date_part('month', date) = 9 then 150000.0 + 115 * coalesce(orders_packed, 0) + 150 * coalesce(orders_delivered, 0) + 500 * coalesce(couriers_count, 0) end as costs,
                       tax
                FROM   (SELECT creation_time::date as date,
                               count(distinct order_id) as orders_packed,
                               sum(price) as revenue,
                               sum(tax) as tax
                        FROM   (SELECT order_id,
                                       creation_time,
                                       product_id,
                                       name,
                                       price,
                                       case when name in ('sugar', 'crackers', 'drying', 'seeds',
                                                          'linseed oil', 'grapes', 'olive oil',
                                                          'watermelon', 'baton', 'yogurt', 'cream', 'buckwheat',
                                                          'oatmeal', 'pasta', 'mutton', 'oranges',
                                                          'bagels', 'bread', 'peas', 'sour cream', 'smoked fish',
                                                          'flour', 'sprats', 'sausages', 'pork', 'rice',
                                                          'sesame oil', 'condensed milk', 'pineapple', 'beef',
                                                          'salt', 'dried fish', 'sunflower oil', 'apples',
                                                          'pears', 'flatbread', 'milk', 'chicken', 'lavash', 'waffles', 'tangerines') then round(price/110*10,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         2)
                                            else round(price/120*20, 2) end as tax
                                FROM   (SELECT order_id,
                                               creation_time,
                                               unnest(product_ids) as product_id
                                        FROM   orders
                                        WHERE  order_id not in (SELECT order_id
                                                                FROM   user_actions
                                                                WHERE  action = 'cancel_order')) t1
                                    LEFT JOIN products using (product_id)) t2
                        GROUP BY date) t3
                    LEFT JOIN (SELECT time::date as date,
                                      count(distinct order_id) as orders_delivered
                               FROM   courier_actions
                               WHERE  order_id not in (SELECT order_id
                                                       FROM   user_actions
                                                       WHERE  action = 'cancel_order')
                                  and action = 'deliver_order'
                               GROUP BY date) t4 using (date)
                    LEFT JOIN (SELECT date,
                                      count(courier_id) as couriers_count
                               FROM   (SELECT time::date as date,
                                              courier_id,
                                              count(distinct order_id) as orders_delivered
                                       FROM   courier_actions
                                       WHERE  order_id not in (SELECT order_id
                                                               FROM   user_actions
                                                               WHERE  action = 'cancel_order')
                                          and action = 'deliver_order'
                                       GROUP BY date, courier_id having count(distinct order_id) >= 5) t5
                               GROUP BY date) t6 using (date)) t7) t8

Візуалізуємо отримані дані:

from plotly.subplots import make_subplots

fig = make_subplots(specs=[[{"secondary_y": True}]])
fig.add_trace(
    go.Bar(
        name="gross_profit_ratio",
        x=results_7.date,
        y=results_7.gross_profit_ratio,
        offsetgroup=1,
    ),
    secondary_y=False,
)
fig.add_trace(
    go.Scatter(
        name="gross_profit", x=results_7.date, y=results_7.gross_profit, offsetgroup=2
    ),
    secondary_y=True,
)
fig.update_yaxes(range=[-150000, 150000], secondary_y=True)
fig.update_yaxes(range=[-250, 250], secondary_y=False)
fig.update_layout(
    legend=dict(orientation="h", yanchor="bottom", y=1.02, xanchor="right", x=1),
)

fig.show()

fig = make_subplots(specs=[[{"secondary_y": True}]])
fig.add_trace(
    go.Bar(
        name="total_gross_profit_ratio",
        x=results_7.date,
        y=results_7.total_gross_profit_ratio,
        offsetgroup=1,
    ),
    secondary_y=False,
)
fig.add_trace(
    go.Scatter(
        name="total_gross_profit",
        x=results_7.date,
        y=results_7.total_gross_profit,
        offsetgroup=2,
    ),
    secondary_y=True,
)
fig.update_yaxes(range=[-1200000, 400000], secondary_y=True)
fig.update_yaxes(range=[-300, 100], secondary_y=False)
fig.update_layout(
    legend=dict(orientation="h", yanchor="bottom", y=1.02, xanchor="right", x=1),
)

fig.show()

(a) Динаміка валового прибутку та частки валового прибутку у виручці на поточний день

(b) Динаміка сумарного виторгу та частки сумарного валового прибутку в сумарному виторгу на поточний день.

Рисунок 22.7: Графік за результатами SQL-запиту

Проаналізуйте побудовані графіки та спробуйте відповісти на такі питання:

1. Починаючи з якого дня щоденний валовий прибуток нашого сервісу став позитивним?
2. У який день сумарний валовий прибуток перевищив нульову позначку та сервіс уперше «вийшов у плюс» за цим показником?
3. Чи можна сказати, що оптимізація вартості складання замовлення у вересні дозволила побачити цього місяця позитивний валовий прибуток?

22.6 Customer Acquisition Cost (CAC)

Познайомимося з метриками, які допомагають оцінити ефективність нашого маркетингу — наскільки ефективно ми залучаємо користувачів до нашого додатку.

Спочатку розглянемо метрику CAC (Customer Acquisition Cost), яка відображає витрати на залучення одного покупця.

Уявімо ситуацію: до нас звернулися маркетологи з проханням порівняти дві рекламні кампанії.

У рекламній кампанії №1 про наш додаток розповів відомий блогер на Youtube-каналі про кулінарії. На цю інтеграцію сумарно витратили 250 000 грошових одиниць. Внаслідок цієї кампанії 1 вересня у додатку зареєструвалася 171 особа.

У рамках рекламної кампанії №2 користувачам показували таргетовану рекламу у соціальних мережах. На це теж сумарно витратили 250 000 грошових одиниць, і в результаті 1 вересня у нас з’явилося 236 нових користувачів.

Як нам оцінити, який із каналів залучення спрацював краще? На перший погляд, друга кампанія показала себе краще, оскільки нам удалося залучити більше людей за ті самі гроші. Але не поспішатимемо з висновками — давайте спочатку проведемо докладніший аналіз і розрахуємо CAC для двох рекламних кампаній.

Завдання 22.8
На основі таблиці user_actions розрахуйте метрику CAC для двох рекламних кампаній.

Список id користувачів, що прийшли після рекламної кампанії №1:

8631, 8632, 8638, 8643, 8657, 8673, 8706, 8707, 8715, 8723, 8732, 8739, 8741, 
8750, 8751, 8752, 8770, 8774, 8788, 8791, 8804, 8810, 8815, 8828, 8830, 8845, 
8853, 8859, 8867, 8869, 8876, 8879, 8883, 8896, 8909, 8911, 8933, 8940, 8972, 
8976, 8988, 8990, 9002, 9004, 9009, 9019, 9020, 9035, 9036, 9061, 9069, 9071, 
9075, 9081, 9085, 9089, 9108, 9113, 9144, 9145, 9146, 9162, 9165, 9167, 9175, 
9180, 9182, 9197, 9198, 9210, 9223, 9251, 9257, 9278, 9287, 9291, 9313, 9317, 
9321, 9334, 9351, 9391, 9398, 9414, 9420, 9422, 9431, 9450, 9451, 9454, 9472, 
9476, 9478, 9491, 9494, 9505, 9512, 9518, 9524, 9526, 9528, 9531, 9535, 9550, 
9559, 9561, 9562, 9599, 9603, 9605, 9611, 9612, 9615, 9625, 9633, 9652, 9654, 
9655, 9660, 9662, 9667, 9677, 9679, 9689, 9695, 9720, 9726, 9739, 9740, 9762, 
9778, 9786, 9794, 9804, 9810, 9813, 9818, 9828, 9831, 9836, 9838, 9845, 9871, 
9887, 9891, 9896, 9897, 9916, 9945, 9960, 9963, 9965, 9968, 9971, 9993, 9998, 
9999, 10001, 10013, 10016, 10023, 10030, 10051, 10057, 10064, 10082, 10103, 
10105, 10122, 10134, 10135

Список id користувачів, що прийшли після рекламної кампанії №2:

8629, 8630, 8644, 8646, 8650, 8655, 8659, 8660, 8663, 8665, 8670, 8675, 8680, 8681, 
8682, 8683, 8694, 8697, 8700, 8704, 8712, 8713, 8719, 8729, 8733, 8742, 8748, 8754, 
8771, 8794, 8795, 8798, 8803, 8805, 8806, 8812, 8814, 8825, 8827, 8838, 8849, 8851, 
8854, 8855, 8870, 8878, 8882, 8886, 8890, 8893, 8900, 8902, 8913, 8916, 8923, 8929, 
8935, 8942, 8943, 8949, 8953, 8955, 8966, 8968, 8971, 8973, 8980, 8995, 8999, 9000, 
9007, 9013, 9041, 9042, 9047, 9064, 9068, 9077, 9082, 9083, 9095, 9103, 9109, 9117, 
9123, 9127, 9131, 9137, 9140, 9149, 9161, 9179, 9181, 9183, 9185, 9190, 9196, 9203, 
9207, 9226, 9227, 9229, 9230, 9231, 9250, 9255, 9259, 9267, 9273, 9281, 9282, 9289, 
9292, 9303, 9310, 9312, 9315, 9327, 9333, 9335, 9337, 9343, 9356, 9368, 9370, 9383, 
9392, 9404, 9410, 9421, 9428, 9432, 9437, 9468, 9479, 9483, 9485, 9492, 9495, 9497, 
9498, 9500, 9510, 9527, 9529, 9530, 9538, 9539, 9545, 9557, 9558, 9560, 9564, 9567, 
9570, 9591, 9596, 9598, 9616, 9631, 9634, 9635, 9636, 9658, 9666, 9672, 9684, 9692, 
9700, 9704, 9706, 9711, 9719, 9727, 9735, 9741, 9744, 9749, 9752, 9753, 9755, 9757, 
9764, 9783, 9784, 9788, 9790, 9808, 9820, 9839, 9841, 9843, 9853, 9855, 9859, 9863, 
9877, 9879, 9880, 9882, 9883, 9885, 9901, 9904, 9908, 9910, 9912, 9920, 9929, 9930, 
9935, 9939, 9958, 9959, 9961, 9983, 10027, 10033, 10038, 10045, 10047, 10048, 10058, 
10059, 10067, 10069, 10073, 10075, 10078, 10079, 10081, 10092, 10106, 10110, 10113, 10131

Назвіть колонку з найменуваннями кампаній ads_campaign, а колонку зі значенням метрики — cac.

Назви кампаній виведіть у такому вигляді:

Company № 1
Company № 2

Отримані значення метрики заокругліть до двох знаків після коми.

Результат має бути відсортований за зменшенням значення метрики.

Поля у результуючій таблиці: ads_campaign, cac

Підказка на випадок, якщо зовсім не виходить

Для вирішення завдання спочатку необхідно відібрати користувачів, що прийшли після проведення рекламних кампаній, а потім серед них відібрати тих, хто став покупцем, тобто зробив хоча б одне замовлення. Далі кожної групи за нескладною формулою можна розрахувати метрику CAC.

%%sql
SELECT concat('Company № ', ads_campaign) as ads_campaign,
       round(250000.0 / count(distinct user_id), 2) as cac
FROM   (SELECT user_id,
               order_id,
               action,
               case when user_id in (8631, 8632, 8638, 8643, 8657, 8673, 8706, 8707, 8715, 8723, 8732,
                                     8739, 8741, 8750, 8751, 8752, 8770, 8774, 8788, 8791,
                                     8804, 8810, 8815, 8828, 8830, 8845, 8853, 8859, 8867,
                                     8869, 8876, 8879, 8883, 8896, 8909, 8911, 8933, 8940,
                                     8972, 8976, 8988, 8990, 9002, 9004, 9009, 9019, 9020,
                                     9035, 9036, 9061, 9069, 9071, 9075, 9081, 9085, 9089,
                                     9108, 9113, 9144, 9145, 9146, 9162, 9165, 9167, 9175,
                                     9180, 9182, 9197, 9198, 9210, 9223, 9251, 9257, 9278,
                                     9287, 9291, 9313, 9317, 9321, 9334, 9351, 9391, 9398,
                                     9414, 9420, 9422, 9431, 9450, 9451, 9454, 9472, 9476,
                                     9478, 9491, 9494, 9505, 9512, 9518, 9524, 9526, 9528,
                                     9531, 9535, 9550, 9559, 9561, 9562, 9599, 9603, 9605,
                                     9611, 9612, 9615, 9625, 9633, 9652, 9654, 9655, 9660,
                                     9662, 9667, 9677, 9679, 9689, 9695, 9720, 9726, 9739,
                                     9740, 9762, 9778, 9786, 9794, 9804, 9810, 9813, 9818,
                                     9828, 9831, 9836, 9838, 9845, 9871, 9887, 9891, 9896,
                                     9897, 9916, 9945, 9960, 9963, 9965, 9968, 9971, 9993,
                                     9998, 9999, 10001, 10013, 10016, 10023, 10030, 10051,
                                     10057, 10064, 10082, 10103, 10105, 10122, 10134, 10135) then 1
                    when user_id in (8629, 8630, 8644, 8646, 8650, 8655, 8659, 8660, 8663, 8665, 8670,
                                     8675, 8680, 8681, 8682, 8683, 8694, 8697, 8700, 8704,
                                     8712, 8713, 8719, 8729, 8733, 8742, 8748, 8754, 8771,
                                     8794, 8795, 8798, 8803, 8805, 8806, 8812, 8814, 8825,
                                     8827, 8838, 8849, 8851, 8854, 8855, 8870, 8878, 8882,
                                     8886, 8890, 8893, 8900, 8902, 8913, 8916, 8923, 8929,
                                     8935, 8942, 8943, 8949, 8953, 8955, 8966, 8968, 8971,
                                     8973, 8980, 8995, 8999, 9000, 9007, 9013, 9041, 9042,
                                     9047, 9064, 9068, 9077, 9082, 9083, 9095, 9103, 9109,
                                     9117, 9123, 9127, 9131, 9137, 9140, 9149, 9161, 9179,
                                     9181, 9183, 9185, 9190, 9196, 9203, 9207, 9226, 9227,
                                     9229, 9230, 9231, 9250, 9255, 9259, 9267, 9273, 9281,
                                     9282, 9289, 9292, 9303, 9310, 9312, 9315, 9327, 9333,
                                     9335, 9337, 9343, 9356, 9368, 9370, 9383, 9392, 9404,
                                     9410, 9421, 9428, 9432, 9437, 9468, 9479, 9483, 9485,
                                     9492, 9495, 9497, 9498, 9500, 9510, 9527, 9529, 9530,
                                     9538, 9539, 9545, 9557, 9558, 9560, 9564, 9567, 9570,
                                     9591, 9596, 9598, 9616, 9631, 9634, 9635, 9636, 9658,
                                     9666, 9672, 9684, 9692, 9700, 9704, 9706, 9711, 9719,
                                     9727, 9735, 9741, 9744, 9749, 9752, 9753, 9755, 9757,
                                     9764, 9783, 9784, 9788, 9790, 9808, 9820, 9839, 9841,
                                     9843, 9853, 9855, 9859, 9863, 9877, 9879, 9880, 9882,
                                     9883, 9885, 9901, 9904, 9908, 9910, 9912, 9920, 9929,
                                     9930, 9935, 9939, 9958, 9959, 9961, 9983, 10027, 10033,
                                     10038, 10045, 10047, 10048, 10058, 10059, 10067, 10069,
                                     10073, 10075, 10078, 10079, 10081, 10092, 10106, 10110,
                                     10113, 10131) then 2
                    else 0 end as ads_campaign,
               count(action) filter (WHERE action = 'cancel_order') OVER (PARTITION BY order_id) as is_canceled
        FROM   user_actions) t1
WHERE  ads_campaign in (1, 2)
   and is_canceled = 0
GROUP BY ads_campaign
ORDER BY cac desc

	ads_campaign	cac
0	Company № 1	1461.99
1	Company № 2	1068.38

Збережемо результат запиту у змінну results_8:

%%sql
results_8 << SELECT concat('Company № ', ads_campaign) as ads_campaign,
       round(250000.0 / count(distinct user_id), 2) as cac
FROM   (SELECT user_id,
               order_id,
               action,
               case when user_id in (8631, 8632, 8638, 8643, 8657, 8673, 8706, 8707, 8715, 8723, 8732,
                                     8739, 8741, 8750, 8751, 8752, 8770, 8774, 8788, 8791,
                                     8804, 8810, 8815, 8828, 8830, 8845, 8853, 8859, 8867,
                                     8869, 8876, 8879, 8883, 8896, 8909, 8911, 8933, 8940,
                                     8972, 8976, 8988, 8990, 9002, 9004, 9009, 9019, 9020,
                                     9035, 9036, 9061, 9069, 9071, 9075, 9081, 9085, 9089,
                                     9108, 9113, 9144, 9145, 9146, 9162, 9165, 9167, 9175,
                                     9180, 9182, 9197, 9198, 9210, 9223, 9251, 9257, 9278,
                                     9287, 9291, 9313, 9317, 9321, 9334, 9351, 9391, 9398,
                                     9414, 9420, 9422, 9431, 9450, 9451, 9454, 9472, 9476,
                                     9478, 9491, 9494, 9505, 9512, 9518, 9524, 9526, 9528,
                                     9531, 9535, 9550, 9559, 9561, 9562, 9599, 9603, 9605,
                                     9611, 9612, 9615, 9625, 9633, 9652, 9654, 9655, 9660,
                                     9662, 9667, 9677, 9679, 9689, 9695, 9720, 9726, 9739,
                                     9740, 9762, 9778, 9786, 9794, 9804, 9810, 9813, 9818,
                                     9828, 9831, 9836, 9838, 9845, 9871, 9887, 9891, 9896,
                                     9897, 9916, 9945, 9960, 9963, 9965, 9968, 9971, 9993,
                                     9998, 9999, 10001, 10013, 10016, 10023, 10030, 10051,
                                     10057, 10064, 10082, 10103, 10105, 10122, 10134, 10135) then 1
                    when user_id in (8629, 8630, 8644, 8646, 8650, 8655, 8659, 8660, 8663, 8665, 8670,
                                     8675, 8680, 8681, 8682, 8683, 8694, 8697, 8700, 8704,
                                     8712, 8713, 8719, 8729, 8733, 8742, 8748, 8754, 8771,
                                     8794, 8795, 8798, 8803, 8805, 8806, 8812, 8814, 8825,
                                     8827, 8838, 8849, 8851, 8854, 8855, 8870, 8878, 8882,
                                     8886, 8890, 8893, 8900, 8902, 8913, 8916, 8923, 8929,
                                     8935, 8942, 8943, 8949, 8953, 8955, 8966, 8968, 8971,
                                     8973, 8980, 8995, 8999, 9000, 9007, 9013, 9041, 9042,
                                     9047, 9064, 9068, 9077, 9082, 9083, 9095, 9103, 9109,
                                     9117, 9123, 9127, 9131, 9137, 9140, 9149, 9161, 9179,
                                     9181, 9183, 9185, 9190, 9196, 9203, 9207, 9226, 9227,
                                     9229, 9230, 9231, 9250, 9255, 9259, 9267, 9273, 9281,
                                     9282, 9289, 9292, 9303, 9310, 9312, 9315, 9327, 9333,
                                     9335, 9337, 9343, 9356, 9368, 9370, 9383, 9392, 9404,
                                     9410, 9421, 9428, 9432, 9437, 9468, 9479, 9483, 9485,
                                     9492, 9495, 9497, 9498, 9500, 9510, 9527, 9529, 9530,
                                     9538, 9539, 9545, 9557, 9558, 9560, 9564, 9567, 9570,
                                     9591, 9596, 9598, 9616, 9631, 9634, 9635, 9636, 9658,
                                     9666, 9672, 9684, 9692, 9700, 9704, 9706, 9711, 9719,
                                     9727, 9735, 9741, 9744, 9749, 9752, 9753, 9755, 9757,
                                     9764, 9783, 9784, 9788, 9790, 9808, 9820, 9839, 9841,
                                     9843, 9853, 9855, 9859, 9863, 9877, 9879, 9880, 9882,
                                     9883, 9885, 9901, 9904, 9908, 9910, 9912, 9920, 9929,
                                     9930, 9935, 9939, 9958, 9959, 9961, 9983, 10027, 10033,
                                     10038, 10045, 10047, 10048, 10058, 10059, 10067, 10069,
                                     10073, 10075, 10078, 10079, 10081, 10092, 10106, 10110,
                                     10113, 10131) then 2
                    else 0 end as ads_campaign,
               count(action) filter (WHERE action = 'cancel_order') OVER (PARTITION BY order_id) as is_canceled
        FROM   user_actions) t1
WHERE  ads_campaign in (1, 2)
   and is_canceled = 0
GROUP BY ads_campaign
ORDER BY cac desc

Візуалізуємо отримані дані:

fig = px.bar(results_8, x="ads_campaign", y="cac", text_auto=True)
fig.show()

Рисунок 22.8: Customer Acquisition Cost (CAC) за кампаніями

У якої рекламної кампанії витрати на залучення одного покупця виявилися нижчими?

22.7 Return on Investment (ROI)

Відмінно, CAC ми розрахували, але чи можемо ми тепер стверджувати, що друга рекламна кампанія краща лише тому, що дозволяє залучати активних користувачів за менші гроші?

Ні, робити такі висновки рано — давайте оцінимо ще один показник, який відображає рентабельність інвестицій і показує, наскільки вигідним є той чи інший проект чи продукт. Ця метрика називається ROI (Return on Investment), у маркетингу її часто застосовують для підрахунку окупності рекламних кампаній.

Наведемо приклад. Допустимо, ми вклали в рекламу 100 грошових одиниць і в результаті продали товарів на 220 грошових одиниць. Тоді ROI буде розрахований так:

\[ROI=\frac{(220−100)}{100}∗100\%=120\%\]

Таким чином, на кожну вкладену одиницю грошей у рекламу ми отримали 1,2 грошових одиниць прибутку.

Порівняємо дві рекламні кампанії за значеннями метрики ROI і зробимо висновок про те, який з рекламних каналів більшою мірою окупає витрати на залучення нових користувачів.

Завдання 22.9
Розрахуйте ROI для кожного рекламного каналу.

Назвіть колонку з найменуваннями кампаній ads_campaign, а колонку зі значенням метрики — roi.

Назви кампаній виведіть у такому вигляді:

Company № 1
Company № 2

Отримані значення метрики необхідно виразити у відсотках та округлити до двох знаків після коми.

Результат має бути відсортований за зменшенням значення метрики.

Поля у результуючій таблиці: ads_campaign, roi

Пояснення:

Списки користувачів, що зареєструвалися, ті ж, що і на попередньому кроці.

Підказка на випадок, якщо зовсім не виходить

Візьміть усіх користувачів, які залучені через рекламний канал. Потім порахуйте сумарну вартість усіх нескасованих замовлень цих користувачів за весь час. Далі відніміть від отриманої суми рекламні витрати та розділіть отриману різницю на рекламні витрати.

%%sql
SELECT concat('Company № ', ads_campaign) as ads_campaign,
       round((sum(price) - 250000.0) / 250000.0 * 100, 2) as roi
FROM   (SELECT ads_campaign,
               user_id,
               order_id,
               product_id,
               price
        FROM   (SELECT ads_campaign,
                       user_id,
                       order_id
                FROM   (SELECT user_id,
                               order_id,
                               case when user_id in (8631, 8632, 8638, 8643, 8657, 8673, 8706, 8707, 8715, 8723, 8732,
                                                     8739, 8741, 8750, 8751, 8752, 8770, 8774, 8788, 8791,
                                                     8804, 8810, 8815, 8828, 8830, 8845, 8853, 8859, 8867,
                                                     8869, 8876, 8879, 8883, 8896, 8909, 8911, 8933, 8940,
                                                     8972, 8976, 8988, 8990, 9002, 9004, 9009, 9019, 9020,
                                                     9035, 9036, 9061, 9069, 9071, 9075, 9081, 9085, 9089,
                                                     9108, 9113, 9144, 9145, 9146, 9162, 9165, 9167, 9175,
                                                     9180, 9182, 9197, 9198, 9210, 9223, 9251, 9257, 9278,
                                                     9287, 9291, 9313, 9317, 9321, 9334, 9351, 9391, 9398,
                                                     9414, 9420, 9422, 9431, 9450, 9451, 9454, 9472, 9476,
                                                     9478, 9491, 9494, 9505, 9512, 9518, 9524, 9526, 9528,
                                                     9531, 9535, 9550, 9559, 9561, 9562, 9599, 9603, 9605,
                                                     9611, 9612, 9615, 9625, 9633, 9652, 9654, 9655, 9660,
                                                     9662, 9667, 9677, 9679, 9689, 9695, 9720, 9726, 9739,
                                                     9740, 9762, 9778, 9786, 9794, 9804, 9810, 9813, 9818,
                                                     9828, 9831, 9836, 9838, 9845, 9871, 9887, 9891, 9896,
                                                     9897, 9916, 9945, 9960, 9963, 9965, 9968, 9971, 9993,
                                                     9998, 9999, 10001, 10013, 10016, 10023, 10030, 10051,
                                                     10057, 10064, 10082, 10103, 10105, 10122, 10134, 10135) then 1
                                    when user_id in (8629, 8630, 8644, 8646, 8650, 8655, 8659, 8660, 8663, 8665, 8670,
                                                     8675, 8680, 8681, 8682, 8683, 8694, 8697, 8700, 8704,
                                                     8712, 8713, 8719, 8729, 8733, 8742, 8748, 8754, 8771,
                                                     8794, 8795, 8798, 8803, 8805, 8806, 8812, 8814, 8825,
                                                     8827, 8838, 8849, 8851, 8854, 8855, 8870, 8878, 8882,
                                                     8886, 8890, 8893, 8900, 8902, 8913, 8916, 8923, 8929,
                                                     8935, 8942, 8943, 8949, 8953, 8955, 8966, 8968, 8971,
                                                     8973, 8980, 8995, 8999, 9000, 9007, 9013, 9041, 9042,
                                                     9047, 9064, 9068, 9077, 9082, 9083, 9095, 9103, 9109,
                                                     9117, 9123, 9127, 9131, 9137, 9140, 9149, 9161, 9179,
                                                     9181, 9183, 9185, 9190, 9196, 9203, 9207, 9226, 9227,
                                                     9229, 9230, 9231, 9250, 9255, 9259, 9267, 9273, 9281,
                                                     9282, 9289, 9292, 9303, 9310, 9312, 9315, 9327, 9333,
                                                     9335, 9337, 9343, 9356, 9368, 9370, 9383, 9392, 9404,
                                                     9410, 9421, 9428, 9432, 9437, 9468, 9479, 9483, 9485,
                                                     9492, 9495, 9497, 9498, 9500, 9510, 9527, 9529, 9530,
                                                     9538, 9539, 9545, 9557, 9558, 9560, 9564, 9567, 9570,
                                                     9591, 9596, 9598, 9616, 9631, 9634, 9635, 9636, 9658,
                                                     9666, 9672, 9684, 9692, 9700, 9704, 9706, 9711, 9719,
                                                     9727, 9735, 9741, 9744, 9749, 9752, 9753, 9755, 9757,
                                                     9764, 9783, 9784, 9788, 9790, 9808, 9820, 9839, 9841,
                                                     9843, 9853, 9855, 9859, 9863, 9877, 9879, 9880, 9882,
                                                     9883, 9885, 9901, 9904, 9908, 9910, 9912, 9920, 9929,
                                                     9930, 9935, 9939, 9958, 9959, 9961, 9983, 10027, 10033,
                                                     10038, 10045, 10047, 10048, 10058, 10059, 10067, 10069,
                                                     10073, 10075, 10078, 10079, 10081, 10092, 10106, 10110,
                                                     10113, 10131) then 2
                                    else 0 end as ads_campaign,
                               count(action) filter (WHERE action = 'cancel_order') OVER (PARTITION BY order_id) as is_canceled
                        FROM   user_actions) t1
                WHERE  ads_campaign in (1, 2)
                   and is_canceled = 0) t2
            LEFT JOIN (SELECT order_id,
                              unnest(product_ids) as product_id
                       FROM   orders) t3 using(order_id)
            LEFT JOIN products using(product_id)) t4
GROUP BY ads_campaign
ORDER BY roi desc

	ads_campaign	roi
0	Company № 1	14.50
1	Company № 2	-1.61

Збережемо результат запиту у змінну results_9:

%%sql
results_9 << SELECT concat('Company № ', ads_campaign) as ads_campaign,
       round((sum(price) - 250000.0) / 250000.0 * 100, 2) as roi
FROM   (SELECT ads_campaign,
               user_id,
               order_id,
               product_id,
               price
        FROM   (SELECT ads_campaign,
                       user_id,
                       order_id
                FROM   (SELECT user_id,
                               order_id,
                               case when user_id in (8631, 8632, 8638, 8643, 8657, 8673, 8706, 8707, 8715, 8723, 8732,
                                                     8739, 8741, 8750, 8751, 8752, 8770, 8774, 8788, 8791,
                                                     8804, 8810, 8815, 8828, 8830, 8845, 8853, 8859, 8867,
                                                     8869, 8876, 8879, 8883, 8896, 8909, 8911, 8933, 8940,
                                                     8972, 8976, 8988, 8990, 9002, 9004, 9009, 9019, 9020,
                                                     9035, 9036, 9061, 9069, 9071, 9075, 9081, 9085, 9089,
                                                     9108, 9113, 9144, 9145, 9146, 9162, 9165, 9167, 9175,
                                                     9180, 9182, 9197, 9198, 9210, 9223, 9251, 9257, 9278,
                                                     9287, 9291, 9313, 9317, 9321, 9334, 9351, 9391, 9398,
                                                     9414, 9420, 9422, 9431, 9450, 9451, 9454, 9472, 9476,
                                                     9478, 9491, 9494, 9505, 9512, 9518, 9524, 9526, 9528,
                                                     9531, 9535, 9550, 9559, 9561, 9562, 9599, 9603, 9605,
                                                     9611, 9612, 9615, 9625, 9633, 9652, 9654, 9655, 9660,
                                                     9662, 9667, 9677, 9679, 9689, 9695, 9720, 9726, 9739,
                                                     9740, 9762, 9778, 9786, 9794, 9804, 9810, 9813, 9818,
                                                     9828, 9831, 9836, 9838, 9845, 9871, 9887, 9891, 9896,
                                                     9897, 9916, 9945, 9960, 9963, 9965, 9968, 9971, 9993,
                                                     9998, 9999, 10001, 10013, 10016, 10023, 10030, 10051,
                                                     10057, 10064, 10082, 10103, 10105, 10122, 10134, 10135) then 1
                                    when user_id in (8629, 8630, 8644, 8646, 8650, 8655, 8659, 8660, 8663, 8665, 8670,
                                                     8675, 8680, 8681, 8682, 8683, 8694, 8697, 8700, 8704,
                                                     8712, 8713, 8719, 8729, 8733, 8742, 8748, 8754, 8771,
                                                     8794, 8795, 8798, 8803, 8805, 8806, 8812, 8814, 8825,
                                                     8827, 8838, 8849, 8851, 8854, 8855, 8870, 8878, 8882,
                                                     8886, 8890, 8893, 8900, 8902, 8913, 8916, 8923, 8929,
                                                     8935, 8942, 8943, 8949, 8953, 8955, 8966, 8968, 8971,
                                                     8973, 8980, 8995, 8999, 9000, 9007, 9013, 9041, 9042,
                                                     9047, 9064, 9068, 9077, 9082, 9083, 9095, 9103, 9109,
                                                     9117, 9123, 9127, 9131, 9137, 9140, 9149, 9161, 9179,
                                                     9181, 9183, 9185, 9190, 9196, 9203, 9207, 9226, 9227,
                                                     9229, 9230, 9231, 9250, 9255, 9259, 9267, 9273, 9281,
                                                     9282, 9289, 9292, 9303, 9310, 9312, 9315, 9327, 9333,
                                                     9335, 9337, 9343, 9356, 9368, 9370, 9383, 9392, 9404,
                                                     9410, 9421, 9428, 9432, 9437, 9468, 9479, 9483, 9485,
                                                     9492, 9495, 9497, 9498, 9500, 9510, 9527, 9529, 9530,
                                                     9538, 9539, 9545, 9557, 9558, 9560, 9564, 9567, 9570,
                                                     9591, 9596, 9598, 9616, 9631, 9634, 9635, 9636, 9658,
                                                     9666, 9672, 9684, 9692, 9700, 9704, 9706, 9711, 9719,
                                                     9727, 9735, 9741, 9744, 9749, 9752, 9753, 9755, 9757,
                                                     9764, 9783, 9784, 9788, 9790, 9808, 9820, 9839, 9841,
                                                     9843, 9853, 9855, 9859, 9863, 9877, 9879, 9880, 9882,
                                                     9883, 9885, 9901, 9904, 9908, 9910, 9912, 9920, 9929,
                                                     9930, 9935, 9939, 9958, 9959, 9961, 9983, 10027, 10033,
                                                     10038, 10045, 10047, 10048, 10058, 10059, 10067, 10069,
                                                     10073, 10075, 10078, 10079, 10081, 10092, 10106, 10110,
                                                     10113, 10131) then 2
                                    else 0 end as ads_campaign,
                               count(action) filter (WHERE action = 'cancel_order') OVER (PARTITION BY order_id) as is_canceled
                        FROM   user_actions) t1
                WHERE  ads_campaign in (1, 2)
                   and is_canceled = 0) t2
            LEFT JOIN (SELECT order_id,
                              unnest(product_ids) as product_id
                       FROM   orders) t3 using(order_id)
            LEFT JOIN products using(product_id)) t4
GROUP BY ads_campaign
ORDER BY roi desc

Візуалізуємо отримані дані:

fig = px.bar(results_9, x="ads_campaign", y="roi", text_auto=True)
fig.show()

Рисунок 22.9: Return on Investment (ROI) за кампаніями

Який висновок щодо ефективності рекламних кампаній можна зробити? У який канал залучення є сенс вкладати більше бюджету?

22.8 Середній чек

Отже, ми з’ясували, що перша рекламна кампанія дозволяє залучати якісніших лідів. Але причина цих відмінностей поки що не зрозуміла. Чому користувачі першого рекламного каналу приносять нам більше грошей? Можливо, у них вищий середній чек?

Давайте проведемо більш детальний аналіз, щоб з’ясувати, чим відрізняються два рекламні канали з точки зору метрик користувача.

Завдання 22.10
Для кожної рекламної кампанії порахуйте середню вартість замовлення залучених користувачів за перший тиждень використання програми з 1 по 7 вересня 2022 року.

Назвіть колонку з найменуваннями кампаній ads_campaign, а колонку зі значенням метрики — avg_check.

Назви кампаній виведіть у такому вигляді:

Company № 1
Company № 2

Отримані значення метрики необхідно заокруглити до двох знаків після коми.

Результат має бути відсортований за зменшенням значення метрики.

Поля у результуючій таблиці: ads_campaign, avg_check

Пояснення:

Покупцями будемо вважати тих користувачів, які зробили хоча б одне замовлення, яке надалі не було скасовано. Наприклад, якщо людина зробила лише одне замовлення, а потім скасувала його, то покупцем ми його не рахуємо.

Списки користувачів, що зареєструвалися, ті ж, що і на попередніх кроках.

Підказка на випадок, якщо зовсім не виходить

Візьміть усіх користувачів, залучених через кожний рекламний канал. Потім для кожного користувача порахуйте середню вартість усіх замовлень. Потім ще раз усередніть отримані значення рекламних каналів.

%%sql
SELECT concat('Company № ', ads_campaign) as ads_campaign,
       round(avg(user_avg_check), 2) as avg_check
FROM   (SELECT ads_campaign,
               user_id,
               round(avg(order_price), 2) as user_avg_check
        FROM   (SELECT ads_campaign,
                       user_id,
                       order_id,
                       sum(price) as order_price
                FROM   (SELECT ads_campaign,
                               user_id,
                               order_id,
                               product_id,
                               price
                        FROM   (SELECT ads_campaign,
                                       user_id,
                                       order_id
                                FROM   (SELECT user_id,
                                               order_id,
                                               time,
                                               case when user_id in (8631, 8632, 8638, 8643, 8657, 8673, 8706, 8707, 8715, 8723, 8732,
                                                                     8739, 8741, 8750, 8751, 8752, 8770, 8774, 8788, 8791,
                                                                     8804, 8810, 8815, 8828, 8830, 8845, 8853, 8859, 8867,
                                                                     8869, 8876, 8879, 8883, 8896, 8909, 8911, 8933, 8940,
                                                                     8972, 8976, 8988, 8990, 9002, 9004, 9009, 9019, 9020,
                                                                     9035, 9036, 9061, 9069, 9071, 9075, 9081, 9085, 9089,
                                                                     9108, 9113, 9144, 9145, 9146, 9162, 9165, 9167, 9175,
                                                                     9180, 9182, 9197, 9198, 9210, 9223, 9251, 9257, 9278,
                                                                     9287, 9291, 9313, 9317, 9321, 9334, 9351, 9391, 9398,
                                                                     9414, 9420, 9422, 9431, 9450, 9451, 9454, 9472, 9476,
                                                                     9478, 9491, 9494, 9505, 9512, 9518, 9524, 9526, 9528,
                                                                     9531, 9535, 9550, 9559, 9561, 9562, 9599, 9603, 9605,
                                                                     9611, 9612, 9615, 9625, 9633, 9652, 9654, 9655, 9660,
                                                                     9662, 9667, 9677, 9679, 9689, 9695, 9720, 9726, 9739,
                                                                     9740, 9762, 9778, 9786, 9794, 9804, 9810, 9813, 9818,
                                                                     9828, 9831, 9836, 9838, 9845, 9871, 9887, 9891, 9896,
                                                                     9897, 9916, 9945, 9960, 9963, 9965, 9968, 9971, 9993,
                                                                     9998, 9999, 10001, 10013, 10016, 10023, 10030, 10051,
                                                                     10057, 10064, 10082, 10103, 10105, 10122, 10134, 10135) then 1
                                                    when user_id in (8629, 8630, 8644, 8646, 8650, 8655, 8659, 8660, 8663, 8665, 8670,
                                                                     8675, 8680, 8681, 8682, 8683, 8694, 8697, 8700, 8704,
                                                                     8712, 8713, 8719, 8729, 8733, 8742, 8748, 8754, 8771,
                                                                     8794, 8795, 8798, 8803, 8805, 8806, 8812, 8814, 8825,
                                                                     8827, 8838, 8849, 8851, 8854, 8855, 8870, 8878, 8882,
                                                                     8886, 8890, 8893, 8900, 8902, 8913, 8916, 8923, 8929,
                                                                     8935, 8942, 8943, 8949, 8953, 8955, 8966, 8968, 8971,
                                                                     8973, 8980, 8995, 8999, 9000, 9007, 9013, 9041, 9042,
                                                                     9047, 9064, 9068, 9077, 9082, 9083, 9095, 9103, 9109,
                                                                     9117, 9123, 9127, 9131, 9137, 9140, 9149, 9161, 9179,
                                                                     9181, 9183, 9185, 9190, 9196, 9203, 9207, 9226, 9227,
                                                                     9229, 9230, 9231, 9250, 9255, 9259, 9267, 9273, 9281,
                                                                     9282, 9289, 9292, 9303, 9310, 9312, 9315, 9327, 9333,
                                                                     9335, 9337, 9343, 9356, 9368, 9370, 9383, 9392, 9404,
                                                                     9410, 9421, 9428, 9432, 9437, 9468, 9479, 9483, 9485,
                                                                     9492, 9495, 9497, 9498, 9500, 9510, 9527, 9529, 9530,
                                                                     9538, 9539, 9545, 9557, 9558, 9560, 9564, 9567, 9570,
                                                                     9591, 9596, 9598, 9616, 9631, 9634, 9635, 9636, 9658,
                                                                     9666, 9672, 9684, 9692, 9700, 9704, 9706, 9711, 9719,
                                                                     9727, 9735, 9741, 9744, 9749, 9752, 9753, 9755, 9757,
                                                                     9764, 9783, 9784, 9788, 9790, 9808, 9820, 9839, 9841,
                                                                     9843, 9853, 9855, 9859, 9863, 9877, 9879, 9880, 9882,
                                                                     9883, 9885, 9901, 9904, 9908, 9910, 9912, 9920, 9929,
                                                                     9930, 9935, 9939, 9958, 9959, 9961, 9983, 10027, 10033,
                                                                     10038, 10045, 10047, 10048, 10058, 10059, 10067, 10069,
                                                                     10073, 10075, 10078, 10079, 10081, 10092, 10106, 10110,
                                                                     10113, 10131) then 2
                                                    else 0 end as ads_campaign,
                                               count(action) filter (WHERE action = 'cancel_order') OVER (PARTITION BY order_id) as is_canceled
                                        FROM   user_actions) t1
                                WHERE  ads_campaign in (1, 2)
                                   and is_canceled = 0
                                   and time::date >= '2022-09-01'
                                   and time::date < '2022-09-08') t2
                            LEFT JOIN (SELECT order_id,
                                              unnest(product_ids) as product_id
                                       FROM   orders) t3 using(order_id)
                            LEFT JOIN products using(product_id)) t4
                GROUP BY ads_campaign, user_id, order_id) t5
        GROUP BY ads_campaign, user_id) t6
GROUP BY ads_campaign
ORDER BY avg_check desc

	ads_campaign	avg_check
0	Company № 2	380.88
1	Company № 1	371.73

Збережемо результат запиту у змінну results_10:

%%sql
results_10 << SELECT concat('Company № ', ads_campaign) as ads_campaign,
       round(avg(user_avg_check), 2) as avg_check
FROM   (SELECT ads_campaign,
               user_id,
               round(avg(order_price), 2) as user_avg_check
        FROM   (SELECT ads_campaign,
                       user_id,
                       order_id,
                       sum(price) as order_price
                FROM   (SELECT ads_campaign,
                               user_id,
                               order_id,
                               product_id,
                               price
                        FROM   (SELECT ads_campaign,
                                       user_id,
                                       order_id
                                FROM   (SELECT user_id,
                                               order_id,
                                               time,
                                               case when user_id in (8631, 8632, 8638, 8643, 8657, 8673, 8706, 8707, 8715, 8723, 8732,
                                                                     8739, 8741, 8750, 8751, 8752, 8770, 8774, 8788, 8791,
                                                                     8804, 8810, 8815, 8828, 8830, 8845, 8853, 8859, 8867,
                                                                     8869, 8876, 8879, 8883, 8896, 8909, 8911, 8933, 8940,
                                                                     8972, 8976, 8988, 8990, 9002, 9004, 9009, 9019, 9020,
                                                                     9035, 9036, 9061, 9069, 9071, 9075, 9081, 9085, 9089,
                                                                     9108, 9113, 9144, 9145, 9146, 9162, 9165, 9167, 9175,
                                                                     9180, 9182, 9197, 9198, 9210, 9223, 9251, 9257, 9278,
                                                                     9287, 9291, 9313, 9317, 9321, 9334, 9351, 9391, 9398,
                                                                     9414, 9420, 9422, 9431, 9450, 9451, 9454, 9472, 9476,
                                                                     9478, 9491, 9494, 9505, 9512, 9518, 9524, 9526, 9528,
                                                                     9531, 9535, 9550, 9559, 9561, 9562, 9599, 9603, 9605,
                                                                     9611, 9612, 9615, 9625, 9633, 9652, 9654, 9655, 9660,
                                                                     9662, 9667, 9677, 9679, 9689, 9695, 9720, 9726, 9739,
                                                                     9740, 9762, 9778, 9786, 9794, 9804, 9810, 9813, 9818,
                                                                     9828, 9831, 9836, 9838, 9845, 9871, 9887, 9891, 9896,
                                                                     9897, 9916, 9945, 9960, 9963, 9965, 9968, 9971, 9993,
                                                                     9998, 9999, 10001, 10013, 10016, 10023, 10030, 10051,
                                                                     10057, 10064, 10082, 10103, 10105, 10122, 10134, 10135) then 1
                                                    when user_id in (8629, 8630, 8644, 8646, 8650, 8655, 8659, 8660, 8663, 8665, 8670,
                                                                     8675, 8680, 8681, 8682, 8683, 8694, 8697, 8700, 8704,
                                                                     8712, 8713, 8719, 8729, 8733, 8742, 8748, 8754, 8771,
                                                                     8794, 8795, 8798, 8803, 8805, 8806, 8812, 8814, 8825,
                                                                     8827, 8838, 8849, 8851, 8854, 8855, 8870, 8878, 8882,
                                                                     8886, 8890, 8893, 8900, 8902, 8913, 8916, 8923, 8929,
                                                                     8935, 8942, 8943, 8949, 8953, 8955, 8966, 8968, 8971,
                                                                     8973, 8980, 8995, 8999, 9000, 9007, 9013, 9041, 9042,
                                                                     9047, 9064, 9068, 9077, 9082, 9083, 9095, 9103, 9109,
                                                                     9117, 9123, 9127, 9131, 9137, 9140, 9149, 9161, 9179,
                                                                     9181, 9183, 9185, 9190, 9196, 9203, 9207, 9226, 9227,
                                                                     9229, 9230, 9231, 9250, 9255, 9259, 9267, 9273, 9281,
                                                                     9282, 9289, 9292, 9303, 9310, 9312, 9315, 9327, 9333,
                                                                     9335, 9337, 9343, 9356, 9368, 9370, 9383, 9392, 9404,
                                                                     9410, 9421, 9428, 9432, 9437, 9468, 9479, 9483, 9485,
                                                                     9492, 9495, 9497, 9498, 9500, 9510, 9527, 9529, 9530,
                                                                     9538, 9539, 9545, 9557, 9558, 9560, 9564, 9567, 9570,
                                                                     9591, 9596, 9598, 9616, 9631, 9634, 9635, 9636, 9658,
                                                                     9666, 9672, 9684, 9692, 9700, 9704, 9706, 9711, 9719,
                                                                     9727, 9735, 9741, 9744, 9749, 9752, 9753, 9755, 9757,
                                                                     9764, 9783, 9784, 9788, 9790, 9808, 9820, 9839, 9841,
                                                                     9843, 9853, 9855, 9859, 9863, 9877, 9879, 9880, 9882,
                                                                     9883, 9885, 9901, 9904, 9908, 9910, 9912, 9920, 9929,
                                                                     9930, 9935, 9939, 9958, 9959, 9961, 9983, 10027, 10033,
                                                                     10038, 10045, 10047, 10048, 10058, 10059, 10067, 10069,
                                                                     10073, 10075, 10078, 10079, 10081, 10092, 10106, 10110,
                                                                     10113, 10131) then 2
                                                    else 0 end as ads_campaign,
                                               count(action) filter (WHERE action = 'cancel_order') OVER (PARTITION BY order_id) as is_canceled
                                        FROM   user_actions) t1
                                WHERE  ads_campaign in (1, 2)
                                   and is_canceled = 0
                                   and time::date >= '2022-09-01'
                                   and time::date < '2022-09-08') t2
                            LEFT JOIN (SELECT order_id,
                                              unnest(product_ids) as product_id
                                       FROM   orders) t3 using(order_id)
                            LEFT JOIN products using(product_id)) t4
                GROUP BY ads_campaign, user_id, order_id) t5
        GROUP BY ads_campaign, user_id) t6
GROUP BY ads_campaign
ORDER BY avg_check desc

Візуалізуємо отримані дані:

fig = px.bar(results_10, x="ads_campaign", y="avg_check", text_auto=True)
fig.show()

Рисунок 22.10: Середній чек за кампаніями

22.9 Retention rate

Середній чек ми порахували, але відповіді на запитання не отримали. У чому тоді може бути справа? Звернемо увагу на ще один важливий показник — Retention rate.

Retention rate – коефіцієнт утримання клієнтів. Він показує частку користувачів, які повернулися до програми через N днів, тижнів або місяців після свого першого входу — метрику можна розраховувати за будь-якими цікавими періодами.

У разі нашого сервісу доставки високий Retention означає, що користувачі часто повертаються, щоб зробити повторне замовлення. Низький Retention, навпаки, говорить про те, що більшість користувачів йдуть у відтік після однієї чи кількох взаємодій. Можливо, їм незручно користуватися нашим додатком, або їх не влаштовують ціни, асортимент товарів, швидкість або вартість доставки — причини можуть бути різні, і щоб їх встановити, потрібні додаткові дослідження.

Таким чином, метрика Retention відображає цінність нашого продукту в очах користувачів: якщо вони повертаються до нас із певною періодичністю, то наш продукт допомагає їм вирішувати свої завдання.

Можливо, саме цей показник допоможе нам відповісти на запитання, чому одна із двох рекламних кампаній показує себе краще. Але перш ніж приступати до аналізу Retention, давайте розберемося докладніше з методикою його розрахунку.

Завдання 22.11
На основі даних у таблиці user_actions розрахуйте показник денного Retention для всіх користувачів, розбивши їх на когорти за датою першої взаємодії з нашим додатком.

У результат увімкніть чотири колонки: місяць першої взаємодії, дату першої взаємодії, кількість днів, що пройшли з дати першої взаємодії (порядковий номер дня починаючи з 0), і саме значення Retention.

Колонки зі значеннями назвіть відповідно start_month, start_date, day_number, retention.

Метрику необхідно виразити у вигляді частки, округливши отримані значення до двох знаків після коми.

Місяць першої взаємодії вкажіть у вигляді дати, заокругленої до першого числа місяця.

Результат має бути відсортований спочатку за зростанням дати першої взаємодії, потім за зростанням порядкового номера дня.

Поля в результуючій таблиці: start_month, start_date, day_number, retention

Пояснення:

В цьому завданні враховуйте всіх унікальних користувачів у таблиці user_actions.

Алгоритм розрахунку Retention

Спочатку для кожного користувача порахуйте дату найпершої дії в додатку (це можна зробити за допомогою віконної функції). Потім зробіть групування за двома полями: датою першої дії та кожною датою, представленою в даних. Далі за згрупованими даними кожного дня порахуйте кількість унікальних користувачів з певною датою першої дії. Після цього для кожної пари “дата першої взаємодії - дата” необхідно порахувати сам Retention, тобто частку унікальних користувачів у кількості користувачів у когорті (число користувачів у нульовий день). Потім залишиться лише витягти місяць із дат і обчислити різницю в днях між кожною датою та датою першої взаємодії.

%%sql
SELECT date_trunc('month', start_date)::date as start_month,
       start_date,
       date - start_date as day_number,
       round(users::decimal / max(users) OVER (PARTITION BY start_date), 2) as retention
FROM   (SELECT start_date,
               date,
               count(distinct user_id) as users
        FROM   (SELECT user_id,
                       time::date as date,
                       min(time::date) OVER (PARTITION BY user_id) as start_date
                FROM   user_actions) t1
        GROUP BY start_date, date) t2

	start_month	start_date	day_number	retention
0	2022-08-01	2022-08-31	4	0.16
1	2022-08-01	2022-08-31	8	0.10
2	2022-08-01	2022-08-31	2	0.16
3	2022-08-01	2022-08-31	3	0.17
4	2022-08-01	2022-08-31	6	0.10
...	...	...	...	...
131	2022-09-01	2022-09-03	1	0.21
132	2022-09-01	2022-09-03	3	0.12
133	2022-09-01	2022-09-03	4	0.14
134	2022-09-01	2022-09-03	2	0.18
135	2022-09-01	2022-09-08	0	1.00

136 rows × 4 columns

Збережемо результат запиту у змінну results_11:

%%sql
results_11 << SELECT date_trunc('month', start_date)::date as start_month,
       start_date,
       date - start_date as day_number,
       round(users::decimal / max(users) OVER (PARTITION BY start_date), 2) as retention
FROM   (SELECT start_date,
               date,
               count(distinct user_id) as users
        FROM   (SELECT user_id,
                       time::date as date,
                       min(time::date) OVER (PARTITION BY user_id) as start_date
                FROM   user_actions) t1
        GROUP BY start_date, date) t2

Побудуємо зведену таблицю:

pd.pivot_table(
    results_11,
    index=["start_month", "start_date"],
    columns="day_number",
    values="retention",
).style.background_gradient(
    cmap="Blues",
)

	day_number	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
start_month	start_date
2022-08-01	2022-08-24	1.000000	0.140000	0.150000	0.180000	0.190000	0.140000	0.120000	0.160000	0.100000	0.150000	0.110000	0.150000	0.070000	0.050000	0.050000	0.070000
	2022-08-25	1.000000	0.180000	0.160000	0.190000	0.170000	0.190000	0.160000	0.170000	0.160000	0.150000	0.140000	0.080000	0.090000	0.100000	0.090000	nan
	2022-08-26	1.000000	0.220000	0.200000	0.220000	0.170000	0.150000	0.150000	0.140000	0.140000	0.150000	0.090000	0.090000	0.100000	0.090000	nan	nan
	2022-08-27	1.000000	0.220000	0.220000	0.190000	0.160000	0.140000	0.170000	0.180000	0.130000	0.130000	0.090000	0.100000	0.090000	nan	nan	nan
	2022-08-28	1.000000	0.220000	0.210000	0.170000	0.170000	0.200000	0.170000	0.170000	0.130000	0.110000	0.120000	0.110000	nan	nan	nan	nan
	2022-08-29	1.000000	0.180000	0.150000	0.160000	0.150000	0.160000	0.140000	0.100000	0.080000	0.090000	0.080000	nan	nan	nan	nan	nan
	2022-08-30	1.000000	0.150000	0.130000	0.140000	0.120000	0.130000	0.110000	0.070000	0.090000	0.090000	nan	nan	nan	nan	nan	nan
	2022-08-31	1.000000	0.160000	0.160000	0.170000	0.160000	0.110000	0.100000	0.120000	0.100000	nan	nan	nan	nan	nan	nan	nan
2022-09-01	2022-09-01	1.000000	0.180000	0.180000	0.180000	0.140000	0.090000	0.120000	0.100000	nan	nan	nan	nan	nan	nan	nan	nan
	2022-09-02	1.000000	0.200000	0.180000	0.150000	0.130000	0.150000	0.130000	nan	nan	nan	nan	nan	nan	nan	nan	nan
	2022-09-03	1.000000	0.210000	0.180000	0.120000	0.140000	0.140000	nan	nan	nan	nan	nan	nan	nan	nan	nan	nan
	2022-09-04	1.000000	0.160000	0.130000	0.160000	0.160000	nan	nan	nan	nan	nan	nan	nan	nan	nan	nan	nan
	2022-09-05	1.000000	0.110000	0.120000	0.150000	nan	nan	nan	nan	nan	nan	nan	nan	nan	nan	nan	nan
	2022-09-06	1.000000	0.120000	0.120000	nan	nan	nan	nan	nan	nan	nan	nan	nan	nan	nan	nan	nan
	2022-09-07	1.000000	0.170000	nan	nan	nan	nan	nan	nan	nan	nan	nan	nan	nan	nan	nan	nan
	2022-09-08	1.000000	nan	nan	nan	nan	nan	nan	nan	nan	nan	nan	nan	nan	nan	nan	nan

А тепер повернемося до аналізу рекламних кампаній та порахуємо Retention для двох груп користувачів. Можливо, справа саме в тому, що один із каналів привів більш активних та зацікавлених користувачів. Давайте це з’ясуємо!

Завдання 22.12
Для кожної рекламної кампанії порахуйте Retention 1-го та 7-го дня у залучених користувачів.

У результаті включіть чотири колонки: колонку з найменуваннями кампаній, дату першої взаємодії з додатком, кількість днів, що минули з дати першої взаємодії (порядковий номер), і саме значення Retention.

Колонки зі значеннями назвіть відповідно ads_campaign, start_date, day_number, retention.

Назви кампаній виведіть у такому вигляді:

Company № 1
Company № 2

Метрику необхідно виразити у вигляді частки, округливши отримані значення до двох знаків після коми.

Результат має бути відсортований спочатку за найменуванням кампанії (за зростанням), потім за зростанням порядкового номера дня.

У результат мають потрапити наступні дні: нульовий, перший та сьомий.

Поля в результуючій таблиці: ads_campaign, start_date, day_number, retention

Пояснення:

Списки користувачів, що зареєструвалися, ті ж, що і на попередніх кроках.

Підказка на випадок, якщо зовсім не виходить

Алгоритм розрахунку Retention той самий, що і в попередньому завданні, тільки тепер необхідно додати рекламну кампанію в групування і потім відфільтрувати необхідні дні.

%%sql
SELECT concat('Company № ', ads_campaign) as ads_campaign,
       start_date,
       day_number,
       round(users::decimal / max(users) OVER (PARTITION BY ads_campaign,
                                                            start_date), 2) as retention
FROM   (SELECT ads_campaign,
               start_date,
               date - start_date as day_number,
               count(distinct user_id) as users
        FROM   (SELECT ads_campaign,
                       user_id,
                       date,
                       min(date) OVER (PARTITION BY ads_campaign,
                                                    user_id) as start_date
                FROM   (SELECT user_id,
                               time::date as date,
                               case when user_id in (8631, 8632, 8638, 8643, 8657, 8673, 8706, 8707, 8715, 8723, 8732,
                                                     8739, 8741, 8750, 8751, 8752, 8770, 8774, 8788, 8791,
                                                     8804, 8810, 8815, 8828, 8830, 8845, 8853, 8859, 8867,
                                                     8869, 8876, 8879, 8883, 8896, 8909, 8911, 8933, 8940,
                                                     8972, 8976, 8988, 8990, 9002, 9004, 9009, 9019, 9020,
                                                     9035, 9036, 9061, 9069, 9071, 9075, 9081, 9085, 9089,
                                                     9108, 9113, 9144, 9145, 9146, 9162, 9165, 9167, 9175,
                                                     9180, 9182, 9197, 9198, 9210, 9223, 9251, 9257, 9278,
                                                     9287, 9291, 9313, 9317, 9321, 9334, 9351, 9391, 9398,
                                                     9414, 9420, 9422, 9431, 9450, 9451, 9454, 9472, 9476,
                                                     9478, 9491, 9494, 9505, 9512, 9518, 9524, 9526, 9528,
                                                     9531, 9535, 9550, 9559, 9561, 9562, 9599, 9603, 9605,
                                                     9611, 9612, 9615, 9625, 9633, 9652, 9654, 9655, 9660,
                                                     9662, 9667, 9677, 9679, 9689, 9695, 9720, 9726, 9739,
                                                     9740, 9762, 9778, 9786, 9794, 9804, 9810, 9813, 9818,
                                                     9828, 9831, 9836, 9838, 9845, 9871, 9887, 9891, 9896,
                                                     9897, 9916, 9945, 9960, 9963, 9965, 9968, 9971, 9993,
                                                     9998, 9999, 10001, 10013, 10016, 10023, 10030, 10051,
                                                     10057, 10064, 10082, 10103, 10105, 10122, 10134, 10135) then 1
                                    when user_id in (8629, 8630, 8644, 8646, 8650, 8655, 8659, 8660, 8663, 8665, 8670,
                                                     8675, 8680, 8681, 8682, 8683, 8694, 8697, 8700, 8704,
                                                     8712, 8713, 8719, 8729, 8733, 8742, 8748, 8754, 8771,
                                                     8794, 8795, 8798, 8803, 8805, 8806, 8812, 8814, 8825,
                                                     8827, 8838, 8849, 8851, 8854, 8855, 8870, 8878, 8882,
                                                     8886, 8890, 8893, 8900, 8902, 8913, 8916, 8923, 8929,
                                                     8935, 8942, 8943, 8949, 8953, 8955, 8966, 8968, 8971,
                                                     8973, 8980, 8995, 8999, 9000, 9007, 9013, 9041, 9042,
                                                     9047, 9064, 9068, 9077, 9082, 9083, 9095, 9103, 9109,
                                                     9117, 9123, 9127, 9131, 9137, 9140, 9149, 9161, 9179,
                                                     9181, 9183, 9185, 9190, 9196, 9203, 9207, 9226, 9227,
                                                     9229, 9230, 9231, 9250, 9255, 9259, 9267, 9273, 9281,
                                                     9282, 9289, 9292, 9303, 9310, 9312, 9315, 9327, 9333,
                                                     9335, 9337, 9343, 9356, 9368, 9370, 9383, 9392, 9404,
                                                     9410, 9421, 9428, 9432, 9437, 9468, 9479, 9483, 9485,
                                                     9492, 9495, 9497, 9498, 9500, 9510, 9527, 9529, 9530,
                                                     9538, 9539, 9545, 9557, 9558, 9560, 9564, 9567, 9570,
                                                     9591, 9596, 9598, 9616, 9631, 9634, 9635, 9636, 9658,
                                                     9666, 9672, 9684, 9692, 9700, 9704, 9706, 9711, 9719,
                                                     9727, 9735, 9741, 9744, 9749, 9752, 9753, 9755, 9757,
                                                     9764, 9783, 9784, 9788, 9790, 9808, 9820, 9839, 9841,
                                                     9843, 9853, 9855, 9859, 9863, 9877, 9879, 9880, 9882,
                                                     9883, 9885, 9901, 9904, 9908, 9910, 9912, 9920, 9929,
                                                     9930, 9935, 9939, 9958, 9959, 9961, 9983, 10027, 10033,
                                                     10038, 10045, 10047, 10048, 10058, 10059, 10067, 10069,
                                                     10073, 10075, 10078, 10079, 10081, 10092, 10106, 10110,
                                                     10113, 10131) then 2
                                    else 0 end as ads_campaign
                        FROM   user_actions) t1
                WHERE  ads_campaign in (1, 2)) t2
        GROUP BY ads_campaign, start_date, date) t3
WHERE  day_number in (0, 1, 7)

	ads_campaign	start_date	day_number	retention
0	Company № 2	2022-09-01	0	1.00
1	Company № 2	2022-09-01	1	0.17
2	Company № 2	2022-09-01	7	0.09
3	Company № 1	2022-09-01	0	1.00
4	Company № 1	2022-09-01	1	0.42
5	Company № 1	2022-09-01	7	0.22

Збережемо результат запиту у змінну results_12:

%%sql
results_12 << SELECT concat('Company № ', ads_campaign) as ads_campaign,
       start_date,
       day_number,
       round(users::decimal / max(users) OVER (PARTITION BY ads_campaign,
                                                            start_date), 2) as retention
FROM   (SELECT ads_campaign,
               start_date,
               date - start_date as day_number,
               count(distinct user_id) as users
        FROM   (SELECT ads_campaign,
                       user_id,
                       date,
                       min(date) OVER (PARTITION BY ads_campaign,
                                                    user_id) as start_date
                FROM   (SELECT user_id,
                               time::date as date,
                               case when user_id in (8631, 8632, 8638, 8643, 8657, 8673, 8706, 8707, 8715, 8723, 8732,
                                                     8739, 8741, 8750, 8751, 8752, 8770, 8774, 8788, 8791,
                                                     8804, 8810, 8815, 8828, 8830, 8845, 8853, 8859, 8867,
                                                     8869, 8876, 8879, 8883, 8896, 8909, 8911, 8933, 8940,
                                                     8972, 8976, 8988, 8990, 9002, 9004, 9009, 9019, 9020,
                                                     9035, 9036, 9061, 9069, 9071, 9075, 9081, 9085, 9089,
                                                     9108, 9113, 9144, 9145, 9146, 9162, 9165, 9167, 9175,
                                                     9180, 9182, 9197, 9198, 9210, 9223, 9251, 9257, 9278,
                                                     9287, 9291, 9313, 9317, 9321, 9334, 9351, 9391, 9398,
                                                     9414, 9420, 9422, 9431, 9450, 9451, 9454, 9472, 9476,
                                                     9478, 9491, 9494, 9505, 9512, 9518, 9524, 9526, 9528,
                                                     9531, 9535, 9550, 9559, 9561, 9562, 9599, 9603, 9605,
                                                     9611, 9612, 9615, 9625, 9633, 9652, 9654, 9655, 9660,
                                                     9662, 9667, 9677, 9679, 9689, 9695, 9720, 9726, 9739,
                                                     9740, 9762, 9778, 9786, 9794, 9804, 9810, 9813, 9818,
                                                     9828, 9831, 9836, 9838, 9845, 9871, 9887, 9891, 9896,
                                                     9897, 9916, 9945, 9960, 9963, 9965, 9968, 9971, 9993,
                                                     9998, 9999, 10001, 10013, 10016, 10023, 10030, 10051,
                                                     10057, 10064, 10082, 10103, 10105, 10122, 10134, 10135) then 1
                                    when user_id in (8629, 8630, 8644, 8646, 8650, 8655, 8659, 8660, 8663, 8665, 8670,
                                                     8675, 8680, 8681, 8682, 8683, 8694, 8697, 8700, 8704,
                                                     8712, 8713, 8719, 8729, 8733, 8742, 8748, 8754, 8771,
                                                     8794, 8795, 8798, 8803, 8805, 8806, 8812, 8814, 8825,
                                                     8827, 8838, 8849, 8851, 8854, 8855, 8870, 8878, 8882,
                                                     8886, 8890, 8893, 8900, 8902, 8913, 8916, 8923, 8929,
                                                     8935, 8942, 8943, 8949, 8953, 8955, 8966, 8968, 8971,
                                                     8973, 8980, 8995, 8999, 9000, 9007, 9013, 9041, 9042,
                                                     9047, 9064, 9068, 9077, 9082, 9083, 9095, 9103, 9109,
                                                     9117, 9123, 9127, 9131, 9137, 9140, 9149, 9161, 9179,
                                                     9181, 9183, 9185, 9190, 9196, 9203, 9207, 9226, 9227,
                                                     9229, 9230, 9231, 9250, 9255, 9259, 9267, 9273, 9281,
                                                     9282, 9289, 9292, 9303, 9310, 9312, 9315, 9327, 9333,
                                                     9335, 9337, 9343, 9356, 9368, 9370, 9383, 9392, 9404,
                                                     9410, 9421, 9428, 9432, 9437, 9468, 9479, 9483, 9485,
                                                     9492, 9495, 9497, 9498, 9500, 9510, 9527, 9529, 9530,
                                                     9538, 9539, 9545, 9557, 9558, 9560, 9564, 9567, 9570,
                                                     9591, 9596, 9598, 9616, 9631, 9634, 9635, 9636, 9658,
                                                     9666, 9672, 9684, 9692, 9700, 9704, 9706, 9711, 9719,
                                                     9727, 9735, 9741, 9744, 9749, 9752, 9753, 9755, 9757,
                                                     9764, 9783, 9784, 9788, 9790, 9808, 9820, 9839, 9841,
                                                     9843, 9853, 9855, 9859, 9863, 9877, 9879, 9880, 9882,
                                                     9883, 9885, 9901, 9904, 9908, 9910, 9912, 9920, 9929,
                                                     9930, 9935, 9939, 9958, 9959, 9961, 9983, 10027, 10033,
                                                     10038, 10045, 10047, 10048, 10058, 10059, 10067, 10069,
                                                     10073, 10075, 10078, 10079, 10081, 10092, 10106, 10110,
                                                     10113, 10131) then 2
                                    else 0 end as ads_campaign
                        FROM   user_actions) t1
                WHERE  ads_campaign in (1, 2)) t2
        GROUP BY ads_campaign, start_date, date) t3
WHERE  day_number in (0, 1, 7)

Побудуємо зведену таблицю:

pd.pivot_table(
    results_12,
    index="ads_campaign",
    columns="day_number",
    values="retention",
).style.background_gradient(
    cmap="Blues",
)

day_number	0	1	7
ads_campaign
Company № 1	1.000000	0.420000	0.220000
Company № 2	1.000000	0.170000	0.090000

То чому дві рекламні кампанії відрізняються за значенням метрики ROI? Який висновок можна зробити?

Висновок: користувачі з обох рекламних каналів практично не відрізняються за середнім чеком, але Retention майже вдвічі вищий у першої групи. Це призводить до того, що користувачі з першої групи приносять нам більше грошей.

22.10 Накопичувальний ARPPU

І насамкінець давайте з’ясуємо, на який день дохід від замовлень покупців, що прийшли після першої рекламної кампанії, перевищив витрати на їхнє залучення. Для цього розрахуємо накопичувальний ARPPU та порівняємо його з уже порахованою раніше метрикою CAC. Зробимо це для двох кампаній, щоб переконатися, що у випадку другої рекламної кампанії витрати ми поки не окупили.

Суть розрахунку накопичувального ARPPU полягає в тому, що для кожного дня кількість покупців у когорті залишатиметься незмінною, а отриманий від їх замовлень дохід — зростатиме. В результаті накопичувальний ARPPU поступово збільшуватиметься і в якийсь момент перевищить суму початкових витрат на залучення одного покупця.

Завдання 22.13
Для кожної рекламної кампанії кожного дня порахуйте дві метрики:

1. Накопичувальний ARPPU.
2. Витрати залучення одного покупця (CAC).

Колонку з найменуваннями кампаній назвіть ads_campaign, колонку з днями – day, а колонки зі значеннями метрик – cumulative_arppu та cac.

Значення метрики CAC вкажіть однаковим для всіх днів (це необхідно для візуалізації).

Назви кампаній виведіть у такому вигляді:

Company № 1
Company № 2

Дні пронумеруйте починаючи з 0 та відобразіть у наступному форматі:

Day 0
Day 1
Day 2
...

Отримані значення метрика необхідно округлити до двох знаків після коми.

Результат повинен бути відсортований спочатку за найменуванням кампанії (за зростанням), потім за найменуванням дня (також за зростанням).

Поля в результуючій таблиці: ads_campaign, day, cumulative_arppu, cac

Пояснення:

Списки користувачів, що зареєструвалися, ті ж, що і на попередніх кроках.

Підказка на випадок, якщо зовсім не виходить

Щоб отримати необхідну таблицю, потрібно для кожної рекламної кампанії для кожного дня порахувати сумарну вартість замовлень користувачів, що прийшли відповідним каналом, і розділити її на кількість користувачів, що прийшли в нульовий день. У колонці зі значенням метрики CAC необхідно проставити те саме значення для кожного дня. Метрику CAC ми рахували раніше.

%%sql
with main_table as (SELECT ads_campaign,
                           user_id,
                           order_id,
                           time,
                           product_id,
                           price
                    FROM   (SELECT ads_campaign,
                                   user_id,
                                   order_id,
                                   time
                            FROM   (SELECT user_id,
                                           order_id,
                                           time,
                                           case when user_id in (8631, 8632, 8638, 8643, 8657, 8673, 8706, 8707, 8715, 8723, 8732,
                                                                 8739, 8741, 8750, 8751, 8752, 8770, 8774, 8788, 8791,
                                                                 8804, 8810, 8815, 8828, 8830, 8845, 8853, 8859, 8867,
                                                                 8869, 8876, 8879, 8883, 8896, 8909, 8911, 8933, 8940,
                                                                 8972, 8976, 8988, 8990, 9002, 9004, 9009, 9019, 9020,
                                                                 9035, 9036, 9061, 9069, 9071, 9075, 9081, 9085, 9089,
                                                                 9108, 9113, 9144, 9145, 9146, 9162, 9165, 9167, 9175,
                                                                 9180, 9182, 9197, 9198, 9210, 9223, 9251, 9257, 9278,
                                                                 9287, 9291, 9313, 9317, 9321, 9334, 9351, 9391, 9398,
                                                                 9414, 9420, 9422, 9431, 9450, 9451, 9454, 9472, 9476,
                                                                 9478, 9491, 9494, 9505, 9512, 9518, 9524, 9526, 9528,
                                                                 9531, 9535, 9550, 9559, 9561, 9562, 9599, 9603, 9605,
                                                                 9611, 9612, 9615, 9625, 9633, 9652, 9654, 9655, 9660,
                                                                 9662, 9667, 9677, 9679, 9689, 9695, 9720, 9726, 9739,
                                                                 9740, 9762, 9778, 9786, 9794, 9804, 9810, 9813, 9818,
                                                                 9828, 9831, 9836, 9838, 9845, 9871, 9887, 9891, 9896,
                                                                 9897, 9916, 9945, 9960, 9963, 9965, 9968, 9971, 9993,
                                                                 9998, 9999, 10001, 10013, 10016, 10023, 10030, 10051,
                                                                 10057, 10064, 10082, 10103, 10105, 10122, 10134, 10135) then 1
                                                when user_id in (8629, 8630, 8644, 8646, 8650, 8655, 8659, 8660, 8663, 8665, 8670,
                                                                 8675, 8680, 8681, 8682, 8683, 8694, 8697, 8700, 8704,
                                                                 8712, 8713, 8719, 8729, 8733, 8742, 8748, 8754, 8771,
                                                                 8794, 8795, 8798, 8803, 8805, 8806, 8812, 8814, 8825,
                                                                 8827, 8838, 8849, 8851, 8854, 8855, 8870, 8878, 8882,
                                                                 8886, 8890, 8893, 8900, 8902, 8913, 8916, 8923, 8929,
                                                                 8935, 8942, 8943, 8949, 8953, 8955, 8966, 8968, 8971,
                                                                 8973, 8980, 8995, 8999, 9000, 9007, 9013, 9041, 9042,
                                                                 9047, 9064, 9068, 9077, 9082, 9083, 9095, 9103, 9109,
                                                                 9117, 9123, 9127, 9131, 9137, 9140, 9149, 9161, 9179,
                                                                 9181, 9183, 9185, 9190, 9196, 9203, 9207, 9226, 9227,
                                                                 9229, 9230, 9231, 9250, 9255, 9259, 9267, 9273, 9281,
                                                                 9282, 9289, 9292, 9303, 9310, 9312, 9315, 9327, 9333,
                                                                 9335, 9337, 9343, 9356, 9368, 9370, 9383, 9392, 9404,
                                                                 9410, 9421, 9428, 9432, 9437, 9468, 9479, 9483, 9485,
                                                                 9492, 9495, 9497, 9498, 9500, 9510, 9527, 9529, 9530,
                                                                 9538, 9539, 9545, 9557, 9558, 9560, 9564, 9567, 9570,
                                                                 9591, 9596, 9598, 9616, 9631, 9634, 9635, 9636, 9658,
                                                                 9666, 9672, 9684, 9692, 9700, 9704, 9706, 9711, 9719,
                                                                 9727, 9735, 9741, 9744, 9749, 9752, 9753, 9755, 9757,
                                                                 9764, 9783, 9784, 9788, 9790, 9808, 9820, 9839, 9841,
                                                                 9843, 9853, 9855, 9859, 9863, 9877, 9879, 9880, 9882,
                                                                 9883, 9885, 9901, 9904, 9908, 9910, 9912, 9920, 9929,
                                                                 9930, 9935, 9939, 9958, 9959, 9961, 9983, 10027, 10033,
                                                                 10038, 10045, 10047, 10048, 10058, 10059, 10067, 10069,
                                                                 10073, 10075, 10078, 10079, 10081, 10092, 10106, 10110,
                                                                 10113, 10131) then 2
                                                else 0 end as ads_campaign,
                                           count(action) filter (WHERE action = 'cancel_order') OVER (PARTITION BY order_id) as is_canceled
                                    FROM   user_actions) t1
                            WHERE  ads_campaign in (1, 2)
                               and is_canceled = 0) t2
                        LEFT JOIN (SELECT order_id,
                                          unnest(product_ids) as product_id
                                   FROM   orders) t3 using(order_id)
                        LEFT JOIN products using(product_id))
SELECT concat('Кампания № ', ads_campaign) as ads_campaign,
       concat('Day ', row_number() OVER (PARTITION BY ads_campaign
                                         ORDER BY date) - 1) as day,
       round(sum(revenue) OVER (PARTITION BY ads_campaign
                                ORDER BY date) / paying_users::decimal, 2) as cumulative_arppu,
       cac
FROM   (SELECT ads_campaign,
               time::date as date,
               sum(price) as revenue
        FROM   main_table
        GROUP BY ads_campaign, time::date) t1
    LEFT JOIN (SELECT ads_campaign,
                      count(distinct user_id) as paying_users,
                      round(250000.0 / count(distinct user_id), 2) as cac
               FROM   main_table
               GROUP BY ads_campaign) t2 using (ads_campaign)

	ads_campaign	day	cumulative_arppu	cac
0	Кампания № 2	Day 0	548.42	1068.38
1	Кампания № 2	Day 1	656.20	1068.38
2	Кампания № 2	Day 2	765.34	1068.38
3	Кампания № 2	Day 3	829.88	1068.38
4	Кампания № 2	Day 4	888.80	1068.38
5	Кампания № 2	Day 5	938.66	1068.38
6	Кампания № 2	Day 6	999.46	1068.38
7	Кампания № 2	Day 7	1051.21	1068.38
8	Кампания № 1	Day 0	521.36	1461.99
9	Кампания № 1	Day 1	784.64	1461.99
10	Кампания № 1	Day 2	1010.70	1461.99
11	Кампания № 1	Day 3	1227.84	1461.99
12	Кампания № 1	Day 4	1375.46	1461.99
13	Кампания № 1	Day 5	1464.25	1461.99
14	Кампания № 1	Day 6	1575.26	1461.99
15	Кампания № 1	Day 7	1674.02	1461.99

Збережемо результат запиту у змінну results_13:

%%sql
results_13 << with main_table as (SELECT ads_campaign,
                           user_id,
                           order_id,
                           time,
                           product_id,
                           price
                    FROM   (SELECT ads_campaign,
                                   user_id,
                                   order_id,
                                   time
                            FROM   (SELECT user_id,
                                           order_id,
                                           time,
                                           case when user_id in (8631, 8632, 8638, 8643, 8657, 8673, 8706, 8707, 8715, 8723, 8732,
                                                                 8739, 8741, 8750, 8751, 8752, 8770, 8774, 8788, 8791,
                                                                 8804, 8810, 8815, 8828, 8830, 8845, 8853, 8859, 8867,
                                                                 8869, 8876, 8879, 8883, 8896, 8909, 8911, 8933, 8940,
                                                                 8972, 8976, 8988, 8990, 9002, 9004, 9009, 9019, 9020,
                                                                 9035, 9036, 9061, 9069, 9071, 9075, 9081, 9085, 9089,
                                                                 9108, 9113, 9144, 9145, 9146, 9162, 9165, 9167, 9175,
                                                                 9180, 9182, 9197, 9198, 9210, 9223, 9251, 9257, 9278,
                                                                 9287, 9291, 9313, 9317, 9321, 9334, 9351, 9391, 9398,
                                                                 9414, 9420, 9422, 9431, 9450, 9451, 9454, 9472, 9476,
                                                                 9478, 9491, 9494, 9505, 9512, 9518, 9524, 9526, 9528,
                                                                 9531, 9535, 9550, 9559, 9561, 9562, 9599, 9603, 9605,
                                                                 9611, 9612, 9615, 9625, 9633, 9652, 9654, 9655, 9660,
                                                                 9662, 9667, 9677, 9679, 9689, 9695, 9720, 9726, 9739,
                                                                 9740, 9762, 9778, 9786, 9794, 9804, 9810, 9813, 9818,
                                                                 9828, 9831, 9836, 9838, 9845, 9871, 9887, 9891, 9896,
                                                                 9897, 9916, 9945, 9960, 9963, 9965, 9968, 9971, 9993,
                                                                 9998, 9999, 10001, 10013, 10016, 10023, 10030, 10051,
                                                                 10057, 10064, 10082, 10103, 10105, 10122, 10134, 10135) then 1
                                                when user_id in (8629, 8630, 8644, 8646, 8650, 8655, 8659, 8660, 8663, 8665, 8670,
                                                                 8675, 8680, 8681, 8682, 8683, 8694, 8697, 8700, 8704,
                                                                 8712, 8713, 8719, 8729, 8733, 8742, 8748, 8754, 8771,
                                                                 8794, 8795, 8798, 8803, 8805, 8806, 8812, 8814, 8825,
                                                                 8827, 8838, 8849, 8851, 8854, 8855, 8870, 8878, 8882,
                                                                 8886, 8890, 8893, 8900, 8902, 8913, 8916, 8923, 8929,
                                                                 8935, 8942, 8943, 8949, 8953, 8955, 8966, 8968, 8971,
                                                                 8973, 8980, 8995, 8999, 9000, 9007, 9013, 9041, 9042,
                                                                 9047, 9064, 9068, 9077, 9082, 9083, 9095, 9103, 9109,
                                                                 9117, 9123, 9127, 9131, 9137, 9140, 9149, 9161, 9179,
                                                                 9181, 9183, 9185, 9190, 9196, 9203, 9207, 9226, 9227,
                                                                 9229, 9230, 9231, 9250, 9255, 9259, 9267, 9273, 9281,
                                                                 9282, 9289, 9292, 9303, 9310, 9312, 9315, 9327, 9333,
                                                                 9335, 9337, 9343, 9356, 9368, 9370, 9383, 9392, 9404,
                                                                 9410, 9421, 9428, 9432, 9437, 9468, 9479, 9483, 9485,
                                                                 9492, 9495, 9497, 9498, 9500, 9510, 9527, 9529, 9530,
                                                                 9538, 9539, 9545, 9557, 9558, 9560, 9564, 9567, 9570,
                                                                 9591, 9596, 9598, 9616, 9631, 9634, 9635, 9636, 9658,
                                                                 9666, 9672, 9684, 9692, 9700, 9704, 9706, 9711, 9719,
                                                                 9727, 9735, 9741, 9744, 9749, 9752, 9753, 9755, 9757,
                                                                 9764, 9783, 9784, 9788, 9790, 9808, 9820, 9839, 9841,
                                                                 9843, 9853, 9855, 9859, 9863, 9877, 9879, 9880, 9882,
                                                                 9883, 9885, 9901, 9904, 9908, 9910, 9912, 9920, 9929,
                                                                 9930, 9935, 9939, 9958, 9959, 9961, 9983, 10027, 10033,
                                                                 10038, 10045, 10047, 10048, 10058, 10059, 10067, 10069,
                                                                 10073, 10075, 10078, 10079, 10081, 10092, 10106, 10110,
                                                                 10113, 10131) then 2
                                                else 0 end as ads_campaign,
                                           count(action) filter (WHERE action = 'cancel_order') OVER (PARTITION BY order_id) as is_canceled
                                    FROM   user_actions) t1
                            WHERE  ads_campaign in (1, 2)
                               and is_canceled = 0) t2
                        LEFT JOIN (SELECT order_id,
                                          unnest(product_ids) as product_id
                                   FROM   orders) t3 using(order_id)
                        LEFT JOIN products using(product_id))
SELECT concat('Кампания № ', ads_campaign) as ads_campaign,
       concat('Day ', row_number() OVER (PARTITION BY ads_campaign
                                         ORDER BY date) - 1) as day,
       round(sum(revenue) OVER (PARTITION BY ads_campaign
                                ORDER BY date) / paying_users::decimal, 2) as cumulative_arppu,
       cac
FROM   (SELECT ads_campaign,
               time::date as date,
               sum(price) as revenue
        FROM   main_table
        GROUP BY ads_campaign, time::date) t1
    LEFT JOIN (SELECT ads_campaign,
                      count(distinct user_id) as paying_users,
                      round(250000.0 / count(distinct user_id), 2) as cac
               FROM   main_table
               GROUP BY ads_campaign) t2 using (ads_campaign)

Візуалізуємо отримані дані:

import plotly.express as px

comp_1 = results_13[results_13["ads_campaign"] == "Кампания № 1"]
comp_2 = results_13[results_13["ads_campaign"] == "Кампания № 2"]

fig = px.area(comp_1, x="day", y="cumulative_arppu")
fig.add_hline(y=1461.99, line_color="red", annotation_text="CAC = 1461.99")
fig.show()

fig = px.area(comp_2, x="day", y="cumulative_arppu")
fig.add_hline(y=1068.38, line_color="red", annotation_text="CAC = 1068.38")
fig.show()

(a) Графік для першої рекламної кампанії

(b) Графік для першої рекламної кампанії

Рисунок 22.11: Графік за результатами SQL-запиту

Який висновок можна зробити на основі побудованих графіків?

Висновок: для першої рекламної кампанії накопичувальний ARPPU перевищив витрати на залучення одного покупця (CAC) вже на 5 день, тоді як для другої кампанії навіть на 7 день значення CAC все ще перевищувало значення ARPPU.