既存記事をリライトするためにGoogleデータポータルを使ってSearch ConsoleとGoogleのインプレッション数や アナリティクスのPVを合算したデータの表を作りました。
Googleデータポータルはクセがあり複雑な設定があるので、具体的にどう 作業してデータを統合したのかをまとめました。
画像多めで詳しく解説しているので、敷居の高いGoogleデータポータルも一人で設定可能です。
この記事はこんな方におすすめです
- ブログのリライトをしたいけどどの記事から手を付けていいかわからない
- データポータルを使っててっとり早くWeb解析をしたい
- データポータルを使っていろんなデータを統合してみたい
注意点！今回はユニバーサルアナリティクスを使用しています！後日、GA4版も作成します。
データポータルはビッグクエリなどと連携しない場合は無料で使えます！
解析対象の記事のURLとタイトルのスプシを準備する
解析対象の記事のスプシを準備します。現在公開中の記事のURLとタイトルを抽出したシートを作りました。
一般の方はWordPressでブログ管理している人が多いと思うので、WP CSV Exporterというプラグインを使って出力するのがベストと思います。
私のブログはGatsbyでできているのでローカル側のGraphQLで記事のみ抽出してシートを作りました。やり方は、こちらに書いておきましたので興味のある方はお読みください。
データポータルにアクセス、無料で利用するでスタート
もしくは、直接アクセスしましょう。
空のレポートを作成しておく
まずは空のレポートを作成します。
データの連携処理
空のレポートからデータの連携処理を行っていきます。
無題のレポートが出来上がりますので編集をクリックします。
スプレッドシートと連携しておく
まずはスプレッドシートのデータを取り込みます。
ディメンション設定をしてデータがきちんと取り込めているか確認する
URLとタイトルを表示してみます。
ディメンションにURLとTITLE（先頭行の名称）を選びます。以下のように表示されていれば成功です。
Search Console と連携する
対象のドメイン、URLのインプレッション、Webを選びます。
スクショでは切れてますが、右下に追加ボタンがあるのでクリック。
これでSearch Consoleのデータを使えるようになったので、データソースのデータを統合をクリック。
別のテーブルを結合をクリック。
Search Consoleでは以下を指標として選びました。
- Impressions（表示回数）
- URL Clicks(クリック数)
- URL CTR（クリック率）
- Avarage Position（掲載平均順位）
各ページの指標にどれだけの数値があるのか一目瞭然です。
Google アナリティクス と連携する（ユニバーサルアナリティクス）
Search Console同様、Google アナリティクス のデータを追加します。
データが追加できたら、データソースを編集（鉛筆マーク）をクリック。
スプシのURLとアナリティクスのランディングページの値が一致しないので専用のフィールドを作る
アナリティクスのディメンションにはランディングページを使います。
アナリティクスのランディングページの値はドメインを除いたフルパスにってしまうので他のデータと一致しません。
// アナリティクス
/blogs/entry500/
// 他のデータの値
https://ginneko-atelier.com/blogs/entry500/そこでスプシやExcelでおなじみのCONCAT関数を使ってフィールドを作成し一致するURLを作成します。
CONTAT("https://ginneko-atelier.com/", ランディング ページ)関数には上記のように設定しますが、少し入力すると候補が出てくるので、補助機能を活用しながら関数を登録していくとラクチンです。
フィールドには名前が必要なので今回はPerfect URLとしました。
適用をクリックします。
私の環境下（Mac Chrome）では適用をクリックしたあともモーダルが閉じません。モーダル外のどこかしらをクリックしたら閉じるので閉じない方はやってみてください。
REGEXP_REPLACE 関数で ？以降のパラメーター を取り除くこともできます。
REGEXP_REPLACE(ランディング ページ,"(\\?.*)?$","")Google アナリティクス と結合の設定と指標の設定
結合の設定（Confiture join）で左外部結合、表1（スプシ）はURL、表3（アナリティクス）はPerfect URLのフィールドを選択します。
アナリティクスでは以下を指標として選びました。
- ページビュー数
- ユーザー数
- 直帰率
- 平均ページ滞在時間
統合したデータに出力したいディメンション、指標を設定し表にする
以下の取得するデータをセットしただけなので、実際に出力する表に必要なデータを設定していきます。
データの統合を保存して閉じ、実際の右側のパネルから指標とディメンションを設定します。
指標を追加するとおそらく右側のアイコンがデフォルトでSUM（合計値）と表示されているので必要に応じてAVR（平均値）に切り替えます。
| ディメンション |
|---|
| URL（表1） |
| TITLE（表1） |
| 指標 |
| - |
| Impressions（表示回数） |
| URL Clicks(クリック数) |
| URL CTR（クリック率） |
| Avarage Position（掲載平均順位） |
| ページビュー数 |
| ユーザー数 |
| 直帰率 |
| 平均ページ滞在時間 |
データの統合の全体はこんな感じになりました。
取得日時をコントロールする
日時を設定します。すべてのデータが一度に設定できたほうが便利なので、コントロールを使った方法をご紹介します。
上部メニューからコントロールの追加をクリックし期間設定を選択します。
表の位置を調整して、表の上にコントロールを設置します。期間を選んでおきます。
表のデフォルトの日付範囲を自動にしておきます。
これでコントロールから日時を集計できるようになりました！
リライト記事をフィールドを使って分類する（2022-06-22追記）
リライトする記事を条件ごと分類できるようにします。【ブログのSEO対策】リライト記事の選び方でリライト記事の選び方を紹介しました。せっかくなので、この記事の条件に沿って分類しできるようにします。
| アクション | 指標 |
|---|---|
| リライト | 公開3ヶ月以上インプレッション数月間100以下 |
| リライト | 順位平均20位以下 |
| リライト | 順位平均11 ~ 20位でクリック率平均3%以下 |
| リライト | 直帰率/滞在時間90%以上/0秒 |
| noindexもしくは削除 | 公開半年以上経ったのにimpression10以下/ページビュー10以下 |
| - | ページビュー300以上 |
ディメンションにフィールドを追加します。
フィールドにリライトという計算式を追加します。
データポータルでの条件式の書き方です。CASE文を使って分岐します。
CASE
WHEN 条件 THEN 値
ELSE デフォ値
ENDまとめて書くとこんな感じになります。
CASE
WHEN Impressions IS NULL AND ページビュー数 IS NULL AND DATE_DIFF(TODAY("Asia/Tokyo"),DATE) > 180 THEN "noindex"
WHEN Impressions < 10 AND ページビュー数 < 10 AND DATE_DIFF(TODAY("Asia/Tokyo"),DATE) > 180 THEN "noindex"
WHEN Impressions < 10 AND ページビュー数 IS NULL AND DATE_DIFF(TODAY("Asia/Tokyo"),DATE) > 180 THEN "noindex"
WHEN Impressions IS NULL AND ページビュー数 < 10 AND DATE_DIFF(TODAY("Asia/Tokyo"),DATE) > 180 THEN "noindex"
WHEN ページビュー数 > 300 THEN "-"
WHEN Impressions < 300 AND Impressions > 50 AND DATE_DIFF(TODAY("Asia/Tokyo"),DATE) > 90 THEN "Phase 1"
WHEN Impressions > 300 AND Average Position > 20 AND Average Position > 50 THEN "Phase 2"
WHEN Average Position > 10 AND Average Position < 20 THEN "Phase 3"
WHEN 直帰率 > 0.9 OR 平均ページ滞在時間 < 10 THEN "Phase 4"
ELSE "-"
ENDいくつか関数をご紹介します。
TODAY("Asia/Tokyo")〇〇 IS NULLDATE_DIFF(終了日,開始日)リライトすべき記事が抽出できるようになります。
作成したデータを期間で区切ってスプなどに落とし作業ログを作る
どのようにリライトしたかなどログを残しておきたいですよね？ログを記録するように作成したデータをさらにスプシに変換します。
表の右上にケバブメニュー（縦3つのドット）があるのでそちらをクリックしエクスポートを選びます。
スプレッドシートにチェックを入れエクスポートをクリックします。もちろん、ExcelやCSVでエクスポートもできます。
これで、いつどんなリライトをしたかログを残しておきつつ、効果測定が行なえます。
まとめ・データポータルを使って各種データを統合すると全貌が把握できる
データポータルでデータを統合すると、総合的に見てどの記事を分析すればいいかなど把握しやすくなります。
今回この表を作るのも結構手探りで作りましたが、まだまだ紹介できていない機能があるのでまた記事にまとめられたらなーと思っています。
この記事が皆さんのWeb解析ライフの一助となれば幸いです。
最後までお読みいただきありがとうございました。
かみーゆ/フロントエンドエンジニア
資金ゼロからフィリピンで起業した海外ノマドエンジニア。IT業界10年以上でテクニカルディレクター（技術責任者）・エンジニア講師・ブリッジSEを経てLenzTechnologies Inc.を設立し、代表を務める。CMS concreteCMSエバンジェリスト。テックブログ以外も「磨耗しない人生」や「海外ノマド」のライフスタイルについて発信。好きなものは肉とハイボール。
