Анализ отзывов с banki.ru
Данный материал представлен в информационно-ознакомительных целях.
Список частей:
-
1часть: Сбор данных ссылка -
2часть: Визуализация ссылка -
3часть: Мониторинг загрузок ссылка -
4часть: Создание сайта и RESTAPI ссылка
Короткое вступление:
В этой статье я хочу показать на примере, как можно собрать данные из открытого источника, на примере сайта banki.ru, страницы народного рейтинга банков - отзывов клиентов.
Начнем:
Для начала, давайте определим: какие данных из отзыва нас будут интересовать?
- номер отзыва (id)
- ссылка (url)
- название (заголовок отзыва)
- город
- название банка
- оценка (цифровое значение)
- статус
- логин/почта клиента
- дата создания отзыва
- кол-во комментариев
- сам отзыв
- ответ банка
- дата ответа банка
- ответ администратора
- дата ответа администратора
Вот пример такой страницы, на которой присутствует вся вышеперечисленная информация.
Технологии:
В качестве инструмента для сбора данных будем использовать python3, для хранения PostgreSQL базу данных.
Список дополнительных модулей python:
- psycopg2
- beautifulsoup4
Функция принимает сырой html-текст, преобразовывает его в дерево объектов, которое принимают в качестве аргумента отдельные функции.
def get_review_info(html: str): tree = bs(html, 'lxml') data = { 'id': _get_id_response_from_html(tree), 'link': _get_link_from_html(tree), 'title': _get_title_from_html(tree), 'city': _get_city_from_html(tree), 'bank_name': _get_bank_name_from_html(tree), 'score': _get_score_from_html(tree), 'status': _get_score_status_from_html(tree), 'username': _get_username_from_html(tree), 'create_dt': _get_create_date_from_html(tree), 'comments': _get_comments_count_from_html(tree), 'content': _get_content_from_html(tree), 'bank_answer': _get_bank_answer_from_html(tree), 'bank_answer_date': _get_date_bank_answer_from_html(tree), 'admin_answer': _get_admin_answer_from_html(tree), 'admin_answer_date': _get_date_admin_answer_from_html(tree), 'parse_dt': _get_parse_date(), } return data Следующим этапом будет “перенос” информации в базу данных, для этого будем использовать универсальную функцию на вставку, для обновления реализуем конструкцию insert on conflict:
Создадим таблицу:
create table if not exists home.dt_banki_responses ( id int primary key, link varchar(255) not null, title varchar(255) not null, city varchar(255) not null, bank_name varchar(255) not null, score integer null, status varchar(100) null, username varchar(100) null, create_dt timestamptz not null, comments integer null, content text not null, bank_answer text null, bank_answer_date timestamptz null, admin_answer text null, admin_answer_date timestamptz null, parse_dt date not null ); def insert_on_conflict(table: str, array: List, sql: str) -> None: item = array[0] columns = ', '.join(item.keys()) dd = ["%s" for _ in range(len(item.keys()))] placeholders = ", ".join(dd) row = sql.format(table, columns, placeholders) cursor.executemany(row, [tuple(item.values()) for item in array]) conn.commit() 3-й аргумент функции - это некий темплейт на обновление данных конкретной таблицы, данные будут обновляться только в том случае, если удовлетворяют условиям:
- Смена статуса
- Изменение оценки
- Изменение кол-ва комментариев под отзывом
insert into {} as t ({}) values ({}) on conflict (id) do update set title = excluded.title, city = excluded.city, bank_name = excluded.bank_name, score = excluded.score, status = excluded.status, username = excluded.username, create_dt = excluded.create_dt, comments = excluded.comments, content = excluded.content, bank_answer = excluded.bank_answer, bank_answer_date = excluded.bank_answer_date, admin_answer = excluded.admin_answer, admin_answer_date = excluded.admin_answer_date, parse_dt = excluded.parse_dt where t.status <> excluded.status or (t.score <> excluded.score or t.comments <> excluded.comments) Анализ данных
С начала 2021 года клиентами было создано более 4800 отзывов, давайте проведем легкий анализ:
Рассчитаем редняю и медианную оценку, к сожалению готовой функции median в postgresql нет, поэтому напишем свой вариант.
Напомню, медиана
исключаетвыбросы по значению, простыми словами - она берет значение элемента из середины отсортированного списка
Источник: PostgreSQL Aggregate Median
CREATE OR REPLACE FUNCTION _final_median(numeric[]) RETURNS numeric AS $$ SELECT AVG(val) FROM ( SELECT val FROM unnest($1) val ORDER BY 1 LIMIT 2 - MOD(array_upper($1, 1), 2) OFFSET CEIL(array_upper($1, 1) / 2.0) - 1 ) sub; $$ LANGUAGE 'sql' IMMUTABLE; CREATE AGGREGATE median(numeric) ( SFUNC=array_append, STYPE=numeric[], FINALFUNC=_final_median, INITCOND='{}' ); select avg(score) as avg_valie, median(score) as median_value from home.dt_banki_responses where date(create_dt) >= '2021-01-01'; Результат: 3.8 - среднее, 5 - медиана, весьма неплохо, давайте повторим тот же запрос в разрезе месяца
2021-01 Среднее = 2.2, Медиана = 1
2021-02 Среднее = 2.8, Медиана = 3
2021-03 Среднее = 2.4, Медиана = 1
2021-04 Среднее = 2.2, Медиана = 1
2021-05 Среднее = 2.2, Медиана = 1
2021-06 Среднее = 3.8, Медиана = 5
2021-07 Среднее = 4.5, Медиана = 5
2021-08 Среднее = 2.4, Медиана = 2
2021-09 Среднее = 1.9, Медиана = 1
Странное распределение оценок по месяцам, учитывая что средняя и медиана >3.5, давайте добавим общее кол-во и кол-во в разрезе оценки
select substr(create_dt::varchar,1,7) as month , round(avg(score), 1) as "Среднее" , trunc(median(score)) as "Медиана" , count(*) as "Общее кол-во" , sum(case when score = 1 then 1 else 0 end) as "Оценка 1" , sum(case when score = 2 then 1 else 0 end) as "Оценка 2" , sum(case when score = 3 then 1 else 0 end) as "Оценка 3" , sum(case when score = 4 then 1 else 0 end) as "Оценка 4" , sum(case when score = 5 then 1 else 0 end) as "Оценка 5" , sum(case when score is null then 1 else 0 end) as "Без оценки" from home.dt_banki_responses where date(create_dt) >= '2021-01-01' group by substr(create_dt::varchar,1,7) order by substr(create_dt::varchar,1,7) asc; Результат. Весьма странно, что на июль пришлось больше 50% отзывов (в банке была акция: 500р за положительный отзыв):
Давайте проверим аналогичнную информацию в разразе статуса обращения
select status as "Статус" , count(*) as "Кол-во" from home.dt_banki_responses where date(create_dt) >= '2021-01-01' group by status order by 2 desc; - Не засчитана
2878 - Без статуса
967 - Проверяется
402 - Проблема решена
358 - Зачтено
235
Большинство отзывов в статусе Не засчитана, проверим на какую оценку приходится большинство из них
select status as "Статус" , count(*) as "Кол-во" , sum(case when score = 1 then 1 else 0 end) as "Оценка 1" , sum(case when score = 2 then 1 else 0 end) as "Оценка 2" , sum(case when score = 3 then 1 else 0 end) as "Оценка 3" , sum(case when score = 4 then 1 else 0 end) as "Оценка 4" , sum(case when score = 5 then 1 else 0 end) as "Оценка 5" , sum(case when score is null then 1 else 0 end) as "Без оценки" from home.dt_banki_responses where date(create_dt) >= '2021-01-01' group by status order by 2 desc; Результат: любопытно, ,большая часть оценок 5 находятся в стутусе Не засчитана. 2202 / 2878 * 100 = 70%
Повторим первый запрос на среднюю и медианную оценку, но с фильтром на статусы: Проблема решена и Зачтено
select substr(create_dt::varchar,1,7) as month , round(avg(coalesce(score, 0)), 1) as "Среднее" , trunc(median(coalesce(score, 0))) as "Медиана" , count(*) as "Общее кол-во" , sum(case when score = 1 then 1 else 0 end) as "Оценка 1" , sum(case when score = 2 then 1 else 0 end) as "Оценка 2" , sum(case when score = 3 then 1 else 0 end) as "Оценка 3" , sum(case when score = 4 then 1 else 0 end) as "Оценка 4" , sum(case when score = 5 then 1 else 0 end) as "Оценка 5" , sum(case when score is null then 1 else 0 end) as "Без оценки" from home.dt_banki_responses where date(create_dt) >= '2021-01-01' and status in ('Проблема решена', 'Зачтено') group by substr(create_dt::varchar,1,7) order by substr(create_dt::varchar,1,7) asc; Теперь статистка выглядит не так радужно, среднее ~0.75, медиана ~1
Далее, давайте посмотрим на время отклика сотрудников банка на обращения в статусах ('Проблема решена', 'Зачтено'):
select round( avg(date_part('day', bank_answer_date-create_dt)::int), 1 ) as "Среднее время ответа в днях" , round( median(date_part('day', bank_answer_date-create_dt)::int), 1 ) as "Медианное время ответа в днях" from home.dt_banki_responses where date(create_dt) >= '2021-01-01' and status in ('Проблема решена', 'Зачтено') and bank_answer_date is not null; - Среднее время оклика сотрудника =
2.9дня - Медианное время оклика сотрудника =
1день
Slowly changing dimension (SCD)
Википедия
Медленно меняющиеся измерения (от англ. Slowly Changing Dimensions, SCD) — механизм отслеживания изменений в данных измерения в терминах хранилища данных. Применяется в случае, если данные меняются не очень часто и не по расписанию. Примером могут служить географические данные (местонахождение склада, юридический адрес организации), статус заказчика по программе лояльности или отдел компании, в котором работает её сотрудник.
Анализ актуальных данных это конечно прекрасно, но хотелось бы также понимать, какая оценка, статус или кол-во комментариев было 1 месяц/неделю назад. Для этого реализуем механизм ведения второй таблицы, которая будет хранить в себе историю изменений данных, включая метод и время транзации.
Добавим 2 новых поля: action и valid_from и создадим новую таблицу home.dth_banki_responses:
create table if not exists home.dth_banki_responses ( id int, link varchar(255) not null, ... action char(1) not null, valid_from timestamptz not null ); Данные в нее будут попадать автоматически, реализуем это с помощью функции и триггера.
CREATE OR REPLACE FUNCTION home.banki_hist_insert() RETURNS trigger LANGUAGE plpgsql AS $function$ BEGIN INSERT INTO home.dth_banki_responses (id, link, ... action, valid_from) VALUES (NEW.id, NEW.link, ... 'I', current_timestamp); RETURN NEW; END; $function$; Функция будет выполнятся после вставки в основную таблицу, по аналогии сделаем для обновления и удаления
create trigger banki_hist_insert_trigger after insert on home.dt_banki_responses for each row execute procedure home.banki_hist_insert(); Посмотрим пример изменившихся данных ссылка:
select id , title , create_dt , score , status , comments , action , valid_from , coalesce( lead(valid_from) over(partition by id order by valid_from asc), '2999-12-31' ) as valid_to from home.dth_banki_responses where id = 10556512 order by valid_from asc; Как видно - изменилось название и статус обращения, с помощью функции LEAD добавим правую дату актуальности информации
