山本ゆうごです。Googleanalyticsを使っている方は多いと思います。

自分しか見ないときにはあまり気にしませんが、いざちゃんとレポーティングしようとすると、エクセルに取り込んであれこれ分析してきれいなグラフにしたくなります。

そしていざ、analyticsをエクセルに取り込もうとすると意外に面倒。

analyticsにはcsvエクスポート機能があるのですが、これがくせ者。

1. 文字コードがUTF8なので、エクセルに取り込むと化ける（なのでいったんテキストエディタなどでエンコード変換必要）
2. CSVとは言いながら表の形式じゃない（なので手作業で修正が必要。ＴＳＶも同様）

やってできないことはありませんが、こういうレポーティングの作業は出来るだけルーチン化したいところ。Webの業務といえど、業務の内容としては、会計情報のレポーティングに近い。部署を考えれば、WindowsのExcelで完結すれば業務の手離れもいい。

そこで一気にエクセルに取り込めないかと。そこで作りましたよ。

以下、結構面倒なことで課題をクリアしています。

認証はXMLHTTPオブジェクトを使ってIEのセッションを流用

MSXMLのXMLHTTPオブジェクトは、その名の通り本当はXMLをHTTPで取得するオブジェクトなのですが、これをHTTPクライアントとして使ってしまいます。XMLHTTPオブジェクトはIEのクッキーを共有しているっぽいので、IEでAnalyticsにログインしていると、ログインフォームを経由せずにいきなり対象のURLをGETすることが可能です。

取得するのはCSVじゃなくてTSV

タブ区切りデータの方が、エクセルでは扱いやすい。レポートの形式としては、XMLという選択もあるのですが、エクセルに表示する段階で結局は表形式にしなくちゃいけないので、TSVを選択します。

エクセルに取り込むのはクリップボード経由

中間ファイルを作らずに横着しようとしたので、クリップボード経由でコピー＆ペーストしています。VBAそのものにはクリップボードにアクセスできないので、クリップボードにアクセスするためだけにテキストBOXにタブ区切りテキストをセットして、全選択して、コピーなんて非エレガントなことをやっています。

エクセルの大量のセルに値をセットする場合というのは、実はマクロで一個一個のセルにアクセスするとすごく遅くなるので、一気にペーストした方がまだ速いのです。

まとめ

あまりエレガントではないものの、今回のシナリオでは、「エクセルでレポート」という出口は与件としてロックされています。なので、ロックされたステップから出来るだけ浅いレイヤーで設計したというのがポイントです。裏では鈍くさいことをしていても、ユーザから見た場合の「浅さ」がポイントです。

サンプルソース

Sub pasteTSV(strID As String, strFrom As String, strTO As String)
    Dim txt As String
    Dim xml As MSXML2.XMLHTTP
    Set xml = New MSXML2.XMLHTTP
    Dim aryText As Variant
    Dim strUrl As String
    strUrl = "https://www.google.com/analytics/reporting/export?fmt=3&id=" & strID
    strUrl = strUrl & "&pdr=" & strFrom & "-" & strTO & "&cmp=average&rpt=TopContentReport&trows=5000&"
    xml.Open "GET", strUrl, False
    xml.send
    txt = xml.responseText
    Dim ptrTable As Long
    Dim ptrStart As Long
    Dim ptrEnd As Long
    ptrTable = InStr(1, txt, vbLf & "# Table")
    If ptrTable = 0 Then
        MsgBox "データが取得できません"
        Debug.Print strUrl
        End
    End If
    ptrStart = InStr(ptrTable, txt, "---" & vbLf)
    If ptrStart > 0 Then
        txt = Mid(txt, ptrStart + 4)
        ptrEnd = InStr(1, txt, vbLf & "# ---")
        If ptrEnd > 0 Then
            txt = Mid(txt, 1, ptrEnd - 1)
        End If
    End If
    Debug.Print txt
    'テキストBOXコントロールを使用してクリップボードにコピーする
    UserForm1.Show vbModeless
    UserForm1.TextBox1.Text = txt
    UserForm1.TextBox1.SelStart = 0
    UserForm1.TextBox1.SelLength = Len(txt)
    UserForm1.TextBox1.Copy
    UserForm1.Hide
    Worksheets("paste").Activate
    cells(1, 1).Select
    ActiveSheet.Paste
End Sub

レポートのIDとFROM（YYYYMMDD）とTO（YYYYMMDD）を入れるとそのURLからデータを取ってきて、欲しい位置を切り取っているだけです。このサンプルでは、TopContentReport（上位のコンテンツ）です。

このソースが含まれている、マクロつきエクセルもUPします。

Ｇoogle Analytics のダウンロードマクロつきエクセル

1. IEであらかじめ、Google Analyticsにログインし
2. マクロつきエクセルをマクロを有効にして開き
3. レポートID、FROM、TOをセットして機動

でOK。