時点での属性情報の取得方法

データマネジメントチームの藤井 温子です。
この記事では、データを集計・分析する際に便利な「時点での属性情報の取得」について具体例を交えながらまとめています。データ分析を行う際に「ある時点での属性情報を集計したい」といったことはありませんか？例えば、「購買日時点での、ユーザーの会員ステータスや累計購買額」など、属性情報が時点によって変化する場合がこれにあたります。このようなケースは、JOIN条件で時点を限定することで簡単に実現できます。2つの例を挙げながら見ていきましょう。

【例1】ある購買日時点での、会員のステータスを属性情報として取得する

やりたいこと

2020年4月に購買があった会員の、購買時点での年齢・会員ランク・クラスタを集計したい

使うデータ

1. 購買テーブル (transaction)
   トランザクションデータ。誰が、いつ、何を買ったかが時系列で記載されている。
2. 会員テーブル(member)
   属性データだが、生年月日や購買状況から、3ヶ月に1度「年齢」「会員ランク」「クラスタ」が更新されて追記される。

購買日時点での年齢・ランク・クラスタを抽出するクエリは以下のようになります。

SELECT
   t1.member
   , t2.age
   , t2.rank
   , t2.cluster
FROM
   purchase AS t1
       INNER JOIN member AS t2
           ON
               t1.member_id = t2.member_id
               AND TD_TIME_RANGE(t1.time, t2.apply_from, t2.apply_to, 'JST')
WHERE
   TD_TIME_RANGE(t1.time, '2020-04-01','2020-05-01', 'JST')

JOIN条件（ON以降）で、購買日時点での最新の会員ステータス更新日のレコードと紐づくようにすることで、購買日時点での会員属性（年齢、ランク、クラスタ）を取得することができます。

【例2】CV発生直前のWeb行動を属性情報として取得する

やりたいこと

2020年4月に資料請求したユーザーの、直前のWeb行動を取得し、流入元や閲覧ページの分析をしたい

使うデータ

1. ウェブログテーブル (Weblog)
   トランザクションデータ。誰が、いつ、どのページを閲覧したかについて全件格納されている。
2. イベントテーブル(event)
   資料請求・申し込みなどのイベントデータ。
   こちらもトランザクションデータで、誰が、いつ、どのイベントを実行したかについて全件格納されている。

資料請求直前（1日前〜資料請求日時まで）のセッションでのランディングページ・流入元を抽出するクエリは以下のようになります。

-- 1セッション1レコード（日付またぎのセッションは2レコード）のリストを作成
WITH session_list AS (
 SELECT
   session_id
   , client_id
   , TD_DATE_TRUNC('day', pv_time, 'JST') as pv_date　
   , MIN(start_time) as session_start_time
 FROM
   weblog
 WHERE
   TD_TIME_RANGE(time, '2020-04-01', '2020-05-01', 'JST')
 GROUP BY
   td_session_id
   , client_id
   , TD_DATE_TRUNC('day', pv_time, 'jst')
),
 
-- 資料請求のイベントとsession_idを紐付ける
event_session_id AS (
 SELECT
   t1.client_id
   t1.event_category
   , t1.time AS event_time
   , MAX_BY(t2.session_id, t2.session_start_time) AS session_id
 FROM
   event_log AS t1
       LEFT JOIN session_list AS t2
       ON
           t1.client_id = t2.client_id
           and TD_DATE_TRUNC('day', t1.time, 'JST') = t2.pv_date
           and t1.time > session_start_time
 GROUP BY
   t1.client_id
   , t1.event_category
   , t1.event_time
 WHERE
   event_category = '資料請求'
)
 
-- session_idをキーにしてウェブログとイベントログを紐付ける
SELECT
   t1.session_id
   , t1.client_id
   , t1.start_time
   , t1.url
   , t1.channel_group
   , t2.event_time
FROM
   weblog AS t1
       INNER JOIN event_session_id AS t2
           ON t1.session_id = t2.session_id
WHERE
   TD_TIME_RANGE(t1.time, '2020-04-01', '2020-05-01', 'JST')

event_session_idの一時テーブルで、JOIN条件（ON以降）を、イベント発生日＝ウェブログのPV発生日かつ、イベント発生時刻>セッション開始時刻とすることで、資料請求直前のセッションIDのみをイベントと紐付けて取得することができます。データを集計・分析する際には、時間の取り扱いが重要になります。慣れてしまえば簡単ですが、時点でデータを抽出するのもテクニックの1つとなりますので、今回解説させていただきました。

少しでもご参考になりましたら幸いです。