データ活用

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

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

利用したデータ

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

結果

可視化

Googleデータポータルを利用

最適なタイミングで施策を打つ

例えばユーザーがもっとも多い時間帯にキャンペーン施策を打つことでボリュームゾーンを狙え、キャンペーン効果を最大化できそうです。

逆に、大きな機能をリリースする場合、ユーザーが少ない時間帯を選んでリリースすることで、最悪不具合が起きたとしても売上の悪影響を最小限にできそうです。

参考

ビッグデータ分析
created by Rinker
¥4,180
(2022/09/26 16:59:15時点 Amazon調べ-詳細)

created by Rinker
技術評論社
¥2,398
(2022/09/26 18:09:01時点 Amazon調べ-詳細)