PHP
curl
PostgreSQL
zendesk
yellowfin

ZendeskのデータをDBに格納して可視化する

最終的にはこんな感じに

image.png

これがあると大体できます

PHP
PostgreSQL
PDO
pear/HTTP/Request2
Restlet Client(あれば開発しやすいchromeの拡張機能)

Zendeskの可視化とデータ構成

そもそもZendeskのデータはあまり横断的に見ることができず、もともとのInsight情報ではユーザーにあまり優しくないようです。
Zendeskのデータは主にTicketsのAPIで取得できますが、それに関連した初回返信時間や解決までにかかった時間は別のTicket MetricsのAPIで取得しなければならないため、ちょっと面倒な構成になっています。また、コメントはTicket Comments APIで取得しなければなりません。。
さらに、Tickets APIで取得できる人のデータ、組織のデータはidで持っており、それぞれUsers API,Organizations APIと別でマスタのようになっているため、これらも取得して、Ticketのデータをより見やすくするのが目的です。

注意点

このAPI取得の方法では、CloseされArchiveされた古いデータは取得できないようなので、そこのデータまで取りたい場合は、テーブルに連結してぶち込んであげる必要があります。
ZendeskAPIは1回のリクエストにつき、100件しか取得できないため、複数回pagingして取得する必要があります。
また、それぞれ契約しているプランによって、1分間にリクエストできる回数が限られているので、リミットに触れないようにうまく、loopの間にsleepなどを挟んであげる必要があります。
変な点が合ったら補足・コメントください。。。

初期処理(前回実行時間取得)

zendesk.php
<?php

// 初期設定
require_once '/usr/share/pear/HTTP/Request2.php';
require_once '/usr/share/php/sendSlack.php'; //errorはSlackに飛ばす

require_once '/var/sett.php'; //setting系は別ファイルへ

date_default_timezone_set('Asia/Tokyo');
$update_time = date('Y-m-d H:i:s');

$seltime = "";

try {

    // データベースに接続
    $pdo = new PDO(
        $dsn,
        $user,
        $pass,
        array(
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        )
    );

    //Ticketは数が多いので、前に起動した時間と比べてupdated_atが新しければinsertかupdateを行う
    $sql = 'select * from tsys001 where tname = \'TZND001\'';
$result = $pdo->query($sql);
foreach ($result as $row) {
    $seltime = $row['seltime'];
}

$pdo->exec("DELETE FROM TZND004"); //Organizations Table
$pdo->exec("DELETE FROM TZND003"); //Users Table
$pdo->exec("DELETE FROM TZND002"); //TicketMetrics table


} catch (PDOException $e) {

    // 例外メッセージを表示
    sendSlack("BZND001",$e->getMessage());
    echo $e->getMessage();
    $pdo = null;

}

//pdoオブジェクトを初期化
$pdo = null;

print_r("seltime : ".$seltime);
$seltime = strtotime($seltime);

一番重要なcurlを使うfunction

全てのAPIはここで投げられ、連想配列で返すfunctionです。

zendesk.php
function exec_curl($url){

    $credentials = "xxxxxxxxxxx@xxxxxxxxx.co.jp:xxxxxxxxxxx"; //zendeskのID:pass

    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL,$url);
    curl_setopt($ch, CURLOPT_USERPWD,$credentials);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($ch, CURLOPT_TIMEOUT, 60);

    $data = curl_exec($ch);
    curl_close($ch);

    if (curl_errno($ch)) {
        print "Error: " . curl_error($ch) ."  ". $url;
    } else {
        $json = mb_convert_encoding($data, 'UTF8', 'ASCII,JIS,UTF-8,EUC-JP,SJIS-WIN');
        $arr = json_decode($json,true);
        return $arr;
    }
}

Ticket(メインテーブル)の取得

フィールドの取得はTicket FieldsAPIで取得できますが、ここもIDになっているので、固定(決め打ち)で指定しています。
IDと中身についてはRestlet Clientかなにかで確認すると楽です。
https://developer.zendesk.com/rest_api/docs/core/ticket_fields
あと、コメントの数も集計しています。pulicコメントかprivateコメント、総コメント数ですね。
データを取得した際に、次のデータが有ればnext_pageに次のページのURLがついてくるので、それを使い自己呼び出しを行います。この際、コメントの取得で最大100リクエストくらいするので60秒sleep処理を入れています。

zendesk.php
function getTickets($url,$seltime){

    global $dsn;
    global $user;
    global $pass;

    $arr = exec_curl($url);

    $upsert_pdo = new PDO(
    $dsn,
    $user,
    $pass,
    array(
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
        )
    );


    try {

        if ($arr === NULL) {
            return;
        }else{
            $json_count = count($arr["tickets"]);

            for($i=$json_count-1;$i>=0;$i--){
                $updt = $arr["tickets"][$i]["updated_at"];

                $tdate1 = substr($updt,0,10);
                $tdate2 = substr($updt,11,8);
                //$seltime = strtotime($seltime);
                $rectime = strtotime($tdate1." ".$tdate2);

                $diff = $rectime - $seltime;

                if ($diff < 0) {
                    # code...
                } else {

                    $tid = $arr["tickets"][$i]["id"];
                    $subject = $arr["tickets"][$i]["subject"];
                    $status = $arr["tickets"][$i]["status"];
                    $via = $arr["tickets"][$i]["via"]["channel"];
                    $crdt = $arr["tickets"][$i]["created_at"];
                    $orgid = $arr["tickets"][$i]["organization_id"];
                    $euser = $arr["tickets"][$i]["requester_id"];
                    $priority = $arr["tickets"][$i]["priority"];

                    $segment = "";
                    $class = "";
                    $ver = "";
                    $category = "";
                    $jira = "";
                    $function = "";
                    $rdb = "";
                    $adb = "";
                    $browser = "";
                    $platform = "";
                    $assignee = "";
                    $sscore = "";
                    $scomment = "";

                    $fields_count = count($arr["tickets"][$i]["fields"]);
                    for($j=$fields_count-1;$j>=0;$j--){

                        if($arr["tickets"][$i]["fields"][$j]["id"]==26705928){
                            $segment = $arr["tickets"][$i]["fields"][$j]["value"];
                        }
                        if($arr["tickets"][$i]["fields"][$j]["id"]==26327017){
                            $class = $arr["tickets"][$i]["fields"][$j]["value"];
                        }
                        if($arr["tickets"][$i]["fields"][$j]["id"]==26302728){
                            $ver = $arr["tickets"][$i]["fields"][$j]["value"];
                        }
                        if($arr["tickets"][$i]["fields"][$j]["id"]==81157147){
                            $category = $arr["tickets"][$i]["fields"][$j]["value"];
                        }
                        if($arr["tickets"][$i]["fields"][$j]["id"]==26327047){
                            $browser = $arr["tickets"][$i]["fields"][$j]["value"];
                        }
                        if($arr["tickets"][$i]["fields"][$j]["id"]==26327037){
                            $platform = $arr["tickets"][$i]["fields"][$j]["value"];
                        }

                    }

                    $assignee = $arr["tickets"][$i]["assignee_id"];
                    $sscore = $arr["tickets"][$i]["satisfaction_rating"]["score"];
                    $scomment = $arr["tickets"][$i]["satisfaction_rating"]["comment"];

                    $comurl = "https://xxxxxxxxxx.zendesk.com/api/v2/tickets/".$tid."/comments.json";

                    $comments = exec_curl($comurl);
                    $totalc = count($comments["comments"]);
                    $publicc = 0;
                    for($k=$totalc-1;$k>=0;$k--){
                        if ($comments["comments"][$k]["public"]==true) {
                            $publicc = $publicc + 1;
                        }
                    }
                    $privatec = $totalc - $publicc;
                    //print_r("commentsum : ".$totalc."<br>public : ".$publicc."<br>private : ".$privatec."<br>");

                    $sql = 'select count(1) as cnt from TZND001 where tid ='.$tid;
                    $result = $upsert_pdo->query($sql);
                    $result = $result->fetch();
                    if ($result['cnt'] == 0) {
                        //insert
                        $stmt_insert = $upsert_pdo->prepare("INSERT INTO TZND001 ( tid,subject,status,via,crdt,updt,orgid,euser,priority,segment,class,ver,category,browser,platform,assignee,totalc,publicc,privatec,sscore,scomment)
                         VALUES (:tid,:subject,:status,:via,:crdt,:updt,:orgid,:euser,:priority,:segment,:class,:ver,:category,:browser,:platform,:assignee,:totalc,:publicc,:privatec,:sscore,:scomment)");
                        $stmt_insert->execute(compact('tid','subject','status','via','crdt','updt','orgid','euser','priority','segment','class','ver','category','browser','platform','assignee','totalc','publicc','privatec','sscore','scomment'));
                    } else {
                        //update
                        $stmt_insert = $upsert_pdo->prepare("UPDATE TZND001 SET subject = :subject, status = :status, via = :via, crdt = :crdt, updt = :updt, orgid = :orgid, euser = :euser, priority = :priority, segment = :segment, class = :class, ver = :ver, category = :category, browser = :browser, platform = :platform, assignee = :assignee, totalc = :totalc, publicc = :publicc, privatec = :privatec, sscore = :sscore, scomment = :scomment WHERE tid = :tid"); 
                        $stmt_insert->execute(compact('tid','subject','status','via','crdt','updt','orgid','euser','priority','segment','class','ver','category','browser','platform','assignee','totalc','publicc','privatec','sscore','scomment'));
                    }

                }//if ($diff < 0) ここまで

            }
            if($arr["next_page"]!=null){
                sleep(60);
                getTickets($arr["next_page"],$seltime);
            }
        }

    } catch (PDOException $e) {
        sendSlack("BZND001","ticketID : ".$tid."
    ".$e->getMessage());
        echo "tID : ".$tid;
        $upsert_pdo = null;
        // 外側のTryブロックに対してスロー
        throw $e;
    }
    $upsert_pdo = null;
}

Ticket Metricsの取得

Ticketに比べてはるかに楽です。あと、UsersとOrganizationsもほとんど同じ(idとnameだけの取得)なので割愛します。

zendesk.php
function getTicketMetrics($url){

    global $dsn;
    global $user;
    global $pass;

    $arr = exec_curl($url);

    $upsert_pdo = new PDO(
        $dsn,
        $user,
        $pass,
        array(
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        )
    );


    try {

            if ($arr === NULL) {
                return;
            }else{
                $json_count = count($arr["ticket_metrics"]);
                $tid = 0;
                $freply = 0;
                $resolve = 0;

                for($i=$json_count-1;$i>=0;$i--){
                    $tid = $arr["ticket_metrics"][$i]["ticket_id"];
                    $freply = $arr["ticket_metrics"][$i]["reply_time_in_minutes"]["business"];
                    $resolve = $arr["ticket_metrics"][$i]["full_resolution_time_in_minutes"]["business"];

                    $stmt_insert = $upsert_pdo->prepare("INSERT INTO TZND002 ( tid,freply,resolve) VALUES (:tid, :freply, :resolve)");
                    $stmt_insert->execute(compact('tid','freply','resolve'));

                }
                if($arr["next_page"]!=null){
                    getTicketMetrics($arr["next_page"]);
                }
            }

    } catch (PDOException $e) {
        sendSlack("BZND002","ticketID : ".$tid."
    ".$e->getMessage());
        echo "ticketID : ".$tid;
        $upsert_pdo = null;
        // 外側のTryブロックに対してスロー
        throw $e;
    }
    $upsert_pdo = null;
}

メインストリーム続き

各functionを起動させて、起動時間をupdateして終了です。

zendesk.php
$url = "https://xxxxxxxxxx.zendesk.com/api/v2/organizations.json";
getOrganizations($url);

$url = "https://xxxxxxxxxx.zendesk.com/api/v2/users.json";
getUsers($url);

$url = "https://xxxxxxxxxx.zendesk.com/api/v2/ticket_metrics.json";
getTicketMetrics($url);

$url = "https://xxxxxxxxxx.zendesk.com/api/v2/tickets.json";
getTickets($url,$seltime);


try {

    // データベースに接続
    $up = new PDO(
        $dsn,
        $user,
        $pass,
        array(
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        )
    );

$result = $up->prepare('UPDATE TSYS001 SET seltime = :update_time, upr = \'BZND001\', updt = date_trunc(\'second\',CURRENT_TIMESTAMP) WHERE tname = \'TZND001\'');
$result->execute(compact('update_time'));


} catch (PDOException $e) {

    // 例外メッセージを表示
    sendSlack("BZND001",$e->getMessage());
    echo $e->getMessage();
    $up = null;

}

$up = null;

echo $update_time." : BZND001 : 処理が最後まで終了しました。\n";

可視化について

最初のテーブルも合わせて、可視化はYellowfinで行います。
入れたデータを結合するだけでも断然見やすくなりますね!
image.png
image.png
image.png

参考にさせて頂きました

PHPでJSONデータの取得の仕方
https://qiita.com/fantm21/items/603cbabf2e78cb08133e