1 2 Previous Next 15 Replies Latest reply on Nov 3, 2011 1:07 PM by Joseph Lantis

# New Players Calculation

How can I calculate the new players that appear today compare with the previous day or period?

For instance, on 10th, there are 100 players , on 11th, there might be 120 players, on 12th, there might be 80 players. All players have unique ID.

There are always overlapping players, so how can I find out how many players on 11th are new from 10th, and how many are new on 12th that their ID have never shown before.

• ###### 1. Re: New Players Calculation

Can I just get you to clarify exactly what you want to report?

Are you essentially saying that for a given day you want to know the count of players who played on that day who did not appear during the previous period (where the previous period is selected by the user - say previous day, previous week, previous month, previous <N> days or who have never appeared before at all?

I'm sure it's possible, but definitely non-triviial.  I just thought before anyone spends too much time thinking about how to do it we should get the objective crystal clear.

• ###### 2. Re: New Players Calculation

Just for fun, going from this sample data:

```Date    ID

6/10/2011    1
6/10/2011    2
6/10/2011    3
6/10/2011    4
6/10/2011    5
6/10/2011    6
6/10/2011    7
6/10/2011    8
6/10/2011    9
6/10/2011    10
6/11/2011    1
6/11/2011    2
6/11/2011    4
6/11/2011    6
6/11/2011    8
6/11/2011    9
6/11/2011    11
6/11/2011    12
6/11/2011    13
6/11/2011    14
6/11/2011    15
6/11/2011    16
6/12/2011    1
6/12/2011    2
6/12/2011    8
6/12/2011    11
6/12/2011    16
6/12/2011    17
6/12/2011    3
6/12/2011    5

```

I am guessing you are looking for a result like:

on 6/10/2011 you get 10 players

on 6/11/2011 you loose 4 players, and get 6 new never seen before players

on 6/12/2011 you loose 7 players, get 1 new never seen before player, and get 2 players seen before

And this can be seen in the attached workbook.

As Richard said, there are many ways to approach this, the attached is just two basic viewpoints, there are many other options as well, and without understanding of table calculations these are non-trivial.

• ###### 3. Re: New Players Calculation

Hi, Joe

Thank you very much!

It really helped me out!

The Bar Chart is very nice and clear, I just wondering  how can I do it in a "Text Table" format?

Other small question is how can I show the number on top of the bar like you did.

• ###### 4. Re: New Players Calculation

As the term in your Tableau file:

"In Previous"represents IDs that shown yesterday, right?

What the meaning of "New Seen Before"?

"Number Lost from Previous" on 11/06/2011 should be 4, but shows 6 in "bar per combination".

• ###### 5. Re: New Players Calculation

> "Number Lost from Previous" on 11/06/2011 should be 4, but shows 6 in "bar per combination".

You are correct, I was a little too hasty with some of the calculations, and did not pay attention to what I was doing. I have corrected my calculations.

>  how can I do it in a "Text Table" format?

Attached is a corrected version, with the additional crosstab.

> "In Previous"represents IDs that shown yesterday, right?

That is correct.

> What the meaning of "New Seen Before"?

In the sample data you will see the last two lines for IDs 3 and 5, they were in 6/10/2011, and not in 6/11/2011, so when they come back on 6/12/2011, they are new again, but have been seen before, so "New Seen Before".

> how can I show the number on top of the bar like you did.

It is a reference line, per Cell, without a line, and then formatted to center alignment.

• ###### 6. Re: New Players Calculation

Hi, Joe

I am too new to use Tableau and sorry that I failed to put your method in my work, I suppose it would be better for me to create a dummy data and have some more detailed explanations like Richard said.

By using the dummy data, I wish to find:

1.    Active payers who purchased in last two days;

2.    Payers/Active Payers;

3.    New payers and repeat Payers every day;

4.    First time purchases by level &Days since install;

5.    Repeat Purchases by Level & Days since Install;

6.    For Repeat Payers, # of Purchases histogram;

7.    Current average value of a payer;

Thank you very much for your help! Hope Richard could give some advices as well.

• ###### 7. Re: New Players Calculation

That sounds like an interesting analysis, and I am sure all of it can be done in Tableau, but you have not provided enough details for me to provide assistance. It is not clear to me what you are requesting, what the terminology you use specifically means or what business logic you want applied in order to calculate those concepts. I am sure it is very clear and straightforward to you, but I would need specific details on what everything means.

• ###### 8. Re: New Players Calculation

Hi, Joe, I wish this could help:

1. Find out how many active payers who purchased in last two days; (ID 1000002 purchased on 21st, 22nd 23rd, so 1000002 should be define as active payers on 21st, 22nd, 23rd , 24th and 25th, on 26th will be inactive payers since didn’t pay in last two days)

2. Payers/Active Payers; (number of payers over active payers on daily basis)

3. New payers (New& never seen before payers) and repeat Payers (seen before payers) every day;

4. New payers (New& never seen before payers) by level &Days since install;

5. Repeat payers (seen before payers)  by Level & Days since Install;

6. For Repeat Payers, find out the number of Purchases they made;

7. Current average value of a payer; (total amount/ total number of players (Unique ID))

• ###### 9. Re: New Players Calculation

Attached is a workbook that demonstrates these calculations.

I am still unable to understand what you mean in question 7. What is the difference between a "payer" and a "player"? If they are the same thing, then I do not understand the request, and you will need to provide additional details.

These are very interesting calculations in the attached workbook. I made use of Sets and Advanced Compute using settings frequently. If these are questions you want to answer, and your data structure is like the file you provided, you may also want to consider a data transformation process prior to Tableau to make these calculations easier.

Thank you for the interesting situations, I was also to learn a bit more about sets and table calculations by attempting your questions.

• ###### 10. Re: New Players Calculation

Thank you very much for your help!

Sorry that I am new to Tableau, what the meaning of:

IF FIRST()==0 THEN

WINDOW_SUM(SUM([Number of Records]),0,IIF(FIRST()==0,LAST(),0))

END

What is "Date Index"? and what it used for?

• ###### 11. Re: New Players Calculation

There are quite a few interesting things going on in that workbook, I could spend hours talking about all the concepts it makes use of.

```IF FIRST()==0 THEN

WINDOW_SUM(SUM([Number of Records]),0,IIF(FIRST()==0,LAST(),0))
END
```

is just about the same thing as

`WINDOW_SUM(SUM([Number of Records]))`

which is an aggregation of an aggregation. Number of Records is summed at one level, and then summed at another. The "IF FIRST()==0 THEN" is to only show one mark, and the "0,IIF(FIRST()==0,LAST(),0)" sets the window size so Tableau can efficiently produce only the one mark (this only makes a difference when dealing with many thousands of records in a partition).

Most of the interesting stuff happens in the partitioning, making use of Sets and Advanced Compute using.

As for "Date Index", that was my attempt to handle weekends, but thinking about it now, I do not think weekends are fully taken into account.

• ###### 12. Re: New Players Calculation

Hi, Problem still not solved, it seems still something wrong, table always contain overlapping text that I couldn't find the sum (total number of new players).

• ###### 13. Re: New Players Calculation

I am not sure what you are referring to. Please attach a packaged workbook that represents your situation and provide specifics on what is wrong along with what you expect to be displayed.

• ###### 14. Re: New Players Calculation

This is almost exactly what I am trying to do.  How would I modify this to show the same results but when my data may have the same ID more than once a day?  I would like to get the distinct count of IDs each day

IF FIRST()==0 THEN

WINDOW_SUM(SUM([Number of Records]),0,IIF(FIRST()==0,LAST(),0))

END