ブログのPV数・収益の推移を集計するVBAマクロツール

副業
2022.04.24

本記事では、ブログのPV数・収益・総文字数の推移を集計するVBAマクロツールを紹介します。

これらの数値が簡単に閲覧できるようになると、ブログ運営のモチベーション維持に役立つかと思います。

参考になれば幸いです。

エクセルブックの紹介

エクセルにて、以下のような管理台帳シートを作って、 Googleアドセンスの収支履歴と、Googleアナリティクスの履歴と、ブログの総文字数の推移を集計します。

実際のマクロ入りブックは以下です。

ブックは以下の通りです。

ブログ収支管理ダウンロード

本マクロブックのサポートやカスタマイズについては、条件によって、有償にて対応受付しております。

以下、クラウドワークスないしココナラのアカウントよりご依頼いただければ幸いです。

FIRE太郎さん(その他)に依頼・外注する | 簡単ネット発注なら【クラウドワークス】
日本最大級のクラウドソーシング「クラウドワークス」にご登録いただいているFIRE太郎さんのプロフィールです。その他のスキルを持つプロフェッショナルに1時間から仕事を気軽に発注！発注者は手数料無料。
crowdworks.jp
FIRE太郎さん(会社員)のプロフィール | ココナラ
Excelマクロ（VBA）による自動化ツールの作成が得意です。｜ブログ「FIRE（早期リタイア）研究所」運営中
coconala.com

ブログのPV数・収益の推移を集計するVBAマクロの紹介

マクロの初期設定

まず、初期設定が必要です。

まず、以下記事を参考に、Seleniumの設定をします。

Seleniumを導入し、VBAでchromeを自動操作する設定手順
2022/6/16、InternetExplorer（IE）のサポートが終了しましたが、従来、IEとVBAを使ってWEBスクレイピングされていた方の乗り換え先として、Seleniumを導入し、VBAでchromeを自動操作するための設定手順を紹介します。
hineke.jp
2022.03.04

マクロコードの紹介

マクロのコードは以下の通りです。

Public driver As New ChromeDriver
Public ks As New Keys


Sub ブログ収支管理(tweet_text As String, tweet_post As String, PROFILE_PATH As String, USER_ID_1 As String, USER_PAS1 As String, URL1 As String)

driver.AddArgument ("user-data-dir=" & PROFILE_PATH)
driver.Start "chrome"
Application.Wait Now + TimeSerial(0, 0, 5)
driver.Get "https://www.google.com/adsense/new/u/0/pub-2007356298290020/reporting/?cc=JPY&ag=date&gm=earnings&m=earnings&oc=date&oo=descending&ct=td&oet=false&co=o&savedreport=%E3%81%82&d=last7days"
        driver.Window.Maximize

Application.Wait Now + TimeSerial(0, 0, 10)

For n = 10 To 4 Step -1

    If WorksheetFunction.CountIf(Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Columns(1), CDate(Mid(driver.FindElementByXPath("/html/body/div[1]/bruschetta-app/as-exception-handler/div[2]/div/div/div/main/div/reporting-app/as-exception-handler/report-page/div[2]/report/div/div[3]/div[1]/report-table/ess-table/ess-particle-table/div[1]/div/div[2]/div[" & n & "]/ess-cell[1]/dimension-cell").Text, 1, Len(driver.FindElementByXPath("/html/body/div[1]/bruschetta-app/as-exception-handler/div[2]/div/div/div/main/div/reporting-app/as-exception-handler/report-page/div[2]/report/div/div[3]/div[1]/report-table/ess-table/ess-particle-table/div[1]/div/div[2]/div[" & n & "]/ess-cell[1]/dimension-cell").Text) - 3))) = 0 Then

        Endrow = Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(1048576, 1).End(xlUp).Row + 1
    
        Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 1) = CDate(Mid(driver.FindElementByXPath("/html/body/div[1]/bruschetta-app/as-exception-handler/div[2]/div/div/div/main/div/reporting-app/as-exception-handler/report-page/div[2]/report/div/div[3]/div[1]/report-table/ess-table/ess-particle-table/div[1]/div/div[2]/div[" & n & "]/ess-cell[1]/dimension-cell").Text, 1, Len(driver.FindElementByXPath("/html/body/div[1]/bruschetta-app/as-exception-handler/div[2]/div/div/div/main/div/reporting-app/as-exception-handler/report-page/div[2]/report/div/div[3]/div[1]/report-table/ess-table/ess-particle-table/div[1]/div/div[2]/div[" & n & "]/ess-cell[1]/dimension-cell").Text) - 3))
        Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 2) = Replace(driver.FindElementByXPath("//*[@id='reporting_container']/reporting-app/as-exception-handler/report-page/div[2]/report/div/div[3]/div[1]/report-table/ess-table/ess-particle-table/div[1]/div/div[2]/div[" & n & "]/ess-cell[2]/metric-cell/div").Text, "￥", "")

    End If

Next n


Endrow = Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(1048576, 1).End(xlUp).Row

driver.Get URL1

driver.FindElementByXPath("//*[@id='user_login']").Clear


driver.FindElementByXPath("//*[@id='user_login']").SendKeys USER_ID_1

Application.Wait Now + TimeSerial(0, 0, 1)


driver.FindElementByXPath("//*[@id='user_pass']").SendKeys USER_PAS1

driver.FindElementByXPath("//*[@id='wp-submit']").SendKeys ks.Enter
Application.Wait Now + TimeSerial(0, 0, 5)

driver.Get "https://hineke.jp/%e7%b7%8f%e6%96%87%e5%ad%97%e3%82%ab%e3%82%a6%e3%83%b3%e3%82%bf%e3%83%bc"

Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 14) = driver.FindElementByXPath("//*[@id='post-4048']/div").Text



Set driver = Nothing

driver.AddArgument ("user-data-dir=" & PROFILE_PATH)
driver.Start "chrome"

Workbooks("ブログ収支管理.xlsm").Sheets("ブログ経費").Cells(1, 6) = 0

10:

    If Workbooks("ブログ収支管理.xlsm").Sheets("ブログ経費").Cells(1, 6) < 6 Then
        On Error GoTo ERR10
    Else
        On Error GoTo 0
    End If
    
    Application.Wait Now + TimeSerial(0, 0, 3)
    driver.Get "https://analytics.google.com/analytics/web/?hl=ja#/p267576555/reports/dashboard?params=_u..nav%3Ddefault%26_u..comparisons%3D%5B%7B%22name%22:%22%E3%81%99%E3%81%B9%E3%81%A6%E3%81%AE%E3%83%A6%E3%83%BC%E3%82%B6%E3%83%BC%22,%22filters%22:%5B%7B%22isCaseSensitive%22:true,%22expression%22:%220%22,%22fieldName%22:%22audience%22%7D%5D%7D%5D%26_r..dimension-value%3D%7B%22dimension%22:%22eventName%22,%22value%22:%22page_view%22%7D%26_u.comparisonOption%3DlastPeriod%26_u.dateOption%3Dyesterday%26_u..insightCards%3D%5B%7B%22question%22:%22%E3%83%A6%E3%83%BC%E3%82%B6%E3%83%BC%E3%81%94%E3%81%A8%E3%81%AE%E4%B8%8A%E4%BD%8D%E3%81%AE%E3%82%A4%E3%83%99%E3%83%B3%E3%83%88%E3%81%AF%E3%81%A9%E3%82%8C%E3%81%A7%E3%81%99%E3%81%8B%EF%BC%9F%22%7D%5D%26_r.0..selmet%3D%5B%22eventCount%22%5D&r=events-overview-page-view"
    
    Application.Wait Now + TimeSerial(0, 0, 5)
    
    Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 9) = driver.FindElementByXPath("//*[@id='mat-tab-label-0-0']/span[2]/span[1]/div/ga-viz-container/ga-metric-tab/div/div[2]/div[1]/span").Text
    
    driver.Get "https://analytics.google.com/analytics/web/?hl=ja#/p267576555/reports/dashboard?params=_u..nav%3Ddefault%26_u..comparisons%3D%5B%7B%22name%22:%22%E3%81%99%E3%81%B9%E3%81%A6%E3%81%AE%E3%83%A6%E3%83%BC%E3%82%B6%E3%83%BC%22,%22filters%22:%5B%7B%22isCaseSensitive%22:true,%22expression%22:%220%22,%22fieldName%22:%22audience%22%7D%5D%7D%5D%26_r..dimension-value%3D%7B%22dimension%22:%22eventName%22,%22value%22:%22page_view%22%7D%26_u.comparisonOption%3DlastPeriod%26_u.dateOption%3Dlast30Days%26_u..insightCards%3D%5B%7B%22question%22:%22%E3%83%A6%E3%83%BC%E3%82%B6%E3%83%BC%E3%81%94%E3%81%A8%E3%81%AE%E4%B8%8A%E4%BD%8D%E3%81%AE%E3%82%A4%E3%83%99%E3%83%B3%E3%83%88%E3%81%AF%E3%81%A9%E3%82%8C%E3%81%A7%E3%81%99%E3%81%8B%EF%BC%9F%22%7D%5D%26_r.0..selmet%3D%5B%22eventCount%22%5D&r=events-overview-page-view"
    
    Application.Wait Now + TimeSerial(0, 0, 10)
    
    Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 11) = driver.FindElementByXPath("//*[@id='mat-tab-label-1-0']/span[2]/span[1]/div/ga-viz-container/ga-metric-tab/div/div[2]/div[1]/span").Text
    
    driver.Get "https://analytics.google.com/analytics/web/?hl=ja#/p267576555/reports/dashboard?params=_u..nav%3Ddefault%26_u..comparisons%3D%5B%7B%22name%22:%22%E3%81%99%E3%81%B9%E3%81%A6%E3%81%AE%E3%83%A6%E3%83%BC%E3%82%B6%E3%83%BC%22,%22filters%22:%5B%7B%22isCaseSensitive%22:true,%22expression%22:%220%22,%22fieldName%22:%22audience%22%7D%5D%7D%5D%26_r..dimension-value%3D%7B%22dimension%22:%22eventName%22,%22value%22:%22page_view%22%7D%26_u.comparisonOption%3DlastPeriod%26_u.dateOption%3Dlast12Months%26_u..insightCards%3D%5B%7B%22question%22:%22%E3%83%A6%E3%83%BC%E3%82%B6%E3%83%BC%E3%81%94%E3%81%A8%E3%81%AE%E4%B8%8A%E4%BD%8D%E3%81%AE%E3%82%A4%E3%83%99%E3%83%B3%E3%83%88%E3%81%AF%E3%81%A9%E3%82%8C%E3%81%A7%E3%81%99%E3%81%8B%EF%BC%9F%22%7D%5D%26_r.0..selmet%3D%5B%22eventCount%22%5D&r=events-overview-page-view"
    
    Application.Wait Now + TimeSerial(0, 0, 19)
    
    Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 13) = driver.FindElementByXPath("//*[@id='mat-tab-label-2-0']/span[2]/span[1]/div/ga-viz-container/ga-metric-tab/div/div[2]/div[1]/span").Text
    
    
    On Error GoTo 0

Workbooks("ブログ収支管理.xlsm").Sheets("ブログ経費").Cells(1, 6) = 0

11:

    If Workbooks("ブログ収支管理.xlsm").Sheets("ブログ経費").Cells(1, 6) < 6 Then
        On Error GoTo ERR11
    Else
        On Error GoTo 0
    End If

    driver.Get "https://twitter.com/compose/tweet"
    
    '総文字数
    sTmp0 = ""
    If Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 15) > 0 Then
    sTmp0 = "+"
    End If
    
    
    '当日PV
    sTmp1 = ""
    If Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 10) > 0 Then
    sTmp1 = "+"
    End If
    
    
    '直近30日PV
    sTmp2 = ""
    If Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 12) > 0 Then
    sTmp2 = "+"
    End If
    
    'アドセンス当日収益
    sTmp3 = ""
    If Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 3) > 0 Then
    sTmp3 = "+"
    End If
    
    'アドセンス直近30日収益
    sTmp4 = ""
    If Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 7) > 0 Then
    sTmp4 = "+"
    End If
    
    '経費累計
    sTmp5 = ""
    If Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 16) - Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow - 1, 16) > 0 Then
    sTmp5 = "+"
    End If
    
    '利益累計
    sTmp6 = ""
    If Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 17) - Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow - 1, 17) > 0 Then
    sTmp6 = "+"
    End If
    
    
    driver.FindElementByXPath(tweet_text).SendKeys _
    Format(Now - 1, "yy/m/d") & "ブログ成績" & vbLf & _
    vbLf & _
    "総文字数　：" & Format(Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 14), "###,###") & "(" & sTmp0 & Format(Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 15), "###,###") & ")" & vbLf & _
    vbLf & _
    "PV" & vbLf & _
    "当日　：" & Format(Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 9), "###,###") & "(" & sTmp1 & Format(Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 10), "###,###") & ")" & vbLf & _
    "30日　：" & Format(Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 11), "###,###") & "(" & sTmp2 & Format(Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 12), "###,###.##%") & ")" & vbLf & _
    "12ヵ月：" & Format(Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 13), "###,###") & vbLf & _
    vbLf & _
    "アドセンス収益" & vbLf & _
    "当日：" & Format(Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 2), "###,###") & "円(" & sTmp3 & Format(Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 3), "###,###") & ")" & vbLf & _
    "30日：" & Format(Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 4), "###,###") & "円(" & sTmp4 & Format(Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 6), "###,###") & ")" & vbLf & _
    "累計：" & Format(Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 8), "###,###") & "円" & vbLf & _
    vbLf & _
    "経費累計：" & Format(Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 16), "###,###") & "円" & "(" & sTmp5 & Format(Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 16) - Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow - 1, 16), "###,###") & ")" & vbLf & _
    "利益累計：" & Format(Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 17), "###,###") & "円" & "(" & sTmp6 & Format(Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow, 17) - Workbooks("ブログ収支管理.xlsm").Sheets("ブログ収益").Cells(Endrow - 1, 17), "###,###") & ")" & vbLf & _
    vbLf & _
    "https://hineke.jp/" & vbLf & _
    "#ブログ #FIRE #副業 " _
    
    driver.FindElementByXPath(tweet_post).Click



    Application.Wait Now + TimeSerial(0, 0, 3)
    
    On Error GoTo 0

Set driver = Nothing

Exit Sub

ERR10:
    Workbooks("ブログ収支管理.xlsm").Sheets("ブログ経費").Cells(1, 6) = Workbooks("ブログ収支管理.xlsm").Sheets("ブログ経費").Cells(1, 6) + 1
    Resume 10
    
ERR11:
    Workbooks("ブログ収支管理.xlsm").Sheets("ブログ経費").Cells(1, 6) = Workbooks("ブログ収支管理.xlsm").Sheets("ブログ経費").Cells(1, 6) + 1
    Resume 11


End Sub

コードの中の、「tweet_text, tweet_post」にセットする値は以下記事を参照ください。

chromeでTwitterにtweetするExcelマクロ(VBA)
FIREを目指し、収入増加を目的にSNS運用をしているものの、時間の制約などから、運用の効率化に課題を感じている方、多いのではないかと思います。この記事では、VBAでchromeを使ってTwitterにtweetする方法を紹介します。SNS運用の参考にしていただければ幸いです。
hineke.jp
2021.07.18

集計したついでに、Twitterに集計結果をtweetする機能を設けています。以下のような感じ。

まとめ

いかがでしたでしょうか。

参考になれば幸いです。

その他、マクロツールは以下記事にて紹介しています。

エクセルマクロ（VBA）を活用し、生産性を向上させる
FIRE（早期リタイア）を実現するためには、一定の資産の蓄財と、当該資産が、生涯にわたって尽きないという状態が必要です。 その状態を早期に達成するためには、仕事の生産性を向上させるのが効果的です。仕事の生産性を向上させると...
hineke.jp
2022.04.19

コメント

タイトルとURLをコピーしました