Анализ потока почты с помощью средств Power BI

Сегодня я расскажу о том, как вы сможете проанализировать свою почтовую переписку средствами Power BI, правда если в качестве почтовой системы у Вас используется Exchange или Exchange Online. Мы воспользуемся заготовкой, которую мы сделали здесь. Напомню, у нас теперь есть книга Excel с моделью Power Pivot, в котором есть два измерения: Дата и Время. Возьмем эту же книгу за основу и приступим.

image1

Так как мой компьютер находится в домене, то Power Query все определил автоматически и без всяких дополнительных запросов подключился к моему почтовому ящику. В результате в панели навигации я увидел:

image2

Дважды кликаем по ветке Mail. Откроется окно редактирования запроса:

image3

В этом запросе я оставлю только входящую почту. Так как она разбита по нескольким папкам, то я их выберу в фильтре:

image4

Как видно на картинке папка пока видится всего одна, это потому что Power Query подгрузил только часть данных, нажмем на надпись «Загрузить еще» и выберем необходимые папки:

image5

Отлично, теперь в запросе только входящие письма. Теперь удалим те столбцы (с помощью кнопки Удалить столбцы на ленте, либо через контекстное меню), которые нам не потребуются далее. Я удалил все, кроме Sender, DateTimeReceived, Importance, HasAttachments:

image6

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

image7

Теперь я разобью столбец DateTimeReceived на два: Дата и Время. Для этого я создам пользовательский столбец:

image9

И в окне мастера напишу формулу:

image10

А затем переименую столбец в Дата. Аналогично сделаю для второго столбца, формула там будет немного другая:
#time(Time.Hour([DateTimeReceived]), Time.Minute([DateTimeReceived]), 0)
Почему именно такая формула – в моем измерении времени гранулярность минута, т.е. секунды там не учитываются, поэтому мы здесь их тоже обрежем.
Затем удалю столбец DateTimeReceived. Именую набор как Входящая почта, ставлю галку Загрузить в модель данных:

image11

Нажимаю Применить и закрыть. Загрузка пошла:

image12

Далее я сделал точно такой же запрос, но уже на исходящую почту (отфильтровав по папке Отправленные). За исключением одного – теперь поле Sender мне не интересно, я и так знаю, что отправителем являюсь я, поэтому этот столбец я удалил, а оставил DisplayTo, а также построил столбцы Дата и Время на базе столбца DateTimeSent. Назвал набор данных Исходящая почта и загрузил в модель.
Я хочу сделать еще одно дополнительное измерение «Вложения» с двумя параметрами «Есть вложение» и «Без вложения». Создам пустой запрос, в редакторе напишу следующий запрос:
= Table.ExpandRecordColumn(Table.FromList({ [ID=false, Value="Без вложения"], [ID=true, Value="Есть вложение"] }, Splitter.SplitByNothing(), { «Data» }), «Data», {«ID», «Value»}, {«ID», «Value»})
Назову Вложения и загружу в модель.
Переходим в модель – на вкладке Power Pivot нажимаем Управление. Открываем вкладку Входящая почта и переименовываем столбцы: Sender.Name -> Отправитель, Importance -> Важность. Указываем, что поле Дата имеет тип данных Дата. Делаем столбцы HasAttachments, Дата и Время невидимыми в клиентских средствах.

image13

Теперь нам нужно сделать показатель (еще называют мерой), который бы показывал количество писем. Я назову ее просто «Входящие». В области вычисления в любой ячейке нужно написать формулу:
Входящие:=COUNTROWS(‘Входящая почта’)
Выглядит это так:

image14

Повторяем для Исходящей почты.
Затем идем на вкладку Вложения и переименовываем столбец: Value -> Вложение. Указываем, что поле ID имеет тип данных True/False и делаем его невидимым в клиентских средствах.

image15

Нам осталось совсем немного. Теперь мы имеем говоря языком разработчиков кубов таблицы фактов (Входящая почта, Исходящая почта) и таблицы измерений (Дата, Время, Вложения). Теперь нам нужно их связать между собой. Сделать это можно разными способами, я покажу как это сделать «мышкой». Переключаемся в режим представления диаграммы:

image16

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

image17

Далее из контейнера Входящая почта цепляем поле Время и тащим на поле TimeID в контейнере Время (тут следует отметить, что у меня сделано не очень красиво с точки зрения проектирования – обычно мы поля в измерениях и фактах по которым идет связь именуем одинаково, т.е. Дата-Дата, Время-Время и т.д., но тут чтобы не растягивать я оставлю все как есть, просто помните об этом). Затем из контейнера Входящая почта цепляем поле HasAttachments и тащим на поле ID в контейнере Входящие. Повторяем тоже самое для Исходящей почты:

image18

Ну теперь можно и отчеты построить. Возвращаемся в Excel и на вкладке Вставка выбираем Power View:

image19

В результате видим пустой «холст» и элементы как при работе со сводными таблицами:

image20

Я думаю на этот пост хватит технических деталей. Посмотрим отчеты, а как их делать расскажу в других статьях.
Итак, мой первый мини «дашборд»:

image21

Видно, что в 2010 году интенсивность переписки росла от начала года к концу, при этом доля писем с вложениями была достаточно велика.

image22

Сначала меня этот «выстрел» удивил самого, но потом вспомнил, что именно тогда мы начали делать рассылки из CRM и это делал я. Потом передал эту функцию другому. В 2011 году стал меньше посылать письма с вложениями.

image23

Как ни странно, но в 2012 году пик активности выпал на апрель, не припомню каких-то факторов это объясняющих.

image24

В 2013 году в апреле опять видна большая активность, но сентябрь опередил, с каждым годом все меньше и меньше вложений в письмах, может SkyDrive помогает?

image25

Специально снял все фильтры – НИКОГДА я не отправлял письма в 4 и 5 утра, во все остальное время бывало.

image26

В декабре 2013 активен был с утра и уже ближе к концу рабочего дня.

image27

В 2013 году больше всех писем я получил от Саши Пинкуса, он явно был активен. Я ему писал раза в четыре меньше. И внимание! На 11 месте Facebook! И это я еще больше половины писем удалял.

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

leave your comment


четыре − = 1