Community

[分析ブロック]日別、週別、月別アクティブユーザー

:us:本記事は、このヘルプ記事を翻訳したものになります

このブロックについて

アクティブユーザーは、顧客が異なる期間でどのようにあなたのプロダクトにエンゲージしているかを測るのによく利用される指標です。日別、週別、月別アクティブユーザー分析ブロックパターン内では、私達は何人のユーザーが今日、過去7日間、過去30日以内にプロダクトに触れたか日別のカウントを集計するためにローリングタイムフレームを利用します。このブロックは以下のような質問に答えるために利用できます。

  • アクティブな顧客層は時間とともにどのように変化するか?
  • 私のアクティブユーザーの特徴はなにか?
  • 製品の変更はアクティブな顧客層にどのように影響するか?

理想的なデータ型

このブロックはGoogle Analytics, Segment, Snowplow などのカスタムイベントトラッキングシステム (各ユーザーイベントごとに行をもっているテーブルのような) からくるイベントデータを分析するのに最適な方法です。

期待されるアウトプット

このブロックは顧客を月別、週別、日別のアクティブユーザーにセグメント化するのに役立ちます。- つまり、各日において過去30日間、過去7日間、及びその日に購入したユーザーにセグメントかします。

注意: これは静的なデータセットであるため、特定の期間枠にフィルターを設定します。

`daily_use`派生テーブル (以下で詳細に説明)に、ユーザーデータテーブルを結合することにより、データを持っていればどのカテゴリごとにもアクティブユーザーを探索できます。例えば、こ過去90日間の週毎のアクティブユーザーを、サインアップ月ごとにコホート化できます。

やってみましょう

方法

最初のステップは、日付テーブルを作成します。このテーブルの構造は使用さているSQLダイアレクトによって異なります。以下の例では、Redshift, BigQuery, Snowflakeを使用します。 MySQLPostgreSQL に、この種のテーブルを作成するためのコミュニティの投稿があります。

日付に必要な行よりも大量の行がある大きなテーブル(ここでは`order_items`)を活用できます。私達は日付テーブルを作成するために window 関数 row_numberを利用しして一連の整数を生成します。

次に、ユーザーが購入したすべての日に対する`user_id`とdateを持っているdaily_useSQLベースの派生テーブル を構築します。この派生テーブルでは、dateテーブルとdaily_useテーブルをクロスジョインします。カレンダーの日付は使用日と使用日+30日の間にあります。これにより、各カレンダー日付について、ユーザーが30日前までアクティブだったかどうかを判断できます。Exploreでは次のようになります。

新しいカレンダー日付はそれぞれ、最終アクションからの日付を増やします。上記の例では、ユーザー1は1/7にアクティブだったことがわかります。しかし、その後アクティブな日はありません。この`daily_use`テーブルは、クエリパフォーマンスを増やすために以下のコードにあるように永続的です。

最後に、daily_use派生テーブルビュー(active_users)にディメンションとメジャーを追加します。これにより月別、週別、日別アクティブユーザーのカウントとドリルができます。

サンプルコード

Redshift:

explore: active_users {
 join: users {
   type: left_outer
   sql_on: ${active_users.user_id} = ${users.id} ;;
   relationship: many_to_one
 }
}

view: dates {
 derived_table: {
   distribution_style: all
   sortkeys: ["date"]
   sql_trigger_value: GETDATE() ;;
   sql: -- ## 1) Create a Date table with a row for each date.
     SELECT '2001-01-01'::DATE + d AS date
     FROM
      (SELECT ROW_NUMBER() OVER(ORDER BY id) -1 AS d FROM orders ORDER BY id LIMIT 20000) AS d
     ;;
 }
}

view: active_users {
 derived_table: {
   sql_trigger_value: GETDATE();;
   distribution: "user_id"
  sortkeys: ["date"]
  sql: WITH daily_use AS (
      -- ## 2 ) Create a table of days and activity by user_id
      SELECT
        user_id
        , DATE_TRUNC('day', created_at) as activity_date
      FROM orders
      GROUP BY 1, 2
    )
    --  ## 3) Cross join activity and dates to build a row for each user/date combo with
    -- days since last activity
    SELECT
          daily_use.user_id
        , wd.date as date
        , MIN(wd.date::date - daily_use.activity_date::date) as days_since_last_action
    FROM ${dates.SQL_TABLE_NAME} AS wd
    LEFT JOIN daily_use
        ON wd.date >= daily_use.activity_date
        AND wd.date < daily_use.activity_date + interval '30 day'
    GROUP BY 1,2
     ;;
}

dimension: date {
  type: date
  sql: ${TABLE}.date ;;
}

dimension: user_id {
  type: number
  sql: ${TABLE}.user_id ;;
}

dimension: days_since_last_action {
  type: number
  sql: ${TABLE}.days_since_last_action ;;
  value_format_name: decimal_0
}

dimension: active_this_day {
  type: yesno
  sql: ${days_since_last_action} <  1 ;;
}

dimension: active_last_7_days {
  type: yesno
  sql: ${days_since_last_action} < 7 ;;
}

measure: user_count_active_30_days {
  label: "Monthly Active Users"
  type: count_distinct
  sql: ${user_id} ;;
  drill_fields: [users.id, users.name]
}

measure: user_count_active_this_day {
  label: "Daily Active Users"
  type: count_distinct
  sql: ${user_id} ;;
  drill_fields: [users.id, users.name]

  filters: {
    field: active_this_day
    value: "yes"
  }
}

measure: user_count_active_7_days {
  label: "Weekly Active Users"
  type: count_distinct
  sql: ${user_id} ;;
  drill_fields: [users.id, users.name]

  filters: {
    field: active_last_7_days
    value: "yes"
  }
}
}

BigQuery:

view: dates {
 derived_table: {

   sql_trigger_value: SELECT CURRENT_DATE() ;;
   sql: 
     SELECT cast(date as date) as date
FROM UNNEST(GENERATE_DATE_ARRAY(DATE_SUB(CURRENT_DATE, INTERVAL 5 YEAR), CURRENT_DATE)) date
      ;;
 }
}

view: active_users {
  derived_table: {
  sql_trigger_value: SELECT CURRENT_DATE();;

    sql: WITH daily_use AS (
      SELECT
        user_id as user_id
        , cast(TIMESTAMP_TRUNC(transactiondate,day) as date) as activity_date
      FROM users
      GROUP BY 1, 2
      )
 
      SELECT
        daily_use.user_id
       , wd.date as date
       , MIN( DATE_DIFF(wd.date, daily_use.activity_date, day) ) as days_since_last_action
      FROM ${dates.SQL_TABLE_NAME} AS wd
      CROSS JOIN daily_use
        WHERE wd.date BETWEEN daily_use.activity_date AND DATE_ADD(daily_use.activity_date, INTERVAL 30 DAY)
      GROUP BY 1,2
        ;;
   }

dimension_group: date {
   type: time
   timeframes: [date,month,quarter,quarter_of_year,year,raw]
   sql: CAST( ${TABLE}.date AS TIMESTAMP);;
 }

 dimension: user_id {
   type: number
   sql: ${TABLE}.user_id ;;
 }

 dimension: days_since_last_action {
   type: number
   sql: ${TABLE}.days_since_last_action ;;
   value_format_name: decimal_0
 }

 dimension: active_this_day {
   type: yesno
   sql: ${days_since_last_action} < 1 ;;
 }

 dimension: active_last_7_days {
   type: yesno
   sql: ${days_since_last_action} < 7 ;;
 }

 measure: user_count_active_30_days {
   label: "Monthly Active Users"
   type: count_distinct
   sql: ${user_id} ;;
   drill_fields: [users.id, users.name]
 }

 measure: user_count_active_this_day {
   label: "Daily Active Users"
   type: count_distinct
   sql: ${user_id} ;;
   drill_fields: [users.id, users.name]

   filters: {
     field: active_this_day
     value: "yes"
   }
 }

 measure: user_count_active_7_days {
   label: "Weekly Active Users"
   type: count_distinct
   sql: ${user_id} ;;
   drill_fields: [users.id, users.name]

   filters: {
     field: active_last_7_days
     value: "yes"
   }
 }
}

Snowflake:

    view: event_active_users_pdt {
    derived_table: {
    sql:
    WITH dau AS (
    SELECT TO_DATE(CREATED_AT) as event_date, COUNT(DISTINCT USER_ID) AS dau
    FROM SCHEMA.EVENTS
    WHERE -- <.. filter criteria...>
    GROUP BY event_date
    )
    SELECT event_date, dau,
    (SELECT COUNT(DISTINCT USER_ID)
    FROM SCHEMA.EVENTS
    WHERE -- <... filter criteria...>
    AND TO_DATE(CREATED_AT) BETWEEN DATEADD(DAY, -29, dau.event_date) AND dau.event_date
    ) AS mau,
    (SELECT COUNT(DISTINCT USER_ID)
    FROM SCHEMA.EVENTS
    WHERE -- <... filter criteria...>
    AND TO_DATE(CREATED_AT) BETWEEN DATEADD(DAY, -7, dau.event_date) AND dau.event_date
    ) AS wau
    FROM dau
    ;;
    datagroup_trigger: events_datagroup
    }

    dimension: event_active_users_pk {
    type: string
    primary_key: yes
    hidden: yes
    sql: TO_VARCHAR(${event_date}, 'YYYYMMDD');;
    }

    dimension_group: event {
    type: time
    timeframes: [
    raw,
    date,
    day_of_week,
    day_of_month,
    day_of_year,
    week,
    week_of_year,
    month,
    quarter,
    year
    ]
    sql: ${TABLE}."EVENT_DATE" ;;
    }

    dimension: dau {
    label: "DAU"
    type: number
    sql: ${TABLE}."DAU" ;;
    }

    dimension: mau {
    label: "MAU"
    type: number
    sql: ${TABLE}."MAU" ;;
    }

    dimension: wau {
    label: "WAU"
    type: number
    sql: ${TABLE}."WAU" ;;
    }

    dimension: dau_mau {
    label: "DAU/MAU"
    type: number
    value_format_name: decimal_2
    sql: 1.0 * (${dau} / nullif(${mau}, 0)) ;;
    }

    dimension: wau_mau {
    label: "WAU/MAU"
    type: number
    value_format_name: decimal_2
    sql: 1.0 * (${wau} / nullif(${mau}, 0)) ;;
    }

    dimension: dau_wau {
    label: "DAU/MAU"
    type: number
    value_format_name: decimal_2
    sql: 1.0 * (${dau} / nullif(${wau}, 0)) ;;
    }

    measure: count {
    label: "Number of Records"
    type: count
    drill_fields: [detail*]
    }

    measure: average_dau_mau {
    label: "Average DAU/MAU"
    type: average
    sql: ${dau_mau} ;;
    value_format_name: decimal_2
    drill_fields: [detail*]
    }

    measure: average_wau_mau {
    label: "Average WAU/MAU"
    type: average
    sql: ${wau_mau} ;;
    value_format_name: decimal_2
    drill_fields: [detail*]
    }

    measure: average_dau_wau {
    label: "Average DAU/WAU"
    type: average
    sql: ${dau_wau} ;;
    value_format_name: decimal_2
    drill_fields: [detail*]
    }

    measure: min_dau_mau {
    label: "Min DAU/MAU"
    type: min
    sql: ${dau_mau} ;;
    value_format_name: decimal_2
    drill_fields: [detail*]
    }

    measure: min_wau_mau {
    label: "Min WAU/MAU"
    type: min
    sql: ${wau_mau} ;;
    value_format_name: decimal_2
    drill_fields: [detail*]
    }

    measure: min_dau_wau {
    label: "Min DAU/WAU"
    type: min
    sql: ${dau_wau} ;;
    value_format_name: decimal_2
    drill_fields: [detail*]
    }

    measure: max_dau_mau {
    label: "Max DAU/MAU"
    type: max
    sql: ${dau_mau} ;;
    value_format_name: decimal_2
    drill_fields: [detail*]
    }

    measure: max_wau_mau {
    label: "Max WAU/MAU"
    type: max
    sql: ${wau_mau} ;;
    value_format_name: decimal_2
    drill_fields: [detail*]
    }

    measure: max_dau_wau {
    label: "Max DAU/WAU"
    type: max
    sql: ${dau_wau} ;;
    value_format_name: decimal_2
    drill_fields: [detail*]
    }

    set: detail {
    fields: [event_date, dau, mau, wau]
    }