【Googleスプレッドシート】ZendeskのチケットをSheetsで集計!サポート分析の自動化

Zendeskに蓄積された大量のチケットデータを、毎回手作業で集計するのはとても手間がかかります。Googleスプレッドシートを使えば、Zendeskのデータを自動で取り込み、リアルタイムに近い形でサポート分析が可能です。この記事では、CSVエクスポートからQUERY関数での集計、さらにGoogle Apps Scriptを使った完全自動化までの手順を解説します。

ZendeskチケットデータをGoogleシートで扱う仕組み

Zendeskは、チケットデータをAPIやCSVエクスポートで外部に取り出す機能を提供しています。Googleスプレッドシートはこれらのデータを読み込むのに適した環境です。特にQUERY関数を使えば、エクセルのピボットテーブルのようにデータを集計できます。さらにGoogle Apps Scriptを組み合わせることで、定期的なデータ更新や加工を完全に自動化できます。この仕組みを理解すると、サポートチームの生産性が大きく向上します。

手動でデータを取り込んで集計する手順

ZendeskからCSVをエクスポートする方法

1. Zendeskにログインし、チケット一覧を開く
   左サイドバーの「チケット」をクリックし、エクスポートしたいビューを選びます。
2. エクスポートオプションを選択する
   画面上部の「エクスポート」ボタンから「CSV」を選びます。フィルタやグループ化を適用した状態でエクスポートできます。
3. CSVファイルをダウンロードする
   メールで届くリンクか、直接ダウンロードをクリックしてCSVファイルを保存します。

CSVをGoogleシートにインポートする

1. Googleスプレッドシートを開く
   新しいシートを作成し、メニューから「ファイル」→「インポート」を選びます。
2. CSVファイルをアップロードする
   「アップロード」タブでダウンロードしたCSVを選択し、インポート形式を「現在のシートに置き換え」にします。
3. データが正しく読み込まれたか確認する
   日付や数値が文字列になっていないかチェックし、必要に応じて書式を整えます。

QUERY関数でチケット数を集計する

1. 集計用のシートを用意する
   インポートしたデータがシート1にある場合、シート2に集計結果を出力します。
2. QUERY関数を入力する
   例えば、ステータス別のチケット件数を表示するには、セルA1に次のように入力します。
   =QUERY(シート1!A:Z, "select B, count(A) where A is not null group by B label count(A) '件数'", 1)
3. 必要に応じて条件を追加する
   期間や担当者で絞り込みたい場合は、where句に日付の条件を加えます。
   =QUERY(シート1!A:Z, "select B, count(A) where D >= date '2024-01-01' and D <= date '2024-12-31' group by B", 1)

Google Apps Scriptで自動更新を設定する手順

スクリプトでZendeskからデータを取得する

1. スクリプトエディタを開く
   Googleシートのメニューから「拡張機能」→「Apps Script」を開きます。
2. Zendesk API用のスクリプトを記述する
   以下のコードをエディタに貼り付け、サブドメインと認証情報を設定します。

   function fetchZendeskTickets() {
     var subdomain = 'your-subdomain';
     var email = 'your-email@example.com';
     var token = 'your-api-token';
     var url = 'https://' + subdomain + '.zendesk.com/api/v2/tickets.json';
     var options = {
       'headers': {
         'Authorization': 'Basic ' + Utilities.base64Encode(email + '/token:' + token)
       }
     };
     var response = UrlFetchApp.fetch(url, options);
     var data = JSON.parse(response.getContentText());
     // データをシートに書き込む処理…
   }

3. データをシートに書き込む
   取得したチケットデータを二次元配列に変換し、シートにセットします。例として、先頭の5列だけ出力するコードを示します。

   var sheet = SpreadsheetApp.getActiveSheet();
   var rows = [];
   for (var i = 0; i < data.tickets.length; i++) {
     var t = data.tickets[i];
     rows.push([t.id, t.subject, t.status, t.created_at, t.assignee_id]);
   }
   sheet.getRange(1, 1, rows.length, 5).setValues(rows);

定期的なトリガーを設定する

1. トリガーを追加する
   スクリプトエディタの左メニューから「トリガー」を選び、「トリガーを追加」をクリックします。
2. 実行する関数と間隔を設定する
   関数に「fetchZendeskTickets」を選択し、時間ベースのトリガーで「日タイマー」を指定します。毎朝8時などに実行すると良いでしょう。
3. 承認を行う
   初回のトリガー実行時に、権限の承認が求められます。指示に従って許可してください。

自動集計の注意点とトラブル対応

インポート時に文字化けが発生する場合

CSVファイルの文字コードがUTF-8でない場合、文字化けすることがあります。Zendeskのエクスポートは通常UTF-8ですが、Windows環境で開くときなどに問題が起きます。Googleシートにインポートする際は、「ファイル」→「インポート」→「文字コード」でUTF-8を明示的に指定してください。

スクリプトの実行エラーが頻発する場合

Zendesk APIのレート制限に引っかかる場合があります。1分あたりのリクエスト数が上限を超えるとエラーになります。スクリプト内でUtilities.sleep(1000)を使って1秒以上の間隔を空けるか、一度に取得するチケット数を100件以下に制限してください。また、トリガーの間隔は最低でも10分以上に設定することをおすすめします。

手動集計と自動化の比較

自動化には初期設定のコストがかかりますが、長期的には大きな時間削減になります。特にチームで共有するダッシュボードを作る場合、自動更新は必須と言えるでしょう。

まとめ

ZendeskのチケットデータをGoogleスプレッドシートで集計する方法を、手動と自動化の両面から解説しました。QUERY関数を使えばエクセルのピボットテーブル並みの集計が可能であり、Apps ScriptとAPIを組み合わせれば毎日のデータ更新を完全自動化できます。まずは簡単なQUERY集計から試し、慣れてきたらスクリプトによる自動化に挑戦してください。応用として、スプレッドシートのグラフ機能と連携すれば、リアルタイムなサポート分析ダッシュボードも作成できます。