本記事のポイント
「参加者リストとアンケート結果が別々のシートにあって、誰が来て誰が答えたかを毎回 VLOOKUP で手作業照合している」。Zoom でウェビナーを開き、アンケートを Google フォームで取る現場で、humbulls がよく聞く悩みです。
原因はツールの相性ではなく、2 つのデータを結ぶ共通キー (メールアドレス) をそろえていないことにあります。
本記事では、Zoom の参加ログと Google フォームの回答をスプレッドシート上で突合し、フォロー対象を 4 象限で洗い出すところまでを STEP 順に再現します。関数はすべてコピーして動く形で載せ、丸ごと組ませる実行キットも巻末に付けました。
この記事は、ウェビナーアンケートをどのツールで作るか で Google フォームを選んだ場合の実装受け皿です。
完成形から見せます。登録者を母集団とし、メールアドレスをキーに「参加したか」「回答したか」の 2 軸で全員を 4 象限に振り分けた、下図のような 1 枚シートです。
象限ごとの人数と、次に取るべきアクションの優先度が一目で決まります。
| 象限 | 参加 | 回答 | 人数 (実測例) | フォローの優先度 |
|---|---|---|---|---|
| A | 参加 | 回答 | 61 | 最優先 (商談化の一次候補) |
| B | 参加 | 未回答 | 67 | お礼 + アンケート再依頼 |
| C | 不参加 | 回答 | 13 | 録画 + 資料を共有 |
| D | 不参加 | 未回答 | 69 | 録画リンクのみ / 次回案内 |
象限ごとの文面とタイミングは ウェビナー フォローアップの流れ に切り出しています。本記事は「誰がどの象限か」を機械的に確定させるところまでを担います。
前提と規模は次のとおりです。
| 項目 | 内容 |
|---|---|
| 所要時間 | 30〜40 分 (フォーム連携が済んでいる場合) |
| 前提 | Zoom (登録ありのウェビナー) / Google フォーム (メール収集 ON) / Google スプレッドシート |
| 追加コスト | $0 (すべて既存ツールの標準機能) |
| 突合の規模 (実測例) | 登録 210 名 / 参加 128 名 / 回答 74 件 を数分で 4 象限化 |
以下、STEP 1 から順に進めます。
「参加者リストはどこから出すのが正解ですか。CSV の列が毎回バラバラで迷う」。突合の入り口で必ず出る質問です。
Zoom のレポートは、Reports > Usage Reports > Meeting and Webinar History から対象ウェビナーを開き、Participants の件数をクリックして Export で CSV を落とします。
ここで出すのは 登録者レポートと参加者レポートの 2 つ です。
参加者レポートを出すときは「Show unique users」にチェックを入れてください。再入室した人が複数行に割れるのを、1 人 1 行に集約できます。
つまずきポイントは、メール列が空になっているケースです。
登録なしで開くと参加者レポートに氏名しか出ず、突合キーになるメールが手に入りません。原因は登録フォームでメールを必須にしていないことなので、次回から登録を必須設定にします。今回の実測例では、登録 210 名・参加 128 名がこの 2 レポートから取れています。
「メールで照合しているのに、同じ人が別人扱いになって回答が拾えない」。STEP 2 で潰すのはこの症状です。
突合が合わない原因のほぼすべては、メールアドレスの表記ゆれです。
Tanaka@example.com と tanaka@example.com、末尾に半角スペースが混じった tanaka@example.com は、スプレッドシートにとって別の文字列です。フォームの自由入力では、コピペ時の全角スペース混入も日常的に起きます。
そこで、3 つのシート (登録者 / 参加者 / フォーム回答) のすべてに、同じルールで正規化した「キー列」を 1 列足します。メール列が B 列なら、キー列にこの式を入れます。
=IF(B2="", "", LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(B2, " ", ""), " ", ""))))
内側の SUBSTITUTE で全角スペースを、外側の SUBSTITUTE で半角スペースを消し、TRIM で残った前後の空白を落とし、LOWER で小文字にそろえています。これで表記ゆれが吸収され、同じ人は必ず同じキーになります。
つまずきポイントは、SUBSTITUTE を 1 回しか書かないことです。半角スペースだけ消すと全角スペースが残り、突合が静かに外れます。全角・半角の 2 段でかけるのが安全です。
→ メールキーの名寄せをさらに詰めたい場合は スプレッドシートの名寄せ関数 を参照してください。
「VLOOKUP だと列を数え間違えて #N/A が出る」。ここは XLOOKUP に置き換えるだけで安定します。
突合シートを 1 枚作り、A 列に登録者のメール、B 列に STEP 2 のキー列を置きます。この登録者キーを軸に、参加情報と回答情報を引き込みます。
C 列で、参加者レポートから参加時間 (分) を引きます。参加していなければ空欄が返ります。
=XLOOKUP(B2, zoom_attendees!$F$2:$F, zoom_attendees!$E$2:$E, "")
D 列で参加フラグを、E 列で回答の有無を立てます。回答は COUNTIF でキーの有無だけを見るのが確実です。
D2: =IF(C2="", "不参加", "参加")
E2: =IF(COUNTIF(form_raw!$G$2:$G, B2) > 0, "回答", "未回答")
満足度など回答内容そのものを持ってきたい列は、XLOOKUP でフォーム回答から引きます。
=XLOOKUP(B2, form_raw!$G$2:$G, form_raw!$C$2:$C, "")
つまずきポイントは、XLOOKUP の第 4 引数を省くことです。見つからないときに "" を返させないと #N/A が象限の式まで伝播し、集計全体が壊れます。必ず既定値を空文字にしておきます。
「象限分けを手で色付けしていたら、200 行で心が折れた」。最後は 1 本の式で機械化します。
G 列に、参加フラグ (D) と回答フラグ (E) の組み合わせから象限ラベルを付けます。
=IFS(
AND(D2="参加", E2="回答"), "A 参加×回答",
AND(D2="参加", E2="未回答"), "B 参加×未回答",
AND(D2="不参加", E2="回答"), "C 不参加×回答",
TRUE, "D 不参加×未回答"
)
象限ごとの人数は、別のセルで COUNTIF を使えばそのまま出ます。
=COUNTIF($G$2:$G, "A 参加×回答")
つまずきポイントは、IFS がどの条件にも当てはまらないと #N/A を返すことです。最後の行を TRUE で受け、残りをすべて「D 不参加×未回答」に流し込みます。実測例では A 61 / B 67 / C 13 / D 69 に振り分けられ、合計は登録者 210 名と一致しました。
ここまで組んだら、次の 3 点を確認します。
3 点が揃えば、あとは象限列でフィルタするだけでフォロー対象がそのまま切り出せます。
Zoom の参加ログと Google フォームの回答は、別々のツールでも「メールアドレスをキーにそろえる」一手で 1 枚に統合できます。正規化キーを作り、XLOOKUP で引き、IFS で 4 象限に振る。この 3 つが決まれば、200 行でも数分で終わります。
4 象限に分け終えたら、次は象限ごとのフォロー文面とタイミングです。参加×回答の温度差に合わせた 48 時間の動線は ウェビナー フォローアップの流れ にまとめています。突合そのものを手作業から解放したいなら、HubSpot と Zoom の連携 で参加ログを CRM に自動で流し込む方法もあります。
humbulls では、こうしたウェビナー運用のデータ整備からフォロー設計までを Growth Partner サービス で伴走しています。
本文の STEP 1〜4 を、そのまま AI に組ませるためのキットです。プロンプトの記入例を自社の列名に置き換えて渡してください。
種別: 実装キット 使うもの: Claude (ブラウザ版で可。CSV の中身を貼り付けるか、ヘッダー行だけでも動きます) / スプレッドシートに関数を貼る作業のみ手動 事前に用意するもの: Zoom 登録者 CSV / Zoom 参加者 CSV / Google フォーム回答 CSV (それぞれ 1 行目のヘッダーだけでも可)
プロンプト:
Zoom のウェビナー参加ログと Google フォームの回答を、
Google スプレッドシートで突合する数式一式を作ってください。
【私のデータ】
- Zoom 登録者シート zoom_registrants: A=名前, B=メール, C=登録日時（記入例。実際の列名に置換）
- Zoom 参加者シート zoom_attendees: A=名前, B=メール, C=参加時刻, D=退出時刻, E=参加時間_分
- フォーム回答シート form_raw: A=タイムスタンプ, B=メール, C=満足度, D=相談希望
- 母集団は登録者（不参加を含めたいため）
【必ず守るルール（本文の対策を転写）】
1. 3 シートすべてに正規化キー列を作る。式は
=IF(B2="","",LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(B2," ","")," ",""))))
（全角スペース→半角スペースの 2 段で除去し、TRIM と LOWER でそろえる）
2. 突合は XLOOKUP を使い、第 4 引数に "" を必ず指定する（#N/A を出さない）
3. 参加フラグ・回答フラグ・4 象限ラベルを列で持たせる。象限は
A 参加×回答 / B 参加×未回答 / C 不参加×回答 / D 不参加×未回答 の 4 つ
4. 象限は IFS で作り、最後の条件を TRUE で受けて取りこぼしをなくす
【出力】
- 各シートのキー列の式
- 突合シートの「参加時間 / 参加フラグ / 回答フラグ / 回答内容 / 象限」各列の式
- 4 象限それぞれの人数を出す COUNTIF の式
- 列名を変えたい場合にどこを直せばよいかの注記
出力の確認ポイント:
SUBSTITUTE を 2 回重ねているか (全角スペースが残ると突合が静かに外れます)"" になっているかTRUE で受けてあり、4 象限の COUNTIF 合計が登録者数と一致するかうまくいかないとき:
CLEAN() を足すよう追記で指示してください