Zendeskの1週間分のチケットをFivetranとSnowflakeを使って抽出してみた

「カスタマーストーリー」を支えるツール特集

#ビジネス・アナリティクス

#Snowflake

#Fivetran

#Zendesk

スズ

2020.08.26

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、スズです。

私が所属しているデータアナリティクス事業本部では、Zendeskを使ってサポートを提供しています。Zendeskには日々新しいチケットが起票され、対応しているとあっという間に1週間が過ぎていきます。そんな1週間を振り返るべく、Fivetran、Snowflakeを使って、直近1週間にZendeskで起票されたチケットの「起票日」「件名」「企業名」の抽出に挑戦してみました。

前提条件と環境

今回使用した環境の前提条件は以下の通りとなります。

Zendeskはサンドボックスを使用
- サンドボックスにはテスト用としていくつかチケットを起票済み
FivetranのDestinationにSnowflakeを設定済み

ZendeskのサンドボックスからFivetranを経由してSnowflakeにデータを送る、という環境を作りました。

FivetranでZendeskと連携

FivetranにはすでにDestinationとしてSnowflakeが設定されています。こちらのConnectorsに新しくZendeskを追加します。

Connectorsの右上にあるCONNECTORをクリックします。

ソースの選択画面が表示されます。ソースの数が多いので、「Zendesk」を検索、Zendesk Supportを見つけて選択します。

Zendesk Supportの設定では、Destination SchemaとDomainを設定します。Destination SchemaはConnectorsに表示される任意の名前（Snowflakeで作成されるスキーマの名前）、Domainは接続先のZendeskを指定します。設定後、AUTHORIZEをクリックして認証します。

認証したのち、SAVE & TESTをクリックします。

テストと保存が完了すると、All connection tests passed!というメッセージが表示されます。VIEW CONNECTORをクリックして作成したConnectorに移動します。

Start Initial SyncをクリックするとZendesk、Snowflakeと初回の接続になります。

初回の接続が完了すると、メールでの通知があります。また、Fivetranの画面は以下のようになりました。

なお、今回は省略しましたが、連携するデータのうち一部はSchemaで有効無効を切り替えることができます。

Snowflakeでビューの作成

初回連携が終わってSnowflakeを見てみると、スキーマが作成されていました。

試しにTICKETのテーブルを見てみると、起票されたチケットのデータが表示されていました。

select * from ticket;

これでチケットのデータをSnowflakeで確認できましたが、カラムが多く、必要な情報を確認するのに探す時間がかかってしまいます。分かりやすく表示するために、「起票日」「件名」「企業名」のみ表示するビューを新しく作ってみます。

TICKETのテーブルには起票日と件名になる「CREATED_AT」「SUBJECT」のカラムがありました。また、「ORGANIZATION_ID」という企業のIDを表すカラムはありましたが、企業名となるカラムはありませんでした。企業名についてはORGANIZATIONのテーブルで参照できました。ORGANIZATIONのテーブルにある「NAME」で企業名、「ID」で企業のIDを確認します。

select * from organization;

TICKETのテーブルからは「CREATED_AT」「SUBJECT」「ORGANIZATION_ID」、ORGANIZATIONのテーブルからは「NAME」「ID」のカラムを使って新しいViewを作成してみます。

まずは「1週間分のチケット」という条件はのぞいて、TICKETテーブルの「CREATED_AT」「SUBJECT」、ORGANIZATIONテーブルの「NAME」を表示するビューを作成してみました。

create view zendesk_sandbox_tickets as
select tic.created_at, tic.subject, org.name 
from ticket tic, organization org
where tic.organization_id = org.id;

作成したビューを確認してみると、「CREATED_AT」のタイムゾーンが日本時間ではないようです。

select * from zendesk_sandbox_tickets;

再度ビューを作成します。今度は「CREATED_AT」を日本時間への修正と、条件に「本日の1週間前以上日時」（直近1週間という想定）を追加しています。

タイムゾーンの変更と日付の計算については、Snowflakeのドキュメントを参考にしました。

create view zendesk_sandbox_tickets as
select convert_timezone('Asia/Tokyo', tic.created_at) as CREATED_AT_in_tokyo,
tic.subject, org.name 
from ticket tic, organization org
where tic.organization_id = org.id
and CREATED_AT_in_tokyo >= dateadd(week, -1, convert_timezone('Asia/Tokyo', current_timestamp()));

ビューを確認しみると、時間が日本時間に修正されていました。また、チケットの件数も先ほどより減少し、4月に起票されたチケットは表示されていません。