概要
コールセンターに限らず、問い合わせフォームを用意しているWebサイトなどでは、とにかく早く架電するという方向性を除き、架電時間を顧客の通話できる最適な時間帯に設定することは重要です。
しかし、顧客の職業や性別・年齢、質問への回答といったパーソナルデータが乏しい場合、適した通話時間帯の予測は困難です。
本記事では、「少なくともサイトにコンバージョンした日時と通話できた場合の日時はあるだろう」という前提に基づき、過去のデータから最適な通話時間帯を可視化する方法をSQLとLooker Studioを用いて紹介します。
1. データセットの概要
以下のようなサンプルデータセットを扱います。
ここでは顧客のサイトCV日時、初回通話日時、顧客成果判定のみが提供されており、顧客の詳細な属性情報がないという設定です。
| サイトCV日時 | 初回通話日時 | 顧客成果判定 |
|---|---|---|
| 2024-09-01 14:25:47 | 2024-09-01 15:30:00 | 受注 |
| 2024-09-05 09:10:12 | 失注 | |
| 2024-09-12 11:45:00 | 2024-09-12 18:00:00 | 失注 |
| 2024-09-15 16:30:20 | 2024-09-15 19:00:00 | 受注 |
| 2024-09-28 20:15:35 | 失注 |
また、サンプルデータの生成方法として、良ければ下記も参考にしてください。
2. SQLでデータの加工
まず、BigQueryなどを用いて以下のSQLクエリで分析用の新しいテーブルを作成します。このテーブルは、サイトCV時間、初回通話時間、サイトCV曜日、初回通話曜日、顧客成果判定を含む形式に加工します。
-- サイトCV時間、初回通話時間、曜日を抽出し、新しいテーブルに保存
CREATE OR REPLACE TABLE `your_project.your_dataset.processed_data` AS
SELECT
EXTRACT(HOUR FROM DATETIME(`サイトCV日時`)) AS site_cv_hour, -- サイトCV時間（0〜23）
EXTRACT(HOUR FROM DATETIME(`初回通話日時`)) AS first_call_hour, -- 初回通話時間（0〜23）
FORMAT_TIMESTAMP('%A', `サイトCV日時`) AS site_cv_day, -- サイトCV曜日（月〜日）
FORMAT_TIMESTAMP('%A', `初回通話日時`) AS first_call_day, -- 初回通話曜日（月〜日）
CASE
WHEN `顧客成果判定` = '受注' THEN 'Success' -- 受注をSuccessに変換
WHEN `顧客成果判定` = '失注' THEN 'Failure' -- 失注をFailureに変換
ELSE 'Unknown' -- その他はUnknown
END AS customer_outcome -- 顧客成果判定
FROM
`your_project.your_dataset.raw_data`
WHERE
`初回通話日時` IS NOT NULL; -- 通話が行われていないデータを除外
SQLの説明
- 時間の抽出
-
サイトCV日時と初回通話日時からそれぞれの時間（0〜23時）を抽出し、site_cv_hourおよびfirst_call_hourとして保存しています。これにより、どの時間帯にCVや通話が発生しているか分析可能になります。
-
- 曜日の抽出
-
サイトCV日時と初回通話日時から曜日を抽出し、site_cv_dayおよびfirst_call_dayとして保存します。これで、曜日ごとの通話傾向を分析できるようになります。
-
- 顧客成果判定の変換
-
顧客成果判定の値（受注/失注）をSuccessまたはFailureに変換し、結果をcustomer_outcomeとして保存しています。
-
- データフィルタリング
-
初回通話日時が空欄のレコードを除外し、通話が行われたデータのみを対象にしています。
-
SQL実行結果
曜日は和名ではないですが、完了しました。
初回通話日時がないレコードを除外し、499,423件となっています。
3. Looker Studioでの可視化
次に、BigQueryで作成したテーブルをLooker Studioに接続し、ヒートマップを作成します。
手順
- データソースの追加: Looker Studioにログインし、BigQueryから先ほど作成したテーブルをデータソースとして追加します。
-
グラフの追加:メニューからグラフの追加をクリックし、その際に「ヒートマップ付ピボットテーブル」を選択します。
-
-
通話時間帯ごとだけで確認する場合: まずは行のディメンションにfirst_call_hourを設定し、指標を件数（RecordCount）にします。そうすると時間帯ごとの通話件数を視覚化します。
-
曜日も含めて確認する場合: 先ほどのグラフに、列のディメンションで
first_call_dayも追加します。英名なので並び替えをどのようにするかは考える必要ありますが、どの曜日のどの時間帯が通話数が多いかまで可視化することができました。
-
条件を絞ってみるには: 現在、すべてのCV時間帯のデータが表示されているため、このままでは使えません。コントロールを追加します。メニューから
コントロールを追加→固定サイズのリストでコントロールを追加し、コントロールフィールドにはsite_cv_dayを設定します。
このようにすると、そのCV時間帯のデータだけが表示されるようになります。また、上図のように成約できているユーザーのみに絞るなど、条件を絞って結果を見たい場合にも、各種コントロールフィルタを使い、特定のデータに絞り込むことができます。
以上により、特定の時間帯に通話数が集中しているかどうか、視覚的に確認することができます。
サンプルデータなのであまり傾向が出ていない結果になっていますが、実際のデータで行うと、時間帯ごとの偏りが見られることが多く、"それはなぜか"を仮説立てできるような結果が多かったです。
まとめ
顧客のパーソナルデータが少ない場合でも、SQLを使ったデータ加工とBIツールを使った可視化によって、通話の最適な時間帯の傾向を可視化することができます。
本データだけでは通話率は出ていませんし、既存のオペレーションにも影響されているため、過信はできませんが、本データをもとに仮説を打ち出して、より詳細なヒアリングや仮説検証のフェーズに進むことができます。