Slide 23
Slide 23 text
事例 Redshift サマリテーブル
マッハバイトでの Livesense Analytics 活用
● １PV単位での生ログを Redshift に保存
● 頑張れば応募経路の分析もできる
● 百行以上のSQLを操るディレクターさん
→ マッハバイト専用にサマリテーブルを先行実装
● １応募単位でのPV数や滞在時間・流入経路
● アトリビューションモデルによる広告効果分析
後に他事業部にも展開
WITH ss AS (
SELECT
TO_NUMBER(REGEXP_SUBSTR(s.landing_url,'\\d+'),'000000') AS client_id,
TO_CHAR(s.start_time,'YYYY-MM-DD') AS date,
s.session_id,
s.total_entry,
s.total_hit,
s.stay_time,
CASE WHEN s.device = 'smartphone' THEN 'sp' ELSE 'pc' END AS device
FROM
js.sessions AS s
WHERE
s.start_time >= '2016-07-01'
AND s.channel_type = 'organic'
AND s.source != 'Indeed'
AND s.landing_page_type = 'detail'
AND s.device IN ('pc', 'smartphone')
)
SELECT
ss.date,
ss.device,
COUNT(ss.session_id) AS sessions,
SUM(ss.total_entry) AS entries,
1.0 * SUM(ss.total_entry) / COUNT(ss.session_id) AS entry_rate,
1.0 * SUM(CASE WHEN ss.total_hit = 1 THEN 1 ELSE 0 END) / COUNT(ss.session_id)
AS bounce_rate,
AVG(ss.stay_time) AS avg_stay_time
FROM
ss
LEFT JOIN (
SELECT
TRUNC(ca.ddate) AS date,
ca.id,
ca.plan
FROM
jsen_archive.client_archives AS ca
WHERE
ca.ddate >= '2016-07-01')
AS ca_plan
ON ca_plan.id = ss.client_id AND ca_plan.date = ss.date
GROUP BY