intent_score.sql

intent_score.sql

WITH
eventos_base_extraidos AS (
    SELECT
        visitor_id, session_id, inserted_date, created, event_name, relative_view, items, timeago,
        jsonb_extract_path_text(data, 'firstname') AS firstname,
        jsonb_extract_path_text(data, 'status') AS session_status,
        jsonb_extract_path_text(data, 'element_label') AS element_label,
        jsonb_extract_path_text(utm_case, 'argumento') AS argumento_busca
    FROM bronze.events, jsonb_array_elements(event_data) AS data
    WHERE inserted_date >= EXTRACT(EPOCH FROM NOW() - INTERVAL '${INTERVAL} minutes')
),
sessoes_com_autenticacao AS (
    SELECT *, MAX(CASE WHEN firstname IS NOT NULL THEN 1 ELSE 0 END) OVER (PARTITION BY visitor_id, session_id) AS sessao_foi_autenticada
    FROM eventos_base_extraidos
),
carrinho_final_estado AS (
    SELECT DISTINCT ON (visitor_id, session_id)
        visitor_id, session_id,
        SUM(CAST(item ->> 'quantity' AS BIGINT)) AS carrinho_total_itens,
        SUM(CAST(NULLIF(item ->> 'value', '') AS NUMERIC) / 100.0) AS carrinho_valor_total,
        COUNT(DISTINCT(item ->> 'item_id')) AS carrinho_itens_distintos
    FROM sessoes_com_autenticacao, jsonb_array_elements(items) AS item
    WHERE event_name = 'view_content' AND items IS NOT NULL AND jsonb_array_length(items) > 0
        AND (item ->> 'item_name') IS NOT NULL AND (item ->> 'item_name') <> 'null'
    GROUP BY visitor_id, session_id, inserted_date
    ORDER BY visitor_id, session_id, inserted_date DESC
)
SELECT
    s.visitor_id, s.session_id,
    MAX(CASE WHEN s.event_name = 'view_content' AND s.relative_view = '/pedido/pedido-finalizado' THEN 1 ELSE 0 END) AS comprou,
    COALESCE(MAX(CASE WHEN s.timeago >= 0 THEN s.timeago END), (MAX(s.inserted_date) - MIN(s.inserted_date)), 0) AS sessao_duracao_segundos,
    COUNT(DISTINCT CASE WHEN s.event_name = 'view_content' THEN s.relative_view END) AS sessao_total_pageviews,
    MAX(s.sessao_foi_autenticada) AS sessao_usuario_autenticado,
    COALESCE(MAX(CASE WHEN s.event_name = 'view_content' AND jsonb_array_length(s.items) > 0 THEN 1 ELSE 0 END), 0) AS carrinho_adicionou_produto,
    COALESCE(cf.carrinho_total_itens, 0) AS carrinho_total_itens,
    COALESCE(cf.carrinho_valor_total, 0) AS carrinho_valor_total,
    COALESCE(cf.carrinho_itens_distintos, 0) AS carrinho_itens_distintos,
    CASE WHEN COALESCE(cf.carrinho_itens_distintos, 0) > 0 THEN COALESCE(cf.carrinho_total_itens, 0)::NUMERIC / cf.carrinho_itens_distintos ELSE 0 END AS carrinho_media_itens_por_produto,
    MAX(CASE WHEN s.event_name = 'click' AND s.relative_view = '/pedido/exibir-carrinho' AND s.element_label = 'Continuar' THEN 1 ELSE 0 END) AS sessao_visitou_checkout,
    MAX(CASE WHEN s.event_name = 'view_content' AND s.relative_view = '/pedido/confirmacao' THEN 1 ELSE 0 END) AS sessao_visitou_pagamento,
    MAX(CASE WHEN s.element_label LIKE '%Pagar com PIX%' THEN 'PIX' WHEN s.element_label LIKE '%Pagar na Loja%' THEN 'Loja' WHEN s.element_label LIKE '%Utilizar um Cartão%' THEN 'Cartao' ELSE NULL END) AS sessao_forma_pagamento_escolhida,
    MAX(CASE WHEN s.argumento_busca IS NOT NULL THEN 1 ELSE 0 END) AS sessao_usou_busca,
    COUNT(DISTINCT CASE WHEN s.relative_view IN ('/material-hidraulico', '/loucas-e-metais', '/pintura', '/iluminacao-e-eletrica', '/portas-e-janelas', '/ferragens-e-ferramentas', '/decoracao', '/utilidades-domesticas', '/pisos-e-revestimentos', '/armarios-tanques-e-tanques', '/outros', '/jardinagem') THEN s.relative_view END) AS sessao_qtde_categorias_distintas,
    MAX(CASE WHEN s.relative_view IN ('/institucional/lojas', '/sobre-nos', '/institucional/historia') THEN 1 ELSE 0 END) AS sessao_visitou_paginas_institucionais,
    CASE WHEN COUNT(DISTINCT CASE WHEN s.event_name = 'view_content' THEN s.relative_view END) = 1 AND MAX(s.relative_view) IN ('/', '/#') THEN 1 ELSE 0 END AS sessao_apenas_homepage
FROM sessoes_com_autenticacao AS s
LEFT JOIN carrinho_final_estado AS cf ON s.visitor_id = cf.visitor_id AND s.session_id = cf.session_id
GROUP BY s.visitor_id, s.session_id, cf.carrinho_total_itens, cf.carrinho_valor_total, cf.carrinho_itens_distintos;