Привет, моя компания хочет лучше отслеживать, сколько пользователей активно на нашей платформе. Мы используем Microsoft SQL Server 2019 в качестве базы данных, подключенной к Azure Data Studio.
Ниже приведены две таблицы DDL из нашей БД:
Скриншот для обеих таблиц ниже:
КАЛЕНДАРНЫЙ СТОЛ
ТАБЛИЦА АНАЛИЗ ДОХОДОВ
Короче говоря, «активный» означает, что участник сделал как минимум одну ставку на реальные деньги в месяц.
Каждый месяц участник имеет определенный тип жизненного цикла. Этот статус будет меняться ежемесячно в зависимости от их активности в предыдущем и текущем месяцах. Статусы следующие:
Мы хотели бы изначально получить представление со столбцами ниже:
MEMBER_ID | 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
Предлагаемое название: Анализ состояния жизненного цикла клиента на основе ежемесячной активности
Определенно согласен @JHH, я постараюсь отредактировать заголовок, чтобы он лучше подходил.
Речь идет об анализе состояния жизненного цикла клиента, для которого требуется несколько вещей:
revenue_analysis
есть все, что нам нужно, и для расчета user acquisition month
lapsed
vs churned
: клиент churned
обычно определяется как неактивный в течение определенного периода времени. Для этого вопроса у нас нет определения, поэтому пользователь будет всегда отмечен как lapsed
.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|
Обновлено:
calendar_month
в коде может быть получено из измерения calendar
.Спасибо JHH, это именно то, что я ищу. Только что проголосовал и принял ваш ответ.
Некоторые примеры выходных строк были бы полезны. Также вы должны форматировать свой код (отступы и разрывы строк) в целом, чтобы писать более чистый код.