Анализ состояния жизненного цикла клиента на основе ежемесячной активности

Привет, моя компания хочет лучше отслеживать, сколько пользователей активно на нашей платформе. Мы используем Microsoft SQL Server 2019 в качестве базы данных, подключенной к Azure Data Studio.

Ниже приведены две таблицы DDL из нашей БД:

  1. КАЛЕНДАРНЫЙ СТОЛ
СТОЛБЕЦ ТИП ДАННЫХ ПОДРОБНОСТИ КАЛЕНДАРЬ_DATE ДАТА НЕ НУЛЕВАЯ Базовая дата (ГГГГ-ММ-ДД) КАЛЕНДАРНЫЙ ГОД ЦЕЛОЕ ЧИСЛО НЕ НУЛЕВОЕ 2010, 2011 и т. д. КАЛЕНДАРЬ_МЕСЯЦ_НОМЕР ЦЕЛОЕ ЧИСЛО НЕ НУЛЕВОЕ 1-12 КАЛЕНДАРЬ_МЕСЯЦ_ИМЯ ВАРЧАР(100) Январь, февраль и т.д. CALENDAR_DAY_OF_MONTH ЦЕЛОЕ ЧИСЛО НЕ НУЛЕВОЕ 1-31 КАЛЕНДАРЬ_DAY_OF_WEEK ЦЕЛОЕ ЧИСЛО НЕ НУЛЕВОЕ 1-7 КАЛЕНДАРЬ_ДЕНЬ_ИМЯ ЦЕЛОЕ ЧИСЛО НЕ НУЛЕВОЕ Понедельник, вторник и т.д. CALENDAR_YEAR_MONTH ЦЕЛОЕ НЕ НУЛЕВОЕ, 201011, 201012, 201101 и т.д.
  1. АНАЛИЗ ДОХОДОВ
Столбец Тип данных Подробности ACTIVITY_DATE ДАТА НЕ НУЛЕВАЯ Дата заключения пари ID ПОЛЬЗОВАТЕЛЯ ЦЕЛОЕ ЧИСЛО НЕ НУЛЕВОЕ Уникальный идентификатор игрока ID_ИГРЫ SMALLINT НЕ NULL Уникальный идентификатор игры WAGER_AMOUNT РЕАЛЬНОЕ НЕ НУЛЕВОЕ Общая сумма ставок на игру NUMBER_OF_WAGERS ЦЕЛОЕ ЧИСЛО НЕ НУЛЕВОЕ Количество ставок на игру WIN_AMOUNT РЕАЛЬНОЕ НЕ НУЛЕВОЕ Общая сумма выигрыша в игре АКТИВНОСТЬ_YEAR_MONTH ЦЕЛОЕ ЧИСЛО НЕ НУЛЕВОЕ ГГГГММ БАНК_TYPE_ID МАЛЕНЬКИЙ ЦЕЛОЕ ПО УМОЛЧАНИЮ 0 НЕ NULL, 0=Реальные деньги, 1=Бонусные деньги

Скриншот для обеих таблиц ниже:

КАЛЕНДАРНЫЙ СТОЛ

ТАБЛИЦА АНАЛИЗ ДОХОДОВ

Короче говоря, «активный» означает, что участник сделал как минимум одну ставку на реальные деньги в месяц.

Каждый месяц участник имеет определенный тип жизненного цикла. Этот статус будет меняться ежемесячно в зависимости от их активности в предыдущем и текущем месяцах. Статусы следующие:

НОВЫЙ Впервые они сделали ставку на реальные деньги СОХРАНЕН Активно в предыдущем календарном месяце и в текущем календарном месяце НЕЗАДЕРЖАННЫЙ Активен в предыдущем календарном месяце, но не активен в текущем календарном месяце ПОВТОРНО АКТИВИРОВАНО Не активен в предыдущем календарном месяце, но активен в текущем календарном месяце ИСЧЕЗ Не активен в предыдущем календарном месяце или в текущем календарном месяце

Мы хотели бы изначально получить представление со столбцами ниже:

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

Некоторые примеры выходных строк были бы полезны. Также вы должны форматировать свой код (отступы и разрывы строк) в целом, чтобы писать более чистый код.

J.D. 21.11.2022 05:25

Предлагаемое название: Анализ состояния жизненного цикла клиента на основе ежемесячной активности

JHH 21.11.2022 06:13

Определенно согласен @JHH, я постараюсь отредактировать заголовок, чтобы он лучше подходил.

ERR 21.11.2022 10:00
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
1
3
89
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Речь идет об анализе состояния жизненного цикла клиента, для которого требуется несколько вещей:

  1. дата приобретения клиента (было бы неплохо сохранить это, потому что некоторые клиенты могут вернуться на годы или десятки лет назад). Для этого вопроса мы предполагаем, что у revenue_analysis есть все, что нам нужно, и для расчета user acquisition month
  2. lapsed vs 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.

Спасибо JHH, это именно то, что я ищу. Только что проголосовал и принял ваш ответ.

ERR 21.11.2022 09:56

Другие вопросы по теме