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

    New Players Calculation

    Zyz Iwi

      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
          Richard Leeke

          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
            Joe Mako

            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
              Zyz Iwi

              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
                Zyz Iwi

                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
                  Joe Mako

                  > "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
                    Zyz Iwi

                    Hi, Joe

                    Really appreciate for your help!

                    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
                      Joe Mako

                      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
                        Zyz Iwi

                        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
                          Joe Mako

                          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
                            Zyz Iwi

                            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
                              Joe Mako

                              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
                                Zyz Iwi

                                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
                                  Joe Mako

                                  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
                                    Joseph Lantis

                                    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

                                     

                                    Adding the compute using:

                                    Results are computed along ATTENDEE_NUM (sorted ascending by Distinct count of ATTENDEE_NUM) for each DATE.

                                    does not seem to provide the correct values.

                                     

                                    Thanks,

                                    1 2 Previous Next