Use Key Performance Indicators (KPIs) to understand business metrics for the video game, Mineblocks

Common Metrics

While every business has different metrics to track their success, most are based on revenue and usage.

The metrics in this lesson are merely a starting point, and from here you'll be able to create and customize metrics to track whatever is most important to your company.

And remember, data science is exploratory! The current set of metrics can always be improved and there's usually more to any spike or dip than what immediately meets the eye.

Let's generalize what we've learned so far:

* *Key Performance Indicators* are high level health metrics for a business.
* *Daily Revenue* is the sum of money made per day. 
* *Daily Active Users* are the number of unique users seen each day
* *Daily Average Revenue Per Purchasing User (ARPPU)* is the average amount of money spent by purchasers each day.
* *Daily Average Revenue Per User (ARPU)* is the average amount of money across all users.
* *1 Day Retention* is defined as the number of players from Day N who came back to play again on Day N+1.


Common Metrics Conclusion

Now that we have our `gameplays` table joined to itself, we can start to calculate retention.

1 Day Retention is defined as the number of players who returned the next day divided by the number of original players, per day. Suppose 10 players played Mineblocks on Dec 10th. If 4 of them play on Dec 11th, the 1 day retention for Dec 10th is 40%. 

1 Day Retention 3

Nice work, you just defined ARPU for Mineblocks!

In our ARPU query, we used `using` instead of `on` in the `join` clause. This is a special case join.

```sql
from daily_revenue
  join daily_players using (dt);
```

When the columns to join have the same name in both tables you can use `using` instead of `on`. Our use of the `using` keyword is in this case equivalent to this clause:

```sql
from daily_revenue
  join daily_players on
    daily_revenue.dt = daily_players.dt;
```

ARPU 3

Now let's find out what percent of Mineblock players are returning to play the next day. This KPI is called *1 Day Retention*.

*Retention* can be defined many different ways, but we'll stick to the most basic definition. For all players on Day N, we'll consider them retained if they came back to play again on Day N+1.

This will let us track whether or not Mineblocks is getting "stickier" over time. The stickier our game, the more days players will spend in-game.

And more time in-game means more opportunities to monetize and grow our business.

1 Day Retention

We've looked at DAU and Daily Revenue in Mineblocks. Now we must understand the purchasing habits of our users.

Mineblocks, like every freemium game, has two types of users:
 * purchasers: users who have bought things in the game
 * players: users who play the game but have not yet purchased

<br/>
The next KPI we'll look at *Daily ARPPU* - Average Revenue Per Purchasing User. This metric shows if the average amount of money spent by purchasers is going up over time.

*Daily ARPPU* is defined as the sum of revenue divided by the number of purchasers per day.

Daily Average Revenue Per Purchasing User

As a data scientist, when you're not investigating spikes or dips in your data, you might be building dashboards of *KPIs*, or *key performance indicators* for a company.

KPIs are often displayed on TVs on the walls of the office, and serve as high level health metrics for the business. While every company's metrics are defined slightly differently, the basics are usually very similar.

In this lesson we'll take a look at basic KPIs like Daily Revenue, Daily Active Users, ARPU, and Retention for a video game, **Mineblocks**.

Introduction

Great! Since Mineblocks is on multiple platforms, we can calculate DAU per-platform.


Daily Active Users 2

Great! Now you're familiar with using the `with` clause to create temporary result sets.

You just built the first part of ARPU, `daily_revenue`. From here we can build the second half of ARPU in our `with` clause, `daily_players`, and use both together to create ARPU.

ARPU 2

The more popular (and difficult) cousin to Daily ARPPU is *Daily ARPU*, Average Revenue Per User. ARPU measures the average amount of money we're getting across all players, whether or not they've purchased.

ARPPU increases if purchasers are spending more money. ARPU increases if more players are choosing to purchase, even if the purchase size stays consistent.

No one metric can tell the whole story. That's why it's so helpful to have many KPIs on the same dashboard.

Daily Average Revenue Per User

Before we can calculate retention we need to get our data formatted in a way where we can determine if a user returned.

Currently the `gameplays` table is a list of when the user played, and it's not easy to see if any user came back.

By using a `self-join`, we can make multiple gameplays available on the same row of results. This will enable us to calculate retention. 

The power of `self-join` comes from joining every row to every other row. This makes it possible to compare values from two different rows in the new result set. In our case, we'll compare rows that are one date apart from each user.

1 Day Retention 2

Daily ARPU is defined as revenue divided by the number of players, per-day. To get that, we'll need to calculate the daily revenue and daily active users separately, and then join them on their dates.

One way to easily create and organize temporary results in a query is with *CTEs*, *Common Table Expressions*, also known as `with` clauses. The `with` clauses make it easy to define and use results in a more organized way than subqueries.

These clauses usually look like this:

```sql
  with {subquery_name} as (
    {subquery_body}
  )
  select ...
  from {subquery_name}
  where ...
```

Great! That query doesn't take refunds into account. We'll update the query to exclude refunds.

Fields like `refunded_at` will only have data if the transaction was refunded, and otherwise left `null`.

Daily Revenue 2

At the heart of every company is revenue, and Mineblocks is no exception. For our first KPI we'll calculate daily revenue.



Daily Revenue

Mineblocks is a game, and one of the core metrics to any game is the number of people who play each day. That KPI is called *Daily Active Users*, or *DAU*.

*DAU* is defined as the number of unique players seen in-game each day. It's important not to double count users who played multiple times, so we'll use `distinct` in our `count` function.

Likewise, Weekly Active Users (WAU) and Monthly Active Users (MAU) are in the same family.
