広告・メール配信のURLに付けるUTMパラメータを命名規則と照らし、表記ゆれ・抜け・媒体との食い違いを入稿前に指摘して直し方を示す

処理の起点を決める

運用担当がメニューから「UTM を点検」を選んだときに動かします。 onOpen の関数で SpreadsheetApp.getUi().createMenu() を使い、メニューの項目に点検の関数を結び付けます。選んだ行だけを点検する項目と、シートの未点検の行をすべて点検する項目の2つを置きます。

時間主導型のトリガーで自動に回さないのは、貼り付けの途中で動くのを避けるためです。 入稿表の行を何回かに分けて貼る運用担当もいて、途中で点検されると、まだ貼っていない行の分だけ「案件のURLが足りない」ような結果が出ます。 運用担当が「貼り終えた」と言える操作をきっかけにします。

1回の実行は6分までです。 公式の割り当てでは、スクリプトの1回の実行時間は6分です。1件の入稿表の20本ほどなら1回で終わりますが、シートのすべての未点検の行を点検する項目では、1回あたりの行数に上限を置き、残りは「未点検」のまま残します。

入力データを集める

質を決めるのは、媒体の対応表です。 入稿表の媒体の列は運用担当が自由に書くので、「Instagram広告」「インスタ」「IG」が混ざります。対応表にこれらを並べ、それぞれに使うべき utm_source と utm_medium を書いておけば、食い違いの多くは規則で判定できます。 AI に回るのは、対応表に無い書き方の行だけになります。

命名規則を共有のドキュメントからシートに移す作業が、最初の準備です。 ドキュメントの文章で書かれた規則を、パラメータごとの行と、値の一覧と、正規表現に直します。この作業で、ドキュメントの規則が曖昧だったところが見えてきます。

Google アナリティクスのヘルプでは、URLにパラメータを付けるときは、どのような場合も utm_source・utm_medium・utm_campaign を使う必要があるとされています。命名規則のシートでも、この3つを必須にします。ヘルプでは、関連するパラメータ（utm_id、utm_source_platform など）もあわせて設定することが強く推奨されています。どこまでを必須にするかは、社内の計測の設計で決めます。

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

URLは、スクリプトで部分に分けます。 ? より前をページのアドレス、? と # のあいだをパラメータ、# より後を残りとして分け、パラメータを & と = で名前と値に分けます。値はパーセントエンコードを戻してから判定し、組み立てるときにもう一度エンコードします。

1行ごとの処理は、次の順で行います。

1. URLを、ページのアドレス・パラメータ・# 以降に分ける
2. 同じ名前のパラメータが2回出ていないか、? が2つないかを見る
3. 必須のパラメータの有無を見る
4. 値ごとに、文字の種類（小文字・数字・- と _ だけか、全角・空白が無いか）を見る
5. utm_source・utm_medium が一覧にあるか、utm_campaign が決まった形かを見る
6. 媒体の対応表で、媒体の列と utm_source・utm_medium の組が合うかを見る
7. キャンペーン名の広告主の部分と、案件のコードの広告主が合うかを見る
8. utm_content とクリエイティブの ID が合うかを見る
9. 1〜8 で規則に合わなかった値と、対応表に無い媒体の行を、AI に渡す一覧にする

1回の呼び出しに、入稿表1件分の行をまとめて渡します。 1本ずつ呼ぶと、同じ入稿表の中で同じ誤りに別々の直し方が出ることがあります。まとめて渡すと、同じ誤りには同じ案が返りやすくなります。

OpenAI の API は UrlFetchApp で呼びます。 API キーはスクリプトのプロパティに置きます。公式の割り当てでは、URL Fetch の呼び出しは一般のアカウントで1日2万回、Google Workspace のアカウントで1日10万回で、月100件の入稿表には十分です。

AIへ渡す前に整形する

1. 規則に合う行は渡さない … 1〜8 をすべて通った行は、AI を呼ばずに「適合」と書きます
2. 値の前後の空白を記録する … 空白は消さずに「空白あり」として判定に残します。黙って消すと、入稿表の元の値の誤りが残ったままになります
3. 規則の一覧を絞って渡す … その行の配信の種類で使える値の一覧だけを渡します
4. 広告主の略称を添える … 案件のコードから引いた略称を渡します
5. ページのアドレスは渡す範囲を決める … 公開前の特設ページのアドレスは、パスの部分を記号に置き換えて渡せます（第13章）

1番目で渡さない行が、呼び出しの量を決めます。 規則に慣れた運用担当の入稿表は、ほとんどの行が規則で通ります。AI が見るのは、誤りのある行と、対応表に無い書き方の行だけです。

AIに処理させる

させるのは、規則に合わなかった値について直した値の案と理由を書くことと、規則では決めきれない食い違いを指摘することです。

キャンペーン名の組み直しでは、年月を勝手に決めさせません。 年月は配信の開始日から決まるので、スクリプトが開始日から出した年月を渡し、AI にはそれを使わせます。 施策の部分は、元の値の語を小文字にしてハイフンでつなぐだけにし、語を足したり言い換えたりさせません。

1行目が、この構成でいちばん守るべきことです。 AI は、一覧に無くても「それらしい」値を案にしがちです。instagram_story のように、規則の一覧に無い値が案として出ると、それを採った時点で表記ゆれが1つ増えます。 そこで、スキーマの enum で utm_source と utm_medium の案を一覧の値に縛り、スクリプトでも8番目の再判定をします。

指示内容を固定する

あなたは広告代理店の運用部で、入稿前のURLのUTMパラメータを校正する立場です。
渡された規則と入稿の行の情報だけを使って、直した値の案を作ってください。

【やること】
- issues に挙がっている値ごとに、直した値の案（suggested）と理由（reason）を書く
- utm_source・utm_medium の案は、渡された一覧の値から選ぶ
- utm_campaign の案は「{yyyymm}_{広告主の略称}_{施策}」の形にする
  - yyyymm は渡された値をそのまま使う
  - 施策は元の値の語を小文字にし、語のあいだをハイフンでつなぐ。語を足さない
- 入稿の行の媒体・配信の種類と、パラメータの値が食い違っていれば mismatches に書く
- 案が1つに決まらないときは、suggested を空にし、needs_human を true にする

【厳守事項】
- 一覧に無い値を案にしないでください。近い値が無ければ needs_human にしてください。
- 規則を変えるべきだという提案はしないでください。
- ページのアドレスの部分には触れないでください。
- 媒体の列が空、または読み取れないときに、utm_source から媒体を決めつけないでください。
- reason には、どの規則に合わなかったか、なぜその案にしたかを1文で書いてください。

【命名規則】{rules}
【この行で使える値の一覧】{allowed_values}
【媒体の対応表（該当しそうな行）】{media_map}
【広告主の略称】{client_alias}
【点検する行】{rows_json}

「媒体から決めつけない」を書くのは、逆向きの推測を止めるためです。 媒体の列が空の行で utm_source=line とあれば、AI は「LINE の広告」と読んで utm_medium の案まで作ります。入稿表のほうが誤っている可能性もあるので、ここは人に回します。

「語を足さない」も要ります。 sale を autumn-sale にするような、気の利いた直しをさせないためです。施策の名前を決めるのは運用担当で、校正の役割はその表記をそろえることだけです。

出力形式を固定する

構造化出力で、次の形の JSON を受け取ります。

{
  "rows": [
    { "row_id": "",
      "fixes": [
        { "param": "utm_source | utm_medium | utm_campaign | utm_content | utm_term | utm_id",
          "original": "", "suggested": "", "reason": "" }
      ],
      "mismatches": [
        { "field": "", "detail": "" }
      ],
      "needs_human": false }
  ]
}

スキーマでは param を enum で縛り、suggested に pattern で ^[a-z0-9_-]*$ を付けます。空の文字列も許す形にして、案が無いときに空を返せるようにします。 公式のページでは、すべての項目を required にし、オブジェクトに additionalProperties: false を必ず付けるとされているので、それに合わせます。

row_id は、スクリプトが付けた行の番号をそのまま返させます。 渡した行と返った行を突き合わせ、抜けた行は「要再点検」にします。 公式のページでは、応答が refusal の型で返ることや、出力の上限で途中で止まった場合は status が incomplete になることがあるとされているので、どちらも JSON として読む前に確かめます。

たとえば、メールの配信の行で次の値が来たとき、返ってくる中身はこうなります。

【入稿の行】媒体：自社メルマガ／配信の種類：メール／案件：BRX-2610-03
  utm_source=Newsletter&utm_medium=cpc&utm_campaign=AW_Sale
【スクリプトの判定】
  utm_source：大文字あり／utm_campaign：形が合わない
  媒体の対応表：自社メルマガ → newsletter・email（utm_medium が合わない）
【AI の案】
  utm_source   Newsletter → newsletter（大文字を小文字に。一覧の値）
  utm_medium   cpc → email（配信の種類がメールで、対応表の組は email）
  utm_campaign AW_Sale → 202610_brx_aw-sale（年月と略称は渡された値）

3つ目の案の 202610 と brx は、AI が考えた値ではありません。 スクリプトが配信の開始日と案件のコードから出して渡した値を、そのまま使わせています。

システムへ連携する

入稿表の元のファイルには書き込みません。 運用担当が点検のシートの結果を見て、自分の入稿表を直します。広告の管理画面にも触れません。

人が確認する

1. 運用担当が「要修正」の行を見る … 案を読み、採るなら直したURLを入稿表に写します。採らないなら、その理由を一言書きます
2. 運用担当が「要確認」の行を点検の担当に回す … needs_human の行と、媒体の列が空の行です
3. 点検の担当が、回ってきた行と、案を採らなかった行を見る … 規則の側の不足なら、規則のシートか対応表を直します

3番目で規則の側を直すのが、この構成を育てる作業です。 対応表に無い媒体の書き方が来るたびに足していくと、AI に回る行が減り、規則で決まる範囲が広がります。

点検の担当が全行を見る形には戻しません。 第10章の6分には、規則で「適合」になった行を見る時間を入れていません。

運用担当が案を採らない理由は、たいてい3つに分かれます。 「広告主の指定でこの値を使う」「新しい媒体で、対応表がまだ無い」「施策の名前を広告主と決め直した」です。1つ目は規則の例外として広告主の一覧に書き、2つ目は対応表に足し、3つ目は入稿表の側を直します。 どれに当たるかを一言書いてもらうだけで、点検の担当の判断が速くなります。

例外に対処する

最も多いのは4行目です。 運用が新しい媒体を始めるたびに出るので、対応表に足す候補の一覧を点検の担当が毎週見ます。

記録を残す

• 点検した日時、点検した人、入稿表の案件のコード、行の数
• 行ごとのスクリプトの判定と、AI に渡した内容、返ってきた JSON
• 運用担当が案を採ったか、採らなかった理由
• 対応表と規則のシートの変更の履歴

3つ目の「採らなかった理由」が、規則の穴を教えてくれます。 同じ理由が続くなら、規則か対応表のほうを直します。

規則のシートを直したときは、直した日をシートに書きます。 同じURLでも、規則を直す前と後で判定が変わるためです。配信が始まってからレポートで表記ゆれが見つかったとき、そのURLを点検した日の規則がどうだったかを確かめられるようにしておきます。