⽤户复购周期计算
⽤户复购周期（两次购买之间的时间间隔）
⼀、⾸先使⽤SQL进⾏计算
注：⽤户在⼀天中发⽣多次购买则只记为1次购买。
1.根据⽤户id与购买⽇期进⾏分组，将⼀天内发⽣多次消费记录进⾏合并。
DROP TABLE member_Repurchase_cycle_01;
CREATE TABLE member_Repurchase_cycle_01
AS
SELECT * FROM member_Repurchase_cycle_all# member_Repurchase_cycle_all是全部数据表
GROUP BY memberid,DATE(createdatetime)
ORDER BY memberid,createdatetime
2. 选出具有复购⾏为的⽤户id（复购指的是具有多天消费记录）
DROP TABLE Repurchase_cycle_memberid;
CREATE TABLE Repurchase_cycle_memberid
AS
groupby是什么函数SELECT memberid ,COUNT(memberid) FROM member_Repurchase_cycle_01
GROUP BY memberid
HAVING COUNT(memberid)>1
ORDER BY memberid
3.复购⽤户记录，包括消费时间
DROP TABLE member_Repurchase_cycle_duble;
CREATE TABLE member_Repurchase_cycle_duble
AS
SELECT * FROM member_Repurchase_cycle_01
WHERE memberid IN(SELECT memberid FROM Repurchase_cycle_memberid)
4.复购⽤户两次相邻消费之间时间间隔
DROP TABLE member_Repurchase_cycle_priod;
CREATE TABLE member_Repurchase_cycle_priod
AS
SELECT memberid,createdatetime,
atedatetime,(SELECT atedatetime) FROM member_Repurchase_cycle_duble b WHERE
atedatetime&atedatetime)) AS diff
FROM member_Repurchase_cycle_duble a
ORDER BY memberid,createdatetime
5.⽤户第⼀次消费与第⼆次消费之间的时间间隔（第⼀次指的是第⼀天，第⼆次指的是第⼆天）
DROP TABLE member_Repurchase_cycle_priod_fir_sec;
CREATE TABLE member_Repurchase_cycle_priod_fir_sec
AS
SELECT * FROM member_Repurchase_cycle_priod AS a WHERE (SELECT COUNT(*) FROM
member_Repurchase_cycle_priod
WHERE berid AND createdatetime<=a.createdatetime)<=2
⼆、使⽤python进⾏处理
1.加载数据包
import pandas as pd
import numpy as np
from pandas import DataFrame,Series
2.加载excel
ad_excel('D:/FBS/member_repurchase_cycle_duble.xlsx')
3.使⽤python的group函数和shift函数进⾏操作，通过shift函数⾥⾯的值来控制向前还是向后偏移, 缺少的值会填充upby函数⾥的参数控制基于什么字段进⾏shift.
shift['time_diff']=upby('memberid')['createdatetime'].shift(1)
4.对偏移后的时间做减法
shift['diff']=pd.to_datetime(shift['createdatetime'])-pd.to_datetime(shift['time_diff']) 5.⾃定义⼀个函数，让结果看起来更直观
def y(x):
try:
return int(x.days)+int(x.seconds)/3600/24
except:
return x
shift['diff_s']=shift['diff'].apply(y)
结果如图所⽰：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。