Прогнозирование продаж в Power BI ч.2

953

Добрый день. На связи Николай Щелков, мы продолжаем разбираться в подходах к прогнозированию продаж и их реализации в Power BI. В прошлой статье мы разобрали два подхода – встроенный инструментарий Power BI и модель линейной регрессии. Рекомендую к прочтению перед этой статьей.

Модель третья – скользящая средняя или выявление нелинейных трендов в данных

Хорошо, что делать, если значения показателя распределены нелинейно? – наверное, самый напрашивающийся вопрос после прочтения предыдущего раздела статьи. В первую очередь, нужно понять – а каким образом они распределены, если не линейным? Здесь на помощь нам может прийти еще один вид прогноза, вернее группа видов прогноза, основанных на усреднении значений предыдущих периодов.

Скользящая средняя – этот термин слышали все, как и в случае с регрессией. Напомним суть – за прогнозное значение показателя в определенном периоде принимается арифметическое среднее значений этого показателя в нескольких предыдущих периодах.

Не будем тянуть и сразу обозначим, когда этот вид прогноза хорошо применим. Должно быть единовременно соблюдено два условия:

  1. Перед нами так называемый “стационарный” временной ряд, то есть в котором линейного тренда нет, либо он неочевиден.
  2. В данных нет ритмично повторяющихся взлетов и падений, иначе говоря – сезонности.

Если вдуматься, действительно, когда данные изменяются достаточно непредсказуемо, имеет смысл просто посчитать среднее значение и отталкиваться от него.

И снова хорошие новости. Если даже в данных есть тренд или сезонность, все равно скользящая средняя будет нам полезна. Но только не для предсказания значения показателя в будущем, а для лучшего понимания этих самых трендов и ритмичных колебаний. Здесь нам нужен наглядный пример (Рис. 5):

Рисунок 5 – Нелинейный тренд в данных, построенный с использованием скользящей средней

Черная линия, наложенная на известный нам график выручки GetCourse, – это значения скользящих средних с интервалом усреднения в 7 периодов (соответствующий параметр задается в нашем отчете ползунком сверху графика). Это означает, что каждая точка на черной линии – это среднее значений зеленой линии за семь предшествующих дней. Чем выше интервал усреднения, тем более сглаженной получится новая линия. Минимальный интервал усреднения (единица) фактически означает, что прогнозным значением за период мы считаем просто значение показателя в предыдущем периоде – это так называемый “наивный прогноз”. В редких случаях и он может оказаться самым точным!

О чем говорит нам новый график? В конце декабря наблюдался рост продаж (возможно, повлияли предновогодние промо-акции), с последующей просадкой в январе. Февраль показал значительный рост выручки, однако, с сильными колебаниями по дням. Такое вполне себе значимое аналитическое суждение может стать неплохим подспорьем тем знаниям, которые у вас уже есть, как у владельца бизнеса. Можно ли было бы сделать аналогичные выводы на основе только зеленой линии, которая была у нас изначально? В целом, да, но новый сглаженный тренд значительно упрощает эту задачу. А что если бы колебания зеленой линии были еще сильнее?

По описанной причине данный вид прогноза, возможно, самый интересный среди всех представленных именно для быстрой аналитики, поскольку позволяет наиболее наглядно разглядеть “пульс” вашего бизнеса – общие тенденции роста и снижения интересующих вас показателей в локальных временных интервалах. Полезное знание, не так ли?

Мы немного отвлеклись, собственно, от прогноза. Прогнозное значение в отчете GetCourse, как и в случае с регрессией, отображается вверху графика. Отличие (и ограничение) заключается в том, что в модели скользящей средней мы заглядываем в будущее только на один период (получается, на один день). Дело в том, что формула в данном случае рекурсивна – для определения скользящего среднего за второй день в будущем потребуется значение скользящего среднего за первый день, и так далее.

Инструмент MS Power BI (в частности, язык DAX) не предназначен для написания рекурсивных функций. Технически подобное возможно, но мы решили не нагромождать код и не снижать тем самым быстродействие отчета. Напомним, что для масштабного статистического анализа есть специализированные программные среды и библиотеки для языков программирования.

Тем не менее, чтобы сделать прогноз более полезным, мы реализовали расчет скользящего среднего по месяцам (Рис. 6):

Рисунок 6 – Расчет скользящего среднего продаж на платформе GetCourse по месяцам

В данном отображении настройки периода усреднения аналогичны, но этот график позволяет увидеть прогнозное значение на следующий месяц, что, наверное, полезнее прогноза лишь на один день вперед.

Таким мы видим базовый необходимый функционал прогнозирования при ведении бизнеса на онлайн-площадке продажи интерактивных курсов “GetCourse”. Мы рассмотрели лишь некоторые аспекты популярных инструментов прогноза. Как нам кажется, именно те аспекты, которые позволяют понимать суть этих моделей, определять их качество и применимость на конкретных данных.

Реализация инструментов прогнозирования в отчете GetCourse

Рассмотрим отчет “Прогноз”, который мы построили, основан на выгрузки данных сервиса mybi connect и модели данных GetCourse, рассмотренную ранее. Он состоит из четырех функциональных блоков (не считая заголовок и ссылки на магазин и другие листы).

Рисунок 7 – Лист “Прогноз” в отчете GetCourse

Давайте разберем подробнее эти функциональные блоки:

  1. Блок выбора показателя и типа анализа. Наш отчет позволяет исследовать пять бизнес-индикаторов – “Стоимость”, “Оплачено”, “Получено”, “Остаток” и “Заработано” – четырьмя моделями прогноза: штатным инструментом Power BI, регрессией, а также скользящим средним по дням и по месяцам.
  2. Блок выбора дополнительных параметров увеличивает глубину анализа, позволяя использовать временной срез, а также фильтрацию по статусам и тегам заказов. В интернете есть немало кейсов построения регрессии в Power BI (пример, еще пример), но в большинстве своем продемонстрированные в них результаты статичны. С гордостью заявляем, что наша регрессия пересчитывается в зависимости от выбранных фильтров (как и другие представленные виды прогноза).
  3. Блок описательных статистик содержит карточки с индикаторами, позволяющими лучше “разглядеть” особенности данных – характер распределения, выбросы, волатильность, и ряд других свойств. Чуть позже в этой статье мы обязательно остановимся подробнее на этих показателях, так как именно с них (наряду с визуальным отображением данных на графике) начинается прогнозный анализ и выбор дальнейшего инструментария.
  4. Основной блок визуализации. Здесь отображаются все графики, а также прогнозные значения показателей. Это блок результатов формирования прогнозных моделей.

В самой модели данных мы сделали минимальные изменения. Напомню, ранее мы разбирали ее. 

Модель данных GetCourse в MS Power BI на основании выгрузки mybi connect

В таблице “GC Заказы”, содержащей основную информацию о суммах заказов за прошлые периоды, мы добавили меры для расчета прогнозных значений показателей. Также мы создали две небольшие таблицы для агрегации данных по месяцам (в случае прогноза по методу скользящей средней).

Первая таблица генерирует список месяцев, основываясь на датах первой и последней продаж при помощи связка функций ADDCOLUMNS и GENERATESERIES:

Заказы (помесячно) =
ADDCOLUMNS (
    FILTER (
        GENERATESERIES (
            EOMONTH ( MIN ( 'GC Заказы'[Дата] )-1 ) + 1,
            EOMONTH ( MAX ( 'GC Заказы'[Дата] ) + 310 ),
            1
        ),
        DAY ( [Value] ) = 1
    ),
    "Месяц", [Value]
)

Вторая таблица агрегирует по месяцам значения необходимых метрик из основной таблицы заказов при помощи функции SUMMARIZE:

GC Заказы Сглаживание (помесячно) =
SUMMARIZE (
    'Заказы (помесячно)',
    'Заказы (помесячно)'[Месяц],
    "Стоимость"SUM ( 'GC Заказы'[Стоимость] ),
    "Оплачено"SUM ( 'GC Заказы'[Оплачено] ),
    "Получено"SUM ( 'GC Заказы'[Получено] ),
    "Остаток"SUM ( 'GC Заказы'[Остаток] ),
    "Заработано"SUM ( 'GC Заказы'[Заработано] )
)

Во второй новой таблице мы создали меры, аналогичные тем, которые использовались в основной таблице “GC Заказы” для расчета итоговых прогнозов и выполнения вспомогательных действий.

Реализация инструмента “Регрессия”

Как мы отметили в первой части статьи, прогноз штатными средствами Power BI не требует использования DAX-функций и реализуется через инструменты анализа на вкладке “Визуализации”, поэтому повторяться не станем, а перейдем к моделям, созданным вручную.

Начнем с регрессии – для этой модели в основной таблице “GC Заказы” было создано несколько мер.

“Регрессия_Показатель_для_прогноза” – мера для выведения на график ежедневных суммарных значений одного из пяти рассматриваемых бизнес-индикаторов (показателей для прогноза) в зависимости от установленных фильтров визуализации.

Регрессия_Показатель_для_прогноза =
VAR MinDate =
    MIN ( 'GC Заказы'[Дата] )
VAR MaxDate =
    MAX ( 'GC Заказы'[Дата] )
VAR Tab =
    ADDCOLUMNS (
        CALENDAR ( MinDateMaxDate ),
        "Показатель для прогноза",
            CALCULATE (
                COALESCE ( [Переключатель показателей для прогноза], 0 ),
                FILTER ( 'GC Заказы', 'GC Заказы'[Дата] = [Date] )
            )
    )
RETURN
    SUMX ( Tab, [Показатель для прогноза] )

В этой формуле первые две переменные MinDate и MaxDate формируют границы, которые привязаны к временному срезу на листе.

В переменной Tab создается врЕменная таблица, которая содержит все даты в диапазоне от MinDate до MaxDate, а также вычисляется показатель для прогноза для каждой даты. Делается это с помощью функции CALCULATE, которая фильтрует таблицу “GC Заказы” для каждой даты из календаря и применяет показатель из столбца [Переключатель показателей для прогноза], если он существует, иначе использует значение 0.

Строка с командой RETURN возвращает сумму всех значений показателя для прогноза из врЕменной таблицы Tab. Таким образом, мы получаем сумму всех показателей прогноза для каждой даты в диапазоне от MinDate до MaxDate.

Подход с созданием врЕменных таблиц, которые динамически пересчитываются прямо внутри мер, проходит красной нитью через весь лист “Прогноз” в нашем отчете. Именно таким образом мы делаем возможным управление расчетными значениями через фильтры и срезы на листе.

Примечание: Формула реализована таким образом, что регрессия считается на интервале дат между первой и последней фактической продажей (даты с не нулевыми значениями сумм продаж по краям). Эта логика показалась нам резонной с точки зрения бизнеса, ведь мы хотим отслеживать те периоды времени, когда работа реально велась. В случае пересмотра бизнес-логики, соответствующие правки могут быть внесены в расчетную формулу.

“Регрессия_Прогнозное_значение” – мера для отображения в карточке конкретного прогнозного значения показателя в заданном пользователем интервале прогноза.

Регрессия_Прогнозное_значение =
VAR MinDate =
    MIN ( 'GC Заказы'[Дата] )
VAR MaxDate =
    MAX ( 'GC Заказы'[Дата] )
VAR Tab =
    ADDCOLUMNS (
        CALENDAR ( MinDateMaxDate ),
        "Показатель для прогноза",
            CALCULATE (
                COALESCE ( [Переключатель показателей для прогноза], 0 ),
                FILTER ( 'GC Заказы', 'GC Заказы'[Дата] = [Date] )
            )
    )
VAR Reg_summary =
    LINESTX ( Tab, [Показатель для прогноза], [Date] )
VAR Slope =
    SELECTCOLUMNS ( Reg_summary, [Slope1] )
VAR Intercept =
    SELECTCOLUMNS ( Reg_summary, [Intercept] )
RETURN
    Intercept + Slope * ( MaxDate + 'Регрессия: параметры'[Parameter Value] )

Как видно, границы дат и врЕменная таблица в этой мере созданы по аналогии с предыдущей, но здесь появились новые переменные. Для их создания использована функция LINESTX, реализованная в Power BI в декабре 2016 года вместе с обновлением Power BI Desktop. Эта функция возвращает таблицу с ключевой информацией о регрессии на основе выбранных значений рассматриваемого параметра.

Переменная Reg_summary – это и есть таблица регрессии, созданная при помощи функции LINESTX. Переменные Slope и Intercept достают из этой таблицы коэффициенты регрессии, на основе которых и будет строиться линейный прогноз.

Функция RETURN возвращает значение регрессии (то есть прогноз) на интересующий пользователя период в будущем (в нашем отчете – от 1 до 30 дней). “Регрессия: параметры” – это инструмент, созданный командой “Числовой диапазон” в группе “Создание параметра” во вкладке “Моделирование”. Он привязан к срезу, в котором пользователь устанавливает количество дней прогноза, тем самым инициируя перерасчет меры для получения прогноза на интересующий его день.

“Регрессия_R_квадрат” – мера для отображения в карточке значения качества прогнозной модели, опять же, в зависимости от установленных фильтров визуализации.

Регрессия_R_квадрат =
VAR MinDate =
    MIN ( 'GC Заказы'[Дата] )
VAR MaxDate =
    MAX ( 'GC Заказы'[Дата] )
VAR Tab =
    ADDCOLUMNS (
        CALENDAR ( MinDateMaxDate ),
        "Показатель для прогноза",
            CALCULATE (
                COALESCE ( [Переключатель показателей для прогноза], 0 ),
                FILTER ( 'GC Заказы', 'GC Заказы'[Дата] = [Date] )
            )
    )
RETURN
    SELECTCOLUMNS (
        LINESTX ( Tab, [Показатель для прогноза], [Date] ),
        [CoefficientOfDetermination]
    )

Аналогичным образом, из таблицы, созданной при помощи функции LINESTX, берется значение коэффициента детерминации (R-квадрат), смысл которого мы кратко раскрыли в прошлой статье.

“Переключатель показателей для прогноза” – техническая мера, которая используется в предыдущих упомянутых мерах для выбора значений по нужному показателя в зависимости от установленного фильтра.

Переключатель показателей для прогноза =
SWITCH (
    VAR SelVal =
        SELECTCOLUMNS (
            SUMMARIZE (
                'Показатели для прогноза',
                'Показатели для прогноза'[Показатели для прогноза],
                'Показатели для прогноза'[Показатели для прогноза Fields]
            ),
            'Показатели для прогноза'[Показатели для прогноза]
        )
    RETURN
        IF ( COUNTROWS ( SelVal ) = 1SelVal ),
    "Стоимость"SUM ( 'GC Заказы'[Стоимость] ),
    "Оплачено"SUM ( 'GC Заказы'[Оплачено] ),
    "Получено"SUM ( 'GC Заказы'[Получено] ),
    "Остаток"SUM ( 'GC Заказы'[Остаток] ),
    "Заработано"SUM ( 'GC Заказы'[Заработано] ),
    BLANK ()
)

При помощи функции SWITCH и ряда вспомогательных команд данная мера передает название одного из пяти бизнес-индикаторов, выбранное в соответствующем срезе на листе, во все рассмотренные меры. Таким образом, вся регрессия автоматически перестраивается под прогноз, например, “Стоимости”, или “Остатка”.

Реализация инструмента “Сглаживание”

Напомним, что  под “сглаживанием” в нашем отчете мы понимаем расчет среднего значения параметра за определенный интервал времени в прошлом. Мы называем этот инструмент так за способность как бы “сглаживать” график данных для более наглядного представления локальных закономерностей в них. Формально, существует еще инструмент прогнозирования под названием “экспоненциальное сглаживание”, который также хорошо работает на данных, в которых общий линейный тренд не прослеживается (где линейная регрессия не применима). Но его мы в нашу модель на данный момент не включили – это одна из точек роста на будущее.

Вернемся к скользящей средней. Она реализована при помощи соответствующей меры:

Сглаживание_Скользящая_Средняя =
AVERAGEX (
    DATESBETWEEN (
        'SHD Связь календарей'[Дата],
        MAX ( 'SHD Связь календарей'[Дата] ) - 'Сглаживание'[Сглаживание Value] + 1,
        MAX ( 'SHD Связь календарей'[Дата] )
    ),
    CALCULATE ( SUMX ( 'GC Заказы', [Переключатель показателей для прогноза] ) )
)

Функция DATESBETWEEN возвращает таблицу дат между начальной и конечной датами. Начальная дата определяется как максимальная дата минус значение сглаживания (количество периодов, на которые нужно сгладить данные), плюс один день. Конечная дата – это просто максимальная дата.

Внутри каждой из дат из таблицы, созданной функцией DATESBETWEEN, происходит вычисление показателя для прогноза с использованием знакомой нам меры “Переключатель показателей для прогноза”, которая, представляет собой различные варианты агрегированных значений (сумма по стоимости, по остатку и др.) в зависимости от выбранного пользователем показателя.

Функция AVERAGEX, собственно, возвращает среднее значение показателя для прогноза за выбранный период времени.

Расчет скользящей средней по месяцам полностью аналогичен. Есть только два примечательных отличия:

  1. Аналогичные меры для расчета данных по месяцам реализованы не в основной таблице “GC Заказы”, а в специально созданной агрегирующей таблице “GC Заказы Сглаживание (помесячно)”.
  2. Для каждого из пяти рассматриваемых бизнес-индикаторов сформированы отдельные меры расчета скользящих средних. Пример одной из пяти мер представлен ниже.
Стоимость: Скользящая Средняя (помесячно) =
CALCULATE (
    AVERAGE ( [Стоимость] ),
    DATESINPERIOD (
        'GC Заказы Сглаживание (помесячно)'[Месяц],
        MAX ( 'GC Заказы Сглаживание (помесячно)'[Месяц] ),
        - [Сглаживание Value],
        MONTH
    )
)

Эта формула вычисляет скользящее среднее значение стоимости заказов по месяцам с использованием указанного количества предыдущих месяцев для сглаживания данных. Она использует функцию DATESINPERIOD для создания таблицы дат по месяцам, определенного с помощью столбца ‘Месяц’, и затем применяет функцию AVERAGE к столбцу ‘Стоимость’, чтобы вычислить среднее значение стоимости заказов за указанный период времени. Функция CALCULATE применяет изменения контекста к выражению, позволяя учитывать фильтры, примененные к отчету.

Таким образом, реализация прогнозных моделей в отчете на основе модели данных GetCourse, в основном, строится на мерах, содержащих в себе динамические таблицы, позволяющие автоматически перестраивать анализ в зависимости от выбранных фильтров и срезов.

Реализация инструмента “Описательные статистики”

Принципы реализации моделей описаны – на этом рассказ о листе прогнозов в нашем отчете можно заканчивать? Никак нет. Мы упоминали одну примечательную часть этого листа и обещали рассказать о ней чуть подробнее. Если по мере прочтения нашей статьи вы еще не начали параллельно поглядывать базовую теорию статистики, возможно, сейчас самый подходящий момент, потому что говорить мы будем об описательных статистиках – “математическом паспорте” ваших данных.

Рисунок 8 – Лист “Прогноз” в отчете GetCourse

Принцип GIGO мы ранее описывали в нашем блоге, и здесь вспомнить о нем будет как нельзя кстати. Прежде чем строить любую (не только прогнозную) модель, необходимо убедиться, что данные, которыми мы располагаем, к этому готовы. Описательные статистики – это простейший, но достаточно действенный инструмент проверки качества данных. Это группа показателей, с помощью которых можно определить характер распределения данных, наличие потенциальных выбросов (аномальных значений), степень волатильности (разброса).

В отчете GetCourse реализованы некоторые описательные статистики. Мы попробуем дать им трактовку с точки зрения их пользы для проверки качества данных перед использованием в какой-либо прогнозной модели в нашем отчете.

  1. Число наблюдений. Это количество дней в прошлом, в которые производились продажи обучающих материалов на платформе GetCourse. Если данные за отдаленные периоды в прошлом, на ваш взгляд, не актуальны, смело “режьте” интервал при помощи временного фильтра на листе.
  2. Среднее значение. Сколько вы зарабатываете в день на продаже курсов? С определенной долей условности, но все же ответить на этот вопрос вам поможет этот показатель.
  3. Стандартное отклонение. Этот показатель говорит о том, насколько вы можете доверять предыдущему. Если отклонение (разброс) не высоко, то у вас довольно стабильный бизнес, и среднему значению, в целом, можно доверять. Если разброс высокий, даже предварительные выводы делать рано – нужно смотреть, что еще происходит в ваших данных.
  4. Медиана. Медиана и средняя хорошо дополняют друг друга, если мы хотим понять характер распределения данных. Об этом, опять же, есть много информации в открытом доступе, мы же обратим ваше внимание на следующее: если значение средней и медианы в ваших данных сильно отличаются, следует сделать тщательную проверку на наличие аномалий. Возможно, в какие-то дни у вас действительно были небывало высокие или низкие продажи, а может быть просто часть данных собрана или внесена некорректно.
  5. Минимальное и максимальное значение. Это еще один эффективный способ поиска аномалий. “Ненормальные” значения сразу бросятся вам в глаза, поскольку вы хорошо знаете свои продажи.
  6. Размах вариации. За неочевидным названием скрывается просто разность между максимальным и минимальным значением за период. Это еще одна мера разброса, которая позволяет в той или иной степени судить, насколько стабилен ваш бизнес.
  7. Межквартильный размах. Если выстроить суммарные значения продаж за каждый день в выбранном периоде в ряд по возрастанию и отбросить верхнюю и нижнюю четверти значений в этом ряду, оставшаяся “серединка” составит основной “сгусток” ваших продаж. Разница между максимальным и минимальным значением этой серединки – это ваш межквартильный размах. 

Как вы догадались, по аналогии с другими визуальными элементами на листе “Прогноз”, описательные статистики динамически изменяются в зависимости от выставленных фильтров и срезов.

Все упомянутые описательные статистики рассчитаны при помощи очень похожих мер. Продемонстрируем на примере расчета среднего значения:

Регрессия_ОС_Среднее =
VAR MinDate =
    MIN ( 'GC Заказы'[Дата] )
VAR MaxDate =
    MAX ( 'GC Заказы'[Дата] )
VAR Tab =
    ADDCOLUMNS (
        CALENDAR ( MinDateMaxDate ),
        "Показатель для прогноза",
            CALCULATE (
                COALESCE ( [Переключатель показателей для прогноза], 0 ),
                FILTER ( 'GC Заказы', 'GC Заказы'[Дата] = [Date] )
            )
    )
RETURN
    AVERAGEX ( Tab, [Показатель для прогноза] )

В этой мере после создания знакомой нам врЕменной таблицы, применена функция AVERAGEX – стандартный инструмент расчета среднего значения в языке DAX. Соответственно, для остальных показателей в группе описательных статистик, врЕменная таблица та же, отличаются лишь результирующие функции.

Что ж, все инструменты под рукой – приглашаем вас проверить их на данных вашего бизнеса! Обратная связь поможет нам сделать отчет и модель данных еще лучше и ближе к вашим прикладным задачам.

Заключение

Напоследок повторим основной посыл, который мы стремились передать в этой статье, и которым мы руководствовались при разработке листа прогнозов в отчете GetCourse: статистический аппарат при должном своем применении не снижает скорость принятия решений в бизнесе. Напротив, быстрый взгляд на ключевые индикаторы позволяет повысить качество выводов о тех данных, которые есть в наличии, и о том, какие тенденции могут наблюдаться в будущем.

Шаблоны для GetCourse на основании выгрузки mybi connect
Шаблоны для GetCourse на основании выгрузки mybi connect

И для тех, кто уделил внимание нашей статье до конца, приятное напоминание: обновленная модель данных GetCourse, общий отчет по продажам и прогнозный отчет, охватывающий все описанные здесь методы и меры, все еще доступны в mybi market, и могут быть использованы вместе с бесплатным пробным периодом сервиса mybi connect. Кроме того, отчет, созданный на основе этой модели данных, также доступен для приобретения отдельно на нашем маркетплейсе.

Также рады пригласить вас в наш телеграм-канал, где мы регулярно публикуем новости и делимся полезными практиками.

Реклама. ООО “Биай Коннект”. erid 2VtzqumhEV9