Looking at the average and median time to convert through time – in SQL

In order to look at the evolution of your average and median time to convert – from the perspective of your game, not of your users – you need to track your payers (active or not) and the time it took them to convert. In other words, for every day the game is live, you need to track the users who have converted by that point – as well as their time to convert. You can obtain that by doing something along the lines of the following query:

By using the datediff function [where datediff(‘day’, convert_date, activity_date)>=0]  you ensure you only track users who have converted by that day. As the title ages you will gradually account for more payers – and you will always account for all payers by that point, whether or not they are actually active after having converted. By using the percentile function, you can then for each activity day determine the median (or the percentile you chose) time to convert on that day. The output you will get from the above query will look something like below. Notice how the median days to convert on Jan 1st 2014 is always the same, regardless of the user considered on that day (you partitioned by activity date).

Here you see how one outlier – Melba – will make your average time to convert go up. For the 7 payers considered above, the median time to convert is 3 days (4 payers have converted by day 3 or less). If you do the average time to convert, then it’s 112.6 days. Unless you are charging by the minute (and even then…), looking at average time to convert will be misleading and reflect the age of your game more than anything else. You need the game to have been live for 768 days before a user can convert 768 days after install. That’s why the longer your game has been live, the bigger the average time to convert will be.

So, once you have the above output, you just need to aggregate everything by activity date:

Now for each day your title is active you are tracking all users who have paid by that point, and the time it took them to convert (both average and median in this case). You can most probably see the trend below in your game (if not please comment I’d love to learn more).