本記事では、HubSpotのEメールイベントのデータと実店舗での販売履歴をBigQueryで統合し、顧客ごとの過去90日間のメール開封回数・クリック回数・購買回数・購買金額を集計してHubSpotのコンタクトに自動で書き戻すワークフローの構築方法を紹介します。
HubSpotの外にある販売履歴も取り込むことで、HubSpot単体では見えなかった顧客の行動傾向を把握し、マーケティング施策に活用できるようになります。

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

• 顧客ごとに配信メールへの反応と販売履歴を数値化 
  「メールをよくクリックしているが商品の購入に至らない顧客」「メールに反応していないが購買頻度が高い顧客」といったセグメントをHubSpot上で作成できるようになります。
• 期間を切り口とした顧客行動を数値化
  BigQueryで集計を行うことで、「過去90日間」といった期間を切り口として顧客行動を数値化し、直近の傾向をマーケティングに活用できます。
• 一連の処理をワークフローで管理 
  データの転送、集計、HubSpotへの書き戻しという一連の処理をワークフローにまとめることで、処理の実行状況の一元管理。データベース（MySQL）等で管理しているデータの受け渡しするリードタイムを短くすることでタイムリーな施策や意思決定を支援します。

ワークフローの設定手順

これから説明するワークフローでは、以下のデータをBigQueryで集計したのち、集計結果をHubSpotに連携します。

• データソース
  • 実店舗の販売履歴（MySQL）
  • 会員情報（MySQL）
  • 配信したメールの開封の履歴（HubSpot）
  • 配信したメール内のURLのクリックの履歴（HubSpot）
• データ集約ツール
  • Google BigQuery
• 集計結果の連携
  • HubSpotのコンタクト

HubSpotのコンタクトには、集計したデータの連携先となるカスタムプロパティを作成します。

STEP1：販売履歴のデータをBigQueryに転送

MySQLに保管されている店舗の販売履歴のデータを、BigQueryに転送する設定を作成します。

※販売履歴のデータの中から今回のワークフローに使用するカラムのみ抜粋しています。

転送設定STEP1の設定：

転送元：

• 転送方法：差分転送
  • 増分データを判別するカラムにpurchase_atを設定

転送先：

• テーブル：sales_history
• 転送モード：追記(APPEND)またはUPSERT(MERGE)
  • UPSERTの場合、マージキーにslip_idを設定

STEP2：会員情報のデータをBigQueryに転送

MySQLに保管されている会員情報のデータを、BigQueryに転送する設定を作成します。

※会員情報のデータの中から今回のワークフローに使用するカラムのみ抜粋しています。

転送設定STEP1の設定：

転送元：

• 転送方法：差分転送
  • 増分データを判別するカラムにupdated_atを設定

転送先：

• テーブル：customers
• 転送モード：UPSERT(MERGE)
  • マージキーにmember_idを設定

STEP3：HubSpotのEメールイベントのデータをBigQueryに転送

HubSpotで配信したメールの開封やクリックの情報を、BigQueryにデータ転送する設定を作成します。イベントのタイプとしてOPEN（メールの開封）とCLICK（メール内のURLのクリック）があるため、それぞれ異なるテーブルに転送します。

※Eメールイベントのデータの中から今回のワークフローに使用するカラムのみ抜粋しています。

転送設定STEP1の設定：

転送元：

• カスタム変数
  • 変数名：$start_date$
    • データ型：時刻・日付 (キューイング時)
    • 値・単位・前/後：取得するデータの範囲にあわせて設定
      　例：過去1週間分のデータを転送する場合は、「7日前」を設定
    • 日付フォーマット：%Y-%m-%d
    • タイムゾーン：Asia/Tokyo (+09:00)
  • 変数名：$end_date$
    • データ型：時刻・日付 (キューイング時)
    • 値・単位・前/後：取得するデータの範囲にあわせて設定
      　例：過去1週間分のデータを転送する場合は、「1日前」を設定
    • 日付フォーマット：%Y-%m-%d
    • タイムゾーン：Asia/Tokyo (+09:00)
• 取得対象：Eメールイベント
• イベントタイプ：OPENまたはCLICK
• データ取得期間：
  • 開始日時： $start_date$ 00:00:00
  • 終了日時： $end_date$ 23:59:59

転送先：

• テーブル：
  • email_event_open
  • email_event_click
• 転送モード：追記(APPEND)またはUPSERT(MERGE)
  • UPSERTの場合、マージキーにidを設定

転送設定STEP2の設定：

createdカラムはUNIX時間の日時カラムです。UNIX時間変換を使って、UNIX時間からtimestamp型に変換します。以下の設定後、変更をプレビューをクリックし、カラムがtimestamp型になっていることを確認します。

• 対象カラム：created
• 変換方法：UNIX -> 日時(TIMESTAMP型)
• 変換前のUNIX時間の単位：ミリ秒

STEP4：データマートで顧客行動データを集計

BigQueryに転送された各テーブルから過去90日間の顧客行動として集計します。テーブルには集計日のカラムを用意し、集計したデータを追記します。

データマートSTEP2の設定：

• カスタム変数
  • 変数名：$current_date$
    • データ型：時刻・日付 (キューイング時)
    • 値・単位・前/後：0時間前
    • 日付フォーマット：%Y-%m-%d
    • タイムゾーン：Asia/Tokyo (+09:00)
• クエリ実行モード：データ転送モード
• 書き込みモード：追記
• 出力先テーブル：customer_activity_summary

SQLクエリのサンプル：

WITH
  -- OPENイベントの集計
  email_open AS (
    SELECT
      recipient,
      COUNT(*) AS open_count_90days
    FROM
      `email_event_open`
    WHERE
      filtered_event = false
      AND created >= TIMESTAMP_SUB(TIMESTAMP('$current_date$', 'Asia/Tokyo'), INTERVAL 90 DAY)
      AND created < TIMESTAMP('$current_date$', 'Asia/Tokyo')
    GROUP BY
      recipient
  ),
  -- CLICKイベントの集計
  email_click AS (
    SELECT
      recipient,
      COUNT(*) AS click_count_90days
    FROM
      `email_event_click`
    WHERE
      filtered_event = false
      AND created >= TIMESTAMP_SUB(TIMESTAMP('$current_date$', 'Asia/Tokyo'), INTERVAL 90 DAY)
      AND created < TIMESTAMP('$current_date$', 'Asia/Tokyo')
    GROUP BY
      recipient
  ),
  -- 販売履歴の集計
  purchase AS (
    SELECT
      member_id,
      COUNT(*) AS purchase_count_90days,
      SUM(amount_excl_tax) AS purchase_amount_90days
    FROM
      `sales_history`
    WHERE
      member_id IS NOT NULL
      AND purchase_at >= TIMESTAMP_SUB(TIMESTAMP('$current_date$', 'Asia/Tokyo'), INTERVAL 90 DAY)
      AND purchase_at < TIMESTAMP('$current_date$', 'Asia/Tokyo')
    GROUP BY
      member_id
  )
SELECT
  CAST('$current_date$' AS DATE) AS aggregated_date, --集計日
  c.email,
  c.member_id,
  COALESCE(o.open_count_90days, 0) AS open_count_90days,
  COALESCE(cl.click_count_90days, 0) AS click_count_90days,
  COALESCE(p.purchase_count_90days, 0) AS purchase_count_90days,
  COALESCE(p.purchase_amount_90days, 0) AS purchase_amount_90days
FROM
  `customers` c
  LEFT JOIN email_open o ON c.email = o.recipient
  LEFT JOIN email_click cl ON c.email = cl.recipient
  LEFT JOIN purchase p ON c.member_id = p.member_id
ORDER BY
  member_id

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

• カスタム変数でバックフィルに対応
  カスタム変数を使って現在の日付を取得し、90日間の期間の指定や集計日の出力に使用します。カスタム変数を使うことで、もしワークフロー実行時にエラーが発生した場合に、エラーになったジョブを再実行することで、当時の日付を反映してクエリを実行できます。

STEP5：集計結果をHubSpotのコンタクトに転送

集計した結果をHubSpotに反映するため、BigQueryからHubSpotのコンタクトに転送します。

転送設定STEP1の設定：

転送元：

• カスタム変数
  • 変数名：$current_date$
  • データ型：時刻・日付 (キューイング時)
  • 値・単位・前/後：0時間前
  • 日付フォーマット：%Y-%m-%d
  • タイムゾーン：Asia/Tokyo (+09:00)

SQLクエリサンプル：

SELECT 
    email,
    open_count_90days,
    click_count_90days,
    purchase_count_90days,
    purchase_amount_90days
FROM 
    `customer_activity_summary`
WHERE
    aggregated_date = CAST('$current_date$' AS DATE)

転送先：

• オブジェクトタイプ：contact
• 転送モード：UPSERT
• UPSERTキー：email

STEP6：ワークフローによる自動実行の設定

これまで作成した転送設定とデータマート定義を、ワークフロー機能で一連の処理としてまとめます。

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

• 依存関係の設定
  MySQLおよびHubSpotからBigQueryへのデータ転送ジョブは、全てのジョブが完了した後にデータマートジョブを実行するように順序を設定します。「タスク同時実行上限数」を4などに設定することで、これらのデータ転送ジョブは並列に実行できるため、ワークフロー全体の処理時間を短縮することができます。
• スケジュール設定
  スケジュールを設定することで、定期的にワークフローを実行することができます。例えば、毎週月曜日の早朝にワークフローを実行することで、業務開始時には最新のデータをHubSpotで確認できます。
• 通知設定
  ワークフローの処理でエラーが発生した場合は、Slackやメールなどへの通知を設定することでエラーを早期に把握して対処することができます。