Пример запроса к базе данных, применимого для построения аналитического отчета о лояльности клиентов:
Loyalty Triangle Report
См. также What is a Claims Triangle Report? - Insurance IT blog
➢Реализация запроса вручную на базе минимального фрагмента данных
➢Реализация запроса с помощью хранимых процедур и циклов – для обработки большого объема данных (n млн. записей)
Суть запроса и его реализация вручную
Например, есть платное учреждение по оказанию образовательных услуг. Клиенты – частные и физические лица. Форма отношений – договорная. Предоставляемые услуги – иностранные языки.
Портфель клиентов растет. Возникает вопрос: сколько из поступивших учеников действительно остаются и в будущем проплачивают обучение из месяца в месяц по контракту и как долго? Пользуются ли клиенты услугой до конца договора и сколько таких?
Другой пример: есть магазин. При покупке товара более, чем на n рублей, выдаётся дисконтная карта, регистрируется ID карты в базе данных. Если клиент заглянет в магазин снова и решит что-нибудь купить, предъявив карту, по нему зарегистрируется вторая продажа. И так далее.
Есть возможность ответить на вопрос: сколько из новых покупателей в n-м году или месяце пришли снова? Сколько из них пришли снова не один раз? Как покупатели отреагировали на акцию?
Анализ лояльности клиентов на примере оказания услуг по образованию.
Стандартные условия:
Разные курсы имеют разную длительность и стоимость. Для всех курсов предусмотрена ежемесячная оплата. Договор заключается на период оказания услуги (длительность курса) и клиент вправе расторгнуть или приостановить действие договора, не оплатив очередного платежа и оказание услуги приостанавливается.
Понятное дело, что есть слушатели, которые расторгают договор и перестают посещать курсы.
Возникает вопрос: сколько из поступивших учеников остаются и в будущем проплачивают обучение из месяца в месяц по контракту и как долго? Пользуются ли клиенты услугой до конца контракта и сколько таких?
Информация о первых и очередных оплатах договора поступает в таблицы базы данных. Пусть это БД, состоящая из двух справочников (CLIENTS и SERVICES) и одной таблицы – с фактами оплаты услуг (SERVICE_FACTS).
Таблица CLIENTS (список клиентов):
ID,
Имя
Таблица SERVICES (перечень предоставляемых услуг):
ID,
Наименование,
Длительность (в месяцах),
Стоимость за курс
SERVICE_FACTS (факты оплаты услуг).
Фрагмент данных по услуге "Китайский для начинающих", с началом курса в 1999-ом году:
ID услуги,
ID клиента,
Дата начала оказания услуги
Дата оплаты услуги
Сумма оплаты
Согласно таблице фактов, клиент 11001 (Петров Василий) купил курсы китайского, с началом 01-го января 1999-го; проплатил из них первый месяц, второй, третий и четвертый, дальше фактов нет. Клиент 11002 купил тот же курс, но с 15-го января и проплатил три месяца. В общем виде, наблюдаем, что только клиент 11004, купивший курсы с февраля, полностью проплатил и прослушал договорной год.
При таком количестве данных легко построить таблицу лояльности вручную.
Представим таблицу в формате двух осей координат, по X будем строить месяцы с началом оказания услуги, по Y – месяцы, последующие от начала оказания услуги:
Поименуем шапки осей координат, согласно известным данным: начало оказания услуги – январь 1999, длительность оказания услуги – 12 месяцев:
Заполним вручную вспомагательную таблицу с координатами данных по фактам оплаты услуги: по оси X будет координата от 1 до 12 как месяц начала оказания услуги; по оси Y будет координата от 1 до 12 как порядковый номер месяца оплаты:
Просуммируем таблицу по количеству клиентов, приходящихся на каждую координату Х и на каждую Y.
Например:
1:1 |
– 3 клиента |
1:2 |
– 3 клиента |
1:3 |
– 3 клиента |
1:4 |
– 1 клиент |
1:5–1:12 |
– никого (0) |
2:1–2:11 |
– 1 клиент |
3:1 |
– 3 клиента |
3:2 |
– 2 клиента |
3:3 |
– 1 клиент |
3:4-3:10 |
– никого (0) |
Заполним полученными результатами таблицу координат:
3 продажи января 1999-го не увенчались успехом: плательщики продержались только 3 месяца (неоправданные ожидания или плохой сервис оказания услуг?).
Продажи февраля 1999-го, несмотря на то, что услуга была продана только одному клиенту, оказались самыми выгодными.
Продажи марта 1999-го неудачные – 3 ученика не продержались и трех месяцев.
Вот такой была бы идеальная картина лояльности на протяжении календарного года (январь 1999 – декабрь 1999):
Реализация подобного запроса с помощью циклов
План реализации состоит из построения рабочей таблицы, содержащей количество записей таблицы фактов (SERVICE_FACTS) по координатам X, Y и построения итогового отчета (куба) на базе данных рабочей таблицы и связанных с ней справочников.
Параметрами процедуры, заполняющей в цикле рабочую таблицу, будут:
•количество колонок по оси X, где одна колонка равна одному месяцу оплат (мы можем получить это количество, проанализировав содержание таблицы фактов с первой даты оплаты курсов по последнюю) и
•количество строк по оси Y – в нашем примере это зависит от длительности предоставляемых услуг (можно заложить максимальную длительность, применимую также и для других, более коротких курсов).
Формат рабочей таблицы:
CREATE TABLE [dbo].[LOYALTY_REPORT_POSITION_DATA](
[row] [int] NULL,
[col] [int] NULL,
[items] [int] NULL,
[amount] [float] NULL
) ON [PRIMARY]
Заполним рабочую таблицу LOYALTY_REPORT_POSITION_DATA данными о колонках отчета (деления по оси X - количество месяцев в рассматриваемом периоде) и о строках (деления по оси Y – создадим 12 строк, чтобы понаблюдать лояльность по оплатам всего года от оплат первого месяца). Макет таблицы выглядит так:
col:row - координата, где row - строка (ось Y); col - колонка (ось X); items - количество, приходящееся на координату col:row по факту оплаты услуги; amount - сумма оплаты, приходящаяся на координату col:row по факту оплаты услуги:
(процедуру заполнения таблицы см. ниже)
Процедура первичного заполнения рабочей таблицы LOYALTY_REPORT_POSITION_DATA:
Create procedure dbo.initLoyaltyReport(
@startDate as DateTime,
@endDateExcluding as DateTime,
@rows as int) as
Begin
Delete from LOYALTY_REPORT_POSITION_DATA
Declare @cols as int
Set @cols = datediff(m, @startDate, @endDateExcluding)
Declare @row as int
Set @row = 1
While @row < = @rows
Begin
Declare @col as int
Set @col = 1
While @col < = @cols
Begin
Insert into LOYALTY_REPORT_POSITION_DATA (row, col, items, amount)
values (@row, @col, 0, 0)
Set @col = @col + 1
end
Set @row = @row + 1
end
End
Запуск процедуры по датам рассматриваемого периода на 12 строк:
execute dbo.initLoyaltyReport
'1999-01-01',
'2000-01-01',
12
Следующая задача – разработать процедуру формирования данных к отчету, заполнить полученный макет рабочей таблицы количествами и денежными суммами по данным таблицы фактов оплат (SERVICE_FACTS), так чтобы каждая оплата проверялась к какой она относится координате (X - месяц начала оказания услуги; Y – месяц, за который услуга оплачена) и в соответствии с результатом проверки проставлялясь бы в соответствующую ячейку макета. Например, услуга с датой начала 01.01.1999, оплаченная за февраль 1999 запишется в виде единицы в колонку items по колонке row = 2, т.к. февраль – 2-й месяц по порядку от начала оказания услуги и по колонке col = 1, т.к. январь – первый месяц в рассматриваемом периоде. Если еще один клиент оплатил услугу за февраль-99, начало которой январь-99, то в эту же ячейку items, где row = 2 и col = 1 запишется еще одна единица и в ячейке будет уже 2 item-а. И так далее.
Аналогичным образом заполнится и колонка по суммам оплат.
Фрагмент таблицы SERVICE_FACTS:
Процедура формирования данных к отчету – заполнение рабочей таблицы LOYALTY_REPORT_POSITION_DATA данными таблицы SERVICE_FACTS:
Create procedure dbo.fillLoyaltyReportRows(
@startDate as DateTime,
@endDateExcluding as dateTime)
as
Begin
DECLARE items CURSOR FOR
SELECT SERVICE_BEGIN_DATE, PAYMENT_DATE, PAYMENT_AMNT
From SERVICE_FACTS
Where SERVICE_BEGIN_DATE >= @startDate
And SERVICE_BEGIN_DATE < @endDateExcluding
OPEN items
Declare @SERVICE_BEGIN_DATE as datetime
Declare @PAYMENT_DATE as datetime
Declare @PAYMENT_AMNT as numeric(18,2)
-- выполнить первое считывание и сохранить указанные значения (выполненного Select запроса)
-- FETCH - достать
FETCH NEXT FROM items
-- переменные должны идти в том же порядке, в котором идут клонки Select запроса
INTO
@SERVICE_BEGIN_DATE,
@PAYMENT_DATE,
@PAYMENT_AMNT
-- проверить, считалась ли запись: если результат считывания успешный обработать полученные данные
WHILE @@FETCH_STATUS = 0
BEGIN
Declare @col as int
Set @col = datediff(month, @startDate, @SERVICE_BEGIN_DATE)
Declare @row as int
Set @row = 1 + datediff(month, @SERVICE_BEGIN_DATE, @PAYMENT_DATE)
print 'col='+convert(varchar,@col)+' row='+convert(varchar,@row)
Update LOYALTY_REPORT_POSITION_DATA
Set
items = items + 1,
amount = amount + @PAYMENT_AMNT
where
col = @col
and row = @row
-- продолжить пока есть записи в рекордсете
FETCH NEXT FROM items
INTO
@SERVICE_BEGIN_DATE,
@PAYMENT_DATE,
@PAYMENT_AMNT
END
CLOSE items
DEALLOCATE items
End
Запуск процедуры dbo.fillLoyaltyReportRows:
execute dbo.fillLoyaltyReportRows
'1999-01-01',
'2000-01-01'
Результат работы dbo.fillLoyaltyReportRows – заполненная таблица LOYALTY_REPORT_POSITION_DATA:
На базе такой таблицы, принятой за таблицу фактов, несложно построить аналитический куб.
Измерениями (Dimensions) куба будут поля Column Nbr и Column Name – таблицы dbo.LOYALTY_REPORT_DIM_X:
и поле Row Nbr таблицы – dbo.LOYALTY_REPORT_DIM_Y:
Аггрегированными фактами куба (Measures) будут
LOYALTY_REPORT_POSITION_DATA.items – количество оплаченных услуг
или
LOYALTY_REPORT_POSITION_DATA.amount – сумма оплаченных услуг
с типом аггрегации Sum.
Схема куба:
Результат разработки куба:
По циклам см. также: Работа с CURSOR (Recordset)
Если интересно как представлен этот куб в Excel, то см. тут