Chaos Tomb: Visualizing Gameplay with D3 and SQL
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.
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:
Players died 22% of the time in the first level. This percentage includes times when players re-entered the first level after playing other levels. I would prefer that the number were a bit lower.
Drowning is more deadly than the monsters. In most levels with lava or water hazards, more players die by drowning than at the monsters’ hands. The one level where this is not true has a gauntlet of monsters between the entrance and the water hazard.
Overall, the death rate seems a bit high.
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.
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.
Minutes spent in game
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.
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' 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' 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.
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.
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
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
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.
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.
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%.
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.
/* 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 );