Chaos Tomb: Visualizing Gameplay with D3 and SQL

, Ludum Dare, Chaos Tomb, Analytics

Contents

Chaos Tomb is a little too hard. How do I know that?

I’ve watched some people play, and I’ve asked questions, but that takes a fair bit of time. To get a broader picture of how people play the game, you can look at the analytics data. The game also stores a little bit of information about each player and each game, so the status messages can be grouped into games and players. This also allows me to filter out my games from the analytics data.

With the help of PostgreSQL and D3.js, here is the analysis of the Chaos Tomb analytics data.

If you are interested in the JavaScript code which generates these charts, you can view map.js and charts.js.

Level map

SQL
select level,
       sum(leveltime) as totaltime,
       count(distinct playerid) as playercount,
       count(case
             when statustype='drown' then 1
             else null
             end) as status_drown,
       count(case
             when statustype='monster' then 1
             else null
             end) as status_monster,
       count(case
             when statustype='door' then 1
             else null
             end) as status_door
from ct_status
     natural left join ct_game
group by level;

Here is what I gleaned from the level data:

Game activity

This is the chart of the number of levels completed each hour. Early on, I would play several other games at a time, and I'd get a short spike in activity while my game was on the front page of the “play and rate” page. The big spike in activity on April 25 is from the post-mortem I posted, and it’s followed by a spike in activity from a comment on Hacker News.

Game activityBar chart showing the amount of activity in the game over time.
SQL
select count(1),
       date_trunc('hour', statustime) as hour
from ct_player
     natural right join ct_game
     natural right join ct_status
group by hour
order by hour;

The usual advice is that you should play other games in order to get other people to play yours. However, this effect wears off, and to keep getting people to play your game, you need to do a little marketing. A post-mortem with some inline videos (or GIFs) works well.

Player stats

Minutes spent in game

Amount of time playedChart showing the number of players who played at least a certain number of minutes
SQL
select sum(leveltime)
from ct_game
     natural right join ct_status
group by playerid;

The median game time is about 2½ minutes, but a handful of people played for 20 minutes or more. The game does have some appeal, but it is limited.

Levels played

Number of levels playedBar chart showing the number of players who played at least a certain number of levels
SQL
select count(distinct level)
from ct_game
     natural right join ct_status
group by playerid;

Half of the players stopped after playing four levels. All that time spent on level design went to waste!

Chests opened

Number of chests openedBar chart showing the number of players who opened at least a centain number of chests
SQL
/*
 * 'chests' is a bitfield, so we want to count the number of ones.
 * Get the maximum number of chests that each player ever opened.
 */
select max(length(replace(cast(chests as text), '0', '')))
from ct_game
     natural right join ct_status
group by playerid;

Here we get to see the real problem with the difficulty: ideally, this chart should more closely match the number of levels played. As I was designing levels, I kept thinking of clever ways to put treasure chests in challenging locations. That was what made the game interesting, after all. But making the chests hard to reach made the rest of the game that much more difficult, because the players were under-equipped.

Weapons acquired

Number of weapons acquiredBar chart showing the number of players who acquired at least a certain number of weapons
SQL
/*
 * 'weapons' is a bitfield, so we want to count the number of ones.
 * Get the maximum number of weapons that each player ever had.
 */
select max(length(replace(cast(weapons as text), '0', '')))
from ct_game
     natural right join ct_status
group by playerid;

More damning evidence about treasure chest accessibility. Since the weapon locations are randomized, players should ideally get at least two weapons without trouble, since there is a decent chance that one of the weapons they pick up is worthless. One of the six weapons is completely powerless, and most of the others are challenging to use.

Hearts acquired

Number of hearts acquiredBar chart showing the number of players who acquired at least a certain number of hearts
SQL
select max(hearts)
from ct_game
     natural right join ct_status
group by playerid;

And here is the most damning result of all. Only about a third of the players ever got another heart, everyone else was stuck with the two hearts (four hit points) they started with. To me, that’s the best way to play the game: with only two hearts, the game is fun and challenging. Again, it would be better if this curve followed the “levels played” curve.

Interesting tidbit: only one person ever got 100% of the hearts in the game. If you want spoilers, you can see the hidden heart chest in the map at the top of the page.

Double-jump acquired

Whether double-jump was acquiredBar chart showing how many players did or did not acquire the double jump
SQL
select bool_or(djump)
from ct_game
     natural right join ct_status
group by playerid;

The double-jump powerup is critical for completing about a third of the game. Finding it requires a little bit of exploration, and getting it requires going through a gauntlet of four monsters with no way around.

So, players who got this far were definitely able to experience the game as intended.

Number of deaths

Number of levels playedBar chart showing how many players died at least a certain number of times
SQL
select count(case
             when statustype='monster' or statustype='drown' then 1
             else null
             end) as deathcount
from ct_game
     natural right join ct_status
group by playerid;

This is conclusive proof that some people must like the game. Why else would you keep playing after dying 25 times or more?

Exited the cave

Whether the cave was exitedBar chart showing the number of players which did or did not exit the cave
SQL
select bool_or(statustype = 'end')
from ct_game
     natural right join ct_status
group by playerid;

The exit to the cave is right at the start of the game. I had hoped that most people would be curious enough to just try exiting.

IPv6 adoption

IPv6 adoption rateBar chart showing number of players accessing the game via IPv6 and IPv4
SQL
select family(clientaddress) as family,
       count(1)
from (select distinct playerid
      from ct_game
      natural right join ct_status) as t1
      natural right join ct_player
group by family;

A significant number of players accessed the game using IPv6, although IPv4 is still quite dominant.

Conclusions

When I made the game, I was thinking only about the obsessive gamers. I wanted it to appeal to people who love action platformers. But these charts drive home the difficulty curve problems. Even if 100% completion is still just as hard as ever, it should be easier to get to 50%.

SQL schema

Here is the complete schema for the SQL analytics database. There's a lot of untapped data in here, like per-chest statistics for the treasure, or correlations between which weapons the players have and how well they do in game.

SQL
/* Unique players */
create table ct_player (
    playerid serial not null,
    /* Random string for identifying the player */
    playerkey text,
    /* The HTTP referrer for when the player first arrived */
    referrer text,
    /* The player's user agent string */
    useragent text,
    /* The first IP address the player used */
    clientaddress inet not null,
    constraint ct_player_pkey
        primary key (playerid)
);

create unique index ct_player_key on ct_player (playerkey);

/* List of games */
create table ct_game (
    gameid serial not null,
    /* Random string for identifying the game */
    gamekey text,
    playerid integer not null,
    /* Time when the game was started */
    starttime timestamp with time zone not null default now(),
    constraint ct_game_pkey
        primary key(gameid),
    constraint ct_game_playerid
        foreign key (playerid) references ct_player
        on delete cascade
        on update cascade
);

create unique index ct_game_key on ct_game (gamekey);

/* Reasons why a status is being recorded */
create type ct_statustype as enum (
    'drown',   /* Player drowned in water or lava */
    'monster', /* Monster killed the player */
    'door',    /* Player went through a door to another level */
    'end'      /* Player left the cave, ending the game */
);

/* Snapshot of a game when a level ended */
create table ct_status (
    gameid integer not null,
    statustime timestamp with time zone not null default now(),
    /* Reason why the level ended (e.g. died or exited through door) */
    statustype ct_statustype not null,
    /* Name of the level */
    level text not null,
    /* Number of level transitions so far this game */
    levelindex int not null,
    /* Amount of time spent in this level */
    leveltime interval not null,
    /* Bitmask for which weapons the player has */
    weapons bit(6) not null,
    /* Whether the player has the double-jump powerup */
    djump boolean not null,
    /* Bitmask for which chests have been opened */
    chests bit(13) not null,
    /* Whether the player has cheated during this game */
    cheat boolean not null,
    /* Number of times the player has saved the game */
    savecount int not null,
    /* Number of times the player has loaded the game */
    loadcount int not null,
    /* Current player health */
    health int not null,
    /* Number of hearts acquired */
    hearts int not null,
    constraint ct_status_gameid
        foreign key (gameid) references ct_game
        on delete cascade
        on update cascade
);