基于每月活動的客戶生命周期狀態分析

您好，我的公司希望更好地跟蹤有多少用戶活躍在我們的平臺上。我們使用 Microsoft SQL Server 2019 作為資料庫，連接到 Azure Data Studio。

下面是我們資料庫中的兩個表 DDL：

1. 日歷表

2. 收入分析

下面兩個表的螢屏截圖：

日歷表

收入分析表

長話短說，“活躍”是指該會員在當月至少進行了一次真錢投注。

每個月，一個成員都有特定的生命周期型別。此狀態將根據他們之前和當前月份的活動每月更改一次。狀態如下：

我們最初希望獲得包含以下列的視圖：

會員編號 | CALENDAR_YEAR_MONT | MEMBER_LIFECYCLE_STATUS | LAPSED_MONTHS

此外，該視圖應顯示每個成員每月一行，從他們第一次下真錢賭注的月份開始。這個視圖應該給出他們那個月的生命周期狀態，如果成員已經失效，它應該顯示自他們上次活躍以來的月數的滾動計數。

到目前為止，我已經提出以下 CTE 來為我提供觀點基礎。但是我不確定 UNRETAINED 和 REACTIVATED 列。有什么想法嗎？

with all_activities as (
select a.member_id, activity_date, calendar_month_number as month_activity, calendar_year as year_activity, 
datepart(month,CURRENT_TIMESTAMP) as current_month, datepart(year,CURRENT_TIMESTAMP) as current_year,
datepart(month,CONVERT(DATE, DATEADD(DAY,-DAY(GETDATE()),GETDATE()))) as previous_month, datepart(year,CONVERT(DATE, DATEADD(DAY,-DAY(GETDATE()),GETDATE()))) as year_last_month,
a.NUMBER_OF_WAGERS, (case when datepart(month,CURRENT_TIMESTAMP) = calendar_month_number and datepart(year,CURRENT_TIMESTAMP) = calendar_year then 'active' else 'inactive' end) as status,
case when (case when datepart(month,CURRENT_TIMESTAMP) = calendar_month_number and datepart(year,CURRENT_TIMESTAMP) = calendar_year then 'active' else 'inactive' end) = 'active' and number_of_wagers = 1 then 'New' 
when (LAG((case when datepart(month,CURRENT_TIMESTAMP) = calendar_month_number and datepart(year,CURRENT_TIMESTAMP) = calendar_year then 'active' else 'inactive' end) ,1,0) OVER(PARTITION BY member_id ORDER BY calendar_month_number desc) = 'active' and calendar_month_number = datepart(month,CONVERT(DATE, DATEADD(DAY,-DAY(GETDATE()),GETDATE())))) then 'Retained' 
when (calendar_month_number = datepart(month,CURRENT_TIMESTAMP) and year_activity = datepart(year,CURRENT_TIMESTAMP) and calendar_month_number = datepart(month,CONVERT(DATE, DATEADD(DAY,-DAY(GETDATE()),GETDATE())))) then 'Unretained' 
from [dbo].[REVENUE_ANALYSIS] a
join CALENDAR b on a.ACTIVITY_DATE= b.CALENDAR_DATE
)
select * from all_activities

uj5u.com熱心網友回復：

這是關于客戶生命周期狀態分析，這需要做幾件事：

1. 客戶獲取日期（最好存盤此日期，因為有些客戶可能會追溯到幾年或幾十年）。對于這個問題，我們假設revenue_analysis有我們需要的一切并計算user acquisition month
2. lapsedvs churned：churned客戶通常被定義為在一段時間內沒有活動。對于這個問題，我們沒有定義，因此，用戶將被報告為lapsed永遠。
3. 對于生命周期狀態計算，我們將收集以下資訊（member_id、calendar_month、acquisition_month、activity_month、prior_activity_month），以便計算最終結果。

with cte_new_user_monthly as (
select member_id,
       min(activity_year_month) as acquisition_month
  from revenue_analysis
 group by 1),
cte_user_monthly as (
select u.member_id,
       u.acquisition_month,
       m.yyyymm as calendar_month
  from cte_new_user_monthly u,
       calendar_month m
 where u.acquisition_month <= m.yyyymm),
cte_user_activity_monthly as (
select f.member_id,
       f.activity_year_month as activity_month
  from revenue_analysis f
 group by 1,2),
cte_user_lifecycle as (
select u.member_id,
       u.calendar_month,
       u.acquisition_month,
       m.activity_month
  from cte_user_monthly u
  left
  join cte_user_activity_monthly m
    on u.member_id = m.member_id
   and u.calendar_month = m.activity_month),
cte_user_status as (
select member_id,
       calendar_month,
       acquisition_month,
       activity_month,
       lag(activity_month,1) over (partition by member_id order by calendar_month) as prior_activity_month
  from cte_user_lifecycle),
user_status_monthly as (
select member_id,
       calendar_month,
       activity_month,
       case
          when calendar_month = acquisition_month then 'NEW'
          when prior_activity_month is not null and activity_month is not null then 'RETAINED'
          when prior_activity_month is not null and activity_month is null then 'UNRETAINED'
          when prior_activity_month is null and activity_month is not null then 'REACTIVATED'
          when prior_activity_month is null and activity_month is null then 'LAPSED'
          else null
       end as user_status
  from cte_user_status)
select member_id,
       calendar_month,
       activity_month,
       user_status,
       row_number() over (partition by member_id, user_status order by calendar_month) as months
  from user_status_monthly
 order by 1,2;

結果（包括activity_month以便于理解）：

member_id|calendar_month|activity_month|user_status|months|
--------- -------------- -------------- ----------- ------ 
     1001|        201701|        201701|NEW        |     1|
     1001|        201702|              |UNRETAINED |     1|
     1001|        201703|              |LAPSED     |     1|
     1001|        201704|              |LAPSED     |     2|
     1001|        201705|        201705|REACTIVATED|     1|
     1001|        201706|        201706|RETAINED   |     1|
     1001|        201707|              |UNRETAINED |     2|
     1001|        201708|              |LAPSED     |     3|
     1001|        201709|        201709|REACTIVATED|     2|
     1001|        201710|              |UNRETAINED |     3|
     1001|        201711|              |LAPSED     |     4|
     1001|        201712|        201712|REACTIVATED|     3|
     1002|        201703|        201703|NEW        |     1|
     1002|        201704|              |UNRETAINED |     1|
     1002|        201705|              |LAPSED     |     1|
     1002|        201706|              |LAPSED     |     2|
     1002|        201707|              |LAPSED     |     3|
     1002|        201708|              |LAPSED     |     4|
     1002|        201709|              |LAPSED     |     5|
     1002|        201710|              |LAPSED     |     6|
     1002|        201711|              |LAPSED     |     7|
     1002|        201712|              |LAPSED     |     8|

編輯：

1. 在 MySQL 中測驗的代碼，因為我沒有注意到洗掉了“mysql”標簽。
2. calendar_month在代碼中可以從calendar維度派生。

標籤：数据库sql服务器数据库

上一篇：SQL在一個查詢中使用多個SELECT陳述句

下一篇：更新groupby列的答案