کدام بهتر است Pandas یا SQL (مورد مطالعه: بازگشت مشتری)

برای تحلیل داده ها ابزارهای مختلفی استفاده میشود. pandas یکی از ماژول های محبوب در محیط پایتون برای تحلیل داده های به شکل جدول است و انواع عملیات برای تمیزسازی، جابجایی و تغییر ساختار داده ها را پشتیبانی میکند. اما در مواردی که داده در پایگاه داده ذخیره شده اند مساله متفاوت است. در تحلیل داده های کسب وکاری که نیاز به واکشی داده از پایگاه داده وجود دارد، اگر بخواهیم از پایتون برای تحلیل داده ها استفاده کنیم. دو راه پیش رو داریم. راه اول: ابتدا داده های خام را از منبع داده استخراج کنیم و از ابزارهایی نظیر pandas برای تمیزسازی و تحلیل استفاده کنیم. راه دوم: به کمک ابزارهای مناسب نظیر SQL داده ها را تاجای ممکن خالص سازی کنیم و تنها بخش نهایی مورد نیاز را واکشی کنیم. در این مقاله این دو راه حل را با هم مقایسه میکنیم و نقطه قوت و ضعف هر کدام را مقایسه میکنیم. برای بررسی مساله تحلیل داده های مشتریان و بازگشت مشتری (customer retention) از این دو راه حل کمک میگیریم.

بازگشت مشتری: یکی از توانایی های کلیدی هر کسب وکار برای حفظ مشتری و تبدیل آن به مشتری دایمی و جلوگیری از جذب او توسط رقباست. این موضوع تابعی از میزان رضایت مندی مشتری در رفع نیازهایش توسط محصولات کسب و کار بوده و از عوامل اصلی موفقیت کسب و کار به شمار میرود. چرا که راضی کردن و جذب مجدد مشتری فعلی به مراتب برای کسب و کار کم هزینه تر است.

داده ها مربوط به یک کسب وکار جواهرفروشی انلاین بوده و شامل 113303 رکورد و ستون های زیر است.

روش مورد استفاده ما برای حل این مساله، حالت ساده ای از بازگشت مشتری است. به این صورت که مشتری بعد از هر خرید، اگر در همان ماه جاری یا ماه بعد از آن مجددا خرید کرد به عنوان مشتری با بازگشت (retention) محسوب شده و در صورتی که بیش از آن وقفه در خرید داشته باشد مثلا تا خرید بعدی سه ماه فاصله بیفتد، به عنوان مشتری دارای تاخیر (delay) در خرید به شمار میرود. دسته دیگر مورد بررسی ما مشتریان جدید و بدون سابقه خرید قبلی هستند. بر اساس هر دو روش داده های مورد نیاز را به دست اورده و در نهایت نمودار تعداد مشتریان قرار گرفته در هر دسته را به شکل نمودار حطی رسم میکنیم.

راه اول:

در این روش برای به دست اوردن مشتریان در دسته های retention ، delay و new و داده های نهایی به طور کامل از کوئری نویسی SQL استفاده میکنیم.

(دستور timeit%% در jupyter notebook برای سنجش زمان لازم برای اجرای کد)

%%timeit
import pandas as pd   
import pyodbc 
# Some other example server values are
# server = 'localhost\sqlexpress' # for a named instance
# server = 'myserver,port' # to specify an alternate port
server = '*' 
database = '*' 
username = '*' 
password = '*' 
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
query=r&quot&quot&quot
#ابتدا داده های سال 2019 را انتخاب میکنیم
with orders as 
(SELECT month([event_time]) as month_num,[user_id]
FROM [jewelry_clean_v2] )
,orders_label as
# فاصله بین خریدهای هر مشتری را حساب میکنیم برچسب ها را مطابق مقادیر گفته شده اختصاص میدهیم 
# labels =  retention , delay , new
    (select [user_id],month_num,return_month,return_month-month_num as gap,case when return_month-month_num=1 or return_month-month_num=0 then 'retention' when return_month-month_num>1 then 'delayed' else null end as label from
    (select [user_id],month_num,lead(month_num,1) 
    over(partition by [user_id] order by month_num) as return_month from orders) as sub)
#در نهایت داده های حاوی مشتریان حدید و قدیمی را با هم ادغام میکنیم
select sub.month_num,count(sub.[user_id]) as count,'new' as label from (select min(month_num) as month_num,[user_id] from orders_label group by [user_id]) as sub group by sub.month_num 
union select return_month,count(distinct [user_id]),label from orders_label group by return_month,label    &quot&quot&quot
df=pd.read_sql(query,cnxn)

در نهایت داده های مطابق زیر خواهیم داشت. به عنوان نمونه برای ماه دوم (February) 2305 مشتری جدید داشتیم و 920 مشتری خود را تکرار کردند (خرید قبلیشان در همان ماه یا ماه قبلی بوده است).

از نظر عملکرد زمانی این کد با 0.12 ثانیه اجرا شد.

output:

10 loops, best of 3: 124 ms per loop

راه دوم:

به عنوان راه دوم فراخوانی داده های خام اولیه را با یک کوئری ساده SQL انجام داده و سایر عملیات روی داده ها را با راه حل اول مقایسه میکنیم.

%%timeit
import pandas as pd
import pyodbc 
server = '*' 
atabase = '*' 
username = '*' 
password = '*' 
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
query=r&quot&quot&quotselect event_time,user_id from [jewelry_clean_v2]&quot&quot&quot
df_pandas=pd.read_sql(query,cnxn)

فقط مقادیر مورد نیاز را از جدول فراخوانی کردیم و داخل متغیر df_pandas ذخیره کردیم و سایر مراحل توسط ماژول pandas انجام شده.

df_pandas['event_time']=df_pandas['event_time'].apply(lambda x:x.month)
df_pandas.sort_values(by=['user_id','event_time'],ascending=True,inplace=True)
df_pandas['shift']=df_pandas.groupby(['user_id'])['event_time'].shift(-1)
df_pandas['label']= (df_pandas['shift'] - df_pandas['event_time'])
df_pandas['label']=df_pandas['label'].apply(lambda x:'retention' if (x==0)|(x==1) else ('delay' if x>1 else None))
df_pandas.sort_values(['user_id','event_time'],inplace=True)
new_users=df_pandas[['event_time','user_id']].groupby('user_id').min().reset_index()
new_users=new_users.groupby('event_time').count().reset_index()
df_pandas=df_pandas[~df_pandas.duplicated()]
df_pandas=df_pandas[['shift','label','user_id']].groupby(['shift','label']).count().reset_index()
df_pandas.columns=['month','label','count']
new_users['label']='new'
new_users.columns=['month','count','label']
df_pandas=df_pandas.append(new_users,ignore_index=True)

همچنین از نظر عملکرد زمانی این کد در 0.7 ثانیه به اجرا درامد

output:

1 loop, best of 3: 702 ms per loop

از دیدگاه فنی:

نوشتن کوئری SQL پیچیده بوده و فهم آن مشکل تر از ماژول pandas به نظر میرسد. فیلتر داده ها در هر بار و انتخاب فیلتر مجدد روی فیلتر قبلی باعث افزایش پیچیدگی کوئری میشود. اما pandas توابع بسیار متنوعی دارد که بسیاری از عملیات روی داده ها را نسبت به کوئری نویسی ساده میکند هرچند از نظر عملکردی با توجه به نتایج و اختلاف حدود 7 برابری نتایج به دست امده مشخص میشود اجرای کوئری SQL تا جای ممکن و تنها خواندن داده های آماده جهت تحلیل از پایگاه داده، مزیت بسیاری دارد. در این مساله زمان و سخت افزار قابل توجهی اشغال نشد اما در حجم داده های بالا و در موارد حساس به زمان پاسخ گویی مثل مواردی که در تعامل با کاربر قرار داریم قطعا کوئری SQL مزیت زیادی نسبت به pandas دارد. گرچه برای پردازش داده های حجیم ماژول های دیگری هم توسعه داده شده است و بسته به شرایط باید تکنولوژی و ابزار مناسب استفاده شود.

از دیدگاه کسب وکار:

تعداد مشتریانی که در هر ماه خرید کردند و دسته های مربوطه به شکل زیر است. بیشترین نعداد مشتری در ماه سوم برای خرید اول جذب شده است ولی روند جذب مشتری تا ماه ششم نزولی شده است. ضمن اینکه روند تکرار خرید تقریبا روندی ثابت داشته است. تکرار خرید بستگی زیادی به نوع محصول دارد. مثلا برای کالاهای مصرفی تکرار با نرخ بالاتر اتفاق میفتد و احتمال خرید مشتری در صورت ایجاد وقفه کمتر میشود چرا که با احتمال بالایی مشتری جذب رقبا شده است. همچنین مدل های توزیع و فروش عامل مهم دیگر در تکرار خرید بوده و محصولات دارای حق اشتراک با نرخ نسبتا ثابت و دفعات مشخصی تکرار میشوند که باید در تحلیل مورد توجه قرار بگیرند. نکته دیگر اهمیت داده ها و نمودارهای تکمیلی نظیر تعداد خریدها یا تعداد مشتریان از دست رفته است که میتواند در تفسیر نتایج موثر باشد