Revenue Metrics Analysis

Based on data obtained from a company operating in the gaming industry, this study aims to analyze the company’s cash flows.

In this context, the files games_paid_users.csv, which contains information about registered users, and games_payment.csv, which contains information about users' payments, have been provided. The columns and their contents in the files are given below.

Sampla Data Structure

1st Data Set: games_paid_users.csv Open in GitHub

Column Name Description
user_id Unique user ids
game_name Names of the games (ie. game1, game2, game3)
language Which country is the user from?
has_older_device_model Is the user on an old device?
age What is the user's age?

2nd Data Set: games_payments.csv Open in GitHub

Column Name Description
user_id Unique user ids
game_name Names of the games (ie. game1, game2, game3)
payment_date User's payment date
revenue_amount_usd User's revenue amount

Upon examining the obtained data, it has been decided to calculate and analyze the following metrics.

Metrics to be Used

  1. Monthly Recurring Revenue (MRR): The amount of revenue generated in a calendar month, but only from sources that recur every month.
  2. Paid Users: The total number of users who have paid.
  3. Average Revenue Per Paid User (ARPPU): The average revenue generated per paying user.
  4. New Paid Users: The number of users who started paying during the specified period (calendarmonth).
  5. New MRR: The MRR generated by newly acquired paying users in a given month.
  6. Churned Users: The number of users who stopped paying during the specified period.
  7. Churn Rate: The ratio of churned users during a specific period to the number of paid users in the previous period (calendar month).
  8. Churned Revenue: The total revenue lost from users who churned, based on their payments from the previous period.
  9. Revenue Churn Rate: The percentage of revenue lost due to churned users in a given month, relative to the total revenue from those users in the previous month.
  10. Expansion MRR: The increase in MRR from one month to the next, generated by existing users who started paying more.
  11. Contraction MRR: The decrease in MRR from one month to the next, due to existing users paying less than they did previously.

The study was conducted in two main phases. The first phase involved cleaning and organizing the data in a Python environment to calculate the determined metrics. The second phase involved the calculation of the metrics, which was carried out in three sub-phases. First, calculations were performed in an SQL environment. Second, calculations were conducted in a Python environment. Finally, using Tableau, the necessary calculations were made, and the results were visualized.


Data Organization

In this section, the data from the obtained CSV files has been processed to be organized according to the determined metrics. This work was carried out in Python and SQL environments, and the queries used in both environments have been shared separately.

Data preparation with Python Data Preparation with SQL


Calculation of Metrics

In this section, the calculation of the metrics we determined has been performed. The previously defined metrics were calculated in three different environments (Python, SQL, and Tableau), and the calculation methods are shared separately below.

SQL Analysis Python Analysis Tableau Analysis