To further understand retention, it is helpful to look at retention by cohorts. A cohort is a group of customers with some shared characteristic. The characteristic can be when they signed up, or what plan they subscribed to, or how large their company is. In this case, we will group customers by the month they signed up.

Suppose we have a table with four columns, ['date', 'customer_id', 'order_num', 'order_amount_dollars'].

We are interested in calculating the monthly customer retention between the most recent month and the of the cohort for each month cohort. Rows should represent the year and month a customer group signed up and columns should be the time series of monthly retention. The resulting DataFrame could look like

Task: Write a function customer_retention_month_cohort(df) which takes a dataframe as above and returns a pandas DataFrame where the index is the year and month of the cohort and the columns are the retention from the original cohort year and month.

Note, this definition of retention is different from previous exercises, where retention was measured between the previous time period and the current time period. This retention is measured between the first time period the customer was active to the current month.

When doing this calculation, you need to be careful to consider only new customers for each cohort. The retention for times before the start of the cohort can be either 0 or nan.

Example Input

Code to generate input

df = pd.DataFrame({'date': pd.to_datetime(['2019-01-01', '2019-01-05', '2019-02-01', '2019-02-07', '2019-02-09', '2019-03-01']),
                   'customer_id': [1, 2, 1, 3, 4, 1],
                   'order_number': [709871, 709872, 709874, 709875, 709877, 709876],
                   'order_amount_dollars': [30, 20, 30, 80, 50, 30]})

Table generated

Example Output