データポータルでページURLや検索クエリをグルーピングするCASE文について

2018年07月23日

Google サーチコンソール Looker Studio(旧 Google データポータル)

Google データポータル(旧データスタジオ)でSearch Consoleコネクタと接続し、検索アナリティクスのデータを可視化していると、特定のページURLや検索クエリをレポート内でグルーピングしたくなるのではないでしょうか。

今回はGoogle データポータル内でCASE文を利用し、特定のページURLや検索クエリをグルーピングする方法を紹介します。

CASE文とは
Search ConsoleやGoogleアナリティクス内ではグルーピングできない
CASE文でページURLをグルーピング
CASE文で検索クエリをグルーピング
作成したデータフィールドはエクスプローラーでも使える

CASE文とは

CASE文とは、Google データポータル内の計算フィールドで利用できる関数の1つです。作成には、Google データポータルでデータソースを編集可能な権限が必要ですが、複数のパターンで集計されたページURLや検索クエリをグルーピングするのに役立つ関数です。

具体的にいうと、条件設定した値の対象となるデータを、新しいフィールドとして作成できる関数です。

下記はヘルプページに掲載されている例です。

CASE WHEN 国 IN ("アメリカ","カナダ","メキシコ")THEN "北アメリカ" WHEN 国 IN ("イギリス","フランス")THEN "ヨーロッパ" ELSE "その他" END

(出典：CASE – データポータルのヘルプ)

これは、国として集計されたデータを、CASE文を利用して

“アメリカ”,”カナダ”や”メキシコ”が記載されているデータを”北アメリカ”としてグルーピングする
“イギリス”や”フランス”が記載されているデータを”ヨーロッパ”としてグルーピングする
それ以外の国が記載されているデータを”その他”としてグルーピングする

グルーピングした例として記載されています。

Search ConsoleやGoogleアナリティクス内ではグルーピングできない

Search Consoleの検索アナリティクスデータを分析、レポート表示するなら、Google データポータルやTableauなどのビジュアライゼーションツールがオススメです。

なぜ、オススメかというと、Search ConsoleやGoogleアナリティクス内でも検索アナリティクスデータは確認できますが

特定のデータは抽出できても保存できない
特定のデータをグルーピングしたデータ同士を同じレポート内で比較することが難しい

といった側面があるためです。下記にそれぞれの例を紹介します。

Search Console内の検索アナリティクス

Search Console内の検索アナリティクスでページタブに切り替えると、AMPページが含まれていることがわかります。フィルタを利用して、AMPページと非AMPページの傾向を確認してみます。

1.「新規」から「ページ」を選択します。

2.フィルタ内容にて「次を含むURL」を選択し、入力欄に/amp/と入力します。

フィルタタブでは特定の条件に一致したデータの抽出を行います。比較タブは特定の条件に一致したデータ同士の比較となり、特定の条件が含まれないデータの抽出を行うことができないため、AMPページと非AMPページの比較には不向きです。

3.フィルタが適用されたレポート同士を比較します。

下記はAMPページのみをデータ抽出したレポートですが、非AMPページに関しても同じようにフィルタ設定を行い、異なる画面に表示した2つのレポートを比較しなければいけません。また、来週同じ内容のレポートを閲覧したくても、残念ながらSearch Console内には保存機能が存在しません。

Search Console内の検索アナリティクス

Googleアナリティクス内の検索アナリティクスで特定のデータを抽出する場合は、アドバンスフィルタを利用する必要があります。
セグメントに関しては、検索アナリティクスのデータを対象にデータ抽出することは不可能です。
アドバンスフィルタを利用して、AMPページと非AMPページの傾向を確認してみます。

1.「アドバンス」部分をクリックし、データを抽出する条件を入力する入力ボックスを開きます。

ディメンションの選択から「ランディングページ」を選ぶ、データ抽出の条件を入力します。Search Console内の検索アナリティクスとは違い、複数の条件を掛け合わせることだけでなく、正規表現も使用できます。

「アドバンス」部分をクリックし、データを抽出する条件を入力する入力ボックスを開く

2.必要に応じて折れ線グラフを調整します。

折れ線グラフ下に表示されている表形式のレポートにて、データ抽出内容が正しいことを確認したら、折れ線グラフ右上のボタンで週、月など、折れ線グラフで傾向を比較しやすい時系列に変更させます。
もし、来週も同じ内容のレポートを閲覧したい場合はページ右上の「保存」を選択することで、保存済みレポートにレポート内容が保存されます。

しかし、Googleアナリティクスにおいても1つのレポート内にAMPページと非AMPページの傾向を表示することは不可能です。傾向を比較するためには、異なる画面に表示した2つのレポートを比較することになります。

データポータル内の検索アナリティクス

データポータル内でSearch Consoleコネクタを利用し、検索アナリティクスデータを表示する場合、CASE文を利用すると以下のように、1つのレポート内に異なるグルーピングのデータを表示させることが可能となります。

実際にページURLと検索クエリのそれぞれの例でCASE文の記述例を紹介します。

CASE文でページURLをグルーピング

Search Consoleコネクタで検索アナリティクスデータを折れ線グラフ、表形式のレポートで可視化したダッシュボードが以下となります。

全体の傾向は確認できますが、表形式のレポートの「Landing Page」を確認してみると、AMPページと非AMPページが混在していることがわかります。AMPページと非AMPページでそれぞれの傾向を確認したいため、CASE文を利用してフィルタを設定します。

1.「リソース」タブより「追加済みのデータソースの管理」を選択します。

2.特定の条件でページURLをグルーピングしたいデータソースの「編集」を選択します。

3.データソース編集画面の右上にある「フィールドを追加」を選択します。

4.計算フィールド編集画面にてCASE文を利用して、抽出したい条件を記載します。

今回は「AMPページと非AMPページを分割する」という目的のため、下記のような条件文を記載しています。
非常にシンプルな条件文となり、ディメンション「Landing Page」のページURL内で、

/amp/という文字列を正規表現で抽出できたら”AMPページ”としてグルーピング
/amp/という文字列を正規表現で抽出できなかったら”非AMPページ”としてグルーピング

としています。

CASE 
WHEN REGEXP_MATCH(Landing Page,"(.*/amp/.*)") THEN "AMPページ" 
ELSE "非AMPページ" END

5.計算フィールドによって作成したデータフィールドがデータソース内に追加されているか確認します。追加されていればOKです。

6.Google データポータルのレポート編集画面にてフィルタアイコンを選択します。

フィルタアイコンを選択後、ディメンションに計算フィールドによって作成したディメンションを適用させます。

7.フィルタ利用以外にレポートのディメンションとしても利用可能です。

棒グラフレポートを選択し、ディメンションに計算フィールドによって作成したディメンションを適用させると、CASE文でグルーピングしたグループ別のグラフを作成できます。

8.レポート編集画面からレポート表示画面に切り替えて、編集したフィルタ機能が正常に機能するか確認します。

下記のレポートはフィルタ設定後のレポートを表示したダッシュボードとなります。

フィルタで、ページURLに/amp/文字列を正規表現で含むという条件の”AMPページ”を選択したため、ダッシュボード内の各レポートが”AMPページ”の条件に一致するデータのみが表示されています。

CASE文で検索クエリをグルーピング

Search Consoleコネクタで、検索アナリティクスデータをバブルチャートで可視化したダッシュボードが以下となります。

検索クエリ別に、x軸に平均掲載順位である「Average Position」、y軸に表示回数である「Impressions」、バブルのサイズにクリック数である「Clicks」を選択しています。

x軸は右に位置するほど平均掲載順位が高いことがわかり、可視化する上でわかりやすいように20位以下のデータはグラフから非表示にしています。

平均掲載順位が高く、表示回数が多いにも関わらずバブルのサイズが小さい検索クエリは、クリック率を改善することでクリック数増(≒サイトへの流入増)を見込める検索クエリであることがわかります。

特定の検索文脈をもつ検索クエリのみのデータに絞り込もうと、検索クエリである「Query」でフィルタを作成すると、検索クエリ1つずつを選択する形式のフィルタが表示されてしまい、運用する上で現実的ではありません。特定の検索クエリデータを抽出するためのCASE文を利用してフィルタ設定を改善してみます。

1.「リソース」タブより「追加済みのデータソースの管理」を選択します。

2.特定の条件で検索クエリをグルーピングしたいデータソースの「編集」を選択します。

3.データソース編集画面の右上にある「フィールドを追加」を選択します。

4.計算フィールド編集画面にてCASE文を利用して、抽出したい条件を記載します。

今回は下記の2つを計算フィールドから作成しています

CASE 
WHEN REGEXP_MATCH(Query, "(.*ミーブ.*|.*アウトランダー.*|.*三菱.*|.*outlander.*|.*i-miev.*)") THEN "三菱" 
WHEN REGEXP_MATCH(Query, "(.*リーフ.*|.*日産.*|.*nissan.*|.*leaf.*)") THEN "日産" 
WHEN REGEXP_MATCH(Query, "(.*トヨタ.*|.*プリウス.*)") THEN "トヨタ" 
WHEN REGEXP_MATCH(Query, "(.*honda.*|.*ホンダ.*)") THEN "ホンダ"
WHEN REGEXP_MATCH(Query, "(.*テスラ.*|.*モデルx.*|.*モデル3.*|.*tesla.*)" ) THEN "テスラ"
WHEN REGEXP_MATCH(Query, "(.*bmw.*|.*BMW.*)") THEN "BMW"
WHEN REGEXP_MATCH(Query, "(.*シボレー.*|.*GM.*|.*gm.*|.*bolt.*)" ) THEN "GM"
ELSE "その他メーカー" END

CASE 
WHEN REGEXP_MATCH(Query, "(.*電気自動車.*|.*電動自動車.*)") THEN "電気自動車含む" 
ELSE "電気自動車含まない" END

5. Google データポータルのレポート編集画面にてフィルタアイコンを選択します。

フィルタアイコンを選択後、ディメンションに計算フィールドによって作成したディメンションを適用させます。

6.検索クエリをCASE文でグルーピングした際も、フィルタ利用以外にレポートのディメンションとしても利用可能です。

表グラフレポートを選択し、ディメンションに計算フィールドによって作成したディメンションを適用させると、CASE文でグルーピングしたグループ別の数値を表示できます。

7.レポート編集画面からレポート表示画面に切り替えて、編集したフィルタ機能が正常に機能するか確認します。

上記は、「Queryフィルタ=メーカー」フィルタにて”テスラ”の条件にあるデータを抽出した際のダッシュボードとなります。

「Queryフィルタ=電気自動車」を利用した表形式のレポートから”テスラ”の条件と一致する検索クエリには、”電気自動車”を含むデータが0ということがわかります。テスラに関連する検索を行うユーザーは、電気自動車のことを知っていることが多いのか、わざわざ電気自動車と検索クエリに入力しないのかもしれません。

バブルチャートからわかることとして、“テスラ充電”は掲載順位を改善することでクリック率を改善し、クリック数増を見込むことが可能と思われます。その他、11位以下でも表示回数が多い検索クエリが存在するため、そのような検索クエリは10位以上に改善すべきと考えられます。