summary of converted and churned customers from time series dataframe

user_3314190 0
Viewed 134

I have similar to the below dataframe and I would like to create a few summary stats around the behaviour of customers over time 

pd.DataFrame([
['id1','23/5/2019','not_emailed']
,['id1','24/5/2019','not_emailed']
,['id1','25/5/2019','emailed']
,['id1','26/5/2019','emailed']
,['id1','27/5/2019','emailed']
,['id1','28/5/2019','emailed']
,['id1','29/5/2019','emailed']
,['id1','30/5/2019','emailed']
,['id1','31/5/2019','emailed']
,['id1','1/6/2019','emailed']
,['id1','2/6/2019','emailed']

,['id2','23/5/2019','not_emailed']
,['id2','24/5/2019','not_emailed']
,['id2','25/5/2019','emailed']
,['id2','26/5/2019','emailed']
,['id2','27/5/2019','emailed']

,['id3','29/5/2019','not_emailed']
,['id3','30/5/2019','emailed']
,['id3','31/5/2019','emailed']
,['id3','1/6/2019','emailed']
,['id3','2/6/2019','emailed']

 ,['id4','29/5/2019','not_emailed']
 ,['id4','30/5/2019','emailed']
 ,['id4','31/5/2019','emailed']
 ,['id4','1/6/2019','emailed']
 ,['id4','2/6/2019','emailed']    
,['id4','2/7/2019','emailed']    
,['id4','3/7/2019','emailed']
,['id4','4/7/2019','emailed'] 
 ],columns=['id','date','status'])

The main scenarios that could be observed in this data set are:

id1 emailed on 25th but not converted

id2 emailed on 27th and converted on 28th because we dont see any more logs for this id

id3 emailed on 30th and converted on 3rd because we dont see any more logs for this id

id4 emailed on 30th and converted on 3rd but churned againon the 2nd

I would like to get a summary of that information per day

How many emailed, how many converted, how many churned that had previously converted

A desired potential output could be:

        pd.DataFrame([
        ['29/5/2019',10,3,1] ,
        ['30/5/2019',10,2,1] 
        ],columns=['date','emailed_total','converted_total','churned_total']
        )

Not that numbers above are random and don't reflect the stats of the first dataset shared

My approaches so far:

1) partially solves the problem: find first day of emailed calculate days passed since first group by the elapsed days and aggregate works but not for churn customers

2) loop through dates filter out unique ids emailed loop through dates in the future and calculate the differences between sets does the job but not very clean and pythonic

pandas-groupby pandas summary time-series analytics
2 Answers

I have written the code to answer your question as I understand it at the moment. But as I commented, the status regarding churning has been fluffed up, so there are only two different totals. It is not done. The name of the column is not the name you want either.

df['date'] = pd.to_datetime(df['date'], format='%d/%m/%Y')
df2 = df.groupby(['date','status']).agg('count').unstack().fillna(0)
df2.columns = df2.columns.droplevel()
df2 = df2.rename_axis(columns=None).reset_index()
df2.sort_index(ascending=True, inplace=True)

df
    date    emailed     not_emailed
0   2019-05-23  0.0     2.0
1   2019-05-24  0.0     2.0
2   2019-05-25  2.0     0.0
3   2019-05-26  2.0     0.0
4   2019-05-27  2.0     0.0
5   2019-05-28  1.0     0.0
6   2019-05-29  1.0     2.0
7   2019-05-30  3.0     0.0
8   2019-05-31  3.0     0.0
9   2019-06-01  3.0     0.0
10  2019-06-02  3.0     0.0
11  2019-07-02  1.0     0.0
12  2019-07-03  1.0     0.0
13  2019-07-04  1.0     0.0
user_131078040

As per your request i made some insight about your data, time to conversion and unconverted id time. I hope it helps

df['date']=pd.to_datetime(df['date'],infer_datetime_format=True)
df.sort_values(by='date',inplace=True)
dates=df['date'].unique()
ids=df['id'].unique()
df=df.set_index(['id','date'])

out=pd.DataFrame(index=dates)
for i, new_df in df.groupby(level=0):
    new_df=new_df.droplevel(0)
    new_df=new_df.rename(columns={'status':i})
    out=out.merge(new_df, how='outer', left_index=True, right_index=True)
    

not_converted=out[out.columns[out.iloc[-1,:]=='emailed']]
converted=out[out.columns[out.iloc[-1,:].isnull()]]

start_mailing_date_NC=(not_converted=='emailed').cumsum().idxmin() #not converted id metrics

delta_NC=(dates[-1]-start_mailing_date_NC) #dates[-1] could be changed to actual date
print("Days from first mail unconverted by id: ")
print(delta_NC.to_string())
print(' Mean Days not converted: %s'%(delta_NC.mean()))
print( '\n')



start_mailing_date=(converted=='emailed').cumsum().idxmin() #converted id metrics
conversion_mailing_date=(converted=='emailed').cumsum().idxmax()#converted id metrics
delta=(conversion_mailing_date-start_mailing_date)
print("Days to conversion by id: ")
print(delta.to_string())
print(' Mean Days to conversion: %s'%(delta.mean()))

output:

Days from first mail unconverted by id: 
id4   42 days
 Mean Days not converted: 42 days 00:00:00


Days to conversion by id: 
id1   10 days
id2    4 days
id3   10 days
 Mean Days to conversion: 8 days 00:00:00
user_125759750
Related