【BigQuery×GA4】CVした・していないユーザーのPVページランキングを抽出

【BigQuery×GA4】広告アカウント無しで線形アトリビューション分析を行うのコピー

会社で、「こんなの見れたら良いよね」という会話をきっかけに、今回のSQLを作りました。

というのも、GA4でCVユーザーのランディングとか見る際に、

トップページはそら多いよな
問い合わせトップがランディングってなんでやねん
設定できていないクライアントだと、問い合わせ送信手前の確認が含まれている…（無い？ｗ）

などなど、感じることがありませんか。私はいつも感じています。

まあトップページ大事だよねとか、設定見直そうとか。

指名検索＞検索結果のリッチ表示箇所に「問い合わせ」がある＞直接ランディングが問い合わせになるとか。考慮すべき点もあるのは承知の上なんですが。

「うーん、知りたいのはそこじゃない」というか。

なんで、アナリストでは無いSEO屋さんたちが、もっと施策の頭出しに使えるようなデータやそれを裏付けるためのデータが欲しいと思って、色々試行錯誤して作ってみました。

CVしたユーザーのPV数ページ別ランキングSQL

WITH cv_users AS (
  SELECT
    user_pseudo_id
  FROM
    `sample.analytics_12345678.events_*`  -- イベントIDを指定
  WHERE
    event_name = 'CVイベント名を入力'  -- コンバージョンイベントを指定
),
pageviews AS (
  SELECT
    e.user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_url
  FROM
    `sample.analytics_12345678.events_*` e
  WHERE
    event_name = 'page_view'
    AND user_pseudo_id IN (SELECT user_pseudo_id FROM cv_users)  -- CVユーザーに限定
    AND NOT (  -- 指定したディレクトリを含むURLを除外
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') LIKE '%/contact/%'-- ランキングから除外したいディレクトリを入力
    )
),
ranked_pages AS (
  SELECT
    page_url,
    COUNT(*) AS pv_count,
    ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rank
  FROM
    pageviews
  GROUP BY
    page_url
)
-- 上位20件を取得
SELECT
  page_url,
  pv_count
FROM
  ranked_pages
WHERE
  rank <= 20
ORDER BY
  rank ASC;

※私はSQLのプロエンジニアではありません。ある程度理解していますが、
既存コードを細かく解析し、AI等と情報を整理した上で理解・組み直しをおこなっております。ご了承ください。

こんな感じです。

ポイントは除外ディレクトリを指定できる部分です。

AND NOT (  -- 指定したディレクトリを含むURLを除外
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') LIKE '%/contact/%'-- ランキングから除外したいディレクトリを入力
    )

冒頭でお伝えしたように、「/contact/」など、このページは除外したいみたいなケースがある際に活用してください。

URL除外応用例

複数のディレクトリを除外：複数のディレクトリを除外する場合は、条件をORでつなげます。

AND NOT (
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') LIKE '%/contact/%'--除外候補.1
  OR (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') LIKE '%/another-directory/%'--除外候補.2
)

正規表現による除外: 正規表現を使用する場合は、REGEXP_CONTAINSを利用します。

AND NOT REGEXP_CONTAINS(
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'),
  r'/jobchange/entry/'
)

正規表現は少しテクニカルですが、より細かな条件を含めたい際には非常に便利です。
最近サーチコンソールでも正規表現使ってみてますが、便利。

出力結果

こんな感じです。
っていっても色々あってURL隠しちゃってますが、PV数順で20個URLが並んでいます。

10個とかでも良いかもしれません。

サイトの規模に応じて増減しても良いかと思いますが、細かく観ても仕方ないというか、本質でも無いといいますか。

CVしなかったユーザーのPV数ページ別ランキングSQL

WITH cv_users AS (
  SELECT
    user_pseudo_id
  FROM
    `sample.analytics_12345678.events_*`  -- イベントIDを指定
  WHERE
    event_name = 'CVイベント名を入力'  -- コンバージョンイベントを指定
),
non_cv_pageviews AS (
  SELECT
    e.user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_url
  FROM
    `probank-project2024.analytics_288736174.events_*` e
  WHERE
    event_name = 'page_view'
    AND user_pseudo_id NOT IN (SELECT user_pseudo_id FROM cv_users)  -- CVユーザー以外
    AND NOT (
     (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') LIKE '%/contact/%'  -- 指定したディレクトリを含むURLを除外
    )
),
ranked_pages AS (
  -- URLごとのPVランキングを作成
  SELECT
    page_url,
    COUNT(*) AS pv_count,
    ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rank
  FROM
    non_cv_pageviews
  GROUP BY
    page_url
)

SELECT
  page_url,
  pv_count
FROM
  ranked_pages
WHERE
  rank <= 20
ORDER BY
  rank ASC;

要領は同じです。

こちらは、CVしていないユーザーにしました。
先ほどとは反対にボトルネックを探るための条件として面白いかなと思います。

ディレクトリの件は、先ほどの箇所と同じ要領で調整をしてください。

出力結果

今度はCVしていないユーザーなので、一般的に考えれば数字が大きくなっています。

こちらでは、コラム・オウンドメディア等のURLが複数入ってきました。

サービスサイト等の改善を軸に考える際、CVしていない側の方では除外してあげたほうが建設的かもしれません。CVしている方で、コラムのURL等が入ってくれば優秀な記事として色々考えられますが。

今回抽出したデータの活用について

ある程度欲しいデータは出ました。
あとは、このデータをどう扱うかが大事ですね。

CVユーザー側のデータ

改めて書くほどでも無いんですが、CVしているユーザーのデータについては、

導線を強化したり、目立つリンクやボタン配置を検討
シンプルにそのページを最適化（デザインや表示速度を詰めるなども面白い）
サーチコンソールで上位ページのクエリを確認して、ランディングページとして強化

あたりの材料になるんじゃ無いかと思います。
全部まとめてしまえば、

「露出強化とページのクオリティ向上を図りつつ、現状露出の強いLP等から導線を設置」

って感じですね。（いうは易しですが。）

CVしていないユーザー側のデータ

こちらも書いておきます。

ボトルネックの特定
既存サイト設計の見直し材料
1つのデータとして活用検討

あたりですかね。
基本的には、

CVしていない上位ページに導線が多く設置されていないか？などの設計見直し

が良いかと思います。

3.の「1つのデータとして活用検討」は珍しい考え方でも無い気がしますが、福田康隆氏著の「THE MODEL」を読んだ際に印象的だった、「商談に繋がらなかったセグメントも、1つのセグメントとして分ける」という考え方を参考にしています。

つまり、ダメなポイントはダメなポイントなりの理由があり、それらもちゃんとセグメントを切り、活かしていく必要があるんです。

ただ、ここまで考えるのは少し重たい気もするので、参考程度に。

余談：CVイベントと、普通のイベントに差はあるのか？

SEO記事っぽい・冗長な内容っぽくて嫌なんですが、触っているうちにこのフラグが気になったので記載しておきます。

普通のイベントの場合

GA4で設定したすべてのイベントは、BigQueryではevent_name列に記録される。

CVイベントの場合

BigQuery上では、event_nameに記録されるイベント名自体は普通のイベントと同じでだが、event_params列に特定のフラグ情報が含まれます。

といった結果になりました。

両者共に同じようにイベントとして抽出ができるものの、CVにはCVとしてのフラグが存在しているので、注意が必要といった感じでした。

まとめ〜BigQueryを実務に活かすために〜

BigQuery、なんだかんだ触っていて面白いです。

あとは、こういったデータを実務に活かして、ケーススタディとして事例紹介とかが今後できれば良いなと思います。

BigQueryとGA4の連携で、ユーザーのページ遷移を追ってみる

葛西洋介

都内Webマーケティング企業に勤務。対応サイトは過去大小含め80サイト以上。
企業サイトの案件が多く、SEOというよりは事業理解やマーケティング側面を大切にしています。
また、上流から下流まですべて対応する経験が多く、大抵のことは自分で実装まで対応可能です。
最近はAWSの理解とPythonとSEOの組み合わせ研究が楽しいです。

Galatea

CVしたユーザーのPV数ページ別ランキングSQL

URL除外応用例

出力結果

CVしなかったユーザーのPV数ページ別ランキングSQL

出力結果

今回抽出したデータの活用について

CVユーザー側のデータ

CVしていないユーザー側のデータ

余談：CVイベントと、普通のイベントに差はあるのか？

普通のイベントの場合

CVイベントの場合

まとめ〜BigQueryを実務に活かすために〜