Дашборд маркетинга МРТ в Google Sheets: готовая схема

Этот материал — готовая схема для построения дашборда маркетинга МРТ в Google Sheets без дорогих BI. Мы шаг за шагом разберём, какие данные собирать, как их сводить, какие метрики считать и как настроить автообновление. В итоге у вас будет понятный шаблон: от UTM-меток до ROMI и загрузки томографа — всё в одном окне решений.

Архитектура данных и источники

Что считаем и зачем

Цель дашборда — связать рекламу с реальным доходом от МРТ и загрузкой аппарата. Поэтому в фокусе:

Воронка: клик → звонок/лид → запись → явка → выполненное исследование → выручка/маржа.
Стоимость: CPL, CPA (за запись), CPShow (за явку), CPScan (за выполненное исследование), CAC.
Эффективность каналов: контекст, таргет, SEO, реферальные врачи, прямые, агрегаторы.
Медицинские KPI: загрузка томографа (%), no-show, средний чек, допродажи (контраст, заключение срочно).
Финансы: ROMI/ROI, маржинальность по исследованиям, LTV повторных пациентов.

Карта листов Google Sheets (готовая схема)

Config — справочники: каналы, источники, UTM-метки, соответствие процедур, прайс, маржа, смены и слоты аппарата.
Ads_Raw — расходы и клики из Google Ads, Яндекс РСЯ, Meta/ВК (дата, кампания, adset, стоимость, показы, клики).
GA_Raw — сессии/конверсии из GA4 (source/medium, utm_campaign, device, geo).
Calls_Raw — коллтрекинг (дата, звонок, длительность, статус, DNI, source/utm).
CRM_Leads — лиды/заявки (дата, канал, UTM, статус, менеджер, стоимость, пациент).
CRM_Appointments — записи (дата записи, дата/время визита, кабинет, исследование, филиал, статус явки).
MRI_Studies — выполненные исследования (код процедуры, длительность, контраст да/нет, чек, скидка, доначисления).
Finance — платежи/выручка, себестоимость, переменные издержки.
Model — нормализация, объединение данных, вычисляемые поля.
Dashboard — итоговые метрики, сводки, графики и фильтры (период, канал, филиал, устройство).

Источники и интеграции

Google Ads/GA4: аддон Google Analytics и Google Ads или BigQuery → Apps Script → Sheets.
Meta/ВК: Supermetrics/OWOX/Adverta или плановый импорт CSV.
Коллтрекинг: Calltouch/Calltracking/UIS по API → Apps Script/аддон.
CRM: amoCRM/Битрикс24/Медицинская ИС (MedWork, Инфоклиника) — выгрузка по API/CSV.
Финансы: 1С/МедИС (CSV, API). Минимум: дата оплаты, сумма, исследование, филиал.

Стратегия UTM:

utm_source — канал (google, yandex, meta, vk, seo, referral_doctor).
utm_medium — тип трафика (cpc, cpm, organic, referral, email).
utm_campaign — исследование/филиал/гео (mri-spine_msk_south).
utm_content — креатив/оффер/аудитория (contrast_999rub_lookalike).
utm_term — ключевая фраза (для поиска).

Связка сущностей: cookie/Client ID → коллтрекинг → CRM лид → запись → явка → исследование → оплата. Храним связки ID (gclid, yclid, fbclid, call_id, lead_id, appointment_id, study_id, payment_id).

Расчёты, модели и визуализация

Нормализация и объединение

Приведение источников к единому словарю каналов: в Config задайте правила сопоставления (regex).
Пример вычисляемого канала в Model: =IFS(REGEXMATCH(A2,»google|gclid»),»Paid Google», REGEXMATCH(A2,»yandex|yclid»),»Paid Yandex», REGEXMATCH(A2,»(facebook|meta|instagram)»),»Paid Meta», REGEXMATCH(A2,»organic»),»SEO», TRUE,»Other»)
Сведение затрат: =QUERY({Ads_Raw!A:F; Another_Ads!A:F},»select Col1,sum(Col6) … group by Col1″,0)
Матчинг лида к источнику: по UTM/Call_ID; fallback — по номерам динамических номеров и времени звонка.

Ключевые метрики (формулы ядра)

CPL: =Расходы_канала / Кол-во_лидов
CPA (за запись): =Расходы_канала / Кол-во_записей
CPShow (за явку): =Расходы_канала / Кол-во_явок
CPScan (за выполненное исследование): =Расходы_канала / Кол-во_исследований
CAC: =Расходы_канала / Кол-во_уникальных_пациентов
Средний чек: =SUM(Выручка)/COUNT(Исследования)
ROMI: =(Выручка, атрибутированная маркетингу − Расходы_маркетинга) / Расходы_маркетинга
Загрузка томографа, %: =Выполненные_минуты / Доступные_минуты_слотов (учитывайте профилактику и простои).
No-show: =Неявки / Все_записи
Конверсии в воронке: =Записи/Лиды, =Явки/Записи, =Исследования/Явки.

Примеры диапазонных формул:

Подсчитать записи по каналу и периоду: =COUNTIFS(CRM_Appointments!B:B, «>=»&D1, CRM_Appointments!B:B, «<="&E1, CRM_Appointments!G:G, "Запланировано/Выполнено", CRM_Appointments!K:K, A2)
Расходы по каналу: =SUMIFS(Ads_Raw!F:F, Ads_Raw!A:A, «>=»&D1, Ads_Raw!A:A, «<="&E1, Ads_Raw!C:C, A2)
Выполненные исследования по типу: =SUMIFS(MRI_Studies!H:H, MRI_Studies!A:A, «>=»&D1, MRI_Studies!A:A, «<="&E1, MRI_Studies!C:C, B2)

Атрибуция и соответствия

Модель по умолчанию: last non-direct click — последний недиректный источник приписывается записи/исследованию.
Продвинутая: time-decay между первым кликом и звонком; офлайн-рефералы врачей — отдельным каналом с ручной атрибуцией.
Матчинг исследования к лиду: по patient_id и близкой дате (±14 дней от записи), при множестве исследований — делим выручку пропорционально.

Визуальные блоки дашборда

Шапка: период, филиал, аппарат, канал, тип исследования.
KPI-панель: CPL, CPA, CPScan, ROMI, загрузка, no-show, средний чек.
Воронка: столбцы Лиды → Записи → Явки → Исследования с конверсиями.
Каналы: расходы, выручка, прибыль, ROMI; топ-кампании/креативы.
Медицина: загрузка по часам/дням, длительность слотов, перераспределение исследований для оптимальной загрузки.
Операционка: пропущенные звонки, SLA call-центра, среднее время до записи.

Автоматизация, контроль качества и запуск

Автообновление и производительность

Обновление данных: аддоны по расписанию + Apps Script (triggers) для API/CSV. Газеткуйте данные в отдельный лист Model, а в Dashboard тяните только агрегаты.
Оптимизация: заменяйте массив формул на QUERY/INDEX/MATCH, используйте ARRAYFORMULA для колонок, ограничивайте диапазоны.
Архивирование: ежемесячный срез факта расходов/выручки, чтобы снизить нагрузку и избежать пересчётов.

Контроль качества данных

Валидатор UTM: список лидов без utm_source/utm_medium — отправка в дообогащение.
Дубликаты: проверка уникальности по phone+дата и appointment_id.
Согласование сумм: сверка выручки MRI_Studies с Finance (расхождение ≤ 1-2%).
Сквозные ID: храните связки call_id → lead_id → appointment_id → study_id → payment_id.

Шаблон запуска за 1–2 дня

Создайте листы по карте и вставьте справочники в Config: процедуры, прайс, филиалы, аппараты, каналы.
Подключите источники: Ads/GA4 аддоны; CRM/коллтрекинг через Apps Script или CSV-импорт.
Скопируйте формулы из раздела Model: нормализация каналов, расчёт воронки, финансовые метрики.
Соберите сводки в Dashboard: KPI-ряд, воронка, каналы, загрузка, операционные метрики.
Проведите контроль качества и тестовый прогон за прошлый месяц; утвердите ROMI и пороги алертов.

Практические подсказки:

Храните прайс и маржу отдельно по исследованиям и надбавкам (контраст, срочность).
Сегментируйте по источникам пациентов: реклама vs. рефералы врачей vs. повторные.
Стройте отчёт по временам звонков и слотам — это быстро снижает no-show и повышает загрузку.

Мини-FAQ

Если нет коллтрекинга? Запустите хотя бы статические номера по каналам и ручной реестр звонков; точность снизится, но дашборд заработает.
Как учесть скидки и акции? Добавьте поле промокода/источника скидки в CRM_Appointments и тяните в Finance.
Как считать повторные визиты? Метка first/return по patient_id и 90-дневному окну; LTV — сумма оплат за 180–365 дней.

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

Ошибки, которых стоит избегать

Несогласованные справочники исследований между CRM и прайсом — ломают ROMI по процедурам.
Смешение каналов из-за неверных UTM — вводите единые пресеты и блокируйте ручное редактирование.
Отсутствие контроля no-show — искажает конверсии и загрузку аппарата.
Нулевая связь с финансами — без платежей дашборд остаётся о расходах, а не о прибыли.

Итоговая структура показателей в Dashboard

Финансы: выручка, маржа, ROMI, CAC.
Маркетинг: расходы, CPL/CPA/CPScan, конверсии по этапам.
Операции: загрузка, no-show, SLA звонков, среднее ожидание записи.
Срезы: канал, кампания, филиал, исследование, устройство, время суток/день недели.

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

Заключение

Вы получили готовую схему дашборда маркетинга МРТ в Google Sheets: структура листов, источники, формулы, атрибуция, визуализация и контроль качества. Настройте UTM, подключите CRM/коллтрекинг, перенесите расчёты в Model, а в Dashboard держите ключевые KPI. Такой дашборд связывает рекламу с выручкой и загрузкой аппарата — и даёт быстрые, уверенные решения.

Дашборд маркетинга МРТ в Google Sheets без BI