teste dash

{ "__inputs": [ { "name": "DS_LAICON", "label": "laicon", "description": "", "type": "datasource", "pluginId": "grafana-postgresql-datasource", "pluginName": "PostgreSQL" } ], "__elements": {}, "__requires": [ { "type": "grafana", "id": "grafana", "name": "Grafana", "version": "12.4.2" }, { "type": "datasource", "id": "grafana-postgresql-datasource", "name": "PostgreSQL", "version": "12.4.2" }, { "type": "panel", "id": "marcusolsson-dynamictext-panel", "name": "Business Text", "version": "6.2.0" } ], "annotations": { "list": [ { "builtIn": 1, "datasource": { "type": "grafana", "uid": "-- Grafana --" }, "enable": true, "hide": true, "iconColor": "rgba(0, 211, 255, 1)", "name": "Annotations & Alerts", "type": "dashboard" } ] }, "editable": true, "fiscalYearStartMonth": 0, "graphTooltip": 0, "links": [], "panels": [ { "collapsed": false, "gridPos": { "h": 1, "w": 24, "x": 0, "y": 0 }, "id": 6, "panels": [], "title": "Funil de Leads", "type": "row" }, { "datasource": { "type": "grafana-postgresql-datasource", "uid": "${DS_LAICON}" }, "fieldConfig": { "defaults": { "thresholds": { "mode": "absolute", "steps": [ { "color": "green", "value": 0 }, { "color": "red", "value": 80 } ] } }, "overrides": [] }, "gridPos": { "h": 13, "w": 15, "x": 0, "y": 1 }, "id": 1, "options": { "afterRender": "", "content": "", "contentPartials": [], "defaultContent": "The query didn't return any results.", "editor": { "format": "auto", "language": "html" }, "editors": [ "helpers", "styles" ], "externalStyles": [], "helpers": "// Aguarda o HTML renderizar as variáveis\r\nsetTimeout(() => {\r\n // Formatar Faturamento\r\n const faturamentoEl = document.getElementById('val-faturamento');\r\n if (faturamentoEl) {\r\n let valorFat = parseFloat(faturamentoEl.getAttribute('data-valor'));\r\n if (!isNaN(valorFat)) {\r\n faturamentoEl.innerText = valorFat.toLocaleString('pt-BR', { style: 'currency', currency: 'BRL' });\r\n }\r\n }\r\n\r\n // Formatar Ticket Médio\r\n const ticketEl = document.getElementById('val-ticket');\r\n if (ticketEl) {\r\n let valorTicket = parseFloat(ticketEl.getAttribute('data-valor'));\r\n if (!isNaN(valorTicket)) {\r\n ticketEl.innerText = valorTicket.toLocaleString('pt-BR', { style: 'currency', currency: 'BRL' });\r\n }\r\n }\r\n}, 100);", "renderMode": "everyRow", "styles": "/* =========================================\r\n CONTAINER PRINCIPAL E CABEÇALHO\r\n ========================================= */\r\n.grafana-funnel-slim {\r\n display: flex;\r\n flex-direction: column;\r\n align-items: center;\r\n font-family: sans-serif;\r\n width: 100%;\r\n color: #fff;\r\n padding: 0px;\r\n margin-right: 0px;\r\n}\r\n\r\n/* =========================================\r\n CABECALHO PADRONIZADO (IGUAL AO CUSTOS)\r\n ========================================= */\r\n.funnel-header {\r\n background-color: #222; /* Fundo grafite escuro da box */\r\n color: #ff6b00; /* Laranja padrão */\r\n width: 100%; /* Ocupa a largura total */\r\n max-width: 500px; /* Alinha com a largura do funil */\r\n \r\n padding: 10px 15px; /* Espaçamento interno */\r\n border-radius: 8px; /* Cantos arredondados */\r\n \r\n font-weight: bold;\r\n font-size: 1.1rem; /* Tamanho padrão do dashboard */\r\n text-align: center; /* Alinhado à esquerda igual ao modelo */\r\n \r\n margin-bottom: 25px; /* Espaço para as barras cinzas */\r\n margin-top: 10px;\r\n \r\n box-sizing: border-box;\r\n \r\n /* Remove a caixa alta total */\r\n text-transform: none; \r\n letter-spacing: 0.5px;\r\n}\r\n\r\n.funnel-header span {\r\n /* Garante que o texto dentro da box herde a cor correta */\r\n color: #ff6b00;\r\n}\r\n\r\n/* =========================================\r\n LAYOUT CENTRALIZADO\r\n ========================================= */\r\n.funnel-layout, .funnel-blocks-layout {\r\n display: flex;\r\n justify-content: center;\r\n width: 100%;\r\n padding-left: 160px; \r\n padding-right: 160px; \r\n box-sizing: border-box;\r\n}\r\n\r\n.funnel-bars-container {\r\n width: 100%;\r\n max-width: 500px; \r\n display: flex;\r\n flex-direction: column;\r\n position: relative; /* FUNDAMENTAL para ancorar o conector da esquerda */\r\n}\r\n\r\n.funnel-row {\r\n width: 100%;\r\n display: flex;\r\n justify-content: center;\r\n}\r\n\r\n.funnel-gap {\r\n width: 100%;\r\n height: 12px;\r\n position: relative;\r\n}\r\n\r\n/* =========================================\r\n NOVO: CONECTOR LATERAL ESQUERDO (TOTAL)\r\n ========================================= */\r\n.total-connector-wrapper {\r\n position: absolute;\r\n right: 100%; /* Empurra para a área de padding-left */\r\n top: 0;\r\n bottom: 0;\r\n display: flex;\r\n align-items: center;\r\n justify-content: flex-end; /* Alinha texto encostado no colchete */\r\n width: 160px;\r\n padding-right: 20px; \r\n box-sizing: border-box;\r\n}\r\n\r\n.total-bracket-line {\r\n position: absolute;\r\n right: 0;\r\n top: 22px; /* Metade da primeira barra */\r\n bottom: 22px; /* Metade da última barra */\r\n width: 12px;\r\n border: 1px solid rgba(255, 255, 255, 0.15);\r\n border-right: none; /* Sem linha na direita = C invertido */\r\n border-radius: 8px 0 0 8px; /* Curvas na esquerda */\r\n}\r\n\r\n.total-bracket-text {\r\n font-size: 0.65rem; \r\n color: #a0a0a0; \r\n line-height: 1.3;\r\n text-transform: uppercase;\r\n letter-spacing: 0.5px;\r\n text-align: right;\r\n}\r\n\r\n.total-bracket-text strong {\r\n font-size: 1.15rem; \r\n color: #ffffff;\r\n display: block;\r\n margin-top: 2px;\r\n letter-spacing: 0;\r\n}\r\n\r\n/* =========================================\r\n CONECTORES LATERAIS DIREITOS (ETAPAS)\r\n ========================================= */\r\n.connector-wrapper {\r\n position: absolute;\r\n left: 100%; \r\n top: 50%;\r\n transform: translateY(-50%);\r\n display: flex;\r\n align-items: center;\r\n width: 160px;\r\n padding-left: 20px; \r\n box-sizing: border-box;\r\n}\r\n\r\n.bracket-line {\r\n position: absolute;\r\n left: 0;\r\n top: -24px; \r\n height: 60px; \r\n width: 12px;\r\n border: 1px solid rgba(255, 255, 255, 0.15);\r\n border-left: none; \r\n border-radius: 0 8px 8px 0; \r\n}\r\n\r\n.bracket-text {\r\n font-size: 0.65rem; \r\n color: #a0a0a0; \r\n line-height: 1.3;\r\n text-transform: uppercase;\r\n letter-spacing: 0.5px; \r\n}\r\n\r\n.bracket-text strong {\r\n font-size: 1.15rem; \r\n color: #ffffff;\r\n display: block;\r\n margin-bottom: 2px;\r\n letter-spacing: 0;\r\n}\r\n\r\n/* =========================================\r\n BARRAS DO FUNIL (SLIM)\r\n ========================================= */\r\n.funnel-step {\r\n display: flex;\r\n justify-content: center;\r\n align-items: center;\r\n border-radius: 12px;\r\n box-shadow: 0 4px 6px rgba(0,0,0,0.2);\r\n position: relative;\r\n min-height: 45px;\r\n color: #fff;\r\n}\r\n\r\n.step-leads { width: 100%; background: linear-gradient(135deg, #ed6217 0%, #f47321 100%); }\r\n.step-agendamentos { width: 90%; background: linear-gradient(135deg, #f09b75 0%, #f6ab89 100%); color: #222; }\r\n.step-comparecimento { width: 80%; background: linear-gradient(135deg, #f2b49b 0%, #f8c3ae 100%); color: #222; }\r\n.step-orcamentos { width: 70%; background: linear-gradient(135deg, #f5c7b5 0%, #fbd5c6 100%); color: #222; }\r\n.step-fechamento { width: 60%; background: linear-gradient(135deg, #f8dccb 0%, #ffffff 100%); color: #222; border: 1px solid rgba(0,0,0,0.1); }\r\n\r\n.step-layout {\r\n display: flex;\r\n width: 100%;\r\n height: 100%;\r\n align-items: center;\r\n justify-content: center;\r\n position: relative;\r\n}\r\n\r\n.step-title {\r\n position: absolute;\r\n left: 50%;\r\n transform: translateX(-50%);\r\n top: 3px;\r\n font-weight: bold;\r\n font-size: 0.85rem;\r\n opacity: 0.9;\r\n z-index: 1;\r\n}\r\n\r\n.step-data {\r\n display: flex;\r\n width: 100%;\r\n justify-content: space-around;\r\n align-items: center;\r\n padding: 15px 10px 5px 10px;\r\n}\r\n\r\n.data-group {\r\n display: flex;\r\n align-items: center;\r\n justify-content: center;\r\n flex: 1;\r\n}\r\n\r\n.data-group.data-meta { justify-content: flex-end; padding-right: 15px; }\r\n.data-group.data-real { justify-content: flex-start; padding-left: 15px; }\r\n\r\n.data-value {\r\n font-size: 1.4rem;\r\n font-weight: 700;\r\n margin: 0 8px;\r\n line-height: 1;\r\n}\r\n\r\n.data-label {\r\n font-size: 0.7rem;\r\n font-weight: normal;\r\n line-height: 1.1;\r\n opacity: 0.8;\r\n text-align: left;\r\n}\r\n.data-meta .data-label { text-align: right; }\r\n\r\n.data-divider {\r\n height: 25px;\r\n width: 1px;\r\n background-color: rgba(0,0,0,0.1);\r\n opacity: 0.6;\r\n}\r\n\r\n.data-icon {\r\n width: 12px;\r\n height: 12px;\r\n background-size: contain;\r\n opacity: 0.5;\r\n}\r\n.target-icon { background-image: url('data:image/svg+xml;utf8,'); }\r\n.check-icon { background-image: url('data:image/svg+xml;utf8,'); }\r\n\r\n/* =========================================\r\n BLOCOS FINAIS (ULTRA SLIM)\r\n ========================================= */\r\n.funnel-blocks-column {\r\n width: 100%;\r\n max-width: 500px; \r\n display: flex;\r\n flex-direction: column;\r\n align-items: center;\r\n margin-top: 8px; \r\n}\r\n\r\n.funnel-block {\r\n display: flex;\r\n flex-direction: column; \r\n justify-content: center;\r\n align-items: center;\r\n border-radius: 6px; \r\n box-shadow: 0 2px 4px rgba(0,0,0,0.2);\r\n padding: 5px 10px; \r\n margin-bottom: 6px; \r\n line-height: 1;\r\n}\r\n\r\n.funnel-block .block-title {\r\n font-weight: 600;\r\n font-size: 0.65rem; \r\n text-transform: uppercase;\r\n opacity: 0.85;\r\n margin-bottom: 3px;\r\n letter-spacing: 0.5px;\r\n}\r\n\r\n.funnel-block .block-value {\r\n font-weight: 700;\r\n font-size: 0.95rem; \r\n margin: 0;\r\n white-space: nowrap; \r\n}\r\n\r\n.block-faturamento { background-color: #00b050; width: 50%; }\r\n.block-ticket { background-color: #0066cc; width: 40%; }\r\n\r\n/* =========================================\r\n AJUSTES RESPONSIVOS PARA CELULAR (MOBILE)\r\n ========================================= */\r\n@media (max-width: 768px) {\r\n .funnel-layout, .funnel-blocks-layout {\r\n padding-left: 0;\r\n padding-right: 0;\r\n }\r\n \r\n .funnel-gap {\r\n height: auto;\r\n margin: 4px 0;\r\n }\r\n\r\n /* Ajuste do Conector Total (Esquerdo) no Mobile */\r\n .total-connector-wrapper {\r\n position: relative;\r\n right: 0;\r\n top: 0;\r\n bottom: auto;\r\n width: 100%;\r\n justify-content: center;\r\n padding-right: 0;\r\n margin-bottom: 12px;\r\n }\r\n\r\n .total-bracket-line { display: none; }\r\n\r\n .total-bracket-text {\r\n background: transparent;\r\n padding: 0;\r\n display: flex;\r\n align-items: center;\r\n gap: 6px;\r\n text-align: center;\r\n }\r\n \r\n .total-bracket-text br { display: none; }\r\n .total-bracket-text strong { margin-top: 0; font-size: 0.95rem; }\r\n\r\n /* Ajuste dos Conectores de Etapas (Direitos) no Mobile */\r\n .connector-wrapper {\r\n position: relative;\r\n left: 0;\r\n top: 0;\r\n transform: none;\r\n width: 100%;\r\n justify-content: center;\r\n padding-left: 0;\r\n }\r\n\r\n .bracket-line { display: none; }\r\n\r\n .bracket-text {\r\n background: transparent;\r\n padding: 0;\r\n display: flex;\r\n align-items: center;\r\n gap: 6px; \r\n }\r\n\r\n .bracket-text strong {\r\n margin-bottom: 0;\r\n font-size: 0.9rem;\r\n }\r\n \r\n .text-desc { display: block; }\r\n\r\n /* Ajustes finos nas fontes */\r\n .data-value { font-size: 1.1rem; margin: 0 5px; }\r\n .data-label { font-size: 0.6rem; }\r\n .step-data { padding: 18px 5px 4px 5px; }\r\n \r\n .block-faturamento { width: 50%; }\r\n .block-ticket { width: 40%; }\r\n \r\n .funnel-block .block-title { font-size: 0.55rem; }\r\n .funnel-block .block-value { font-size: 0.8rem; }\r\n}", "wrap": false }, "pluginVersion": "6.2.0", "targets": [ { "dataset": "laicon_crm", "datasource": { "type": "grafana-postgresql-datasource", "uid": "${DS_LAICON}" }, "editorMode": "code", "format": "table", "rawQuery": true, "rawSql": "WITH cohort_leads AS (\r\n -- 1. A CERCA (COHORT): Filtramos quem entrou no período e na campanha selecionada\r\n SELECT id AS lead_id\r\n FROM crm_leads.leads \r\n WHERE instancia = 'OdontoMarco'\r\n AND $__timeFilter(data) -- A ÚNICA DATA QUE IMPORTA EM TODO O PAINEL\r\n AND ('TODOS' IN ($plataforma) OR plataforma IN ($plataforma))\r\n AND ('TODOS' IN ($campanha) OR campanha IN ($campanha))\r\n AND ('TODOS' IN ($conjunto) OR conjunto IN ($conjunto))\r\n AND ('TODOS' IN ($anuncio) OR anuncio IN ($anuncio))\r\n),\r\nleads_cte AS (\r\n -- Contamos quantos leads formam a nossa cerca\r\n SELECT COUNT(DISTINCT lead_id) AS leads_real FROM cohort_leads\r\n),\r\nagendamentos_base AS (\r\n -- 2. O CRUZAMENTO BLINDADO: Só trazemos dados de quem está dentro da \"cerca\" (cohort_leads)\r\n SELECT \r\n a.agendamento_id, \r\n a.status_type, \r\n a.orcamento_id, \r\n a.orcamento_status, \r\n a.valor_total\r\n FROM clinicorp.vw_agendamentos_com_leads a\r\n \r\n -- A MÁGICA ESTÁ NESTA LINHA: O INNER JOIN obriga a base a ser idêntica ao topo do funil\r\n INNER JOIN cohort_leads c ON a.lead_id = c.lead_id \r\n \r\n WHERE a.instancia = 'OdontoMarco'\r\n AND a.clinica_id = '5275512294080512'\r\n -- Note que NÃO EXISTE MAIS filtro de data aqui. \r\n -- Se o lead entrou hoje e fechar a venda daqui a 2 anos, a venda entra para o funil de hoje.\r\n),\r\nagendamentos_cte AS (\r\n SELECT COUNT(DISTINCT agendamento_id) as agendamentos_real FROM agendamentos_base\r\n),\r\ncomparecimento_cte AS (\r\n SELECT COUNT(DISTINCT agendamento_id) as comparecimento_real \r\n FROM agendamentos_base WHERE status_type = 'CHECKOUT'\r\n),\r\norcamentos_gerados_cte AS (\r\n SELECT COUNT(DISTINCT orcamento_id) as orcamentos_gerados_real \r\n FROM agendamentos_base \r\n WHERE status_type = 'CHECKOUT' AND orcamento_id IS NOT NULL\r\n),\r\nfechamento_cte AS (\r\n SELECT \r\n COUNT(DISTINCT orcamento_id) as fechamento_real,\r\n COALESCE(SUM(valor_total), 0) as faturamento_real\r\n FROM (\r\n SELECT DISTINCT orcamento_id, valor_total\r\n FROM agendamentos_base \r\n WHERE status_type = 'CHECKOUT' \r\n AND orcamento_id IS NOT NULL \r\n AND orcamento_status = 'APPROVED'\r\n ) orcamentos_unicos\r\n)\r\nSELECT \r\n l.leads_real,\r\n 2000 AS leads_meta, \r\n a.agendamentos_real,\r\n 1000 AS agendamentos_meta, \r\n c.comparecimento_real,\r\n 500 AS comparecimento_meta, \r\n o.orcamentos_gerados_real,\r\n 400 AS orcamentos_gerados_meta, \r\n f.fechamento_real,\r\n 200 AS fechamento_meta, \r\n f.faturamento_real,\r\n CASE WHEN f.fechamento_real > 0 THEN f.faturamento_real / f.fechamento_real ELSE 0 END AS ticket_medio,\r\n\r\n COALESCE(ROUND((CAST(a.agendamentos_real AS NUMERIC) * 100.0) / NULLIF(l.leads_real, 0), 1), 0) AS tx_agendamento,\r\n COALESCE(ROUND((CAST(c.comparecimento_real AS NUMERIC) * 100.0) / NULLIF(a.agendamentos_real, 0), 1), 0) AS tx_comparecimento,\r\n COALESCE(ROUND((CAST(o.orcamentos_gerados_real AS NUMERIC) * 100.0) / NULLIF(c.comparecimento_real, 0), 1), 0) AS tx_orcamento,\r\n COALESCE(ROUND((CAST(f.fechamento_real AS NUMERIC) * 100.0) / NULLIF(o.orcamentos_gerados_real, 0), 1), 0) AS tx_fechamento,\r\n COALESCE(ROUND((CAST(f.fechamento_real AS NUMERIC) * 100.0) / NULLIF(l.leads_real, 0), 1), 0) AS tx_total\r\n\r\nFROM leads_cte l\r\nCROSS JOIN agendamentos_cte a\r\nCROSS JOIN comparecimento_cte c\r\nCROSS JOIN orcamentos_gerados_cte o\r\nCROSS JOIN fechamento_cte f;", "refId": "A", "sql": { "columns": [ { "parameters": [], "type": "function" } ], "groupBy": [ { "property": { "type": "string" }, "type": "groupBy" } ], "limit": 50 } } ], "transparent": true, "type": "marcusolsson-dynamictext-panel" }, { "datasource": { "type": "grafana-postgresql-datasource", "uid": "${DS_LAICON}" }, "fieldConfig": { "defaults": { "thresholds": { "mode": "absolute", "steps": [ { "color": "green", "value": 0 }, { "color": "red", "value": 80 } ] } }, "overrides": [] }, "gridPos": { "h": 12, "w": 9, "x": 15, "y": 1 }, "id": 2, "options": { "afterRender": "", "content": "", "contentPartials": [], "defaultContent": "The query didn't return any results.", "editor": { "format": "auto", "language": "html" }, "editors": [ "helpers", "styles" ], "externalStyles": [], "helpers": "setTimeout(() => {\r\n // Formatar Moedas (CPA, CPO, etc)\r\n const moedas = document.querySelectorAll('.val-moeda');\r\n moedas.forEach(el => {\r\n let val = parseFloat(el.getAttribute('data-valor'));\r\n if (!isNaN(val)) {\r\n el.innerText = val.toLocaleString('pt-BR', { style: 'currency', currency: 'BRL' });\r\n }\r\n });\r\n\r\n // Formatar ROAS (Ex: 5,4)\r\n const roasEl = document.querySelectorAll('.val-roas');\r\n roasEl.forEach(el => {\r\n let val = parseFloat(el.getAttribute('data-valor'));\r\n if (!isNaN(val)) {\r\n el.innerText = val.toLocaleString('pt-BR', { minimumFractionDigits: 1, maximumFractionDigits: 2 });\r\n }\r\n });\r\n}, 100);", "renderMode": "everyRow", "styles": ".grafana-custos {\r\n display: flex;\r\n flex-direction: column;\r\n font-family: sans-serif;\r\n width: 100%;\r\n max-width: 450px; /* Largura ideal para ficar ao lado do funil */\r\n margin: 0 auto;\r\n gap: 15px; /* Espaço entre as barras */\r\n}\r\n\r\n.custos-header {\r\n background-color: #222;\r\n color: #ff6b00;\r\n font-weight: bold;\r\n font-size: 1.1rem;\r\n padding: 8px 15px;\r\n border-radius: 6px;\r\n margin-bottom: 20px;\r\n margin-top: 15px;\r\ntext-align: center; /* Alinhado à esquerda igual ao modelo */\r\n}\r\n\r\n.custo-row {\r\n display: flex;\r\n border-radius: 4px 12px 12px 4px; /* Arredondado apenas na direita */\r\n overflow: hidden;\r\n min-height: 48px;\r\n}\r\n\r\n.custo-label {\r\n flex: 1;\r\n padding: 12px 15px;\r\n font-size: 0.85rem;\r\n font-weight: bold;\r\n color: #fff;\r\n display: flex;\r\n align-items: center;\r\n}\r\n\r\n.custo-value {\r\n width: 130px; /* Largura fixa para os valores ficarem alinhados */\r\n padding: 12px 15px;\r\n font-size: 1.05rem;\r\n font-weight: bold;\r\n color: #fff;\r\n display: flex;\r\n align-items: center;\r\n}\r\n\r\n/* Efeito Cascata de Cinzas (Igual à imagem) */\r\n.row-1 .custo-label { background-color: #3b3b3b; }\r\n.row-1 .custo-value { background-color: #4b4b4b; }\r\n\r\n.row-2 .custo-label { background-color: #555555; }\r\n.row-2 .custo-value { background-color: #666666; }\r\n\r\n.row-3 .custo-label { background-color: #6b6b6b; }\r\n.row-3 .custo-value { background-color: #7d7d7d; }\r\n\r\n.row-4 .custo-label { background-color: #7f7f7f; }\r\n.row-4 .custo-value { background-color: #8f8f8f; }\r\n\r\n.row-5 .custo-label { background-color: #949494; }\r\n.row-5 .custo-value { background-color: #a3a3a3; }", "wrap": false }, "pluginVersion": "6.2.0", "targets": [ { "dataset": "laicon_crm", "datasource": { "type": "grafana-postgresql-datasource", "uid": "${DS_LAICON}" }, "editorMode": "code", "format": "table", "rawQuery": true, "rawSql": "WITH cohort_leads AS (\r\n -- 1. A CERCA (COHORT): Puxa apenas os leads do período e filtros selecionados\r\n SELECT id AS lead_id\r\n FROM crm_leads.leads \r\n WHERE instancia = 'OdontoMarco' \r\n AND $__timeFilter(data)\r\n AND ('TODOS' IN ($plataforma) OR plataforma IN ($plataforma))\r\n AND ('TODOS' IN ($campanha) OR campanha IN ($campanha))\r\n AND ('TODOS' IN ($conjunto) OR conjunto IN ($conjunto))\r\n AND ('TODOS' IN ($anuncio) OR anuncio IN ($anuncio))\r\n),\r\nleads_cte AS (\r\n -- Conta quantos leads temos na nossa cerca\r\n SELECT COUNT(DISTINCT lead_id) as leads_real \r\n FROM cohort_leads\r\n),\r\nagendamentos_base AS (\r\n -- 2. CRUZAMENTO BLINDADO: Puxa agendamentos/vendas apenas de quem tá na cerca\r\n SELECT \r\n a.agendamento_id, \r\n a.status_type, \r\n a.orcamento_id, \r\n a.orcamento_status, \r\n a.valor_total,\r\n a.lead_campanha,\r\n a.lead_conjunto,\r\n a.lead_anuncio,\r\n a.meta_gasto_anuncio\r\n FROM clinicorp.vw_agendamentos_com_leads a\r\n \r\n -- O INNER JOIN que garante que só vai calcular custo de quem realmente entrou no funil\r\n INNER JOIN cohort_leads c ON a.lead_id = c.lead_id\r\n \r\n -- Usa a instância para isolar o cliente, e NÃO usamos mais filtro de data aqui\r\n WHERE a.instancia = 'OdontoMarco'\r\n),\r\nagendamentos_cte AS (\r\n SELECT COUNT(DISTINCT agendamento_id) as agendamentos_real FROM agendamentos_base\r\n),\r\ncomparecimento_cte AS (\r\n SELECT COUNT(DISTINCT agendamento_id) as comparecimento_real \r\n FROM agendamentos_base WHERE status_type = 'CHECKOUT'\r\n),\r\nfechamento_cte AS (\r\n SELECT \r\n COUNT(DISTINCT orcamento_id) as vendas_real, \r\n COALESCE(SUM(valor_total), 0) as faturamento_real\r\n FROM (\r\n SELECT DISTINCT orcamento_id, valor_total \r\n FROM agendamentos_base \r\n WHERE status_type = 'CHECKOUT' AND orcamento_id IS NOT NULL AND orcamento_status = 'APPROVED'\r\n ) orcamentos_unicos\r\n),\r\ngasto_cte AS (\r\n SELECT COALESCE(SUM(meta_gasto_anuncio), 0) as gasto_total\r\n FROM (\r\n SELECT DISTINCT lead_campanha, lead_conjunto, lead_anuncio, meta_gasto_anuncio \r\n FROM agendamentos_base \r\n WHERE meta_gasto_anuncio IS NOT NULL\r\n ) ads_unicos\r\n)\r\nSELECT \r\n g.gasto_total,\r\n \r\n -- CÁLCULOS DE CUSTO (Protegidos contra divisão por zero)\r\n CASE WHEN l.leads_real > 0 THEN g.gasto_total / l.leads_real ELSE 0 END AS cpa_lead,\r\n CASE WHEN a.agendamentos_real > 0 THEN g.gasto_total / a.agendamentos_real ELSE 0 END AS custo_agendamento,\r\n CASE WHEN c.comparecimento_real > 0 THEN g.gasto_total / c.comparecimento_real ELSE 0 END AS custo_comparecimento,\r\n CASE WHEN f.vendas_real > 0 THEN g.gasto_total / f.vendas_real ELSE 0 END AS cpo_venda,\r\n \r\n -- CÁLCULO DE ROAS\r\n CASE WHEN g.gasto_total > 0 THEN f.faturamento_real / g.gasto_total ELSE 0 END AS roas\r\n\r\nFROM gasto_cte g\r\nCROSS JOIN leads_cte l\r\nCROSS JOIN agendamentos_cte a\r\nCROSS JOIN comparecimento_cte c\r\nCROSS JOIN fechamento_cte f;", "refId": "A", "sql": { "columns": [ { "parameters": [], "type": "function" } ], "groupBy": [ { "property": { "type": "string" }, "type": "groupBy" } ], "limit": 50 } } ], "transparent": true, "type": "marcusolsson-dynamictext-panel" }, { "collapsed": false, "gridPos": { "h": 1, "w": 24, "x": 0, "y": 14 }, "id": 5, "panels": [], "title": "Investimentos e Performance", "type": "row" }, { "datasource": { "type": "grafana-postgresql-datasource", "uid": "${DS_LAICON}" }, "fieldConfig": { "defaults": { "thresholds": { "mode": "absolute", "steps": [ { "color": "green", "value": 0 }, { "color": "red", "value": 80 } ] } }, "overrides": [] }, "gridPos": { "h": 17, "w": 7, "x": 4, "y": 15 }, "id": 3, "options": { "afterRender": "", "content": "", "contentPartials": [], "defaultContent": "The query didn't return any results.", "editor": { "format": "auto", "language": "html" }, "editors": [ "helpers", "styles" ], "externalStyles": [], "helpers": "setTimeout(() => {\r\n const moedas = document.querySelectorAll('.val-moeda');\r\n\r\n moedas.forEach(el => {\r\n let val = parseFloat(el.getAttribute('data-valor'));\r\n\r\n if (!isNaN(val)) {\r\n // Formata como BRL\r\n el.innerText = val.toLocaleString('pt-BR', {\r\n style: 'currency',\r\n currency: 'BRL'\r\n });\r\n }\r\n });\r\n}, 100);", "renderMode": "everyRow", "styles": ".grafana-investimento {\r\n display: flex;\r\n flex-direction: column;\r\n font-family: sans-serif;\r\n width: 100%;\r\n max-width: 500px; /* Largura para manter a proporção da imagem */\r\n margin: 0 auto;\r\n margin-top: 2px;\r\n}\r\n\r\n/* Cabeçalho Principal */\r\n.investimento-main-header {\r\n background-color: #222;\r\n color: #ff6b00;\r\n text-align: center;\r\n font-weight: bold;\r\n font-size: 1.2rem;\r\n padding: 12px;\r\n border-radius: 8px;\r\n margin-bottom: 20px;\r\n}\r\n\r\n/* Seções das Plataformas */\r\n.investimento-section {\r\n margin-bottom: 15px;\r\n display: flex;\r\n flex-direction: column;\r\n border-radius: 6px;\r\n overflow: hidden;\r\n box-shadow: 0 2px 4px rgba(0,0,0,0.1);\r\n}\r\n\r\n/* Títulos coloridos de cada bloco */\r\n.section-title {\r\n text-align: center;\r\n color: #fff;\r\n font-weight: bold;\r\n padding: 8px 0;\r\n font-size: 0.95rem;\r\n}\r\n\r\n.title-meta { background-color: #0066cc; } /* Azul Meta */\r\n.title-google { background-color: #00b050; } /* Verde Google */\r\n.title-outras { background-color: #333333; } /* Cinza Escuro */\r\n.title-total { background-color: #ff5500; } /* Laranja Forte */\r\n\r\n/* Grade de 2 Colunas */\r\n.section-grid {\r\n display: grid;\r\n grid-template-columns: 1fr 1fr; /* Duas colunas de tamanhos iguais */\r\n}\r\n\r\n/* Cabeçalhos da Tabela (Plano de Mídia / Investimento Total) */\r\n.grid-header {\r\n background-color: #444444; /* Fundo cinza escuro */\r\n color: #ffffff;\r\n text-align: center;\r\n font-weight: bold;\r\n font-size: 0.85rem;\r\n padding: 6px;\r\n border-right: 1px solid rgba(255,255,255,0.1);\r\n}\r\n.grid-header:last-child { border-right: none; }\r\n\r\n/* Valores em Reais */\r\n.grid-value {\r\n background-color: #e0e0e0; /* Fundo cinza claro */\r\n color: #222;\r\n text-align: center;\r\n font-weight: bold;\r\n font-size: 1rem;\r\n padding: 12px;\r\n border-right: 1px solid rgba(0,0,0,0.05);\r\n}\r\n.grid-value:last-child { border-right: none; }\r\n\r\n/* Linha Divisória antes do Total */\r\n.divider {\r\n height: 1px;\r\n background-color: rgba(255,255,255,0.2);\r\n margin: 5px 0 20px 0;\r\n width: 100%;\r\n}\r\n\r\n/* Cores específicas da Tabela do TOTAL (Laranjas mais claros) */\r\n.header-total { background-color: #ff8c1a; }\r\n.value-total { background-color: #ffcc99; color: #222; }", "wrap": false }, "pluginVersion": "6.2.0", "targets": [ { "dataset": "laicon_crm", "datasource": { "type": "grafana-postgresql-datasource", "uid": "${DS_LAICON}" }, "editorMode": "code", "format": "table", "rawQuery": true, "rawSql": "WITH gasto_meta AS (\r\n -- Pega o gasto total do Meta Ads sem duplicar valores de um mesmo anúncio\r\n SELECT COALESCE(SUM(meta_gasto_anuncio), 0) as meta_total\r\n FROM (\r\n SELECT DISTINCT lead_campanha, lead_conjunto, lead_anuncio, meta_gasto_anuncio \r\n FROM clinicorp.vw_agendamentos_com_leads \r\n WHERE meta_gasto_anuncio IS NOT NULL\r\n AND $__timeFilter(data_agendamento)\r\n \r\n -- A MÁGICA DOS FILTROS AQUI DENTRO (Com o 'TODOS' ativado)\r\n AND ('TODOS' IN ($plataforma) OR lead_plataforma IN ($plataforma))\r\n AND ('TODOS' IN ($campanha) OR lead_campanha IN ($campanha))\r\n AND ('TODOS' IN ($conjunto) OR lead_conjunto IN ($conjunto))\r\n AND ('TODOS' IN ($anuncio) OR lead_anuncio IN ($anuncio))\r\n \r\n ) ads_unicos\r\n)\r\nSELECT \r\n -- META ADS\r\n 0 AS meta_plano, -- Seu orçamento previsto para Meta (Mude aqui)\r\n gm.meta_total,\r\n \r\n -- GOOGLE ADS (Sem dados por enquanto)\r\n 0 AS google_plano, -- Seu orçamento previsto para Google (Mude aqui)\r\n 0 AS google_total, -- Ficará 0 até você ter a integração\r\n \r\n -- OUTRAS PLATAFORMAS (Sem dados por enquanto)\r\n 0 AS outras_plano, -- Seu orçamento previsto para Outros (Mude aqui)\r\n 0 AS outras_total, -- Ficará 0\r\n \r\n -- TOTAIS (Soma automática)\r\n 0 AS total_plano,\r\n (gm.meta_total + 0 + 0) AS total_investimento\r\n\r\nFROM gasto_meta gm;", "refId": "A", "sql": { "columns": [ { "parameters": [], "type": "function" } ], "groupBy": [ { "property": { "type": "string" }, "type": "groupBy" } ], "limit": 50 } } ], "transparent": true, "type": "marcusolsson-dynamictext-panel" }, { "datasource": { "type": "grafana-postgresql-datasource", "uid": "${DS_LAICON}" }, "fieldConfig": { "defaults": { "thresholds": { "mode": "absolute", "steps": [ { "color": "green", "value": 0 }, { "color": "red", "value": 80 } ] } }, "overrides": [] }, "gridPos": { "h": 27, "w": 9, "x": 13, "y": 15 }, "id": 4, "options": { "afterRender": "", "content": "", "contentPartials": [], "defaultContent": "", "editor": { "format": "auto", "language": "html" }, "editors": [ "helpers", "styles" ], "externalStyles": [], "helpers": "", "renderMode": "everyRow", "styles": ".grafana-ranking {\r\n display: flex;\r\n flex-direction: column;\r\n font-family: sans-serif;\r\n width: 100%;\r\n max-width: 650px;\r\n margin: 0 auto;\r\n gap: 20px; \r\n}\r\n\r\n/* Cabeçalho Principal */\r\n.ranking-main-header {\r\n background-color: #222;\r\n color: #ff6b00;\r\n text-align: center;\r\n font-weight: bold;\r\n font-size: 1.2rem;\r\n padding: 12px;\r\n border-radius: 8px;\r\n}\r\n\r\n/* ESTRUTURA DAS TABELAS */\r\n.ranking-table {\r\n width: 100%;\r\n border-collapse: collapse;\r\n border-spacing: 0;\r\n background-color: #f5f5f5; \r\n border-radius: 6px;\r\n overflow: hidden;\r\n box-shadow: 0 2px 4px rgba(0,0,0,0.1);\r\n table-layout: fixed; \r\n}\r\n\r\n.ranking-table thead th {\r\n background-color: #222 !important;\r\n color: #fff !important;\r\n padding: 12px 10px;\r\n font-size: 0.95rem;\r\n font-weight: normal;\r\n text-align: center;\r\n border: none !important; \r\n}\r\n\r\n/* Definindo a largura EXATA de cada coluna */\r\n.col-nome { width: calc(100% - 110px) !important; text-align: left !important; padding-left: 15px !important; }\r\n.col-qtd { width: 110px !important; text-align: center !important; }\r\n\r\n/* A MÁGICA DO ALINHAMENTO: Forçando o comportamento correto de tabela */\r\n.ranking-table tbody td {\r\n display: table-cell !important; /* Remove o display: flex que causava o bug */\r\n height: 48px !important; /* Trava a altura para ambas as colunas ficarem idênticas */\r\n padding: 0 15px !important;\r\n border-bottom: 1px solid #dcdcdc !important;\r\n border-left: none !important; \r\n border-right: none !important; \r\n border-top: none !important;\r\n vertical-align: middle !important; /* Centraliza tudo perfeitamente na vertical */\r\n background-color: transparent !important; \r\n}\r\n\r\n.ranking-table tbody tr:last-child td {\r\n border-bottom: none !important;\r\n}\r\n\r\n.ranking-table tbody tr:hover td {\r\n background-color: #e8e8e8 !important; \r\n}\r\n\r\n/* Coluna da Esquerda (Nome do Anúncio) */\r\n.ad-desc {\r\n width: 100%;\r\n white-space: nowrap;\r\n}\r\n\r\n/* Ícone alinhado em bloco (sem flexbox) */\r\n.data-icon {\r\n display: inline-block;\r\n vertical-align: middle;\r\n width: 16px;\r\n height: 16px;\r\n margin-right: 8px;\r\n background-size: contain;\r\n background-repeat: no-repeat;\r\n}\r\n\r\n.meta-icon { \r\n background-image: url('data:image/svg+xml;utf8,'); \r\n}\r\n\r\n/* Nome do anúncio alinhado em bloco */\r\n.ranking-name {\r\n display: inline-block;\r\n vertical-align: middle;\r\n font-weight: 600;\r\n color: #333;\r\n font-size: 0.85rem;\r\n max-width: calc(100% - 30px); /* Garante que o ícone não seja esmagado */\r\n overflow: hidden;\r\n text-overflow: ellipsis;\r\n cursor: help; \r\n}\r\n\r\n/* Coluna da Direita (Quantidade) */\r\n.data-val-col {\r\n text-align: center !important;\r\n}\r\n\r\n.data-val {\r\n display: inline-block;\r\n font-weight: bold;\r\n color: #222;\r\n background-color: rgba(0,0,0,0.08); \r\n padding: 4px 12px;\r\n border-radius: 4px;\r\n font-size: 0.9rem;\r\n min-width: 40px; \r\n text-align: center;\r\n}", "wrap": false }, "pluginVersion": "6.2.0", "targets": [ { "dataset": "laicon_crm", "datasource": { "type": "grafana-postgresql-datasource", "uid": "${DS_LAICON}" }, "editorMode": "code", "format": "table", "rawQuery": true, "rawSql": "WITH top_leads AS (\r\n SELECT string_agg(\r\n '' ||\r\n '' || COALESCE(anuncio, 'Sem nome') || '' ||\r\n '' || qtd || '' ||\r\n '', \r\n '') as html_leads\r\n FROM (\r\n SELECT anuncio, COUNT(id) as qtd\r\n FROM crm_leads.leads\r\n WHERE instancia = 'OdontoMarco' AND anuncio IS NOT NULL AND $__timeFilter(data)\r\n GROUP BY anuncio\r\n ORDER BY qtd DESC\r\n LIMIT 5\r\n ) t\r\n),\r\ntop_agendamentos AS (\r\n SELECT string_agg(\r\n '' ||\r\n '' || COALESCE(lead_anuncio, 'Sem nome') || '' ||\r\n '' || qtd || '' ||\r\n '', \r\n '') as html_agendamentos\r\n FROM (\r\n SELECT lead_anuncio, COUNT(DISTINCT agendamento_id) as qtd\r\n FROM clinicorp.vw_agendamentos_com_leads\r\n WHERE lead_anuncio IS NOT NULL AND $__timeFilter(data_agendamento)\r\n GROUP BY lead_anuncio\r\n ORDER BY qtd DESC\r\n LIMIT 5\r\n ) t\r\n),\r\ntop_vendas AS (\r\n SELECT string_agg(\r\n '' ||\r\n '' || COALESCE(lead_anuncio, 'Sem nome') || '' ||\r\n '' || qtd || '' ||\r\n '', \r\n '') as html_vendas\r\n FROM (\r\n SELECT lead_anuncio, COUNT(DISTINCT orcamento_id) as qtd\r\n FROM clinicorp.vw_agendamentos_com_leads\r\n WHERE status_type = 'CHECKOUT' \r\n AND orcamento_id IS NOT NULL \r\n AND orcamento_status = 'APPROVED'\r\n AND lead_anuncio IS NOT NULL \r\n AND $__timeFilter(data_agendamento)\r\n GROUP BY lead_anuncio\r\n ORDER BY qtd DESC\r\n LIMIT 5\r\n ) t\r\n)\r\nSELECT \r\n (SELECT html_leads FROM top_leads) AS html_leads,\r\n (SELECT html_agendamentos FROM top_agendamentos) AS html_agendamentos,\r\n (SELECT html_vendas FROM top_vendas) AS html_vendas;", "refId": "A", "sql": { "columns": [ { "parameters": [], "type": "function" } ], "groupBy": [ { "property": { "type": "string" }, "type": "groupBy" } ], "limit": 50 } } ], "transparent": true, "type": "marcusolsson-dynamictext-panel" }, { "collapsed": false, "gridPos": { "h": 1, "w": 24, "x": 0, "y": 42 }, "id": 7, "panels": [], "title": "Vendas x Metas", "type": "row" }, { "datasource": { "type": "grafana-postgresql-datasource", "uid": "${DS_LAICON}" }, "fieldConfig": { "defaults": { "thresholds": { "mode": "absolute", "steps": [ { "color": "green", "value": 0 }, { "color": "red", "value": 80 } ] } }, "overrides": [] }, "gridPos": { "h": 13, "w": 24, "x": 0, "y": 43 }, "id": 8, "options": { "afterRender": "", "content": "", "contentPartials": [], "defaultContent": "", "editor": { "format": "auto", "language": "html" }, "editors": [ "helpers", "styles" ], "externalStyles": [], "helpers": "const rastreadorGrafico = setInterval(() => {\r\n\r\n const container = document.getElementById('painel-vendas-grafico');\r\n const dados = document.getElementById('dados-grafico-vendas');\r\n\r\n if (!container || !dados) return;\r\n clearInterval(rastreadorGrafico);\r\n\r\n try {\r\n // Trava o painel para o balãozinho nascer no lugar exato\r\n container.style.position = 'relative';\r\n\r\n let labelsTxt = dados.getAttribute('data-labels') || '[]';\r\n let vendasTxt = dados.getAttribute('data-vendas') || '[]';\r\n let metasTxt = dados.getAttribute('data-meta') || '[]';\r\n\r\n labelsTxt = labelsTxt.replace(/"/g, '\"');\r\n vendasTxt = vendasTxt.replace(/"/g, '\"');\r\n metasTxt = metasTxt.replace(/"/g, '\"');\r\n\r\n const labels = JSON.parse(labelsTxt);\r\n const vendas = JSON.parse(vendasTxt);\r\n const metas = JSON.parse(metasTxt);\r\n let rawMax = parseFloat(dados.getAttribute('data-max')) || 1000;\r\n\r\n if (labels.length === 0) throw new Error(\"Sem dados na Query SQL.\");\r\n\r\n const formataBRL = (valor) => {\r\n return new Intl.NumberFormat('pt-BR', { style: 'currency', currency: 'BRL', maximumFractionDigits: 0 }).format(valor);\r\n };\r\n\r\n let multiplicador = rawMax > 100000 ? 50000 : 10000;\r\n let maxY = Math.ceil(rawMax / multiplicador) * multiplicador;\r\n if (maxY === 0) maxY = 10000;\r\n\r\n const yAxis = container.querySelector('.y-axis');\r\n const xAxis = container.querySelector('.x-axis');\r\n const barsContainer = container.querySelector('.bars-container');\r\n const dotsContainer = container.querySelector('.dots-container');\r\n const chartArea = container.querySelector('.chart-area');\r\n const tooltip = container.querySelector('#graficoTooltip');\r\n\r\n yAxis.innerHTML = ''; xAxis.innerHTML = '';\r\n barsContainer.innerHTML = ''; dotsContainer.innerHTML = '';\r\n chartArea.querySelectorAll('.grid-line, .line-overlay').forEach(e => e.remove());\r\n\r\n yAxis.style.width = '70px';\r\n container.querySelector('.x-axis').style.marginLeft = '70px';\r\n\r\n for (let i = 5; i >= 0; i--) {\r\n const val = (maxY / 5) * i;\r\n const topPct = 100 - (val / maxY * 100);\r\n let valFormatado = val >= 1000 ? `R$ ${(val / 1000).toFixed(0)}k` : formataBRL(val);\r\n yAxis.insertAdjacentHTML('beforeend', ``);\r\n chartArea.insertAdjacentHTML('beforeend', ``);\r\n }\r\n\r\n let svgPoints = [];\r\n\r\n // Monta as barras e injeta os dados nelas\r\n for (let i = 0; i < labels.length; i++) {\r\n const valVendas = parseFloat(vendas[i]) || 0;\r\n const valMeta = parseFloat(metas[i]) || 0;\r\n\r\n const hPct = (valVendas / maxY) * 100;\r\n const cx = (i + 0.5) * (100 / labels.length);\r\n const cy = 100 - ((valMeta / maxY) * 100);\r\n\r\n barsContainer.insertAdjacentHTML('beforeend', `\r\n \r\n `);\r\n\r\n xAxis.insertAdjacentHTML('beforeend', ``);\r\n svgPoints.push(`${cx},${cy}`);\r\n\r\n dotsContainer.insertAdjacentHTML('beforeend', `\r\n \r\n `);\r\n }\r\n\r\n const svg = document.createElementNS(\"http://www.w3.org/2000/svg\", \"svg\");\r\n svg.setAttribute(\"class\", \"line-overlay\");\r\n svg.setAttribute(\"viewBox\", \"0 0 100 100\");\r\n svg.setAttribute(\"preserveAspectRatio\", \"none\");\r\n svg.style.position = 'absolute'; svg.style.top = '0'; svg.style.left = '0';\r\n svg.style.width = '100%'; svg.style.height = '100%'; svg.style.zIndex = '10'; svg.style.overflow = 'visible';\r\n\r\n // A MÁGICA: O mouse agora atravessa o \"vidro\" da linha preta e toca nas barras!\r\n svg.style.pointerEvents = 'none';\r\n\r\n const polyline = document.createElementNS(\"http://www.w3.org/2000/svg\", \"polyline\");\r\n polyline.setAttribute(\"points\", svgPoints.join(' '));\r\n polyline.setAttribute(\"vector-effect\", \"non-scaling-stroke\");\r\n polyline.setAttribute(\"stroke\", \"#333\");\r\n polyline.setAttribute(\"stroke-width\", \"4\");\r\n polyline.setAttribute(\"fill\", \"none\");\r\n polyline.setAttribute(\"stroke-linejoin\", \"round\");\r\n\r\n svg.appendChild(polyline);\r\n chartArea.appendChild(svg);\r\n\r\n // ==========================================\r\n // MÁGICA DO MOUSE (HOVER E MOVIMENTO)\r\n // ==========================================\r\n const interativos = container.querySelectorAll('.bar-wrapper, .meta-dot');\r\n\r\n interativos.forEach(elemento => {\r\n elemento.addEventListener('mousemove', (e) => {\r\n const rect = container.getBoundingClientRect();\r\n const x = e.clientX - rect.left;\r\n const y = e.clientY - rect.top;\r\n\r\n const mes = elemento.getAttribute('data-mes');\r\n const venda = elemento.getAttribute('data-venda');\r\n const meta = elemento.getAttribute('data-meta');\r\n\r\n tooltip.innerHTML = `\r\n ${mes}\r\n Vendas: ${venda}\r\n Meta: ${meta}\r\n `;\r\n\r\n tooltip.style.left = `${x}px`;\r\n tooltip.style.top = `${y}px`;\r\n tooltip.style.opacity = '1';\r\n });\r\n\r\n elemento.addEventListener('mouseout', () => {\r\n tooltip.style.opacity = '0';\r\n });\r\n });\r\n\r\n } catch (err) {\r\n container.innerHTML = ``;\r\n }\r\n\r\n}, 100);", "renderMode": "everyRow", "styles": ".custom-chart-container {\r\n background-color: #eef0f2; /* Fundo cinza clarinho da imagem */\r\n border: 1px solid #d0d0d0;\r\n padding: 15px;\r\n font-family: sans-serif;\r\n border-radius: 4px;\r\n \r\n /* --- AJUSTE DE TAMANHO SOLICITADO --- */\r\n width: calc(100% - 20px); /* Diminui 10px da largura total */\r\n margin: 0 auto; /* Centraliza o painel se houver espaço sobressalente */\r\n box-sizing: border-box; /* Garante que o padding não aumente o tamanho final */\r\n /* ------------------------------------ */\r\n \r\n overflow: hidden; /* Evita barras de rolagem indesejadas */\r\n}\r\n\r\n.chart-header {\r\n background-color: #333;\r\n color: #fff;\r\n font-weight: bold;\r\n font-size: 1.1rem;\r\n padding: 10px;\r\n border-radius: 6px;\r\n text-align: center;\r\n margin-bottom: 20px;\r\n}\r\n\r\n.chart-legend {\r\n text-align: center;\r\n font-size: 0.9rem;\r\n color: #333;\r\n margin-bottom: 20px;\r\n}\r\n\r\n.box-orange { \r\n display: inline-block; \r\n width: 12px; \r\n height: 12px; \r\n background: #ff5500; \r\n margin-left: 5px; \r\n vertical-align: middle; \r\n}\r\n\r\n.box-black { \r\n display: inline-block; \r\n width: 12px; \r\n height: 12px; \r\n background: #333; \r\n margin-left: 5px; \r\n vertical-align: middle; \r\n}\r\n\r\n/* Estrutura do gráfico */\r\n.chart-body { \r\n display: flex; \r\n height: 260px; /* Altura principal do gráfico */\r\n position: relative; \r\n}\r\n\r\n/* Eixo Y (Valores na esquerda) */\r\n.y-axis { \r\n width: 40px; \r\n position: relative; \r\n}\r\n\r\n.y-label { \r\n position: absolute; \r\n right: 10px; \r\n transform: translateY(-50%); \r\n font-size: 0.75rem; \r\n color: #666; \r\n font-weight: 500;\r\n}\r\n\r\n/* Área do desenho (fundo com as linhas de grade) */\r\n.chart-area { \r\n flex: 1; \r\n position: relative; \r\n border-left: 1px solid #ccc; \r\n border-bottom: 1px solid #ccc; \r\n}\r\n\r\n.grid-line { \r\n position: absolute; \r\n width: 100%; \r\n height: 1px; \r\n background: #ccc; \r\n left: 0; \r\n z-index: 1;\r\n}\r\n\r\n/* Barras Laranjas */\r\n.bars-container { \r\n position: absolute; \r\n width: 100%; \r\n height: 100%; \r\n display: flex; \r\n z-index: 5; \r\n}\r\n\r\n.bar-wrapper { \r\n width: 8.3333%; /* 100% dividido por 12 meses */\r\n height: 100%; \r\n display: flex; \r\n align-items: flex-end; \r\n justify-content: center; \r\n position: relative; \r\n}\r\n\r\n.bar { \r\n width: 65%; \r\n background: #ff5500; /* Laranja da sua marca */\r\n transition: filter 0.2s; \r\n cursor: pointer; \r\n}\r\n\r\n.bar:hover { \r\n filter: brightness(1.15); /* Acende a barra ao passar o mouse */\r\n}\r\n\r\n/* Linha Preta da Meta e Bolinhas */\r\n.line-overlay { \r\n position: absolute; \r\n top: 0; \r\n left: 0; \r\n width: 100%; \r\n height: 100%; \r\n z-index: 10; \r\n overflow: visible; \r\n}\r\n\r\n.dots-container { \r\n position: absolute; \r\n top: 0; \r\n left: 0; \r\n width: 100%; \r\n height: 100%; \r\n z-index: 15; \r\n pointer-events: none; \r\n}\r\n\r\n.meta-dot { \r\n position: absolute; \r\n width: 14px; \r\n height: 14px; \r\n background: #333; \r\n border-radius: 50%; \r\n transform: translate(-50%, -50%); \r\n pointer-events: auto; \r\n cursor: help; \r\n}\r\n\r\n/* Eixo X (Meses embaixo do gráfico) */\r\n.x-axis { \r\n display: flex; \r\n margin-left: 40px; \r\n margin-top: 5px; \r\n}\r\n\r\n.x-label { \r\n width: 8.3333%; \r\n text-align: center; \r\n font-size: 0.8rem; \r\n color: #555; \r\n transform: rotate(-45deg); /* Dá aquele grau tortinho estiloso */\r\n padding-top: 10px; \r\n}\r\n/* =========================================\r\n ESTILO DO TOOLTIP FLUTUANTE (BALÃOZINHO)\r\n ========================================= */\r\n.grafico-tooltip {\r\n position: absolute;\r\n background-color: rgba(30, 30, 30, 0.95);\r\n color: #fff;\r\n padding: 10px 14px;\r\n border-radius: 6px;\r\n font-size: 0.85rem;\r\n pointer-events: none; /* Impede que o mouse trave no balão */\r\n opacity: 0; /* Escondido por padrão */\r\n transition: opacity 0.15s ease-in-out;\r\n z-index: 1000;\r\n white-space: nowrap;\r\n box-shadow: 0 4px 10px rgba(0,0,0,0.3);\r\n transform: translate(-50%, -100%); /* Centraliza acima do mouse */\r\n margin-top: -15px; /* Dá uma distanciada do cursor */\r\n border: 1px solid #555;\r\n}\r\n\r\n.grafico-tooltip .tt-mes {\r\n display: block;\r\n font-size: 0.75rem;\r\n color: #aaa;\r\n margin-bottom: 4px;\r\n text-transform: uppercase;\r\n letter-spacing: 1px;\r\n}\r\n\r\n.grafico-tooltip .tt-valor {\r\n display: block;\r\n font-size: 1.1rem;\r\n font-weight: bold;\r\n color: #ff5500; /* Laranja em destaque */\r\n}\r\n\r\n.grafico-tooltip .tt-meta {\r\n display: block;\r\n font-size: 0.8rem;\r\n color: #ddd;\r\n margin-top: 4px;\r\n border-top: 1px solid #444;\r\n padding-top: 4px;\r\n}", "wrap": false }, "pluginVersion": "6.2.0", "targets": [ { "dataset": "laicon_crm", "datasource": { "type": "grafana-postgresql-datasource", "uid": "${DS_LAICON}" }, "editorMode": "code", "format": "table", "rawQuery": true, "rawSql": "WITH months AS (\r\n -- Gera os últimos 12 meses\r\n SELECT DATE_TRUNC('month', CURRENT_DATE - INTERVAL '11 months' + (n || ' months')::interval) AS month_date\r\n FROM generate_series(0, 11) n\r\n),\r\ncohort_leads AS (\r\n -- 1. A CERCA: Pega todos os leads que entraram no CRM (Respeitando os filtros do Grafana)\r\n SELECT id AS lead_id\r\n FROM crm_leads.leads \r\n WHERE instancia = 'OdontoMarco'\r\n AND ('TODOS' IN ($plataforma) OR plataforma IN ($plataforma))\r\n AND ('TODOS' IN ($campanha) OR campanha IN ($campanha))\r\n AND ('TODOS' IN ($conjunto) OR conjunto IN ($conjunto))\r\n AND ('TODOS' IN ($anuncio) OR anuncio IN ($anuncio))\r\n),\r\nunique_sales AS (\r\n -- 2. AS VENDAS: Puxa apenas vendas de quem é lead E veio de anúncio\r\n SELECT DISTINCT ON (a.orcamento_id)\r\n DATE_TRUNC('month', a.data_agendamento) AS month_date,\r\n a.orcamento_id,\r\n COALESCE(a.valor_total, 0) AS valor_venda\r\n FROM clinicorp.vw_agendamentos_com_leads a\r\n \r\n -- O INNER JOIN blinda para trazer apenas pessoas que estão na lista de leads\r\n INNER JOIN cohort_leads c ON a.lead_id = c.lead_id\r\n \r\n WHERE a.status_type = 'CHECKOUT' \r\n AND a.orcamento_id IS NOT NULL \r\n AND a.orcamento_status = 'APPROVED' \r\n AND a.instancia = 'OdontoMarco'\r\n \r\n -- 🔥 A TRAVA FINAL: Garante que o lead obrigatoriamente tem uma origem de anúncio rastreada\r\n AND a.lead_anuncio IS NOT NULL \r\n \r\n AND a.data_agendamento >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '11 months')\r\n ORDER BY a.orcamento_id, a.data_agendamento DESC\r\n),\r\nmonthly_sales AS (\r\n -- 3. Soma os valores únicos por mês\r\n SELECT \r\n month_date,\r\n SUM(valor_venda) AS vendas\r\n FROM unique_sales\r\n GROUP BY 1\r\n),\r\ngoals (mes_num, meta) AS (\r\n -- Ajuste as suas metas financeiras mensais aqui\r\n VALUES (1, 100000), (2, 150000), (3, 120000), (4, 180000), (5, 200000), (6, 250000), \r\n (7, 220000), (8, 190000), (9, 210000), (10, 250000), (11, 300000), (12, 350000)\r\n),\r\ncombined AS (\r\n SELECT\r\n m.month_date,\r\n CASE EXTRACT(MONTH FROM m.month_date)\r\n WHEN 1 THEN 'Jan' WHEN 2 THEN 'Fev' WHEN 3 THEN 'Mar'\r\n WHEN 4 THEN 'Abr' WHEN 5 THEN 'Mai' WHEN 6 THEN 'Jun'\r\n WHEN 7 THEN 'Jul' WHEN 8 THEN 'Ago' WHEN 9 THEN 'Set'\r\n WHEN 10 THEN 'Out' WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dez'\r\n END AS mes_pt,\r\n COALESCE(s.vendas, 0) AS vendas,\r\n COALESCE(g.meta, 0) AS meta\r\n FROM months m\r\n LEFT JOIN monthly_sales s ON m.month_date = s.month_date\r\n LEFT JOIN goals g ON EXTRACT(MONTH FROM m.month_date) = g.mes_num\r\n)\r\n-- 4. Compacota os arrays JSON pro Grafana desenhar o gráfico\r\nSELECT\r\n COALESCE((SELECT json_agg(mes_pt ORDER BY month_date ASC)::text FROM combined), '[]') AS labels_json,\r\n COALESCE((SELECT json_agg(vendas ORDER BY month_date ASC)::text FROM combined), '[]') AS vendas_json,\r\n COALESCE((SELECT json_agg(meta ORDER BY month_date ASC)::text FROM combined), '[]') AS meta_json,\r\n COALESCE((SELECT MAX(GREATEST(vendas, meta)) FROM combined), 1000) AS max_val;", "refId": "A", "sql": { "columns": [ { "parameters": [], "type": "function" } ], "groupBy": [ { "property": { "type": "string" }, "type": "groupBy" } ], "limit": 50 } } ], "transparent": true, "type": "marcusolsson-dynamictext-panel" } ], "preload": false, "schemaVersion": 42, "tags": [], "templating": { "list": [ { "allowCustomValue": true, "current": {}, "datasource": { "type": "grafana-postgresql-datasource", "uid": "${DS_LAICON}" }, "definition": "SELECT DISTINCT lead_plataforma FROM clinicorp.vw_agendamentos_com_leads WHERE lead_plataforma IS NOT NULL ORDER BY 1;", "includeAll": true, "label": "Plataforma", "multi": true, "name": "plataforma", "options": [], "query": { "dataset": "laicon_crm", "editorMode": "code", "format": "table", "query": "SELECT DISTINCT lead_plataforma FROM clinicorp.vw_agendamentos_com_leads WHERE lead_plataforma IS NOT NULL ORDER BY 1;", "rawQuery": true, "rawSql": "SELECT DISTINCT lead_plataforma FROM clinicorp.vw_agendamentos_com_leads WHERE lead_plataforma IS NOT NULL ORDER BY 1;", "refId": "SQLVariableQueryEditor-VariableQuery", "sql": { "columns": [ { "parameters": [], "type": "function" } ], "groupBy": [ { "property": { "type": "string" }, "type": "groupBy" } ], "limit": 50 }, "table": "crm_leads.leads" }, "refresh": 1, "regex": "", "regexApplyTo": "value", "type": "query" }, { "allowCustomValue": true, "current": {}, "datasource": { "type": "grafana-postgresql-datasource", "uid": "${DS_LAICON}" }, "definition": "SELECT DISTINCT lead_campanha FROM clinicorp.vw_agendamentos_com_leads WHERE lead_campanha IS NOT NULL ORDER BY 1;", "description": "", "includeAll": true, "label": "Campanha", "multi": true, "name": "campanha", "options": [], "query": { "dataset": "laicon_crm", "editorMode": "code", "format": "table", "query": "SELECT DISTINCT lead_campanha FROM clinicorp.vw_agendamentos_com_leads WHERE lead_campanha IS NOT NULL ORDER BY 1;", "rawQuery": true, "rawSql": "SELECT DISTINCT lead_campanha FROM clinicorp.vw_agendamentos_com_leads WHERE lead_campanha IS NOT NULL ORDER BY 1;", "refId": "SQLVariableQueryEditor-VariableQuery", "sql": { "columns": [ { "parameters": [], "type": "function" } ], "groupBy": [ { "property": { "type": "string" }, "type": "groupBy" } ], "limit": 50 }, "table": "crm_leads.leads" }, "refresh": 1, "regex": "", "regexApplyTo": "value", "type": "query" }, { "allowCustomValue": true, "current": {}, "datasource": { "type": "grafana-postgresql-datasource", "uid": "${DS_LAICON}" }, "definition": "SELECT DISTINCT lead_conjunto FROM clinicorp.vw_agendamentos_com_leads WHERE lead_conjunto IS NOT NULL ORDER BY 1;", "description": "", "includeAll": true, "label": "Conjunto", "multi": true, "name": "conjunto", "options": [], "query": { "dataset": "laicon_crm", "editorMode": "code", "format": "table", "query": "SELECT DISTINCT lead_conjunto FROM clinicorp.vw_agendamentos_com_leads WHERE lead_conjunto IS NOT NULL ORDER BY 1;", "rawQuery": true, "rawSql": "SELECT DISTINCT lead_conjunto FROM clinicorp.vw_agendamentos_com_leads WHERE lead_conjunto IS NOT NULL ORDER BY 1;", "refId": "SQLVariableQueryEditor-VariableQuery", "sql": { "columns": [ { "parameters": [], "type": "function" } ], "groupBy": [ { "property": { "type": "string" }, "type": "groupBy" } ], "limit": 50 }, "table": "crm_leads.leads" }, "refresh": 1, "regex": "", "regexApplyTo": "value", "type": "query" }, { "allowCustomValue": true, "current": {}, "datasource": { "type": "grafana-postgresql-datasource", "uid": "${DS_LAICON}" }, "definition": "SELECT DISTINCT lead_anuncio FROM clinicorp.vw_agendamentos_com_leads WHERE lead_anuncio IS NOT NULL ORDER BY 1;", "description": "", "includeAll": true, "label": "Anúncio", "multi": true, "name": "anuncio", "options": [], "query": { "dataset": "laicon_crm", "editorMode": "code", "format": "table", "query": "SELECT DISTINCT lead_anuncio FROM clinicorp.vw_agendamentos_com_leads WHERE lead_anuncio IS NOT NULL ORDER BY 1;", "rawQuery": true, "rawSql": "SELECT DISTINCT lead_anuncio FROM clinicorp.vw_agendamentos_com_leads WHERE lead_anuncio IS NOT NULL ORDER BY 1;", "refId": "SQLVariableQueryEditor-VariableQuery", "sql": { "columns": [ { "parameters": [], "type": "function" } ], "groupBy": [ { "property": { "type": "string" }, "type": "groupBy" } ], "limit": 50 }, "table": "crm_leads.leads" }, "refresh": 1, "regex": "", "regexApplyTo": "value", "type": "query" } ] }, "time": { "from": "now-7d", "to": "now" }, "timepicker": {}, "timezone": "browser", "title": "Dashboard funil de vendas e performance", "uid": "adzhjzc", "version": 67, "weekStart": "" }