Cohort Analysis Binning by Shipping instances count (Python)

Viewed 28

I have a df:

ordernum customerid     shippedate 
1            A      2021-06-21 15:01:02
2            A      2021-02-22 15:51:05
3            B      2021-08-14 06:31:01
4            B      2021-11-23 05:35:08
5            B      2021-02-19 16:56:41

I am trying to do cohort analysis by having each billing number as a period (due to the nature of the subscription content)

The resulting df/table/matrix should look something like:

                       billing # 
           1           2        3           4
cohort
01/2021   100%        42%      12%          1%
02/2021   100%        45%      15%          NA
03/2021   100%        49%      NA           NA

If its not possible to do monthly/billing cohort, I would be okay with doing billing cohorts instead of monthly

df:

 df  =   pd.DataFrame({'ordernum':[1,2,3,4,5],
                         'customerId': ['A','A','B','B','B'],
                         'shippedDate':['2021-06-21 15:01:02',
                                        '2021-02-22 15:51:05',
                                        '2021-08-14 06:31:01',
                                        '2021-11-23 05:35:08',
                                        '2021-02-19 16:56:41']},
                         index = [1,2,3,4,5])

I am able to complete the first part—but I mostly stuck on creating the billing periods and dynamically calculating them

# convert transactionDate to datetime object

df['shippedDate'] = pd.to_datetime(df['shippedDate'])

# add order month column 

df['order_month'] = df['shippedDate'].dt.to_period("M")

# define cohort

# take the first occurence of a purchase--assign cohort

df['cohort'] = df.groupby('customerId')['shippedDate'] \
                 .transform('min') \
                 .dt.to_period('M') 
0 Answers
Related