Source code for various requests at https://www.reddit.com/r/WorldofTanksConsole/comments/6mp76o/kamakazis_datamining_adventure_part_2_lets/

battlesbytank.sql

select sum(battles), stats.tank_id, short_name, tier, is_premium from stats, tanks where tanks.tank_id=stats.tank_id group by stats.tank_id;

graph xbox players.ipynb

List of plots and data.rst

Data

Data used for this session can be downloaded from: https://drive.google.com/open?id=0B1qZrP_GY8KXeHh4WjZCSmdIQkE

Plots

A combined listing of graphs that can be had at Plot.ly

• Playerbase growth
• Account Lifespan
• Tank ownership
• Battles vs account age (large!)
• Battles per tank
• Estimated revenue by (in)active players

revenue.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# import numpy as np\n",
    "# import pandas as pd\n",
    "# # File size is too large. Need more than 32GB of RAM to work with this!\n",
    "# data = pd.read_csv('../data/playerbattleinfo xbox.csv', low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sqlite3 as sql\n",
    "from datetime import datetime\n",
    "from collections import defaultdict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "database = sql.connect('../data/playerStats xbox.sqlite')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "conn = database.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "premium_tanks = {}\n",
    "for tank_id, tank_name, price in conn.execute('select tank_id, short_name, price_gold from tanks where is_premium=1').fetchall():\n",
    "    premium_tanks[tank_id] = {'name': tank_name, 'price': price, 'owners': set()}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for tank in premium_tanks.keys():\n",
    "    for player in conn.execute('select account_id from stats where tank_id=?', (tank,)).fetchall():\n",
    "        premium_tanks[tank]['owners'].add(player[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "players = dict()\n",
    "already = set()\n",
    "for tank in premium_tanks.keys():\n",
    "    for player in premium_tanks[tank]['owners']:\n",
    "        if player not in already:\n",
    "            players[player] = datetime.utcfromtimestamp(conn.execute('select last_battle_time_raw from players where account_id=?', (player,)).fetchone()[0])\n",
    "            already.add(player)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "now = datetime(2017, 7, 8)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Labeling players as (in)active\n",
    "\n",
    "My standard is that an \"inactive\" player is one who has not had a battle in over 180 days. While others may define \"active\" as 7 days, 14 days, 30 days, etc. there are many who can be on break for the summer or are pursuing other games. I believe that 180 days is an agreeable timeframe that doesn't create some fear of impending doom or other nonsense"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "active180 = set()\n",
    "inactive180 = set()\n",
    "for player, battle in players.iteritems():\n",
    "    if (now - battle).days <= 180:\n",
    "        active180.add(player)\n",
    "    else:\n",
    "        inactive180.add(player)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "percentages180 = {\n",
    "    'active': defaultdict(int),\n",
    "    'inactive': defaultdict(int)\n",
    "}\n",
    "for player in active180:\n",
    "    for tank in premium_tanks.keys():\n",
    "        if player in premium_tanks[tank]['owners']:\n",
    "            percentages180['active'][tank] += 1\n",
    "            \n",
    "for player in inactive180:\n",
    "    for tank in premium_tanks.keys():\n",
    "        if player in premium_tanks[tank]['owners']:\n",
    "            percentages180['inactive'][tank] += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "active_revenue180 = 0\n",
    "for tank in premium_tanks.keys():\n",
    "    active_revenue180 += percentages180['active'][tank] * (premium_tanks[tank]['price'] / 250.0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "inactive_revenue180 = 0\n",
    "for tank in premium_tanks.keys():\n",
    "    inactive_revenue180 += percentages180['inactive'][tank] * (premium_tanks[tank]['price'] / 250.0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print active_revenue180\n",
    "print inactive_revenue180"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with open('output/activerevenue180.csv', 'w') as f:\n",
    "    f.write('Tank,Gold,Qty. owned by active players,Qty. owned by inactive players,Revenue by active,Revenue by inactive,Total tank revenue,\"Estimated revenue = ${:,.2f}\"'.format(active_revenue180 + inactive_revenue180) + '\\n')\n",
    "    for tank in premium_tanks.keys():\n",
    "        f.write('\"{}\",{},{},{},\"${:,.2f}\",\"${:,.2f}\",\"${:,.2f}\"\\n'.format(\n",
    "            premium_tanks[tank]['name'].encode('utf-8'),\n",
    "            premium_tanks[tank]['price'],\n",
    "            percentages180['active'][tank],\n",
    "            percentages180['inactive'][tank],\n",
    "            percentages180['active'][tank] * (premium_tanks[tank]['price'] / 250.0),\n",
    "            percentages180['inactive'][tank] * (premium_tanks[tank]['price'] / 250.0),\n",
    "            percentages180['active'][tank] * (premium_tanks[tank]['price'] / 250.0) + percentages180['inactive'][tank] * (premium_tanks[tank]['price'] / 250.0)\n",
    "        ))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Changing players to 90 days instead of 180\n",
    "\n",
    "(For fun)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "active90 = set()\n",
    "inactive90 = set()\n",
    "for player, battle in players.iteritems():\n",
    "    if (now - battle).days <= 90:\n",
    "        active90.add(player)\n",
    "    else:\n",
    "        inactive90.add(player)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "percentages90 = {\n",
    "    'active': defaultdict(int),\n",
    "    'inactive': defaultdict(int)\n",
    "}\n",
    "for player in active90:\n",
    "    for tank in premium_tanks.keys():\n",
    "        if player in premium_tanks[tank]['owners']:\n",
    "            percentages90['active'][tank] += 1\n",
    "            \n",
    "for player in inactive90:\n",
    "    for tank in premium_tanks.keys():\n",
    "        if player in premium_tanks[tank]['owners']:\n",
    "            percentages90['inactive'][tank] += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "active_revenue90 = 0\n",
    "for tank in premium_tanks.keys():\n",
    "    active_revenue90 += percentages90['active'][tank] * (premium_tanks[tank]['price'] / 250.0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "inactive_revenue90 = 0\n",
    "for tank in premium_tanks.keys():\n",
    "    inactive_revenue90 += percentages90['inactive'][tank] * (premium_tanks[tank]['price'] / 250.0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "35662667.78\n",
      "15874165.288\n"
     ]
    }
   ],
   "source": [
    "print active_revenue90\n",
    "print inactive_revenue90"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with open('output/activerevenue90days.csv', 'w') as f:\n",
    "    f.write('Tank,Gold,Qty. owned by active players,Qty. owned by inactive players,Revenue by active,Revenue by inactive,Total tank revenue,\"Estimated revenue = ${:,.2f}\"'.format(active_revenue90 + inactive_revenue90) + '\\n')\n",
    "    for tank in premium_tanks.keys():\n",
    "        f.write('\"{}\",{},{},{},\"${:,.2f}\",\"${:,.2f}\",\"${:,.2f}\"\\n'.format(\n",
    "            premium_tanks[tank]['name'].encode('utf-8'),\n",
    "            premium_tanks[tank]['price'],\n",
    "            percentages90['active'][tank],\n",
    "            percentages90['inactive'][tank],\n",
    "            percentages90['active'][tank] * (premium_tanks[tank]['price'] / 250.0),\n",
    "            percentages90['inactive'][tank] * (premium_tanks[tank]['price'] / 250.0),\n",
    "            percentages90['active'][tank] * (premium_tanks[tank]['price'] / 250.0) + percentages90['inactive'][tank] * (premium_tanks[tank]['price'] / 250.0)\n",
    "        ))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}