Tutorial: Dashboard de Analytics em Tempo Real

Construa um sistema completo de coleta, processamento e visualização de eventos em tempo real de múltiplas fontes com alertas inteligentes.

O Que Você Vai Construir

Sistema de analytics que: 1. Coleta eventos de apps web/mobile, APIs, webhooks 2. Processa e enriquece dados em tempo real 3. Calcula métricas agregadas 4. Detecta anomalias automaticamente 5. Envia alertas para equipe 6. Gera relatórios visuais

Tempo estimado: 50 minutos Nível: Avançado Impacto: Insights em tempo real, decisões baseadas em dados

Pré-requisitos

• ✅ PostgreSQL ou TimescaleDB
• ✅ Redis (opcional, para cache)
• ✅ Metabase ou Grafana (visualização)

Estrutura

-- Extensão TimescaleDB para séries temporais (opcional mas recomendado)
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Eventos brutos
CREATE TABLE analytics_events (
  id BIGSERIAL PRIMARY KEY,
  event_name VARCHAR(100) NOT NULL,
  event_type VARCHAR(50),
  user_id VARCHAR(255),
  session_id VARCHAR(255),
  properties JSONB,
  user_properties JSONB,
  device_info JSONB,
  location_info JSONB,
  timestamp TIMESTAMP NOT NULL DEFAULT NOW(),
  processed BOOLEAN DEFAULT FALSE
);

-- Converter para hypertable (TimescaleDB)
SELECT create_hypertable('analytics_events', 'timestamp', if_not_exists => TRUE);

-- Métricas agregadas (por hora)
CREATE TABLE metrics_hourly (
  id SERIAL PRIMARY KEY,
  metric_name VARCHAR(100),
  metric_type VARCHAR(50),
  value DECIMAL(20,4),
  dimensions JSONB,
  period_start TIMESTAMP,
  period_end TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Métricas diárias
CREATE TABLE metrics_daily (
  id SERIAL PRIMARY KEY,
  metric_name VARCHAR(100),
  metric_type VARCHAR(50),
  value DECIMAL(20,4),
  dimensions JSONB,
  date DATE,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Alertas configurados
CREATE TABLE alert_rules (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  metric_name VARCHAR(100),
  condition VARCHAR(50), -- greater_than, less_than, equals, percent_change
  threshold DECIMAL(20,4),
  timeframe_minutes INT DEFAULT 60,
  enabled BOOLEAN DEFAULT TRUE,
  notification_channels JSONB,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Histórico de alertas
CREATE TABLE alert_history (
  id SERIAL PRIMARY KEY,
  rule_id INT REFERENCES alert_rules(id),
  metric_value DECIMAL(20,4),
  threshold_value DECIMAL(20,4),
  message TEXT,
  triggered_at TIMESTAMP DEFAULT NOW()
);

-- Índices
CREATE INDEX idx_events_timestamp ON analytics_events(timestamp DESC);
CREATE INDEX idx_events_user ON analytics_events(user_id);
CREATE INDEX idx_events_name ON analytics_events(event_name);
CREATE INDEX idx_events_processed ON analytics_events(processed);
CREATE INDEX idx_metrics_hourly_period ON metrics_hourly(period_start);
CREATE INDEX idx_metrics_daily_date ON metrics_daily(date);

Parte 1: Coleta de Eventos

1.1. Webhook para Receber Eventos

// Webhook Trigger: "Receber Evento"
// URL: https://api.lumina.app.br/v1/webhooks/wh_analytics

// Transform: "Processar Evento"
{{
  {
    "eventName": $trigger.body.event || $trigger.body.name || 'unnamed_event',
    "eventType": $trigger.body.type || 'custom',
    "userId": $trigger.body.user_id || $trigger.body.userId || null,
    "sessionId": $trigger.body.session_id || $trigger.body.sessionId || null,
    "properties": $trigger.body.properties || {},
    "userProperties": $trigger.body.user || $trigger.body.user_properties || {},
    "deviceInfo": {
      "userAgent": $trigger.headers['user-agent'],
      "ip": $trigger.headers['x-forwarded-for'] || $trigger.headers['x-real-ip'],
      "platform": $trigger.body.platform,
      "appVersion": $trigger.body.app_version
    },
    "timestamp": $trigger.body.timestamp || new Date().toISOString()
  }
}}

1.2. Enriquecimento de Dados

// Function: "Enriquecer Evento"
{{
  (() => {
    const event = $nodes['processar'].output;

    // GeoIP (simplificado - em produção use MaxMind ou similar)
    let locationInfo = {
      country: null,
      city: null,
      timezone: null
    };

    if (event.deviceInfo.ip) {
      // Chamada para API de GeoIP seria aqui
      locationInfo = {
        country: 'BR',
        city: 'São Paulo',
        timezone: 'America/Sao_Paulo'
      };
    }

    // Enriquecer propriedades
    const enriched = {
      ...event,
      locationInfo,
      processedAt: new Date().toISOString()
    };

    return enriched;
  })()
}}

1.3. Salvar Evento

// Database Insert: "Salvar Evento"
{
  "table": "analytics_events",
  "data": {
    "event_name": "{{ $nodes['enriquecer'].output.eventName }}",
    "event_type": "{{ $nodes['enriquecer'].output.eventType }}",
    "user_id": "{{ $nodes['enriquecer'].output.userId }}",
    "session_id": "{{ $nodes['enriquecer'].output.sessionId }}",
    "properties": "{{ JSON.stringify($nodes['enriquecer'].output.properties) }}",
    "user_properties": "{{ JSON.stringify($nodes['enriquecer'].output.userProperties) }}",
    "device_info": "{{ JSON.stringify($nodes['enriquecer'].output.deviceInfo) }}",
    "location_info": "{{ JSON.stringify($nodes['enriquecer'].output.locationInfo) }}",
    "timestamp": "{{ $nodes['enriquecer'].output.timestamp }}",
    "processed": false
  }
}

Parte 2: Agregação de Métricas

2.1. Flow Agendado (a cada 5 minutos)

// Schedule Trigger: */5 * * * * (a cada 5 minutos)

// Database Query: "Eventos Não Processados"
SELECT
  event_name,
  event_type,
  COUNT(*) as event_count,
  COUNT(DISTINCT user_id) as unique_users,
  COUNT(DISTINCT session_id) as unique_sessions,
  date_trunc('hour', timestamp) as period_start
FROM analytics_events
WHERE processed = FALSE
  AND timestamp >= NOW() - INTERVAL '1 hour'
GROUP BY event_name, event_type, period_start;

2.2. Calcular Métricas

// Function: "Calcular Métricas"
{{
  (() => {
    const events = $nodes['buscar-eventos'].output.rows;

    const metrics = [];

    events.forEach(event => {
      // Total de eventos
      metrics.push({
        metric_name: `${event.event_name}.count`,
        metric_type: 'counter',
        value: event.event_count,
        dimensions: {
          event_type: event.event_type
        },
        period_start: event.period_start,
        period_end: new Date(new Date(event.period_start).getTime() + 60*60*1000).toISOString()
      });

      // Usuários únicos
      metrics.push({
        metric_name: `${event.event_name}.unique_users`,
        metric_type: 'gauge',
        value: event.unique_users,
        dimensions: {
          event_type: event.event_type
        },
        period_start: event.period_start,
        period_end: new Date(new Date(event.period_start).getTime() + 60*60*1000).toISOString()
      });

      // Sessões únicas
      metrics.push({
        metric_name: `${event.event_name}.unique_sessions`,
        metric_type: 'gauge',
        value: event.unique_sessions,
        dimensions: {
          event_type: event.event_type
        },
        period_start: event.period_start,
        period_end: new Date(new Date(event.period_start).getTime() + 60*60*1000).toISOString()
      });
    });

    return metrics;
  })()
}}

2.3. Salvar Métricas

// Database Insert (Batch): "Salvar Métricas Horárias"
{
  "table": "metrics_hourly",
  "data": "{{ $nodes['calcular-metricas'].output }}"
}

// Database Update: "Marcar Eventos Como Processados"
{
  "query": `
    UPDATE analytics_events
    SET processed = TRUE
    WHERE processed = FALSE
      AND timestamp >= NOW() - INTERVAL '1 hour'
  `
}

Parte 3: Cálculo de Métricas Especializadas

3.1. Taxa de Conversão

-- Adicionar à query de métricas:
WITH funnel AS (
  SELECT
    COUNT(*) FILTER (WHERE event_name = 'page_view') as views,
    COUNT(*) FILTER (WHERE event_name = 'add_to_cart') as add_to_cart,
    COUNT(*) FILTER (WHERE event_name = 'checkout_started') as checkout,
    COUNT(*) FILTER (WHERE event_name = 'purchase_completed') as purchases
  FROM analytics_events
  WHERE timestamp >= date_trunc('hour', NOW())
    AND timestamp < date_trunc('hour', NOW()) + INTERVAL '1 hour'
)
SELECT
  'conversion_rate' as metric_name,
  ROUND((purchases::DECIMAL / NULLIF(views, 0)) * 100, 2) as value
FROM funnel;

3.2. Tempo Médio de Sessão

WITH session_durations AS (
  SELECT
    session_id,
    EXTRACT(EPOCH FROM (MAX(timestamp) - MIN(timestamp))) as duration_seconds
  FROM analytics_events
  WHERE timestamp >= date_trunc('hour', NOW())
    AND timestamp < date_trunc('hour', NOW()) + INTERVAL '1 hour'
    AND session_id IS NOT NULL
  GROUP BY session_id
)
SELECT
  'avg_session_duration' as metric_name,
  AVG(duration_seconds) as value
FROM session_durations;

3.3. Usuários Ativos (DAU/MAU)

-- DAU (Daily Active Users)
SELECT
  'dau' as metric_name,
  COUNT(DISTINCT user_id) as value,
  CURRENT_DATE as date
FROM analytics_events
WHERE timestamp >= CURRENT_DATE
  AND timestamp < CURRENT_DATE + INTERVAL '1 day'
  AND user_id IS NOT NULL;

-- MAU (Monthly Active Users)
SELECT
  'mau' as metric_name,
  COUNT(DISTINCT user_id) as value,
  date_trunc('month', CURRENT_DATE) as date
FROM analytics_events
WHERE timestamp >= date_trunc('month', CURRENT_DATE)
  AND timestamp < date_trunc('month', CURRENT_DATE) + INTERVAL '1 month'
  AND user_id IS NOT NULL;

-- Stickiness (DAU/MAU ratio)
WITH dau AS (
  SELECT COUNT(DISTINCT user_id) as count
  FROM analytics_events
  WHERE timestamp >= CURRENT_DATE AND user_id IS NOT NULL
),
mau AS (
  SELECT COUNT(DISTINCT user_id) as count
  FROM analytics_events
  WHERE timestamp >= date_trunc('month', CURRENT_DATE) AND user_id IS NOT NULL
)
SELECT
  'stickiness' as metric_name,
  ROUND((dau.count::DECIMAL / NULLIF(mau.count, 0)) * 100, 2) as value
FROM dau, mau;

Parte 4: Detecção de Anomalias

4.1. Configurar Regras de Alerta

-- Inserir regras exemplo
INSERT INTO alert_rules (name, metric_name, condition, threshold, timeframe_minutes, notification_channels) VALUES
('Quedas de Tráfego', 'page_view.count', 'percent_decrease', -30, 60, '{"slack": "#alertas", "email": "ops@empresa.com"}'),
('Picos de Erro', 'error.count', 'greater_than', 100, 30, '{"slack": "#tech", "pagerduty": "P1"}'),
('Taxa de Conversão Baixa', 'conversion_rate', 'less_than', 2.0, 120, '{"slack": "#growth"}'),
('Latência Alta', 'api_latency.p95', 'greater_than', 1000, 15, '{"slack": "#tech", "email": "sre@empresa.com"}');

4.2. Flow de Monitoramento (a cada 5 minutos)

// Schedule Trigger

// Database Query: "Buscar Regras Ativas"
SELECT * FROM alert_rules WHERE enabled = TRUE;

// Para cada regra:

// Database Query: "Calcular Métrica Atual"
{{
  const rule = $item;
  const timeframeStart = new Date(Date.now() - rule.timeframe_minutes * 60 * 1000).toISOString();

  // Query dinâmica baseada na métrica
  return `
    SELECT
      AVG(value) as current_value,
      MIN(value) as min_value,
      MAX(value) as max_value
    FROM metrics_hourly
    WHERE metric_name = '${rule.metric_name}'
      AND period_start >= '${timeframeStart}'
  `;
}}

// Function: "Verificar Condição"
{{
  (() => {
    const rule = $item;
    const metrics = $nodes['calcular-metrica'].output.rows[0];

    if (!metrics || metrics.current_value === null) {
      return { triggered: false };
    }

    const currentValue = parseFloat(metrics.current_value);
    const threshold = parseFloat(rule.threshold);

    let triggered = false;
    let message = '';

    switch (rule.condition) {
      case 'greater_than':
        triggered = currentValue > threshold;
        message = `${rule.metric_name} está em ${currentValue.toFixed(2)}, acima do limite de ${threshold}`;
        break;

      case 'less_than':
        triggered = currentValue < threshold;
        message = `${rule.metric_name} está em ${currentValue.toFixed(2)}, abaixo do limite de ${threshold}`;
        break;

      case 'percent_decrease':
        // Comparar com período anterior
        const previousPeriod = $nodes['calcular-periodo-anterior']?.output.rows[0];
        if (previousPeriod && previousPeriod.previous_value) {
          const percentChange = ((currentValue - previousPeriod.previous_value) / previousPeriod.previous_value) * 100;
          triggered = percentChange < threshold;
          message = `${rule.metric_name} caiu ${Math.abs(percentChange).toFixed(1)}% (de ${previousPeriod.previous_value.toFixed(2)} para ${currentValue.toFixed(2)})`;
        }
        break;
    }

    return {
      triggered,
      currentValue,
      threshold,
      message
    };
  })()
}}

// If/Else: "Alerta Disparado?"
{{ $nodes['verificar-condicao'].output.triggered }}

4.3. Enviar Alertas

// Branch TRUE:

// Database Insert: "Registrar Alerta"
{
  "table": "alert_history",
  "data": {
    "rule_id": "{{ $item.id }}",
    "metric_value": "{{ $nodes['verificar-condicao'].output.currentValue }}",
    "threshold_value": "{{ $nodes['verificar-condicao'].output.threshold }}",
    "message": "{{ $nodes['verificar-condicao'].output.message }}"
  }
}

// Slack Message: "Notificar Time"
{
  "channel": "{{ JSON.parse($item.notification_channels).slack }}",
  "text": "🚨 Alerta de Analytics",
  "blocks": [
    {
      "type": "header",
      "text": {
        "type": "plain_text",
        "text": "🚨 {{ $item.name }}"
      }
    },
    {
      "type": "section",
      "text": {
        "type": "mrkdwn",
        "text": `*Métrica:* \`{{ $item.metric_name }}\`\n*Condição:* {{ $item.condition }}\n*Threshold:* {{ $item.threshold }}\n*Valor Atual:* {{ $nodes['verificar-condicao'].output.currentValue }}\n\n{{ $nodes['verificar-condicao'].output.message }}`
      }
    },
    {
      "type": "context",
      "elements": [
        {
          "type": "mrkdwn",
          "text": `Timeframe: últimos {{ $item.timeframe_minutes }} minutos | {{ new Date().toLocaleString('pt-BR') }}`
        }
      ]
    }
  ]
}

Parte 5: Dashboards e Visualizações

5.1. Queries para Metabase/Grafana

-- Eventos por hora (últimas 24h)
SELECT
  date_trunc('hour', timestamp) as hour,
  event_name,
  COUNT(*) as count
FROM analytics_events
WHERE timestamp >= NOW() - INTERVAL '24 hours'
GROUP BY hour, event_name
ORDER BY hour DESC;

-- Top páginas visitadas
SELECT
  properties->>'page_url' as page,
  COUNT(*) as views,
  COUNT(DISTINCT user_id) as unique_visitors
FROM analytics_events
WHERE event_name = 'page_view'
  AND timestamp >= NOW() - INTERVAL '7 days'
GROUP BY page
ORDER BY views DESC
LIMIT 20;

-- Funil de conversão
WITH funnel_steps AS (
  SELECT
    user_id,
    MAX(CASE WHEN event_name = 'page_view' THEN 1 ELSE 0 END) as viewed,
    MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) as added_cart,
    MAX(CASE WHEN event_name = 'checkout_started' THEN 1 ELSE 0 END) as started_checkout,
    MAX(CASE WHEN event_name = 'purchase_completed' THEN 1 ELSE 0 END) as purchased
  FROM analytics_events
  WHERE timestamp >= NOW() - INTERVAL '7 days'
    AND user_id IS NOT NULL
  GROUP BY user_id
)
SELECT
  SUM(viewed) as "1. Visualizações",
  SUM(added_cart) as "2. Adicionou ao Carrinho",
  SUM(started_checkout) as "3. Iniciou Checkout",
  SUM(purchased) as "4. Comprou",
  ROUND((SUM(purchased)::DECIMAL / NULLIF(SUM(viewed), 0)) * 100, 2) as "Taxa de Conversão %"
FROM funnel_steps;

-- Dispositivos mais usados
SELECT
  device_info->>'platform' as platform,
  COUNT(*) as sessions,
  COUNT(DISTINCT user_id) as users
FROM analytics_events
WHERE timestamp >= NOW() - INTERVAL '30 days'
GROUP BY platform
ORDER BY sessions DESC;

-- Países com mais tráfego
SELECT
  location_info->>'country' as country,
  COUNT(DISTINCT session_id) as sessions,
  AVG(EXTRACT(EPOCH FROM (MAX(timestamp) - MIN(timestamp)))) as avg_session_duration_seconds
FROM analytics_events
WHERE timestamp >= NOW() - INTERVAL '30 days'
  AND session_id IS NOT NULL
GROUP BY country
ORDER BY sessions DESC
LIMIT 10;

5.2. Relatório Automático Diário

// Schedule Trigger: 0 9 * * * (todo dia às 9h)

// Database Query: "Métricas de Ontem"
SELECT
  'dau' as metric,
  COUNT(DISTINCT user_id) as value
FROM analytics_events
WHERE DATE(timestamp) = CURRENT_DATE - 1;

-- ... mais queries

// Function: "Gerar Relatório HTML"
{{
  (() => {
    const metrics = $nodes['buscar-metricas'].output.rows;

    return {
      html: `
        <h1>📊 Relatório Diário - ${new Date().toLocaleDateString('pt-BR')}</h1>

        <h2>Usuários</h2>
        <ul>
          <li><strong>DAU:</strong> ${metrics.dau} usuários ativos</li>
          <li><strong>Novos Usuários:</strong> ${metrics.new_users}</li>
          <li><strong>Taxa de Retenção:</strong> ${metrics.retention_rate}%</li>
        </ul>

        <h2>Engajamento</h2>
        <ul>
          <li><strong>Sessões:</strong> ${metrics.sessions}</li>
          <li><strong>Duração Média:</strong> ${Math.round(metrics.avg_session_duration / 60)} minutos</li>
          <li><strong>Páginas por Sessão:</strong> ${metrics.pages_per_session}</li>
        </ul>

        <h2>Conversão</h2>
        <ul>
          <li><strong>Taxa de Conversão:</strong> ${metrics.conversion_rate}%</li>
          <li><strong>Vendas:</strong> ${metrics.purchases}</li>
          <li><strong>Receita:</strong> R$ ${metrics.revenue.toFixed(2)}</li>
        </ul>

        <p><a href="https://dashboard.empresa.com/analytics">Ver Dashboard Completo</a></p>
      `
    };
  })()
}}

// Send Email: "Enviar Relatório"
{
  "to": ["growth@empresa.com", "produto@empresa.com"],
  "subject": "📊 Relatório Diário de Analytics - {{ new Date().toLocaleDateString('pt-BR') }}",
  "html": "{{ $nodes['gerar-relatorio'].output.html }}"
}

Exemplo de Uso (JavaScript SDK)

// Enviar evento do frontend
fetch('https://api.lumina.app.br/v1/webhooks/wh_analytics', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    event: 'page_view',
    type: 'pageview',
    user_id: 'user_123',
    session_id: 'session_456',
    properties: {
      page_url: window.location.href,
      page_title: document.title,
      referrer: document.referrer
    },
    user_properties: {
      plan: 'premium',
      signup_date: '2025-01-01'
    },
    platform: 'web',
    app_version: '2.0.0'
  })
});

Próximos Passos

1. Data Sync - Sincronize dados
2. Automated Reports - Relatórios avançados
3. Feedback Loop - Análise de sentimento

---

Última atualização: Janeiro 2025