ebernhard_playtomic_data_analyst

Introduction

This report summarises some of the performances of Playtomic, using the dataset provided by the hiring team. In the following, we are going to cover different aspects, from generic values and KPIs to detailed modelling of the data.

Because the hiring team suggested that Playtomic uses DeepNote and SQL(BigQuery), which I have not used before, I decided to go down the path of using these tools to complete this task. I had to spend about 4-to-5 hours to set up everything (e.g. creating an account on GCP,...), read about DeepNote, and understanding how these tools interact which each others, and how I can use them for this task. Therefore, I was not able to answer in details all the questions asked for this task, but instead tried to roughly cover some that could show my skills.

The idea was to use BigQuery as a DataWarehouse, and the integration tools in DeepNote to link the data to this notebook,for then exploring the data using SQL and Python. The first task, however, was to reduce the size of the dataset since I could only store files of sizes <10MB on the cloud. Therefore, I spent an other hour on my local machine to randomly select data from the full datasets. I stress that all of the following conclusions are drawn from this limited dataset, and it is likely that the statistics will change when using the full size dataset.

General

We first look at the matches table, ordered by start_date, and we select few of the columns that are of interests for the following analysis. The 10 first entries of the table are shown below.

SELECT owner_id, start_date, end_date, sport_id, is_canceled, created_at, club_info FROM playtomic-task-340110.playtomic_ds.matches ORDER BY start_date

We note that our data starts from the first of October 2021, and spans until the 30th of January 2022, therefore covering 4 months of match booking with Playtomic.

from IPython.display import Markdown as md import pandas as pd n_matches = len(df_gen[df_gen['is_canceled'] == False]) date_start = str(df_gen['start_date'].dt.date.min()) popular_sport = df_gen['sport_id'].mode()[0] frac_matches_cancel = int(len(df_gen[(df_gen['is_canceled'] == True)])/len(df_gen)*100.) fraction_pop = int(len(df_gen[(df_gen['is_canceled'] == False) & (df_gen['sport_id'] == popular_sport)])/n_matches*100.) df_gen['date'] = df_gen['start_date'].dt.date print('Number of matches played: %i'%(n_matches)) print('Fraction of matches cancelled: %i'%(frac_matches_cancel)) print('Most popular sport: '+popular_sport) print('Fraction of matches played for the most popular sport: %i'%(fraction_pop))

Within these four month time period, there has been over 5000 matches booked via Playtomic, of which a stunning number of 4239 have been played. This corresponds to a low fraction of 19% cancellation. The most popular game is Padel, taking 66% of the booking.

The following graph shows the cumulative number of games per day since October, split in terms of played/cancelled matches in blue and orange respectively. As one can see, Playtomic is doing well, where there is no evidence of changes with times, and the number of matches played remain constant, and there is no reasons to think that this will change if no further actions are taken.

Playtomic implementation

In this section, we simply explore where Playtomic is actively implemented so far. To do this, we use the table club which contains this information. We then change Espana for Spain to avoid double counting, and display the results in a bar chart.

SELECT country, name, status FROM playtomic-task-340110.playtomic_ds.club WHERE status = 'ACTIVE'

We can clearly see that Playtomic is heavily implemented in Spain, with 5 active clubs. Italy and Denmark closely follow with 2 active clubs. Finally, Belgium totalise one active club so far. Therefore, Playtomic has a strong implementation in Spain, but some clubs start appearing in Europe.

Club Evolution

In this section, we briefly check whether the number of clubs increases with time. To do this, we use the matches table which contains the clubs. We then group the matches per months and count the number of unique clubs within each of the months. The results are displayed in a bar chart.

import pandas as pd club = [df_gen['club_info'][i][0]['club_name'] for i, club_i in enumerate(df_gen['club_info'])] df_gen['club'] = club grouped_df = df_gen.groupby(pd.Grouper(key='start_date',freq='M')) grouped_df = grouped_df.agg({'club': "nunique"}) grouped_df = grouped_df.reset_index() grouped_df['date'] = grouped_df['start_date'].dt.date

The graph above shows that the number of unique clubs has slightly increased with time, suggesting that matches are played in new clubs as time passes. It also suggests that the clubs that have offered matches for a given month, continue offering matches in the following months (i.e. no withdrawal).

We also investigate below the changes in the number of people using Playtomic to book matches and per Club. To do this, we count the number of unique "owner_id" (assuming these are unique people) grouped by month and split between clubs. The results are shown below as a line chart.

df2 = df_gen.groupby([pd.Grouper(key='start_date',freq='M'),'club']) df2 = df2.agg({'owner_id': "nunique"}) df2 = df2.reset_index() df2['date'] = df2['start_date'].dt.date df2

The figure above shows that Padel Box is doing very well with a net overall increase in the number of players with time. Some clubs like Club El Padelito and Padel Madrid show decreases in the number of unique players, suggesting that some stop booking their matches with Playtomic. Overall, there is no evidence for strong increase or decrease in the number of players with time for most clubs.

Testing player fidelity

In this section, we are going to test player fidelity. To do this, I investigated how the cumulative number of (not cancelled) games increases with time for each of the players that have booked at least more than 1 game. If a player books a lot of matches, and like it so much that they book more and more games, the curve is expected to follow a power function with a>1 (where a is the exponent). Instead, if Playtomic looses fidelity, and players use Playtomic less and less, the curve is expected to follow a power function with 0<a<1, and flattens with time since players stop playing matches via Playtomic.

To calculate an average trend, I fit a power function model with free a. The results are shown below.

from datetime import datetime import numpy as np df3 = df_gen unique_owner = df3['owner_id'].unique() date_format_str = '%Y-%m-%d %H:%M:%S' elapsed_time_per_owner = [] for owner_i in unique_owner: df_i = df3[df3['owner_id'] == owner_i] date_time = [datetime.strptime(str(pd.to_datetime(df_i['start_date'].values[i])), date_format_str) for i in range(len(df_i))] diff_in_days = [(date_time[i+1] - date_time[i]).total_seconds() / 3600. /24. for i in range(len(date_time)-1)] if len(diff_in_days) >= 2: diff_in_days.insert(0,0.) elapsed_time_per_owner.append(diff_in_days)

import matplotlib.pyplot as plt from scipy.optimize import curve_fit from matplotlib.lines import Line2D def function(x, root): return x**(1./root) popt = 0 for i in range(len(elapsed_time_per_owner)): popt_i, _ = curve_fit(function, np.cumsum(elapsed_time_per_owner[i]), np.arange(len(elapsed_time_per_owner[i]))+1) popt += popt_i plt.plot(np.cumsum(elapsed_time_per_owner[i]), np.arange(len(elapsed_time_per_owner[i]))+1, 'k-', alpha = 0.3, lw = 0.2) root = popt/float(i) x = np.arange(1, 120,0.01) plt.plot(x, function(x, root), 'r--', label = 'Average owner fidelity') handles, labels = plt.gca().get_legend_handles_labels() line = Line2D([0], [0], label='Unique owner ID', color='k') handles.extend([line]) plt.legend(handles=handles) plt.xlabel('Elapsed days between games') plt.ylabel('Cummulative number of games') plt.yscale('log') # plt.xlim(0., 30.) plt.show()

The figure above shows the cumulative number of games played by each of the players from their first booking. The general trend is that it flattens, suggesting that there is more elapsed time between matches played for each unique owner, and less matches are played overall. By fitting the average trend I find that a = 1/3, and the critical time in days is between 7-to-14 days, where Playtomic is significantly losing players fidelity.

Finally, although Playtomic records a stunning number of games booked, some of them get cancelled. I now investigate the reasons for this cancellations. To do this, we will join the table matches and matches_history and look at the cancel_reason columns. I stress that most of the cancellation have no reasons, and we only focus here on those for which a reason has been reported. The results are shown with a bar graph, split in terms of sports, where we can see that the main reason is a cancellation by the owner.

SELECT tab_1._id, tab_1.sport_id, tab_1.is_canceled, tab_2.cancel_reason FROM playtomic-task-340110.playtomic_ds.matches tab_1 INNER JOIN playtomic-task-340110.playtomic_ds.match_history tab_2 ON tab_1._id = tab_2.match_id WHERE tab_2.cancel_reason != 'None'