<jupyter_start><jupyter_text>MineRaftLet's look at the hypothetical freemium game MineRaft and derive some basic key performance indicators (KPIs) for the business.We'll be looking at the following KPIs:- Daily Revenue- Daily Active Users- Daily Average Revenue Per Purchasing User (ARPPU)- Daily Average Revenue Per User (ARPU)- 1 Day Retention  Data StructureMineRaft structures its data in a database called `game_metrics.db` which holds two tables: `purchases` and `gameplays`.The schema for each table is:**purchases**| Column | Description || :---: | :--: || id | The id of the purchase || user_id | The id of the user || price | The price of the purchase || refunded_at | The date if the purchase was refunded || created_at | The date the purchase was created |**gameplays**| Column | Description || :---: | :--: || id | The id of the play session || user_id | The id of the user || created_at | The date of the play session || platform | What platform the user was on | In order to do our reports, we'll first set up the database connection.<jupyter_code>import sqlite3

# connection to db
con = sqlite3.connect('game_metrics.db')
# cursor for commands
cur = con.cursor()<jupyter_output><empty_output><jupyter_text>Daily RevenueLet's first look at daily revenue. This is how much MineRaft makes across all players per day. Notice that we select out refunds from the query.Note: For all of the 'Daily' metrics, I will only be displaying the first week as an easy to read table due to their length.<jupyter_code>cur.execute("""
select
	date(created_at) as date,
	ROUND(SUM(price), 2) as daily_revenue
from purchases
where refunded_at is null
group by 1
order by 1;

""")
print(cur.fetchall())<jupyter_output>[('2015-08-04', 41.5), ('2015-08-05', 53.0), ('2015-08-06', 24.5), ('2015-08-07', 51.0), ('2015-08-08', 40.5), ('2015-08-09', 14.0), ('2015-08-10', 30.0), ('2015-08-11', 27.0), ('2015-08-12', 64.0), ('2015-08-13', 34.0), ('2015-08-14', 40.5), ('2015-08-15', 41.0), ('2015-08-16', 54.5), ('2015-08-17', 49.5), ('2015-08-18', 53.0), ('2015-08-19', 63.0), ('2015-08-20', 23.5), ('2015-08-21', 31.5), ('2015-08-22', 35.0), ('2015-08-23', 43.5), ('2015-08-24', 45.5), ('2015-08-25', 52.5), ('2015-08-26', 31.0), ('2015-08-27', 29.0), ('2015-08-28', 43.0), ('2015-08-29', 42.5), ('2015-08-30', 42.0), ('2015-08-31', 33.5), ('2015-09-01', 24.5), ('2015-09-02', 26.5), ('2015-09-03', 43.5), ('2015-09-04', 41.5), ('2015-09-05', 46.5), ('2015-09-06', 41.5), ('2015-09-07', 50.0), ('2015-09-08', 48.5), ('2015-09-09', 28.0), ('2015-09-10', 21.5), ('2015-09-11', 31.0), ('2015-09-12', 53.0), ('2015-09-13', 47.0), ('2015-09-14', 51.5), ('2015-09-15', 52.0), ('2015-09-16', 34.5), ('2015-09-17', 52.0), ('2015-09[...]<jupyter_text>**date**|**daily\_revenue**:-----:|:-----:2015-08-04|41.52015-08-05|53.02015-08-06|24.52015-08-07|51.02015-08-08|40.52015-08-09|14.02015-08-10|30.0  Daily Active Users (DAU)Our Daily Active Users segmented by platform and date.DAU is the number of unique players seen in-game per day.We could also calculate Weekly Active Users (WAU) and Monthly Active Users (MAU).<jupyter_code>cur.execute("""
select
	date(created_at) as date,
	platform,
	count(distinct user_id) as dau
from gameplays
group by 1, 2
order by 1, 2;
""")
print(cur.fetchall())<jupyter_output>[('2015-08-04', 'Android', 27), ('2015-08-04', 'Web', 42), ('2015-08-04', 'iOS', 36), ('2015-08-05', 'Android', 51), ('2015-08-05', 'Web', 37), ('2015-08-05', 'iOS', 43), ('2015-08-06', 'Android', 34), ('2015-08-06', 'Web', 44), ('2015-08-06', 'iOS', 35), ('2015-08-07', 'Android', 45), ('2015-08-07', 'Web', 33), ('2015-08-07', 'iOS', 32), ('2015-08-08', 'Android', 39), ('2015-08-08', 'Web', 43), ('2015-08-08', 'iOS', 25), ('2015-08-09', 'Android', 55), ('2015-08-09', 'Web', 44), ('2015-08-09', 'iOS', 37), ('2015-08-10', 'Android', 38), ('2015-08-10', 'Web', 34), ('2015-08-10', 'iOS', 43), ('2015-08-11', 'Android', 40), ('2015-08-11', 'Web', 37), ('2015-08-11', 'iOS', 47), ('2015-08-12', 'Android', 40), ('2015-08-12', 'Web', 34), ('2015-08-12', 'iOS', 31), ('2015-08-13', 'Android', 36), ('2015-08-13', 'Web', 27), ('2015-08-13', 'iOS', 42), ('2015-08-14', 'Android', 48), ('2015-08-14', 'Web', 33), ('2015-08-14', 'iOS', 31), ('2015-08-15', 'Android', 48), ('2015-08-15', 'Web', 44), ('2015[...]<jupyter_text>**date**|**platform**|**dau**:-----:|:-----:|:-----:2015-08-04|Android|272015-08-04|Web|422015-08-04|iOS|362015-08-05|Android|512015-08-05|Web|372015-08-05|iOS|432015-08-06|Android|342015-08-06|Web|442015-08-06|iOS|352015-08-07|Android|452015-08-07|Web|332015-08-07|iOS|322015-08-08|Android|392015-08-08|Web|432015-08-08|iOS|252015-08-09|Android|552015-08-09|Web|442015-08-09|iOS|372015-08-10|Android|382015-08-10|Web|342015-08-10|iOS|43  Daily Average Revenue Per Purchasing User (ARPPU)The Daily ARPPU is the sum of revenue divided by the number of purchasers, per day.MineRaft has two types of players:- `purchasers`: users who have bought things in the game- `players`: users who play but have not yet bought anythingBy looking at the Daily ARPPU, we can look to see if the money spent by purchases is increasing over time.<jupyter_code>cur.execute("""
select
	date(created_at) as date,
	round(100 * sum(price)/ count(), 2) as arppu
from purchases
where refunded_at is null
group by 1
order by 1;
""")
print(cur.fetchall())<jupyter_output>[('2015-08-04', 319.23), ('2015-08-05', 265.0), ('2015-08-06', 144.12), ('2015-08-07', 231.82), ('2015-08-08', 238.24), ('2015-08-09', 175.0), ('2015-08-10', 300.0), ('2015-08-11', 225.0), ('2015-08-12', 266.67), ('2015-08-13', 283.33), ('2015-08-14', 270.0), ('2015-08-15', 256.25), ('2015-08-16', 259.52), ('2015-08-17', 235.71), ('2015-08-18', 265.0), ('2015-08-19', 262.5), ('2015-08-20', 261.11), ('2015-08-21', 242.31), ('2015-08-22', 250.0), ('2015-08-23', 290.0), ('2015-08-24', 267.65), ('2015-08-25', 228.26), ('2015-08-26', 221.43), ('2015-08-27', 181.25), ('2015-08-28', 238.89), ('2015-08-29', 223.68), ('2015-08-30', 300.0), ('2015-08-31', 223.33), ('2015-09-01', 222.73), ('2015-09-02', 203.85), ('2015-09-03', 290.0), ('2015-09-04', 276.67), ('2015-09-05', 244.74), ('2015-09-06', 276.67), ('2015-09-07', 227.27), ('2015-09-08', 255.26), ('2015-09-09', 200.0), ('2015-09-10', 238.89), ('2015-09-11', 193.75), ('2015-09-12', 331.25), ('2015-09-13', 293.75), ('2015-09-14', 271.05), ('2[...]<jupyter_text>**date**|**arppu**:-----:|:-----:2015-08-04|319.232015-08-05|265.02015-08-06|144.122015-08-07|231.822015-08-08|238.242015-08-09|175.02015-08-10|300.0  Daily Average Revenue Per User (ARPU)The daily average revenue per user is the amount of money across all players, whether or not they've purchased.It increases if either `purchasers` spend more money and/or if more `players` choose to purchase.Since the ARPU is defined as revenue over daily players, it represents how much the company is making per player, per day.<jupyter_code>cur.execute("""
with daily_revenue as (
  select
    date(created_at) as dt,
    round(sum(price), 2) as rev
  from purchases
  where refunded_at is null
  group by 1
),
daily_players as (
  select
    date(created_at) as dt,
    count(distinct user_id) as players
  from gameplays
  group by 1
)
select
  daily_revenue.dt as date,
  round((daily_revenue.rev / daily_players.players),2) as arpu
from daily_revenue
  join daily_players using (dt);
""")
print(cur.fetchall())<jupyter_output>[('2015-08-04', 0.42), ('2015-08-05', 0.45), ('2015-08-06', 0.23), ('2015-08-07', 0.49), ('2015-08-08', 0.4), ('2015-08-09', 0.11), ('2015-08-10', 0.29), ('2015-08-11', 0.23), ('2015-08-12', 0.67), ('2015-08-13', 0.35), ('2015-08-14', 0.39), ('2015-08-15', 0.36), ('2015-08-16', 0.5), ('2015-08-17', 0.46), ('2015-08-18', 0.47), ('2015-08-19', 0.57), ('2015-08-20', 0.2), ('2015-08-21', 0.28), ('2015-08-22', 0.3), ('2015-08-23', 0.46), ('2015-08-24', 0.42), ('2015-08-25', 0.44), ('2015-08-26', 0.3), ('2015-08-27', 0.25), ('2015-08-28', 0.38), ('2015-08-29', 0.37), ('2015-08-30', 0.39), ('2015-08-31', 0.29), ('2015-09-01', 0.22), ('2015-09-02', 0.22), ('2015-09-03', 0.41), ('2015-09-04', 0.42), ('2015-09-05', 0.4), ('2015-09-06', 0.38), ('2015-09-07', 0.43), ('2015-09-08', 0.5), ('2015-09-09', 0.26), ('2015-09-10', 0.22), ('2015-09-11', 0.25), ('2015-09-12', 0.52), ('2015-09-13', 0.42), ('2015-09-14', 0.46), ('2015-09-15', 0.53), ('2015-09-16', 0.36), ('2015-09-17', 0.44), ('2015-09-18', 0[...]<jupyter_text>**date**|**arpu**:-----:|:-----:2015-08-04|0.422015-08-05|0.452015-08-06|0.232015-08-07|0.492015-08-08|0.42015-08-09|0.112015-08-10|0.29  1 Day RetentionFinally, we can determine what percent of MineRaft players are returning the following day.For all players on Day N, we'll consider them retained if they played again on Day N+1.This is helpful because it lets us know if users are continuing to play as our game changes over time.<jupyter_code>cur.execute("""
select
  date(g1.created_at) as date,
  round(100 * count(distinct g2.user_id) /
    count(distinct g1.user_id)) as retention
from gameplays as g1
  left join gameplays as g2 on
    g1.user_id = g2.user_id
    and date(g1.created_at) = date(datetime(g2.created_at, '-1 day'))
group by 1
order by 1;
""")
print(cur.fetchall())<jupyter_output>[('2015-08-04', 23.0), ('2015-08-05', 25.0), ('2015-08-06', 22.0), ('2015-08-07', 22.0), ('2015-08-08', 19.0), ('2015-08-09', 20.0), ('2015-08-10', 24.0), ('2015-08-11', 16.0), ('2015-08-12', 18.0), ('2015-08-13', 19.0), ('2015-08-14', 24.0), ('2015-08-15', 26.0), ('2015-08-16', 23.0), ('2015-08-17', 18.0), ('2015-08-18', 21.0), ('2015-08-19', 23.0), ('2015-08-20', 26.0), ('2015-08-21', 24.0), ('2015-08-22', 20.0), ('2015-08-23', 24.0), ('2015-08-24', 21.0), ('2015-08-25', 19.0), ('2015-08-26', 23.0), ('2015-08-27', 20.0), ('2015-08-28', 29.0), ('2015-08-29', 14.0), ('2015-08-30', 28.0), ('2015-08-31', 20.0), ('2015-09-01', 32.0), ('2015-09-02', 17.0), ('2015-09-03', 16.0), ('2015-09-04', 27.0), ('2015-09-05', 24.0), ('2015-09-06', 20.0), ('2015-09-07', 18.0), ('2015-09-08', 28.0), ('2015-09-09', 22.0), ('2015-09-10', 19.0), ('2015-09-11', 15.0), ('2015-09-12', 27.0), ('2015-09-13', 21.0), ('2015-09-14', 22.0), ('2015-09-15', 11.0), ('2015-09-16', 20.0), ('2015-09-17', 21.0), ('2015-09[...]
{'fraction_non_alphanumeric': 0.26083553468349474, 'fraction_numerical': 0.28138146952353, 'mean_word_length': 4.167846309403438, 'pattern_counts': {'":': 0, '<': 20, '<?xml version=': 0, '>': 20, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '516112', 'license': 'MIT', 'n_tokens_mistral': 6063, 'n_tokens_neox': 4146, 'n_words': 1026, 'path': 'Data Analysis/Business Analytics/KPIs/MineRaft/mineraft.ipynb', 'repo_name': 'DrakeOpensource/Portfolio', 'size': '10221'}