Exemplos de consultas para dados do Adobe Analytics

Os dados dos conjuntos de relatórios selecionados do Adobe Analytics são transformados em dados que estão em conformidade com a classe XDM ExperienceEvent e assimilados no Adobe Experience Platform como conjuntos de dados.

Este documento descreve uma série de casos de uso em que o Adobe Experience Platform Query Service usa esses dados, incluindo consultas de exemplo, devem funcionar com seus conjuntos de dados do Adobe Analytics. Consulte a documentação em Mapeamento de campo do Analytics para obter mais informações sobre o mapeamento para Experience Events.

Introdução

Os exemplos de SQL em todo este documento exigem que você edite o SQL e preencha os parâmetros esperados para suas consultas com base no conjunto de dados, eVar, evento ou período que você está interessado em avaliar. Forneça parâmetros onde quer que você veja { } nos exemplos de SQL a seguir.

Exemplos de SQL comumente usados

Os exemplos a seguir mostram queries SQL comumente usados para analisar seus dados do Adobe Analytics.

Contagem de visitantes por hora para um determinado dia

SELECT Substring(from_utc_timestamp(timestamp, 'America/New_York'), 1, 10) AS Day,
       Substring(from_utc_timestamp(timestamp, 'America/New_York'), 12, 2) AS Hour, 
       Count(DISTINCT enduserids._experience.aaid.id) AS Visitor_Count 
FROM   {TARGET_TABLE}
WHERE TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
GROUP BY Day, Hour
ORDER BY Hour;

As 10 principais páginas visualizadas de um determinado dia

SELECT web.webpagedetails.name AS Page_Name, 
       Sum(web.webpagedetails.pageviews.value) AS Page_Views 
FROM   {TARGET_TABLE}
WHERE TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
GROUP BY web.webpagedetails.name 
ORDER BY page_views DESC 
LIMIT  10;

Os 10 usuários mais ativos

SELECT enduserids._experience.aaid.id AS aaid, 
       Count(timestamp) AS Count
FROM   {TARGET_TABLE}
WHERE TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
GROUP BY enduserids._experience.aaid.id
ORDER BY Count DESC
LIMIT  10;

As 10 principais cidades por atividade do usuário

SELECT concat(placeContext.geo.stateProvince, ' - ', placeContext.geo.city) AS state_city, 
       Count(timestamp) AS Count
FROM   {TARGET_TABLE}
WHERE TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
GROUP BY state_city
ORDER BY Count DESC
LIMIT  10;

Os 10 principais produtos visualizados

SELECT Product_SKU,
       Sum(Product_Views) AS Total_Product_Views
FROM  (SELECT Explode(productlistitems.sku) AS Product_SKU, 
              commerce.productviews.value   AS Product_Views 
       FROM   {TARGET_TABLE}
            WHERE TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
              AND commerce.productviews.value IS NOT NULL) 
GROUP BY Product_SKU 
ORDER BY Total_Product_Views DESC
LIMIT  10;

Os 10 principais totais de receita de pedidos

SELECT Purchase_ID, 
       Round(Sum(Product_Items.priceTotal * Product_Items.quantity), 2) AS Total_Order_Revenue 
FROM   (SELECT commerce.`order`.purchaseid AS Purchase_ID, 
               Explode(productlistitems)   AS Product_Items 
        FROM   {TARGET_TABLE} 
        WHERE  commerce.`order`.purchaseid IS NOT NULL 
                AND TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')

GROUP BY Purchase_ID 
ORDER BY total_order_revenue DESC 
LIMIT  10;

Contagens de evento por dia

SELECT Substring(from_utc_timestamp(timestamp, 'America/New_York'), 1, 10) AS Day, 
       Substring(from_utc_timestamp(timestamp, 'America/New_York'), 12, 2) AS Hour, 
       Sum(_experience.analytics.event1to100.{TARGET_EVENT}.value) AS Event_Count
FROM   {TARGET_TABLE}
WHERE  _experience.analytics.event1to100.{TARGET_EVENT}.value IS NOT NULL 
        AND TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
GROUP BY Day, Hour
ORDER BY Hour;

Variáveis de merchandising (sintaxe do produto)

Sintaxe do produto

No Adobe Analytics, os dados personalizados a nível de produto podem ser coletados por meio de variáveis configuradas especialmente chamadas variáveis de merchandising. Elas se baseiam em um eVar ou em eventos personalizados. A diferença entre essas variáveis e seu uso padrão é que elas representam um valor separado para cada produto encontrado na ocorrência, em vez de apenas um valor único para a ocorrência.

Essas variáveis são chamadas de variáveis de merchandising de sintaxe de produto. Isso permite a coleta de informações, como um "valor de desconto" por produto ou informações sobre o "local na página" do produto nos resultados de pesquisa do cliente.

Para saber mais sobre o uso da sintaxe do produto, leia a documentação do Adobe Analytics em implementação de eVars usando a sintaxe do produto.

As seções abaixo destacam os campos XDM necessários para acessar as variáveis de comercialização em seu conjunto de dados Analytics:

eVars

productListItems[#]._experience.analytics.customDimensions.evars.evar#
  • #: O índice da matriz que você está acessando.
  • evar#: A variável de eVar específica que você está acessando.

Eventos personalizados

productListItems[#]._experience.analytics.event1to100.event#.value
  • #: O índice da matriz que você está acessando.
  • event#: A variável de evento personalizada específica que você está acessando.

Exemplos de consultas

Esta é uma consulta de amostra que retorna um eVar de merchandising e um evento para o primeiro produto encontrado no productListItems.

SELECT
  productListItems[0]._experience.analytics.customDimensions.evars.eVar1,
  productListItems[0]._experience.analytics.event1to100.event1.value
FROM adobe_analytics_midvalues
WHERE timestamp = to_timestamp('2019-07-23')
  AND productListItems[0].SKU IS NOT NULL
  AND productListItems[0]._experience.analytics.customDimensions.evars.eVar1 IS NOT NULL
  AND productListItems[0]._experience.analytics.event1to100.event1.value IS NOT NULL
LIMIT 10

Esta próxima consulta explode a matriz productListItems e retorna cada eVar de merchandising e evento por produto. O campo _id é incluído para mostrar a relação com a ocorrência original. O valor _id é uma chave primária exclusiva para o conjunto de dados.

SELECT
  _id,
  productItem._experience.analytics.customDimensions.evars.eVar1,
  productItem._experience.analytics.event1to100.event1.value
FROM (
  SELECT
    _id,
    explode(productListItems) as productItem
  FROM adobe_analytics_midvalues
  WHERE TIMESTAMP = to_timestamp('2019-07-23')
  AND productListItems[0].SKU IS NOT NULL
  AND productListItems[0]._experience.analytics.customDimensions.evars.eVar1 IS NOT NULL
  AND productListItems[0]._experience.analytics.event1to100.event1.value IS NOT NULL
)
LIMIT 20
OBSERVAÇÃO

Se você tentar recuperar um campo que não existe em seu conjunto de dados atual, o erro "Nenhum campo de estrutura semelhante" ocorrerá. Avalie o motivo retornado na mensagem de erro para identificar um campo disponível, atualize sua query e execute novamente.

ERROR: ErrorCode: 08P01 sessionId: XXXX queryId: XXXX Unknown error encountered. Reason: [No such struct field evar1 in eVar10, eVar13, eVar62, eVar88, eVar2;]

Sintaxe de conversão

Outro tipo de variável de merchandising encontrada no Adobe Analytics é a sintaxe de conversão. Com a sintaxe do produto, o valor é coletado ao mesmo tempo que o produto, mas isso requer que os dados estejam presentes na mesma página. Há cenários em que os dados ocorrem em uma página antes da conversão ou do evento de interesse relacionado ao produto. Por exemplo, considere o caso de uso do método de descoberta de produto.

  1. Um usuário realiza uma pesquisa interna por "chapéu de inverno", que define o eVar 6 da Sintaxe de conversão habilitada para Merchandising como "pesquisa interna:chapéu de inverno"
  2. O usuário clica em "waffle beanie" e chega à página de detalhes do produto.
    a. Aterrissar aqui dispara um evento Product View para o "waffle beanie" por $12,99.
    b. Como Product View é configurado como um evento de vinculação, o produto "waffle beanie" agora é vinculado ao valor de eVar6 de "pesquisa interna:chapéu de inverno". Sempre que o produto "waffle beanie" for coletado, ele será associado a "pesquisa interna:chapéu de inverno" até que (1) a configuração de expiração seja alcançada ou (2) um novo valor de eVar6 seja definido e o evento de vinculação ocorra novamente com esse produto.
  3. O usuário adiciona o produto ao carrinho, acionando o evento Cart Add.
  4. O usuário realiza outra pesquisa interna por "camisa de verão", que define o eVar 6 da Sintaxe de conversão habilitada para Merchandising como "pesquisa interna:camisa de verão"
  5. O usuário clica em "t-shirt do esporte" e chega à página de detalhes do produto.
    a. Aterrissar aqui dispara um evento Product View para "camiseta esportiva por $19,99.
    b. O evento Product View ainda é nosso evento compulsório, portanto, agora que o produto "camiseta esportiva" está vinculado ao valor de eVar6 de "pesquisa interna:camisa de verão" e o produto anterior "waffle beanie" ainda está vinculado a um valor de eVar6 de "pesquisa interna:waffle beanie".
  6. O usuário adiciona o produto ao carrinho, acionando o evento Cart Add.
  7. O usuário faz o check-out com ambos os produtos.

Nos relatórios, os pedidos, a receita, as visualizações de produtos e as inclusões no carrinho serão relatáveis em relação ao eVar6 e serão alinhadas à atividade do produto vinculado.

eVar6 (método de descoberta do produto) Receita ordens exibições de produto adições ao carrinho
pesquisa interna:camisa de verão 19,99 1 1 1
pesquisa interna:chapéu de inverno 12,99 1 1 1

Para saber mais sobre o uso da sintaxe de conversão, leia a documentação do Adobe Analytics em implementação de eVars usando a sintaxe de conversão.

Estes são os campos XDM para produzir a sintaxe de conversão em seu conjunto de dados Analytics:

eVars

_experience.analytics.customDimensions.evars.evar#
  • evar#: A variável de eVar específica que você está acessando.

Produto

productListItems[#].sku
  • #: O índice da matriz que você está acessando.

Exemplos de consultas

Aqui está uma consulta de amostra que vincula o valor ao par de produto e evento específico, neste caso o evento de exibição de produto.

SELECT
  endUserIds._experience.aaid.id AS AAID,
  timestamp,
  CASE WHEN commerce.productViews.value = 1 THEN ATTRIBUTION_LAST_TOUCH(timestamp, 'bindConversionSyntaxMerchVariable_eVar1', _experience.analytics.customDimensions.eVars.eVar1)
  OVER(PARTITION BY endUserIds._experience.aaid.id
       ORDER BY timestamp
       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).value
  END AS eVar1Bind,
  EXPLODE(productListItems) AS Product_List,
  commerce.productViews.value AS prodView,
  commerce.purchases.value AS purchase
FROM adobe_analytics_midvalues
WHERE commerce.productViews.value = 1 OR commerce.purchases.value = 1 OR _experience.analytics.customDimensions.eVars.eVar1 IS NOT NULL
LIMIT 100

Aqui está uma consulta de amostra que persiste o valor vinculado às ocorrências subsequentes do respectivo produto. O subquery mais baixo estabelece a relação de valores com o produto no evento de vínculo declarado. O próximo subquery executa a atribuição desse valor vinculado em interações subsequentes com o respectivo produto. E a seleção de nível superior agrega os resultados para produzir o relatório.

SELECT
  Product_List.SKU,
  eVar1101ConversionSyntax,
  SUM(prodView) AS Product_Views,
  SUM(purchase) AS Purchases
FROM
(
  SELECT
    Product_List,
    ATTRIBUTION_LAST_TOUCH(timestamp, 'ConversionSyntax_eVar1', eVar1Bind)
      OVER(PARTITION BY AAID, Product_List.SKU
           ORDER BY timestamp
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).value
    AS eVar1ConversionSyntax,
    prodView,
    purchase
  FROM
  (
    SELECT
      endUserIds._experience.aaid.id AS AAID,
      timestamp,
      CASE WHEN commerce.productViews.value = 1 THEN ATTRIBUTION_LAST_TOUCH(timestamp, 'bindConversionSyntaxMerchVariable_eVar1', _experience.analytics.customDimensions.eVars.eVar1)
      OVER(PARTITION BY endUserIds._experience.aaid.id
           ORDER BY timestamp
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).value
      END AS eVar1Bind,
      EXPLODE(productListItems) AS Product_List,
      commerce.productViews.value AS prodView,
      commerce.purchases.value AS purchase
    FROM adobe_analytics_midvalues
    WHERE commerce.productViews.value = 1 OR commerce.purchases.value = 1 OR _experience.analytics.customDimensions.eVars.eVar1 IS NOT NULL
  )
)
WHERE eVar1ConversionSyntax IS NOT NULL
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 100

Nesta página