Анализ отзывов с banki.ru [Часть 1] История изменений в SCD таблице

7 minute read
code
,

img

Данный материал представлен в информационно-ознакомительных целях.

Список частей:

Короткое вступление:

В этой статье я хочу показать на примере, как можно собрать данные из открытого источника, на примере сайта banki.ru, страницы народного рейтинга банков - отзывов клиентов.

Начнем:

Для начала, давайте определим: какие данных из отзыва нас будут интересовать?

  • номер отзыва (id)
  • ссылка (url)
  • название (заголовок отзыва)
  • город
  • название банка
  • оценка (цифровое значение)
  • статус
  • логин/почта клиента
  • дата создания отзыва
  • кол-во комментариев
  • сам отзыв
  • ответ банка
  • дата ответа банка
  • ответ администратора
  • дата ответа администратора

Вот пример такой страницы, на которой присутствует вся вышеперечисленная информация.

Технологии:

В качестве инструмента для сбора данных будем использовать python3, для хранения PostgreSQL базу данных.
Список дополнительных модулей python:

  1. psycopg2
  2. 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р за положительный отзыв):

img

Давайте проверим аналогичнную информацию в разразе статуса обращения

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%

img

Повторим первый запрос на среднюю и медианную оценку, но с фильтром на статусы: Проблема решена и Зачтено

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

img

Далее, давайте посмотрим на время отклика сотрудников банка на обращения в статусах ('Проблема решена', 'Зачтено'):

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 добавим правую дату актуальности информации

img