---
title: SQLでコホート・リテンションを計算する方法
description: "SQLにてリテンションを算出する方法を解説しています。
BIツール操作には必須の知識となりますので、是非参考にしてください。"
image: https://sisense.gaprise.jp/hubfs/Sisense/business%20documents%20on%20office%20table%20with%20smart%20phone%20and%20digital%20tablet%20and%20graph%20business%20diagram%20and%20man%20working%20in%20the%20background.jpeg
---

[![Sisense-Logo-Horizontal-combination (1)](https://sisense.gaprise.jp/hubfs/Sisense/Sisense-Logo-Horizontal-combination%20(1).svg "Sisense-Logo-Horizontal-combination (1)")](https://sisense.gaprise.jp/)

# Sisense日本公式ブログ

「Infused Analytics」全く新しい第三世代BIツール

## おすすめ記事

[![著者名](https://sisense.gaprise.jp/hs-fs/hubfs/whatgraph/gaprise_icon.jpg?width=20&name=gaprise_icon.jpg)  マーケティングチーム ](https://whatagraph.gaprise.jp/blog/author/%E3%82%AE%E3%83%A3%E3%83%97%E3%83%A9%E3%82%A4%E3%82%BA%E3%83%9E%E3%83%BC%E3%82%B1%E3%83%86%E3%82%A3%E3%83%B3%E3%82%B0%E3%83%81%E3%83%BC%E3%83%A0)

2021年08月23日

## [SISENSE×REDSHIFTでCOVID-19患者と医師とのコミュニケーションを改善](https://sisense.gaprise.jp/blog/0002)

[UseCase](https://gaprise-19917462.hs-sites.com/blog/topic/test1)

[** ](https://sisense.gaprise.jp/blog/0010)

[![著者名](https://sisense.gaprise.jp/hs-fs/hubfs/whatgraph/gaprise_icon.jpg?width=20&name=gaprise_icon.jpg)  マーケティングチーム ](https://whatagraph.gaprise.jp/blog/author/%E3%82%AE%E3%83%A3%E3%83%97%E3%83%A9%E3%82%A4%E3%82%BA%E3%83%9E%E3%83%BC%E3%82%B1%E3%83%86%E3%82%A3%E3%83%B3%E3%82%B0%E3%83%81%E3%83%BC%E3%83%A0)

2021年08月23日

## [住宅修繕会社での劇的なワークフローの高速化を行った具体的な手法](https://sisense.gaprise.jp/blog/1)

[UseCase](https://gaprise-19917462.hs-sites.com/blog/topic/test1)

[** ](https://sisense.gaprise.jp/blog/sisense.gaprise.jp/blog/0009)

[![著者名](https://sisense.gaprise.jp/hs-fs/hubfs/whatgraph/gaprise_icon.jpg?width=20&name=gaprise_icon.jpg)  マーケティングチーム ](https://whatagraph.gaprise.jp/blog/author/%E3%82%AE%E3%83%A3%E3%83%97%E3%83%A9%E3%82%A4%E3%82%BA%E3%83%9E%E3%83%BC%E3%82%B1%E3%83%86%E3%82%A3%E3%83%B3%E3%82%B0%E3%83%81%E3%83%BC%E3%83%A0)

2021年08月20日

## [データサイエンス VS データアナリティクス：その違いとは？](https://sisense.gaprise.jp/blog/0007)

[BI](https://gaprise-19917462.hs-sites.com/blog/topic/test1)

[** ](https://sisense.gaprise.jp/blog/0007)

[![著者名](https://sisense.gaprise.jp/hs-fs/hubfs/whatgraph/gaprise_icon.jpg?width=20&name=gaprise_icon.jpg)  マーケティングチーム ](https://whatagraph.gaprise.jp/blog/author/%E3%82%AE%E3%83%A3%E3%83%97%E3%83%A9%E3%82%A4%E3%82%BA%E3%83%9E%E3%83%BC%E3%82%B1%E3%83%86%E3%82%A3%E3%83%B3%E3%82%B0%E3%83%81%E3%83%BC%E3%83%A0)

2021年08月23日

## [【活用事例】埋め込み型ダッシュボードで教師を支援する](https://sisense.gaprise.jp/blog/1)

[UseCase](https://gaprise-19917462.hs-sites.com/blog/topic/test1)

[** ](https://sisense.gaprise.jp/blog/0008)

## 最新記事

カテゴリータグ

- [Sisense](https://sisense.gaprise.jp/blog/topic/sisense)
- [BI](https://sisense.gaprise.jp/blog/topic/bi)
- [UseCase](https://sisense.gaprise.jp/blog/topic/usecase)
- [Marketing](https://sisense.gaprise.jp/blog/topic/marketing)

[![マーケティングチーム](https://sisense.gaprise.jp/hubfs/whatgraph/2e5b8005-5df1-437c-9bcd-a1555da7d44e.jpg)](https://sisense.gaprise.jp/blog/author/ギャプライズマーケティングチーム)

 By

[マーケティングチーム](https://sisense.gaprise.jp/blog/author/ギャプライズマーケティングチーム)

 2021年08月31日

[**](https://www.facebook.com/sharer/sharer.php?u=https%3A%2F%2Fsisense.gaprise.jp%2Fblog%2F0034) [**](http://www.linkedin.com/shareArticle?mini=true&url=https://sisense.gaprise.jp/blog/0034) [**](https://www.twitter.com/share?url=https%3A%2F%2Fsisense.gaprise.jp%2Fblog%2F0034) [**](https://plus.google.com/share?url=https%3A%2F%2Fsisense.gaprise.jp%2Fblog%2F0034)

## [SQLでコホート・リテンションを計算する方法](https://sisense.gaprise.jp/blog/0034)

**  [Marketing](https://sisense.gaprise.jp/blog/topic/marketing) [UseCase](https://sisense.gaprise.jp/blog/topic/usecase) [Sisense](https://sisense.gaprise.jp/blog/topic/sisense) [BI](https://sisense.gaprise.jp/blog/topic/bi)

![business documents on office table with smart phone and digital tablet and graph business diagram and man working in the background](https://sisense.gaprise.jp/hs-fs/hubfs/Sisense/business%20documents%20on%20office%20table%20with%20smart%20phone%20and%20digital%20tablet%20and%20graph%20business%20diagram%20and%20man%20working%20in%20the%20background.jpeg?width=1000&name=business%20documents%20on%20office%20table%20with%20smart%20phone%20and%20digital%20tablet%20and%20graph%20business%20diagram%20and%20man%20working%20in%20the%20background.jpeg)

SQLは、アナリストにとって最も強力なツールの1つです。**SQL Superstar**では、この汎用性の高い言語を最大限に活用し、美しく効果的なクエリを作成するための実用的なアドバイスをお届けします。

ビジネスにおいて、最も避けるべきことは顧客を失うことです。スタートアップ企業であれば、顧客維持が重要であることをご存知でしょう。ユーザー維持率を常に測定し、改善することで、より多くのユーザーを長期的に維持できるようにする必要があります。この記事では、SQLを使って独自のデータからユーザー維持率を計算する方法を紹介します。

 

\ かんたん60秒で登録できます /  
\ GAPRISEならBI設計もまるっと請負 /

[お問合せはこちら](https://sisense.gaprise.jp/contact) [成功事例はこちら](https://sisense.gaprise.jp/whitepaper?hsCtaTracking=c0951af2-a9ef-4a38-8a8e-fe0b1dddcee9%7C4a9e4ad3-ff17-4800-8cd4-d07e5792b064) [デモ画面はこちら](https://sisense.gaprise.jp/demo?hsCtaTracking=18178425-fc9a-4c11-8d0a-02ce28f0a8e2%7Cd86e0c1a-3ae2-4423-bcd1-90f6446b61f9)

 

## リテンションの定義

グロリアが月曜日に製品を使用し、火曜日にも製品を使用した場合、彼女は継続的なユーザーと言えます。ビルが月曜日に製品を使用し、火曜日には使用しなかった場合、彼は失効したユーザーです。月曜日の保持率は、保持されたユーザー数を総ユーザー数で割ったものです。月曜日のユーザーがGloriaとBillの2人だけだった場合、月曜日の保持率は50%です。

### 基本的なユーザー維持率の算出

リテンションを計算するには、#1の時点でアクティブだったユーザーを数え、次に#2の時点でアクティブだったユーザーの数を数えることが重要です。SQLでこれを行う簡単な方法は、次のようにユーザーアクティビティテーブルを自身に左結合することです：

select *

from activity

left join activity as future_activity on

  activity.user_id = future_activity.user_id

  and activity.date = future_activity.date - interval '1 day'

 

現在、ユーザーのアクティビティの各行には、同じ行に1日後のユーザーのアクティビティが表示されています。これは、いくつかの単純なカウントでリテンションを計算するための理想的なテーブルとなります：

select

  activity.date, 

  count(distinct activity.user_id) as active_users, 

  count(distinct future_activity.user_id) as retained_users,

  count(distinct future_activity.user_id) / 

    count(distinct activity.user_id)::float as retention

from activity

left join activity as future_activity on

  activity.user_id = future_activity.user_id

  and activity.date = future_activity.date - interval '1 day'

group by 1

**このような図式になります：**

![](https://sisense.gaprise.jp/hs-fs/hubfs/Sisense/blog_0034_image4.png?width=602&height=300&name=blog_0034_image4.png)

さらに、1日の保存期間を7日や30日に変更すると、より長期的なユーザーのエンゲージメントを把握することができます。

### 新規ユーザーと既存ユーザーのリテンションを計算する

多くの場合、登録したばかりのユーザーと、ロイヤルティの高い長期的なユーザーとでは、リテンションが大きく異なります。新規ユーザーのリテンションを計算するには、単純にユーザーテーブルに参加し、そのユーザーの参加日に発生したアクティビティ行のみを調べます：

select

  users.date as date,

  count(distinct activity.user_id) as new_users, 

  count(distinct future_activity.user_id) as retained_users,

  count(distinct future_activity.user_id) / 

    count(distinct activity.user_id)::float as retention

from activity

-- Limits activity to activity from new users

join users on

  activity.user_id = users.id 

  and users.date = activity.date

left join activity as future_activity on

  activity.user_id = future_activity.user_id

  and activity.date = future_activity.date - interval '1 day'

group by 1

![](https://sisense.gaprise.jp/hs-fs/hubfs/Sisense/blog_0034_image1.png?width=602&height=300&name=blog_0034_image1.png)

 

全体のリテンションが46％であるのに対し、新規ユーザーのリテンションはわずか5.8％であることがわかります。これで、新規ユーザーを分けることが非常に有効であることがわかりました。新規ユーザーの保持率を向上させることは、明らかに優先すべきことです。

 

戻ってきたユーザーの保持率を見るには、単純に次のように変更します：

users.date = activity.date 

to:

users.date != activity.date

 

これにより、その日に参加したユーザーのアクティビティは効果的に除外されます。クエリは次のようになります：

select

  activity.date as date,

  count(distinct activity.user_id) as new_users, 

  count(distinct future_activity.user_id) as retained_users,

  count(distinct future_activity.user_id) / 

    count(distinct activity.user_id)::float as retention

from activity

-- Limits activity to activity from existing users

join users on 

  activity.user_id = users.id 

  and users.date != activity.date

left join activity as future_activity on

  activity.user_id = future_activity.user_id

  and activity.date = future_activity.date - interval '1 day'

group by 1

![](https://sisense.gaprise.jp/hs-fs/hubfs/Sisense/blog_0034_image3.png?width=602&height=300&name=blog_0034_image3.png)

予想通り、既存ユーザーの維持率は全体の平均よりも高い。66%対46%です。

 

\ かんたん60秒で登録できます /  
\ GAPRISEならBI設計もまるっと請負 /

[お問合せはこちら](https://sisense.gaprise.jp/contact) [成功事例はこちら](https://sisense.gaprise.jp/whitepaper?hsCtaTracking=c0951af2-a9ef-4a38-8a8e-fe0b1dddcee9%7C4a9e4ad3-ff17-4800-8cd4-d07e5792b064) [デモ画面はこちら](https://sisense.gaprise.jp/demo?hsCtaTracking=18178425-fc9a-4c11-8d0a-02ce28f0a8e2%7Cd86e0c1a-3ae2-4423-bcd1-90f6446b61f9)

 

### コホートにおけるリテンションの算出

A週に加わったユーザーとB週に加わったユーザーの定着率を比較することは、非常に有益です。これにより、製品の変更によって定着率が向上しているかどうかを確認することができます。

理想的には、次のようなグラフになります：

![](https://sisense.gaprise.jp/hs-fs/hubfs/Sisense/blog_0034_image2.png?width=602&height=300&name=blog_0034_image2.png)

 

まず、問題を簡単にするために、便利なサブクエリをいくつか定義します。new_user_activityは、ユーザーの活動を新規ユーザーに制限します：

with new_user_activity as (

  select activity.* from activity

  join users on

    users.id = activity.user_id

    and users.date = activity.date

)

Cohort_active_user_countは、各デイリーコホートにおけるアクティブユーザーの総数（リテンション計算の分母）を算出します：

, cohort_active_user_count as (

  select 

    date, count(distinct user_id) as count 

  from new_user_activity

  group by 1

)

それに加えて、メインのクエリにいくつかの小さな変更を加えます。

 

保持期間（日数）をfuture_activity.date - new_user_activity.dateとして計算し、それによってグループ化します。このグループ化では、コホート内のアクティブユーザーの単純なカウントが失われます。幸いなことに、この点を考慮してCohort_active_user_countサブクエリを作成し、これに結合して分母として使用することができます。

 

最後に、見栄えのする出力を作成し、偽の行を除外し、ソートを行う外部クエリでクエリをラップします。

select date, 'Day '|| to_char(period, 'DD') as period,

  new_users, retained_users, retention 

from (

  select 

    new_user_activity.date as date,

    (future_activity.date 

      - new_user_activity.date) as period,

    max(cohort_size.count) as new_users, -- all equal in group

    count(distinct future_activity.user_id) as retained_users,

    count(distinct future_activity.user_id) / 

      max(cohort_size.count)::float as retention

  from new_user_activity

  left join activity as future_activity on

    new_user_activity.user_id = future_activity.user_id

    and new_user_activity.date < future_activity.date

    and (new_user_activity.date + interval '10 days')

      >= future_activity.date

  left join cohort_active_user_count as cohort_size on 

    new_user_activity.date = cohort_size.date 

  group by 1, 2) t

where period is not null

order by date, period

 

また、私たちのお気に入りのSQLトリックの1つであるレンジジョインを使って、複数の日の保持量を1つのチャートに表示していることにも注目してください。

この結果、テーブルには：

![](https://sisense.gaprise.jp/hs-fs/hubfs/Sisense/blog_0034_image5.png?width=487&height=319&name=blog_0034_image5.png)

 

Sisense for Cloud Data Teamsでは、結果を自動的にピボットし、パーセンタイルで色分けすることができます：

![](https://sisense.gaprise.jp/hs-fs/hubfs/Sisense/blog_0034_image2.png?width=602&height=300&name=blog_0034_image2.png)

 

\ かんたん60秒で登録できます /  
\ GAPRISEならBI設計もまるっと請負 /

[お問合せはこちら](https://sisense.gaprise.jp/contact) [成功事例はこちら](https://sisense.gaprise.jp/whitepaper?hsCtaTracking=c0951af2-a9ef-4a38-8a8e-fe0b1dddcee9%7C4a9e4ad3-ff17-4800-8cd4-d07e5792b064) [デモ画面はこちら](https://sisense.gaprise.jp/demo?hsCtaTracking=18178425-fc9a-4c11-8d0a-02ce28f0a8e2%7Cd86e0c1a-3ae2-4423-bcd1-90f6446b61f9)

 

## リテンションをより具体的に

新規ユーザーと既存ユーザーのリテンションがどれほど違うか覚えていますか？多くのユーザーセグメントで同じような変化が見られます。人口統計、ユーザー獲得チャネル、有料ユーザーと非有料ユーザー、または閲覧、作成、購入などのアクティビティの種類によってリテンションを分けることは効果的です。

※本記事は、「[How To Calculate Cohort Retention in SQL](https://www.sisense.com/blog/how-to-calculate-cohort-retention-in-sql/)」を翻訳・加筆修正したものです。

[![マーケティングチーム](https://sisense.gaprise.jp/hs-fs/hubfs/whatgraph/2e5b8005-5df1-437c-9bcd-a1555da7d44e.jpg?width=20&name=2e5b8005-5df1-437c-9bcd-a1555da7d44e.jpg) マーケティングチーム](https://sisense.gaprise.jp/blog/author/ギャプライズマーケティングチーム)

4 21, 2023

## [Bioforum社はSisenseと提携し臨床試験における次世代データ解析...](https://sisense.gaprise.jp/blog/0053)

[Marketing](https://sisense.gaprise.jp/blog/topic/marketing) [UseCase](https://sisense.gaprise.jp/blog/topic/usecase) [Sisense](https://sisense.gaprise.jp/blog/topic/sisense) [BI](https://sisense.gaprise.jp/blog/topic/bi)

[**](https://sisense.gaprise.jp/blog/0053)

[![マーケティングチーム](https://sisense.gaprise.jp/hs-fs/hubfs/whatgraph/2e5b8005-5df1-437c-9bcd-a1555da7d44e.jpg?width=20&name=2e5b8005-5df1-437c-9bcd-a1555da7d44e.jpg) マーケティングチーム](https://sisense.gaprise.jp/blog/author/ギャプライズマーケティングチーム)

4 07, 2023

## [公共事業で第三世代アナリティクスSisenseを活用し効率的な運用に成功！](https://sisense.gaprise.jp/blog/0052)

[Marketing](https://sisense.gaprise.jp/blog/topic/marketing) [UseCase](https://sisense.gaprise.jp/blog/topic/usecase) [Sisense](https://sisense.gaprise.jp/blog/topic/sisense) [BI](https://sisense.gaprise.jp/blog/topic/bi)

[**](https://sisense.gaprise.jp/blog/0052)

[![マーケティングチーム](https://sisense.gaprise.jp/hs-fs/hubfs/whatgraph/2e5b8005-5df1-437c-9bcd-a1555da7d44e.jpg?width=20&name=2e5b8005-5df1-437c-9bcd-a1555da7d44e.jpg) マーケティングチーム](https://sisense.gaprise.jp/blog/author/ギャプライズマーケティングチーム)

3 24, 2023

## [GameDay社！第三世代BIツールSisenseを導入し、データへのリア...](https://sisense.gaprise.jp/blog/0051)

[Marketing](https://sisense.gaprise.jp/blog/topic/marketing) [UseCase](https://sisense.gaprise.jp/blog/topic/usecase) [Sisense](https://sisense.gaprise.jp/blog/topic/sisense) [BI](https://sisense.gaprise.jp/blog/topic/bi)

[**](https://sisense.gaprise.jp/blog/0051)

[![マーケティングチーム](https://sisense.gaprise.jp/hs-fs/hubfs/whatgraph/2e5b8005-5df1-437c-9bcd-a1555da7d44e.jpg?width=20&name=2e5b8005-5df1-437c-9bcd-a1555da7d44e.jpg) マーケティングチーム](https://sisense.gaprise.jp/blog/author/ギャプライズマーケティングチーム)

3 10, 2023

## [Sisense成功事例！保険業界のリーディングカンパニーがアナリティクスで...](https://sisense.gaprise.jp/blog/0050)

[Marketing](https://sisense.gaprise.jp/blog/topic/marketing) [UseCase](https://sisense.gaprise.jp/blog/topic/usecase) [Sisense](https://sisense.gaprise.jp/blog/topic/sisense) [BI](https://sisense.gaprise.jp/blog/topic/bi)

[**](https://sisense.gaprise.jp/blog/0050)

### 人気記事

<https://sisense.gaprise.jp/blog/0005>

[前年比成長率の算出方法](https://sisense.gaprise.jp/blog/0005) 2021年08月22日

<https://sisense.gaprise.jp/blog/0003>

[【Sisense使い方】MTD値、QTD値、YTD値の説明](https://sisense.gaprise.jp/blog/0003) 2021年08月21日

<https://sisense.gaprise.jp/blog/0006>

[SQLの中央値（Median）](https://sisense.gaprise.jp/blog/0006) 2021年08月22日

### タグ

[Sisense](https://sisense.gaprise.jp/blog/topic/sisense) [BI](https://sisense.gaprise.jp/blog/topic/bi) [UseCase](https://sisense.gaprise.jp/blog/topic/usecase) [Marketing](https://sisense.gaprise.jp/blog/topic/marketing)

[![Whatagraph_banner1](https://sisense.gaprise.jp/hs-fs/hubfs/Sisense/Whatagraph_banner1.png?width=320&name=Whatagraph_banner1.png)](https://whatagraph.gaprise.jp/)

 

[![monday_banner](https://sisense.gaprise.jp/hs-fs/hubfs/whatgraph/monday_300x250-300x250.jpg?width=360&name=monday_300x250-300x250.jpg)](https://monday.gaprise.jp/)

 

[![Powtoon_banner](https://sisense.gaprise.jp/hs-fs/hubfs/whatgraph/Powtoon_banner.png?width=360&name=Powtoon_banner.png)](https://powtoon.gaprise.jp)

 

[Tweets by @inboundplace](https://twitter.com/gaprise_martech)

[Previous Post](https://sisense.gaprise.jp/blog/0033)

##### [データ分析の手法をワンランクアップさせる5つのテクニック](https://sisense.gaprise.jp/blog/0033)

[Next Post](https://sisense.gaprise.jp/blog/0035)

##### [レポート作成の先にあるものとは？ DNVによるアプリケーションへのアナリティクスの導入で解ること](https://sisense.gaprise.jp/blog/0035)

[BUY *On* HUBSPOT](https://marketplace.hubspot.com/products/psdtohubspot/card-based-blog)

[![Sisense-Logo-Horizontal-combination (1)](https://sisense.gaprise.jp/hubfs/Sisense/Sisense-Logo-Horizontal-combination%20(1).svg "Sisense-Logo-Horizontal-combination (1)")](https://sisense.gaprise.jp/)

©All Rights Reserved