prepare( "SELECT DISTINCT utm_source FROM asksuite_leads WHERE utm_source IS NOT NULL AND fetch_date BETWEEN :start AND :end ORDER BY utm_source" ); $stmt->execute([':start' => $start, ':end' => $end]); $channels = $stmt->fetchAll(PDO::FETCH_COLUMN); $stmt = $pdo->prepare( "SELECT tags FROM asksuite_leads WHERE JSON_LENGTH(tags) > 0 AND fetch_date BETWEEN :start AND :end" ); $stmt->execute([':start' => $start, ':end' => $end]); $allTags = []; foreach ($stmt->fetchAll(PDO::FETCH_COLUMN) as $jsonTags) { $arr = json_decode($jsonTags, true); if (is_array($arr)) { foreach ($arr as $t) { $allTags[$t] = true; } } } sort($channels); $tags = array_keys($allTags); sort($tags); // 3) Monta query de estatísticas diárias $sql = << $start, ':end' => $end]; if ($utm_source) { $sql .= " AND utm_source = :utm_source"; $params[':utm_source'] = $utm_source; } if ($tag) { $sql .= " AND JSON_CONTAINS(tags, JSON_QUOTE(:tag), '$')"; $params[':tag'] = $tag; } $sql .= " GROUP BY fetch_date ORDER BY fetch_date"; $stmt = $pdo->prepare($sql); $stmt->execute($params); $detail_daily = $stmt->fetchAll(PDO::FETCH_ASSOC); // 4) Totais gerais e percentuais $totals = array_fill_keys([ 'total_leads','leads_req_price','leads_geral', 'cotacoes_abertas','nao_clicou','reservas','receita_potencial' ], 0); foreach ($detail_daily as $row) { foreach ($totals as $k => $_) { $totals[$k] += $row[$k]; } } function pct($n, $d) { return $d > 0 ? round($n / $d * 100, 2) : 0; } $totals['pct_pediu_preco'] = pct($totals['leads_req_price'], $totals['total_leads']); $totals['pct_abertura_cotacao'] = pct($totals['cotacoes_abertas'], $totals['leads_req_price']); $totals['pct_conversao_final'] = pct($totals['reservas'], $totals['total_leads']); ?> Dashboard Leads AskSuite

Dashboard Leads AskSuite

Totais ( a )

Total Leads	Pediu Preço	Geral	Cotações Abertas	Não Clicou	Reservas	Receita Potencial	% Pediu Preço	% Abertura	% Conversão
						R$	%	%	%

Detalhamento Diário

Data	Total	Pediu Preço	Cotações Abertas	Reservas	Receita Potencial
					R$