Отчёты о рассылках из UniSender в Power BI.
Часть 2. Обработка отчета в Power BI
Продолжение статьи от EmailSoldiers по созданию автоматических отчетов из сервиса рассылок Unisender в Power BI.
Научимся принимать и обрабатывать запросы из Unisender в Power BI. Первая часть статьи по ссылке.

Запускаем Power BI. На первом этапе мы будем работать с редактором запросов (Query Editor).
Работа над отчётом будет строиться следующим образом.
Сначала получим от UniSender по API все необходимые данные, затем обработаем их.
На третьем шаге сделаем визуализацию.
После всего этого посмотрим, как выложить этот отчёт в онлайн, чтобы за ним могли смотреть заказчик или команда.

Открываем Query Editor и совершаем первый запрос.
Кликаем в Новый источник данных (New Source).
Выбираем подходящий источник. Данные мы получаем из интернета по ссылке, поэтому как в качестве источника выбираем Web:
В URL вставляем нашу ссылку запроса по методу getCampaigns, которую мы уже проверили в Postman.
Получили данные. Результат нам пришёл в виде списка (List). Давайте посмотрим, что же произошло после отправки формы.

Power BI сохранил наш запрос в списке запросов. Он выполнил этот запрос и полученные данные в формате JSON вернул нам в виде List-а. И ещё он записал наше первое действие в секции APPLIED STEPS. Откроем этот запрос в редакторе запросов (Advanced Editor).
Разберём код:
let  

Source = Json.Document(Web.Contents("http://api.unisender.com/ru/api/getCampaigns?format=
json&api_key=xxxxxxxxxxxxxxxxxxxxxxxx"))  

in   

Source
Вот наша ссылка. Указан тип источника Web.Contents — веб содержимое. Выполняя запрос, Power BI прочитал заголовок ответа, увидел, что ответ приходит в виде JSON, поэтому Power BI самостоятельно принял решение читать данные, как JSON, и поэтому здесь появилась функция Json.Document.

Если бы возвращаемые данные были в каком-то другом формате, например, в XML, на этом месте оказалась бы другая соответствующая функция. Тут следует сказать, что язык запросов Power BI — это «MS Power Query», также известный, как «M». Безусловно, нам понадобится документация по этому языку. В настоящий момент для нас самое главное усвоить структуру запроса.

Сам запрос и вообще все манипуляции производятся после выражения let, а возвращаемый результат следует после in. Редактор запросов Power BI не имеет подсветки синтаксиса, автоматического переноса строк и поиска. По сути своей, это не редактор вовсе, а поле ввода. Поэтому читать код и работать с кодом в нём неудобно.

Однако Power Query имеет синтаксис, отдалённо похожий на синтаксис JavaScript. Поэтому для работы с кодом запросов на языке M можно использовать любой редактор кода в режиме подсветки синтаксиса JavaScript:
Вернёмся к Power BI.

Опишу сразу конечную задачу, к которой мы будем стремиться. Полученные первым запросом данные пока не являются таблицей. Нам надо не только получать данные от источников, но также проделывать ряд операцией, приводящих сырые данные к табличному, пригодному для дальнейшего использования, виду. Мы можем просмотреть содержимое полученного списка, кликнув в его ячейку. Мы видим, что внутри списка содержатся записи (Record). Открываем список кликом по ссылке «List» в ячейке данных. Обращаем внимание на то, что в списке примененных шагов (APPLIED STEPS) появился новый пункт «Navigation» и в редакторе запросов также появилась соответствующая функция.
Вообще все манипуляции с данными в визуальном редакторе Query Editor находят своё отражение в списке шагов и в коде запросов.

Теперь просмотрим содержимое записей. Мы видим, что в каждой записи находятся данные об одной рассылке.
Если мы кликнем в запись, то получим не тот результат, к которому мы стремимся. Поэтому мы отменим действие Navigation и снова откроем лист. Преобразуем наш список записей к табличному виду с помощью стандартного инструмента Power BI — To Table.
И теперь раскроем записи. В форме раскрытия мы можем выбрать данные, которые останутся в таблице на следующем шаге.

Небольшое лирическое отступление:
Мы видим предупреждение List may be incomplete (список может быть не полным), и это очень важный момент. По умолчанию Power BI анализирует только первые несколько записей и предлагает раскрыть поля, которые присутствуют в них. Но реально бывает так, что в какой-то 155-й по счёту записи появляется поле, которого в предыдущих записях не было. Чтобы это значение попало в список раскрываемых данных, необходимо кликнуть на ссылку Load more. Желательно это делать всегда. И даже так будет просканировано только 1000 первых записей. Если вы уверены в том, что каких-то данных после этого шага недостает, вы всегда можете доработать запрос в редакторе запросов, вручную добавив недостающие поля.
Также в данном случае, можно деактивировать чекбокс «Use original column name as prefix». Если от префикса избавиться, то названия столбцов будут более компактными. Так что я деактивировал чекбокс.

Итак, раскрываем записи. Этот шаг зафиксирован.

Смотрим в код:
let  

Source = Json.Document(
Web.Contents(
"http://api.unisender.com/ru/api/getCampaigns?format=
json&api_key=5tmjbcir5g4xuew573mxcoh4ypo98xbbbadqgs4a")),  
result = Source[result],  
#"Converted to Table" = Table.FromList(result, Splitter.SplitByNothing(), null, null, ExtraValues.Error),  
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", 
{"id", "start_time", "status", "message_id", "list_id", "subject", "sender_name", "sender_email", "stats_url"},
{"id", "start_time", "status", "message_id", "list_id", "subject", "sender_name", "sender_email", "stats_url"})  

in  

#"Expanded Column1"
За последние два шага у нас появились две новых функции: Table.FromList и Table.ExpandRecordColumn. Позже мы будем дорабатывать код запросов в редакторе запросов. Но я не буду подробно останавливаться на каждой функции, на принципах их работы. Подробную информацию о любой функции можно найти в документации по Power Query. Например, для этих двух: Table.FromList и Table.ExpandRecordColumn.

Итак, табличные данные мы получили. Теперь обращаем внимание на то, что у всех данных в таблице сейчас тип данных Any – буквально «любой». Power BI, конечно, попробует сам угадать тип данных в процессе их использования, но он не всегда делает это успешно. Поэтому, чтобы потом успешно работать с данными, надо сейчас определиться с их типом.
Первым делом мы выделяем всю таблицу Ctrl+A, и в разделе Transform ленты кликаем по кнопке Detect Data Type.
На этом шаге Power BI фиксирует в коде свои догадки относительно природы данных. Их надо откорректировать вручную. Практика показала, что уникальный идентификатор должен быть всегда в текстовом формате, даже если он состоит из одних только цифр.

Меняем тип у соответствующего столбца:
Формат всех остальных данных нас, в принципе, устраивает. Переименуем наш первый запрос. Этим запросом мы получили информацию о кампаниях, вот и назовём наш запрос «Campaigns».
Теперь вернёмся в документацию UniSender и посмотрим, какие ещё данные для нашего отчёта мы можем получить с помощью API. Будем дальше развивать тему кампаний.

Нам нужна подробная статистика о результатах каждой осуществлённой рассылки (статистика доставляемости, кликов, ошибок, отписок и т.д.). В API UniSender такую информацию возвращают методы getCampaignCommonStats и getCampaignAggregateStats.

Вы можете их подробно рассмотреть в документации платформы. Я, в свою очередь, уже это сделал и определился с тем, что лучше подходит метод getCampaignCommonStats (этот метод в API платформы UniSender появился совсем недавно). В документации метода мы видим, что в качестве аргумента, кроме обязательного для всех запросов API-key, мы должны использовать id кампании.

ID всех кампаний мы уже получали ранее при помощи метода getCampaigns, и они находятся в результатах запроса, который мы назвали Campaigns. Получается, теперь нам надо эти данные использовать в новом запросе.

Для начала проверим работу метода в Postman.

Превосходно! Метод getCampaignCommonStats вернул нам всю основную информацию о рассылке.
Теперь используем его в Power BI. Сначала для одной кампании.

Результат мы получили в виде записи.

Назовем этот запрос CampaignCommonStats.

Далее нам надо сделать запрос, который будет содержать внутри себя несколько запросов getCampaignCommonStats (отдельный для каждой компании). С точки зрения кода запроса, нам нужен цикл. Используя графический интерфейс Query Editor, решить эту задачу невозможно, поэтому нам надо самостоятельно написать нужный нам запрос. Но как это сделать?

Гуглим «power query cycle». Первая ссылка в выдаче и комментарии к материалу содержат наш вопрос и ответ на него.
let  

Election.Results = (state) => let    
Source = Web.Page(Web.Contents("http://en.wikipedia.org/wiki/" & state)),  
Data5 = Source{[Caption="Presidential elections results"]}[Data],  
ChangedType = Table.TransformColumnTypes(Data5,{{"Year", type number}, {"Republican", type text}, 
{"Democratic", type text}})  
in  
ChangedType,  
States = Table.FromRows({{"California"}, {"Idaho"}, {"Massachusetts"}}, {"State"}),  
InsertedCustom = Table.AddColumn(States, "Custom", each Election.Results([State])),  
#"Expand Custom" = Table.ExpandTableColumn(InsertedCustom, "Custom", 
{"Year", "Republican", "Democratic"}, {"Year", "Republican", "Democratic"})  

in  

#"Expand Custom"
По аналогии попробуем сделать то же самое с нашими запросами к API UniSender:

Создадим новый пустой запрос.
Исходные данные, включая id кампаний, которые будут аргументами функции цикла, мы получаем с помощью кода запроса Campaigns, значит, мы берём код этого запроса и дорабатываем его вручную. Для лучшего восприятия кода переименуем автоматически созданную переменную «Changed Type» в Campaigns.

Реализация цикла будет выглядеть так:
campaignsWithStat = Table.AddColumn(Campaigns, "stat", each getStat([id]))
Код нашего запроса CampaignCommonStats будет выполняться внутри функции getStat цикла. Аргументом функции будет id кампании. Проверяем. Всё получается!

Теперь сделаем так, чтобы функция getStat возвращала табличные данные. Подставляем полученный код в наш запрос.

Мы получили в новом столбце таблицы, но при попытке раскрыть данные, мы видим, что Power BI предлагает получить новые столбцы Name и Value. Это не совсем то, что нам надо. Нам в таблице нужны значения total, sent, delivered и т.д. Чтобы получить желаемый результат, надо транспонировать результат выполнения функции getStat и использовать бывшие ключи в качестве заголовков.

Всё это можно сделать через интерфейс Query Editor: выбираем запрос CampaignCommonStats и в ленте инструментов нажимаем
1. Transform → Transpose,
2. Home → Use First Row As Headers.

Получившийся код запроса CampaignCommonStats вставляем в функцию getStat. Теперь добавленный столбец stat можно раскрыть.
Переименуем наш новый запрос в allCampaigns. В конечном виде должен получиться примерно такой код запроса allCampaigns:
let // Функция, которая будет циклически выполняться  
getStat = (campaignId) => let  

Source = Json.Document(Web.Contents("http://api.unisender.com/ru/api/getCampaignCommonStats?format=  
json&api_key=5tmjbcir5g4xuew573mxcoh4ypo98xbbbadqgs4a&campaign_id=" & campaignId)),  
result = Source[result],  
#"Converted to Table" = Record.ToTable(result),  
#"Transposed Table" = Table.Transpose(#"Converted to Table"),  
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table")  

in  

#"Promoted Headers",  
Source = Json.Document(Web.Contents("http://api.unisender.com/ru/api/getCampaigns?format=  
json&api_key=5tmjbcir5g4xuew573mxcoh4ypo98xbbbadqgs4a")), result = Source[result],  
#"Converted to Table" = Table.FromList(result, Splitter.SplitByNothing(), null, null, ExtraValues.Error),  
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1",  
{"id", "start_time", "status", "message_id", "list_id", "subject", "sender_name", "sender_email",  
"stats_url"},  
{"id", "start_time", "status", "message_id", "list_id", "subject", "sender_name", "sender_email", "stats_url"}),  

Campaigns = Table.TransformColumnTypes(#"Expanded Column1",{{"id", type text},  
{"start_time", type datetime}, {"status", type text}, {"message_id", Int64.Type},  
{"list_id", Int64.Type}, {"subject", type text}, {"sender_name", type text},  
{"sender_email", type text}, {"stats_url", type text}}),  
campaignsWithStat = Table.AddColumn(Campaigns, "stat", each getStat([id])),  
#"Expanded stat" = Table.ExpandTableColumn(campaignsWithStat, "stat",  
{"total", "sent", "delivered", "read_unique", "read_all", "clicked_unique", "clicked_all", "unsubscribed", "spam"},  
{"total", "sent", "delivered", "read_unique", "read_all", "clicked_unique", "clicked_all", "unsubscribed", "spam"})  
in  
#"Expanded stat"
Сохраним проект в текущем состоянии.

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