Сбор статистики viber
Возник интерес как получить статистику по чату в вайбере. По информации на сайте viber.com статистика предоставляется только для сообществ, в которых не менее 1000 участников.
Далее опишу инструкцию как я получал статистику для чата viber используя файл резервной копии.
1. Резервная копия аккаунта имеет формат базы данных viber.db, для ПК версии находится по пути: C:\Users\<Имя_пользователя>\AppData\Roaming\ViberPC\<№_телефона>.
2. Для чтения файла БД использовал SQL Lite Browser.
3. Открываем файл viber.db в SQL Lite Browser. БД предстает в следующем виде:
4. Основная таблица это Events, все события (по аккаунту) в вайбере имеют сквозную нумерацию (EventID). Другие таблицы имеют связь по внешнему ключу EventID. Информация по сообщениям хранится в таблице Messages, информация по пользователям — в таблице Contact. Я использовал только эти три таблицы.
5. Для того чтобы получить статистику по чату, для начала необходимо узнать идентификатор нужного чата ChatID. Для этого выбираем пункт «Просмотр данных» контекстного меню для таблицы ChatInfo и переходим на вкладку «Данные» для просмотра.
6. По названию чата (атрибут Name) ищем нужный и смотрим Его идентификатор (атрибут ChatID).
7. Аналогично можно получить идентификатор пользователя (ContactID) в таблице Contact через атрибуты Name (как пользователь записан у вас в телефоне) или ClientName (имя пользователя по его аккаунту). Я использовал атрибут ClientName, т.к. не все пользователи у меня есть в записной книжке телефона.
8. Далее получаем нужные данные с помощью формирования запросов Select на вкладке SQL. По каким атрибутам делать зависит от потребностей, для этого необходимо составить соответствующий запрос. Для себя я составил запросы, отраженные в таблице 1.
Таблица 1. Перечень используемых запросов
| № п/п | Текст запроса | Описание |
|---|---|---|
| 1 | Select Count(Messages.EventID) from Messages, Events where Messages.EventID=Events.EventID and Events.ChatID=46 | Количество сообщений в чате |
| 2 | Select count(Messages.EventID), Contact.ClientName from Messages, Events, Contact where Messages.EventID=Events.EventID and Events.ChatID=46 and Events.ContactID=Contact.ContactID group by Events.ContactID order by Contact.ContactID | Количество сообщений по пользователям (возвращает количество событий каждого пользователя) |
| 3 | Select Contact.ContactID, Contact.ClientName from Messages, Events, Contact where Messages.EventID=Events.EventID and Events.ChatID=46 and Events.ContactID=Contact.ContactID group by Events.ContactID order by Contact.ContactID | Список участников в чате |
| 4 | Select * from Messages, Events, Contact where Messages.EventID=Events.EventID and Events.ContactID=Contact.ContactID and Events.ChatID=46 and Events.ContactID=465 | Поиск всех сообщений указанного пользователя |
| 5 | Select count(Messages.EventID), Messages.Type from Messages, Events where Messages.EventID=Events.EventID and Events.ChatID=46 group by Messages.Type order by Messages.Type | количество событий по типу (см. таблицу 2) |
| 6 | Select Count(Messages.Type), Messages.Type from Messages, Events, Contact where Messages.EventID=Events.EventID and Events.ContactID=Contact.ContactID and Events.ChatID=46 and Events.ContactID=482 group by Messages.Type | количество событий по типу для выбранного пользователя |
| 7 | Select Count(Messages.PGIsLiked), Contact.ClientName from Messages, Events, Contact where Messages.EventID=Events.EventID and Events.ChatID=46 and Messages.Type=0 and Messages.PGIsLiked=1 and Events.ContactID=Contact.ContactID group by Contact.ClientName order by Contact.ContactID | Количество поставленных лайков каждым пользователем |
| 8 | Select Sum(Messages.PGLikeCount), Contact.ClientName from Messages, Events, Contact where Messages.EventID=Events.EventID and Events.ChatID=46 and Events.ContactID=Contact.ContactID group by Contact.ClientName order by Contact.ContactID | Количество полученных лайков каждым пользователем |
| 9 | Select Events.TimeStamp from Messages, Events, Contact where Messages.EventID=Events.EventID and Events.ContactID=Contact.ContactID and Events.ChatID=46 and Events.ContactID=460 order by Events.TimeStamp | Активность пользователя (см. п. 11) |
Таблица 2. Описание атрибутов
| № п/п | Наименование атрибута | Описание |
|---|---|---|
| 1 | EventID | Порядковый номер события. Событием может быть сообщение, звонок и т.д. Сквозная нумерация для всех чатов, контактов в рамках аккаунта |
| 2 | Type | Тип события (расшифровал только те, которые были у меня в выбранном чате, на самом деле их больше): 0 – сервисное: изменено сообщение, поставлен лайк 1 – текстовое сообщение 2 — картинка 3 — видео 4 – стикер 9 – ссылка 11 – аудиосообщение 12 — геолокация 15 – опрос, голосование 65 – переименование группы 66 – новый пользователь в чате 67 – группа пользователей в чате 68 – смена аватарки чата 69 — вы удалили пользователя 72 – пользователь удалил сообщение 77 — пользователь покинул группу |
| 3 | ContactID | Идентификатор пользователя |
| 4 | ClientName | Имя пользователя |
| 5 | ChatID | Идентификатор чата |
| 6 | TimeStamp | UNIX-время или POSIX-время (англ. Unix time) — способ кодирования времени, принятый в UNIX и других POSIX-совместимых операционных системах. Моментом начала отсчёта считается полночь (по UTC) с 31 декабря 1969 года на 1 января 1970, в секундах. |
9. После выполнения запроса результат я экспортировал сначала в PDF, а потом в excel (мне так было проще):
10. После использовал данные для построения инфографики в excel, например по запросу №5 (см. таблицу 1):
Примечание 1. Данные разнятся, т.к. чат активен и БД обновляется.
Примечание 2. Уже в excel объединил типы 65…77 в общий (сервисные).
11. Как работать со атрибутом времени (TimeStamp). В БД для привязки событий ко времени используется UNIX-время и для получения данных, связанных с привязкой к дате и времени, возникла необходимость их дополнительной обработки. Приведу пример как я делал это в excel:
a. Сохраняем результаты запроса №9 (см. таблицу 1) в excel (столбец А).
b. В столбце В отбрасываем последние 3 символа (не понял для чего они) чтобы получить время в UNIX-формате. Делаем это с помощью формулы =ЛЕВСИМВ(A2; ДЛСТР(A2)-3). Растягиваем формулу на весь диапазон строк.
c. Столбец С заполняем единицами (счетчик сообщений).
d. В столбце D переводим дату в человекопонятную с помощью формулы =(B2 / 86400) + 25569. Растягиваем формулу на весь диапазон строк.
e. Для подсчета количества сообщений помесячно (ячейки E2:R2) используем формулу =СУММЕСЛИМН($C$2:$C$1434;$D$2:$D$1434; ">="&E$1;$D$2:$D$1434;"<="&КОНМЕСЯЦА(E$1;0)). Растягиваем формулу на весь диапазон столбцов E1:R1.
f. По диапазону ячеек E1:R2 строим гистограмму.
Заключение
Данный способ сбора статистики выполняется в ручном режиме, он ресурсоемкий (по крайней мере по затраченному времени), но как минимум работоспособный. За неимением лучшего, так сказать. Данный способ можно использовать не только для получения статистики по чату, но и по аккаунту вайбер в целом. Исходники не прикрепляю, т.к. все операции я проводил на реальном чате, содержащим персональные данные пользователей, а создавать отдельный чат для теста мне лень. Спасибо за внимание.