サイトへの初回訪問日からCVまでの日数をBigQueryに蓄積されたGA4のデータから集計するクエリを解説。
初回訪問から初回CVまでの日数を取得するクエリ
CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20240315');
WITH
FirstTouch_CV_period AS (
SELECT
user_pseudo_id,
event_name,
first_touch_time_jst,
cv_time_jst,
cv_date_jst,
DATETIME_DIFF(cv_time_jst, first_touch_time_jst, DAY) AS diff_day,
page_location
FROM (
SELECT
user_pseudo_id,
event_name,
DATETIME(TIMESTAMP_MICROS(user_first_touch_timestamp), 'Asia/Tokyo') AS first_touch_time_jst,
DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS cv_time_jst,
DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS cv_date_jst,
REGEXP_REPLACE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), r"\?.*", "") AS page_location,
ROW_NUMBER() OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp ASC) AS num
FROM
`<project>.<dataset>.events_*`
WHERE
/*
(
event_name = 'click_header_purchase'
OR
event_name = 'click_footer_purchase'
)
*/
event_name = 'page_view'
AND
_TABLE_SUFFIX BETWEEN date_from() AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND
--イベント集計では以下をコメントアウト
REGEXP_REPLACE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), r"\?.*", "") = 'https://xxx'
QUALIFY
num = 1
)
)
SELECT
*
FROM
FirstTouch_CV_period
クエリ解説
ユーザーがサイトに訪問した時間はデフォルトで’user_first_touch_timestamp’フィールドに格納されているため、再度初回訪問日を計算する必要はなくこれを利用する。上記のクエリはCVをURLで集計できるものであれば、URLを指定し、イベントとしてCVを集計するのであればイベント名を指定する。得られる結果は初回訪問日から初回CVまでの日数であるが、正確にいうと初回訪問日から翌日にCVした場合は1日間の日数がかかるものとして計算している。つまり24時間経たなくとも日にちが変われば日数は1インクリメントされ集計されるが、あまり厳密さにこだわなければ問題ない。
参考：[GA4] BigQuery Export スキーマ > user_first_touch_timestamp -アナリティクス ヘルプ
CVまでの日数を「分析視点」で活用する
CVまでの日数をそれ単体でみても、おそらく特段目新しい発見は得られないであろう。UAの時代からそうであったが、ネットで散見される「CVまでの日数」について言及される施策案は決まって「この日数をもとに広告のリターゲティングの期間の目安にしましょう」というものであり、それ以外については特段何か述べられることはあまりない。
それもそのはず、CVまでの日数については、それ単体では周知の事実しか得られないことが多いが、CVまでの日数の経過を時系列でモニタリングすることで施策の検証に使える指標として活用できることがある。もちろん、CVまでの日数を短縮することが一概に良いとは言えず、コンテンツを増やすほどに潜在層のユーザーの流入が増えCVまでの日数は長くなるだろう。この場合はコンテンツによるナーチャリング期間が長くなるためCV日数が伸びるのは必然である。そうではなく、販売目的のランディングページ主体のサイトなど、より購買までの期間を短くすることが目的であれば、この「CVまでの日数」の変化を追うことで施策の検証を補う方法になりえる。例えば「期間限定」のタイムリミット的な施策を打ったとき、ユーザーの購買までの時間を短縮できるかを検証する際に活用することができるだろう。
初回訪問から初回CVまでの「秒数」を取得する
このようにランディングページの改善施策の検証においては、日単位よりもさらに粒度を細かくしCVまでの時間が短縮されたかを確認する必要がある。UAでは日単位よりもドリルダウンすることはできなかったが、マイクロ秒単位でイベント送信されるGA4ではさらに細かく分析が可能になるため、より精緻な効果検証が可能になる。
