{
    "version": "https:\/\/jsonfeed.org\/version\/1",
    "title": "Блог об аналитике, визуализации данных, data science и BI, заметки с тегом: retention",
    "home_page_url": "http:\/\/test.leftjoin.ru\/tags\/retention\/",
    "feed_url": "http:\/\/test.leftjoin.ru\/tags\/retention\/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": "66",
            "url": "http:\/\/test.leftjoin.ru\/all\/retention-difference\/",
            "title": "Разница между Retention на основе 24-часовых окон и календарных дней",
            "content_html": "<p>Вчера в Telegram мне написал читатель блога:<\/p>\n<blockquote>\n<p><i>Допустим, сегодня понедельник, я сделал 187 установок, и хочу посмотреть Retention первого дня, в какой день недели это можно сделать?<\/i><\/p>\n<\/blockquote>\n<p>Речь идет о <a href=\"http:\/\/test.leftjoin.ru\/all\/retention-rate\/\">посте про Retention<\/a>. Дам некоторые пояснения на этот счёт. Retention можно считать как на основе календарных дней, так и на основе 24-часовых окон. Retention нулевого дня в данном случае будет понедельник, а первого дня — вторник. Но здесь есть небольшая загвоздка.<\/p>\n<p>К примеру, если вы начали продвижение в понедельник 5 октября в 23:59, то все установки этой минуты через пару минут будут иметь Retention первого дня. Это проблема календарного исчисления. Для решения этой проблемы некоторые аналитики измеряют Retention не только по календарю, но и по 24-часовым окнам.<\/p>\n<p>Приложим эту идею к вышеописанному случаю:<\/p>\n<ul>\n<li>Retention нулевых суток в таком случае — все инсталлы с 5 октября 23:59 по 6 октября 23:59<\/li>\n<li>Retention первого дня: с 6 октября 23:59 по 7 октября 23:59<\/li>\n<li>И так далее со сдвигом 24-часового окна.<\/li>\n<\/ul>\n<h2>Как рассчитать Retention на основе 24-часовых окон с использованием SQL?<\/h2>\n<p>Вспомним <a href=\"http:\/\/test.leftjoin.ru\/all\/retention-rate\/\">запрос из предыдущего поста<\/a> в блоге. Там мы считали разницу в днях между датой установки и датой активности пользователя. Модифицируем запрос так, чтобы активность считалась в 24-часовых окнах: заменим расчёт <i>datediff<\/i> на расчёт 24-часовых окон, обновив строки, выделенные жирным<\/p>\n<pre class=\"e2-text-code\"><code class=\"SQL\">\r\nSELECT from_unixtime(user.installed_at, \"yyyy-MM-dd\") AS reg_date,\r\n   <b>floor((cast(cs.created_at as int)-cast(installed_at as int))\/(24*3600)) as date_diff,<\/b>\r\n   ndv(user.id) AS ret_base\r\n   FROM USER\r\n   LEFT JOIN client_session cs ON cs.user_id=user.id\r\n   WHERE 1=1\r\n     <b>AND floor((cast(cs.created_at as int)-cast(installed_at as int))\/(24*3600)) between 0 and 30<\/b>\r\n     AND from_unixtime(user.installed_at)>=date_add(now(), -60)\r\n     AND from_unixtime(user.installed_at)<=date_add(now(), -31)\r\n   GROUP BY 1,2\r\n<\/pre>\n<\/code><p>Обновленный запрос:<\/p>\n<pre class=\"e2-text-code\"><code>SELECT \r\n       cohort.date_diff AS day_difference,\r\n       avg(reg.users) AS cohort_size,\r\n       avg(cohort.ret_base) AS retention_base,\r\n       avg(cohort.ret_base)\/avg(reg.users)*100 AS retention_rate\r\nFROM\r\n  (SELECT from_unixtime(user.installed_at, &quot;yyyy-MM-dd&quot;) AS reg_date,\r\n          ndv(user.id) AS users\r\n   FROM USER\r\n   WHERE from_unixtime(user.installed_at)&gt;=date_add(now(), -60)\r\n     AND from_unixtime(user.installed_at)&lt;=date_add(now(), -31)\r\n   GROUP BY 1) reg\r\nLEFT JOIN\r\n  (SELECT from_unixtime(user.installed_at, &quot;yyyy-MM-dd&quot;) AS reg_date,\r\n    floor((cast(cs.created_at as int)-cast(installed_at as int))\/(24*3600)) as date_diff,\r\n          ndv(user.id) AS ret_base\r\n   FROM USER\r\n   LEFT JOIN client_session cs ON cs.user_id=user.id\r\n    WHERE 1=1\r\n     AND floor((cast(cs.created_at as int)-cast(installed_at as int))\/(24*3600)) between 0 and 30\r\n     AND from_unixtime(user.installed_at)&gt;=date_add(now(), -60)\r\n     AND from_unixtime(user.installed_at)&lt;=date_add(now(), -31)\r\n   GROUP BY 1,2\r\n  ) cohort ON reg.reg_date=cohort.reg_date\r\n    GROUP BY 1        \r\n    ORDER BY 1<\/code><\/pre><h2>Результат:<\/h2>\n<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/newplot-(57).png\" width=\"1170\" height=\"400\" alt=\"\" \/>\n<\/div>\n<p>Сравним с результатам, полученным предыдущим скриптом:<\/p>\n<div class=\"e2-text-picture\">\n<img src=\"http:\/\/test.leftjoin.ru\/pictures\/newplot-(56).png\" width=\"1170\" height=\"400\" alt=\"\" \/>\n<\/div>\n<p>Заметно, что в первые дни Retention, посчитанный методом 24-часового окна, несколько ниже.<\/p>\n",
            "date_published": "2020-10-08T13:55:14+03:00",
            "date_modified": "2020-10-09T11:51:00+03:00",
            "image": "http:\/\/test.leftjoin.ru\/pictures\/newplot-(57).png",
            "_date_published_rfc2822": "Thu, 08 Oct 2020 13:55:14 +0300",
            "_rss_guid_is_permalink": "false",
            "_rss_guid": "66",
            "_e2_data": {
                "is_favourite": false,
                "links_required": [
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css"
                ],
                "og_images": [
                    "http:\/\/test.leftjoin.ru\/pictures\/newplot-(57).png",
                    "http:\/\/test.leftjoin.ru\/pictures\/newplot-(56).png"
                ]
            }
        }
    ],
    "_e2_version": 3365,
    "_e2_ua_string": "E2 (v3365; Aegea)"
}