楽天ペイの利用履歴を集計してみた

はじめに

楽天ペイの利用履歴の確認はアプリでは行えず、Web画面も楽天ポイントの履歴から確認となり、家計簿アプリとの連携もできないため集計などができず使い道の振り返りが難しいと感じます。
そこで、楽天ペイから送付される利用のお知らせから情報を抜き出してダッシュボードを作成してみました。
作成に当たりt-chi様のmailから始める自動化生活の記事を大いに参照させていただきました。ありがとうございました。詳細な手順についてはこちらをご参照ください。

全体の流れ

Gmailで受信した楽天ペイからの利用のお知らせをGoogleスプレッドシートへ書き出しを行い、利用日、店舗名、金額を抜き出します。GoogleスプレッドシートをデータソースとしてLooker Studioでダッシュボードを作成します。

Gmailのラベル作成

Gmailのラベルとフィルタを作成します。

ラベル名	フィルタ	説明
楽天ペイ利用	有	受信メールに適用するラベル
GSA処理済	無	AppScriptで処理後に付与するラベル

label:楽天ペイ利用 は楽天ペイ利用した際に受信するメールへフィルタを適用してください。AppScript内で使用します。
条件はFROMと件名の一致でほぼ問題は発生していません。
参照：Gmail の自動振り分け設定
label:GSA処理済はAppScriptで処理が終わっているメールをマーキングするためのラベルです。AppScript内でもラベルの存在チェックが行われるので、手動で作成しなくても問題ありません。

Googleスプレッドシートの作成

スプレッドシートを新規作成します。
ファイル名：楽天ペイアプリご利用内容確認メール ※任意の名前
シート名:テーブル ※シート1から変更。AppScript内で使用します。

シートの下記セルにカラムのタイトルを入力します。

	A	B	C	D	E	F
1	日付	差出人	件名	内容	ID	URL

AppScriptの作成

code

var mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('テーブル');

// searchContactMail関数を定義します。Gmailのメッセージを検索し、処理するための関数です。
function searchContactMail() {
  
  // "gsa処理済"ラベルの取得。存在しない場合は新たに作成します。
  var mylabel = GmailApp.getUserLabelByName('gsa処理済');
  if (!mylabel) {
    mylabel = GmailApp.createLabel('gsa処理済');
  }

  // "楽天ペイ利用"ラベルが付いていて、"gsa処理済"ラベルが付いていないメールを検索するクエリ
  var strTrams = 'label:楽天ペイ利用 -label:gsa処理済';  // gsa処理済のラベルを除外して検索
  var myThreads = GmailApp.search(strTrams, 0, 20);  // 上記の条件で最大20件のスレッドを取得

  var ValMsgs = [];  // メッセージ情報を格納するための配列を初期化

  // 検索結果から取得した各スレッドを順に処理します
  for (var i = 0; i < myThreads.length; i++) {
    var msgs = myThreads[i].getMessages();  // スレッド内のメッセージを取得

    // スレッド内の各メッセージを順に処理します
    for (var j = 0; j < msgs.length; j++) {
      var msg = msgs[j];  // 現在のメッセージを取得
      var msid = msg.getId();  // メッセージのIDを取得

      // メッセージの詳細情報を収集
      var date = msg.getDate();  // メッセージの日付を取得
      var from = msg.getFrom();  // メッセージの差出人を取得
      var subj = msg.getSubject();  // メッセージの件名を取得
      var body = msg.getBody().slice(0, 10000);  // メッセージ本文の最初の10000文字を取得
      var perm = myThreads[i].getPermalink();  // スレッド全体のリンクを取得

      // 収集したメッセージ情報を配列に追加
      ValMsgs.push([date, from, subj, body, msid, perm]);
    }

    // スレッド全体に "gsa処理済" ラベルを追加
    myThreads[i].addLabel(mylabel);
  }

  // 収集したデータが存在する場合、それをスプレッドシートに書き込みます
  if (ValMsgs.length > 0) {
    var lastRow = mySheet.getLastRow();  // 現在のシートの最後の行を取得
    mySheet.getRange(lastRow + 1, 1, ValMsgs.length, 6).setValues(ValMsgs);  // 新しいデータを書き込み
  }
}

実行に成功するとテーブルシートのA～F列にメールから取得した情報が書き込まれます。
D列にメールの本文がHTMLで書きこまれます。

トリガーの設定

スクリプトの自動実行設定を行います。AppScript画面の左側の時計のアイコンから設定します。

• 実行する関数を選択: serchCotactMail
• デプロイ時に実行: Head
• イベントのソースを選択: 時間主導型
• 時間ベースのトリガーのタイプを選択: 日付ベースのタイマー
• 時刻を選択: 午前4 時～5 時
• エラー通知設定: 毎日通知を受け取る

Googleスプレッドシートのデータ取り出し

下記の受信メールの内容をHTMLから各セルに取り出しをします。

シートを追加します。
シート名: クエリ ※シート2から変更
A1:

=SORT('テーブル'!A:F, 1, FALSE)

SORT関数でテーブルシートのAからF列('テーブル'!A:F)をA列(1)を降順(FALSE)にソートします。

テーブルのカラム名を設定します。

	G	H	I	J
1	年月	伝票番号	ご利用店名	決済総額

D列のHTMLから各要素を取り出します。
G2:

=IF(D2<>"", TRIM(REGEXEXTRACT(D2, "ご利用日時\s*</td>\s*<td[^>]*>([^<]*)")), "")

H2:

=IF(D2<>"", TRIM(REGEXEXTRACT(D2, "伝票番号\s*</td>\s*<td[^>]*>([^<]*)")), "")

I2:

=IF(D2<>"", TRIM(REGEXEXTRACT(D2, "ご利用店舗\s*</td>\s*<td[^>]*>([^<]*)")), "")

J2:

=IF(D2<>"", VALUE(SUBSTITUTE(SUBSTITUTE(TRIM(REGEXEXTRACT(D2, "決済総額\s*</td>\s*<td[^>]*>([^<]*)")), "&yen;", ""), ",", "")), "")

関数は下の行に自動コピーしてください

解説

1. D2の値が空欄でないことを確認します。
2. REGEXEXTRACT関数を用いて、D2の値から正規表現のグループ化を用いてご利用日時の後ろのタグタグの間の文字列をキャプチャしています。
3. TRIM関数で不要な改行や空白を取り除きます。

J1の関数はさらに\¥を取り除くためにSUBSTITUTE関数を使用して""空白に置き換えをしています。

設定すると下の画像のようになります。

Looker Studioでダッシュボードの作成

LookerStudioを開き新規レポートを作成します。

上記のような画面を作成していきます。
左上の期間を選択すると表、積み上げグラフ、ツリーマップのデータが選択した期間のものに変更されます。

コントロール: 期間設定

• デフォルトの期間範囲: 過去30日(今日を除く)

グラフ: スコアシート

• 期間のディメンション: 日付
• 指標: 決済総額
  • データの種類: 通貨(JPY -円 (¥))
  • 表示フォーマット: Financial(0)
  • 集計方法: 合計
  • 比較合計: なし
  • 関数: なし
• デフォルトの日付範囲: 自動

グラフ: 表

• 期間のディメンション: 年月(日付)
• ディメンション:
  • 年月(日付),
    • データの種類: 日付
  • 伝票番号,
  • ご利用店舗,
  • 決済総額
    • データの種類: 通貨(JPY -円 (¥))
    • 表示フォーマット: Financial(0)
• 並べ替え: 年月(日付)
  • データの種類: 日付

グラフ: 積み上げ縦棒グラフ

• 期間のディメンション: 年月(日付)
• ディメンション:
  • 年月(日付),
    • データの種類: 日付
  • ご利用店舗,
• 内訳ディメンション:
  • ご利用店舗,
• 指標: 決済総額
  • データの種類: 通貨(JPY -円 (¥))
  • 表示フォーマット: Financial(0)
  • 集計方法: 合計
  • 比較合計: なし
  • 関数: なし
• 並べ替え: 年月(日付)
  • データの種類: 日付

グラフ: ツリーマップ

• 期間のディメンション: 年月(日付)

  • データの種類: 日付

• ディメンション: ご利用店舗

• 並べ替え: 年月(日付)

  • データの種類: 日付

• 指標: 決済総額

  • データの種類: 通貨(JPY -円 (¥))
  • 表示フォーマット: Financial(0)
  • 集計方法: 合計
  • 比較合計: なし
  • 関数: なし

• デフォルトの日付範囲: 自動

説明

期間設定でページに表示するデータの期間を指定します。
スコアシートで表示している決算金額の合計を表示します。
表で指定しているデータの一覧が表示します。
積み上げ縦棒グラフで日毎の金額と店舗の内訳を棒グラフで表示します。棒グラフをクリックすると表示するデータを指定します。
ツリーマップで期間内の店舗ごとの金額を表示します。店舗をクリックすると表示するデータを指定します。

おわりに

定型メールからデータを取り出しているので、楽天ペイ以外にもいろいろなデータを扱うことができると思います。

操作説明の部分はほぼ省略してしまったので調べながら操作してもらうような記事になってしまって申し訳ありません。