troccoを活用して顧客情報登録を自動化してみた！

By Chinen trocco 2024/03/05

１．ゴール

２．設定方法

１．Snowflakeの顧客情報を含むデフォルトビューをタスクで定期的に別ビューへ抽出する

２．troccoでSnowflakeとHubspotの繋ぎ込みをする

３．留意点

４．troccoのサポートがとても良い件

５．おわりに

１．ゴール

最近PODBの最新情報を確実にユーザーの皆さんお届けするためにメーリングリストを開設したのですが、Snowflakeからユーザー情報を抽出しHubspot（メーリングリスト運用に使用しているCRMツール）に顧客情報登録をする作業を毎回手動で行う手間をtroccoを活用することによって省けないかと考えました。

※従来のステップでは、Snowflakeから最新の顧客情報をcsv.で抽出し、HubspotのUIでcsv.をロードし取り込んでいました。

２．設定方法

１．Snowflakeの顧客情報を含むデフォルトビューをタスクで定期的に別ビューへ抽出する

まず使用するのは、SnowflakeのSnowflakeDBに用意されている、DATA_SHARING_USAGEというスキーマのLISTING_EVENTS_DAILYというビューです。

このビューでは、いつ・どこのリージョンの・何というアカウントロケーターを使用する・何というメールアドレスを保有するユーザーが・何のリスティングを取得したかなど、日々のMarketplaceでの取引(?)の詳細を確認することができます。

※SnowflakeDBの詳細についてはこちらのブログでも紹介しています。

PODBでは、このビューのメールアドレスをユニークに抽出し、メーリングリスト送信前にHubspotのコンタクト情報を更新しています。

ここから一気にtrocco設定へ！…と行けば良いのですが、SnowflakeとHubspotの情報を結び付けるためのプライマリキー的なものを設定する際にカラム名を揃える必要があり、SnowflakeもHubspotもデフォルトで用意されていたカラムを使用する関係上、カラム名を直接変更することはできず、まずはSnowflake上でHubspotに合わせたカラム名を別名で付けた別のビューを用意する必要があります。

また、別のビューを作成した後は、定期的に最新のユーザー情報をデフォルトビューから流す必要があります。今回はSnowflakeのタスク設定で毎朝9時に最新のユーザー情報をこの別のビュー（「PODB_CONSUMER_UPDATE」と呼ぶことにします。）に流すことにしました。

設定したSQLは以下の通りです。（Hubspot上でPODBの顧客とその他の顧客を区別する必要があるため、新たに「event_contact_type」というカラムが追加されています。）

CREATE OR REPLACE TASK PODB_CONSUMER.CONSUMER_INFORMATION.PODB_CONSUMER_UPDATE
　WAREHOUSE = TROCCO
　SCHEDULE = 'USING CRON 00 09 * * * JAPAN'
AS
　CREATE OR REPLACE SECURE VIEW PODB_CONSUMER.CONSUMER_INFORMATION.PODB_CONSUMER_UPDATE
AS
SELECT DISTINCT
　CONSUMER_EMAIL AS EMAIL,
　'PODB' AS EVENT_CONTACT_TYPE
FROM
　SNOWFLAKE.DATA_SHARING_USAGE.LISTING_EVENTS_DAILY
WHERE
　CONSUMER_EMAIL IS NOT NULL;

これでSnowflakeでの下準備は完了です！