Game KPI Reporting on the Cloud

Leading gaming companies have used Treasure Data to store all of their behavior logs to achieve data-driven product decisions. These insights help product managers enhance existing game systems and deliver a better customer experience, resulting in higher revenues.

This article will explain how to log data from your applications and calculate some basic KPIs using Treasure Data. Treasure Data lets your team focus on building awesome games instead of having to spend valuable human/IT resources maintaining a data analytics infrastructure.

Table of Contents

Background

Online gaming companies run their entire businesses on the web. For these companies, monitoring business KPI and analyzing user behavior are top strategic priorities. Their applications are deployed on hundreds of servers in order to handle requests from millions of users from various devices (e.g. iPhone, Android, PC).

These application processes generate terabytes of user behavior data every day: access, register, login, invite, battle logs, etc. By processing that data, the company can track key metrics like ARPU and ARPPU, as well as more game specific metrics such as the distribution of user levels. Maintaining and tracking these KPIs is a critical activity in continuously improving the game system.

Prerequisites

  • Basic knowledge of Treasure Data, including the toolbelt.
  • Basic knowledge of td-agent.
  • Ruby 1.8 or higher (for local testing).

Architecture



td-agent is a versatile daemon program that can process various kinds of streaming log data. It is developed and maintained by Treasure Data, Inc..

When an application posts logs to td-agent, td-agent continuously 1) receives the data, 2) buffers it, 3) and uploads it into the cloud. The data is automatically compressed before it is uploaded into the cloud.

Installing td-agent

You must first set up td-agent on your application servers. Please refer to the following articles.

If you have... Please refer to...
MacOS X Installing td-agent on MacOS X
Ubuntu System Installing td-agent for Debian and Ubuntu
RHEL / CentOS System Installing td-agent for Redhat and CentOS
AWS Elastic Beanstalk Installing td-agent on AWS Elastic Beanstalk

For Linux systems, we provide deb/rpm packages.

Modifying /etc/td-agent/td-agent.conf

You need to specify your authentication key in /etc/td-agent/td-agent.conf. You can check your apikey from the console. Next, set the apikey option in your td-agent.conf file.

# Treasure Data Input and Output
<match td.*.*>
  type tdlog
  endpoint api.treasuredata.com
  apikey YOUR_API_KEY
  auto_create_table
  buffer_type file
  buffer_path /var/log/td-agent/buffer/td
  use_ssl true
</match>
Untitled-3
*YOUR_API_KEY* should be your actual API key string.

Now, restart td-agent to reflect the changes you made above.

$ sudo /etc/init.d/td-agent restart

td-agent is now ready to accept logs from your application.

Logging from Game Applications

In this example, we will assume that the application is written is Ruby. For other languages, please refer to these documents.

Supported Languages
Ruby or Rails Java Perl
Python PHP Scala
Node.js

Let’s first set up the logging code for important game events. Shown below is actual code that can be used. The time is automatically added by the logging library.

#setting it all up: capturing our API key, creating the table and starting the logger
api_key = ARGV[0]
TreasureData::Logger.open('ibeacon_test', 
          :apikey=> api_key, 
          :auto_create_table=> true)

  # access (all accesses)
  TD.event.post('access', {:uid=>123})

  # register
  TD.event.post('register', {:uid=>123, :from=>'AdCampaign1234'})

  # login
  TD.event.post('login', {:uid=>123})

  # home
  TD.event.post('home', {:uid=>123, :level=>13, :hp=>21323, :mp=>313})

  # invite
  TD.event.post('invite', {:uid=>123, :target_uid=>567})

  # invite_accept
  TD.event.post('invite_accept', {:uid=>567})

  # pay
  TD.event.post('pay', {:uid=>123, :item_name=>'Stone of Jordan',
                        :category=>'ring', :price=>100, :count=>1})
  # resign
  TD.event.post('resign', {:uid=>123})
# flushing the logger speeds up the event posting process
TreasureData::Logger.logger.flush()

Sample Queries

Here are a few sample queries that calculate some KPIs accessing the logs posted in the production database.

Daily Access Count

$ td query -w -d production \
  "SELECT
     TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'PDT') AS day,
     COUNT(1) AS cnt
   FROM access
   WHERE TD_TIME_RANGE(time, '2013-01-01 PDT', NULL, 'PDT')
   GROUP BY TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'PDT')"

Daily Access User (DAU)

$ td query -w -d production \
  "SELECT
     TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'PDT') AS day,
     COUNT(DISTINCT uid) AS cnt
   FROM access
   WHERE TD_TIME_RANGE(time, '2013-01-01 PDT', NULL, 'PDT')
   GROUP BY TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'PDT')"

Montly Access User (MAU)

$ td query -w -d production \
  "SELECT
     TD_TIME_FORMAT(time, 'yyyy-MM', 'PDT') AS day,
     COUNT(DISTINCT uid) AS cnt
   FROM access
   WHERE TD_TIME_RANGE(time, '2013-01-01 PDT', NULL, 'PDT')
   GROUP BY TD_TIME_FORMAT(time, 'yyyy-MM', 'PDT')"

Daily Paid User

$ td query -w -d production \
  "SELECT
     TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'PDT') AS day,
     COUNT(DISTINCT uid) AS cnt
   FROM pay
   WHERE TD_TIME_RANGE(time, '2013-01-01 PDT', NULL, 'PDT')
   GROUP BY TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'PDT')"

Daily Sales

$ td query -w -d production \
  "SELECT
     TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'PDT') AS day,
     SUM(CAST(price AS INT) * CAST(count AS INT))
   FROM pay
   WHERE TD_TIME_RANGE(time, '2013-01-01 PDT', NULL, 'PDT')
   GROUP BY TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'PDT')"

Daily Sales per Item Name

$ td query -w -d production \
  "SELECT
     TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'PDT') AS day,
     item_name,
     SUM(CAST(price AS INT) * CAST(count AS INT))
   FROM pay
   WHERE TD_TIME_RANGE(time, '2013-01-01 PDT', NULL, 'PDT')
   GROUP BY item_name, TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'PDT')"

ARPU (Average Revenue per User)

Daily Sales / DAU    

ARPPU (Average Revenue per Paid User)

Daily Sales / Daily Paid User

Last modified: Feb 24 2017 09:27:52 UTC

If this article is incorrect or outdated, or omits critical information, please let us know. For all other issues, please see our support channels.