Skip to content

Latest commit

 

History

History
243 lines (131 loc) · 9.85 KB

guide-customer-retention-analysis-sql.md

File metadata and controls

243 lines (131 loc) · 9.85 KB
Raw

雿輻 SQL 餈�摰Ｘ��������

��嚗https://www.kdnuggets.com/2017/12/guide-customer-retention-analysis-sql.html](https://www.kdnuggets.com/2017/12/guide-customer-retention-analysis-sql.html)

c 霂捏

�� Luba Belokon嚗Statsbot](https://statsbot.co/)

�捏雿���桅�������∟��臬頨思����啣恥�瑞������芣��其�隞砍�甈∟揚銋唳��甇�������銝��寧���蝘唬蛹����嚗�隞砌蝙�函��寞��臬恥�瑞�摮�����臬蔣��亦��喲��銋���摰Ｘ����雿嚗�撠������嗅�梯晶�刻��銝��

�賑��銝葵霂曄��刻�

1. 靚瑟�蝵�摰霂髡 - 敹恍���舐�蝏��刻�銝�瘨胯��

2. 靚瑟��唳��銝�霂髡 - ��雿��唳������

3. 靚瑟� IT �舀�銝�霂髡 - �舀�雿�蝏��� IT

�嚗����賡�� SQL ��桀�隞交迤蝖桃��孵�餈�霈∠�嚗銋���摮�銋����游捆��餈���銝哨��賑撠�郊�紡雿�雿�銵�祉�摰Ｘ����嚗�雿����園�蝘餃遣蝡恥�瑞�摮��啁�銝�恥�瑞����脩瑪嚗誑��雿銝�蝢支�銝剛恣蝞�摮����

�箇�摰Ｘ���脩瑪

摰Ｘ���脩瑪撖嫣遙雿���閫�恥�瑞�銝�喳��嚗�撠��拐�閫���嗡�鈭嚗�憒��格�格��仿�瘣餃�蔣���隞祆�航��恥�瑚�銝銋�喲鈭���撘�銋停�航秩嚗恥�瑕蝚砌�甈∟挪�桀��臬隡��乒��誑���亦��漲��

�遣摰Ｘ���脩瑪�洵銝�甇交霂�典����挪�桐�銝�恥�瘀�餈��妍銋蛹 p1����摨血�霂交����撟嗡���憸��挪�桅����

銝�蝐餃����∪笆摰Ｘ��霈輻�������嚗�

  • 銝�摰嗅��∪��航�瘥霈輸銝�甈∠�憸�憸���

  • 頞��航隡��湧���瘥�2�冽�銝�銝芣���

�其誑銝內靘葉嚗�撠蝙�其�銝芣�嚗僎�挽�賑�刻�撖�2016撟�1�挪�桃�摰Ｘ�冽銝銝�撟渡������

憒���餈堆�蝚砌�甇交霂��摰Ｘ瘙�

January_pool AS

(                
                SELECT DISTINCT cust_id

                FROM            dataset

                WHERE           month(transaction_date)=1

                AND             year(transaction_date)=2016)

�嗅�嚗�隞祈�撖�鈭恥�琿����園�蝘餌�銵蛹嚗�憒�隞賑�典銝�撟游漲��銝芣�餈���憭�嚗�

SELECT *Year*(transaction_date),
       *Month*(transaction_date),
       count (distinct cust_id) AS number

FROM dataset

WHERE year(transaction_date)=2016

AND cust_id IN january_pool

GROUP BY 1,

         2

甇��雿�閫����� SELECT �賣鋡怠��怠蝚砌�甇乩葉��

憒��賑�其��遢��1000��孵恥�瘀��賑�臭誑憸��賑�����航��瑞�嚗�

蝏��曉�蝐颱撮鈭��瘀�

�唳�航��蝙�阬Statsbot](https://statsbot.co/solutions/product-analytics?utm_source=blog&utm_medium=article&utm_campaign=retention)

摰Ｘ靽�����

銝膩�捆�曄�芣蝚砌�甇伐��蛹�賑餈���恥�瑚���行�隞颱�頞嚗�賑�臬�典�敺憟踝�

�迨嚗�隞砍�賭���銝芣瘜��其��遢�亥挪�恥�瑚葉嚗�憭�鈭箏鈭�隞質����其��遢�亥挪�恥�瑚葉嚗�憭�鈭箏銝�隞質���隞亙��嗡�銝�銝芣�����

�迨嚗�隞祇�閬遣蝡�銝芾翮隞�芋��餈隞仿���葵蝞���甇仿炊摰������賑��閬�撱箔�銝芾”�潘�霈啣�瘥葵�冽���挪�格��蛛�撟嗅�霈貉�鈭挪�桀�質楊頞�銝芸僑隞踝��蛹餈�霈輸�航���冽�隞祉�銝撘�憪��亙��遙雿�氬���刻���霈曉�憪�2000撟湛�雿��臭誑�寞��閬��氬��

Visit_log AS

SELECT cust_id,

       datediff(month, ��2000��01��01��, transaction_date) AS visit_month

FROM dataset

GROUP BY 1,

         2

ORDER BY 1,

         2

餈�蝏�隞砌�銝芰掩隡潔�餈���橘�

�嗅�嚗�隞祇�閬��唳��鈭縑�荔�隞亦＆摰�甈∟挪�桐��渡��園�湧���甇歹�撖嫣�瘥葵鈭箏�瘥葵���亦�銝活霈輸��氬��

Time_lapse AS

    SELECT cust_id,

           visit_month lead(visit_month, 1) over (partition BY cust_id ORDER BY cust_id, visit_month)

    FROM visit_log

�乩��伐��賑��閬恣蝞挪�桐��渡��園�湧�嚗�

Time_diff_calculated AS

    SELECT cust_id,

           visit_month,

           lead,

           lead �� visit_month AS time_diff

    FROM time_lapse

�啣嚗悟�賑�▽銝�銝恥�瑚�����瘚���摰對�摰��挾�園嚗 撱嗉�嚗�餈��恥�瑟�靘��甇歹��賑閬��瘥��冽�銝芣�霈輸�恥�瑟��餈�摰Ｘ�其�銝芣�餈�����隞祈���閬�銋鈭�挾�園����摰Ｘ嚗誑�鈭��其�餈��恥�瑯�蛹鈭��啗�銝��對��賑��閬�桐�隞祉�霈輸璅∪�撖孵恥�瑁�銵�蝐颯��

Custs_categorized AS

SELECT cust_id,

       visit_month,

       CASE

             WHEN time_diff=1 THEN �etained��,

             WHEN time_diff>1 THEN �agger��,

             WHEN time_diff IS NULL THEN �ost��

       END AS cust_type

FROM time_diff_calculated

餈�雿踵�隞砍����甇亥憭�霈∪�冽�銝芣�霈輸�恥�瑟��隞亙��嗡葉憭�鈭箏銝葵�����

SELECT visit_month,

       count(cust_id where cust_type=�etained��)/count(cust_id) AS retention

FROM custs_categorized

GROUP BY 1

餈蛹�賑��鈭�銝芣�餈�摰Ｘ��靘��

�唳�航��蝙�阬Statsbot](https://statsbot.co/solutions/product-analytics?utm_source=blog&utm_medium=article&utm_campaign=retention)

�游��詨霂�

��蝏���

摰Ｘ靽���撠蛹隞颱�銝��憓溶瘛勗漲嚗僎�捂�喟���頦芯�隞祉���蝑�����蛛�隞亙��典恥�瑚�撉�Ｙ�銵函����恥�瑟瓷��憭湛������閬餈��唳�航�其�鈭批�韐券���摰Ｘ�蝟颯�����噶鈭蝠�暹�霈唳迨蝐駁憸��

蝞���[�Ｗ毀繚韐�摮(https://www.linkedin.com/in/luba-belokon-35078512b/) �� Statsbot ��摰嫣�摰嗚��

���歇�瑁捂�航蓮頧賬��

�詨嚗�

�賑��銝�霂曄��刻�

1. 靚瑟�蝵�摰霂髡 - 敹恍���亦�蝏��刻�銝�瘨胯��

2. 靚瑟��唳��銝�霂髡 - ���函��唳��瘞游像

3. 靚瑟� IT �舀�銝�霂髡 - �舀��函�蝏��� IT ��瘙�

�游��詨霂�