【データ抽出/BigQuery】ユーザー毎の訪問頻度を集計する方法

使用テーブル

【log_table】

user_id	timestamp
A	2022-12-01 18:00:00
B	2022-12-01 18:10:00
A	2022-12-02 17:00:00
C	2022-12-02 17:10:00
A	2022-12-03 10:20:00
B	2022-12-03 10:20:00

■使用クエリ

/* 日付カラムを付与 */
with daily_col as (
    select
        user_id
        , timestamp
        , format_date("%Y-%m-%d", timestamp) as day
    from
        log_table
)
/* user_id毎にログの数をカウントする */
, daily_action_count as (
    select
        user_id
        , count(distinct day) as action_count
    from
        daily_col
    group by
        day
)
/* action_countの数値毎に */
select
    action_count
    , count(distinct user_id) as user_count
from
    daily_action_count
group by
    action_count

手順

1. 日付カラムを付与する。
2. user_id毎にdayカラムのデータの数をカウントして、ログ(action_count)の数を集計する。
3. action_count毎に、ユーザーの数をカウントする。

これで、ユーザー毎の訪問頻度（ログの数）を算出することができた。