{"id":11532,"date":"2026-09-23T03:56:28","date_gmt":"2026-09-23T03:56:28","guid":{"rendered":"https:\/\/otus.ru\/journal\/?p=11532"},"modified":"2026-09-23T03:58:29","modified_gmt":"2026-09-23T03:58:29","slug":"kogortnyj-analiz-ltv-i-rfm-v-sql","status":"publish","type":"post","link":"https:\/\/otus.ru\/journal\/kogortnyj-analiz-ltv-i-rfm-v-sql\/","title":{"rendered":"\u041a\u043e\u0433\u043e\u0440\u0442\u043d\u044b\u0439 \u0430\u043d\u0430\u043b\u0438\u0437, LTV \u0438 RFM \u0432 SQL: \u0437\u0430\u043f\u0440\u043e\u0441\u044b \u043d\u0430 \u043e\u0434\u043d\u043e\u0439 \u0442\u0430\u0431\u043b\u0438\u0446\u0435 \u0437\u0430\u043a\u0430\u0437\u043e\u0432"},"content":{"rendered":"

\u041a\u043e\u0433\u043e\u0440\u0442\u043d\u044b\u0439 \u0430\u043d\u0430\u043b\u0438\u0437<\/strong> — \u044d\u0442\u043e \u0441\u0440\u0430\u0432\u043d\u0435\u043d\u0438\u0435 \u0433\u0440\u0443\u043f\u043f \u043a\u043b\u0438\u0435\u043d\u0442\u043e\u0432 (\u043a\u043e\u0433\u043e\u0440\u0442), \u043e\u0431\u044a\u0435\u0434\u0438\u043d\u0435\u043d\u043d\u044b\u0445 \u043c\u0435\u0441\u044f\u0446\u0435\u043c \u043f\u0435\u0440\u0432\u043e\u0439 \u043f\u043e\u043a\u0443\u043f\u043a\u0438: \u043a\u0430\u043a \u0447\u0430\u0441\u0442\u043e \u043e\u043d\u0438 \u0432\u043e\u0437\u0432\u0440\u0430\u0449\u0430\u044e\u0442\u0441\u044f \u0438 \u0441\u043a\u043e\u043b\u044c\u043a\u043e \u043f\u0440\u0438\u043d\u043e\u0441\u044f\u0442 \u0432 \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0438\u0435 \u043f\u0435\u0440\u0438\u043e\u0434\u044b. LTV<\/strong> (lifetime value) — \u043d\u0430\u043a\u043e\u043f\u043b\u0435\u043d\u043d\u0430\u044f \u0432\u044b\u0440\u0443\u0447\u043a\u0430 \u0438\u043b\u0438 \u043c\u0430\u0440\u0436\u0430 \u043d\u0430 \u043e\u0434\u043d\u043e\u0433\u043e \u043a\u043b\u0438\u0435\u043d\u0442\u0430 \u043a\u043e\u0433\u043e\u0440\u0442\u044b \u0437\u0430 \u0432\u044b\u0431\u0440\u0430\u043d\u043d\u044b\u0439 \u0433\u043e\u0440\u0438\u0437\u043e\u043d\u0442. RFM<\/strong> — \u0434\u0440\u0443\u0433\u043e\u0439 \u0441\u0440\u0435\u0437: \u0441\u0435\u0433\u043c\u0435\u043d\u0442\u0430\u0446\u0438\u044f \u043e\u0442\u0434\u0435\u043b\u044c\u043d\u044b\u0445 \u043a\u043b\u0438\u0435\u043d\u0442\u043e\u0432 \u043f\u043e \u0434\u0430\u0432\u043d\u043e\u0441\u0442\u0438 \u043f\u043e\u0441\u043b\u0435\u0434\u043d\u0435\u0439 \u043f\u043e\u043a\u0443\u043f\u043a\u0438 (Recency), \u0447\u0438\u0441\u043b\u0443 \u043f\u043e\u043a\u0443\u043f\u043e\u043a (Frequency) \u0438 \u0441\u0443\u043c\u043c\u0435 (Monetary).<\/p>\n

\n

\u0421\u043e\u0434\u0435\u0440\u0436\u0430\u043d\u0438\u0435<\/strong><\/p>\n

    \n
  1. \u0423\u0447\u0435\u0431\u043d\u0430\u044f \u0442\u0430\u0431\u043b\u0438\u0446\u0430 orders<\/a><\/li>\n
  2. \u0428\u0430\u0433 1. \u041a\u043e\u0433\u043e\u0440\u0442\u0430 \u0438 \u0441\u043c\u0435\u0449\u0435\u043d\u0438\u0435 \u0432 \u043c\u0435\u0441\u044f\u0446\u0430\u0445<\/a><\/li>\n
  3. \u0428\u0430\u0433 2. Retention \u043f\u043e \u043a\u043e\u0433\u043e\u0440\u0442\u0430\u043c<\/a><\/li>\n
  4. \u0428\u0430\u0433 3. LTV \u043f\u043e \u043a\u043e\u0433\u043e\u0440\u0442\u0430\u043c<\/a><\/li>\n
  5. \u0428\u0430\u0433 4. RFM-\u0441\u0435\u0433\u043c\u0435\u043d\u0442\u0430\u0446\u0438\u044f<\/a><\/li>\n
  6. \u0424\u0443\u043d\u043a\u0446\u0438\u0438 \u0434\u0430\u0442 \u0432 \u0434\u0440\u0443\u0433\u0438\u0445 \u0434\u0438\u0430\u043b\u0435\u043a\u0442\u0430\u0445<\/a><\/li>\n
  7. \u0412\u044b\u0432\u043e\u0434\u044b<\/a><\/li>\n
  8. \u0413\u0434\u0435 \u043f\u0440\u0438\u043c\u0435\u043d\u044f\u0435\u0442\u0441\u044f \/ \u0441\u0432\u044f\u0437\u044c \u0441 \u043f\u0440\u0430\u043a\u0442\u0438\u043a\u043e\u0439<\/a><\/li>\n
  9. FAQ<\/a><\/li>\n<\/ol>\n<\/div>\n

    \u0412\u0441\u0435 \u0437\u0430\u043f\u0440\u043e\u0441\u044b — \u043d\u0430 \u043e\u0434\u043d\u043e\u0439 \u0443\u0447\u0435\u0431\u043d\u043e\u0439 \u0442\u0430\u0431\u043b\u0438\u0446\u0435 orders<\/code> \u0432 PostgreSQL (\u0432\u044b\u0432\u043e\u0434\u044b \u043f\u0440\u043e\u0432\u0435\u0440\u0435\u043d\u044b \u043f\u0440\u043e\u0433\u043e\u043d\u043e\u043c \u0432 PostgreSQL 18). \u0417\u0430\u043c\u0435\u043d\u044b \u0444\u0443\u043d\u043a\u0446\u0438\u0439 \u0434\u0430\u0442 \u0434\u043b\u044f SQLite, MySQL \u0438 SQL Server — \u0432 \u0442\u0430\u0431\u043b\u0438\u0446\u0435 \u0432 \u043a\u043e\u043d\u0446\u0435.<\/p>\n

    \u0423\u0447\u0435\u0431\u043d\u0430\u044f \u0442\u0430\u0431\u043b\u0438\u0446\u0430 orders<\/h2>\n

    \u0428\u0435\u0441\u0442\u044c \u043a\u043b\u0438\u0435\u043d\u0442\u043e\u0432, 11 \u0437\u0430\u043a\u0430\u0437\u043e\u0432; \u0434\u0430\u0442\u0430 \u0430\u043d\u0430\u043b\u0438\u0437\u0430 — 1 \u0430\u043f\u0440\u0435\u043b\u044f 2026.<\/p>\n

    CREATE<\/span> <\/span>TABLE<\/span> <\/span>orders<\/span> (<\/span>\n    <\/span>order_id<\/span>   <\/span>INTEGER<\/span> <\/span>PRIMARY<\/span> <\/span>KEY<\/span>,<\/span>\n    <\/span>user_id<\/span>    <\/span>INTEGER<\/span> <\/span>NOT<\/span> <\/span>NULL<\/span>,<\/span>\n    <\/span>order_date<\/span> <\/span>DATE<\/span>    <\/span>NOT<\/span> <\/span>NULL<\/span>,<\/span>\n    <\/span>amount<\/span>     <\/span>NUMERIC<\/span>(<\/span>10<\/span>, <\/span>2<\/span>) <\/span>NOT<\/span> <\/span>NULL<\/span> <\/span>CHECK<\/span> (<\/span>amount<\/span> <\/span>><\/span> <\/span>0<\/span>)<\/span>\n);<\/span>\n\nINSERT<\/span> <\/span>INTO<\/span> <\/span>orders<\/span> (<\/span>order_id<\/span>, <\/span>user_id<\/span>, <\/span>order_date<\/span>, <\/span>amount<\/span>) <\/span>VALUES<\/span>\n    (<\/span>1<\/span>,  <\/span>1<\/span>, <\/span>'2026-01-10'<\/span>, <\/span>200<\/span>),<\/span>\n    (<\/span>2<\/span>,  <\/span>1<\/span>, <\/span>'2026-02-05'<\/span>, <\/span>150<\/span>),<\/span>\n    (<\/span>3<\/span>,  <\/span>1<\/span>, <\/span>'2026-03-20'<\/span>, <\/span>100<\/span>),<\/span>\n    (<\/span>4<\/span>,  <\/span>2<\/span>, <\/span>'2026-01-15'<\/span>, <\/span>300<\/span>),<\/span>\n    (<\/span>5<\/span>,  <\/span>3<\/span>, <\/span>'2026-01-28'<\/span>, <\/span>120<\/span>),<\/span>\n    (<\/span>6<\/span>,  <\/span>3<\/span>, <\/span>'2026-03-02'<\/span>,  <\/span>80<\/span>),<\/span>\n    (<\/span>7<\/span>,  <\/span>4<\/span>, <\/span>'2026-02-03'<\/span>, <\/span>500<\/span>),<\/span>\n    (<\/span>8<\/span>,  <\/span>4<\/span>, <\/span>'2026-02-25'<\/span>,  <\/span>90<\/span>),<\/span>\n    (<\/span>9<\/span>,  <\/span>4<\/span>, <\/span>'2026-03-15'<\/span>,  <\/span>60<\/span>),<\/span>\n    (<\/span>10<\/span>, <\/span>5<\/span>, <\/span>'2026-02-18'<\/span>,  <\/span>40<\/span>),<\/span>\n    (<\/span>11<\/span>, <\/span>6<\/span>, <\/span>'2026-03-05'<\/span>, <\/span>250<\/span>);<\/span><\/code><\/pre>\n
    CHECK (amount > 0)<\/code> \u043d\u0435 \u043f\u0443\u0441\u043a\u0430\u0435\u0442 \u0432 \u0442\u0430\u0431\u043b\u0438\u0446\u0443 \u0432\u043e\u0437\u0432\u0440\u0430\u0442\u044b; \u0432 \u0440\u0435\u0430\u043b\u044c\u043d\u044b\u0445 \u0434\u0430\u043d\u043d\u044b\u0445 \u0438\u0445 \u0432\u044b\u0447\u0438\u0442\u0430\u044e\u0442 \u044f\u0432\u043d\u043e \u0434\u043e \u0440\u0430\u0441\u0447\u0435\u0442\u0430 LTV.<\/p>\n
    \u0428\u0430\u0433 1. \u041a\u043e\u0433\u043e\u0440\u0442\u0430 \u0438 \u0441\u043c\u0435\u0449\u0435\u043d\u0438\u0435 \u0432 \u043c\u0435\u0441\u044f\u0446\u0430\u0445<\/h2>\n
    \u041a\u043e\u0433\u043e\u0440\u0442\u0430 \u043a\u043b\u0438\u0435\u043d\u0442\u0430 — \u043c\u0435\u0441\u044f\u0446 \u0435\u0433\u043e \u043f\u0435\u0440\u0432\u043e\u0439 \u043f\u043e\u043a\u0443\u043f\u043a\u0438. \u0414\u043b\u044f \u043a\u0430\u0436\u0434\u043e\u0433\u043e \u0437\u0430\u043a\u0430\u0437\u0430 \u043d\u0443\u0436\u043d\u044b cohort_month<\/code> \u0438 month_offset<\/code> — \u0447\u0438\u0441\u043b\u043e \u043a\u0430\u043b\u0435\u043d\u0434\u0430\u0440\u043d\u044b\u0445 \u043c\u0435\u0441\u044f\u0446\u0435\u0432 \u043e\u0442 \u043c\u0435\u0441\u044f\u0446\u0430 \u043f\u0435\u0440\u0432\u043e\u0439 \u043f\u043e\u043a\u0443\u043f\u043a\u0438. \u041e\u0444\u043e\u0440\u043c\u0438\u043c \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0438\u0435\u043c.<\/p>\n
    CREATE<\/span> <\/span>VIEW<\/span> <\/span>orders_with_cohort<\/span> <\/span>AS<\/span>\nWITH<\/span> <\/span>first_order<\/span> <\/span>AS<\/span> (<\/span>\n    <\/span>SELECT<\/span> <\/span>user_id<\/span>, <\/span>MIN<\/span>(<\/span>order_date<\/span>) <\/span>AS<\/span> <\/span>first_date<\/span>\n    <\/span>FROM<\/span> <\/span>orders<\/span>\n    <\/span>GROUP<\/span> <\/span>BY<\/span> <\/span>user_id<\/span>\n)<\/span>\nSELECT<\/span> <\/span>o<\/span>.<\/span>user_id<\/span>,<\/span>\n       <\/span>o<\/span>.<\/span>amount<\/span>,<\/span>\n       <\/span>DATE_TRUNC<\/span>(<\/span>'month'<\/span>, <\/span>f<\/span>.<\/span>first_date<\/span>)::<\/span>date<\/span> <\/span>AS<\/span> <\/span>cohort_month<\/span>,<\/span>\n       ((<\/span>EXTRACT<\/span>(<\/span>YEAR<\/span> <\/span>FROM<\/span> <\/span>o<\/span>.<\/span>order_date<\/span>) <\/span>-<\/span> <\/span>EXTRACT<\/span>(<\/span>YEAR<\/span> <\/span>FROM<\/span> <\/span>f<\/span>.<\/span>first_date<\/span>)) <\/span>*<\/span> <\/span>12<\/span>\n      <\/span>+<\/span> (<\/span>EXTRACT<\/span>(<\/span>MONTH<\/span> <\/span>FROM<\/span> <\/span>o<\/span>.<\/span>order_date<\/span>) <\/span>-<\/span> <\/span>EXTRACT<\/span>(<\/span>MONTH<\/span> <\/span>FROM<\/span> <\/span>f<\/span>.<\/span>first_date<\/span>)))::<\/span>int<\/span> <\/span>AS<\/span> <\/span>month_offset<\/span>\nFROM<\/span> <\/span>orders<\/span> <\/span>o<\/span>\nJOIN<\/span> <\/span>first_order<\/span> <\/span>f<\/span> <\/span>ON<\/span> <\/span>f<\/span>.<\/span>user_id<\/span> <\/span>=<\/span> <\/span>o<\/span>.<\/span>user_id<\/span>;<\/span><\/code><\/pre>\n
    \u0421\u043c\u0435\u0449\u0435\u043d\u0438\u0435 \u0441\u0447\u0438\u0442\u0430\u0435\u0442\u0441\u044f \u043f\u043e \u043a\u0430\u043b\u0435\u043d\u0434\u0430\u0440\u043d\u044b\u043c \u043c\u0435\u0441\u044f\u0446\u0430\u043c: \u0437\u0430\u043a\u0430\u0437 28 \u044f\u043d\u0432\u0430\u0440\u044f \u0438 \u0437\u0430\u043a\u0430\u0437 2 \u043c\u0430\u0440\u0442\u0430 \u0434\u0430\u044e\u0442 month_offset = 2<\/code>, \u0445\u043e\u0442\u044f \u043f\u0440\u043e\u0448\u043b\u043e 33 \u0434\u043d\u044f. \u0424\u0443\u043d\u043a\u0446\u0438\u044f AGE<\/code> \u0441\u0447\u0438\u0442\u0430\u0435\u0442 \u0438\u043d\u0430\u0447\u0435: \u0432\u044b\u0447\u0438\u0442\u0430\u0435\u0442 \u0434\u0430\u0442\u044b \u043f\u043e\u043b\u0435 \u0437\u0430 \u043f\u043e\u043b\u0435\u043c \u0438 \u0432\u043e\u0437\u0432\u0440\u0430\u0449\u0430\u0435\u0442 \u00ab\u0441\u0438\u043c\u0432\u043e\u043b\u044c\u043d\u044b\u0439\u00bb \u0438\u043d\u0442\u0435\u0440\u0432\u0430\u043b \u0438\u0437 \u043c\u0435\u0441\u044f\u0446\u0435\u0432 \u0438 \u0434\u043d\u0435\u0439:<\/p>\n
    SELECT<\/span> <\/span>AGE<\/span>(<\/span>DATE<\/span> <\/span>'2026-03-02'<\/span>, <\/span>DATE<\/span> <\/span>'2026-01-28'<\/span>) <\/span>AS<\/span> <\/span>age_interval<\/span>;<\/span><\/code><\/pre>\n
     age_interval\n--------------\n 1 mon 5 days\n<\/code><\/pre>\n
    \u0417\u0434\u0435\u0441\u044c 1 \u043c\u0435\u0441\u044f\u0446, \u0430 \u043d\u0435 2. \u0414\u043d\u0438 \u0442\u043e\u0436\u0435 \u043d\u0435 \u00ab\u0444\u0430\u043a\u0442\u0438\u0447\u0435\u0441\u043a\u0438\u0435\u00bb: \u043f\u0440\u0438 \u0437\u0430\u0435\u043c\u0435 \u043c\u0435\u0441\u044f\u0446\u0430 PostgreSQL \u0431\u0435\u0440\u0435\u0442 \u0434\u043b\u0438\u043d\u0443 \u043c\u0435\u0441\u044f\u0446\u0430 \u0431\u043e\u043b\u0435\u0435 \u0440\u0430\u043d\u043d\u0435\u0439 \u0434\u0430\u0442\u044b (\u044f\u043d\u0432\u0430\u0440\u044c, 31 \u0434\u0435\u043d\u044c), \u043f\u043e\u044d\u0442\u043e\u043c\u0443 5 \u0434\u043d\u0435\u0439, \u0445\u043e\u0442\u044f \u043e\u0442 28 \u0444\u0435\u0432\u0440\u0430\u043b\u044f \u0434\u043e 2 \u043c\u0430\u0440\u0442\u0430 \u0432\u0441\u0435\u0433\u043e 2 \u0434\u043d\u044f. \u0414\u043b\u044f \u043a\u043e\u0433\u043e\u0440\u0442\u043d\u043e\u0439 \u0442\u0430\u0431\u043b\u0438\u0446\u044b \u043d\u0443\u0436\u043d\u0430 \u043a\u0430\u043b\u0435\u043d\u0434\u0430\u0440\u043d\u0430\u044f \u0440\u0430\u0437\u043d\u0438\u0446\u0430, \u0438\u043d\u0430\u0447\u0435 \u043c\u0430\u0440\u0442\u043e\u0432\u0441\u043a\u0438\u0435 \u0437\u0430\u043a\u0430\u0437\u044b \u043a\u043b\u0438\u0435\u043d\u0442\u043e\u0432 \u0438\u0437 \u043d\u0430\u0447\u0430\u043b\u0430 \u0438 \u043a\u043e\u043d\u0446\u0430 \u044f\u043d\u0432\u0430\u0440\u044f \u043f\u043e\u043f\u0430\u0434\u0443\u0442 \u0432 \u0440\u0430\u0437\u043d\u044b\u0435 \u0441\u0442\u043e\u043b\u0431\u0446\u044b.<\/p>\n
    \u0428\u0430\u0433 2. Retention \u043f\u043e \u043a\u043e\u0433\u043e\u0440\u0442\u0430\u043c<\/h2>\n
    Retention \u043c\u0435\u0441\u044f\u0446\u0430 N<\/strong> \u0437\u0434\u0435\u0441\u044c — \u0434\u043e\u043b\u044f \u043a\u043b\u0438\u0435\u043d\u0442\u043e\u0432 \u043a\u043e\u0433\u043e\u0440\u0442\u044b, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u0441\u0434\u0435\u043b\u0430\u043b\u0438 \u0445\u043e\u0442\u044f \u0431\u044b \u043e\u0434\u043d\u0443 \u043f\u043e\u043a\u0443\u043f\u043a\u0443 \u0432 \u043c\u0435\u0441\u044f\u0446\u0435 N \u043f\u043e\u0441\u043b\u0435 \u043f\u0435\u0440\u0432\u043e\u0433\u043e. \u042d\u0442\u043e \u043d\u0435 \u00ab\u0434\u043e\u043b\u044f \u043e\u0441\u0442\u0430\u0432\u0448\u0438\u0445\u0441\u044f \u0434\u043e \u043c\u0435\u0441\u044f\u0446\u0430 N\u00bb: \u043a\u043b\u0438\u0435\u043d\u0442 \u043c\u043e\u0436\u0435\u0442 \u043f\u0440\u043e\u043f\u0443\u0441\u0442\u0438\u0442\u044c \u043c\u0435\u0441\u044f\u0446 \u0438 \u0432\u0435\u0440\u043d\u0443\u0442\u044c\u0441\u044f.<\/p>\n
    WITH<\/span> <\/span>cohort_size<\/span> <\/span>AS<\/span> (<\/span>\n    <\/span>SELECT<\/span> <\/span>cohort_month<\/span>, <\/span>COUNT<\/span>(<\/span>DISTINCT<\/span> <\/span>user_id<\/span>) <\/span>AS<\/span> <\/span>users<\/span>\n    <\/span>FROM<\/span> <\/span>orders_with_cohort<\/span>\n    <\/span>WHERE<\/span> <\/span>month_offset<\/span> <\/span>=<\/span> <\/span>0<\/span>\n    <\/span>GROUP<\/span> <\/span>BY<\/span> <\/span>cohort_month<\/span>\n)<\/span>\nSELECT<\/span> <\/span>a<\/span>.<\/span>cohort_month<\/span>,<\/span>\n       <\/span>a<\/span>.<\/span>month_offset<\/span>,<\/span>\n       <\/span>COUNT<\/span>(<\/span>DISTINCT<\/span> <\/span>a<\/span>.<\/span>user_id<\/span>) <\/span>AS<\/span> <\/span>active_users<\/span>,<\/span>\n       <\/span>ROUND<\/span>(<\/span>100<\/span>.<\/span>0<\/span> <\/span>*<\/span> <\/span>COUNT<\/span>(<\/span>DISTINCT<\/span> <\/span>a<\/span>.<\/span>user_id<\/span>) <\/span>\/<\/span> <\/span>c<\/span>.<\/span>users<\/span>, <\/span>1<\/span>) <\/span>AS<\/span> <\/span>retention_pct<\/span>\nFROM<\/span> <\/span>orders_with_cohort<\/span> <\/span>a<\/span>\nJOIN<\/span> <\/span>cohort_size<\/span> <\/span>c<\/span> <\/span>ON<\/span> <\/span>c<\/span>.<\/span>cohort_month<\/span> <\/span>=<\/span> <\/span>a<\/span>.<\/span>cohort_month<\/span>\nGROUP<\/span> <\/span>BY<\/span> <\/span>a<\/span>.<\/span>cohort_month<\/span>, <\/span>a<\/span>.<\/span>month_offset<\/span>, <\/span>c<\/span>.<\/span>users<\/span>\nORDER<\/span> <\/span>BY<\/span> <\/span>a<\/span>.<\/span>cohort_month<\/span>, <\/span>a<\/span>.<\/span>month_offset<\/span>;<\/span><\/code><\/pre>\n
     cohort_month | month_offset | active_users | retention_pct\n--------------+--------------+--------------+---------------\n 2026-01-01   |            0 |            3 |         100.0\n 2026-01-01   |            1 |            1 |          33.3\n 2026-01-01   |            2 |            2 |          66.7\n 2026-02-01   |            0 |            2 |         100.0\n 2026-02-01   |            1 |            1 |          50.0\n 2026-03-01   |            0 |            1 |         100.0\n<\/code><\/pre>\n
    \u0423 \u044f\u043d\u0432\u0430\u0440\u0441\u043a\u043e\u0439 \u043a\u043e\u0433\u043e\u0440\u0442\u044b \u043c\u0435\u0441\u044f\u0446 2 \u0432\u044b\u0448\u0435 \u043c\u0435\u0441\u044f\u0446\u0430 1: \u043a\u043b\u0438\u0435\u043d\u0442 3 \u043f\u0440\u043e\u043f\u0443\u0441\u0442\u0438\u043b \u0444\u0435\u0432\u0440\u0430\u043b\u044c \u0438 \u0432\u0435\u0440\u043d\u0443\u043b\u0441\u044f \u0432 \u043c\u0430\u0440\u0442\u0435. \u0423 \u043c\u0430\u0440\u0442\u043e\u0432\u0441\u043a\u043e\u0439 \u043a\u043e\u0433\u043e\u0440\u0442\u044b \u0442\u043e\u043b\u044c\u043a\u043e \u0441\u0442\u043e\u043b\u0431\u0435\u0446 0: \u043f\u0443\u0441\u0442\u0430\u044f \u044f\u0447\u0435\u0439\u043a\u0430 \u0437\u0434\u0435\u0441\u044c — \u00ab\u043c\u0435\u0441\u044f\u0446 \u0435\u0449\u0435 \u043d\u0435 \u043d\u0430\u0441\u0442\u0443\u043f\u0438\u043b\u00bb, \u0430 \u043d\u0435 \u00ab0% \u0443\u0434\u0435\u0440\u0436\u0430\u043d\u0438\u044f\u00bb.<\/p>\n
    \u0427\u0430\u0441\u0442\u0430\u044f \u043e\u0448\u0438\u0431\u043a\u0430: \u0440\u0430\u0437\u043c\u0435\u0440 \u043a\u043e\u0433\u043e\u0440\u0442\u044b \u0447\u0435\u0440\u0435\u0437 COUNT(*)<\/h3>\n
    \u0420\u0430\u0437\u043c\u0435\u0440 \u043a\u043e\u0433\u043e\u0440\u0442\u044b \u043f\u043e \u0447\u0438\u0441\u043b\u0443 \u0437\u0430\u043a\u0430\u0437\u043e\u0432, \u0430 \u043d\u0435 \u043a\u043b\u0438\u0435\u043d\u0442\u043e\u0432:<\/p>\n
    SELECT<\/span> <\/span>cohort_month<\/span>, <\/span>COUNT<\/span>(<\/span>*<\/span>) <\/span>AS<\/span> <\/span>cohort_size<\/span>\nFROM<\/span> <\/span>orders_with_cohort<\/span>\nWHERE<\/span> <\/span>month_offset<\/span> <\/span>=<\/span> <\/span>0<\/span>\nGROUP<\/span> <\/span>BY<\/span> <\/span>cohort_month<\/span>\nORDER<\/span> <\/span>BY<\/span> <\/span>cohort_month<\/span>;<\/span><\/code><\/pre>\n
     cohort_month | cohort_size\n--------------+-------------\n 2026-01-01   |           3\n 2026-02-01   |           3\n 2026-03-01   |           1\n<\/code><\/pre>\n
    \u0412 \u0444\u0435\u0432\u0440\u0430\u043b\u044c\u0441\u043a\u043e\u0439 \u043a\u043e\u0433\u043e\u0440\u0442\u0435 \u0434\u0432\u0430 \u043a\u043b\u0438\u0435\u043d\u0442\u0430, \u043d\u043e \u043a\u043b\u0438\u0435\u043d\u0442 4 \u043a\u0443\u043f\u0438\u043b \u0432 \u0444\u0435\u0432\u0440\u0430\u043b\u0435 \u0434\u0432\u0430\u0436\u0434\u044b — \u0437\u043d\u0430\u043c\u0435\u043d\u0430\u0442\u0435\u043b\u044c \u0441\u0442\u0430\u043b 3, retention \u043c\u0435\u0441\u044f\u0446\u0430 1 \u0437\u0430\u043d\u0438\u0437\u0438\u0442\u0441\u044f \u0441 50% \u0434\u043e 33,3%. \u0418\u0441\u043f\u0440\u0430\u0432\u043b\u0435\u043d\u0438\u0435 — COUNT(DISTINCT user_id)<\/code>, \u043a\u0430\u043a \u0432\u044b\u0448\u0435: \u0444\u0435\u0432\u0440\u0430\u043b\u044c \u0434\u0430\u0435\u0442 2.<\/p>\n
    \u0428\u0430\u0433 3. LTV \u043f\u043e \u043a\u043e\u0433\u043e\u0440\u0442\u0430\u043c<\/h2>\n
    \u0417\u0434\u0435\u0441\u044c LTV<\/strong> — \u043a\u0443\u043c\u0443\u043b\u044f\u0442\u0438\u0432\u043d\u0430\u044f \u0432\u044b\u0440\u0443\u0447\u043a\u0430 \u043a\u043e\u0433\u043e\u0440\u0442\u044b \u043a \u043c\u0435\u0441\u044f\u0446\u0443 N, \u0434\u0435\u043b\u0435\u043d\u043d\u0430\u044f \u043d\u0430 \u0447\u0438\u0441\u043b\u043e \u043a\u043b\u0438\u0435\u043d\u0442\u043e\u0432 \u043a\u043e\u0433\u043e\u0440\u0442\u044b. \u042d\u0442\u043e \u0443\u043f\u0440\u043e\u0449\u0435\u043d\u043d\u043e\u0435 \u043e\u043f\u0440\u0435\u0434\u0435\u043b\u0435\u043d\u0438\u0435 \u0441 \u0434\u0432\u0443\u043c\u044f \u0433\u0440\u0430\u043d\u0438\u0446\u0430\u043c\u0438:<\/p>\n