This is an advanced topic that assumes good, pre-existing knowledge of SQL and Cube.
Whether you’re selling groceries, financial services, or gym memberships,
successful recruitment of new customers is only truly successful if they return
to buy from you again. The metric that reflects this is called retention,
and the approach we use is customer retention analysis. Retention analysis
is typically done using cohort analysis.Cohort analysis is a technique to see how variables change over in different
groups with different starting conditions. Retention is a simplified one, where
the starting condition is usually the time of signup and the variable is
simply activity.It’s usually visualized as a cohort grid or retention curves.
Cohort retention analysis is pretty hard to do in SQL. We need to have the
user-date combination, which tells us about a user’s activity on that date,
including dates with no activity. To do this, we need to make a tricky join,
which gives us a dates list. Once we have it, we can “fill it” with users’
activities.The example below shows monthly cohort retention. The same technique can be used
for daily or weekly retention.
The SQL code in this guide is Postgres-compliant. The final SQL code may be
different depending on your database. Also, this technique requires at least 1
user to be active during the month, otherwise this month will not be included in
the months’ list.
cubes: - name: monthly_retention sql: | SELECT users.id as user_id, date_trunc('month', users.created_at) as signup_month, months_list.activity_month as activity_month, data.monthly_pageviews FROM users LEFT JOIN ( SELECT DISTINCT (date_trunc('month', pages.original_timestamp)) as activity_month FROM pages ) as months_list ON months_list.activity_month >= date_trunc('month', users.created_at) LEFT JOIN ( SELECT p.user_id, date_trunc('month', p.original_timestamp) as activity_month, COUNT(DISTINCT p.id) as monthly_pageviews FROM pages p GROUP BY 1,2 ) as data ON data.activity_month = months_list.activity_month AND data.user_id = users.id
cube(`monthly_retention`, { sql: `SELECT users.id as user_id, date_trunc('month', users.created_at) as signup_month, months_list.activity_month as activity_month, data.monthly_pageviews FROM users LEFT JOIN ( SELECT DISTINCT (date_trunc('month', pages.original_timestamp)) as activity_month FROM pages ) as months_list ON months_list.activity_month >= date_trunc('month', users.created_at) LEFT JOIN ( SELECT p.user_id, date_trunc('month', p.original_timestamp) as activity_month, COUNT(DISTINCT p.id) as monthly_pageviews FROM pages p GROUP BY 1,2 ) as data ON data.activity_month = months_list.activity_month AND data.user_id = users.id`})
The SQL above provides the base table for our retention cube. It would show
signup months and activity months with pageviews:
user_id
signup_month
activity_month
monthly_pageviews
1
1/18
1/18
10
1
1/18
2/18
5
1
1/18
3/18
0
2
2/18
2/18
12
2
2/18
3/18
0
3
3/18
3/18
5
Now we can calculate a total count of users and the total count of active users,
who has more than 0 page views, for every month. Based on these two measures we
can calculate monthly percentage_of_active.
To be able to build cohorts, we need to group by two dimensions: signup
date, which will define our cohorts, and months since signup, which will
show how the percentage of active users is changing.
cubes: - name: monthly_retention # ... dimensions: - name: months_since_signup sql: "DATEDIFF('month', signup_month, activity_month)" type: number - name: signup_date sql: "(signup_month AT TIME ZONE 'America/Los_Angeles')" type: time
cube(`monthly_retention`, { // ... dimensions: { months_since_signup: { sql: `DATEDIFF('month', ${CUBE}.signup_month, ${CUBE}.activity_month)`, type: `number` }, signup_date: { sql: `(signup_month AT TIME ZONE 'America/Los_Angeles')`, type: `time` } }})
Note, we are explicitly setting the signup_month timezone. date_trunc
returns UTC dates and not setting a correct timezone would lead to wrong results
due to time shift.
{"docsConfig":{"theme":"mint","$schema":"https://mintlify.com/docs.json","name":"Cube Documentation","colors":{"primary":"#5339CF","light":"#A5A3FF","dark":"#716EEE"},"logo":{"light":"https://mintcdn.com/cubed3/wCTntgEXVYYYnx4l/logo/light.svg?fit=max\u0026auto=format\u0026n=wCTntgEXVYYYnx4l\u0026q=85\u0026s=8bbf45ba64eac6116629064fac55aad7","dark":"https://mintcdn.com/cubed3/wCTntgEXVYYYnx4l/logo/dark.svg?fit=max\u0026auto=format\u0026n=wCTntgEXVYYYnx4l\u0026q=85\u0026s=d8fa230950c8c63b3c2f88d2768694d0"},"favicon":"https://mintcdn.com/cubed3/wCTntgEXVYYYnx4l/favicon.svg?fit=max\u0026auto=format\u0026n=wCTntgEXVYYYnx4l\u0026q=85\u0026s=3b0cb7aa770d2abd546ef19463fbba8c","navbar":{"links":[{"href":"https://cube.dev/demos","label":"Demos"},{"href":"https://cube.dev/changelog","label":"Changelog"},{"href":"https://slack.cube.dev","label":"Community"}],"primary":{"type":"button","label":"Get Started","href":"https://cubecloud.dev/auth/signup"}},"footer":{"socials":{"x":"https://x.com/the_cube_dev","github":"https://github.com/cube-js/cube","linkedin":"https://www.linkedin.com/company/cube-dev"},"links":[{"header":"Legal","items":[{"label":"Privacy Policy","href":"https://cube.dev/legal/privacy-policy"},{"label":"Cookie Preferences","href":"#cookie-preferences"}]}]},"seo":{"metatags":{"og:type":"website","og:site_name":"Cube Documentation"},"indexing":"navigable"},"icons":{"library":"tabler"},"styling":{"codeblocks":"dark","latex":false},"navigation":{"pages":[]}},"deployment":{"deploymentMetadataValue":{"subdomain":"cubed3","hasAudioNarration":false,"actualSubdomain":"cubed3","customDomains":["docs.cube.dev"],"gitSource":{"type":"github","owner":"cube-js","repo":"cube","deployBranch":"master","contentDirectory":"docs-mintlify","isPrivate":true,"mountPath":"","label":""},"entitlements":{"AI_CHAT":{"status":"ENABLED"},"WEB_MCP":{"status":"ENABLED"},"NARRATION":{"status":"ENABLED"}},"buildId":"6aff77fbb5f9972b85a20c09","clientVersion":"0.0.4227","searchFilterCounts":[{"language":"default","tag":"API","count":238},{"language":"default","tag":"Platform Endpoints","count":231},{"language":"default","tag":"Documentation","count":101},{"language":"default","tag":"Admin","count":95},{"language":"default","tag":"Data Modeling","count":65},{"language":"default","tag":"Recipes","count":47},{"language":"default","tag":"Topics","count":47},{"language":"default","tag":"Reference","count":44},{"language":"default","tag":"Connect to data","count":31},{"language":"default","tag":"Explore \u0026 Analyze","count":30}]},"assistantConfig":{"enableAskAiButton":false,"enableStarterQuestions":false,"enableContextualStarterQuestions":false,"enableAskAiSearchBar":true},"basePath":"","hasChatPermissions":true,"hasWebMcpPermissions":true,"toggles":[{"name":"kill-isr","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"custom-rbac-roles","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"agent-mintlify-slack","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"agent-integrations-settings","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"rbac-expansion","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"automations-pr-grouping","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"custom-prefix","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"analytics-v7","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"user-flows-analytics","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"no-publish-flow","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"workos-onboarding","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"snapshot-serving-live","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"stripTemperatureMiddleware","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"support-platform-pylon-zendesk","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"snapshot-serving","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"instant-rollback","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"granular-notifications-server","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"granular-notifications","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"automations-fable","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"fixed-ai-action-pricing","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"site-roles","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"automation-resolve-merge-conflict","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"scoped-publishing","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"mintql","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"page-level-analytics","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"editor-version-history-v2","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"billing-page-v2","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"full-page-search","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"workspace-projects","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"assistant-v5","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"fork-pr-previews","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"revalidation-jwt","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"workos-sso-scim","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"hobby-abuse-scan","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"dashboard-inbox","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"workos-signup","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"turbopuffer-dual-write","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"workos-enabled","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"approve-review-flow","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"editor-metadata","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"sso-setup-deadline-banner","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"turbopuffer-serve","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"live-agents","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"starter-pricing","enabled":true,"variant":{"name":"control","enabled":true,"payload":{"type":"string","value":"control"},"feature_enabled":true},"impressionData":false},{"name":"llms-txt-v2","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false},{"name":"wildcard-email-auth","enabled":true,"variant":{"name":"disabled","enabled":false,"feature_enabled":true},"impressionData":false}],"googleFontsInlined":false},"customCss":[{"filePath":"style.css","content":"#content-area table,.mdx-content table{width:100%!important}.cube-consent{position:fixed;right:16px;bottom:16px;z-index:2147483000;box-sizing:border-box;display:flex;flex-direction:column;gap:8px;width:min(440px, calc(100vw - 32px));padding:16px;background:#fff;color:#111827;border:1px solid #e5e7eb;border-radius:8px;box-shadow:0 8px 24px rgb(0 0 0 / 12%);font-size:14px;line-height:1.5}html.dark .cube-consent{background:#111318;color:#f3f4f6;border-color:#2b2f38}.cube-consent__title{margin:0;padding-right:24px;font-size:16px;font-weight:600}.cube-consent__text,.cube-consent__status{margin:0;color:#4b5563}html.dark .cube-consent__text,html.dark .cube-consent__status{color:#b6bcc8}.cube-consent__link{color:inherit;text-decoration:underline}.cube-consent__actions{display:flex;gap:8px}.cube-consent__button{flex:1;padding:8px 16px;border:1px solid #111827;border-radius:6px;background:#111827;color:#fff;font:inherit;font-weight:500;cursor:pointer}html.dark .cube-consent__button{border-color:#f3f4f6;background:#f3f4f6;color:#111318}.cube-consent__button:hover{opacity:0.88}.cube-consent__button:focus-visible,.cube-consent__close:focus-visible,.cube-consent__link:focus-visible{outline:2px solid #111827;outline-offset:2px}html.dark .cube-consent :focus-visible{outline-color:#f3f4f6}.cube-consent__close{position:absolute;top:8px;right:8px;width:24px;height:24px;padding:0;border:0;background:transparent;color:inherit;font-size:20px;line-height:1;cursor:pointer}"}]}
{"page":{"pagePath":"recipes/data-modeling/cohort-retention","slug":"/recipes/data-modeling/cohort-retention","pageMetadata":{"title":"Implementing retention analysis \u0026 cohorts","description":"This is an advanced topic that assumes good, pre-existing knowledge of SQL and Cube.","href":"/recipes/data-modeling/cohort-retention"},"description":{"compiledSource":"\"use strict\";\nconst {jsx: _jsx} = arguments[0];\nconst {useMDXComponents: _provideComponents} = arguments[0];\nfunction _createMdxContent(props) {\n const _components = {\n p: \"p\",\n ..._provideComponents(),\n ...props.components\n };\n return _jsx(_components.p, {\n children: \"This is an advanced topic that assumes good, pre-existing knowledge of SQL and Cube.\"\n });\n}\nfunction MDXContent(props = {}) {\n const {wrapper: MDXLayout} = {\n ..._provideComponents(),\n ...props.components\n };\n return MDXLayout ? _jsx(MDXLayout, {\n ...props,\n children: _jsx(_createMdxContent, {\n ...props\n })\n }) : _createMdxContent(props);\n}\nreturn {\n default: MDXContent\n};\n","frontmatter":{},"scope":{}},"pageType":"default","mdx":{"mdxSource":{"compiledSource":"\"use strict\";const{Fragment:_Fragment,jsx:_jsx,jsxs:_jsxs}=arguments[0];const{useMDXComponents:_provideComponents}=arguments[0];function _createMdxContent(props){const _components={code:`code`,p:`p`,pre:`pre`,span:`span`,strong:`strong`,tbody:`tbody`,td:`td`,th:`th`,thead:`thead`,tr:`tr`,..._provideComponents(),...props.components},{CodeBlock,CodeGroup,Info,OptimizedImage,Table}=_components;if(!CodeBlock)_missingMdxReference(`CodeBlock`,true);if(!CodeGroup)_missingMdxReference(`CodeGroup`,true);if(!Info)_missingMdxReference(`Info`,true);if(!OptimizedImage)_missingMdxReference(`OptimizedImage`,true);if(!Table)_missingMdxReference(`Table`,true);return _jsxs(_Fragment,{children:[_jsxs(_components.p,{children:[`Whether you’re selling groceries, financial services, or gym memberships,\nsuccessful recruitment of new customers is only truly successful if they return\nto buy from you again. The metric that reflects this is called `,_jsx(_components.strong,{children:`retention`}),`,\nand the approach we use is `,_jsx(_components.strong,{children:`customer retention analysis`}),`. Retention analysis\nis typically done using `,_jsx(_components.strong,{children:`cohort analysis`}),`.`]}),`\n`,_jsxs(_components.p,{children:[`Cohort analysis is a technique to see how variables change over in different\ngroups with different starting conditions. Retention is a simplified one, where\nthe `,_jsx(_components.strong,{children:`starting condition is usually the time of signup and the variable is\nsimply activity`}),`.`]}),`\n`,_jsx(_components.p,{children:`It’s usually visualized as a cohort grid or retention curves.`}),`\n`,_jsx(`div`,{style:{textAlign:`center`},children:_jsx(OptimizedImage,{src:`https://ucarecdn.com/72b9bce1-eaa2-49bb-8d74-5dd4bb5dd6eb/`,style:{border:`none`},width:`100%`})}),`\n`,_jsxs(_components.p,{children:[`Cohort retention analysis is pretty hard to do in SQL. `,_jsx(_components.strong,{children:`We need to have the\nuser-date combination`}),`, which tells us about a user’s activity on that date,\nincluding dates with no activity. To do this, we need to make a tricky join,\nwhich gives us a dates list. Once we have it, we can “fill it” with users’\nactivities.`]}),`\n`,_jsx(_components.p,{children:`The example below shows monthly cohort retention. The same technique can be used\nfor daily or weekly retention.`}),`\n`,_jsx(Info,{children:_jsx(_components.p,{children:`The SQL code in this guide is Postgres-compliant. The final SQL code may be\ndifferent depending on your database. Also, this technique requires at least 1\nuser to be active during the month, otherwise this month will not be included in\nthe months’ list.`})}),`\n`,_jsxs(CodeGroup,{children:[_jsx(CodeBlock,{filename:`YAML`,numberOfLines:`23`,language:`yaml`,children:_jsx(_components.pre,{className:`shiki shiki-themes dark-plus dark-plus`,style:{backgroundColor:`#0B0C0E`,\"--shiki-dark-bg\":`#0B0C0E`,color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},language:`yaml`,children:_jsxs(_components.code,{language:`yaml`,numberOfLines:`23`,children:[_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:`cubes`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`:`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` - `}),_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:`name`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`: `}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:`monthly_retention`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:` sql`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`: `}),_jsx(_components.span,{style:{color:`#C586C0`,\"--shiki-dark\":`#C586C0`},children:`|`})]}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` SELECT`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` users.id as user_id,`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` date_trunc('month', users.created_at) as signup_month,`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` months_list.activity_month as activity_month,`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` data.monthly_pageviews`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` FROM users LEFT JOIN (`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` SELECT`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` DISTINCT (date_trunc('month', pages.original_timestamp)) as`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` activity_month`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` FROM pages`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` ) as months_list ON months_list.activity_month >= date_trunc('month',`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` users.created_at) LEFT JOIN (`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` SELECT`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` p.user_id,`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` date_trunc('month', p.original_timestamp) as activity_month,`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` COUNT(DISTINCT p.id) as monthly_pageviews`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` FROM pages p`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` GROUP BY 1,2`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` ) as data ON data.activity_month = months_list.activity_month AND`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` data.user_id = users.id`})}),`\n`]})})}),_jsx(CodeBlock,{filename:`JavaScript`,numberOfLines:`26`,language:`javascript`,children:_jsx(_components.pre,{className:`shiki shiki-themes dark-plus dark-plus`,style:{backgroundColor:`#0B0C0E`,\"--shiki-dark-bg\":`#0B0C0E`,color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},language:`javascript`,children:_jsxs(_components.code,{language:`javascript`,numberOfLines:`26`,children:[_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#DCDCAA`,\"--shiki-dark\":`#DCDCAA`},children:`cube`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`(`}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:\"`monthly_retention`\"}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`, {`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:` sql:`}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:\" `SELECT\"})]}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` users.id as user_id,`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` date_trunc('month', users.created_at) as signup_month,`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` months_list.activity_month as activity_month,`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` data.monthly_pageviews`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` FROM users`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` LEFT JOIN`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` (`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` SELECT`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` DISTINCT (date_trunc('month', pages.original_timestamp)) as activity_month`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` FROM pages`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` ) as months_list`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` ON months_list.activity_month >= date_trunc('month', users.created_at)`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` LEFT JOIN`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` (`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` SELECT`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` p.user_id,`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` date_trunc('month', p.original_timestamp) as activity_month,`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` COUNT(DISTINCT p.id) as monthly_pageviews`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` FROM pages p`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` GROUP BY 1,2`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` ) as data`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` ON data.activity_month = months_list.activity_month`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:\" AND data.user_id = users.id`\"})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`})`})}),`\n`]})})})]}),`\n`,_jsx(_components.p,{children:`The SQL above provides the base table for our retention cube. It would show\nsignup months and activity months with pageviews:`}),`\n`,_jsxs(Table,{children:[_jsx(_components.thead,{children:_jsxs(_components.tr,{children:[_jsx(_components.th,{children:`user_id`}),_jsx(_components.th,{children:`signup_month`}),_jsx(_components.th,{children:`activity_month`}),_jsx(_components.th,{children:`monthly_pageviews`})]})}),_jsxs(_components.tbody,{children:[_jsxs(_components.tr,{children:[_jsx(_components.td,{\"data-numeric\":`true`,children:`1`}),_jsx(_components.td,{\"data-numeric\":`true`,children:`1/18`}),_jsx(_components.td,{\"data-numeric\":`true`,children:`1/18`}),_jsx(_components.td,{\"data-numeric\":`true`,children:`10`})]}),_jsxs(_components.tr,{children:[_jsx(_components.td,{\"data-numeric\":`true`,children:`1`}),_jsx(_components.td,{\"data-numeric\":`true`,children:`1/18`}),_jsx(_components.td,{\"data-numeric\":`true`,children:`2/18`}),_jsx(_components.td,{\"data-numeric\":`true`,children:`5`})]}),_jsxs(_components.tr,{children:[_jsx(_components.td,{\"data-numeric\":`true`,children:`1`}),_jsx(_components.td,{\"data-numeric\":`true`,children:`1/18`}),_jsx(_components.td,{\"data-numeric\":`true`,children:`3/18`}),_jsx(_components.td,{\"data-numeric\":`true`,children:`0`})]}),_jsxs(_components.tr,{children:[_jsx(_components.td,{\"data-numeric\":`true`,children:`2`}),_jsx(_components.td,{\"data-numeric\":`true`,children:`2/18`}),_jsx(_components.td,{\"data-numeric\":`true`,children:`2/18`}),_jsx(_components.td,{\"data-numeric\":`true`,children:`12`})]}),_jsxs(_components.tr,{children:[_jsx(_components.td,{\"data-numeric\":`true`,children:`2`}),_jsx(_components.td,{\"data-numeric\":`true`,children:`2/18`}),_jsx(_components.td,{\"data-numeric\":`true`,children:`3/18`}),_jsx(_components.td,{\"data-numeric\":`true`,children:`0`})]}),_jsxs(_components.tr,{children:[_jsx(_components.td,{\"data-numeric\":`true`,children:`3`}),_jsx(_components.td,{\"data-numeric\":`true`,children:`3/18`}),_jsx(_components.td,{\"data-numeric\":`true`,children:`3/18`}),_jsx(_components.td,{\"data-numeric\":`true`,children:`5`})]})]})]}),`\n`,_jsxs(_components.p,{children:[`Now we can calculate a total count of users and the total count of active users,\nwho has more than 0 page views, for every month. Based on these two measures we\ncan calculate monthly `,_jsx(_components.code,{children:`percentage_of_active`}),`.`]}),`\n`,_jsxs(CodeGroup,{children:[_jsx(CodeBlock,{filename:`YAML`,numberOfLines:`27`,language:`yaml`,children:_jsx(_components.pre,{className:`shiki shiki-themes dark-plus dark-plus`,style:{backgroundColor:`#0B0C0E`,\"--shiki-dark-bg\":`#0B0C0E`,color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},language:`yaml`,children:_jsxs(_components.code,{language:`yaml`,numberOfLines:`27`,children:[_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:`cubes`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`:`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` - `}),_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:`name`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`: `}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:`monthly_retention`})]}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#6A9955`,\"--shiki-dark\":`#6A9955`},children:` # ...`})}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:` measures`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`:`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` - `}),_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:`name`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`: `}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:`total_count`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:` sql`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`: `}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:`user_id`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:` type`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`: `}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:`count_distinct`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:` public`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`: `}),_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:`false`})]}),`\n`,_jsx(_components.span,{className:`line`}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` - `}),_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:`name`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`: `}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:`total_active_count`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:` sql`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`: `}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:`user_id`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:` type`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`: `}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:`count_distinct`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:` filters`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`:`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` - `}),_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:`sql`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`: `}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:`monthly_pageviews > 0`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:` drill_members`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`:`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` - `}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:`users.id`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` - `}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:`users.email`})]}),`\n`,_jsx(_components.span,{className:`line`}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` - `}),_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:`name`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`: `}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:`percentage_of_active`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:` sql`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`: `}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:`\"1.0 * {total_active_count} / NULLIF({total_count}, 0)\"`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:` type`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`: `}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:`number`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:` format`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`: `}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:`percent`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:` drill_members`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`:`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` - `}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:`users.email`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` - `}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:`bots.team`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` - `}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:`bots.last_seen`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` - `}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:`percentage_of_active`})]}),`\n`]})})}),_jsx(CodeBlock,{filename:`JavaScript`,numberOfLines:`30`,language:`javascript`,children:_jsx(_components.pre,{className:`shiki shiki-themes dark-plus dark-plus`,style:{backgroundColor:`#0B0C0E`,\"--shiki-dark-bg\":`#0B0C0E`,color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},language:`javascript`,children:_jsxs(_components.code,{language:`javascript`,numberOfLines:`30`,children:[_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#DCDCAA`,\"--shiki-dark\":`#DCDCAA`},children:`cube`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`(`}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:\"`monthly_retention`\"}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`, {`})]}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#6A9955`,\"--shiki-dark\":`#6A9955`},children:` // ...`})}),`\n`,_jsx(_components.span,{className:`line`}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:` measures:`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` {`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:` total_count:`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` {`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:` sql:`}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:\" `user_id`\"}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`,`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:` type:`}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:\" `count_distinct`\"}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`,`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:` public:`}),_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:` false`})]}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` },`})}),`\n`,_jsx(_components.span,{className:`line`}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:` total_active_count:`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` {`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:` sql:`}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:\" `user_id`\"}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`,`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:` type:`}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:\" `count_distinct`\"}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`,`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:` filters:`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` [{ `}),_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:`sql:`}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:\" `\"}),_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:\"${\"}),_jsx(_components.span,{style:{color:`#4FC1FF`,\"--shiki-dark\":`#4FC1FF`},children:`CUBE`}),_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:`}`}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:\".monthly_pageviews > 0`\"}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` }],`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:` drill_members:`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` [`}),_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:`users`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`.`}),_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:`id`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`, `}),_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:`users`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`.`}),_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:`email`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`]`})]}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` },`})}),`\n`,_jsx(_components.span,{className:`line`}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:` percentage_of_active:`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` {`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:` sql:`}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:\" `1.0 * \"}),_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:\"${\"}),_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:`total_active_count`}),_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:`}`}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:` / NULLIF(`}),_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:\"${\"}),_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:`total_count`}),_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:`}`}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:\", 0)`\"}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`,`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:` type:`}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:\" `number`\"}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`,`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:` format:`}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:\" `percent`\"}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`,`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:` drill_members:`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` [`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:` users`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`.`}),_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:`email`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`,`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:` bots`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`.`}),_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:`team`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`,`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:` bots`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`.`}),_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:`last_seen`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`,`})]}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:` percentage_of_active`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` ]`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` }`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` }`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`})`})}),`\n`]})})})]}),`\n`,_jsxs(_components.p,{children:[`To be able to build cohorts, we need to group by two dimensions: `,_jsx(_components.strong,{children:`signup\ndate`}),`, which will define our cohorts, and `,_jsx(_components.strong,{children:`months since signup`}),`, which will\nshow how the percentage of active users is changing.`]}),`\n`,_jsxs(CodeGroup,{children:[_jsx(CodeBlock,{filename:`YAML`,numberOfLines:`11`,language:`yaml`,children:_jsx(_components.pre,{className:`shiki shiki-themes dark-plus dark-plus`,style:{backgroundColor:`#0B0C0E`,\"--shiki-dark-bg\":`#0B0C0E`,color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},language:`yaml`,children:_jsxs(_components.code,{language:`yaml`,numberOfLines:`11`,children:[_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:`cubes`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`:`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` - `}),_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:`name`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`: `}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:`monthly_retention`})]}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#6A9955`,\"--shiki-dark\":`#6A9955`},children:` # ...`})}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:` dimensions`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`:`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` - `}),_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:`name`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`: `}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:`months_since_signup`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:` sql`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`: `}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:`\"DATEDIFF('month', signup_month, activity_month)\"`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:` type`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`: `}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:`number`})]}),`\n`,_jsx(_components.span,{className:`line`}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` - `}),_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:`name`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`: `}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:`signup_date`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:` sql`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`: `}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:`\"(signup_month AT TIME ZONE 'America/Los_Angeles')\"`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:` type`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`: `}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:`time`})]}),`\n`]})})}),_jsx(CodeBlock,{filename:`JavaScript`,numberOfLines:`15`,language:`javascript`,children:_jsx(_components.pre,{className:`shiki shiki-themes dark-plus dark-plus`,style:{backgroundColor:`#0B0C0E`,\"--shiki-dark-bg\":`#0B0C0E`,color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},language:`javascript`,children:_jsxs(_components.code,{language:`javascript`,numberOfLines:`15`,children:[_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#DCDCAA`,\"--shiki-dark\":`#DCDCAA`},children:`cube`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`(`}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:\"`monthly_retention`\"}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`, {`})]}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#6A9955`,\"--shiki-dark\":`#6A9955`},children:` // ...`})}),`\n`,_jsx(_components.span,{className:`line`}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:` dimensions:`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` {`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:` months_since_signup:`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` {`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:` sql:`}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:\" `DATEDIFF('month', \"}),_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:\"${\"}),_jsx(_components.span,{style:{color:`#4FC1FF`,\"--shiki-dark\":`#4FC1FF`},children:`CUBE`}),_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:`}`}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:`.signup_month, `}),_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:\"${\"}),_jsx(_components.span,{style:{color:`#4FC1FF`,\"--shiki-dark\":`#4FC1FF`},children:`CUBE`}),_jsx(_components.span,{style:{color:`#569CD6`,\"--shiki-dark\":`#569CD6`},children:`}`}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:\".activity_month)`\"}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`,`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:` type:`}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:\" `number`\"})]}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` },`})}),`\n`,_jsx(_components.span,{className:`line`}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:` signup_date:`}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` {`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:` sql:`}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:\" `(signup_month AT TIME ZONE 'America/Los_Angeles')`\"}),_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`,`})]}),`\n`,_jsxs(_components.span,{className:`line`,children:[_jsx(_components.span,{style:{color:`#9CDCFE`,\"--shiki-dark\":`#9CDCFE`},children:` type:`}),_jsx(_components.span,{style:{color:`#CE9178`,\"--shiki-dark\":`#CE9178`},children:\" `time`\"})]}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` }`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:` }`})}),`\n`,_jsx(_components.span,{className:`line`,children:_jsx(_components.span,{style:{color:`#D4D4D4`,\"--shiki-dark\":`#D4D4D4`},children:`})`})}),`\n`]})})})]}),`\n`,_jsx(Info,{children:_jsxs(_components.p,{children:[`Note, we are explicitly setting the `,_jsx(_components.code,{children:`signup_month`}),` timezone. `,_jsx(_components.code,{children:`date_trunc`}),`\nreturns UTC dates and not setting a correct timezone would lead to wrong results\ndue to time shift.`]})})]})}function MDXContent(props={}){const{wrapper:MDXLayout}={..._provideComponents(),...props.components};return MDXLayout?_jsx(MDXLayout,{...props,children:_jsx(_createMdxContent,{...props})}):_createMdxContent(props)}return{default:MDXContent};function _missingMdxReference(id,component){throw new Error(`Expected `+(component?`component`:`object`)+\" `\"+id+\"` to be defined: you likely forgot to import, pass, or provide it.\")}","frontmatter":{},"scope":{"codeStyling":"dark","config":{},"pageMetadata":{"title":"Implementing retention analysis \u0026 cohorts","description":"This is an advanced topic that assumes good, pre-existing knowledge of SQL and Cube.","href":"/recipes/data-modeling/cohort-retention"}}},"mdxSourceWithNoJs":null},"mdxExtracts":{"tableOfContents":[],"codeExamples":{}},"jsLinks":["/_mintlify/custom/cubed3/73c42f6b43b9b3b0/scripts/extract-chat.js","/_mintlify/custom/cubed3/08d502537a7bd0d7/scripts/extract-core-data.js","/_mintlify/custom/cubed3/476a9fa93e98d93b/consent.js"],"subdomain":"cubed3","siteName":"Cube Documentation","title":"Implementing retention analysis \u0026 cohorts - Cube Documentation","navMaps":{"firstHrefInVersion":{},"firstHrefInLanguage":{}},"lastModified":"2026-11-14T14:24:10.142Z","originalFileLocation":"recipes/data-modeling/cohort-retention.mdx"},"headHtml":"\u003cscript data-mint-head>(function(){var d=document.documentElement,p=null;if(!p){try{p=localStorage.getItem(\"isDarkMode\")}catch(e){}if(p==='true')p='dark';else if(p==='false')p='light';if(p!=='light'\u0026\u0026p!=='dark'\u0026\u0026p!=='system')p=\"system\"}var r=p==='system'?(matchMedia(\"(prefers-color-scheme: dark)\").matches?'dark':'light'):p;d.classList.remove(r==='dark'?'light':'dark');d.classList.add(r);d.setAttribute(\"data-theme-preference\",p)})();\u003c/script>\u003cmeta data-mint-head name=\"description\" content=\"This is an advanced topic that assumes good, pre-existing knowledge of SQL and Cube.\">\u003cmeta data-mint-head name=\"application-name\" content=\"Cube Documentation\">\u003cmeta data-mint-head name=\"generator\" content=\"Mintlify\">\u003clink data-mint-head rel=\"icon\" type=\"image/png\" sizes=\"192x192\" href=\"/mintlify-assets/_mintlify/favicons/cubed3/JtLYPWhDFrYGSK8W/_generated/favicon/android-chrome-192x192.png\" media=\"(prefers-color-scheme: light)\">\u003clink data-mint-head rel=\"apple-touch-icon\" type=\"image/png\" sizes=\"180x180\" href=\"/mintlify-assets/_mintlify/favicons/cubed3/JtLYPWhDFrYGSK8W/_generated/favicon/apple-touch-icon.png\">\u003clink data-mint-head rel=\"icon\" type=\"image/png\" sizes=\"16x16\" href=\"/mintlify-assets/_mintlify/favicons/cubed3/JtLYPWhDFrYGSK8W/_generated/favicon/favicon-16x16.png\" media=\"(prefers-color-scheme: light)\">\u003clink data-mint-head rel=\"icon\" type=\"image/png\" sizes=\"32x32\" href=\"/mintlify-assets/_mintlify/favicons/cubed3/JtLYPWhDFrYGSK8W/_generated/favicon/favicon-32x32.png\" media=\"(prefers-color-scheme: light)\">\u003clink data-mint-head rel=\"shortcut icon\" type=\"image/x-icon\" href=\"/mintlify-assets/_mintlify/favicons/cubed3/JtLYPWhDFrYGSK8W/_generated/favicon/favicon.ico\" media=\"(prefers-color-scheme: light)\">\u003clink data-mint-head rel=\"icon\" type=\"image/png\" sizes=\"192x192\" href=\"/mintlify-assets/_mintlify/favicons/cubed3/JtLYPWhDFrYGSK8W/_generated/favicon-dark/android-chrome-192x192.png\" media=\"(prefers-color-scheme: dark)\">\u003clink data-mint-head rel=\"icon\" type=\"image/png\" sizes=\"16x16\" href=\"/mintlify-assets/_mintlify/favicons/cubed3/JtLYPWhDFrYGSK8W/_generated/favicon-dark/favicon-16x16.png\" media=\"(prefers-color-scheme: dark)\">\u003clink data-mint-head rel=\"icon\" type=\"image/png\" sizes=\"32x32\" href=\"/mintlify-assets/_mintlify/favicons/cubed3/JtLYPWhDFrYGSK8W/_generated/favicon-dark/favicon-32x32.png\" media=\"(prefers-color-scheme: dark)\">\u003clink data-mint-head rel=\"shortcut icon\" type=\"image/x-icon\" href=\"/mintlify-assets/_mintlify/favicons/cubed3/JtLYPWhDFrYGSK8W/_generated/favicon-dark/favicon.ico\" media=\"(prefers-color-scheme: dark)\">\u003cmeta data-mint-head property=\"og:title\" content=\"Implementing retention analysis \u0026amp; cohorts - Cube Documentation\">\u003cmeta data-mint-head property=\"og:description\" content=\"This is an advanced topic that assumes good, pre-existing knowledge of SQL and Cube.\">\u003cmeta data-mint-head property=\"og:site_name\" content=\"Cube Documentation\">\u003cmeta data-mint-head property=\"og:url\" content=\"https://docs.cube.dev/recipes/data-modeling/cohort-retention\">\u003cmeta data-mint-head property=\"og:type\" content=\"website\">\u003cmeta data-mint-head property=\"og:image\" content=\"https://cubed3.mintlify.site/_mintlify/api/og/v2?division=User+%26+Event+Analytics\u0026amp;title=Implementing+retention+analysis+%26+cohorts\u0026amp;description=This+is+an+advanced+topic+that+assumes+good%2C+pre-existing+knowledge+of+SQL+and+Cube.\u0026amp;theme=6d1e003873cec4de62649d7c\u0026amp;signature=9b1e2336884a62a5c32cf9fe707a7d2bc6717b04259d94676b73eb3f8053fd22\">\u003cmeta data-mint-head property=\"og:image:width\" content=\"1200\">\u003cmeta data-mint-head property=\"og:image:height\" content=\"630\">\u003cmeta data-mint-head name=\"twitter:card\" content=\"summary_large_image\">\u003cmeta data-mint-head name=\"twitter:title\" content=\"Implementing retention analysis \u0026amp; cohorts - Cube Documentation\">\u003cmeta data-mint-head name=\"twitter:description\" content=\"This is an advanced topic that assumes good, pre-existing knowledge of SQL and Cube.\">\u003cmeta data-mint-head name=\"twitter:image\" content=\"https://cubed3.mintlify.site/_mintlify/api/og/v2?division=User+%26+Event+Analytics\u0026amp;title=Implementing+retention+analysis+%26+cohorts\u0026amp;description=This+is+an+advanced+topic+that+assumes+good%2C+pre-existing+knowledge+of+SQL+and+Cube.\u0026amp;theme=6d1e003873cec4de62649d7c\u0026amp;signature=9b1e2336884a62a5c32cf9fe707a7d2bc6717b04259d94676b73eb3f8053fd22\">\u003cmeta data-mint-head name=\"twitter:image:width\" content=\"1200\">\u003cmeta data-mint-head name=\"twitter:image:height\" content=\"630\">\u003cmeta data-mint-head name=\"msapplication-config\" content=\"/mintlify-assets/_mintlify/favicons/cubed3/JtLYPWhDFrYGSK8W/_generated/favicon/browserconfig.xml\">\u003cmeta data-mint-head name=\"apple-mobile-web-app-title\" content=\"Cube Documentation\">\u003cmeta data-mint-head name=\"msapplication-TileColor\" content=\"#5339CF\">\u003clink data-mint-head rel=\"canonical\" href=\"https://docs.cube.dev/recipes/data-modeling/cohort-retention\">\u003clink data-mint-head rel=\"alternate\" type=\"application/xml\" href=\"/sitemap.xml\">\u003clink data-mint-head rel=\"alternate\" type=\"text/markdown\" href=\"/recipes/data-modeling/cohort-retention.md\">\u003cscript data-mint-head type=\"application/ld+json\">{\"@context\":\"https://schema.org\",\"@graph\":[{\"@type\":\"Organization\",\"@id\":\"https://docs.cube.dev/#organization\",\"name\":\"Cube Documentation\",\"url\":\"https://docs.cube.dev\",\"logo\":{\"@type\":\"ImageObject\",\"url\":\"https://mintcdn.com/cubed3/wCTntgEXVYYYnx4l/logo/light.svg?fit=max\u0026auto=format\u0026n=wCTntgEXVYYYnx4l\u0026q=85\u0026s=8bbf45ba64eac6116629064fac55aad7\"}},{\"@type\":\"WebSite\",\"@id\":\"https://docs.cube.dev#website\",\"name\":\"Cube Documentation\",\"url\":\"https://docs.cube.dev\",\"publisher\":{\"@id\":\"https://docs.cube.dev/#organization\"}},{\"@type\":\"WebPage\",\"@id\":\"https://docs.cube.dev/recipes/data-modeling/cohort-retention#webpage\",\"url\":\"https://docs.cube.dev/recipes/data-modeling/cohort-retention\",\"name\":\"Implementing retention analysis \u0026 cohorts\",\"description\":\"This is an advanced topic that assumes good, pre-existing knowledge of SQL and Cube.\",\"dateModified\":\"2026-11-14T14:24:10.142Z\",\"isPartOf\":{\"@id\":\"https://docs.cube.dev#website\"},\"breadcrumb\":{\"@id\":\"https://docs.cube.dev/recipes/data-modeling/cohort-retention#breadcrumb\"}},{\"@type\":\"BreadcrumbList\",\"@id\":\"https://docs.cube.dev/recipes/data-modeling/cohort-retention#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Topics\",\"item\":\"https://docs.cube.dev/recipes\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Data Modeling\",\"item\":\"https://docs.cube.dev/recipes/data-modeling/percentiles\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"User \u0026 Event Analytics\",\"item\":\"https://docs.cube.dev/recipes/data-modeling/active-users\"},{\"@type\":\"ListItem\",\"position\":4,\"name\":\"Implementing retention analysis \u0026 cohorts\",\"item\":\"https://docs.cube.dev/recipes/data-modeling/cohort-retention\"}]},{\"@type\":[\"Article\",\"TechArticle\"],\"@id\":\"https://docs.cube.dev/recipes/data-modeling/cohort-retention#article\",\"headline\":\"Implementing retention analysis \u0026 cohorts\",\"name\":\"Implementing retention analysis \u0026 cohorts\",\"description\":\"This is an advanced topic that assumes good, pre-existing knowledge of SQL and Cube.\",\"url\":\"https://docs.cube.dev/recipes/data-modeling/cohort-retention\",\"mainEntityOfPage\":{\"@id\":\"https://docs.cube.dev/recipes/data-modeling/cohort-retention#webpage\"},\"dateModified\":\"2026-11-14T14:24:10.142Z\",\"publisher\":{\"@id\":\"https://docs.cube.dev/#organization\"},\"isPartOf\":{\"@id\":\"https://docs.cube.dev#website\"}}]}\u003c/script>\u003cscript data-mint-head type=\"application/ld+json\">{\"@context\":\"https://schema.org\",\"@type\":\"WebSite\",\"name\":\"Cube Documentation\",\"publisher\":{\"@type\":\"Organization\",\"name\":\"Cube Documentation\"},\"provider\":{\"@type\":\"Organization\",\"name\":\"Mintlify\",\"url\":\"https://mintlify.com\"}}\u003c/script>","agentPointerHref":"/recipes/data-modeling/cohort-retention.md","siteName":"Cube Documentation","nav":{"global":null,"tabs":[{"tab":"Documentation","pages":[{"title":"Documentation","href":"/docs/introduction"}]},{"tab":"Admin","pages":[{"title":"Admin","href":"/admin/connect-to-data"}]},{"tab":"Embedding","pages":[{"title":"Embedding","href":"/embedding"}]},{"tab":"Reference","pages":[{"title":"Reference","href":"/reference/data-modeling/cube"}]},{"tab":"Cube Core","pages":[{"title":"Cube Core","href":"/cube-core"}]},{"tab":"Recipes","groups":[{"group":"Topics","pages":[{"title":"Overview","mode":"wide","href":"/recipes"},{"group":"Data Modeling","pages":[{"group":"Calculations \u0026 Metrics","pages":[{"title":"Calculating averages and percentiles","href":"/recipes/data-modeling/percentiles"},{"title":"Calculating nested aggregates","href":"/recipes/data-modeling/nested-aggregates"},{"title":"Calculating filtered aggregates","href":"/recipes/data-modeling/filtered-aggregates"},{"title":"Calculating share of total","href":"/recipes/data-modeling/share-of-total"},{"title":"Calculating average order value","href":"/recipes/data-modeling/average-order-value"},{"title":"Calculating period-over-period changes","href":"/recipes/data-modeling/period-over-period"},{"title":"Reusing period-to-date logic across measures","href":"/recipes/data-modeling/reusable-period-to-date"}]},{"group":"Query-time parameters","pages":[{"title":"Configurable rolling windows and time shifts","href":"/recipes/data-modeling/dynamic-rolling-windows"},{"title":"Passing dynamic parameters in a query","href":"/recipes/data-modeling/passing-dynamic-parameters-in-a-query"}]},{"group":"Time Series \u0026 Calendars","pages":[{"title":"Working around string time dimensions","href":"/recipes/data-modeling/string-time-dimensions"},{"title":"Implementing custom time dimension granularities","href":"/recipes/data-modeling/custom-granularity"},{"title":"Implementing custom calendars","href":"/recipes/data-modeling/custom-calendar"},{"title":"Implementing data snapshots","href":"/recipes/data-modeling/snapshots"}]},{"group":"User \u0026 Event Analytics","pages":[{"title":"Daily, Weekly, Monthly Active Users (DAU, WAU, MAU)","href":"/recipes/data-modeling/active-users"},{"title":"Implementing event analytics","description":"Turn raw clickstream events into session definitions and metrics you control, using SQL-based patterns that work across common event pipelines.","href":"/recipes/data-modeling/event-analytics"},{"title":"Implementing retention analysis \u0026 cohorts","href":"/recipes/data-modeling/cohort-retention"}]},{"title":"Cube style guide","description":"This style guide includes best practices on data modeling in Cube.","href":"/recipes/data-modeling/style-guide"},{"title":"Designing metrics","hidden":true,"href":"/recipes/data-modeling/designing-metrics"},{"title":"Implementing Entity-Attribute-Value Model (EAV)","href":"/recipes/data-modeling/entity-attribute-value"},{"title":"Using Cube with dbt","href":"/recipes/data-modeling/dbt"},{"title":"dbt pull with the Cube CLI","href":"/recipes/data-modeling/dbt-cli"},{"title":"Generating the data model dynamically","href":"/recipes/data-modeling/using-dynamic-measures"},{"title":"Using dynamic union tables","href":"/recipes/data-modeling/dynamic-union-tables"},{"title":"Querying across data sources","href":"/recipes/data-modeling/cross-data-source-queries"},{"title":"Polymorphic cubes","href":"/recipes/data-modeling/polymorphic-cubes"},{"title":"Reusing a view measure across views","href":"/recipes/data-modeling/reusing-view-measures"},{"title":"Custom ordering for categorical values","href":"/recipes/data-modeling/custom-order"}]},{"group":"AI","pages":[{"title":"Agent-to-agent: using the Chat API as a tool","href":"/recipes/ai/agent-to-agent"}]},{"group":"Pre-aggregations","pages":[{"title":"Accelerating non-additive measures","href":"/recipes/pre-aggregations/non-additivity"},{"title":"Incrementally building pre-aggregations for a date range","href":"/recipes/pre-aggregations/incrementally-building-pre-aggregations-for-a-date-range"},{"title":"Disabling pre-aggregations","href":"/recipes/pre-aggregations/disabling-pre-aggregations"},{"title":"Using `original_sql` and `rollup` pre-aggregations effectively","href":"/recipes/pre-aggregations/using-originalsql-and-rollups-effectively"},{"title":"Refreshing select partitions","href":"/recipes/pre-aggregations/refreshing-select-partitions"},{"title":"Joining data from multiple data sources","href":"/recipes/pre-aggregations/joining-multiple-data-sources"},{"title":"Joining pre-aggregations with different refresh cadences","href":"/recipes/pre-aggregations/mixed-refresh-cadences-rollup-join"}]},{"group":"Configuration","pages":[{"title":"Referencing environment variables","href":"/recipes/configuration/environment-variables"},{"title":"Using SSL connections to a data source","href":"/recipes/configuration/using-ssl-connections-to-data-source"},{"title":"Optimizing data source usage","href":"/recipes/configuration/data-store-cost-saving-guide"},{"title":"Providing a custom data source for each tenant","href":"/recipes/configuration/multiple-sources-same-schema"},{"title":"Providing a custom data model for each tenant","href":"/recipes/configuration/custom-data-model-per-tenant"}]},{"group":"Core Data API","pages":[{"title":"Getting unique values for a field","href":"/recipes/core-data-api/getting-unique-values-for-a-field"},{"title":"Retrieving numeric values on the front-end","href":"/recipes/core-data-api/cast-numerics"},{"title":"Implementing custom sorting","href":"/recipes/core-data-api/sorting"},{"title":"Implementing pagination","href":"/recipes/core-data-api/pagination"},{"title":"Drilldowns","href":"/recipes/core-data-api/drilldowns"},{"title":"Real-Time data fetch in the REST (JSON) API","href":"/recipes/core-data-api/real-time-data-fetch"},{"title":"Using cloud groups and user attributes with Core Data APIs","href":"/recipes/core-data-api/cloud-groups-and-user-attributes"}]}]}]},{"tab":"API","pages":[{"title":"API","href":"/api-reference/introduction"}]}]},"basePath":"","title":"Implementing retention analysis \u0026 cohorts - Cube Documentation","deploymentId":"dpl_vSEmv961Ncyvz1psVwgUnTXQMSYk","buildId":"dpl_vSEmv961Ncyvz1psVwgUnTXQMSYk","dynamics":["a74be98ba7"],"chunks":["zoom-image-lightbox-BguUeq2N.js","lightbox-handle-r17bVUz3.js"]}