GA4のデータを使ったサイト分析方法：エクセルVBAマクロで指定月のPVを比較して改善点を考える

エクセルVBAマクロを使い、指定月のPVを比較してサイトの改善点を考える方法を紹介します。

分析データはGA4（Googleアナリティクス4）から抽出し、VBAマクロはコピーして貼り付けるだけで使えるものを記事内で紹介しています。

動作確認情報

動作確認した時の情報を下記に記載しておきます。

• VABマクロ：Microsoft Office Professional Plus 2019
• Googleアナリティクス4（GA4）：2023年10月時点のバージョン

VABマクロは「Microsoft Office Professional Plus 2019」で動作確認をしています。

また、Googleアナリティクス4（GA4）は2023年10月時点のバージョンです。もし、バージョンアップで出力されるデータ形式が変更になるとVBAマクロも変更が必要になります。

サイト分析用のエクセルVBAマクロの「概要」

このツールでは、二つの月のPV（ページビュー）を比較して、PVが減少しているページ、および激減しているページを抽出できます。

PVが下がっているということは、検索順位も下がっている可能性が高いです。

理由は、Googleの検索結果一覧に表示される記事のクリック率（下画像）は、1位と10位では10倍以上の差があるため、アクセス数（PV）が減っていれば、検索順位が下がっていると考えられます。

出典：https://www.seoclarity.net/mobile-desktop-ctr-study-11302//

GA4で、PVを使って個別ページの増減を確認することはできますが、一覧性が悪いので、このマクロを使って個別ページの増減を確認すると便利です。

例えば、8月と9月のページのPVを比較て、どのページのアクセスが減少したかを確認し、原因を探して改善するといったことに使えます。また抽出するデータを変えれば、前年同月の比較も可能です。

サイト分析用のエクセルVBAマクロの「設定と実行」

実際にVBAマクロの設定方法と実行方法について説明します。

下記の手順で進めていきます。

• 1）GA4データの抽出
• 2）抽出データをエクセルに貼り付け
• 3）VBAマクロの設定
• 4）マクロを実行

1）GA4データの抽出

GA4を開いて「レポート」＞「エンゲージメント」＞「ページとスクリーン」をクリック。

「確認したい月の範囲」を右上の日付で設定します（計測したい月の1日～月末を設定）。

次に「共有」からCSV形式でファイルをダウンロードします。

比較したい二つの月をそれぞれCSV形式ダウンロードしてください。

※以下、8月と9月のデータをダウンロードしたと仮定して説明していきます。

2）抽出データをエクセルに貼り付け

8月のCSV形式のファイルをテキストエディタで開いて、表のヘッダーから下を全てコピー。

エクセルを起動して空のブックを開き、先ほどコピーした8月のデータを貼り付けて、シート名を「data1」にします。

同じように、新しいシートを作成して、9月データを貼り付けてシート名を「data2」にします。

3）VBAマクロの設定

次にVBAマクロを設定します。

上記のエクセルで「alt」＋「F11」を押してVBAマクロの画面を表示し、メニューの「挿入」＞「標準モジュール」を選択します。

左メニューの「Module 1」を選択して、右側の編集画面に下記のエクセル用VBAマクロを貼り付けてください。

エクセル用VBAマクロ

Sub SplitCommaSeparatedData()
    Dim sourceRange As Range
    Dim cell As Range
    Dim dataArray() As String
    Dim i As Integer
    Dim rowOffset As Integer
    
    ' ソースデータのセル範囲を指定
    Set sourceRange = Sheets("data1").UsedRange
    
    ' 各セルを処理
    For Each cell In sourceRange
        ' カンマでデータを分割
        dataArray = Split(cell.Value, ",")
        
        ' 各要素をセルに配置
        i = 0
        rowOffset = 0
        For Each element In dataArray
            cell.Offset(rowOffset, i).Value = element
            i = i + 1
        Next element
    Next cell
    
    ' ソースデータのセル範囲を指定
    Set sourceRange = Sheets("data2").UsedRange
    
    ' 各セルを処理
    For Each cell In sourceRange
        ' カンマでデータを分割
        dataArray = Split(cell.Value, ",")
        
        ' 各要素をセルに配置
        i = 0
        rowOffset = 0
        For Each element In dataArray
            cell.Offset(rowOffset, i).Value = element
            i = i + 1
        Next element
    Next cell
End Sub

Sub ComparePVWithMark()
    Dim wsData1 As Worksheet
    Dim wsData2 As Worksheet
    Dim wsPVCompare As Worksheet
    Dim lastRowData1 As Long
    Dim lastRowData2 As Long
    Dim pvCompareRow As Long
    
    ' ワークシートを設定
    Set wsData1 = ThisWorkbook.Sheets("data1")
    Set wsData2 = ThisWorkbook.Sheets("data2")
    
    On Error Resume Next
    Set wsPVCompare = ThisWorkbook.Sheets("PV比較")
    On Error GoTo 0
    
    ' "PV比較" シートを作成または既存のシートをクリア
    If wsPVCompare Is Nothing Then
        Set wsPVCompare = ThisWorkbook.Sheets.Add
        wsPVCompare.Name = "PV比較"
    Else
        wsPVCompare.Cells.Clear
    End If
    
    Call SplitCommaSeparatedData
    
    ' "data2" シートからデータをコピー
    lastRowData2 = wsData2.Cells(wsData2.Rows.Count, "A").End(xlUp).Row
    For pvCompareRow = 1 To lastRowData2
        wsPVCompare.Cells(pvCompareRow, 1).Value = wsData2.Cells(pvCompareRow, 1).Value ' ページパス
        wsPVCompare.Cells(pvCompareRow, 3).Value = wsData2.Cells(pvCompareRow, 2).Value ' 表示回数
        
        ' 列ヘッダーを設定
        wsPVCompare.Cells(1, 1).Value = "URL"
        wsPVCompare.Cells(1, 2).Value = "data1"
        wsPVCompare.Cells(1, 3).Value = "data2"
        wsPVCompare.Cells(1, 4).Value = "激減"
    
        ' "data1" シートからデータをコピー
        lastRowData1 = wsData1.Cells(wsData1.Rows.Count, "A").End(xlUp).Row
        For i = 1 To lastRowData1
            If wsData1.Cells(i, 1).Value = wsPVCompare.Cells(pvCompareRow, 1).Value Then
                ' セルの内容が数値であることを確認
                If IsNumeric(wsData1.Cells(i, 2).Value) Then
                    wsPVCompare.Cells(pvCompareRow, 2).Value = wsData1.Cells(i, 2).Value
                    
                    ' B列とC列を比較して、その差が500以上であればD列に〇を付ける
                    If IsNumeric(wsPVCompare.Cells(pvCompareRow, 2).Value) Then
                        If wsPVCompare.Cells(pvCompareRow, 2).Value - wsPVCompare.Cells(pvCompareRow, 3).Value >= 500 Then
                            wsPVCompare.Cells(pvCompareRow, 4).Value = "〇"
                        End If
                    End If
                End If
                
                Exit For
            End If
        Next i
        
        ' PV比較
        If IsNumeric(wsPVCompare.Cells(pvCompareRow, 2).Value) And IsNumeric(wsPVCompare.Cells(pvCompareRow, 3).Value) Then
            If wsPVCompare.Cells(pvCompareRow, 2).Value > wsPVCompare.Cells(pvCompareRow, 3).Value Then
                wsPVCompare.Cells(pvCompareRow, 3).Interior.Color = RGB(255, 0, 0) ' 赤色背景
            End If
        End If
    Next pvCompareRow
End Sub

4）マクロを実行する

マクロの実行ボタンを押して「実行」します。

処理が成功すれば、「PV比較」という新しいシートが作成され、下記の結果が表示されます。

• A列：URL
• B列：data1（8月のPV）
• C列：data2（9月のPV）
• D列：激減

C列「data2」の赤い背景は「data1」と比較してPVが減少したところです。

D列「激減」はPVが500以上下がったところに「〇」が付きます。

エクセルの「並べ替えとフィルター」を使って絞り込むと見やすくなります。

マクロがうまく実行できない場合

エラーは表示されないけど、「PV比較」シートが作成されない場合は、下記の手順で実行してください。

標準モジュールの「Module1」を選択してから「実行」をクリック。

その後実行するマクロ画面が表示されたら「ComparePVWithMark」を選択して「実行」をクリックしてください。

補足1）「激減」として抽出するデータを変える

「激減」として抽出するデータを変えたい場合は、VBAマクロに設定している数値（500と書かれている場所）を変更してください。

「50」にすれば、PVが50以上減少したページを「激減」として抽出できます。

If wsPVCompare.Cells(pvCompareRow, 2).Value - wsPVCompare.Cells(pvCompareRow, 3).Value >= 500 Then

補足2）前年同月で比較したい場合

GA4で抽出するデータを「前年の対象月に変更（例えば前年9月のデータ）」して、CSV形式でファイルをダウンロードします。

抽出したデータを「data1」に貼り付けてVBAマクロを実行すれば、前年と今年9月の比較が可能です。

結果を分析する方法

このツールを使えば、PVが減少しているページ（検索順位が下がったページ）を抽出できるので次はそのページのPVが減少した原因を探る必要があります。

PVが減少している原因は、主にSearchConsoleを使って調べられます。

「検索パフォーマンス」＞「検索結果」をクリック。

「新規」＞「ページ」を指定し、「エクセルファイルでPVが減少しているURL」を指定します。

画面下の方に表示される「クエリ」を確認して、実際にその「クエリ」を使って検索します。上位に表示されているページを確認して自分のサイトと比較してください。

順位が下がる原因はいろいろありますが、主に下記の項目が考えられます。

• Googleコアアップデート
• 競合サイトの記事更新（最新ニュースなど）
• 自サイトの情報が古い・間違っている
• 自サイトのキーワードカニバリゼーション
• 自サイトの表示が競合サイトより遅いなど

どの項目が該当するかを調べて対応が必要です。

Googleコアアップデート対策

Googleコアアップデート（core update）では、検索順位が大きく変わることがあります。

特に「コンテンツの質が低い」と順位が大きく下がるので、日ごろから品質の高い記事作成を心がけましょう。これは一日二日でできる対策ではないのでコツコツ積み重ねが必要です。

なお、コアアップデートで順位が下がった場合、順位を大きく回復できるのは次のコアアップデートと言われています。順位が落ちた記事は、コンテンツの質を改善することで回復させることもできますが、前の順位に戻る補償はありません。

競合サイトの記事更新対策

競合サイトが記事を更新して、新しい情報を追加すると順位があがり、自分のサイトの順位が下がることがあります。

対策としては日ごろから情報収集して、競合サイトに先を越されないように更新が必要です。

自サイトの情報が古い・間違っている

上記の競合サイトの更新とかぶりますが、記事に掲載している情報が古くなったり、間違っていたりすると、順位が下がることがあります。

こちらも情報収集しながら、記事を最新に保つためのリライトが必要です。

自サイトのキーワードカニバリゼーション

記事が多くなってくると、前と同じキーワードで記事を書いてしまうことがあります。

同じキーワードで検索順位に記事が二つのることを「キーワードカニバリゼーション」といって、SEO上は悪い状態になっています。

キーワードカニバリゼーションが起きている場合、検索結果から除外したい記事に下記の対策を施します。

• noindex、nofollowを追加
• 301リダイレクト

記事を残したい場合は「noindex、nofollow」を追加して検索結果から消えるようにします。もし、記事自体がいらないのであれば「301リダイレクト」を設定してもう一つの記事に誘導しましょう。

自サイトの表示が競合サイトより遅い

似たような記事内容になってくると、記事が表示される速度も重要になってきます。競合サイトよりも速く表示できることが検索上位を狙う一つの条件ということです。

ただし、SEO対策の重要度は低いため、まずはコンテンツの改善を行ったうえで、他に対策がない場合に行います。

速度改善はPageSpeed Insightsなどのツールを使って自サイトの表示速度遅延の原因を抽出してそれを修正しましょう。

SearchConsoleで表示回数が増えているのにCTRが増えない

SearchConsoleのデータを分析していると下記のようなページも出てきます。

• 検索順位はほぼ変化なし
• 表示回数は増加している
• でもCTR（クリック率）は減少している

「検索順位が同じ」で表示回数が増えれば、クリック率は増えそうに思えますが、表示回数が増えても自分より検索順位が高いページがクリックされればクリック率は下がります。

この場合は「タイトル」「メタデスクリプション」などの検索結果に表示される項目を、競合よりも魅力的な文言に変更する必要があります。

まとめ

以上、エクセルのVBAマクロを使って、GA4から抽出した二つの月のPV（ページビュー）を比較し、サイト分析する方法を紹介しました。

もし、VBAが分かる人であれば、今回紹介したVBAマクロの列データを変更することで、「平均エンゲージメント時間」「イベント数」「コンバージョン」の比較もできます。

それほどページが多くない場合は、GA4でPVを使って個別ページの増減を確認する方法も使えます。