電話・LINE・Webを“一枚”に：問い合わせ統合KPIの作り方 - 株式会社メタアルケミスト
株式会社メタアルケミスト
業務DX
kent_takamatsu

電話・LINE・Webを“一枚”に：問い合わせ統合KPIの作り方

要約（60秒）
電話（PBXログ）・LINE公式・Web（フォーム/予約/GA4）を同じ物差しで可視化し、日次で試行→接続→予約→来院のファネルを出す最小レーンを解説します。
下のトグル（開閉）コードブロックに、BigQueryスキーマ／集計SQL／LINE Webhook雛形／DNI管理CSVをすべて収録しています。

1. まずKPIを“言葉で”固定する

  • 試行（Attempt）：電話発信/着信、LINEメッセージ、問い合わせ送信、電話リンククリック
  • 接続（Connected）：通話がつながる（answered>0秒）、LINEでやり取り開始
  • 予約（Booked）：予約完了（reservation_id 付与）
  • 来院（Arrived）：チェックイン/会計完了

ゴールは**日付×チャネル（phone/line/web）**で「件数と率」を出すこと。
例）接続率＝Connected/Attempt、予約化率＝Booked/Connected、来院率＝Arrived/Booked

2. データ設計（L1→L2→L3の三層で“まず動く”）

  • L1（Raw）：そのままのログを格納
    • L1_PHONE_LOG … call_id, started_at, duration, status, phone_e164, agent など
    • L1_LINE_EVENTS … event_ts, event_type, line_user_id, postback, link_token
    • L1_WEB_EVENTS … GA4/フォーム/予約：event_ts, event_name, user_pseudo_id, email/phone, reservation_id, UTM, gclid
  • L2（Identity）：統合ID contact_id を決める（優先度：予約ID > 電話 > メール > LINE > Web）
    • 個人情報は E.164整形→SHA256（メールはlower→SHA256）
  • L3（Unified Events）：ファネル段階（attempt/connected/booked/arrived）まで正規化

下のDDLトグルをそのまま利用できます（project_id を自環境に置換）。

3. 連携キーのつくり方（最小で堅く）

  1. 予約ID：最強の主キー。Web/LINE/電話で「確定」したら必ず付与
  2. 電話：E.164（数字＋先頭+）に正規化→SHA256で保存
  3. メール：lower化→SHA256
  4. LINE：line_user_id を接点キーに
  5. セッション：session_id（30分窓）や gclid/utm_* は補助リンク

まずは決定的キー（予約/電話/メール）で6〜8割を結合、残りはセッション近傍で“ゆるく”繋ぐのが現実解。

4. 変換・統合のロジック（サンプル付き）

  • ID割当て（優先度スコアで contact_id を決定）
  • イベント正規化
    • phone: answered → connected、その他 → attempt
    • web : lead_submit/phone_call_click → attempt、booking_created → booked
    • line: message/follow → attempt、予約確定のpostback → booked、来院連携があれば arrived

すべて identity_map_build.sql に収録。日次実行で L2/L3 を更新。

5. 日次KPIテーブルの作成

  • DAILY_FUNNEL を日付×チャネルで集計（件数・率）
  • 指標：contacts_attempted / connected / booked / arrived と contact_rate / booking_rate / show_rate

SQLは funnel_build.sql に収録。

6. 計測（GTM/GA4）イベントの最小実装

推奨イベントとパラメータは gtm_event_specs.md に整理済み。
最低限：

  • lead_submit（form_id, email, phone, reservation_id）
  • phone_call_click（DNIの番号／session_id）
  • booking_created（reservation_id, fee, clinic_id）
  • visit_completed（reservation_id）

フロントでPIIを直送しない運用を強く推奨。PIIはバックエンドで正規化→ハッシュ化→L1投入。

7. LINEと電話の取り込み（雛形あり）

  • LINE：doPost(e) で webhook を受け、時刻・type・userId・postback/data をスプレッドシートへ。
    • サンプル line_webhook_sample.gs（Apps Script）
    • 本番は署名検証と再送対策を必ず追加
  • 電話（DNI）：dni_number_map.csv を管理し、広告別に表示番号を出し分け。
    • PBXログ（Twilio/MiiTel/BIZTEL等）に表示番号が残る → UTMと紐付く

8. ダッシュボード設計（Looker Studio/Looker）

**1ページで“経営の手元感”**を出す構成：

  1. KPIカード（7/30日移動）：Attempt / Connected / Booked / Arrived と各率
  2. チャネル別トレンド（line/phone/web）
  3. キャンペーン×チャネルの貢献表（Booked/Arrived）
  4. 業務SLA：Missed Calls、初回応答までの中央値
  5. 予約から来院までのリードタイム分布

9. セキュリティと運用

  • できる限りハッシュ化保存（phone/email）
  • アクセス権限は“閲覧/編集”をロールで分離、ログ監査を月次で
  • 保持期間は用途に応じて設定（例：未成約は短期、会計確定は長期）
  • 同意管理（consent_marketing）をユーザープロパティで保持

10. 導入チェックリスト（貼って使える）

  • L1スキーマ作成（kpi_schema.sql の project_id 置換）
  • PBX/LINE/GA4のデータ投入経路を決める
  • identity_map_build.sql を日次実行（Scheduler/Cloud Workflows 等）
  • funnel_build.sql を日次実行し DAILY_FUNNEL を更新
  • ダッシュボード（KPIカード/チャネル/キャンペーン/ミスコール）を作成
  • DNI番号の在庫と割当（dni_number_map.csv）を整備
  • 権限・保持・監査の運用を明文化

よくある質問

Q: GA4のuser_pseudo_idだけで繋いで大丈夫？
A: 補助キーです。予約ID/電話/メールが主、user_pseudo_id は**近傍時間（30分）**の補助に。

Q: PIIはどこまで入れて良い？
A: 保存はハッシュ化が基本。閲覧は役割限定。必要な場合のみ復元可能なシステム側に閉じる。

コード一式

# 問い合わせ統合KPIパック（電話・LINE・Webを“一枚”に）

このパックは、電話／LINE公式アカウント／Web（フォーム・予約）を統合し、
**日次でKPIを可視化**するための最小構成テンプレートです。

## 同梱物
- `kpi_schema.sql` … BigQueryのスキーマDDL（L1/L2/L3）
- `identity_map_build.sql` … 連携ID（contact_id）生成とイベント統合のサンプル
- `funnel_build.sql` … 日次ファネルKPI（試行→接続→予約→来院）集計SQL
- `gtm_event_specs.md` … GA4/GTMの推奨イベント・パラメータ命名規約
- `line_webhook_sample.gs` … LINE Webhook（Apps Script）サンプル
- `dni_number_map.csv` … ダイナミックナンバー挿入（DNI）番号管理の雛形

## 想定ランタイム
- BigQuery（標準SQL）
- Apps Script（LINE webhookの超軽量実装例）

## 導入手順（概要）
1. `kpi_schema.sql` を編集して `project_id` を自プロジェクトに置換→実行
2. 電話ログ・LINE・Webイベント（GA4）をL1に投入（バッチ/ストリーム不問）
3. `identity_map_build.sql` を日次実行（contact_idの整備とL3イベント生成）
4. `funnel_build.sql` を日次実行（KPIテーブルを更新）
5. Looker Studio/Looker でダッシュボードを作成（本文の指標を参照）

## 連携キーの考え方（最小）
- 強キー： `reservation_id`（予約が基軸）
- 中キー： `phone_hash`（E.164正規化→SHA256） ／ `email_hash`
- 補助キー： `line_user_id` ／ `user_pseudo_id`（GA4）／ `session_id`（30分窓）
- contact_idの優先度： reservation_id > phone > email > line > web

## 注意
- 個人情報は可能な限り **ハッシュ化** して保存してください（本サンプルに実装）。
- 実運用では **署名検証**（LINE signature）・**レート制御** 等を必ず追加。

-- BigQuery Standard SQL
-- 1) まず `project_id` を自環境の GCP プロジェクトIDに置換してください

CREATE SCHEMA IF NOT EXISTS `project_id.contact_kpi` OPTIONS (location="asia-northeast1");

-- L1: RAW PHONE LOG
CREATE TABLE IF NOT EXISTS `project_id.contact_kpi.L1_PHONE_LOG` (
  call_id STRING,
  started_at TIMESTAMP,
  duration_sec INT64,
  direction STRING,             -- inbound/outbound
  phone_e164 STRING,            -- +81形式など（可能ならE.164）
  call_status STRING,           -- ringing/answered/missed/voicemail
  agent_id STRING,
  reservation_id STRING,
  gclid STRING,
  session_id STRING,
  metadata JSON
) PARTITION BY DATE(started_at);

-- L1: RAW LINE EVENTS
CREATE TABLE IF NOT EXISTS `project_id.contact_kpi.L1_LINE_EVENTS` (
  event_ts TIMESTAMP,
  event_type STRING,            -- follow/message/postback/block 等
  line_user_id STRING,
  link_token STRING,
  message_text STRING,
  reservation_id STRING,
  utm_source STRING, utm_medium STRING, utm_campaign STRING,
  gclid STRING,
  session_id STRING,
  metadata JSON
) PARTITION BY DATE(event_ts);

-- L1: RAW WEB EVENTS（GA4/フォーム/予約）
CREATE TABLE IF NOT EXISTS `project_id.contact_kpi.L1_WEB_EVENTS` (
  event_ts TIMESTAMP,
  event_name STRING,            -- lead_submit / booking_created / visit_completed など
  user_pseudo_id STRING,
  web_client_id STRING,
  page_location STRING,
  form_id STRING,
  email STRING,
  phone_e164 STRING,
  reservation_id STRING,
  gclid STRING,
  session_id STRING,
  utm_source STRING, utm_medium STRING, utm_campaign STRING,
  metadata JSON
) PARTITION BY DATE(event_ts);

-- L2: IDENTITY MAP（ハッシュ化した識別子も保持）
CREATE TABLE IF NOT EXISTS `project_id.contact_kpi.L2_IDENTITY_MAP` (
  contact_id STRING,            -- 統一ID（優先度で決定）
  phone_hash STRING,
  email_hash STRING,
  line_user_id STRING,
  web_client_id STRING,
  user_pseudo_id STRING,
  first_seen_ts TIMESTAMP,
  last_seen_ts TIMESTAMP,
  join_rule STRING,             -- reservation|phone|email|line|web など
  join_confidence FLOAT64
);

-- L3: UNIFIED EVENTS（KPI集計前の正規化イベント帯）
CREATE TABLE IF NOT EXISTS `project_id.contact_kpi.L3_EVENTS` (
  event_ts TIMESTAMP,
  event_name STRING,
  contact_id STRING,
  channel STRING,               -- phone|line|web
  stage STRING,                 -- attempt|connected|qualified|booked|arrived
  reservation_id STRING,
  session_id STRING,
  campaign STRING,
  source STRING,
  medium STRING
) PARTITION BY DATE(event_ts);

-- BigQuery Standard SQL
-- 連携ID（contact_id）を優先度ルールで決定し、L3_EVENTSを生成します。
-- 実運用では重複統合・遷移検出などを追加してください。

-- 正規化 & ハッシュ関数（電話は数字+先頭+を残し、他はlower）
CREATE OR REPLACE TEMP FUNCTION NORM_PHONE(p STRING) AS (
  REGEXP_REPLACE(IFNULL(p,''), r'[^0-9\+]', '')
);
CREATE OR REPLACE TEMP FUNCTION H_PHONE(p STRING) AS (
  TO_HEX(SHA256(NORM_PHONE(p)))
);
CREATE OR REPLACE TEMP FUNCTION H_LOWER(s STRING) AS (
  TO_HEX(SHA256(LOWER(IFNULL(s,''))))
);

-- 1) L1から鍵候補を抽出
WITH phone_keys AS (
  SELECT
    started_at AS event_ts,
    IFNULL(reservation_id,'') AS reservation_id,
    H_PHONE(phone_e164) AS phone_hash,
    NULL AS email_hash,
    NULL AS line_user_id,
    NULL AS web_client_id,
    NULL AS user_pseudo_id,
    'phone' AS channel,
    call_status,
    duration_sec,
    session_id, gclid, NULL AS utm_source, NULL AS utm_medium, NULL AS utm_campaign
  FROM `project_id.contact_kpi.L1_PHONE_LOG`
),
line_keys AS (
  SELECT
    event_ts,
    IFNULL(reservation_id,'') AS reservation_id,
    NULL AS phone_hash,
    NULL AS email_hash,
    line_user_id,
    NULL AS web_client_id,
    NULL AS user_pseudo_id,
    'line' AS channel,
    event_type AS call_status,
    NULL AS duration_sec,
    session_id, gclid, utm_source, utm_medium, utm_campaign
  FROM `project_id.contact_kpi.L1_LINE_EVENTS`
),
web_keys AS (
  SELECT
    event_ts,
    IFNULL(reservation_id,'') AS reservation_id,
    H_PHONE(phone_e164) AS phone_hash,
    H_LOWER(email) AS email_hash,
    NULL AS line_user_id,
    web_client_id,
    user_pseudo_id,
    'web' AS channel,
    event_name AS call_status,
    NULL AS duration_sec,
    session_id, gclid, utm_source, utm_medium, utm_campaign
  FROM `project_id.contact_kpi.L1_WEB_EVENTS`
),
unioned AS (
  SELECT * FROM phone_keys
  UNION ALL
  SELECT * FROM line_keys
  UNION ALL
  SELECT * FROM web_keys
),

-- 2) contact_id の優先生成（reservation > phone > email > line > web）
scored AS (
  SELECT
    *,
    CASE
      WHEN reservation_id != '' THEN 100
      WHEN phone_hash IS NOT NULL AND phone_hash != '' THEN 90
      WHEN email_hash IS NOT NULL AND email_hash != '' THEN 80
      WHEN line_user_id IS NOT NULL AND line_user_id != '' THEN 70
      WHEN user_pseudo_id IS NOT NULL AND user_pseudo_id != '' THEN 60
      ELSE 0
    END AS priority_score
  FROM unioned
),

id_assigned AS (
  SELECT
    *,
    CASE
      WHEN reservation_id != '' THEN CONCAT('resv:', reservation_id)
      WHEN phone_hash IS NOT NULL AND phone_hash != '' THEN CONCAT('ph:', phone_hash)
      WHEN email_hash IS NOT NULL AND email_hash != '' THEN CONCAT('em:', email_hash)
      WHEN line_user_id IS NOT NULL AND line_user_id != '' THEN CONCAT('line:', line_user_id)
      WHEN user_pseudo_id IS NOT NULL AND user_pseudo_id != '' THEN CONCAT('web:', user_pseudo_id)
      ELSE CONCAT('anon:', FORMAT_TIMESTAMP('%Y%m%d%H%M%S', event_ts))
    END AS contact_id,
    CASE
      WHEN reservation_id != '' THEN 'reservation'
      WHEN phone_hash IS NOT NULL AND phone_hash != '' THEN 'phone'
      WHEN email_hash IS NOT NULL AND email_hash != '' THEN 'email'
      WHEN line_user_id IS NOT NULL AND line_user_id != '' THEN 'line'
      WHEN user_pseudo_id IS NOT NULL AND user_pseudo_id != '' THEN 'web'
      ELSE 'anonymous'
    END AS join_rule
  FROM scored
),

-- 3) IDENTITY MAP の upsert（最小）
agg_id AS (
  SELECT
    contact_id,
    ANY_VALUE(phone_hash) AS phone_hash,
    ANY_VALUE(email_hash) AS email_hash,
    ANY_VALUE(line_user_id) AS line_user_id,
    ANY_VALUE(web_client_id) AS web_client_id,
    ANY_VALUE(user_pseudo_id) AS user_pseudo_id,
    MIN(event_ts) AS first_seen_ts,
    MAX(event_ts) AS last_seen_ts,
    ANY_VALUE(join_rule) AS join_rule,
    MAX(priority_score)/100.0 AS join_confidence
  FROM id_assigned
  GROUP BY contact_id
)

-- MERGE into L2_IDENTITY_MAP
MERGE `project_id.contact_kpi.L2_IDENTITY_MAP` T
USING agg_id S
ON T.contact_id = S.contact_id
WHEN MATCHED THEN
  UPDATE SET
    phone_hash = IFNULL(T.phone_hash, S.phone_hash),
    email_hash = IFNULL(T.email_hash, S.email_hash),
    line_user_id = IFNULL(T.line_user_id, S.line_user_id),
    web_client_id = IFNULL(T.web_client_id, S.web_client_id),
    user_pseudo_id = IFNULL(T.user_pseudo_id, S.user_pseudo_id),
    first_seen_ts = LEAST(T.first_seen_ts, S.first_seen_ts),
    last_seen_ts = GREATEST(T.last_seen_ts, S.last_seen_ts),
    join_rule = T.join_rule,
    join_confidence = GREATEST(T.join_confidence, S.join_confidence)
WHEN NOT MATCHED THEN
  INSERT ROW;

-- 4) L3_EVENTS 生成（最小ルール）
--   phone: ringing→attempt, answered→connected
--   line: message/follow→attempt, 予約完了タグ→booked
--   web : lead_submit→attempt, booking_created→booked, visit_completed→arrived(任意)
DECLARE run_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP();

CREATE OR REPLACE TEMP TABLE tmp_events AS
SELECT
  event_ts,
  contact_id,
  channel,
  reservation_id,
  session_id,
  utm_campaign AS campaign,
  utm_source AS source,
  utm_medium AS medium,
  CASE
    WHEN channel = 'phone' AND call_status = 'answered' THEN 'connected'
    WHEN channel = 'phone' THEN 'attempt'
    WHEN channel = 'line'  AND REGEXP_CONTAINS(IFNULL(call_status,''), r'予約|book|confirm') THEN 'booked'
    WHEN channel = 'line'  THEN 'attempt'
    WHEN channel = 'web'   AND LOWER(call_status) = 'booking_created' THEN 'booked'
    WHEN channel = 'web'   AND LOWER(call_status) = 'visit_completed' THEN 'arrived'
    WHEN channel = 'web'   THEN 'attempt'
    ELSE 'attempt'
  END AS stage,
  CASE
    WHEN channel='phone' AND call_status='answered' AND SAFE_CAST(duration_sec AS INT64) > 0 THEN 'call_connected'
    ELSE LOWER(IFNULL(call_status,'event'))
  END AS event_name
FROM id_assigned;

-- 既存重複を避けつつ追記
INSERT INTO `project_id.contact_kpi.L3_EVENTS` (
  event_ts, event_name, contact_id, channel, stage, reservation_id, session_id, campaign, source, medium
)
SELECT
  event_ts, event_name, contact_id, channel, stage, reservation_id, session_id, campaign, source, medium
FROM tmp_events;

-- BigQuery Standard SQL
-- 日次×チャネルのファネル指標を作成します。

CREATE OR REPLACE TABLE `project_id.contact_kpi.DAILY_FUNNEL` AS
WITH base AS (
  SELECT
    DATE(event_ts) AS dt,
    channel,
    contact_id,
    MAX(IF(stage='attempt', 1, 0)) AS f_attempt,
    MAX(IF(stage='connected', 1, 0)) AS f_connected,
    MAX(IF(stage='booked', 1, 0)) AS f_booked,
    MAX(IF(stage='arrived', 1, 0)) AS f_arrived
  FROM `project_id.contact_kpi.L3_EVENTS`
  GROUP BY dt, channel, contact_id
)
SELECT
  dt,
  channel,
  COUNTIF(f_attempt=1)   AS contacts_attempted,
  COUNTIF(f_connected=1) AS contacts_connected,
  COUNTIF(f_booked=1)    AS contacts_booked,
  COUNTIF(f_arrived=1)   AS contacts_arrived,
  SAFE_DIVIDE(COUNTIF(f_connected=1), COUNTIF(f_attempt=1)) AS contact_rate,
  SAFE_DIVIDE(COUNTIF(f_booked=1), COUNTIF(f_connected=1))  AS booking_rate,
  SAFE_DIVIDE(COUNTIF(f_arrived=1), COUNTIF(f_booked=1))    AS show_rate
FROM base
GROUP BY dt, channel
ORDER BY dt DESC, channel;

# GA4 / GTM 推奨イベント命名

## イベント
- `lead_submit` … お問い合わせ送信（form_id, phone, email, reservation_id）
- `phone_call_click` … クリックtoコール（telリンクやDNI番号）
- `call_connected` … 通話接続（PBX側で計測→L1_PHONE_LOGに投入）
- `line_click` … LINE遷移（link_token, session_id）
- `booking_created` … 予約完了（reservation_id, clinic_id, service_id, fee）
- `visit_completed` … 来院完了/会計完了（reservation_id）

## 共通パラメータ
- `reservation_id` / `session_id` / `campaign` / `source` / `medium` / `gclid`
- `contact_id`（フロントでは送らず、バックエンドで付与する運用が望ましい）

## ユーザープロパティ
- `consent_marketing`（true/false）
- `membership_tier`（任意）
- `clinic_id`（マルチ拠点の場合）

## 命名ルール
- スネークケース、英小文字
- 数値は数値型で送信し、BigQuery側も数値列で受ける

/**
 * LINE Webhook サンプル（Apps Script）
 * - 署名検証やリトライ制御は省略（本番では必須）
 * - スプレッドシートの「line_events」シートに追記
 */
function doPost(e) {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("line_events") || ss.insertSheet("line_events");
  const body = JSON.parse(e.postData.contents);
  const now = new Date();
  body.events.forEach(ev => {
    const row = [
      new Date(ev.timestamp),
      ev.type || "",
      ev.source && ev.source.userId || "",
      ev.replyToken || "",
      (ev.message && ev.message.text) || (ev.postback && ev.postback.data) || "",
      "", // reservation_id（必要に応じてpostback.dataから抽出）
      "", "", "", // utm_source / utm_medium / utm_campaign（リンク先から取得する場合は別管理）
      "", // gclid
      Utilities.getUuid(), // session_id（暫定）
      JSON.stringify(ev)
    ];
    sh.appendRow(row);
  });
  return ContentService.createTextOutput("OK");
}

dni_number,display_number,source,medium,campaign,notes
+815012300001,+81-50-1230-0001,google,cpc,clinic-brand,LP A用（午前）
+815012300002,+81-50-1230-0002,google,cpc,clinic-brand,LP B用（午後）
+815012300101,+81-50-1230-0101,organic,seo,/access,アクセスページ用
+815012300201,+81-50-1230-0201,line,social,line-home,LINE公式プロフィール用

タグ

#BigQuery #DNI #GA4 #LINE #Looker Studio #クリニックDX #予約率 #問い合わせKPI #電話