build_collection
Export JSON
Формат совместим с будущим HTTP-импортом. database_id = 2 (1 зарезервирован как Sample).
11 readyИсточник — только marts/core; без live API платформ
{
"collection": "AME — Pulse",
"database_id": 2,
"dashboards": [
{
"key": "D-01",
"name": "D-01 — Пульс юнит-экономики",
"title": "Пульс юнит-экономики",
"audience": "CMO/CEO",
"purpose": "Ежедневный снимок ROMI/POAS, выручки и прибыли без сырых API платформ.",
"flags": {
"requires_maturity": true,
"has_inflation_toggle": true,
"has_change_markers": true
},
"cards": [
{
"panel": "romi_poas",
"title": "ROMI / POAS",
"kind": "metric",
"display": "scalar",
"status": "ready",
"description": "Периодный ROMI и POAS по витрине unit_econ_daily.",
"layout": {
"row": 0,
"col": 0,
"size_x": 6,
"size_y": 4
},
"sql_key": "D-01.romi_poas",
"dataset_query": {
"type": "native",
"native": {
"query": "SELECT\n round(if(sum(cost_rub) = 0, NULL, sum(revenue_rub) / sum(cost_rub)), 2) AS romi,\n round(if(sum(cost_rub) = 0, NULL, sum(gross_profit_rub) / sum(cost_rub)), 2) AS poas\nFROM marts.unit_econ_daily\nWHERE date BETWEEN toDate({{day_from}}) AND toDate({{day_to}})\n AND maturity_share >= {{min_maturity}}\n",
"template-tags": {
"day_from": {
"id": "day_from",
"name": "day_from",
"display-name": "Day From",
"type": "date",
"required": false
},
"day_to": {
"id": "day_to",
"name": "day_to",
"display-name": "Day To",
"type": "date",
"required": false
},
"min_maturity": {
"id": "min_maturity",
"name": "min_maturity",
"display-name": "Min Maturity",
"type": "number",
"required": false
}
}
},
"database": 2
},
"sql_smoke": "SELECT\n round(if(sum(cost_rub) = 0, NULL, sum(revenue_rub) / sum(cost_rub)), 2) AS romi,\n round(if(sum(cost_rub) = 0, NULL, sum(gross_profit_rub) / sum(cost_rub)), 2) AS poas\nFROM marts.unit_econ_daily\nWHERE date >= today() - 30\n AND maturity_share >= 0.8\n",
"tables": [
"marts.unit_econ_daily"
]
},
{
"panel": "revenue_profit",
"title": "Выручка и валовая прибыль",
"kind": "series",
"display": "line",
"status": "ready",
"description": "Дневной ряд revenue / gross profit / cost.",
"layout": {
"row": 0,
"col": 6,
"size_x": 18,
"size_y": 4
},
"sql_key": "D-01.revenue_profit",
"dataset_query": {
"type": "native",
"native": {
"query": "SELECT\n multiIf(\n {{grain}} = 'week', toStartOfWeek(date, 1),\n {{grain}} = 'month', toStartOfMonth(date),\n date\n ) AS period,\n sum(revenue_rub) AS revenue_rub,\n sum(gross_profit_rub) AS gross_profit_rub,\n sum(cost_rub) AS cost_rub\nFROM marts.unit_econ_daily\nWHERE date BETWEEN toDate({{day_from}}) AND toDate({{day_to}})\n AND maturity_share >= {{min_maturity}}\nGROUP BY period\nORDER BY period\n",
"template-tags": {
"grain": {
"id": "grain",
"name": "grain",
"display-name": "Grain",
"type": "text",
"required": false
},
"day_from": {
"id": "day_from",
"name": "day_from",
"display-name": "Day From",
"type": "date",
"required": false
},
"day_to": {
"id": "day_to",
"name": "day_to",
"display-name": "Day To",
"type": "date",
"required": false
},
"min_maturity": {
"id": "min_maturity",
"name": "min_maturity",
"display-name": "Min Maturity",
"type": "number",
"required": false
}
}
},
"database": 2
},
"sql_smoke": "SELECT\n date AS period,\n sum(revenue_rub) AS revenue_rub,\n sum(gross_profit_rub) AS gross_profit_rub,\n sum(cost_rub) AS cost_rub\nFROM marts.unit_econ_daily\nWHERE date >= today() - 30\n AND maturity_share >= 0.8\nGROUP BY period\nORDER BY period\n",
"tables": [
"marts.unit_econ_daily"
]
},
{
"panel": "new_vs_returning",
"title": "Новые vs возвращающиеся",
"kind": "series",
"display": "bar",
"status": "todo",
"description": "Сплит новых и возвращающихся клиентов. SQL — TODO.",
"layout": {
"row": 4,
"col": 0,
"size_x": 8,
"size_y": 8
},
"sql_key": "D-01.new_vs_returning",
"dataset_query": null,
"sql_smoke": null
},
{
"panel": "trend_to_target",
"title": "Тренд к цели",
"kind": "series",
"display": "line",
"status": "todo",
"description": "Факт vs target_revenue. SQL — TODO.",
"layout": {
"row": 4,
"col": 8,
"size_x": 16,
"size_y": 8
},
"sql_key": "D-01.trend_to_target",
"dataset_query": null,
"sql_smoke": null
},
{
"panel": "top_deltas",
"title": "Топ дельт",
"kind": "table",
"display": "table",
"status": "todo",
"description": "Крупнейшие отклонения юнит-экономики. SQL — TODO.",
"layout": {
"row": 12,
"col": 0,
"size_x": 24,
"size_y": 6
},
"sql_key": "D-01.top_deltas",
"dataset_query": null,
"sql_smoke": null
}
]
},
{
"key": "D-02",
"name": "D-02 — Операционный контроль кампаний",
"title": "Операционный контроль кампаний",
"audience": "Маркетолог-оператор",
"purpose": "Pacing, обучение, гео-CPA и очередь согласований на уровне кампаний.",
"flags": {
"requires_maturity": false,
"has_inflation_toggle": false,
"has_change_markers": true
},
"cards": [
{
"panel": "pacing",
"title": "Pacing бюджета",
"kind": "series",
"display": "bar",
"status": "ready",
"description": "Дневной spend vs план из core.fct_stats_daily.",
"layout": {
"row": 0,
"col": 0,
"size_x": 12,
"size_y": 6
},
"sql_key": "D-02.pacing",
"dataset_query": {
"type": "native",
"native": {
"query": "SELECT\n multiIf(\n {{grain}} = 'week', toStartOfWeek(date, 1),\n {{grain}} = 'month', toStartOfMonth(date),\n date\n ) AS period,\n sum(cost_rub) AS spend_rub,\n sum(budget_plan_rub) AS budget_plan_rub,\n if(sum(budget_plan_rub) = 0, NULL, sum(cost_rub) / sum(budget_plan_rub)) AS pacing\nFROM core.fct_stats_daily\nWHERE date BETWEEN toDate({{day_from}}) AND toDate({{day_to}})\nGROUP BY period\nORDER BY period\n",
"template-tags": {
"grain": {
"id": "grain",
"name": "grain",
"display-name": "Grain",
"type": "text",
"required": false
},
"day_from": {
"id": "day_from",
"name": "day_from",
"display-name": "Day From",
"type": "date",
"required": false
},
"day_to": {
"id": "day_to",
"name": "day_to",
"display-name": "Day To",
"type": "date",
"required": false
}
}
},
"database": 2
},
"sql_smoke": "SELECT\n date AS period,\n sum(cost_rub) AS spend_rub,\n sum(budget_plan_rub) AS budget_plan_rub,\n if(sum(budget_plan_rub) = 0, NULL, sum(cost_rub) / sum(budget_plan_rub)) AS pacing\nFROM core.fct_stats_daily\nWHERE date >= today() - 30\nGROUP BY period\nORDER BY period\n",
"tables": [
"core.fct_stats_daily"
]
},
{
"panel": "learning_status",
"title": "Статус обучения",
"kind": "table",
"display": "table",
"status": "todo",
"description": "Learning phase кампаний. SQL — TODO.",
"layout": {
"row": 0,
"col": 12,
"size_x": 12,
"size_y": 6
},
"sql_key": "D-02.learning_status",
"dataset_query": null,
"sql_smoke": null
},
{
"panel": "cpc_cpa_geo",
"title": "CPC / CPA по гео",
"kind": "series",
"display": "bar",
"status": "todo",
"description": "Гео-разрез стоимости. SQL — TODO.",
"layout": {
"row": 6,
"col": 0,
"size_x": 12,
"size_y": 6
},
"sql_key": "D-02.cpc_cpa_geo",
"dataset_query": null,
"sql_smoke": null
},
{
"panel": "anomaly_flags",
"title": "Аномалии",
"kind": "table",
"display": "table",
"status": "todo",
"description": "Флаги аномалий расхода/конверсий. SQL — TODO.",
"layout": {
"row": 6,
"col": 12,
"size_x": 12,
"size_y": 6
},
"sql_key": "D-02.anomaly_flags",
"dataset_query": null,
"sql_smoke": null
},
{
"panel": "approvals_queue",
"title": "Очередь согласований",
"kind": "table",
"display": "table",
"status": "todo",
"description": "Изменения, ждущие апрува. SQL — TODO.",
"layout": {
"row": 12,
"col": 0,
"size_x": 12,
"size_y": 6
},
"sql_key": "D-02.approvals_queue",
"dataset_query": null,
"sql_smoke": null
},
{
"panel": "drilldown",
"title": "Drill-down кампании",
"kind": "table",
"display": "table",
"status": "todo",
"description": "Разрез campaign → adgroup → ad. SQL — TODO.",
"layout": {
"row": 12,
"col": 12,
"size_x": 12,
"size_y": 6
},
"sql_key": "D-02.drilldown",
"dataset_query": null,
"sql_smoke": null
}
]
},
{
"key": "D-03",
"name": "D-03 — Когорты и лаги",
"title": "Когорты и лаги",
"audience": "Аналитик",
"purpose": "Лаг конверсии, когортная таблица и история рестейтментов.",
"flags": {
"requires_maturity": true,
"has_inflation_toggle": false,
"has_change_markers": true
},
"cards": [
{
"panel": "lag_distribution",
"title": "Распределение лагов",
"kind": "series",
"display": "bar",
"status": "todo",
"description": "Горизонты дозревания конверсии. SQL — TODO.",
"layout": {
"row": 0,
"col": 0,
"size_x": 24,
"size_y": 6
},
"sql_key": "D-03.lag_distribution",
"dataset_query": null,
"sql_smoke": null
},
{
"panel": "cohort_table",
"title": "Когортная таблица",
"kind": "table",
"display": "table",
"status": "ready",
"description": "sales/CPA/DRR cum по горизонту и зрелости.",
"layout": {
"row": 6,
"col": 0,
"size_x": 24,
"size_y": 10
},
"sql_key": "D-03.cohort_table",
"dataset_query": {
"type": "native",
"native": {
"query": "SELECT\n cohort_date,\n campaign_id,\n entity_type,\n horizon_days,\n sales_cum,\n cpa_cum,\n drr_cum,\n maturity_share\nFROM marts.cohort_conversion_lag\nWHERE cohort_date BETWEEN toDate({{day_from}}) AND toDate({{day_to}})\n AND maturity_share >= {{min_maturity}}\nORDER BY cohort_date DESC, horizon_days\n",
"template-tags": {
"day_from": {
"id": "day_from",
"name": "day_from",
"display-name": "Day From",
"type": "date",
"required": false
},
"day_to": {
"id": "day_to",
"name": "day_to",
"display-name": "Day To",
"type": "date",
"required": false
},
"min_maturity": {
"id": "min_maturity",
"name": "min_maturity",
"display-name": "Min Maturity",
"type": "number",
"required": false
}
}
},
"database": 2
},
"sql_smoke": "SELECT\n cohort_date,\n campaign_id,\n entity_type,\n horizon_days,\n sales_cum,\n cpa_cum,\n drr_cum,\n maturity_share\nFROM marts.cohort_conversion_lag\nWHERE cohort_date >= today() - 90\n AND maturity_share >= 0.8\nORDER BY cohort_date DESC, horizon_days\n",
"tables": [
"marts.cohort_conversion_lag"
]
},
{
"panel": "restatement_history",
"title": "История рестейтментов",
"kind": "table",
"display": "table",
"status": "todo",
"description": "Пересчёты когорт. SQL — TODO.",
"layout": {
"row": 16,
"col": 0,
"size_x": 24,
"size_y": 6
},
"sql_key": "D-03.restatement_history",
"dataset_query": null,
"sql_smoke": null
}
]
},
{
"key": "D-04",
"name": "D-04 — Атрибуция и окна",
"title": "Атрибуция и окна",
"audience": "Аналитик/CMO",
"purpose": "Сравнение моделей и окон атрибуции, post-view и валюта/НДС.",
"flags": {
"requires_maturity": false,
"has_inflation_toggle": true,
"has_change_markers": false
},
"cards": [
{
"panel": "models_compare",
"title": "Сравнение моделей атрибуции",
"kind": "series",
"display": "bar",
"status": "ready",
"description": "credit_share / revenue / profit по model × window.",
"layout": {
"row": 0,
"col": 0,
"size_x": 12,
"size_y": 8
},
"sql_key": "D-04.models_compare",
"dataset_query": {
"type": "native",
"native": {
"query": "SELECT\n model,\n window_days,\n sum(credit_share) AS credit_share,\n sum(credit_revenue) AS credit_revenue,\n sum(credit_profit) AS credit_profit\nFROM core.fct_attribution\nWHERE date BETWEEN toDate({{day_from}}) AND toDate({{day_to}})\nGROUP BY model, window_days\nORDER BY model, window_days\n",
"template-tags": {
"day_from": {
"id": "day_from",
"name": "day_from",
"display-name": "Day From",
"type": "date",
"required": false
},
"day_to": {
"id": "day_to",
"name": "day_to",
"display-name": "Day To",
"type": "date",
"required": false
}
}
},
"database": 2
},
"sql_smoke": "SELECT\n model,\n window_days,\n sum(credit_share) AS credit_share,\n sum(credit_revenue) AS credit_revenue,\n sum(credit_profit) AS credit_profit\nFROM core.fct_attribution\nWHERE date >= today() - 30\nGROUP BY model, window_days\nORDER BY model, window_days\n",
"tables": [
"core.fct_attribution"
]
},
{
"panel": "windows_compare",
"title": "Сравнение окон",
"kind": "series",
"display": "bar",
"status": "todo",
"description": "Окна атрибуции. SQL — TODO.",
"layout": {
"row": 0,
"col": 12,
"size_x": 12,
"size_y": 8
},
"sql_key": "D-04.windows_compare",
"dataset_query": null,
"sql_smoke": null
},
{
"panel": "post_view_share",
"title": "Доля post-view",
"kind": "metric",
"display": "scalar",
"status": "todo",
"description": "Доля post-view кредита. SQL — TODO.",
"layout": {
"row": 8,
"col": 0,
"size_x": 12,
"size_y": 6
},
"sql_key": "D-04.post_view_share",
"dataset_query": null,
"sql_smoke": null
},
{
"panel": "currency_vat",
"title": "Валюта и НДС",
"kind": "table",
"display": "table",
"status": "todo",
"description": "Нормализация валюты и НДС. SQL — TODO.",
"layout": {
"row": 8,
"col": 12,
"size_x": 12,
"size_y": 6
},
"sql_key": "D-04.currency_vat",
"dataset_query": null,
"sql_smoke": null
}
]
},
{
"key": "D-05",
"name": "D-05 — Change Log / Track Record",
"title": "Change Log / Track Record",
"audience": "Все",
"purpose": "Лента гипотез и hit-rate изменений — track record команды.",
"flags": {
"requires_maturity": false,
"has_inflation_toggle": false,
"has_change_markers": true
},
"cards": [
{
"panel": "change_feed",
"title": "Лента изменений",
"kind": "table",
"display": "table",
"status": "ready",
"description": "change_cohort: кто/что/гипотеза/вердикт.",
"layout": {
"row": 0,
"col": 0,
"size_x": 16,
"size_y": 12
},
"sql_key": "D-05.change_feed",
"dataset_query": {
"type": "native",
"native": {
"query": "SELECT\n change_id,\n created_at,\n actor_type,\n actor_id,\n entity_type,\n entity_id,\n change_type,\n hypothesis,\n tier,\n status,\n verdict_30d\nFROM marts.change_cohort\nWHERE created_at >= toDate({{day_from}})\nORDER BY created_at DESC\nLIMIT 200\n",
"template-tags": {
"day_from": {
"id": "day_from",
"name": "day_from",
"display-name": "Day From",
"type": "date",
"required": false
}
}
},
"database": 2
},
"sql_smoke": "SELECT\n change_id,\n created_at,\n actor_type,\n actor_id,\n entity_type,\n entity_id,\n change_type,\n hypothesis,\n tier,\n status,\n verdict_30d\nFROM marts.change_cohort\nWHERE created_at >= today() - 90\nORDER BY created_at DESC\nLIMIT 200\n",
"tables": [
"marts.change_cohort"
]
},
{
"panel": "hit_rate",
"title": "Hit rate гипотез",
"kind": "metric",
"display": "scalar",
"status": "ready",
"description": "Доля hit/win среди закрытых вердиктов 30д.",
"layout": {
"row": 0,
"col": 16,
"size_x": 8,
"size_y": 4
},
"sql_key": "D-05.hit_rate",
"dataset_query": {
"type": "native",
"native": {
"query": "SELECT\n countIf(verdict_30d IN ('hit', 'win'))\n / nullIf(countIf(verdict_30d IS NOT NULL AND verdict_30d != ''), 0) AS hit_rate,\n count() AS changes_n,\n countIf(status = 'closed') AS closed_n\nFROM marts.change_cohort\nWHERE created_at >= toDate({{day_from}})\n",
"template-tags": {
"day_from": {
"id": "day_from",
"name": "day_from",
"display-name": "Day From",
"type": "date",
"required": false
}
}
},
"database": 2
},
"sql_smoke": "SELECT\n countIf(verdict_30d IN ('hit', 'win'))\n / nullIf(countIf(verdict_30d IS NOT NULL AND verdict_30d != ''), 0) AS hit_rate,\n count() AS changes_n,\n countIf(status = 'closed') AS closed_n\nFROM marts.change_cohort\nWHERE created_at >= today() - 90\n",
"tables": [
"marts.change_cohort"
]
}
]
},
{
"key": "D-06",
"name": "D-06 — Города (воронка)",
"title": "Города (воронка)",
"audience": "CMO + оператор",
"purpose": "Клики → расход → лиды → CPL → продажи → CPS и CR лид→продажа. Один grain: город × период.",
"flags": {
"requires_maturity": false,
"has_inflation_toggle": false,
"has_change_markers": false
},
"cards": [
{
"panel": "kpis_acq",
"title": "Закупка: клики, расход, лиды, CPL",
"kind": "metric",
"display": "scalar",
"status": "ready",
"description": "Суммы без GROUP BY city. CPL = cost/leads в SQL.",
"layout": {
"row": 0,
"col": 0,
"size_x": 12,
"size_y": 4
},
"sql_key": "D-06.kpis_acq",
"dataset_query": {
"type": "native",
"native": {
"query": "SELECT\n sum(d.clicks) AS clicks,\n sum(d.cost_rub) AS cost_rub,\n sum(d.leads) AS leads,\n round(if(sum(d.leads) = 0, NULL, sum(d.cost_rub) / sum(d.leads)), 2) AS cpl\nFROM marts.geo_city_daily AS d\nINNER JOIN core.dim_geo AS g ON g.region_id = d.region_id\nWHERE d.date BETWEEN toDate({{day_from}}) AND toDate({{day_to}})\n AND g.region_type = 'city'\n AND g.name IN (\n 'Анапа',\n 'Астрахань',\n 'Батайск',\n 'Волгоград',\n 'Геленджик',\n 'Краснодар',\n 'Новороссийск',\n 'Саратов',\n 'Сочи'\n )\n AND (\n {{cities}} = ''\n OR has(splitByChar('|', {{cities}}), g.name)\n)\n",
"template-tags": {
"day_from": {
"id": "day_from",
"name": "day_from",
"display-name": "Day From",
"type": "date",
"required": false
},
"day_to": {
"id": "day_to",
"name": "day_to",
"display-name": "Day To",
"type": "date",
"required": false
},
"cities": {
"id": "cities",
"name": "cities",
"display-name": "Cities",
"type": "text",
"required": false
}
}
},
"database": 2
},
"sql_smoke": "SELECT\n sum(d.clicks) AS clicks,\n sum(d.cost_rub) AS cost_rub,\n sum(d.leads) AS leads,\n round(if(sum(d.leads) = 0, NULL, sum(d.cost_rub) / sum(d.leads)), 2) AS cpl\nFROM marts.geo_city_daily AS d\nINNER JOIN core.dim_geo AS g ON g.region_id = d.region_id\nWHERE d.date >= today() - 30\n AND g.region_type = 'city'\n AND g.name IN (\n 'Анапа',\n 'Астрахань',\n 'Батайск',\n 'Волгоград',\n 'Геленджик',\n 'Краснодар',\n 'Новороссийск',\n 'Саратов',\n 'Сочи'\n )\n AND 1\n",
"tables": [
"marts.geo_city_daily",
"core.dim_geo"
]
},
{
"panel": "kpis_mon",
"title": "Монетизация: лиды, продажи, CPS, CR",
"kind": "metric",
"display": "scalar",
"status": "ready",
"description": "Лиды как мост. CPS = cost/sales, CR = sales/leads в SQL.",
"layout": {
"row": 0,
"col": 12,
"size_x": 12,
"size_y": 4
},
"sql_key": "D-06.kpis_mon",
"dataset_query": {
"type": "native",
"native": {
"query": "SELECT\n sum(d.leads) AS leads,\n sum(d.sales) AS sales,\n round(if(sum(d.sales) = 0, NULL, sum(d.cost_rub) / sum(d.sales)), 2) AS cps,\n round(if(sum(d.leads) = 0, NULL, sum(d.sales) / sum(d.leads)), 4) AS cr_lead_sale\nFROM marts.geo_city_daily AS d\nINNER JOIN core.dim_geo AS g ON g.region_id = d.region_id\nWHERE d.date BETWEEN toDate({{day_from}}) AND toDate({{day_to}})\n AND g.region_type = 'city'\n AND g.name IN (\n 'Анапа',\n 'Астрахань',\n 'Батайск',\n 'Волгоград',\n 'Геленджик',\n 'Краснодар',\n 'Новороссийск',\n 'Саратов',\n 'Сочи'\n )\n AND (\n {{cities}} = ''\n OR has(splitByChar('|', {{cities}}), g.name)\n)\n",
"template-tags": {
"day_from": {
"id": "day_from",
"name": "day_from",
"display-name": "Day From",
"type": "date",
"required": false
},
"day_to": {
"id": "day_to",
"name": "day_to",
"display-name": "Day To",
"type": "date",
"required": false
},
"cities": {
"id": "cities",
"name": "cities",
"display-name": "Cities",
"type": "text",
"required": false
}
}
},
"database": 2
},
"sql_smoke": "SELECT\n sum(d.leads) AS leads,\n sum(d.sales) AS sales,\n round(if(sum(d.sales) = 0, NULL, sum(d.cost_rub) / sum(d.sales)), 2) AS cps,\n round(if(sum(d.leads) = 0, NULL, sum(d.sales) / sum(d.leads)), 4) AS cr_lead_sale\nFROM marts.geo_city_daily AS d\nINNER JOIN core.dim_geo AS g ON g.region_id = d.region_id\nWHERE d.date >= today() - 30\n AND g.region_type = 'city'\n AND g.name IN (\n 'Анапа',\n 'Астрахань',\n 'Батайск',\n 'Волгоград',\n 'Геленджик',\n 'Краснодар',\n 'Новороссийск',\n 'Саратов',\n 'Сочи'\n )\n AND 1\n",
"tables": [
"marts.geo_city_daily",
"core.dim_geo"
]
},
{
"panel": "city_table",
"title": "Воронка по городам",
"kind": "table",
"display": "table",
"status": "ready",
"description": "9 городов allowlist, ORDER BY cost_rub DESC. Без «Прочие».",
"layout": {
"row": 4,
"col": 0,
"size_x": 24,
"size_y": 10
},
"sql_key": "D-06.city_table",
"dataset_query": {
"type": "native",
"native": {
"query": "SELECT\n g.name AS city,\n sum(d.clicks) AS clicks,\n sum(d.cost_rub) AS cost_rub,\n sum(d.leads) AS leads,\n sum(d.sales) AS sales,\n round(if(sum(d.leads) = 0, NULL, sum(d.cost_rub) / sum(d.leads)), 2) AS cpl,\n round(if(sum(d.sales) = 0, NULL, sum(d.cost_rub) / sum(d.sales)), 2) AS cps,\n round(if(sum(d.leads) = 0, NULL, sum(d.sales) / sum(d.leads)), 4) AS cr_lead_sale\nFROM marts.geo_city_daily AS d\nINNER JOIN core.dim_geo AS g ON g.region_id = d.region_id\nWHERE d.date BETWEEN toDate({{day_from}}) AND toDate({{day_to}})\n AND g.region_type = 'city'\n AND g.name IN (\n 'Анапа',\n 'Астрахань',\n 'Батайск',\n 'Волгоград',\n 'Геленджик',\n 'Краснодар',\n 'Новороссийск',\n 'Саратов',\n 'Сочи'\n )\n AND (\n {{cities}} = ''\n OR has(splitByChar('|', {{cities}}), g.name)\n)\nGROUP BY city\nORDER BY cost_rub DESC\n",
"template-tags": {
"day_from": {
"id": "day_from",
"name": "day_from",
"display-name": "Day From",
"type": "date",
"required": false
},
"day_to": {
"id": "day_to",
"name": "day_to",
"display-name": "Day To",
"type": "date",
"required": false
},
"cities": {
"id": "cities",
"name": "cities",
"display-name": "Cities",
"type": "text",
"required": false
}
}
},
"database": 2
},
"sql_smoke": "SELECT\n g.name AS city,\n sum(d.clicks) AS clicks,\n sum(d.cost_rub) AS cost_rub,\n sum(d.leads) AS leads,\n sum(d.sales) AS sales,\n round(if(sum(d.leads) = 0, NULL, sum(d.cost_rub) / sum(d.leads)), 2) AS cpl,\n round(if(sum(d.sales) = 0, NULL, sum(d.cost_rub) / sum(d.sales)), 2) AS cps,\n round(if(sum(d.leads) = 0, NULL, sum(d.sales) / sum(d.leads)), 4) AS cr_lead_sale\nFROM marts.geo_city_daily AS d\nINNER JOIN core.dim_geo AS g ON g.region_id = d.region_id\nWHERE d.date >= today() - 30\n AND g.region_type = 'city'\n AND g.name IN (\n 'Анапа',\n 'Астрахань',\n 'Батайск',\n 'Волгоград',\n 'Геленджик',\n 'Краснодар',\n 'Новороссийск',\n 'Саратов',\n 'Сочи'\n )\n AND 1\nGROUP BY city\nORDER BY cost_rub DESC\n",
"tables": [
"marts.geo_city_daily",
"core.dim_geo"
]
},
{
"panel": "cpl_cps_bars",
"title": "CPL и CPS рядом",
"kind": "series",
"display": "bar",
"status": "ready",
"description": "city, cpl, cps — выражение в метриках, не два экрана.",
"layout": {
"row": 14,
"col": 0,
"size_x": 24,
"size_y": 6
},
"sql_key": "D-06.cpl_cps_bars",
"dataset_query": {
"type": "native",
"native": {
"query": "SELECT\n g.name AS city,\n round(if(sum(d.leads) = 0, NULL, sum(d.cost_rub) / sum(d.leads)), 2) AS cpl,\n round(if(sum(d.sales) = 0, NULL, sum(d.cost_rub) / sum(d.sales)), 2) AS cps\nFROM marts.geo_city_daily AS d\nINNER JOIN core.dim_geo AS g ON g.region_id = d.region_id\nWHERE d.date BETWEEN toDate({{day_from}}) AND toDate({{day_to}})\n AND g.region_type = 'city'\n AND g.name IN (\n 'Анапа',\n 'Астрахань',\n 'Батайск',\n 'Волгоград',\n 'Геленджик',\n 'Краснодар',\n 'Новороссийск',\n 'Саратов',\n 'Сочи'\n )\n AND (\n {{cities}} = ''\n OR has(splitByChar('|', {{cities}}), g.name)\n)\nGROUP BY city\nORDER BY sum(d.cost_rub) DESC\n",
"template-tags": {
"day_from": {
"id": "day_from",
"name": "day_from",
"display-name": "Day From",
"type": "date",
"required": false
},
"day_to": {
"id": "day_to",
"name": "day_to",
"display-name": "Day To",
"type": "date",
"required": false
},
"cities": {
"id": "cities",
"name": "cities",
"display-name": "Cities",
"type": "text",
"required": false
}
}
},
"database": 2
},
"sql_smoke": "SELECT\n g.name AS city,\n round(if(sum(d.leads) = 0, NULL, sum(d.cost_rub) / sum(d.leads)), 2) AS cpl,\n round(if(sum(d.sales) = 0, NULL, sum(d.cost_rub) / sum(d.sales)), 2) AS cps\nFROM marts.geo_city_daily AS d\nINNER JOIN core.dim_geo AS g ON g.region_id = d.region_id\nWHERE d.date >= today() - 30\n AND g.region_type = 'city'\n AND g.name IN (\n 'Анапа',\n 'Астрахань',\n 'Батайск',\n 'Волгоград',\n 'Геленджик',\n 'Краснодар',\n 'Новороссийск',\n 'Саратов',\n 'Сочи'\n )\n AND 1\nGROUP BY city\nORDER BY sum(d.cost_rub) DESC\n",
"tables": [
"marts.geo_city_daily",
"core.dim_geo"
]
}
]
}
],
"invariant": "Источник — только marts/core; без live API платформ"
}