ソーシャルゲームのカスタマーサポートを支える行動ログとredash

この記事はTech KAYAC Advent Calendar 2016の21日目の記事です。

こんにちは、ソーシャルゲーム事業部のぼくらの甲子園！ポケットのサーバサイド開発・運用を担当しておりますマコピーことid:mackee_wです。

↑のヘッダ画像の人物はワタクシ、という噂があります。みなさま答えはあっていましたでしょうか。

ぼくらの甲子園！ポケットとは

• 2014年9月にリリースされた共闘スポーツRPGのスマートフォン向けゲーム
  • 現在3年目でございます！！！
• 甲子園をモチーフにしてプレイヤーとプレイヤーが協力して別のチーム(CPUではない)と対戦して甲子園の頂点を目指す
  • チーム(高校)の部員がプレイヤー9人揃わなければ試合が始まらない縛りとかも特徴です

そんな感じで運営しておりますので興味を持たれましたらこちらからダウンロードのほどよろしくお願いしますm( )m。

ちなみにこの記事はぼくポケの記事の第1弾のつもりで、今回はカスタマーサポート・ログ編という感じです。あと「試合がつらいけれど試合は面白い編」と「同時に10個ぐらい複数イベント開発運用回していくウェイウェイウェイ〜〜！！！編」の用意がありますが来年にやっていきます。

この記事の対象者

• ソーシャルゲームやウェブサービスでユーザ様の行動ログを元に調査をしている人
• 運営しているサービスの機能が多すぎて都度管理画面を作るのがめんどくさい人
• 一部の人はSQLを叩けるが、大体の人はSQLを知らないチームに所属している人

TL;DR

• 行動ログをこまめに吐くことがカスタマーサポートの品質向上につながる
• redashは集計や解析だけではなく行動ログを調査する簡易的な管理画面としても使用可能である
• 一度SQLを登録しておけばフォームから調査クエリを投げることができる
• redash自体は安定しているしAPIをもあるので皆さん活用していきましょう

お問い合わせからの調査

ぼくらの甲子園！ポケット(以下ぼくポケ)では新機能の開発やイベントの運用の他にもカスタマーサポートという形でユーザ様からお問い合わせをいただくことがあります。例としてお問い合わせには以下の様なものがあります。

• ◯◯ということがしたい。どうすればよいか？
• スターティングメンバーが思った通りになっていない。説明して欲しい。
• とある操作をしたらゲーム内通貨が意図せず消えたようにみえる。何が起こったのか？

1個目に関しては説明すればいいのですが、2個目や3個目などはユーザ様の高校内の状況や、前後の行動から調べる必要があります。

そのための行動ログ

そのためにサーバのアプリ内でAPIが叩かれた際にいくつかの行動ログを仕込んでいます。

ぼくポケのサーバはPerlで書かれているのですが以下のような感じで何かやったらログを吐くようにしています。（以下のコードはあくまで例です！ 実際とは結構違います）

sub subtract_item_amount {
    # 保持数チェックとかロック取ったりとか

    $user_item_for_updated->update({
        amount => $before_amount - $subtraction_amount,
    });

    $user_action_logger->submit({
        type          => "subtract_item_amount",
        item_id       => $user_item_for_updated->item_id,
        before_amount => $before_amount,
        after_amount  => $user_item_for_updated->amount,
    });

    # 残数返したりとか
}

以上はアイテムを消費した時に出しているログのコードです。DB上で引いた後に$user_loggerというobjectのメソッドでログを送っています。

このクエリはトランザクション内で実行されていますが、ログが実際に送られることが確定するのはこのトランザクションが完了した際です。仕組みとしてはsubmit自体はこのログが送られるような無名関数をトランザクション成功時のフックに登録しているという感じです。 これによりロールバックした際はログが送られず実際にDBに反映された場合にのみ送られることを保証しています。(他にも色々考えることがありますがここでは語りません！)

さてこのログの送り方には特徴があります。

• $user_action_loggerはどのユーザの行動によりログが送られたかを知るために前もってユーザ情報を持っています。なのでこのログにはユーザを識別するためのIDが自動的に埋め込まれます。
• typeはログの種別です。typeの内容は他の箇所で発行されている行動ログとは違う識別子である必要があります
  • 現在この識別子の発行は人が手で判断して行っています。ですが人間なのでダブっちゃうことがありそういうときは困ることもあります。。。
  • 今思いましたがcallerを使ってソースコード上の位置で一意にする方法があるなと思いましたが後述するクエリで引く時に困りそうな感じがしますね
• その他の要素はスキーマレスです。何故ならば行動ログとして保存しておきたい情報やキー名というのは機能や行動ごとに違うからです

行動ログの保存

行動ログは各PerlのAPIサーバからfluentdによってログ集約サーバに送られます。(インスタンス数や名前などは実際とは違います)

さらにlog-aggregatorに集約されたログはfluentd-plugin-s3で行動ログ1つが1行のJSONになったファイルとしてアップロードされます。

S3へのアップロードされるとそれがSQSによってlog-aggregatorにいるRinというデーモンがRedshiftに対してクエリを投げます。RedshiftはS3からログをスキーマ内に格納します。

Rinにつきましてはid:sfujiwaraさんの記事が参考になります。

sfujiwara.hatenablog.com

Redshiftに保存する際のログのスキーマは以下のようになります。

CREATE TABLE action_logs (
  uid         INTEGER DISTKEY encode delta32k,
  time        INTEGER SORTKEY encode delta,
  type        VARCHAR(65535)  encode lzo,
  json        VARCHAR(65535)  encode lzo,
  created_at  DATETIME        encode delta32k,
  req_id      BIGINT          encode delta32k
);

uidが行動ログを発行したユーザの識別子でcreated_atがログが発行された日時、typeが先程紹介したログの種別ごとの識別子、jsonがその他のキーを格納しているスキーマレスの部分です。req_idはAPIリクエストごとに発行されるIDですがこれについては後述します。

行動ログを検索する

例えばお問合わせで「何故アイテムが減ったのかを教えてほしい』というのが遭った場合に検索する場合にはまず以下のようなクエリで調べます。

SELECT * FROM action_logs
    WHERE uid = <ユーザ様のID> AND created_at BETWEEN <検索開始日時> AND <検索終了日時>
        AND type = 'subtract_item_amount';

日時で絞っているのはRedshiftで出てくるログは大量なのである程度当たりをつけるためにお問い合わせなどから推定して絞り込みます。ユーザ様のIDを調べるのはお問い合わせ時についてくるので簡単なのですが、時間はお問い合わせに書かれている時間が曖昧であったり、明確であっても人間の記憶なので前後してしまっていることもあります。経験から2,3時間前後の時間帯をいつも調べるようにしています。

typeに関しては過去のクエリなどを見たりとかソースコードを見たりなどして「あーアレね」など毎回やってます。後述のredashなので「こういうときはこれを見る」みたいなのが分かっているのでいいのですが、一覧にした方がいいのかもなーとも思っています。

さらに特定のアイテムであることが分かっている場合はJSON_EXTRACT_PATH_TEXTを用いて絞り込みが出来ます。

SELECT * FROM action_logs
    WHERE uid = <ユーザ様のID> AND created_at BETWEEN <検索開始日時> AND <検索終了日時>
         AND type = 'subtract_item_amount'
         AND JSON_EXTRACT_PATH_TEXT(json, 'item_id') = '<特定できているアイテムのID>';

JSON_EXTRACT_PATH_TEXTは入れ子になっていても検索することが出来るので便利です。さらに結果をわかりやすくしたいときにも使えます。

SELECT
    uid,
    created_at,
    JSON_EXTRACT_PATH_TEXT(json, 'item_id') AS item_id,
    JSON_EXTRACT_PATH_TEXT(json, 'before_amount') AS before_amount,
    JSON_EXTRACT_PATH_TEXT(json, 'after_amount') AS after_amount
FROM action_logs
    WHERE uid = <ユーザ様のID> AND created_at BETWEEN <検索開始日時> AND <検索終了日時>
        AND type = 'subtract_item_amount'
        AND JSON_EXTRACT_PATH_TEXT(json, 'item_id') = '<特定できているアイテムのID>';

さて、これだけではアイテムが減ったことだけがわかりますが、何故減ったのかがわかりません。ここでreq_idが効果を発揮します。

req_id

req_idはAPIリクエストごとに付けられた一意のIDです。具体的な発行方法としてはロードバランサーからやってきたリクエストをnginxで受ける時にkatsubushiを用いてsnowflake風味の64bitのIDをHTTPヘッダーに入れてそれをPerl側で読み取ってログに埋め込んでいます。

Perlでやらずにnginxでやっている理由としてはアクセスログにも同じIDを埋め込むためです。また、文字列(例えばUUIDなど)ではなくsnowflakeにしている理由としては数字でのソートが可能である点や発行された日時がIDからも容易に推測できる点などからです。

また、アクセスログも行動ログと同様の仕組みでredshiftに入れているのでJOINやサブクエリなどで一緒に検索することが出来ます。

SELECT * FROM access_logs
WHERE req_id IN (
    SELECT req_id FROM action_logs
        WHERE uid = <ユーザ様のID> AND created_at BETWEEN <検索開始日時> AND <検索終了日時>
            AND type = 'subtract_item_amount'
            AND JSON_EXTRACT_PATH_TEXT(json, 'item_id') = '<特定できているアイテムのID>'
);

これによりどのAPIアクセスでアイテムが減ったのかがわかります。また、前後のログにも同じreq_idが入っているので、

SELECT * FROM action_logs
WHERE req_id IN (
    SELECT req_id FROM action_logs
        WHERE uid = <ユーザ様のID> AND created_at BETWEEN <検索開始日時> AND <検索終了日時>
            AND type = 'subtract_item_amount'
            AND JSON_EXTRACT_PATH_TEXT(json, 'item_id') = '<特定できているアイテムのID>'
);

とすることも出来ます。これによりAPIの種類や前後のログからどのような操作が行われてアイテムが減ったかが分かります。

redashを用いて行動ログを他の人にも利用してもらう

redashについてはid:handlenameさんが書かれたこちらの記事に詳細な記述があります。

techblog.kayac.com

簡単に言えば様々なデータストアに対してクエリを投げて結果を保持しビジュアライズする機能を持ったソフトウェアです。

さて、redash導入以前にはSQLを知っているサーバサイドエンジニアしか調査が出来ていませんでした。お問い合わせ数の多い事例に関してはもともとあった管理画面からPostgreSQLのドライバを入れてRedshiftを叩いて表示するなどしていましたが、機能やイベント数が多いため管理画面の更新が追いついていないのが現状でした。

それとは別件で統計などを取るケースも多いため、Lobi事業部のほうで導入されているredashの噂を聞きつけて統計・解析用にredashを導入したところ、これはカスタマーサポートにも使えるぞ？と気が付きました。

以下にredashがカスタマーサポートに向いている点について挙げてみます。

WebUIでクエリを投稿してボタンポチッで投げることが出来る

redashの機能そのものですが、redashが来るまでは調査用に立てているRedshiftや本番DBのスレーブのMySQLにつなぐことの出来るサーバにログインしてそこからクエリを投げていたわけです。もちろん管理画面に自由にSQLを入れられるようなUIを作ることも出来ますが、調査用のクエリは実行時間が長いことも多く、ジョブキュー的なしくみを作り込まなければなりません。

redashはそのあたりがちゃんと作りこんであり、またクエリエディタやカラム名の補完(最近付きました)、forkなどの機能もあり、クエリを登録しておいて他の人に投げたり結果を見せたりするのには非常に便利なソフトウェアであると言えます。

またSQLを知らなくてもSQLを投げることが出来るというメリットがあります。なのでカスタマーサポートに関わる人に対して「このような問い合わせにはこのクエリを使ってみてください」とredashに登録したクエリへのリンクを提示することが出来ます。

フォームでクエリに文字列の埋め込みが出来る

redashで感動したのは{{hogehoge}}というふうに波括弧で囲った文字列をクエリに埋め込むとシュッとフォームが出来上がることです。

例えば先程のクエリであれば、

このようにフォームが現れます。クエリの部分は人に見せるときには隠れていますから、フォームにIDや日時などを入れてもらうだけで様々なユーザ様の問い合わせに対応することが出来ます。

その他やっていること

• redashのドキュメントにはない気がしますがAPI Keyを発行してAPIを投げることが出来ます。redashは定期的に投げたり一日の特定の時間にスケジューリングすることは出来ますが、1ヶ月に1回投げて結果を保存しておくという事はできないので、Jenkinsにタスクを登録してAPIを投げてSlackに通知するというジョブを作っています
• アラート機能を使ってデータの変化を知ることが出来ます。これを用いてバッチの実行の確認などをSlackに登録しています

まとめ

だいたいTL;DRに書いてあることですが、redashで業務を改善したことでサーバサイドエンジニアもコーディングに集中出来るようになりましたし、驚いたのはカスタマーサポートを担当する企画部の方から「redashの登場は本当にイノベーションです」と言われたことです。

そんな感じで業務を良くしていって良いゲームを作る手助けをしていただけるエンジニアをカヤックでは募集しております！