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')