年始ようやくの記事ですね、今回はBigQuery記事です。
(いくつかリリースしようとして下書きに何本かは書いているんです…。)
BigQueryならではのポイントを探している中で、表題のテーマが出てきました。
お客さんのオーダーとかもありますし、企業の文化もあるので、正解の無い仕事だなとは思います。
ただ、個人的には最近順位等をシビアに追うよりも、エンゲージメント面に気を使い、後々順位がついてくれば良いかなと思うことが多くてですね。
同じようなことお話しされるトッププレイヤーさんたちも多いのですが、心のどこかでは「わかるけど、綺麗事だよなぁ」と思ってました。この話も長くなってしまうのでまたどこかでw。
お客さんに向き合うと、正解も色々な形がありますからね。(もちろんモラルは遵守してね。)
では早速。
目次
SQL(初回訪問からコンバージョンまでのリードタイム & 訪問回数)
DECLARE cv_event_name STRING DEFAULT '問い合わせ完了';
WITH predata AS (
SELECT
user_pseudo_id,
SAFE_CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS INT64) AS session_id,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') AS source,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') AS medium,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'campaign') AS campaign,
event_name,
event_timestamp
FROM `chromatic-craft-435212-u5.analytics_255569927.events_*` -- 修正: バッククォートで囲む
),
first_visit AS (
SELECT
user_pseudo_id,
MIN(event_timestamp) AS first_visit_time
FROM predata
GROUP BY user_pseudo_id
),
first_source_data AS (
SELECT
user_pseudo_id,
source AS first_source,
medium AS first_medium,
campaign AS first_campaign,
page AS landing_page, -- 初回訪問時のランディングページを追加
event_timestamp,
ROW_NUMBER() OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp ASC) AS rn
FROM predata
WHERE source IS NOT NULL
),
cv_conversions AS (
SELECT
user_pseudo_id,
session_id,
MIN(event_timestamp) AS conversion_time -- 同じセッション内で最初のCVのみ取得
FROM predata
WHERE event_name = cv_event_name
GROUP BY user_pseudo_id, session_id
),
session_counts AS (
SELECT
user_pseudo_id,
COUNT(DISTINCT session_id) AS total_sessions
FROM predata
GROUP BY user_pseudo_id
)
SELECT
f.user_pseudo_id,
DATE(TIMESTAMP_MICROS(f.first_visit_time)) AS first_visit_date,
-- チャネル情報
fs.first_source AS first_source,
fs.first_medium AS first_medium,
fs.first_campaign AS first_campaign,
fs.landing_page AS landing_page, -- 初回訪問時のランディングページを出力
-- CVデータ(セッションベース)
COUNT(DISTINCT cv.session_id) AS conversion_sessions, -- セッション単位のCV数
MIN(DATE(TIMESTAMP_MICROS(cv.conversion_time))) AS first_conversion_date, -- 初回CV日
COALESCE(MIN(TIMESTAMP_DIFF(TIMESTAMP_MICROS(cv.conversion_time), TIMESTAMP_MICROS(f.first_visit_time), DAY)), -1) AS lead_time_days,
-- 訪問回数
s.total_sessions AS visit_count_to_conversion
FROM first_visit f
LEFT JOIN first_source_data fs ON f.user_pseudo_id = fs.user_pseudo_id AND fs.rn = 1
INNER JOIN cv_conversions cv ON f.user_pseudo_id = cv.user_pseudo_id -- INNER JOIN に変更
LEFT JOIN session_counts s ON f.user_pseudo_id = s.user_pseudo_id
GROUP BY f.user_pseudo_id, f.first_visit_time, fs.first_source, fs.first_medium, fs.first_campaign, fs.landing_page, s.total_sessions
ORDER BY lead_time_days DESC;
こんな感じです。
今回のSQLロジック
・初回訪問日(first_visit_date
)を計測
・ 流入チャネル(first_source
/ first_medium
/ first_campaign
)を取得
・ CV発生日(conversion_date
)を取得 & 初回訪問からの経過日数(lead_time_days
)を計算
・ 訪問回数(visit_count_to_conversion
)をカウント
アトリビューションは初回接触をベースにしております。
この値を元に、リードタイムと扱ってデータを整理しております。
SQL出力例

結果をどのように具体的に活用する?
使い方としましては、スプレッドシート等に出力し、フィルターをかけます。
それぞれのチャネルのリードタイムと訪問回数の平均を算出し、数字と睨めっこです。
数値に応じて、まずは現状がどうなっているのか理解するところからで良いと思います。
例えば下記のような結果を出せたとします。
Organicのリードタイム平均:3.4日
Organicの訪問回数平均:4回
CPCのリードタイム平均:1.2日
CPCの訪問回数平均:6回
Referrerのリードタイム平均:2.1日
Referrerの訪問回数平均:8回
Organic>リードタイムが長め。競合と比較検討するケースが多いため、他社の情報をカバーできるコンテンツを追加検討。
CPC>わりかし即決傾向にあるが、訪問回数が多い。無駄なクリックを減らしたいので、リターゲティングなどのターゲティングの調整が必要。
Referrer>経由元のサイトを確認。可能な範囲で、紹介元サイトでの情報を充実させるか、遷移先のページで情報を網羅できる工夫などを行い、訪問回数を減らしたい。
※数値は例です。あくまで一つの考え方です。
テーマによってもある程度リードタイムってかかっちゃうケースも多いと思います。
比較検討をカバーするならmyb○stさんのサイトとかも良さそうです。
あのサイトのリードタイムとか数字見てみたい〜。
この結果がもたらす物
そもそもなんですが、自社商材のリードタイムをしっかり理解できている企業さんって少ないんじゃないでしょうか。優秀な営業さんやCRM等をしっかり使って管理できている企業さんであればともかく…。
この数字から考察してWeb戦略に活かすことも良いと思いますし、そもそも客観的な自社のデータを得た上で、「競合のサイトどうだろう」「自分がユーザーだとして…」みたいな協議を行うのも良いと思います。