Разработка отчета, как и обычная разработка, стоит времени программиста. Информация о количестве просмотров отчетов, количестве активных пользователей и многих других метрик, связанных с BI, может повысить эффективность вашей команды аналитики и помочь правильно отслеживать КПД вашей аналитики. Такие метрики каждая команда определяет для себя сама. Чаще всего они связанны с использованием отчетов и пользователями, которые их просматривают. Метрики, которые используются в нашей команде, и как реализовать отчет с ними в Metabase, рассмотрим в этой статье.

Где взять информацию для отчета?

Вся необходимая информация хранится в базе данных самого приложения Metabase. Поэтому сначала нам нужно подключить эту базу к Metabase. Если вы работаете с Metabase, данный этап для вас скорее всего понятен, но все же рассмотрим его. Заходим в Управление -> Базы данных и нажимаем "Добавить базу данных".

Выбираем тип базы данных, которую вы используете для приложения и вводим необходимые данные. Мы используем PostgreSQL для Metabase.

Метрики и SQL-запросы для их получения

1. Количество пользователей

SELECT count(*) as "Количество"
FROM  core_user
where is_active = True

Запрос возвращает нам информацию о количестве активных пользователей. Если убрать условие is_active, то мы получим информацию о количестве всех пользователей, созданных в Metabase.

2. Количество уникальных пользователей в день

SELECT timestamp::date as "Дата", 
count(distinct user_id) as "Количество уникальных пользователей"
FROM public.view_log
INNER JOIN core_user on core_user.id = user_id
INNER JOIN report_dashboard on report_dashboard.id = model_id
where model = 'dashboard'
group by 1
ORDER BY 1 

Запрос возвращает нам информацию о количестве уникальных пользователей в разрезе дней. Эта метрика ещё называется DAU. Для расчета этой метрики мы ищем пользователей, которые просмотрели хотя бы один отчет за день. Немного изменив данный запрос, мы можем также построить WAU и MAU.

3. Количество отчётов

SELECT count(*) as "Количество"
FROM  report_dashboard
where archived = False

Запрос возвращает нам количество отчетов в Metabase, без архивированных отчетов.

4. Количество просмотров отчетов в день

SELECT timestamp::date as "Дата", count(*) as "Количество"
FROM public.view_log
where model = 'dashboard'
GROUP BY 1
ORDER BY 1 

Запрос возвращает нам информацию о количестве просмотров отчетов в разрезе дней. Также можно объединить эти данные с таблицей core_user (INNER JOIN core_user on core_user.id = user_id), если необходима информация по конкретным пользователям.

5. Количество запросов

SELECT count(*) as "Количество"
FROM public.report_card
where archived = False
ORDER BY 1 

Запрос возвращает нам количество запросов в Metabase, без архивированных запросов. Любой отчет в Metabase состоит из некоторого количества запросов.

6. Количество просмотров запросов в день

SELECT timestamp::date as "Дата", count(*) as "Количество"
FROM public.view_log
where model = 'card'
GROUP BY 1
ORDER BY 1 

Запрос возвращает нам информацию о количестве просмотров запросов в разрезе дней. Также можно объединить эти данные с таблицей core_user, как в примере с отчетами.

7. Популярные отчёты

SELECT name as "Имя отчёта", count(*) as "Количество"
FROM public.view_log
INNER JOIN report_dashboard on report_dashboard.id = model_id
where model = 'dashboard' 
GROUP BY 1
ORDER BY 2 desc

Запрос возвращает нам информацию о количестве просмотров каждого отчета, отсортированную от самых просматриваемых к менее просматриваемым.

8. Просмотр отчётов пользователями

SELECT timestamp as "Дата", email, name as "Имя отчёта"
FROM public.view_log
INNER JOIN report_dashboard on report_dashboard.id = model_id
where model = 'dashboard'
ORDER BY 1 desc

Запрос возвращает нам информацию о запуске отчетов и кто их просматривал. Сортировка по дате позволяет увидеть кто и какие отчеты просматривал недавно.

9. Количество просмотров отчётов пользователями

SELECT email, name as "Имя отчёта", COUNT(*)
FROM public.view_log
INNER JOIN report_dashboard on report_dashboard.id = model_id
where model = 'dashboard'
GROUP BY 1,2
ORDER BY 3 desc

Запрос возвращает нам информацию о том, кто и сколько раз просматривал отчеты за все время.

Отчет с данными метриками

Данные запросы необходимо сохранить в Metabase и добавить их в отчет. При сохранении обязательно выбирать базу данных Metabase для них. В итоге у вас получится такой отчёт:

По необходимости можно добавить нужные вам фильтры.

Какие метрики могут быть полезны ещё?

1. Пользователи, которые никогда не входили в систему

select id, email, first_name, last_name
from core_user
where last_login is null;

Запрос возвращает нам информацию о зарегистрированных пользователях, которые никогда не заходили в Metabase.

2. Запросы без отчетов

select q.id, q.name, q.description, q.display, q.query_type
from report_card q
LEFT JOIN report_dashboardcard rdc on rdc.card_id = q.id
where q.archived = 0 and rdc.card_id is null;

Запрос возвращает нам информацию о запросах, которые не добавлены ни в один отчет.

3. Распределение пользователей по группам

SELECT name, count(*)
FROM permissions_group_membership
INNER JOIN permissions_group on group_id = permissions_group.id
GROUP BY 1
ORDER BY 2 DESC

Запрос возвращает нам информацию о том, сколько пользователей состоит в каждой из групп доступов, созданных в Metabase.

Заключение

Данные запросы помогут построить вам информативный отчет, который позволит оценить, что происходит с вашими отчетами после публикации.