メインコンテンツへスキップ

バラバラな顧客データを1つに。TROCCOのワークフローで実現する「解約予兆」の可視化

更新

概要

SaaSビジネスにおいて、顧客の利用状況や継続利用の可能性を測る指標としてヘルススコアを正しく把握することは、解約防止やアップセルを実現するための最優先事項です。しかし、必要なデータが各所に散らばり分析のためのデータ準備に時間がかかってしまったり信頼性のあるヘルススコアを作れないという課題を多くのチームが抱えています。

この記事では、TROCCOのワークフローで複数のシステムからデータを自動集約・スコアリングし、常に最新の状態で維持される「データマート」の構築方法を紹介します。一度設定すれば、カスタマーサクセスチームは集計作業から解放され、常に最新のスコアを基にしたアクションが可能になります。

ワークフロー導入のメリット

  • データ収集と更新の完全自動化
    MySQL、Salesforce、Zendeskといった複数のシステムにあるデータをデータウェアハウス（DWH）に自動で集約・加工し、安定運用を実現できます。
  • メンテナンス性の高い「スコアリング基盤」
    計算ロジックをTROCCOのデータマート定義で管理。指標の追加や重み付けの変更も柔軟に対応可能です。
  • 「データ待ち」の解消
    ワークフローを日次実行することで、毎日業務開始時には最新の顧客情報が整っている環境を提供します。

ワークフローの設定手順

「利用頻度」「NPS」「サポート回数」「満足度」という4つの指標をもとにSQLでスコアリングするロジックを作成するところまでを説明します。

STEP 1：データ転送の設定

まずは、TROCCOの転送設定でスコアの材料となるデータを各システムからデータウェアハウス（※）に集約します。
※この記事では、データウェアハウス製品としてGoogle BigQueryを使用しています。

① プロダクトDB（MySQL）：ユーザー情報と利用状況

プロダクトDB(MySQL)のユーザー情報・利用状況のデータをBigQueryに転送するための転送設定を作成します。

カラム名説明
user_idユーザーID
emailメールアドレス
last_login_at最終ログイン日時
session_count_30d直近30日間のセッション数
updated_at更新日時（差分転送キー）

※例として一部項目のみを記載しています。

設定例

  • 転送元
    • 転送方法：差分転送（増分データを判別するカラムに「updated_at」を設定）
  • 転送先
    • 転送先テーブル名：raw_mysql_user_activity
    • 転送モード：UPSERT(MERGE)

② Salesforce：顧客情報とNPS（満足度）

Salesforceの顧客情報やアンケートなどで収集したNPS(満足度)のデータをBigQueryに転送するための転送設定を作成します。

（a）Accountオブジェクト（取引先）

Salesforce項目名転送後のカラム名説明
Idid取引先ID
Namename取引先名
Industryindustry業種
CommonEmal__ccommon_email__c登録メールアドレス
ContractStatus__ccontract_status__c契約ステータス
LastModifiedDatelast_modified_date最終更新日時

設定例

  • 転送元
    • Salesforceコネクタで「Account」オブジェクトを選択
    • オブジェクト取得方法
      • オブジェクトの項目を全転送を選択
  • 転送先
    • 転送先テーブル名：raw_salesforce_account
    • 転送モード：REPLACE（洗い替え）
  • データ加工
    • 命名規則の変換: TROCCOの転送設定STEP2にある「一括変換」機能を活用します。 Salesforceの標準的な命名（例: ContractStatus__c）を、クエリで使いやすいスネークケース（例: contract_status__c）へ一括変換することで、クエリ記述時のミスを防止できます。

（b）NPSカスタムオブジェクト（例：NPS_Response__c）

Salesforce項目名転送後のカラム名説明
IdidNPSレコードID
AccountId__caccount_id__c紐づく取引先ID（Account参照）
NpsScore__cnps_score__cNPSスコア
SurveyDate__csurvey_date__c回答日時
LastModifiedDatelast_modified_date最終更新日時

設定例

  • 転送元
    • カスタムオブジェクト「NPS_Response__c」を選択
      • 接続情報のユーザーに参照権限があるオブジェクトが選択候補として表示されます。
  • 転送先
    • 転送先テーブル名：raw_salesforce_nps

③ Zendesk：問い合わせチケット

Zendeskの問い合わせチケット(ticket)のデータをBigQueryに転送するための転送設定を作成します。

（a）Zendesk チケットの一覧を取得

カラム名説明
idチケットID
requester_idチケット依頼者のユーザーID
created_atチケット作成日時
solved_at解決日時
satisfaction_rating満足度評価（good/bad）
updated_at更新日時

設定例

  • 転送元
    • 更新日時にカスタム変数を設定
      • 追加するカスタム変数
        • 変数名：$date$
        • データ型：時刻・日付（キューイング時）
        • 値：1日前
        • 日付フォーマット：%Y-%m-%d
        • タイムゾーン：Asia/Tokyo
      • 「チケット更新日時」の 「開始」 にカスタム変数を埋め込む
        • 開始：$date$
  • 転送先
    • 転送先テーブル名：raw_zendesk_tickets
    • 転送モード：UPSERT(MERGE)

（b）Zendesk ユーザーの一覧を取得

カラム名

説明

idユーザーID
emailZendeskに登録したユーザーのメールアドレス

設定例

  • 転送先
    • 転送先テーブル名：raw_zendesk_users
    • 転送モード：REPLACE（洗い替え）

補足：本記事における転送モードの使い分け

転送モード：REPLACE（洗い替え）取引先マスタなど、テーブル全体を毎日最新の状態に上書きしたい場合に適しています。
転送モード：UPSERT(MERGE)日々発生する利用ログなどの増分データを、既存のレコードと統合（更新または新規追加）し、履歴の欠落を防ぎます。

STEP 2：データマート設定

データ転送でBigQueryに転送された各データをメールアドレスをキーに統合してから、集計します。

Salesforceの取引先を主軸（左側におくLEFT JOIN）に置くことで、「契約中だが、まだ一度もログインしていない顧客（MySQLにデータがない）」や「問い合わせが一度もない顧客（Zendeskにデータがない）」についても、スコア 0 点や 100 点としてリストに漏らさず表示させることができます。

クエリ実行モード：データ転送モード
書き込みモード： 全件洗い替え
出力テーブル名：mart_customer_health_score

SQL例

WITH 
-- 1. 最新のNPS回答のみを抽出
latest_nps AS (
  SELECT 
    account_id__c, 
    nps_score__c
  FROM 
    `project.dataset.raw_salesforce_nps`
  QUALIFY ROW_NUMBER() OVER(PARTITION BY account_id__c ORDER BY survey_date__c DESC) = 1
),

-- 2. Zendesk：emailを介してチケット状況を集計
support_summary AS (
  SELECT 
    u.email,
    COUNT(t.id) AS ticket_cnt_30d,
    AVG(CASE WHEN t.satisfaction_rating like '%good%' THEN 1 ELSE 0 END) AS satisfaction_rate
  FROM 
    `project.dataset.raw_zendesk_tickets` AS t
  JOIN 
    `project.dataset.raw_zendesk_users` AS u ON t.requester_id = u.id
-- ここでは、直近30日間のチケット件数と満足度評価を集計します。    
  WHERE 
    t.created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  GROUP BY 
    u.email
)

-- 3. 顧客マスタを中心に全てのデータをemailで名寄せ・スコアリング
SELECT
  a.name AS company_name,
  a.common_email__c AS email,
  COALESCE(m.session_count_30d, 0) AS session_count_30d,
  COALESCE(n.nps_score__c, 0) AS nps_score,
  COALESCE(s.ticket_cnt_30d, 0) AS support_tickets_30d,

  -- 【ヘルススコア算出ロジック】
  ROUND(
    (LEAST(COALESCE(m.session_count_30d, 0), 30) / 30.0 * 40) + -- 利用頻度(40点)
    (COALESCE(n.nps_score__c, 0) * 3) +                         -- NPS(30点)
    (GREATEST(0, (1 - COALESCE(s.ticket_cnt_30d, 0) / 10.0)) * 20) + -- サポート回数(20点)
    (COALESCE(s.satisfaction_rate, 0) * 10)                     -- 満足度(10点)
  , 1) AS health_score

FROM 
  `project.dataset.raw_salesforce_account` AS a
LEFT JOIN `project.dataset.raw_mysql_user_activity` AS m 
  ON LOWER(a.common_email__c) = LOWER(m.email) -- 大文字小文字の差異を無視して結合
LEFT JOIN latest_nps AS n 
  ON a.id = n.account_id__c
LEFT JOIN support_summary AS s 
  ON LOWER(a.common_email__c) = LOWER(s.email)
WHERE 
  a.contract_status__c = 'Active';

※SQLは一例です。

STEP 3：ワークフローの構築と運用設定

最後に、これら一連の処理をTROCCOのワークフロー機能で統合し、自動実行の仕組みを整えます。

  1. 依存関係の定義: ４つのデータ転送が完了した後に、データマート作成が実行されるよう順序を設定
  2. スケジュール設定: 毎日早朝（例: AM 5:00）に実行
  3. エラー通知: 転送エラー時にはSlackやメールへの通知設定をすることで、早期に復旧
スクリーンショット 2026-03-02 18.10.58.png

まとめ

本ワークフローを構築することで、バラバラだった顧客データは「常に最新かつ計算済みの状態」でBigQueryに蓄積されるようになります。

このデータマートを使ってダッシュボードでの可視化やCRMへの書き戻しなど、その後の活用のための「信頼できる土台」をTROCCOで運用することができます。

関連記事

コメント

0件のコメント

記事コメントは受け付けていません。