Qlik Automate (自動化)によるQlik SenseとHubSpotの連携③

前回はQlik Automateを活用したHubSpotのコンタクト作成の自動化について説明しました。
今回は『Update Contact』コンタクトの更新の自動化について説明しようと思います。

Liner Notes Corporate Site

Qlik Automate (自動化)によるQlik SenseとHubSpotの連携① | Liner Notes Corporate Site Qlik Automateを使い、Qlik SenseとHubSpotのデータの連携を自動化します。

Liner Notes Corporate Site

Qlik Automate (自動化)によるQlik SenseとHubSpotの連携② | Liner Notes Corporate Site Qlik Automateを使い、Qlik SenseとHubSpotのデータの連携、Qlik SenseのデータからHubSpotのコンタクト作成を自動化します。

概要

基本的な流れは、Qlikアプリのデータロードエディタにて、Qlikのデータをcsv形式でAmazon S3保存し、Qlik Automateにて、csvファイルを読み込みHubSpotのコンタクトを更新します。
ただし、毎回全件を更新するのではなく、データロードエディタでcsvを作成する際に更新されているレコードのみをcsvに保存し、更新をかけていくほうが望ましいです。

Qlik側でレコードが更新されているかどうかについて、最終更新日時といった項目があればそちらを参照すればよいですが、今回はUPDATE KEYを作成し、UPDATE KEYにてレコードの更新を管理する方法を紹介しようと思います。

UPDATE KEYの作成

左記の6項目のうち、Eメール、郵便番号、住所をUPDATE_KEYに設定するとします。郵便番号や住所のデータが更新されていたら、UPDATE_KEYが変わり、更新されているレコードとしてcsvに保存される流れです。
また、UPDATE_KEYは左記のような形でExcelファイルで管理するものとします。

hubspot_contact_property:
Load
    項目名,
    Internal name,
    UPDATE_KEY
FROM [Excelファイル];

update_key_string_contact:
Load Concat('[' & 項目名 & ']', '&' & chr(39) & '-' & chr(39) & '&',UPDATE_KEY) as update_key_string_contact
Resident hubspot_contact_property
Where UPDATE_KEY > 0;

Let vUpdate_key_string_contact = FieldValue('update_key_string_contact',1);

update_key_string_contactは『[Eメール]&’-’&[郵便番号] &’-’&[住所]』という形になり、これを変数に格納しました。
この変数を使い、実データよりUPDATE KEYを作成します。

顧客データ:
Load *,
    $(update_key_string_contact) as UPDATE_KEY;
Load
    姓,
    名,
    生年月日,
    Eメール,
    郵便番号,
    住所
Resident 顧客データ_Temp
Where Len(Trim(Eメール)) > 0;

これにて、Eメール、郵便番号、住所からなるUPDATE_KEYが作成されました。
例えば、とある顧客についてEメールが『test@mail』、郵便番号が『1000000』、住所が『東京都』だった場合、UPDATE_KEYは『test@mail-1000000-東京都』となります。
顧客の住所のデータが『東京都』から『北海道』に変わった場合、UPDATE_KEYは『test@mail-1000000-北海道』となり、UPDATE_KEYの相違よりデータが更新されたと認識します。

後の流れは、前回のコンタクト作成の自動化と同様です。
HubSpotのコンタクトからEメールとUPDATE_KEYの一覧を取り込み、QlikアプリのロードスクリプトにてUPDATE_KEYの突合を行い、UPDATE_KEYが一致しないデータをQlikからHubSpotへエクスポートする、という流れです。 UPDATE_KEYは、hubspot側にカスタムプロパティを作成し、コンタクト作成の段階から持たせておきます。

HubSpotデータの取り込み

Qlik Automateにて、HubSpotのコンタクトのレコードをcsv形式でAmazon S3へ保存します。前回記事の『HubSpotデータの取り込み』と同様です。今回は、id、email、カスタムプロパティのupdate_keyの3つのプロパティをそれぞれコンタクトID、Eメール、UPDATE_KEYというカラム名で取り込むこととし、『hubspot_contact.csv』というファイル名で保存することとします。

QlikとHubSpotデータの突合

Qlik Sense分析アプリのロードスクリプトにて、データと突合を行い、HubSpotに既にデータがありかつUPDATE_KEYが変わっている個人を絞り込んだうえで、絞り込んだ個人のデータをAmazon S3にcsv形式で保存します。

// S3に保存したcsvよりメールアドレス、UPDATE_KEYの一覧をロード
Contact_Temp:
Load
    コンタクトID,
    Eメール,
    UPDATE_KEY
From [S3に保存したhubspot_contact.csv];

// Qlik側の顧客データからHubSpotにアップデートとしてエクスポートしたいデータをロード
// 今回は簡単に7項目をHubSpotにエクスポートすることとする
Contact_Update_Temp:
Load
    姓,
    名,
    Eメール,
    生年月日,
    郵便番号,
    住所,
    UPDATE_KEY
Resident 顧客データ
Where Len(Trim(Eメール)) > 0;

// Inner JoinによりHubSpot側に既にEメールのあるデータに絞る
Inner Join(Contact_Update_Temp)
Load
    Eメール,
    コンタクトID
Resident Contact_Temp;

// EメールとUPDATE_KEYが同じものにフラグを振る
Left Join(Contact_Update_Temp)
Load
    Eメール,
    UPDATE_KEY,
    1 as UPDATE_FLAG
Resident Contact_Temp;

// コンタクトIDがあり、フラグがないもの（UPDATE_KEYが異なるもの）をLoad
NoConcatenate
Contact_Update:
Load * Resident Contact_Update_Temp
Where UPDATE_FLAG <> 1 and Len(Trim(コンタクトID)) > 0;

Drop Table Contact_Update_Temp,Contact_Temp;

// Amazon S3へcsv形式で保存
Store Contact_Update into [S3の任意のフォルダ]

これにて、UPDATE_KEYに更新のあった顧客データのみをS3に保存することができました。