概要

本記事では、ECサイトの注文データと実店舗での販売実績を統合し、顧客ごとのLTVを自動で算出・可視化するワークフローの構築方法を解説します。

転送元の生データをDWH（データウェアハウス）に転送し加工処理を行い、ECサイトを運営している現場担当者が利用しているサービス（顧客管理ツールやBIツールなど）でLTVを把握できるようにします。

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

• 集計・名寄せ作業の完全自動化
  決まったスケジュール（日次・時間次など）でワークフローが自動実行され、最新のデータがBIツールや顧客管理ツール上に生成されます。これにより、複数システムからのCSVダウンロードや、Excelで関数を利用した複雑な集計が不要になり、属人化を防ぐことができます。
• 現場の運用を変えずに「真の顧客像」を可視化
  現場のスタッフは、Shopifyやスマレジ、kintone、スプレッドシートなどの既存システムをこれまで通り利用するだけです。TROCCOを通して名寄せや集計を行うため、現場に新たな入力負荷をかけることなく、ECと店舗を横断した「真の優良顧客」を特定できるようになります。
• データの鮮度向上によるタイムリーな施策実行
  DWHに集計された最新の購買分析データが、BIツールや顧客管理ツールなどに自動連携されます。集計待ちによるロスタイムがなくなり、「昨日店舗で購入した顧客に対し、今日ECサイトから最適なレコメンドを送る」といった、データの鮮度を活かしたスピーディーな意思決定と施策展開が可能になります。

ワークフローの設定手順

これから説明するワークフローでは以下のサービスのデータを一度BigQueryで集計したのちに、スプレッドシートに連携するフローを作成します。

• データソース

  • ECサイトの注文情報

    • Shopify

    • 楽天

  • 実店舗の購入情報と顧客情報

    • MySQL

• データ集約ツール

  • Google BigQuery

• データ可視化ツール

  • Google Spreadsheets

各ソースのデータはサンプルデータを抜粋したものです。実際にShopifyや楽天から取得されるデータにはより詳細な注文情報が含まれています。
また、各フィールドは階層構造になっている可能性があり、別途JSON展開が必要です。

STEP1：Shopify（ECサイト）の注文データをBigQueryに転送する

1. 転送元Shopify → 転送先Google BigQueryの転送設定を作成する
2. 転送設定STEP1のターゲットに「orders」を選択する
3. 昨日分の注文情報を取得するために、注文日時にカスタム変数を埋め込んで、動的に展開されるようにする
   • 追加するカスタム変数
     • 変数名：$date$
     • データ型：時刻・日付（キューイング時）
     • 値：1日前
     • 日付フォーマット：%Y-%m-%d
     • タイムゾーン：Asia/Tokyo
   • 「注文日時」の from / to にカスタム変数を埋め込む
     • from：$date$T15:00:00
     • to：$date$T14:59:59
4. 転送モードは 追記 or UPSERT にする
   • UPSERT にした場合は、name をマージキーにする
5. STEP2のカラム定義で転送するカラムを選択、JSON展開する
   • current_subtotal_price（税抜）：current_subtotal_price_setをJSON展開してJSONパスに["presentment_money"]["amount"]を入力
   • current_total_price（税込）：current_total_price_setをJSON展開してJSONパスに["presentment_money"]["amount"]を入力
     
6. BigQueryに転送したテーブルは以下。必要なカラムのみ転送する

STEP2：楽天（ECサイト）の注文データをBigQueryに転送する

楽天RMSコネクタはありませんが、転送元HTTP・HTTPSで取得可能です。設定方法はこちらを参考にしてください。

1. 転送元HTTP・HTTPS → 転送先Google BigQueryの転送設定を作成する
2. searchOrderを実行して注文番号を取得する転送設定を作成する
   • 設定記事を参考に、昨日分の注文データが取得できるようにカスタム変数を作成し、リクエストボディに埋め込む
3. getOrderを実行する転送設定を作成する
   • 設定記事を参考に転送設定STEP1の設定をする
   • STEP2のカラム定義で転送するカラムを選択、JSON展開する
     • emailAddress：OrdererModelをJSON展開してJSONパスに["emailAddress"]を入力する
     • phoneNumber1：OrdererModelをJSON展開してJSONパスに["phoneNumber1"]を入力する
     • phoneNumber2：OrdererModelをJSON展開してJSONパスに["phoneNumber2"]を入力する
     • phoneNumber3：OrdererModelをJSON展開してJSONパスに["phoneNumber3"]を入力する
4. BigQueryに転送したテーブルは以下。必要なカラムのみ転送する

STEP3：購入データ（オフライン）をBigQueryに転送する

店舗の購入データがMySQLに保管されているという前提で転送設定を作成しています。

1. 転送元MySQL → 転送先Google BigQueryの転送設定を作成する
2. 転送設定STEP1の転送方法で「クエリを使用して転送」を選択する
   • BigQueryに転送したいカラムだけSELECT句に指定

   • 昨日分の購入データを取得できるようにカスタム変数を埋め込んだWHERE句で絞り込む

     -- 例
     SELECT
       slip_no,
       member_id,
       amount_incl_tax,
       amount_excl_tax,
       tax_rate,
       purchase_at
     FROM
       table_name
     WHERE
       purchase_at >= '$date$ 15:00:00'
     AND
       purchase_at <= '$date$ 14:59:59'

3. 転送モードは 追記 or UPSERT にする
   • UPSERT にした場合は、slip_no をマージキーにする
4. BigQueryに転送したテーブル

STEP4：会員情報をBigQueryに転送する

店舗での購入情報のmember_idからメールアドレスや電話番号の情報を紐づけるために会員情報データもBigQueryに転送しておきます。

1. 転送設定STEP1の転送方法で「クエリを使用して転送」を選択する
   • BigQueryに転送したいカラムだけSELECT句に指定

   • 昨日更新された会員情報を取得するためにカスタム変数を埋め込んだWHERE句で絞り込む

     -- 例
     SELECT
       member_id,
       full_name,
       tel,
       email,
       birthday,
       created_at,
       updated_at
     FROM
       table_name
     WHERE
       updated_at >= '$date$ 15:00:00'
     AND
       updated_at <= '$date$ 14:59:59'

2. 転送モードは UPSERT にする
   • member_id をマージキーにする
3. BigQueryに転送したテーブル

STEP5：データマート（SQL）で名寄せ・集計

各チャネルからの注文データを統合し、電話番号とメールアドレスを用いた名寄せとLTVの算出などを行います。TROCCOのデータマート定義でSQLクエリを実行してデータを作成します。

データマート作成のポイント：

• データ取得設定
  • クエリ実行モード：データ転送モード
  • 書き込みモード：全件洗い替え

SQLクエリのサンプル：

• メールアドレスは全て小文字に変換
• 電話番号はハイフンを削除
• 税抜価格でLTVを出すので価格を計算
• 顧客ごとに、以下を算出する
  • 全期間のLTV
  • 累計購入回数
  • 直近一年間のLTV
  • 最初の購入から直近の購入までの経過月数

WITH 
-- 1. Shopifyデータの整形
shopify_raw AS (
  SELECT 
    LOWER(email) AS email,
    REGEXP_REPLACE(phone, r'[^0-9]', '') AS tel_clean,
    current_subtotal_price AS amount,
    created_at AS order_at,
    'Shopify' AS channel
  FROM `systemn-playground.kimura_test.shopify_order`
),

-- 2. 楽天データの整形（税込から税抜を計算）
rakuten_raw AS (
  SELECT 
    LOWER(emailAddress) AS email,
    CONCAT(phoneNumber1, phoneNumber2, phoneNumber3) AS tel_clean,
    FLOOR(totalprice / (1 + taxRate)) AS amount,
    OrderDatetime AS order_at,
    'Rakuten' AS channel
  FROM `systemn-playground.kimura_test.rakuten_order`
),

-- 3. 店舗データの整形（会員マスターと紐付け）
store_raw AS (
  SELECT 
    LOWER(m.email) AS email,
    REGEXP_REPLACE(m.tel, r'[^0-9]', '') AS tel_clean,
    s.amount_excl_tax AS amount,
    s.purchase_at AS order_at,
    'Store' AS channel
  FROM `systemn-playground.kimura_test.offline_sales` s
  LEFT JOIN `systemn-playground.kimura_test.member_master` m ON s.member_id = m.member_id
),

-- 4. 全チャネルの統合（Union）
all_union AS (
  SELECT * FROM shopify_raw UNION ALL
  SELECT * FROM rakuten_raw UNION ALL
  SELECT * FROM store_raw
),

-- 5. 最終集計：顧客ごとのLTVと属性算出
final_ltv AS (
  SELECT 
    -- 電話番号(tel_clean)を第一のキーにする
    -- もし電話番号もなければメールアドレスをキーにする
    COALESCE(tel_clean, email) AS customer_key, 
    -- 重複を除いて、カンマ区切りで全てのメールアドレスを結合する
    STRING_AGG(DISTINCT email, ', ') AS all_emails,
    SUM(amount) AS total_ltv,                -- 全期間LTV
    COUNT(*) AS total_orders,               -- 累計購入回数
    MIN(order_at) AS first_purchase_at,     -- 初回購入日
    MAX(order_at) AS last_purchase_at,      -- 最終購入日
    -- 直近1年間のLTV（2026年3月現在を基準に計算）
    SUM(CASE WHEN order_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 365 DAY) THEN amount ELSE 0 END) AS ltv_last_12month,
    STRING_AGG(DISTINCT channel, ', ') AS used_channels
  FROM all_union
  GROUP BY 1
  -- メールも電話番号もないレコード（名寄せ不可）を除外
  HAVING customer_key IS NOT NULL
)

SELECT 
  *,
  -- 継続月数の計算
  DATE_DIFF(EXTRACT(DATE FROM last_purchase_at), EXTRACT(DATE FROM first_purchase_at), MONTH) AS tenure_months
FROM final_ltv
ORDER BY total_ltv DESC

STEP6：クレンジングしたデータをスプレッドシートに転送する

データマート定義を利用してクレンジングしたデータを現場担当者が確認しやすいようにスプレッドシートに転送します。

1. 転送元Google BigQuery → 転送先Google Spreadsheetsの転送設定を作成する
2. 転送設定STEP1のSQLは、以下を参考にする
   • クレンジングしたデータを抽出するために全取得の * をSELECT句に記載する

   • 平均客単価、顧客ランク、離脱リスク、オムニチャンネル購入者なのかを見える化する

     SELECT 
       *,
       -- 1. 平均客単価 (LTV / 購入回数)
       SAFE_DIVIDE(total_ltv, total_orders) AS avg_order_value,
     
       -- 2. 顧客ランク (LTV金額によるセグメント)
       CASE 
         WHEN total_ltv >= 50000 THEN 'SS_VIP'
         WHEN total_ltv >= 30000 THEN 'S_Gold'
         WHEN total_ltv >= 10000 THEN 'A_Silver'
         ELSE 'B_Bronze'
       END AS customer_rank,
     
       -- 3. 離脱リスク判定 (最終購入から90日以上経過)
       CASE 
         WHEN DATE_DIFF(CURRENT_DATE(), EXTRACT(DATE FROM last_purchase_at), DAY) > 90 THEN '離脱懸念'
         ELSE 'アクティブ'
       END AS churn_risk,
     
       -- 4. オムニチャネル判定
       CASE 
         WHEN REGEXP_CONTAINS(used_channels, ',') THEN '複数チャネル利用'
         ELSE '単一チャネル利用'
       END AS channel_usage_type
     
     FROM `systemn-playground.kimura_test.customer_ltv_summary`

3. 転送モードは「全件洗い替え（シート作り直し）」を選択する
4. 転送を実行すると以下のスプレッドシートが出力される

STEP7：ワークフローによる自動化の設定

ここまでに作成した５つの転送設定とデータマート定義を、ワークフロー機能を活用して一つの処理としてまとめます。これにより、ワークフローの実行のみで一連のデータ処理を制御することができます。 

ワークフロー作成のポイント：

• 各チャネルからの注文情報・購入情報取得タスクは同時に実行しても問題ないため、並列にフローを結びます。そして、「タスク同時実行上限数」を５などに設定することで、同タイミングで実行するジョブ数を制御します。これにより、ワークフロー全体の処理時間を短縮させることができます。
• ワークフローにスケジュールを設定することで、定期的にワークフローを実行することができます。例えば、毎日朝6:00に実行するように設定しておくことで、出勤後に最新のデータを参照することができます。
• ワークフローに通知を設定することで、API側の問題でエラーが発生した、取得するデータ内に不正な値が入ってきたなどの原因でジョブがエラーになった際に、slackやメールなどお好みの通知先にお知らせすることができます。