HOW TO TD(User Engagement)Treasure Data User Engagement

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

ホーム » 時点での属性情報の取得方法

データマネジメントチームの藤井 温子です。
この記事では、データを集計・分析する際に便利な「時点での属性情報の取得」について具体例を交えながらまとめています。

データ分析を行う際に「ある時点での属性情報を集計したい」といったことはありませんか?
例えば、「購買日時点での、ユーザーの会員ステータスや累計購買額」など、属性情報が時点によって変化する場合がこれにあたります。

このようなケースは、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つとなりますので、今回解説させていただきました。
少しでもご参考になりましたら幸いです。

藤井 温子

Data Managementチーム

新卒でデジタルマーケティング支援企業に入社し、UXデザインコンサルタントとして大手保険会社、食品メーカー、機器メーカー等に向けたユーザーリサーチ・WebサイトのUX・UI改善等のプロジェクトを担当。その後、同社のデータ分析系SaaSのカスタマーサクセスの立ち上げメンバーとして、トレーニングプログラムの立ち上げやお客様の活用支援に従事。よりテクニカル領域に関わりたいと思い、2020年にトレジャーデータに参画。データマネジメントチームにて、Treasure Data CDPの構築や施策のデータ集計等、データ基盤の設計・開発・運用支援に従事。

得意領域 : データ活用・分析支援、ウェブ解析、ユーザーリサーチ(定性調査)、コンサルティング

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

Back to top button