At Robot Riot we needed a cheap and easy to setup metrics dashboard to access, visualize and share data generated by our first free-to-play browser game “The Big Catch“. Business analytics tools like Kontagent were way too expensive and overpowered for what we needed in the beginning. On the other hand, developing our own tool including a custom made web front-end was also not an option because of limited coding resources. So we went for a solution with an unbeatable price tag: Google Spreadsheets. In this article I want to share some of our learnings and also explain some basic metrics we track in our dashboard.
Why Google spreadsheets?
The key to a useful metrics dashboard on Google docs is to automatically upload selected metrics on a daily basis from the database via the Google API. The spreadsheet becomes your front-end, no need to code any custom views.
Here are some advantages of an online metrics dashboard based on Google spreadsheets:
- It’s free
- Easy to create, maintain and extend
- Accessible through the browser and hence it also works on your smartphone (big plus! However, charts use Flash so no iOS support for them.)
- Reliable
- Fast enough
- Automatic data updates possible
- Easy to share with others and to set permissions
- It is surprisingly powerful and provides a great user experience for people viewing the data.
What is it lacking off?
The dashboard is not meant to be a full-blown analytics tool that allows you to drill deep down into your player data (although Pivot tables and custom made gadgets can take you quite a long way). If I want to take a much closer look at player behaviour I still run SQL queries using another fantastic tool: Sequel Pro and then massage the resulting data into a static sheet for presentation purposes. Importing copious amounts of data can push a Google spreadsheet to its limit, though. The number of characters you can copy/paste into a sheet is limited as well as the total number of cells a spreadsheet can use. In that case falling back to Excel is still an option.
Another potential downside is that Google knows what did last summer. If you are worried about disclosing your data to Google you might want to consider alternative solutions.
How does it work?
The server developers set up a bunch of small jobs, which run on a nightly basis. Each job calculates typically only one value for the previous day (e.g. daily active users, number of registrations, amount of virtual currency spent, etc.) and stores it in a database. Another program then collects all the stored values from the database and uploads them into a previously set-up Google spreadsheet via Google’s API.
Here are a three learnings from our server devs:
- Do not let the server-side calculation jobs perform stuff you could as well calculate in the Google spreadsheet.
- Google spreadsheets have a maximum cell limit, so you should plan on uploading only data for some time in the past, not all data since collection started.
- Some numbers may need to be calculated for the day before yesterday because they need to inspect data which is yet to come (e.g. “average player session length”, where sessions can start a certain day but may end the next day).
Spreadsheet structure
The following picture shows the high-level structure of the setup:
Raw Data sheet
The server uploads new data every night to the Raw Data sheet. Its content is replaced completely during this process. Hence it does not make any sense to modify this sheet manually since all changes will be overwritten with the next data upload. We just leave the Raw Data sheet alone, hide it and created another sheet that copies all relevant columns – the KPI sheet in this example.
KPI sheet
All other sheets (e.g. Dashboard, Revenue charts, etc.) refer to the KPI sheet. Since the KPI sheet refers to the Raw Data one the new data that comes in every night cascades throughout all dynamic sheets.
The KPI sheet is a reformatted and filtered version of the Raw Data sheet. It also contains in-cell sparkline charts, which are a nice little feature to get a quick impression how the different metrics have developed over time.
Some data in our Raw Data sheet turned out to be not as relevant as we originally thought when we defined the list of metrics we wanted to track. It’s easy to come up with all kinds of metrics you think might be important pre-launch, only to figure out post-launch that you actually needed completely ones. The best practice here is to keep your set of metrics to a minimum in the beginning. Besides the usual suspects (DAU, MAU, Revenue, etc.) it should be easy to determine one to three key gameplay metrics, which best represents player engagement and progression (e.g. session length, number of players per level per day, number of catches/kills/main action per DAU, etc.) Once the has been launched and you find yourself running the same SQL queries over and over again it might be a good idea to add that query to the nightly server jobs.
How to keep the KPI sheet up to date?
The untouchable Raw Data sheet grows by one row each day. Of course, you want your nice and shiny KPI sheet to stay up to date with the Raw Data one as well. In this case the ARRAYFORMULA function is your friend.
Let’s assume column A in the Raw Data sheet contains the dates in plain text format, as it comes from the server. In this case the following formula will fill a column of your choice in your KPI sheet with all values it finds in the corresponding Raw Data sheet’s column A, starting at cell A2:
=ARRAYFORMULA(DATEVALUE('Raw Data'!A2:A))
The DATEVALUE function transforms the plain text date into a proper date format you need, for example, for the timeline charts later on.
Your Raw Data sheet stays untouched while you can format the data in your KPI sheet as you like, add Sparkline charts, add and rename columns and use columns for calculations. For example to get the number of all returning players per day: Column C contains the number of DAU per day, column B the number of new players per day. When you deduct the new players from the DAU you get the number players who returned:
=ARRAYFORMULA(C2:C-B2:B)
Side note: It would be nice to use named ranges here, but these ranges cannot be dynamic. Since each day a new column is added to the KPI sheet the named range should cover the new row as well. Unfortunately a named range like B2:B returns an error. If column B has 100 rows, the named range would include B2:B100. On the next day it has 101 rows but the named range still only takes the rows 2 to 100 into account.
Dashboard sheet
Combined with a previous day comparison the dashboard sheet provides all metrics at a glance for a quick daily check. Our Dashboard looks like this (filled with dummy data):
It also contains an overview of the level progression (dummy data again):
Relevant metrics to be considered:
- Last day’s total revenue
- ARPDAU (average revenue per daily active user)
- ARPWAU (average revenue per weekly active user)
- ARPMAU (average revenue per monthly active user)
- Number of transactions
- DAU (daily active users)
- New players
- Returning players
- 7 day retention (DAU / WAU)
- 30 day retention (DAU / MAU)
- Average session length
- (whatever in-game metrics you need)
Monthly metrics sheet
This sheet is the basis for the long-term scope. Unfortunately, this sheet has to be party updated manually because in our case it requires data from an external reporting system that provides information about transaction revenues and total registration numbers. As mentioned before, the game’s own database only stores players who have logged in at least once. This excludes players who registered but then dropped out before logging in.
As the name suggests the monthly metrics summarises the metrics in form of monthly reports. Each month has its own row. We populate the columns with the following monthly metrics:
- Year.month
- Total registrations
- New players
- Drop-out rate
- MAU
- Average DAU
- Average retention (= average DAU / MAU)
- Total revenue
- Average ARPDAU
- ARPU (30 days)
- ARPPU (30 days)
- Conversion rate (= number of paying users / MAU in per cent)
- Average lifetime (all players)
- Average lifetime (returning players = lifetime > 0 days)
- Average session time
- (relevant gameplay metrics)
Chart sheets
Another positive surprise in Google Spreadsheets are interactive timeline charts:
Timeline charts let you zoom into periods of time either in pre-defined steps or freely by simply using sliders. Timeline series can also be annotated which is a great way to mark feature and balancing deploys. And, as you can see, you can embed them into any HTML page.
Metrics definitions
The following list explains some common metrics. It does not cover gameplay metrics simply because they differ from game to game. However, getting your gameplay metrics right is essential to measure the effects of feature and balancing changes. The challenge there is to ask the right questions and make the right conclusions based on the data. Metrics driven game development is a great thing, but making decisions based on cold data alone will lead to a stagnating game experience at one point and often hinders innovation. Finding the right balance between data and gut instinct is a real challenge.
Defining metrics can be tricky business as well and it is dangerously easy to get confused. A good example is the Hotel Problem.
Here’s the list:
Revenue (previous day)
This is simply the Euro equivalent of all hard currency spent within the game during the previous day. Hard Currency (HC) is a virtual in-game currency that players purchase with real money.
The revenue per day has its own column in the KPI sheet. For the Dashboard sheet only yesterday’s value is of interest. So how to get the value from the last cell in that column only? Here is one way to get the last cell (if you know a simpler way, please let me know):
=INDEX('KPI'!A:A;max(('KPI'!A:A<>"")*row('KPI'!A:A));1)
The function refers to the KPI sheet and returns the cell content of the last row within the defined range (A:A). It also ignores empty cells.
All time revenue
Nothing special here, the value is just the sum of all daily revenues of the whole range.
ARPU (lifetime value)
This is the lifetime average revenue per user.
ARPU (lifetime) = All time revenue / Players
ARPDAU / ARPWAU / ARPMAU
The average revenue per last day’s/last seven days’/last 30 days’ active user.
ARPDAU = Last day’s revenue / DAU
ARPWAU = Last seven days’ revenue / WAU
ARPMAU = Last 30 days’ revenue / MAU
ARPPDAU
Average revenue per paying daily active user.
ARPPDAU = last day’s revenue / last day’s number of unique paying users
Conversion rate
The percentage of paying players.
= number of paying player per time period / total number of unique players of the same time period
Churn rate
That’s a tricky one. How do you define churn? If you run a subscription based service the answer is easy. Churn is the number of customers who cancel their subscription per time period. But how do you define churn in a free-to-play game? At which point do you declare a player as “dead”? After one week? After month? Maybe he comes back three months later and distorts your average lifetime value because his gross lifetime is four month but his actual net playtime was just ten days.
One approach could be to compare the number of MAU at the start and end of a given period, a month for example, and also take the number of new players during that period into account:
Churn rate (%) = 1 - (MAU[end] / (MAU[start] + new players))
Example:
MAU @ start of month: 5.000
MAU @ end of month: 6.000
New player during that month: 3.000
Churn = 25%
More on calculation churn can be found in this blog post.
DAU / WAU / MAU
A DAU is a daily active user, or a daily unique. If the same user logs in twice during the same day she is still counted as one DAU. The same principle applies to count weekly and monthly active users, just the time range changes: although a user might log in 20 times in April she is only counted once in the MAU number for that month.
New players
How many new players entered the game over the last day. It’s important to see where those players came from. If the registration process is handled by a publisher’s API it might be difficult to get that data.
Registered players
At least in the case of “The Big Catch” there are two ways when it comes to counting users: registered users and players.
Registered Users have completed the registration process. That does not make them players yet. Most registered players still have to download and install the Unity3D web player plugin and then log into the game client.
Players on the other hand have logged into the game client at least once. This is our definition of a player and used mainly for calculating lifetime values. Of course, you can define as many player categories as are relevant and fill them with players based on their retention characteristics (e.g. visitor (LT < 0), player (LT 1 – 7 days), loyal player (LT 8 – 30 days), veteran (LT > 30 days, etc.).
To answer a popular question: With “The Big Catch” we lose 22% of players after registration and before their first login into the browser based game client. Unfortunately we do not know why they decide not to login. If the Unity3D web player install screen would be more educating and inviting, the drop-out rate would probably drop below 20%.
Returning players
This is the difference between last day’s DAU and the number of new players during the same time.
Retention
Retention is commonly calculated on a monthly or weekly basis.
Monthly retention (%) = average DAU per month / MAU