質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.13%

日付毎にその日から一週間の間のアクティブユーザー数を集計したい

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 1,757

akm2929

score 12

完成イメージ↓

日付 WAU MAU
2018/09/09 200 500
2018/09/10 180 480

WAU・・・日付−7日間の間に一度でもログインしたユニークユーザー数
MAU・・・日付−30日間の間に一度でもログインしたユニークユーザー数

何を使ってどう集計すればいいのでしょうか?
ちなみにDAUは以下のように出しています。

with T as (
    select
      parse_date("%Y%m%d", event_date) event_date
      , user_id
      , event_name
    from
      `sample*`
    where(
      _TABLE_SUFFIX BETWEEN
       "20180707" AND
      FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    )
    and
        app_info.install_source = 'iTunes'
    and
        date(TIMESTAMP_ADD(timestamp_micros(user_first_touch_timestamp), interval device.time_zone_offset_seconds SECOND)) > "2018-07-06"
    and
        user_id is not null
)

,dau as (
  select 
  event_date
  ,count(distinct(user_id)) as dau_count 
  from 
  T
  group by 
  event_date
)


user_idはイベント毎に計測されるため、元データでは重複があります。

<↓saziさんの回答を自分なりに解釈して書いたコード(コピペだとエラーで、saziさんの書き方が高度でどう変えればいいかわかりませんでした。。。)>
エラーはないのですが、dau、wau、mauの数字がほぼ同じになってしまいました。

with T as (
    select
      parse_date("%Y%m%d", event_date) event_date
      , user_id
      , event_name
    from
      `sample*`
    where(
      _TABLE_SUFFIX BETWEEN
       "20180707" AND
      FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    )
    and
        app_info.install_source = 'iTunes'
    and
        date(TIMESTAMP_ADD(timestamp_micros(user_first_touch_timestamp), interval device.time_zone_offset_seconds SECOND)) > "2018-07-06"
    and
        user_id is not null
    limit 10000000
)

, dau as (
  select
    event_date
    ,count(distinct(user_id)) as dau_count
  from
    T
  group by
    event_date
)

, wau as (
  select
     event_date
    ,count(distinct(user_id)) as wau_count
  from
    T
  where
    event_date between t.event_date and date_add(t.event_date,interval 7 day)
  group by event_date
)

, mau as (
  select
    event_date
    ,count(distinct(user_id)) as mau_count
  from
    T
  where
    event_date between t.event_date and date_add(t.event_date,interval 1 month)
  group by event_date
)

, before_formating as (
  select
    dau.event_date
    ,dau_count
    ,wau_count
    ,mau_count
  from
    dau
  left join
    wau
  on
    dau.event_date = wau.event_date
  left join
    mau
  on
    dau.event_date = mau.event_date
)
select *
from before_formating
order by event_date asc
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • yambejp

    2018/11/19 17:56 編集

    Tテーブルのサンプルデータを提示して下さい。とくにuser_idはevent_date毎にユニークなのかそれとも1日何度もログインできるのか提示してください

    キャンセル

  • akm2929

    2018/11/19 18:03

    すいません、追記させていただきました。user_idは一日に複数回カウントされます。不足失礼しました。

    キャンセル

回答 1

checkベストアンサー

0

試せてませんが、DATE_ADDを使用すると以下のようになるかと。

  select event_date
       , (select count(distinct(user_id)) from T 
          where event_date between t1.event_date and DATE_ADD(t1.event_date, INTERVAL -7 DAY)) 
         ) as wau_count 
       , (select count(distinct(user_id)) from T 
          where event_date between t1.event_date and DATE_ADD(t1.event_date, INTERVAL -1 MONTH)) 
         ) as mau_count 
from (
  select event_date
  from  T
  group by event_date
) t1

追記

追加された記述より展開

with T as (
    select
        date(event_date) event_date
      , user_id
      , event_name
    from
      `sample*`
    where(
      _TABLE_SUFFIX BETWEEN
       "20180707" AND
      FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    )
    and
        app_info.install_source = 'iTunes'
    and
        date(TIMESTAMP_ADD(timestamp_micros(user_first_touch_timestamp), interval device.time_zone_offset_seconds SECOND)) > "2018-07-06"
    and
        user_id is not null
    limit 10000000
)
, dau as (
  select
     event_date
    ,count(distinct(user_id)) as dau_count
  from
    T
  group by
    event_date
)
, base_date as(
  select event_date from T group by event_date
 )

, wau as (
  select
      base_date.event_date
    , count(distinct(T.user_id)) as wau_count
  from base_date inner join T
       on T.event_date between base_date.event_date and date(date_add(date base_date.event_date,interval 7 day))
  group by base_date.event_date
)
, mau as (
  select
      base_date.event_date
    , count(distinct(T.user_id)) as wau_count
  from base_date inner join T
       on T.event_date between base_date.event_date and date(date_add(date base_date.event_date,interval 1 month))
  group by base_date.event_date
)
, before_formating as (
  select
    dau.event_date
    ,dau_count
    ,wau_count
    ,mau_count
  from
    dau
  left join
    wau
  on
    dau.event_date = wau.event_date
  left join
    mau
  on
    dau.event_date = mau.event_date
)
select *
from before_formating
order by event_date asc


BigQueryでは相関問い合わせは使用しない方が良いみたいですね。
BigQueryで相関サブクエリがうまく動かなかった話

追記

予め、範囲となる日付を求めておくように修正

with T as (
    select
        date(event_date) event_date
      , date(date_add(event_date,interval 7 day)) wau_range_date
      , date(date_add(event_date,interval 1 month)) mau_range_date
      , user_id
      , event_name
    from `sample*`
    where    (_TABLE_SUFFIX BETWEEN "20180707" AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)))
      and    app_info.install_source = 'iTunes'
      and date(TIMESTAMP_ADD(timestamp_micros(user_first_touch_timestamp), interval device.time_zone_offset_seconds SECOND)) > "2018-07-06"
      and user_id is not null
    limit 10000000
)
, dau as (
  select
     event_date
    ,count(distinct(user_id)) as dau_count
  from
    T
  group by
    event_date
)
, base_date as(
  select event_date from T group by event_date
 )

, wau as (
  select
      base_date.event_date
    , count(distinct(T.user_id)) as wau_count
  from base_date inner join T
       on base_date.event_date between T.event_date and T.wau_range_date
  group by base_date.event_date
)
, mau as (
  select
      base_date.event_date
    , count(distinct(T.user_id)) as wau_count
  from base_date inner join T
       on base_date.event_date between T.event_date and T.mau_range_date
  group by base_date.event_date
)
, before_formating as (
  select
    dau.event_date
    ,dau_count
    ,wau_count
    ,mau_count
  from
    dau
  left join
    wau
  on
    dau.event_date = wau.event_date
  left join
    mau
  on
    dau.event_date = mau.event_date
)
select *
from before_formating
order by event_date asc


単にユーザーのIDをカウントするだけなので、シンプルになりそう。

with T as (
    select
        date(event_date) event_date
      , date(date_add(event_date,interval 7 day)) wau_range_date
      , date(date_add(event_date,interval 1 month)) mau_range_date
      , user_id
      , event_name
    from `sample*`
    where    (_TABLE_SUFFIX BETWEEN "20180707" AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)))
      and    app_info.install_source = 'iTunes'
      and date(TIMESTAMP_ADD(timestamp_micros(user_first_touch_timestamp), interval device.time_zone_offset_seconds SECOND)) > "2018-07-06"
      and user_id is not null
    limit 10000000
)
select
    dau.event_date
  , count(distinct(dau.user_id)) as dau_count
  , count(distinct(wau.user_id)) as wau_count
  , count(distinct(mau.user_id)) as mau_count
from  T as dau
      inner join T as wau
      on  dau.event_date between wau.event_date and wau.wau_range_date
      inner join T as mau
      on  dau.event_date between mau.event_date and mau.wau_range_date
group by dau.event_date
order by dau.event_date asc

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/11/21 17:52

    ありがとうございます…!

    キャンセル

  • 2018/11/22 17:23

    詳細な回答ありがとうございました!!

    キャンセル

  • 2018/11/22 17:28

    最後ので、上手くいきましたか?

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.13%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる