ExcelのVBAマクロにてGoogle Analyticsの訪問数・PV数を日ごと集計①

Google Analytics APIを利用すると様々なカスタムビューをXMLデータとして取得することが可能ですが、分析するにあたって行きつくところExcel等に取り込んで加工なりすることになると思います。

この「取り込んで」というのが、毎日の定常作業である場合、非常に面倒であるため、Excel自身に取得させて直接表示しようというのが今回のミッションです。

注意

本記事内で利用しているGoogleクライアントログイン（ClientLogin）による認証方式および、Google Analytics API v2.2は現在利用できません。
以下に新しい認証方式であるOAuth2.0および、Google Analytics API v3を利用したアクセス方法をまとめていますので、こちらをご覧ください。

OAuth認証による Excel VBA Google Analytics APIアクセス　①

やり方は２とおり

大きくやり方は次の２とおりあります。

IE（Internet Explorer）をVBAマクロから操作して取得する
HTML DOMオブジェクト操作にて取得する

今回は、IEを操作して取得する方法を記載します。

計測開始日、計測終了日を引数指定してその間のセッション数・PV数を取得する

Option Explicit
'Win32API(Sleep)
Public Declare PtrSafe Sub Sleep Lib "Kernel32" (ByVal dwMilliseconds As Long)
 
'PV、セッション数取得インターフェース
Public Function getPvSession_Auto(sDate As Date, eDate As Date)
    Dim oIE As Object  'IEオブジェクト
    Dim sURL As String
    Dim lPv As Long
    Dim lSess As Long
     
    ' IE起動
    Set oIE = CreateObject("InternetExplorer.Application")
    oIE.Visible = True
    Call waitIE(oIE)
     
    'クエリ文字列生成
    sURL = "https://www.google.com/analytics/web/?hl=ja&pli=1#report/visitors-overview/a99999999w99999999p99999999/"
    sURL = sURL & "%3F_.date00%3D" & Format(sDate, "yyyymmdd")
    sURL = sURL & "%26_.date01%3D" & Format(eDate, "yyyymmdd") & "/"
    '指定URL遷移
    Call oIE.Navigate(sURL)
    If waitIE(oIE) Then
        Call Sleep(500)
        'ページビュー・セッション数取得
        Call getPvSession(oIE, lPv, lSess)
    End If
     
    '取得したセッション数（lSess）とPV数（lPV）を取り扱う処理を記載
    '～～～
 
    ' IE破棄
    oIE.Quit
    Set oIE = Nothing
     
End Function
 
'ページビュー・セッション数取得
Private Sub getPvSession(ie As Object, lPv As Long, lSess As Long)
    Dim s1 As String
    Dim vTags As Variant
     
    Static sPvSessionClassName As String
     
    If sPvSessionClassName = "" Then
        sPvSessionClassName = getPvSessionClassName(ie)
    End If
    If sPvSessionClassName = "NOTFOUND" Then
        Err.Raise 50001, , "PvSessionクラス名取得できず"
    End If
     
    Set vTags = ie.Document.getElementsByClassName(sPvSessionClassName)
     
    'ページビュー数
    s1 = vTags(2).innerText
    s1 = Replace(s1, ",", "")
    lPv = Val(s1)
    'セッション数（訪問数）
    s1 = vTags(0).innerText
    s1 = Replace(s1, ",", "")
    lSess = Val(s1)
     
End Sub
 
'アナリティクスページタグ解析
Private Function getPvSessionClassName(ie As Object) As String
    Dim oElement As Object
    Dim oElement0 As Object
    Dim oElementX As Object
    Dim s1 As String
    Dim b1 As Boolean
     
    'divタグを全てチェックし、class属性に"ID-tooltipcontent-0"を含む要素を探す
    '→"ID-tooltipcontent-0"を持つ要素とは、ページ上の「セッション」と書かれた見出し部分である。
    For Each oElement In ie.Document.getElementsByTagName("div")
        If oElement.hasAttribute("class") Then
            s1 = oElement.getAttribute("class")
            If InStr(s1, "ID-tooltipcontent-0") > 0 Then
                b1 = True
                Set oElement0 = oElement
                Exit For
            End If
        End If
    Next
     
    If Not b1 Then
        '見つからなかったら文字"NOTFOUND"を返して終わる
        getPvSessionClassName = "NOTFOUND"
        Exit Function
    End If
     
    '目的の要素まで移動(現状「セッション」の次の要素の２階層下の要素である。)
    Set oElementX = oElement0.NextSibling.FirstChild.FirstChild
     
    '念のためclass属性を持っているかチェック
    If Not oElementX.hasAttribute("class") Then
        '見つからなかったら文字"NOTFOUND"を返して終わる
        getPvSessionClassName = "NOTFOUND"
        Exit Function
    End If
     
    'クラス名を返却
    getPvSessionClassName = oElementX.getAttribute("class")
     
End Function
 
'IEがビジー状態の間wait
Public Function waitIE(ie As Object) As Boolean
On Error Resume Next
    Dim iCnt As Integer
     
    iCnt = 0
     
    Do
        'IE描画完了している
        If ie.Busy = False And ie.readystate = 4 Then
            waitIE = True
            Exit Function
        End If
         
        Sleep 200
        iCnt = iCnt + 1
        If iCnt >= 100 Then
            Exit Do
        End If
    Loop
 
    'タイムアウト
    waitIE = False
 
End Function

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

Option Explicit

'Win32API(Sleep)

Public Declare PtrSafe Sub Sleep Lib "Kernel32" (ByVal dwMilliseconds As Long)

'PV、セッション数取得インターフェース

Public Function getPvSession_Auto(sDate As Date, eDate As Date)

Dim oIE As Object 'IEオブジェクト

Dim sURL As String

Dim lPv As Long

Dim lSess As Long

' IE起動

Set oIE = CreateObject("InternetExplorer.Application")

oIE.Visible = True

Call waitIE(oIE)

'クエリ文字列生成

sURL = "https://www.google.com/analytics/web/?hl=ja&pli=1#report/visitors-overview/a99999999w99999999p99999999/"

sURL = sURL & "%3F_.date00%3D" & Format(sDate, "yyyymmdd")

sURL = sURL & "%26_.date01%3D" & Format(eDate, "yyyymmdd") & "/"

'指定URL遷移

Call oIE.Navigate(sURL)

If waitIE(oIE) Then

Call Sleep(500)

'ページビュー・セッション数取得

Call getPvSession(oIE, lPv, lSess)

End If

'取得したセッション数（lSess）とPV数（lPV）を取り扱う処理を記載

'～～～

' IE破棄

oIE.Quit

Set oIE = Nothing

End Function

'ページビュー・セッション数取得

Private Sub getPvSession(ie As Object, lPv As Long, lSess As Long)

Dim s1 As String

Dim vTags As Variant

Static sPvSessionClassName As String

If sPvSessionClassName = "" Then

sPvSessionClassName = getPvSessionClassName(ie)

End If

If sPvSessionClassName = "NOTFOUND" Then

Err.Raise 50001, , "PvSessionクラス名取得できず"

End If

Set vTags = ie.Document.getElementsByClassName(sPvSessionClassName)

'ページビュー数

s1 = vTags(2).innerText

s1 = Replace(s1, ",", "")

lPv = Val(s1)

'セッション数（訪問数）

s1 = vTags(0).innerText

s1 = Replace(s1, ",", "")

lSess = Val(s1)

End Sub

'アナリティクスページタグ解析

Private Function getPvSessionClassName(ie As Object) As String

Dim oElement As Object

Dim oElement0 As Object

Dim oElementX As Object

Dim s1 As String

Dim b1 As Boolean

'divタグを全てチェックし、class属性に"ID-tooltipcontent-0"を含む要素を探す

'→"ID-tooltipcontent-0"を持つ要素とは、ページ上の「セッション」と書かれた見出し部分である。

For Each oElement In ie.Document.getElementsByTagName("div")

If oElement.hasAttribute("class") Then

s1 = oElement.getAttribute("class")

If InStr(s1, "ID-tooltipcontent-0") > 0 Then

b1 = True

Set oElement0 = oElement

Exit For

End If

If Not b1 Then

'見つからなかったら文字"NOTFOUND"を返して終わる

getPvSessionClassName = "NOTFOUND"

Exit Function

End If

'目的の要素まで移動(現状「セッション」の次の要素の２階層下の要素である。)

Set oElementX = oElement0.NextSibling.FirstChild.FirstChild

'念のためclass属性を持っているかチェック

If Not oElementX.hasAttribute("class") Then

'見つからなかったら文字"NOTFOUND"を返して終わる

getPvSessionClassName = "NOTFOUND"

Exit Function

End If

'クラス名を返却

getPvSessionClassName = oElementX.getAttribute("class")

End Function

'IEがビジー状態の間wait

Public Function waitIE(ie As Object) As Boolean

On Error Resume Next

Dim iCnt As Integer

iCnt = 0

'IE描画完了している

If ie.Busy = False And ie.readystate = 4 Then

waitIE = True

Exit Function

End If

Sleep 200

iCnt = iCnt + 1

If iCnt >= 100 Then

Exit Do

End If

Loop

'タイムアウト

waitIE = False

End Function

※アクセス先URLのhttps://www.google.com/analytics/web/?hl=ja&pli=1#report/visitors-overview/a99999999w99999999p99999999/ について、99999999の部分は計測したいGoogleAnalyticsレポートのトップページURLによって修正してください。

使い方

sDateとeDateの２つの引数にDate型で日付を指定してgetPvSession_Autoを呼び出します。
毎日自動実行させるなら実行当日の日付を指定するのがよいでしょう。
実際にこれで取得した日次の訪問数（セッション数）とページビュー数がそれぞれ、lSessとlPVという２つの変数に数値として設定されます。
今回はその変数をどう利用するかはあえて記載していません。
縦軸を日付とした集計シートにセットするAutoマクロとして実装する等よしなに利用すればよいと思いますので。

IE操作型マクロの利点

１つはマクロ実行をするとIEオブジェクトのVisible（可視）をTrueにして実行するので、実行進捗が目で見てわかるということ。
それにより、何かしら不具合（Google Analytics画面のリニューアルがされたときに動かなくなった場合等）をすぐ視認できるということ。
たまに表示されるGoogleアカウント認証（ログイン画面）を手打ちすることでマクロを止めずに続行することができること。
逆に欠点は、 HTML DOMタイプよりも低速であること。
たまにGoogleアカウント認証画面をはさんでしまうことで、完全自動化には向いていないこと。

となります。

miminoinu

Take technical articles etc ...

ExcelのVBAマクロにてGoogle Analyticsの訪問数・PV数を日ごと集計①

注意

やり方は２とおり

計測開始日、計測終了日を引数指定してその間のセッション数・PV数を取得する

使い方

IE操作型マクロの利点