{ "version": "https:\/\/jsonfeed.org\/version\/1", "title": "Блог об аналитике, визуализации данных, data science и BI, заметки с тегом: ltv", "home_page_url": "http:\/\/test.leftjoin.ru\/tags\/ltv\/", "feed_url": "http:\/\/test.leftjoin.ru\/tags\/ltv\/json\/", "icon": "http:\/\/test.leftjoin.ru\/user\/userpic@2x.jpg", "author": { "name": "Николай Валиотти", "url": "http:\/\/test.leftjoin.ru\/", "avatar": "http:\/\/test.leftjoin.ru\/user\/userpic@2x.jpg" }, "items": [ { "id": "115", "url": "http:\/\/test.leftjoin.ru\/all\/modeling-ltv-with-sql\/", "title": "Моделирование LTV в SQL", "content_html": "

У большинства игровых и мобильных компаний имеется кривая Retention, ранее мы писали о том, что такое Retention и как его посчитать<\/a>. Вкратце — это метрика, которая позволяет понять насколько хорошо продукт вовлекает пользователей в ежедневное использование. А ещё при помощи Retention и ARPDAU можно посчитать LTV (Lifetime Value), пожизненный доход с одного пользователя. Зная средний доход с пользователя за день и кривую Retention мы можем смоделировать ее и спрогнозировать LTV.<\/p>\n

Для материала были взяты данные из одного реального игрового проекта. Нулевой день не отображен для того, чтобы видеть динамику в деталях<\/p>\n

В сегодняшнем материале мы подробно разберём, как смоделировать LTV для 180 дней при помощи SQL и просто линейной регрессии.<\/p>\n

Как посчитать LTV?<\/h2>\n

В общем случае формула LTV выглядит как ARPDAU умноженное на Lifetime — время жизни пользователя в проекте.<\/p>\n

\n\"\"\n<\/div>\n

Посмотрим на классический график Retention:<\/p>\n

\n\"\"\n<\/div>\n

Lifetime — это площадь фигуры под Retention:<\/p>\n

\n\"\"\n<\/div>\n

Откуда взялись интегралы и площади можно подробнее узнать в этом материале<\/a><\/p>\n

Значит, чтобы посчитать Lifetime, нужно взять интеграл от функции удержания по времени. Формула приобретает следующий вид:<\/p>\n

\n\"\"\n<\/div>\n

Для описания кривой Retention лучше всего подходит степенная функция a*x^b. Вот как она выглядит в сравнении с кривой Retention:<\/p>\n

\n\"\"\n<\/div>\n

При этом x — номер дня, a и b — параметры функции, которую мы построим при помощи линейной регрессии. Регрессия появилась неслучайно — эту степенную функцию можно привести к виду линейной функции, логарифмируя:<\/p>\n

\n\"\"\n<\/div>\n
\n\"\"\n<\/div>\n
\n\"\"\n<\/div>\n

ln(a) — intercept, b — slope. Остаётся найти эти параметры — в линейной регрессии для этого используют метод наименьших квадратов. Lifetime — кумулятивная сумма прогноза за 180 дней. Посчитав её, остаётся умножить Lifetime на ARPDAU и получим LTV за 180 дней.<\/p>\n

Строим LTV<\/h2>\n

Перейдём к практике. Для всех расчётов мы использовали данные одной игровой компании и СУБД PostgreSQL — в ней уже реализованы функции поиска параметров для линейной регрессии. Начнём с построения Retention: соберём общее количество пользователей в период с 1 марта по 1 апреля 2021 года — мы изучаем активность за один месяц:<\/p>\n

--общее количество юзеров в когорте\r\nwith cohort as (\r\n    select count(distinct id) as total_users_of_cohort\r\n    from users\r\n    where date(registration) between date '2021-03-01' and date '2021-03-30'\r\n),<\/code><\/pre>
Теперь посмотрим, как ведут себя эти пользователи в последующие 90 дней:<\/p>\n
--количество активных юзеров на 1ый день, 2ой, 3ий и тд. из когорты\r\nactive_users as (\r\n    select date_part('day', activity.date - users.registration) as activity_day, \r\n               count(distinct users.id) as active_users_of_day\r\n    from activity\r\n    join users on activity.user_id = users.id\r\n    where date(registration) between date '2021-03-01' and date '2021-03-30' \r\n    group by 1\r\n    having date_part('day', activity.date - users.registration) between 1 and 90 --берем только первые 90 дней, остальные дни предсказываем.\r\n),<\/code><\/pre>
Кривая Retention — отношение количества активных пользователей к размеру когорты текущего дня. В нашем случае она выглядит так:<\/p>\n
\n\"\"\n<\/div>\n
По данным кривой посчитаем параметры для линейной регрессии. regr_slope(x, y) — функция для вычисления наклона регрессии, regr_intercept(x, y) — функция для вычисления перехвата по оси Y. Эти функции являются стандартными агрегатными функциями в PostgreSQL<\/a> и для известных X и Y по методу наименьших квадратов.<\/p>\n
Вернёмся к нашей формуле — мы получили линейное уравнение, и хотим найти коэффициенты линейной регрессии. Перехват по оси Y и коэффициент наклона можем найти по дефолтным для PostgreSQL функциям. Получается:<\/p>\n
Подробнее о том, как работают функции intercept(x, y) и slope(x, y) можно почитать в этом мануале<\/a><\/p>\n
\n\"\"\n<\/div>\n
\n\"\"\n<\/div>\n
Из свойства натурального логарифма следует, что:<\/p>\n
\n\"\"\n<\/div>\n
Наклон считаем аналогичным образом:<\/p>\n
\n\"\"\n<\/div>\n
Эти же вычисления запишем в подзапрос для расчёта коэффициентов регрессии:<\/p>\n
--рассчитываем коэффициенты регрессии\r\ncoef as (\r\n    select exp(regr_intercept(ln(activity), ln(activity_day))) as a, \r\n                regr_slope(ln(activity), ln(activity_day)) as b\r\n    from(\r\n                select activity_day,\r\n                            active_users_of_day::real \/ total_users_of_cohort as activity\r\n                from active_users \r\n                cross join cohort order by activity_day \r\n            )\r\n),<\/code><\/pre>
И получим прогноз на 180 дней, подставив параметры в степенную функцию, описанную ранее. Заодно посчитаем Lifetime — кумулятивную сумму спрогнозированных данных. В подзапросе coef мы получим только два числа — параметр наклона и перехвата. Чтобы эти параметры были доступны каждой строке подзапроса lt, делаем cross join к coef:<\/p>\n
lt as(\r\n    select generate_series as activity_day,\r\n               active_users_of_day::real\/total_users_of_cohort as real_data,\r\n               a*power(generate_series,b) as pred_data, \t \r\n               sum(a*power(generate_series,b)) over(order by generate_series) as cumulative_lt\r\n    from generate_series(1,180,1)\r\n    cross join coef\r\n    join active_users on generate_series = activity_day::int\r\n),<\/code><\/pre>
Сравним прогноз на 180 дней с Retention:<\/p>\n
\n\"\"\n<\/div>\n
Наконец, считаем сам LTV — Lifetime, умноженный на ARPDAU. В нашем случае ARPDAU равняется $83.7:<\/p>\n
select cumulative_lt as LT,\r\n           cumulative_lt * 83.7 as LTV\r\nfrom lt<\/code><\/pre>
Наконец, построим график LTV на 180 дней:<\/p>\n
\n\"\"\n<\/div>\n
Весь запрос:<\/p>\n
--общее количество юзеров в когорте\r\nwith cohort as (\r\n    select count(*) as total_users_of_cohort\r\n    from users\r\n    where date(registration) between date '2021-03-01' and date '2021-03-30'\r\n),\r\n--количество активных юзеров на 1ый день, 2ой, 3ий и тд. из когорты\r\nactive_users as (\r\n    select date_part('day', activity.date - users.registration) as activity_day, \r\n               count(distinct users.id) as active_users_of_day\r\n    from activity\r\n    join users on activity.user_id = users.id\r\n    where date(registration) between date '2021-03-01' and date '2021-03-30' \r\n    group by 1\r\n    having date_part('day', activity.date - users.registration) between 1 and 90 --берем только первые 90 дней, остальные дни предсказываем.\r\n),\r\n--рассчитываем коэффициенты регрессии\r\ncoef as (\r\n    select exp(regr_intercept(ln(activity), ln(activity_day))) as a, \r\n                regr_slope(ln(activity), ln(activity_day)) as b\r\n    from(\r\n                select activity_day,\r\n                            active_users_of_day::real \/ total_users_of_cohort as activity\r\n                from active_users \r\n                cross join cohort order by activity_day \r\n            )\r\n),\r\nlt as(\r\n    select generate_series as activity_day,\r\n               active_users_of_day::real\/total_users_of_cohort as real_data,\r\n               a*power(generate_series,b) as pred_data, \t \r\n               sum(a*power(generate_series,b)) over(order by generate_series) as cumulative_lt\r\n    from generate_series(1,180,1)\r\n    cross join coef\r\n    join active_users on generate_series = activity_day::int\r\n),\r\nselect cumulative_lt as LT,\r\n            cumulative_lt * 83.7 as LTV\r\nfrom lt<\/code><\/pre>",
            "date_published": "2021-08-16T09:32:37+03:00",
            "date_modified": "2021-08-16T11:03:19+03:00",
            "image": "http:\/\/test.leftjoin.ru\/pictures\/chart.png",
            "_date_published_rfc2822": "Mon, 16 Aug 2021 09:32:37 +0300",
            "_rss_guid_is_permalink": "false",
            "_rss_guid": "115",
            "_e2_data": {
                "is_favourite": false,
                "links_required": [
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css"
                ],
                "og_images": [
                    "http:\/\/test.leftjoin.ru\/pictures\/chart.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/--2021-08-13--15.55.13.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/chart-2.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/--2021-08-13--15.58.19.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/chart-4.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/chart-5.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/--2021-08-13--15.54.53.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/chart-6.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/chart-10.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/chart-11.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/chart-12.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/--2021-08-13--15.30.59.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/--2021-08-13--15.51.05.png"
                ]
            }
        }
    ],
    "_e2_version": 3365,
    "_e2_ua_string": "E2 (v3365; Aegea)"
}