媒体社から届く掲載実績のPDFレポートを読み取って、広告主ごとの統合レポートに転記する

処理の起点を決める

Apps Script の時間主導のトリガーで、1時間ごとに動かします。 Gmail で媒体社からのメールに自動でラベルが付くようにしておき、ラベルの付いた未処理のメールからPDFを取り出して受領フォルダに保存します。 取り出したメールには「処理済み」のラベルを付け直します。

届いたときに即座に動かす形にはしません。 統合レポートの締めは月に1回で、1時間の遅れは問題になりません。 時間主導のトリガーは、たとえば9時に設定しても9時から10時のあいだで実行時刻が少しずれるとされていますが、この業務では影響がありません。

インストール型トリガーは、作った人のアカウントで動くとされています。メディア部門の誰が受け取ったメールでも処理できるよう、媒体社からのメールを受ける共用のアカウントで作ります。 担当者の個人のアカウントで作ると、その人が異動したときに止まります。

締めの週だけ、担当者が手で動かす入口も用意します。 スプレッドシートのメニューから「いますぐ取り込む」を実行できるようにし、急ぐ広告主の分をその場で処理できるようにします。

入力データを集める

質を決めるのは、列の対応表と発注台帳です。 対応表が無ければ、毎月AIに対応づけを提案させることになり、確認の手間が減りません。発注台帳に回数や面数が入っていなければ、掲載が発注どおりだったかを確かめる先がありません。

媒体社の特定は、差出人のメールアドレスから行います。 レポートの中の媒体社名は、ロゴの画像だったり略称だったりして、読み取りでは確実に取れないことがあります。 差出人で引けないとき（転送されてきたときなど）に限り、読み取った本文から探します。

データの取得方法を決める

表のセルの文字は、text の位置で引きます。 読み取り結果では、全文の text が「正本」になっていて、表のセルやキーと値の組は、textAnchor の開始と終了の位置で text の一部を指しています。セルの中身を取るには、この位置で text を切り出します。

発注番号は、キーと値の組から取ります。 媒体社のレポートの多くに「御社発注番号」「オーダーNo.」のような欄があり、formFields に出ます。欄の名前が媒体社ごとに違うので、媒体社の一覧に欄の名前を持たせておきます。 発注番号が無いレポートは、広告主名・媒体社・期間で発注台帳を引きます。

AIへ渡す前に整形する

1. ファイルの形式を確かめる … PDFと画像だけを受け付けます。ExcelやCSVで届いたものは、読み取りをせずに直接取り込む経路に回します
2. ページ数を確かめる … 15ページを超えるものは、ページを分けて送るか、バッチ処理に回します
3. パスワード付きのPDFを分ける … 開けないものは確認の一覧に回し、媒体社にパスワードを確かめます
4. 同じレポートの重複を検知する … 媒体社・発注番号・期間が同じものが既にあれば、差し替え版か重複かを担当者に確かめます
5. 統合レポートの列の一覧を用意する … 対応づけの先になる列（媒体、期間、回数、面数、表示回数、クリック数、費用など）を固定の名前で持ちます

4番目は、媒体社が訂正版を送ってくるときに効きます。 「先日のレポートに誤りがありました」と訂正版が届いたとき、両方を転記すると数値が倍になります。 同じ発注・同じ期間のものは自動で上書きせず、担当者が差し替えを決めます。

AIに処理させる

させるのは、レポートの表の列の名前を、統合レポートの列に対応づける案を作ることだけです。 数値には触れさせません。

単位の読み取りを分けているのが、この表の要点です。 媒体社によっては表示回数を「千回」単位で、費用を「税抜」で載せています。列の対応が正しくても、単位を取り違えると数値が1,000倍ずれたり、税の分だけずれたりします。 単位が書かれていなければ推測させず、担当者に確かめてもらいます。

1行目と2行目が、この構成の要です。 AIに数値を転記させれば、表の読み取りから転記まで1回で済むように見えます。しかし、読み取れなかったセルをAIが前後から補うと、第1章の検算が合ってしまいます。 検算が合うように埋められた数値は、もう検算では見つかりません。

指示内容を固定する

あなたは広告代理店のメディア部門で、媒体社から届いた掲載実績の
レポートの表を、自社の統合レポートの列に対応づける立場です。
渡す表のヘッダと、先頭の3行だけを見て判断してください。
推測で補わないでください。

【やること】
1. レポートの表の各列について、統合レポートの列の一覧の
   どれに当たるかを target に書いてください。
   当たるものが無ければ "unmapped" にしてください。
2. 各列の単位を unit に書いてください。
   列の名前か、表の注記に書かれている単位だけを使ってください。
   （例：「回」「千回」「円」「千円」「税込」「税抜」）
   書かれていなければ "unknown" にしてください。
3. 表の中で、合計や小計に当たる行があれば、その行番号を
   total_rows に書いてください。
4. 対応づけの根拠を reason に1文で書いてください。

【厳守事項】
- 数値を書き写さないでください。数値を計算しないでください。
- 単位を推測しないでください。「表示回数なら回」のように、
  一般的な単位を当てはめないでください。
- 1つの列を、統合レポートの2つの列に対応づけないでください。
- 統合レポートの2つの列に、同じレポートの列を対応づけないでください。
- 列の名前が似ていても、意味が違うと考えられる場合は
  "unmapped" にし、reason に理由を書いてください。
  （例：「掲出面数」と「掲出枚数」、「配信数」と「表示回数」）

【媒体社】{media_name}
【統合レポートの列の一覧】{target_columns}
【前回までの対応表（あれば）】{previous_mapping}
【レポートの表のヘッダ】{header_rows}
【レポートの表の先頭3行】{sample_rows}

渡すのが「ヘッダと先頭の3行だけ」なのは、数値を扱わせないためです。 表の全部を渡すと、AIは数値にも目を向け、合計が合わないことに気づいて「おそらくこの値の読み取りの誤り」と補正しようとします。 列の意味を決めるのにヘッダと数行で足りるなら、それ以上は渡しません。入力が短くなるので、費用も抑えられます。

「似ていても意味が違う列」の例を名指しで書いているのは、媒体の業界で実際に混ざりやすいからです。 交通広告の「面数」は掲出した場所の数、「枚数」はポスターの枚数で、1面に2枚貼ることもあります。 Web媒体の「配信数」と「表示回数」も、媒体社によって定義が違います。

出力形式を固定する

次の形のJSONで受け取ります。 Claude API の構造化出力で、スキーマに沿った形で返させます。

{
  "media_code": "",
  "columns": [
    {
      "source_index": 0,
      "source_name": "",
      "target": "media | period_start | period_end | insertions | faces | impressions | clicks | cost | unmapped",
      "unit": "",
      "reason": ""
    }
  ],
  "total_rows": [],
  "notes": ""
}

1つ目の理由は、そのまま列の対応表の行になることです。 担当者が確かめて承認すると、columns がその媒体社の対応表に登録され、翌月からはAIを呼ばずに、この対応表で転記します。

2つ目は、source_index で、読み取り結果の表の列の位置と結びつくことです。 Document AI の表のセルは行と列の順に並んでいるので、列の位置が決まれば、各行のセルからその列の文字を取り出せます。 数値の転記は、この位置を使ってプログラムが行います。

3つ目は、total_rows で検算の相手が決まることです。 合計行を明示させておくと、合計行を1行の実績として転記してしまう失敗を防げます。

たとえば地域のWeb媒体のレポートなら、対応づけは次のようになります。

5行目の unknown が出たら、担当者が媒体社の請求書や発注書を見て決めます。 決めた単位は対応表に書き込み、翌月からは確かめ直しません。CTRのように統合レポートに列の無いものは、転記しません。 率は統合レポートの側で、表示回数とクリック数から計算し直します。

転記の後の検算は、プログラムの側で行います。

システムへ連携する

発注台帳には書き込みません。 発注と実績の食い違いを見つけても、発注台帳を直すのは発注を担当した人です。この構成から台帳を書き換えると、何が発注で何が実績かが分からなくなります。

統合レポートへの書き込みは、検算を通ったものだけです。 通らなかったものは、担当者が確認の一覧で直してから転記します。広告主へ送るのは人です。

人が確認する

1. 対応づけの案を確かめる … 新しい媒体社か、書式が変わった媒体社のときだけです。単位の列を最優先で見ます
2. 検算が合わなかったレポートを開く … 信頼度の低いセルに印が付いているので、PDFの該当箇所と見比べます
3. 発注との食い違いを確かめる … 読み取りの誤りでなければ、媒体社に問い合わせます
4. 統合レポートを見直す … 全媒体がそろった時点で、前月と比べて極端に違う数値が無いかを見ます
5. 判定を直したら記録する … どのセルを、どの値に直したかを残します

1番目は、最初の数か月に集中します。 40社の媒体社の対応表がそろえば、それ以降は書式が変わったときだけになります。

目標は、ならして1件5分です。 検算も突き合わせも通ったレポートは、統合レポートに転記された結果を流し見るだけで、開くのは検算が合わなかったものと食い違いのあったものです。

例外に対処する

1行目は、媒体の業界では珍しくありません。 交通広告のレポートで、路線名のセルが複数の駅の行にまたがって結合されている、という書式です。読み取り結果の説明では、Form Parser の表の抽出は行や列にまたがるセルの無い通常の表だけを認識し、rowSpan と colSpan は常に1になるとされています。結合のある書式の媒体社は、対応表に印を付け、読み取った行の路線名が空になっていないかを必ず確かめます。

記録を残す

• 受け取ったPDFと、差出人・受信日時
• Document AI が返したJSONの全文
• AIへ渡した入力と、返ってきた対応づけの案
• 対応表の変更の履歴 … いつ、誰が、どの列の対応を確かめたか
• 検算と突き合わせの結果
• 担当者がセルの値を直した記録 … どのセルを、どの値からどの値へ
• 媒体社への問い合わせと、その回答

対応表の変更の履歴を残すのは、転記の誤りが見つかったときに遡るためです。 ある媒体社の数値が数か月ずれていたと分かったとき、いつの対応表の変更から始まったかが分かれば、直す範囲が決まります。