【トレジャーデータ】自社サイトから他社サイトへ遷移する日数の分布集計（日数差分はdate_diffを用いる）

前置き。。

• 遷移日数の分布を見ればユーザの行動変容を読み取れる

自社サイトから他社サイトへ遷移するまでの期間を知ることは、自社サイトを通じたユーザの行動変容を知る上で大変有用。例えば、他社サイト→自社サイト、自社サイト→他社サイトとの遷移期間の分布がそれぞれ同様の構造を持っていれば、自社サイトを通じた行動変容は起こっていないと言える。一方、自社サイト→他社サイトの遷移で遷移期間に対するUUの減衰の変化、例えば、通常、遷移期間が長くなるほど右肩下がりに減衰するはずのUUがある特定の期間で増加するような現象が見られる場合、その特定に期間においてユーザの行動変容が起きているという仮説を立てることができる。この仮説を設定することで、ユーザの行動変容が起きるタイミングで広告配信をおこなう等の施策が設定可能となる。ただ、効果の高い施策を実施することのみを目的とする場合、基本的には、自社サイト→他社サイトへの遷移は24時間以内に起きるのが圧倒的に多いため、自社サイト訪問直後にアプローチするのが最も高い効果が期待される。

下記は、他社サイト→自社サイト、自社サイト→他社サイトへの遷移期間を集計するSQL。

 ---start---
WITH mysite_with AS(
  SELECT
    MIN(TD_TIME_FORMAT(time,
        'yyyy-MM-dd HH:mm:ss',
        'JST')) AS date_time,
    user_id
  FROM
    log_db.log_mysite
  WHERE
    TD_TIME_RANGE(time,
      '2016-06-01',
      '2016-10-01',
      'JST')ｃ
  GROUP BY
    user_id
),
othersite_with AS(
  SELECT
    MIN(TD_TIME_FORMAT(time,
        'yyyy-MM-dd HH:mm:ss',
        'JST')) AS date_time,
    user_id
  FROM
    log_db.log_othersite
  WHERE
    TD_TIME_RANGE(time,
      '2016-06-01',
      '2016-10-01',
      'JST')
    AND brand = 'brand_name'-- ブランド名を指定
  GROUP BY
    user_id
) SELECT
  date_diff(
    'day', -- 他にも minute, hour, week, monthなども指定できる
    CAST(
      mysite_with.date_time AS TIMESTAMP
    ),
    CAST(
      othersite_with.date_time AS TIMESTAMP
    )
  ) AS time_diff,
  COUNT(DISTINCT mysite_with.user_id) AS uu
FROM (mysite_with)
JOIN
  (othersite_with)
  ON (
    mysite_with.user_id = othersite_with.user_id
  )
GROUP BY
  date_diff(
    'day',
    CAST(
      mysite_with.date_time AS TIMESTAMP
    ),
    CAST(
      othersite_with.date_time AS TIMESTAMP
    )
  )
ORDER BY
  time_diff
 ---end---


Prestoでの期間計算（date_diff）の詳細は下記参照

• Hadoop利用者ならきっと知ってる、Hive/Prestoクエリ関数の挙動の違い
• 6.10. Date and Time Functions and Operators