Нередко компании, которые обращаются к нам за системой сквозной аналитики, уже используют какие-то инструменты для работы с данными. Иногда это «коробочные» решения. Сразу отказываться от них в пользу кастома не обязательно — на старте их можно интегрировать в новую систему, чтобы сэкономить время и деньги на разработку. Но в этом случае надо быть готовым к тому, что готовые решения не гарантируют точность данных на том же уровне, что специально построенная под вас система аналитики. Именно с этой проблемой мы столкнулись, когда начали строить аналитику для стартапа Refocus: до нас они использовали Roistat, но ошибки в его работе вынудили нас отказаться от него раньше, чем мы планировали.

Способов построить сквозную аналитику, не обращаясь к готовым решениям, немало: можно собрать in-house отдел аналитики или найти специалистов на аутсорсе.

При этом нужно учитывать, что работа целой команды аналитики может стоить порядка 5-7 тысяч евро или 500-700 тысяч рублей в месяц. И это не предел — все зависит от размеров компании-заказчика, состояния аналитики до начала работ и других факторов.

На начальном этапе для многих компаний, у которых объем данных небольшой, такие расходы неоправданы. Из-за этого они часто предпочитают «коробочные» продукты — например, тот же Roistat. Это самое популярное решение на рынке, которое предлагает премиум-тариф с максимумом функционала и полной поддержкой от 1500 евро / 150 тысяч рублей в месяц. Базовые тарифы у них и того дешевле. У этого инструмента больше 200 встроенных интеграций, и не только с популярными Google Analytics или Яндекс Директ, но и с целым списком нишевых кабинетов и систем, о которых вы даже не слышали.

После настройки интеграций с вашими источниками Roistat позволит создать дашборды за пару кликов, а сверху предложит автоматизацию маркетинга и лидогенерации. Конкуренты Roistat вроде OWOX BI или Alytics также предоставляют базовые возможности для аналитики и визуализации. В общем, недорогих решений под любые потребности много.

Хотя “коробка” гораздо лучше бесконечных таблиц и поначалу хорошо оптимизирует работу с данными, она далеко не идеальна. С точки зрения эксперта все минусы универсальных решений видны как на ладони:

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

Кроме того, у вендоров таких готовых решений почти никогда нет ресурсов и возможностей, чтобы подогнать его под специфические требования каждого клиента. Более того, чаще такие “кастомные” требования многократно превосходят стоимость самой коробки (в этом суть готового решения). Если что-то не так на бэке у юзера, если меняется воронка в CRM, если нужно настроить кастомные интеграции — велики шансы, что за это придется доплатить немаленькие деньги, если вендор вообще согласится реализовать эти “специфичные требования”.

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

Как Refocus работал с аналитикой

Один из наших заказчиков, который строил аналитику на самых ранних этапах — EdTech-стартап Refocus. Неудивительно, что когда мы пришли создавать для них кастомную систему, они уже работали с Roistat.

Почему Refocus выбрали именно его? Во-первых, он доступен на русском языке, и это было удобно для команды, в которой много русскоязычных сотрудников. Во-вторых, им нужна была именно система сквозной аналитики, а не CRM с функцией автоматического построения графиков, поэтому альтернативы вроде HubSpot не подходили. В-третьих, все базовые потребности компании он удовлетворял за относительно небольшую сумму. Ну и в-четвертых — многие сотрудники из команды Refocus уже имели опыт работы с Roistat, так что для них это был удобный и знакомый инструмент.

Roistat тянул данные из двух основных рекламных кабинетов — Google и Facebook*. В нем трекались все базовые метрики — бюджеты, показы, клики, конверсия — и отражались на автоматически созданных внутри программы дашбордах.

К сожалению, скрины дашбордов из Roistat у нас не сохранились, но мы нашли для вас пример в интернете — выглядели они примерно так.

Там даже присутствовал свой пользовательский идентификатор — когда мы вводили общий айди студентов, мы думали попробовать использовать его для мэтчинга персональных данных. На практике оказалось, что сопоставление по нему проходит очень неточно, и эту идею оставили.

Стартап рос, число требований и людей увеличилось, поэтому нас позвали строить для ребят кастомную аналитику и более подробные дашборды, и подключились мы.

Как обнаружилась проблема

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

Откуда мы тянули данные для этого дашборда? Конечно, из Roistat, ведь именно там уже лежали готовые маркетинговые показатели. Настройка хранилища и выгрузка данных из источников, которые Roistat не трекал, еще не была закончена, а смотреть на классные, качественные дашборды хотелось уже сейчас.

Например, так выглядел код для выгрузки данных о затратах на рекламу через API:

def get_costs(channel, channel_level, date_from, date_to, source):
         
     data = '{"dimensions": ["marker_level_2", "marker_level_3", "marker_level_4"],\
     "metrics":["visitsCost", "impressions", "visits"],\
     "period":{"from":"' + date_from + '","to":"' + date_to + '"},\
     "filters":[{"field":"' + channel_level + '", "operation":"=", "value":"' + channel + '"}],\
     "interval": "1d"}'
     columns=['dimensions.marker_level_2.value',
             'dimensions.marker_level_2.title',
             'dimensions.marker_level_3.value',
             'dimensions.marker_level_3.title',
             'dimensions.marker_level_4.value',
             'dimensions.marker_level_4.title',
             'dateFrom'
             ]
     headers = {'content-type': 'application/json'}

     response = requests.request("POST", url, data=data, headers=headers)
     df = response.json()['data']

     l = []
     for i in range(len(df)):
         if df[i]['items'] != []:
             l.append(df[i])
     if len(l) > 0:
         res1 = pd.json_normalize(l, 'items', ['dateFrom'])[columns]
         res2 = pd.DataFrame()
         q = pd.json_normalize(l, ['items', 'metrics'])
         for x in ["visitsCost", "impressions", "visits"]:
             res2[x] = q.query('metric_name == @x')['value'].values
         res = pd.concat([res1, res2], axis=1)
         res['source'] = source
         return res
     else:
         return pd.DataFrame()

Подробнее о marker_levels можно прочитать здесь в документации API Roistat.

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

Мы начали искать ошибку с нашей стороны, но убедились, что наш код работает исправно и данные в нашем дашборде один в один совпадают с цифрами в Roistat. Никаких сбоев между источником и дашбордом не происходило.

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

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

Об этом мы и сообщили коллегам, продемонстрировав совпадение. Как раз в этот момент сыграл роль недостаток закрытой системы — посмотреть внутрь Roistat и найти, где именно происходит ошибка, не могли ни мы, ни бэкенд-специалисты Refocus.

Разумеется, кастомная система уже предполагала отключение этого источника в будущем — зачем платный посредник, если мы все равно будем грузить данные напрямую и из рекламных кабинетов, и из кучи других источников?

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

Полностью отказаться от Roistat сразу не получилось, поэтому мы добавили на дашборд фильтр “match with Roistat”.

На нем мы отмечали количество рекламных кампаний, данные по которым расходятся с показателями на нашем дашборде и на Roistat’овском.

Как мы наконец заменили Roistat на кастомную систему и уточнили данные о воронках

Мы сразу написали несколько скриптов для выгрузки тех же самых данных из GA4 и Facebook Ads* через API.
Например, так данные о рекламных показах, затратах и кликах за день в разрезе {campaign, adset, adname} выгружались из кабинета GA4:

def get_google_costs(date_start, customer_id):

    request = RunReportRequest(
        property = f"properties/{property_id}",
        dimensions = [Dimension(name = "date"),
                     Dimension(name = "sessionGoogleAdsCustomerId"),
                     Dimension(name = "sessionGoogleAdsCampaignId"),
                     Dimension(name = "sessionGoogleAdsCampaignName"),
                     Dimension(name = "sessionGoogleAdsAdGroupId"),
                     Dimension(name = "sessionGoogleAdsAdGroupName"),
                     Dimension(name = "sessionGoogleAdsCreativeId")],
        metrics = [Metric(name="advertiserAdCost"),
                   Metric(name="advertiserAdImpressions"),
                   Metric(name='advertiserAdClicks')],
        date_ranges = [DateRange(start_date = date_start, end_date = date_start)],
        dimension_filter = FilterExpression(
            filter = Filter(
                field_name = "sessionGoogleAdsCustomerId",
                string_filter = Filter.StringFilter(value = customer_id),
            )
        ),
    )
    response = client.run_report(request)

    date = map(lambda x: x.dimension_values[0].value, response.rows)
    customer_id = map(lambda x: int(x.dimension_values[1].value), response.rows)
    campname_id = map(lambda x: int(x.dimension_values[2].value), response.rows)
    campname = map(lambda x: x.dimension_values[3].value, response.rows)
    groupname_id = map(lambda x: int(x.dimension_values[4].value), response.rows)
    groupname = map(lambda x: x.dimension_values[5].value, response.rows)
    adnam_id = map(lambda x: int(x.dimension_values[6].value), response.rows)
    visitsCost = map(lambda x: float(x.metric_values[0].value), response.rows)
    impressions = map(lambda x: int(x.metric_values[1].value), response.rows)
    visits = map(lambda x: int(x.metric_values[2].value), response.rows)

    df = pd.DataFrame({'region': customer_id, 'date': date, 'campname': campname, 'groupname': groupname, 'campname_id': campname_id, 'groupname_id': groupname_id, 'adnam_id': adnam_id, 'visitsCost': visitsCost, 'impressions': impressions, 'visits': visits})
    return df

df = pd.DataFrame()

После этого обнаружилось еще одно несовпадение: метрики в самом кабинете были чуть меньше, чем у нас на дашбордах. Отклонение не было критичным, но клиент снова не был уверен в точности наших данных.

Оказалось, что в GA4 по умолчанию включается настройка Thresholding applied. Ее цель — предотвратить возможную идентификацию отдельных пользователей по данным о возрасте, гендере и интересах. В результате ее применения терялись данные о кампаниях с небольшим (меньше 50) количеством кликов. Чтобы этого избежать, нужно было или не трекать эти показатели, или не использовать в дашбордах метрики, касающиеся пользователей напрямую. Roistat тоже не выгружал эти “пограничные” кампании через свою дефолтную интеграцию с GA4, поэтому для Refocus расхождение стало неожиданностью. Зато при прямой выгрузке через API этого ограничения не было, и ускользнувшие поначалу кампании считались, так что данные были более полными без дополнительных ухищрений.

С добавлением все новых и новых источников, конечно, пришлось еще и мэтчить данные рекламных кабинетов о затратах, кликах и показах с записями на вебинары и их посещением, со сделками в CRM — но Roistat этого вовсе не делал, и поэтому не мог дать полной картины о воронках.

После введения прямой выгрузки из рекламных кабинетов данные наконец-то стали полностью совпадать с реальностью, и Roistat отключили, заменив другими источниками. Теперь Refocus могли не платить дважды и получить куда более точный анализ.

Выводы

Кастомная сквозная аналитика — конечно, не панацея, и есть много причин и возможностей обойтись без услуг агентств, которые ее строят. Тем не менее, даже самое популярное на рынке коробочное решение, очевидно, не всегда корректно работает. Обрубать его сразу не обязательно, и ничего не мешает сочетать Roistat и похожие продукты с кастомом. В конце концов, иногда удобно просто достроить интеграции, которые не поддерживает Roistat, и держать дашборды в нем. Но в этом случае важно понимать, что данные там могут быть неполными или некорректными, а сообщение с бэком компании — ненадежным. В результате обнаружить или исправить ошибки может быть сложнее, чем организовать прямую выгрузку данных в хранилище — и здесь кастомная система отлично справится.

*Facebook принадлежит компании Meta, которая признана экстремистской организацией в России.

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

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

Кроме того, что предобработка данных — база для аналитика, без правильного объединения персональных данных в принципе сложно отследить клиентский путь. Значит, нужно настраивать процессы объединения неоднородных персональных данных.

Сегодня в любом клиентском бизнесе воронки регистрации устроены таким образом, что клиенты попадают в базу множеством способов — часто через маркетинговые каналы, которых всегда много (рассылки, реклама, соцсети). В каждом таком канале может быть ссылка на форму подписки, регистрацию на платформе или чат, и один клиент часто проходит все эти этапы. Сразу же образуется путаница в идентификации, которая сильно влияет на качество данных и результаты аналитики, если ее не лечить.

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

Кейс Refocus: данные и путь клиента

Мы разрабатывали кастомную систему сквозной аналитики для эдтех-стартапа Refocus. Данные каждого студента в системы Refocus попадали из нескольких источников и были записаны несколько раз — как минимум при регистрации на курс, при первом входе на образовательную платформу и при входе в чат сопровождения.

В нашем случае мэтчинг был важнее всего по трем источникам из тринадцати:

• amoCRM, где фиксируется весь клиентский путь студента;
• Discord, где проходило сопровождение студентов;
• Thinkific, сама образовательная платформа с курсами.

Остальные источники, с которыми мы работали, либо не содержали данных студентов (например, цифры эффективности работы sales-менеджеров были завязаны на данных сотрудников и трекались через другие системы), либо дублировали информацию из указанных трех.

В Discord и Thinkific данные попадали напрямую, от студентов при регистрации в системах, а затем подтягивались в amoCRM. Основные причины несовпадения клиентских данных как у Refocus, так и в похожих случаях — человеческий фактор (опечатки), наличие у людей более чем одного телефона или адреса почты и ограничения самих платформ, с которых приходят данные: разный заданный формат полей и их количество.

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

Задача и поиск решения

Данные в Refocus мы подгружали в хранилище в BigQuery напрямую из интересующих нас источников (рекламных кабинетов, LMS и т. д.), используя Python. В дальнейшем на этих данных строились дашборды в Tableau.

Обнаружить проблему несложно — при создании хранилища и дальнейшей выгрузке данных из него мы в любом случае чистим датасет от дубликатов и несовпадений.

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

• имя — да, люди иногда вводят разные вариации ФИО (Юлия, Юля и Бля — на деле один человек!);
• телефон — с кодом страны или без, с пробелами, дефисами или слитно;
• электронная почта — длинные строки сложного формата, в которых легко опечататься.

Поначалу, пока количество студентов Refocus было относительно небольшим, достаточно было скриптов, которые объединяли данные по одному из этих полей. В полученных таблицах в Tableau проводился поиск строк с пустым значением в соответствующем поле — и вот видно всех студентов, чьи данные не сошлись.

Количество таких строк было в пределах пары десятков, и трекать и объединять их было несложно вручную. Это делалось прямо в первоисточниках сотрудниками Refocus, которые могли поправить опечатки и ошибки у себя в системах. После этого наш код выгрузки в хранилище перезапускался и тянул уже чистые данные. Если после этого что-то не сходилось, то наши аналитики правили информацию на уровне базы данных.

Но при росте компании в какой-то момент число студентов, потерянных при мэтчинге, могло достигать сотни за месяц. Пока ошибка обнаружится, данные поправят в источниках, а мы перезапустим код выгрузки, могло пройти несколько часов — а это критичный интервал. Да и перезапускать выгрузку каждый день ради нескольких несовпадений — неэффективно. Стало понятно, что масштаб проблемы требует более точного и универсального решения.

Вообще, в такой ситуации возможны несколько вариантов. Можно бесконечно править скрипты мэтчинга, учитывая новые и новые случаи и создавая костыли. А можно, например, настроить алерты в оркестраторе процессов (в нашем случае  — Airflow), которые позволят моментально узнавать о появившемся несовпадении и объединять “потерянные” клиентские сущности по паре за раз. Но это все еще неполная автоматизация, и она только ускоряет, а не упрощает процесс.

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

Реализация решения и рабочий процесс

Чтобы понять масштаб проблемы, мы начали с того, что создали таблицы несовпадающих персональных данных. Для этого мы использовали скрипты на Python. Эти скрипты объединяли данные из разных источников и создавали из них большую сводную таблицу. Для того, чтобы свести данные о студенте в одну сущность, использовался мэтчинг по адресу электронной почты. Мы попробовали мэтчить по имени, фамилии, телефону (который сначала надо было привести к одному формату!) и почте, и именно последний вариант показал самую высокую точность. Возможно, дело в том, что из всех данных почта имеет самый однородный формат, поэтому остается учитывать только опечатки.

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

WITH snapshot_ AS (
      SELECT DISTINCT s.*,
        IFNULL(ae.name, ap.name) as contact_name,
        ap.phone, ae.email,
        split(replace(trim(lower(ae.email)),' ',''),'@')[OFFSET(0)] as email_first_part,
        ai.thinkific_id, ai.intercom_id, ac.student_id
      FROM (
        SELECT *,
          ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY updated_at DESC) as num_,
        FROM `Differture.amocrm_leads_snapshot`
      ) s
      LEFT JOIN (
        SELECT DISTINCT lead_id, contact_id, name, email,
          ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY contact_id) as num1_
        FROM `Differture.amo_emails`
      ) ae using(lead_id)
      LEFT JOIN (
        SELECT DISTINCT lead_id, contact_id, name, phone,
          ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY contact_id) as num2_
        FROM `Differture.amo_phones`
      ) ap using(lead_id)
      LEFT JOIN `Differture.amo_contact_thinkific_intercom_match` ai using(lead_id)
      LEFT JOIN `Differture.AmoContacts` ac on cast(ae.contact_id as string)=ac.amo_id
      WHERE (num_=1 or num_ is null) and (num1_=1 or num1_ is null) and (num2_=1 or num2_ is null)
        and s.pipeline_id in (4920421,5245535) and s.status_id=142 and lower(s.lead_name) not like '%test%'
    )

Как можно заметить, идея сквозного идентификатора здесь уже присутствует — фигурирует student_id. На самом деле, в этой версии скрипта это графа из AmoContacts — таблицы, в которой хранятся только данные из amoCRM. Никаких джойнов по student_id пока не происходит. А происходят по email_first_part, адресу почты до символа @:

select distinct * from th_amo_ds_rf
    left join calendly ce using(email_first_part)
    left join typeform_live tfl on email_first_part=tf_email_first_part
    left join typeform tf using(email_first_part)
    left join csat using(email_first_part)

Первым шагом по практическому введению идентификатора была таблица students_main_info, созданная в BigQuery in-house специалистом Refocus. К сожалению, у нас нет доступа к коду, который использовался для присвоения идентификатора. Зато мы можем показать вид этой таблицы:

В students_main_info хранились данные из нужных источников с общим идентификатором в первой строке, и объединение проходило через сравнение этого поля.

При этом поле student_id использовалось пока не везде; также использовались другие поля этой таблицы — например, thinkific_student_id или discord_user_id.

После выгрузки и мэтчинга данных с помощью students_main_info студентов, которые потерялись при объединении, стало меньше, чем при первой схеме мэтчинга. Так мы убедились, что движемся в верном направлении. Тем не менее, использование одной таблицы, которая содержит больше десятка полей обо всех имеющихся персональных данных, не очень эффективно. Данные в ней уже обработаны скриптом специалиста Refocus, и если надо сверить их с сырыми источниками или ввести новый критерий отслеживания, все придется менять на бэкенде.

Что получилось в итоге

После теста сквозного идентификатора через одну большую таблицу мы продолжили улучшать структуру данных на бэке. Вместо students_main_info усилиями специалиста Refocus появилась подробная сеть более мелких таблиц, которые могут обращаться друг к другу и лежат в одном хранилище с нашими таблицами сырых данных.

Вот так выглядела схема соотношения этих таблиц:

А вот так выглядела основная таблица Students:

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

Остальные таблицы выглядели похоже: всегда было поле с идентификатором и информация о какой-то характеристике студента — когорта, курс, роль в дискорде и так далее.

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

Сначала он отбирал собранные нами данные из amoCRM (amocrm_leads_snapshot) и объединял их с контактной информацией клиентов. Затем в таблицу добавлялось поле student_id и отбирались данные, которые понадобятся нам дальше.

WITH snapshot_ AS (
      SELECT DISTINCT s.*,
        ac.name as contact_name, ac.phone, ac.email,
        split(replace(trim(lower(ac.email)),' ',''),'@')[OFFSET(0)] as email_first_part,
        ac.intercom_id, ac.student_id
      FROM (
        SELECT *,
          ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY updated_at DESC) as num_,
        FROM `Differture.amocrm_leads_snapshot`
      ) s
      LEFT JOIN (
        select cast(al.amo_id as INT64) as lead_id, cast(ac.amo_id as INT64) as contact_id,
          ac.name, emails as email, phone, student_id, ic.intercom_id,
          ROW_NUMBER() OVER(PARTITION BY al.amo_id ORDER BY ac.amo_id) as num1_
        from `Differture.AmoContacts` ac
        left join `Differture.AmoLeads` al on al.amo_contact_id=ac.id
        left join `Differture.IntercomContacts` ic using(student_id)
        , unnest(ac.emails) emails
      ) ac using(lead_id)
      WHERE (num_=1 or num_ is null) and (num1_=1 or num1_ is null)
        and s.pipeline_id in (4920421,5245535) and s.status_id=142 and lower(s.lead_name) not like '%test%'
    )

Теперь при создании общей таблицы о возвратах с данными из amo, Thinkific и Discord объединение проходило через student_id:

th_amo_ds_rf as (
      select distinct * except (channel_id, channel),
        ifnull(channel_id, 'Not in discord') as channel_id,
        ifnull(channel, 'Not in discord') as channel
      from thinkific_amo_refunds
      full outer join discord using(student_id)
    )

Когда объединенные таблицы данных студентов были созданы, получить таблицы несовпадений можно было простой строкой кода в Tableau:

Пустое значение поля student_id означает, что мэтча не случилось — где-то информация расходилась слишком сильно и не подтянулась в таблицы с идентификатором. Раньше, до введения идентификатора, поиск был таким же, но обращался к полям почты, телефона или имени-фамилии.

Ниже можно увидеть таблицу, где данные из Thinkific не совпадали с amoCRM после перехода на Student ID. В этом случае студент есть в LMS, значит, на курсе учится — но его либо нет в системе учета, либо данные в ней разнятся с LMS.

А вот таблица, где данные из Discord не совпадали с amoCRM. Все так же, как выше — студент есть в чатах сопровождения, но не ищется по своим данным в amoCRM.

Оба скриншота показывают количество несовпадений примерно за месяц. Как видно по этим таблицам, количество несовпадений уменьшилось с 80-90 до пары десятков — примерно на 75%. Это позволило сократить количество перезапусков кода выгрузки вручную и уменьшить затраты времени и технических ресурсов на поддержание системы.

Выводы

Сквозной идентификатор — эффективное решение проблемы мэтчинга персональных данных. Он позволяет максимально автоматизировать процесс отслеживания и устранения несовпадений или дубликатов клиентских сущностей при выгрузке данных для анализа. В случаях, когда объем данных в системе невелик, а у компании нет возможности выделить ресурсы на реализацию такого решения, можно воспользоваться и другими вариантами. Например, алерты в оркестраторе процессов хорошо справятся в ситуации, когда объединить данные — вопрос ручного запуска одного скрипта раз в неделю. Но сквозной идентификатор — наверное, самое универсальное из доступных решений, которое покроет большинство ошибок и заметно уменьшит погрешность в качестве данных.

В кастомных системах сквозной аналитики конечный продукт, с которым взаимодействует пользователь — дашборды. Он не видит хранилище, витрины данных, скрипты, а дашборды видит каждый день, поэтому важно сделать их не только функциональными, но и понятными. Не всегда удается сразу нащупать оптимальный вариант, и полезно вести работу поэтапно.

Дело в том, что в современных BI-тулах, которые мы используем — в частности, в популярном Tableau — возможности кастомизации гораздо шире, чем может себе представить сотрудник, мигрирующий с excel-таблиц. Часто пользователь просто не знает, каким вообще может быть дашборд и как составить ТЗ.

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

Именно такая ситуация сложилась у нас в процессе работы с образовательным стартапом Refocus.

Один из главных их дашбордов — обзор SLA (Service Level Agreement). Его пользователи — руководители отдела продаж и сейлз-менеджеры. Именно здесь их интересует прежде всего скорость обработки входящих лидов в зависимости от ряда факторов, от источника лида до региона.

Нормативное значение этого показателя для Refocus — 15 минут, значит, там, где он выше, есть потенциал для оптимизации процессов и роста выручки.

Мы работаем над дашбордами итеративно: интервью с пользователем — прототип — фидбэк — следующая версия — фидбэк, и так далее. Но в случае с SLA после утверждения финальной версии остались нерешенные аналитиками функциональные проблемы.

У заказчика было желание оптимизировать дашборды и бюджет на это, так что мы предложили общую переверстку и подключили к проекту датавиз-эксперта.

Версия 1: с чем мы работаем

Первая версия SLA-дашборда, которой некоторое время пользовался клиент, выглядела так:

В фокусе три графика:

1. Скаттерплот по времени ответа с момента получения лида.
2. Гистограмма по медианному времени ответа.
3. Гистограмма по средней длительности первого звонка с лидом.

Справа — 15 фильтров, от региона и гранулярности до имени сотрудника, работавшего с лидом.

Сверху — много текста, поясняющего тонкости работы с фильтрами по времени.

Задача

Представьте, что ваш технический бэкграунд испарился, и вы на месте пользователя: о чем вам говорят эти графики? Сколько кликов вам нужно совершить, чтобы оценить перформанс подчиненных в вашем отделе? Чтобы сравнить SLA для лидов из разных источников? Ясен ли текст-легенда?

А главное — есть ли у вас понимание, как сделать понятнее и что сказать исполнителю проекта?

У аналитиков в этой области может быть слепая зона — нам-то на картине выше все понятно. А в прямые задачи датавиз-спеца входит проинтерпретировать запрос пользователя в контексте существующего функционала и создать визуализацию на стыке предпочтений заказчика и здравого смысла.

Важно определить и причины этих проблем. Иногда ошибки — просто неудачная попытка выполнить запрос заказчика. Конечно, всегда можно отмести «плохой» запрос и сделать, как правильно — но тогда можно потерять важные для заказчика функции.

Проблемы этого дашборда:

• Основной график — скаттерплот с SLA для всех лидов — выстроен на логарифмической шкале по оси Y без возможности переключиться на линейную. Есть сомнения, что все сейлз-менеджеры, глядя на дашборд, понимают, как работает логарифмическая шкала.
• При выборе гранулярности по неделям масштаб позволяет показать распределение точек на оси X по дням — но они распределены случайно с помощью функции RANDOM().
• Ось Y не синхронизирована между графиками, поэтому сложно оценить связь всех трех значений.
• Невозможно сравнить медианный SLA по следующим параметрам: курс, источник лида, команда, сейлз-менеджер — все они расположены в фильтрах. Сравнение требует скриншотов разных отображений или нескольких вкладок с одним дашбордом.

Установленные причины:

• Логарифмическая шкала: заказчик, который являлся только одним из юзеров дашборда, попросил реализовать логарифмическую шкалу, чтобы смотреть на значения SLA, близкие к нулю. Видимо, это было релевантно для целей именно его отдела.
• Рандомное распределение по оси X: попытка сделать график проще и красивее, потому что скаттерплот уже перегружен информацией, и оценить распределение значений внутри недели — не приоритет.
• Неэффективные фильтры: до ввода дашборда заказчик не знал, какие именно сравнения окажутся ему полезны.

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

Решение

Наш датавиз-специалист рисовал макеты карандашом на бумаге, а после утверждения переносил в Tableau: аналитический функционал и UX-дизайн для готового дашборда можно оптимизировать и без специальных тулов. Здесь хорошо ориентироваться на заказчика: если ему приятнее смотреть на файл в Figma — замечательно. Все понятно и на бумаге — отлично.

Предложенные изменения:

• Фильтры справа сделать бар-чартами вместо выпадающих меню, чтобы разбивку по категориям было сразу видно.
• Реорганизовать дашборд в соответствии с принципами дизайна, учитывающими движение глаз человека слева-направо, сверху-вниз.
• Добавить линейную ось на скаттерплот, сделать именно ее форматом по умолчанию. Оставить возможность переключиться на логарифмическую ось.
• Нормализовать шкалу X, убрав рандомное распределение точек.

Версия 2: рабочая версия после переверстки

Что поменялось:

• В левой части дашборда появилась таблица-фильтр по командам и сейлз-менеджерам. По ней можно оценить показатели работы отдельных сотрудников и отделов и получить общее представление о том, как обстоят дела с SLA в компании — а именно это изначальное назначение дашборда.
• В центральной части дашборда остался скаттерплот с SLA по всем лидам, только теперь формат отображения оси (линейная или логарифмическая) можно переключить.
• Фильтры курсов, источников лидов и категорий превратились в кликабельные бар-чарты в правой части экрана. Они позволяют сразу визуально оценить медианный SLA по этим параметрам.
• Все глобальные фильтры по категориям, которые нет необходимости сравнивать между собой, оказались в верхней части дашборда, там же — фильтр по дате. Теперь пространство экрана организовано эффективнее.

Фидбэк:

• Потерялся график по средней длительности первого звонка.
• На центральном графике исчезли отсечки с медианным SLA.
• Не подписаны столбцы в таблице слева.
• Центральный скаттерплот очень долго загружается при первом открытии дашборда, так как Tableau пытается вывести все лиды из датасета — по умолчанию ни один фильтр не применяется и никак не ограничивает количество информации для отображения.
• Необходимы мелкие правки по оформлению и тултипам.

Версия 3: доработки и брендирование

Что поменялось:

• В таблице слева появились подписи столбцов — теперь половина информации о работе отделов понятна при первом взгляде на дашборд.
• Центральный скаттерплот теперь отображается только после выбора фильтра по команде или конкретному сейлзу — это снижает нагрузку на хранилище и сильно ускоряет загрузку графика.
• Добавились отсечки с медианным SLA за конкретный период (месяц, неделю или день в зависимости от выбранной гранулярности).
• Вернулся график с длительностью первого звонка.
• Ось времени всех трех графиков теперь синхронизирована — не нужно сверять единицы измерения, чтобы выявить общие тренды.

Когда функционал доработан и утвержден, вводится брендирование по корпоративным гайдлайнам. Они у Refocus выглядели так:

Обзор функционала

Итоговый дашборд стал гораздо проще в использовании и настройке, а функционал стал интуитивно понятен.

Так выглядит финальный дашборд при открытии, пока не выбран ни один фильтр:

А так, если выбрать сейлз-менеджера из таблицы слева:

Использование фильтров справа (например, отобразить только лидов, пришедших с вебинаров):

Тултипы со ссылкой на первоисточник на скаттерплоте с лидами (время в них отображается в минутах при выборе любой шкалы):

Наконец, переключение между линейной и логарифмической шкалами:

Этот кейс отлично демонстрирует важность чуткой работы с заказчиком. Всегда важен баланс между экспертностью и ориентацией на пользователя. Конечно, стоит показывать ему разные возможности и предлагать оптимальные с технической точки зрения решения. Но не менее важно доверять клиенту и разбираться в причинах его запросов, чтобы сделать по-настоящему полезно и красиво. Скажем, вдруг он просит логарифмическую шкалу не потому, что не подумал о других, а потому, что без нее в его отделе не обойтись? Внимание к таким моментам — огромный плюс к компетенциям аналитика.

Скажу без лишней скромности — мне есть что рассказать про аналитику и про свою работу. Опытом и кейсами я и моя команда делимся в социальных сетях и в том числе — на LinkedIn, который активно ведем с 2023 года. У нас там три аккаунта: мой личный профиль, страницы Valiotti Analytics и кипрского дата-коммьюнити.

Соцсети ­— это инструмент, эффективность которого надо контролировать:

• следить за приростом подписчиков,
• выявлять тренды, популярные посты и рубрики,
• держать руку на пульсе, чтобы быстро отреагировать, если упадут вовлеченность или охваты.

Это можно делать вручную, но это будет долго и неэффективно, и чем больше становится соцсетей, тем больше времени это отнимает. Да и в конце концов, аналитики мы или нет? Зачем делать руками то, что можно автоматизировать?

Так мы решили создать дашборд.

Наш подход

Делали для себя, но по тому же алгоритму, что для клиентов.

1. Собрали команду из BI-специалиста и дата-инженеров. Подобные дашборды часто создаются силами одного человека, но у нас была возможность выделить нескольких сотрудников, чтобы ускорить работу.
2. Они познакомились с доступными данными, API LinkedIn и статистикой, которую предоставляет сайт. Не надо недооценивать этот этап — перед первым интервью с заказчиком разобраться, с чем предстоит иметь дело. Это поможет сразу понять, какие требования из его ТЗ выполнимы.
3. Затем BI-специалист провел серию интервью с главными пользователями — со мной и с лидом отдела контента. На этом этапе он выясняет контекст использования дашборда — об этом ниже.
4. Работали итеративно: интервью — первый макет дашборда — согласование и внесение правок — следующая версия — и так до создания финального, рабочего варианта.

Что отличает хороший дашборд от плохого?

При создании дашборда главное — понять контекст использования.

• Кто будет пользоваться дашбордом?
• Для каких целей он нужен?
• Какие метрики и зачем пользователь будет отслеживать?
• Как часто пользователь будет обращаться к дашборду?
• Будет ли он использовать дашборд только для своих рабочих задач или собирается показывать его на конференциях?
• Какие нужны показатели, фильтры, периоды и гранулярность?

Важно уметь говорить с заказчиком на его языке и погружаться в его проблемы и задачи. Только так можно создать рабочий и полезный инструмент, а не просто красивый, но неудобный и перегруженный дашборд.

Отличный фреймворк для создания дашбордов создал Роман Бунин — Dashboard Canvas. Он описал алгоритм работы, подготовил примеры вопросов для интервью с заказчиком и шаблоны.

Инжиниринг: два решения для одного проекта

Стек проекта:

• Python,
• Airflow (оркестратор процессов),
• AWS (облако, где это все развернуто),
• PostgreSQL (база данных),
• Tableau (BI-инструмент),
• Selenium (надо было установить драйвер для парсинга, мы использовали Firefox; скрипты писали на Python)
• Fivetran

Из наших аккаунтов два корпоративные, и один личный. В зависимости от типа страниц LinkedIn по-разному «делится» аналитикой.

Данные из корпоративных аккаунтов Valiotti Analytics и Cyprus Data Community мы собираем с помощью Fivetran.

Это инструмент для автоматической выгрузки данных в базу из разных источников. А еще — наши партнеры, с которыми мы давно работаем над разными проектами для наших клиентов. Так что мы и не сомневались, что будем использовать для своего дашборда.

Чтобы настроить передачу данных, Необходимо было просто подключить Fivetran к аккаунту и базе данных. Дальше он автоматически вытягивает данные из соцсети, преобразует и передает базе.

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

1. Подключить к нему Fivetran нельзя — это вариант только для корпоративных страниц.
2. Сервис, специально созданный для сбора информации с личных аккаунтов на LinkedIn, inlytics.io через какое-то время начал требовать заполнить капчу. Для нас была важна полная автоматизация процесса, поэтому такой вариант нам категорически не подходил.
3. Токен к официальному API LinkedIn не дал — их перестали раздавать из-за повышенного спроса.

В итоге пришлось ограничиться тем, что LinkedIn выдает сам — Excel-файл с данными по приросту подписчиков, демографии и взаимодействиям, а также топ-50 самых популярных постов.

• Выгрузку Excel-файлов автоматизировали через связку Airflow + Selenium.
• В Notion ведется таблица с постами — в ней указывается заголовок, тема и ссылка на публикацию.
• Данные выгружаются с помощью Airflow и объединяются их со статистикой из LinkedIn. Чтобы сметчить данные используются ссылки — они в обеих таблицах одинаковые.

Вот такой дашборд получился в итоге.

Оба подхода позволяют видеть отслеживать тренды, вовлеченность, прирост подписчиков. Изначально хотели также собирать данные о числе посетителей, количестве личных сообщений, а еще подглядывать за конкурентами, но LinkedIn такой возможности не дает.

Визуализация в Tableau

Ну вот, контекст использования выяснили, данные собрали, макеты согласовали… пора наконец-то делать дашборд!

Мы выбрали Tableau, во-первых, за богатый функционал. Его возможностей хватит, чтобы воплотить практически любые идеи в жизнь.

Для дашборда для другого нашего проекта мы даже сделали календарь! Это наше изобретение — в Tableau такой функции нет.

Во-вторых, в Tableau приятные решения для визуализации. Даже без специальных знаний о дизайне и датавизе в нем можно собрать визуально приятный, читабельный дашборд.

Ну а у нас-то знания есть. А еще — свой стайлгайд, по которому мы оформляем все свои дашборды. Можете убедиться, взглянув на скрины выше: дашборды в корпоративных цветах не только выглядят хорошо, но и получаются очень наглядными — а значит, функцию свою выполняют.

Выводы

Со стороны может показаться, что задача тривиальная — всего-то визуализировать данные по нескольким аккаунтам в соцсетях. Но она подкинула немало челленджей и несколько раз заставила хорошо задуматься над поиском решения.

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

Что на практике дают эти дашборды?

1. Анализ аудитории. Дашборд собирает данные, откуда меня читают мои подписчики, в каких компаниях и на каких должностях работают. Это позволяет строить гипотезы, какие темы и форматы будут наиболее интересны.
2. Регулярная проверка основных показателей аккаунта: роста аудитории, вовлеченности подписчиков, охватов с детальной статистикой по каждому посту. Если я вижу спад или рост какого-то из них, можно отследить, когда он произошел, и корректировать контент-стратегию. Например, поэкспериментировать с подачей или темами.
3. Выделение трендовых тем. Провожу долгосрочный анализ по 10 темам постов и выясняю, про что аудитории интереснее читать — нейросети, аналитику и данные или же больше про бизнес и предпринимательство. Затем список тем сужается до 5 самых популярных, на которых я концентрируюсь в дальнейшем. Уже успел сделать вывод, что моей аудитории интереснее всего читать про лайфхаки из анализа данных, личные истории и бизнес-кейсы.
4. AB-тест визуального оформления — экспериментирую с оформлением постов и смотрю на реакцию.

Как вам такой проект? Стали бы делать дашборд для сбора данных из соцсетей или вам хватает их встроенной аналитики? Пишите в комментариях!

Реляционные СУБД могут применяться для решения аналитических и транзакционных задач, и сегодня мы хотим рассказать вам о последних.

Транзакция — это несколько операций чтения и записи, сгруппированные в одну логически неделимую операцию. Самый наглядный пример — банковский перевод, для которого нужно совершить несколько действий: снять деньги с одного счета и перекинуть их на другой.

Пример исчерпывающе доносит смысл, но дьявол, как всегда, кроется в деталях, а точнее в деталях реализации. Во-первых, СУБД, поддерживающие транзакции, должны удовлетворять гарантиям функциональной безопасности ACID (это аббревиатура, которая ничего общего с химией не имеет). Во-вторых, есть понятие уровней изоляции транзакции, всего существует 4 различных уровня. Давайте начинать!

A — Atomicity

Как мы уже сказали, ACID — аббревиатура, каждая буква которой обозначает свойство транзакций. Первая буква «А» — atomicity, то есть атомарность.

В большинстве СУБД для того, чтобы начать транзакцию, необходимо выполнить запрос с выражением START TRANSACTION. Все дальнейшие действия будут относиться к начатой транзакции. Чтобы завершить транзакцию, нужно выполнить выражение COMMIT, а если вы хотите отменить все действия транзакции, то можно «откатиться», используя выражение ROLLBACK.

И именно атомарность гарантирует, что ВСЕ операции между START TRANSACTION и COMMIT либо выполнятся, либо не выполнятся — промежуточного состояния не будет.

В качестве примера вспомним опять про банковское приложение:

Николай хочет перевести с одного счета на другой 200 рублей. Для этого сначала с первого счета будет списано 200 рублей, а уже потом на второй счет эти деньги будут зачислены. На первом счету у него 400 рублей, на втором — 500.

Если после первого UPDATE питание в здании отключится и сервер базы данных выключится, то на счетах останется 400 и 500 рублей, как будто никакой транзакции и не было.

C — Consistency

Вторая буква «C» — consistency, то есть согласованность или консистентность.

Согласованность в рамках баз данных можно понимать как выполнение некоторых утверждений относительно данных в таблицах. На примере про перевод между счетами Николая можно сформировать такое утверждение: «При переводе между счетами сумма денег на обоих счетах не должна измениться». Это мы и наблюдаем: до транзакции было 400+500=900 рублей, а после стало 600+300=900 рублей.

I — Isolation

Третья буква — «I» — isolation, то есть изолированность одной транзакции от любой другой.

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

Чтобы разобраться, чем чревато отсутствие изоляции двух транзакций, достаточно в наш пример добавить Маргариту, которая хочет через банкомат внести на счет Николая 300 рублей в момент перевода Николая. Если реализовать ее действия через механизм считывания текущего баланса счета, прибавления к нему 300 рублей и его перезаписи, то появляется риск нарушения консистентности.

Последнее действие, выполненное в рамках начисления через банкомат, перезатрёт все изменения от другой транзакции, что вызовет нарушение целостности данных в базе: у Николая на первом счету будет 600 рублей, а на втором 800 рублей, хотя должно быть 600. Эта ситуация называется «потерей обновления».

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

D — Durability

Последняя буква аббревиатуры отвечает за принцип сохраняемости.

Тут все просто: все транзакции, которые были успешно «закоммичены», не должны быть потеряны ни при каких сбоях. Если база данных реплицируется (запись идет на еще один или несколько работающих БД), то сохраняемость может означать успешную запись в БД на другом сервере. Если база данных не реплицируется, то сохраняемость означает запись на энергонезависимый носитель информации (например, жесткий диск).

Уровни изоляции
READ UNCOMMITED

Самый первый уровень изоляции, который мы рассмотрим — READ UNCOMMITED. То есть он позволяет читать все изменения незавершенных (или «незакоммиченных») транзакций.

Чтение изменений незафиксированных транзакций называется «грязным чтением». Этот уровень изоляции не позволяет изменять внутри транзакции объекты (строки или таблицы или что-то другое, это зависит от конкретной СУБД и движка таблицы), уже измененные другой одновременной транзакцией («грязная запись»). Транзакция просто повиснет в ожидании COMMITа другой транзакции.

Рассмотрим похожий пример, в котором Николай хочет оплатить покупку на 100 рублей с первого счет, а чуть позже Маргарита захотела начислить ему 100 рублей через банкомат. Изначально у него на счетах было по 500 рублей. Тогда схема выполнения будет следующей:

Обновление баланса второй транзакции — это «грязная» запись, именно на этом моменте транзакция и повиснет, изменение произойдет после фиксации перевода Николая, но его можно будет отменить командой ROLLBACK, ведь будет понятно, что произошла «грязная запись».

READ COMMITED

Следующий уровень изоляции READ COMMITED. Как можно догадаться из названия, он позволяет читать только те изменения, которые были зафиксированы (или «закоммичены»).

Этот уровень изоляции, как и read uncommitted, позволяет избежать ситуации «грязной записи», но также позволяет избежать ситуации «грязного чтения». То есть внутри транзакции можно прочитать только те изменения объектов, которые были совершены внутри уже завершенных транзакций.

Вспоминая пример с предыдущей карточки, если бы уровень изоляции был «READ COMMITED», то чтение внутри операции пополнения счета Маргаритой вернуло бы неизмененные балансы счетов Николая (по 500 рублей на каждом, а не 500 и 400, как в предыдущем примере).

Как же эти два уровня изоляции реализованы технически?

Для предотвращения «грязных записей» транзакция перед изменением данных в объекте устанавливает на него блокировку. Таким образом, никакая другая транзакция не сможет изменить содержимое заблокированного объекта, но читать эти изменения можно.

Применять блокировки для предотвращения «грязного чтения» слишком затратно, ведь тогда доступ может быть закрыт для множества читающих транзакций. Для решения этой проблемы после изменения объекта БД запоминает старую версию объекта, которую видят читающие транзакции.

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

REPEATABLE READ

И вот, кажется, что вроде бы достаточно таких уровней для работы с базой данных, ведь никто не увидит незафиксированные изменения других.

Но что, если во время выполнения одной очень длинной транзакции прочитать данные до изменения и после? Тогда можно заметить базу данных в неконсистентном состоянии.

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

Сначала аудитор узнал, что на втором счету Николая 500 рублей (до перевода), затем увидел на втором счету уже 400 рублей, так как транзакция Николая была зафиксирована. У аудитора сложится впечатление, что у Николая 900 рублей суммарно на двух счетах. Но это не так, а возникшая ситуация называется «асимметрией чтения». Пример может показаться надуманным, ведь аудитор может заново прочитать баланс второго аккаунта, но если представить, что длинная транзакция — копирование данных на резервную копию, могут возникнуть большие проблемы.

Во избежание такой ситуации используют уровень изоляции REPEATABLE READ. При таком уровне внутри транзакции чтение происходит из согласованного снимка состояния базы, то есть будут видны только те изменения, которые были зафиксированы на момент начала транзакции.

MVCC

Для реализации такого уровня используется мультиверсионное управление конкурентным доступом (MVCC).

При таком механизме хранится несколько версий каждого зафиксированного объекта, вместе с ней хранятся номер транзакции — txid, которая создала версию объекта, и номер транзакции, которая ее удалила. Каждой новой транзакции присваивается свой номер, и она читает только те версии объектов, номер создания которых меньше собственного.

Идентификатор транзакции аудитора 9. Таким образом, все данные, которые будут прочитаны или изменены, должны иметь идентификатор не больше 9. Txid транзакции Николая — 10, внутри нее было произведено изменение двух объектов, которые были созданы транзакциями с txid 6 и 4. Изменение будет означать удаление этих версий объектов и создание новых. Когда аудитор попытается прочитать баланс первого счета Николая, СУБД увидит, что объект был создан транзакцией с бОльшим txid и возьмет версию, созданную ранее.

SERIALIZABLE

Закончим нашу статью рассказом про самый сильный уровень изоляции — SERIALIZABLE.

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

Этот уровень изоляции может быть реализован тремя способами:

1. Действительно последовательное выполнение транзакций
2. Применение двухфазной блокировки (2PL)
3. Использование сериализуемой изоляции снимков состояния (SSI).

Обо всем по порядку:

1. По-настоящему последовательное выполнение предполагает выполнение всех транзакций одна за другой, в одном потоке. Такой уровень изоляции имеет смысл применять только если транзакции выполняются быстро, чтобы другим не пришлось ждать выполнения одной большой и долгой транзакции. К тому же, если объем данных одной транзакции не помещается в память, то обращение к диску может очень сильно замедлить работу с БД.

2. Двухфазная блокировка — это усиленная версия уже упоминавшейся блокировки. Во время одновременного выполнения нескольких транзакций допускается чтение одного объекта (строки или нескольких строк), но для его изменения нужен монопольный доступ. То есть если транзакция «А» читает строки, которые транзакция «В» хочет изменить, то вторая должна дождаться завершения первой. И наоборот.

3. Сериализуемая изоляция снимков состояния — относительно свежий способ обеспечения сериализуемости транзакций. По сравнению с предыдущими двумя этот метод обеспечивает оптимистичное управление доступом, то есть без блокировок. Все чтение из базы выполняется из согласованного снимка состояния базы данных, а изменения происходят свободно, в расчете, что они не затрагивают объекты других транзакций. При фиксации транзакции происходит проверка, не были ли прочитаны или изменены уже измененные другой транзакцией объекты. Если да, то транзакция прерывается и ее выполнение приходится выполнить еще раз.
   Все транзакции при этом методе должны иметь уровень изоляции Serializable.

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

Контакты автора: LinkedIn | Telegram