title: Cumulative Customer Retention
body: <p>I have a table of customers (cust_id), period &amp; subscription. The ask is to calculate customer retention in a cumulative manner from one period to the other paying attention to customer subscription (sub) when it is 1-active sub or 0-cancelled.</p>
  <p>()Period 1 has 1,2,3 unique customers &amp; period 2 has 1,2,4 unique customers, two customers came from period 1 into period 2 and so the retention rate is 2/3 = 0.6667. The denominator been a count of customer (1,2,3) from period 1.
  ()Period 2 has 1,2,4 and Period 3 has 3,4,5 but customer 1 has stopped their subscription so we are not necessarily expecting to see 1 and also there is customer 3 from period 1 so that retention rate is 1/3 = 0.3333. The denominator been a count of customer (2,4) from period 2 &amp; customer 3 from period 1.
  ()Period 3 has 3,4,5 and period 4 has 2,3,6 but customer 2 still has their subscription on, so cumulative customer retention is 1/4 = 0.25. The denominator been a count of customer (3,4,5) from period 3 &amp; customer 2 from period 1 who was also in period 2.</p>
  <p><strong>Table</strong></p>
  <pre><code>customer_table &lt;- structure(list(cust_id = c(1, 2, 3, 1, 4, 2, 2, 1, 5, 3, 3, 4, 
                             3, 2, 2, 6, 5, 4, 2, 4), period = c(1, 1, 1, 1, 2, 2, 2, 2, 
                                                                  3, 3, 3, 3, 4, 4, 4, 4, 5, 5, 5, 5), Subscription = c(1, 1, 1, 1, 1, 1, 1, 0, 1, 1, 1, 1, 0, 1, 1, 1, 1, 1, 1, 1)), .Names = c(&quot;cust_id&quot;, &quot;period&quot;, &quot;Subscription&quot;
                                                                 ), row.names = c(NA, -20L), class = &quot;data.frame&quot;)
  
    cust_id period Subscription
  1        1      1            1
  2        2      1            1
  3        3      1            1
  4        1      1            1
  5        4      2            1
  6        2      2            1
  7        2      2            1
  8        1      2            0
  9        5      3            1
  10       3      3            1
  11       3      3            1
  12       4      3            1
  13       3      4            0
  14       2      4            1
  15       2      4            1
  16       6      4            1
  17       5      5            1
  18       4      5            1
  19       2      5            1
  20       4      5            1
  </code></pre>
  <p>Code used lacks the cumulative element and also not sure if the exclusion of cancelled subscription has been applied correctly</p>
  <pre><code>library(dplyr)
  customer_table %&gt;% 
    group_by(period) %&gt;% 
   summarise(retention=length(intersect(cust_id,customer_table$cust_id[customer_table$period==(period+1)]))/n_distinct(cust_id[customer_table$Subscription==1])) %&gt;% 
    mutate(retention=lag(retention))
  </code></pre>
  <p>Wrong output</p>
  <pre><code>  period retention
     &lt;dbl&gt;     &lt;dbl&gt;
  1      1     NA   
  2      2      0.5 
  3      3      0.25
  4      4      0.25
  5      5      0.25
  </code></pre>
  <p>Required output</p>
  <pre><code>  period retention
     &lt;dbl&gt;     &lt;dbl&gt;
  1      1     NA   
  2      2      0.666 
  3      3      0.33
  4      4      0.25
  5      5      0.33
  </code></pre>