BigQueryでFirebaseアクティブユーザー数の推移を出す

Contents

時間帯別アクティブユーザー数

時間帯別アクティブユーザー数

利用したデータ

BigQueryの一般公開データセットになります。このデータを利用するにはこちらの記事を参照ください。

firebase analyticsのイベントデータから、時間帯ごとのアクティブユーザー数を求めます。結果は以下のようなグラフになります。

求め方

クエリ

-- firebaseの公開データセット firebase-public-projectより
-- 2018-09-01〜2018-09-30の期間の時間ごとの
-- アクティブなUU数を集計する
WITH firebase_events AS  (
  SELECT
    CAST
    (
      FORMAT_TIMESTAMP(
        '%Y-%m-%d %H:%M:%S',
        TIMESTAMP_SECONDS(CAST(CAST(event_timestamp AS  INT64)/1000000 AS  INT64))
      ) AS timestamp
    ) AS event_timestamp,
    user_pseudo_id,
  FROM
    `firebase-public-project.analytics_153293282.events_*`
  WHERE
    _table_suffix BETWEEN FORMAT_DATE('%Y%m%d', '2018-09-01') AND FORMAT_DATE('%Y%m%d', '2018-09-30')
    AND event_name = 'user_engagement'
)

, firebase_events_extract AS (
  SELECT
    EXTRACT(YEAR from event_timestamp) AS  year,
    EXTRACT(MONTH from event_timestamp) AS  month,
    EXTRACT(DAY from event_timestamp) AS  day,
    EXTRACT(DAYOFWEEK from event_timestamp) AS  day_of_week,
    EXTRACT(HOUR from event_timestamp) AS  hour,
    user_pseudo_id,
  FROM
    firebase_events
)

, firebase_events_agged AS (
  SELECT
    year,
    month,
    day,
    hour,
    user_pseudo_id,
  FROM
    firebase_events_extract
  GROUP BY
    year, month, day, hour, user_pseudo_id
)

SELECT
  year,
  month,
  day,
  hour,
  count(*) AS  uu,
FROM
  firebase_events_agged
GROUP BY
  year, month, day, hour
ORDER BY
  year, month, day, hour