注目の投稿

【kepler.gl】コロナ対策による人流の変化も地図上に可視化(各種メディアで報道)

kepler.glのサイト画面 kepler.glを使ってコロナ対策の効果を分析したところ、テレビ、新聞、ネットのメディアから問い合わせや報道依頼が殺到。今も、土日返上で都内や全国の人流変化を分析しています。この記事では人流変化の可視化に便利なkepler.glにつ...

2017年2月14日火曜日

【トレジャーデータ:Presto】各ユーザの曜日別アクセス率を集計してライフスタイルに合ったアプローチをする

ユーザ一人ひとりのライフスタイルに合ったアプローチをしたいと考えたとき、まずは、各ユーザの曜日別のアクセス率を把握することが有効。各ユーザの曜日別アクセス率が分かれば、一人ひとりのユーザに適切なタイミングでコンテンツを提供できる可能性を高めることができる。

◇各ユーザの曜日別アクセス率の集計イメージ

user_id
100010.5000000.5
10002000.80.2000
100030.10.10.10.20.10.10.3
100040000100


◇集計例(Presto)

WITH sq_data AS(
  SELECT
    DOW(
      --DOWで日付から曜日(1~7:月~日)を得る
      FROM_UNIXTIME(TD_TIME_PARSE(TD_TIME_FORMAT(time,
            'yyyy-MM-dd',
            'jst')))
    ) AS weekly,
    user_id,
    CAST(--後で比率を計算するため浮動小数点型に変換する
      COUNT(0) AS DOUBLE
    ) AS pv
  FROM
    access_log
  WHERE
    AND TD_TIME_RANGE(time,
      '2017-01-01',
      '2017-02-01',
      'jst')
  GROUP BY
    DOW(
      FROM_UNIXTIME(TD_TIME_PARSE(TD_TIME_FORMAT(time,
            'yyyy-MM-dd',
            'jst')))
    ),
    user_id
  ORDER BY
    weekly,
    user_id
),
sq_week_pv AS(
  SELECT
    user_id,
    COALESCE(
      --nullの場合は0とする
      SUM(CASE
          WHEN weekly = 1 THEN pv END),
      0
    ) AS Mon,
    COALESCE(
      SUM(CASE
          WHEN weekly = 2 THEN pv END),
      0
    ) AS Tue,
    COALESCE(
      SUM(CASE
          WHEN weekly = 3 THEN pv END),
      0
    ) AS Wed,
    COALESCE(
      SUM(CASE
          WHEN weekly = 4 THEN pv END),
      0
    ) AS Thurs,
    COALESCE(
      SUM(CASE
          WHEN weekly = 5 THEN pv END),
      0
    ) AS Fri,
    COALESCE(
      SUM(CASE
          WHEN weekly = 6 THEN pv END),
      0
    ) AS Sat,
    COALESCE(
      SUM(CASE
          WHEN weekly = 7 THEN pv END),
      0
    ) AS Sun,
    SUM(pv) AS pv
  FROM
    sq_data
  GROUP BY
    user_id
)
SELECT
  user_id,
  --比率の計算
  Mon / pv AS Mon,
  Tue / pv AS Tue,
  Wed / pv AS Wed,
  Thurs / pv AS Thurs,
  Fri / pv AS Fri,
  Sat / pv AS Sat,
  Sun / pv AS Sun
FROM
  sq_week_pv

0 件のコメント :

コメントを投稿

注: コメントを投稿できるのは、このブログのメンバーだけです。