2 Replies Latest reply on Nov 13, 2011 11:27 PM by Per Strid

    Life Time Value Matrix

    Per Strid

      I have a databas table that contains life time values for our paying members. The format is fairly straight forward, first a date then a seed date. After that the number of paying members and last the amount they spent. A collegue has built a lifetime value model in Excel that looks reasonable. But it would be a lot more easy to maintain and access from Tableau.

       

      Data looks like this:

      Date;Seed Date;Users;Amount

      1/1/2008;1/1/2008;34526;37465

      1/2/2008;1/1/2008;32267;34764

      1/3/2008;1/1/2008;29645;30245

      1/3/2008;1/1/2008;27987;29345

      .

      .

      .

      Interpretation is that when the Seed Date and the Date are the same there are 100% new paying members and as the date increments the Users are the ones out of the observed at the first date that has showed any transactions. Data is monthly.

      Above this means that in January 2008 we got 34526 new paying members who spent 37465 Euro that first month. Out of these 32267 had transactions in February 2008 spending 34764 Euro that month and so on.

       

      Reading the data in Tableau and cross tab on Date and Seed Date is easy and gives a "staired" table as expected, where the diagonal shows new paying members or spent Euros. that is Dates on the rows, Seed Date on the column shelf and sum(Users) or sum(Euro) as text.

       

      First issue is to compute the percentage of users that remain month by month. Difference from the previous period is easy and compared with first instance for the first column is easy too. But doing it for the whole table?

       

      This is the formula I use. But since row one becomes NULL in the second column, rows one and two in the third and so on these columns become NULL.

      ZN(SUM([Gross Sales (EUR)])) / LOOKUP(ZN(SUM([Gross Sales (EUR)])), FIRST())

       

      Is there a way to increment FIRST() as the column changes?

       

      Best regards

      Per