Gaming analytics for Crank - an incremental game - on BigQuery

3 min readMar 17, 2016

I’m addicted to incremental games and data visualization - and when I asked some incremental game developers for their data, /u/FaeDine was awesome enough to share their stats with all of us.

This is the data for Crank (an incremental game) on BigQuery:

Table: CrankStats (>2 million rows, 1.18 GB)

A series of “snapshots” of the current game progress. These show progress over time, tracking the realtime and gametime (in seconds) progress. Most of these stats are showing values like the amount of resources someone has over time, the amount of times things have been researched, and so on.

/table/fh-bigquery:public_dump.crankstats

Table: CrankCombat (>3 million rows, 421 MB)

Similar to CrankStats, but focusing on combat and in shorter intervals. Shows the health of the current player/enemy, and the outcome of the fight.

/table/fh-bigquery:public_dump.crankcombat

Table: CrankTimes

Has a unique GameID (generated whenever a new game is made) and tracks when (in seconds) they reach certain milestones (Discovering or Repairing items).

/table/bqpipeline:imports.cranktimes

Sample queries

Combat stats: Here we can see 8,427 games, with the top getting 470 (hours?) played, and other stats.

SELECT GameID, MAX(GameTime) gametime, MIN(GameTime) first_combat_gametime, MAX(RealTime) last_combat, MAX(PlayerHP) max_hp, COUNT(*) combats
FROM [fh-bigquery:public_dump.crankcombat] 
GROUP BY 1 
ORDER BY 2 DESC

Game stats: We can see 18,736 games, with the average game taking 40 minutes, and the top one getting 53 hours of “cranking” (you’ll need to play to understand).

SELECT INTEGER(AVG(TimeCranked)) avg_time_cranked,  INTEGER(MAX(TimeCranked)) max_time_cranked, MAX(PowerGeneratedCrank) max_power_generated_crank, COUNT(DISTINCT GameID, 20000) games
FROM [fh-bigquery:public_dump.crankstats]

Goals

Can we find out what keeps gamers engaged? Where they struggle?

Bonus: Most popular incremental games?

As a bonus query, I searched the reddit comments on BigQuery (thanks Jason)to find how many mentions Cookie Clicker, Crank, Critter Mound and MatterOfScale had on /r/incremental_games during 2015. Query and results:

SELECT LEFT(STRING(SEC_TO_TIMESTAMP(created_utc)), 7) month, SUM(LOWER(body) CONTAINS 'cookie') cookie, SUM(LOWER(body) CONTAINS 'crank') crank, SUM(LOWER(body) CONTAINS 'critter') critter,
SUM(LOWER(body) CONTAINS 'astarsearcher') astarsearcher
FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'table_id CONTAINS "2015_" OR table_id CONTAINS "2016_"')
WHERE subreddit='incremental_games'
GROUP BY 1
ORDER BY 1