Clever Geek Handbook

独自のコホート復帰レポート作成のガイド

LTV値を使用したコホートレポートの例







ユーザーリターンのコホート分析は、顧客のさまざまなグループ（顧客の行動とビジネスへの関連性）を理解するための強力な方法です。 ただし、ファイナルテーブルを初めて理解することは難しく、外出中にそれらを構築する方法を見つけ出すことはさらに難しくなります。







この記事では、テーブルのコホートを構築するための比較的単純だが有用なアルゴリズムについて説明し、 Python / PandasおよびSQLを使用したコードの概要も説明します 。 コホートレポートの作成をプログラムで実装する必要がある場合、またはこのアルゴリズムを知ることが非常に興味深い場合は、catに問い合わせてください。







はじめに



私の開発の1つは、企業分析のアプリケーションです。 また、再発に関するコホートレポートがおそらく最も重要な機能です。 私はそれを複数回書き直さなければならず、よりカスタマイズ可能で柔軟になりました。







開発中、このようなレポートの実装例は見つかりませんでした。そのため、以下で説明するアルゴリズムは、コホート分析の本質を理解して作成されました。 誰かが横棒を見つけたり、より良いオプションを知っている場合は、コメントでお知らせください。







条件付き定義 ：









コホート表の例









コホートテーブルをいくつかの手順で作成します。各ステップで新しいテーブルを取得し、それぞれに名前を付けました。







ステップ1.問題のステートメント、表Orders



注文の表Ordersがあり 、これに従ってコホート分析を行う必要があります。 構造は次のとおりです。 







//   date: DateTime, // ID  clientID: String, //   price: Int


ステップ2.顧客到着日が記載されたクライアントテーブル



クライアントの到着日を含むClientsテーブルを取得する必要があります。 







 date: DateTime, clientID: String


おそらく誰かがすでに持っています（たとえば、ユーザーの登録日やアプリケーションのインストール日など）が、最初の注文の日付としてカウントすることもできます。







コード例

Python： 







 Clients = pd.groupby(Orders, by=['clientID'], as_index=False) Clients = Clients.agg({ 'dt' : {'date' : 'min' }}) Clients.columns = cli.columns.droplevel() Clients.columns = ['clientID', 'date']


MySQL： 







 SELECT clientID, MIN(date) AS date FROM Orders GROUP BY clientID


ステップ3.注文とクライアントを組み合わせて組み合わせる



共通のclientIDフィールドを介して、 LeftタイプのOrdersテーブルとClientsテーブルをマージします。 混乱を避けるため、最初のテーブルの日付フィールドはdateOrと呼ばれ、2番目のテーブルはdateClと呼ばれます 。







次に、グループを取得するために日付を丸める必要があります。 1か月に切り上げて数値を戻し、任意の日付までの週数を計算できます。 結果として、日付は文字列でなければなりません。







ミックステーブル構造： 







 //   dateCl: String, //   dateOr: String, // ID  clientID: String, //   price: Int


コード例

Python： 







 Mix = pd.merge(Orders, Clients, how='left', on=['clientID']) Mix.columns = ['dateOr', 'clientID', 'price', 'dateCl'] def cutDate(txt): return txt[:7] Mix['dateOr'] = Mix['dateOr'].apply(cutDate) Mix['dateCl'] = Mix['dateCl'].apply(cutDate)


MySQL： 







 SELECT STRFTIME_UTC_USEC(Clients.date, "%Y-%m") AS dateCl, STRFTIME_UTC_USEC(Orders.date, "%Y-%m") AS dateOr, clientID, price FROM Clients INNER JOIN Orders ON (Clients.date = Orders.date)


ステップ4.グループ時間、表Preresult



最後に、コホートにアプローチします！ dateCl、dateOr 、 clientIDの 3つのフィールドに一度にグループ化します。







日付だけでなく、クライアントもなぜですか？
これがないと、どの期間でもアクティブな顧客の数を見つけることができません。 行数（ Count()



関数）は、特定の期間のコホート内の注文数のみを提供し、アクティブなクライアントの数を見つける方法はありません。


これらのフィールドに次を追加します。









全体の構造： 







 //   dateCl: String, //   dateOr: String, // ID  clientID: String, //  ,        ordersCount: Int, // ,           total: Int,


コード例

Python： 







 Preresult = pd.groupby(Mix, by=['tel', 'dateOr', 'dateCl'], as_index=False) Preresult = Preresult.agg({ 'price': { 'total': 'sum', 'ordersCount': 'count' } }) Preresult.columns = Preresult.columns.droplevel() Preresult.columns = ['clientID', 'dateOr', 'dateCl', 'total', 'ordersCount']


MySQL： 







 SELECT clientID, dateCl, dateOr, COUNT(*) AS ordersCount, SUM(price) AS total, FROM Mix GROUP BY dateCl, dateOr, clientID


ステップ5.グループ2、結果表



dateClとdateOrのみでグループ化することで、データの個人化を解除できます。 他のフィールドを追加します。









次の表がわかります。 







 //   dateCl: String, //   dateOr: String, //     clientsCount: Int, //  ,           ordersCount: Int, // ,             total: Int,


コード例

Python： 







 Result = pd.groupby(Preresult, by=['dateOr', 'dateCl'], as_index=False) Result = Result.agg({ 'total': { 'total': 'sum' }, 'ordersCount': { 'ordersCount': 'sum', 'clientsCount': 'count' } }) Result.columns = Result.columns.droplevel() Result.columns = ['dateOr', 'dateCl', 'total', 'ordersCount', 'clientsCount'])


MySQL： 







 SELECT dateCl, dateOr, COUNT(*) AS clientsCount, SUM(ordersCount) AS ordersCount, SUM(total) AS total FROM Preresult GROUP BY dateCl, dateOr


ステップ6.最終的な変換、ピボットコホートテーブル



次に、テーブル構造を変換する必要があります。行にはdateCl値、列にはdateOr 、セル内の目的の値（ clientsCount 、 ordersCount 、 totalなど）を含める必要があります。







これは何ですか

この操作はピボットテーブルと呼ばれます（ロシア語では、通常の用語はないようです）。 簡単な例： 







  xy val yx 1 2 1 1 5 1 5 - 1 3 8 -> 2 - 7 2 2 7 3 8 -


xの値から列が取得され、 yから行が取得されます。 ここでは、行x = 1、y = 3、val = 8があり、列（x）1に値（val）8の行（y）3のセルがありました。または行x = 2、y = 2 val = 7、値（val）7の行（y）2のセル列（x）2になりました。







通常、元のテーブルの行で値が記述されていないセルには、NULLまたは論理的に同等の値が入力されます。







コード例

Python： 







 #      clientsCount, ordersCount, total    Cohort = Preresult.pivot(index='dateCl', columns='dateOr', values='Data') #   null'   Cohort.fillna(0, inplace=True)


MySQL：

残念ながら、 MySQLは単純な方法でセルを行と列に変換する方法を知らないため、この目的で他の言語を使用しました。 しかし、誰かがこの方法を知っていれば-コメントを書いてくれて、感謝します。







可能な改善



  1. 列の名前を変更する

    現在、 dateOrとdateClは日付を独立して記述し、コホートテーブルの中心は上下に向けられています。 ただし、ステップ3、4、または5で操作dateOr-= dateClが実行される場合、このフィールドにはコホートの先頭からの日付が表示され、テーブルの中央が左向きになります。 

     * 09 10 11 * 0 1 2 09 3 2 1 09 3 2 1 10 - 3 2 -> 10 3 2 - 11 - - 3 11 3 - -
  2. 追加オプション

    注文表に他の興味深いパラメーターがある場合はどうしますか？ たとえば、支払いの種類やアフィリエイトIDは？ 非常に簡単です。これらのパラメーターは、 ステップ3、4、5の表（ Mix、Preresult、Result ）にも存在し、両方のグループのフィールドに追加する必要があります。 次に、 ステップ6で、パラメーターの可能な組み合わせごとに、 コホートテーブルを作成する必要があります。 たとえば、3つの支店と2つの支払い方法がある場合、3 * 2 = 6のコホートテーブルがあります。
  3. LTVを見つける

    長期統計を使用すると、 コホートテーブルの行を調べることにより、 ライフタイムバリューコホートを計算できます。


おわりに



再発のコホート表は、コホート分析の唯一のアプリケーションではなく、他のより視覚的なアプリケーションがあります。 たとえば、ある特性（コホート）に従ってユーザーを2つのグループに分割し、それらの特性を2つの独立した線としてグラフに表示します。







有用なトピック： Google Analyticsのコホート分析 。







すべての成功;）







More articles:


All Articles