Как анализировать личные расходы с помощью Airtable R и PowerBI

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

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

Как выбрать, где собирать и анализировать данные

Для ввода и хранения данных выбор стоял между Google Forms и Google Sheets или Airtable. Airtable мне понравился больше — удобством, разнообразием функций, а еще он круто адаптирован под смартфоны.

Мои требования к системе:

• удобно вводить информацию с возможностью самостоятельно называть категории;
• доступное хранение (возможность подключиться через API) и соблюдение структуры данных при хранении;
• понятные отчеты — с возможностью кастомизации и построения прогнозов;
• приоритет использования на мобильных устройствах (одно из самых важных требований).

В рамках своей системы я создал базу в Airtable и таблицы Spends, Revenue, Desires с такой структурой:

Еще один плюс Airtable — фиксирование даты создания записи. Ее можно получить при выгрузке из API.

В таблицах я создал формы, сформировал ссылку, и сделал ярлык на телефоне.

Получилось почти «приложение». После нажатия на ярлык сразу открывается форма, которая готова к заполнению и отправке. Можно отправлять несколько форм сразу.

Как пользоваться?

Я заполнял форму каждый раз, когда что-то покупаю — особенно удобно платить через PayPass и сразу фиксировать сумму в форме.

После запуска сбора информации я занялся инструментами выгрузки. Систему визуализации выбрал PowerBI, потому что для меня она самая понятная и удобная.

Изначально пробовал использовать только PowerQuery, но столкнулся с пагинацией при выгрузке. Победить эту проблему решил средствами R, для этого я использовал одноименный пакет airtable.

Этого кода мне хватило, чтоб получить, все что нужно в PowerBI:

library(airtabler)Sys.setlocale("LC_ALL", "russian")setwd(
'C:/Users/Administrator/Documents/my_project') 
#свой путь к папкеSys.setenv(AIRTABLE_API_KEY="key000000000000") # 
свой токенprojects <-airtable(base = "appqSx0000000001",
 # id базы из строки браузераtables = 
c("spends", "revenue", "desires"))spends <- projects$spends$select_all()

Что я сделал дальше:

• сформировал модель данных;
• создал каталог дат в DAX и связал все таблицы через поле с датой;
• написал меры;
• построил визуализации.

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

Мне стало понятно, куда и в каком количестве утекают деньги.

revenue_plan =DIVIDE(/*Общая сумма денег с учетом расходов*/((DIVIDE([Spends], 
COUNTROWS(CALENDAR(FIRSTDATE(DISTINCT('spends'[createdTime])),LASTDATE(DISTINCT('spends'
[createdTime])))),0)) // средний показатель в день по расходам*COUNTROWS(CALENDAR(TODAY(),
LASTDATE(desires[final_date]))) // дней до цели)+[desires] // +
 стоимость цели,COUNTROWS(SUMMARIZE(ADDCOLUMNS(CALENDAR(TODAY(),
LASTDATE(desires[final_date])),"Month", 
MONTH([Date])),[Month],"daySum",COUNT([Date])))// колличество месяцев до цели,0)

Потом я подумал, как управлять этим всем, ведь надо выполнять поставленные задачи перед собой. Чтобы понять, сколько мне нужно в месяц с учетом расходов, я написал формулу:

В результате я понял, какие расходы и насколько сильно влияют на достижение цели в назначенный срок.

Уже дальше можно с этим работать, пытаться оптимизировать расход в рамках каждой категории, планировать траты на месяц и контролировать свои результаты в PowerBI.

Мнение авторов гостевого поста может не совпадать с позицией редакции и специалистов агентства Netpeak.