03. Pivotando os dados das tabelas Actions do Facebook

O Facebook define ações como atividades que ocorrem no anúncio ou como consequência dele. As ações no anúncio podem ser cliques em links ou visualizações de vídeo, por exemplo, enquanto as ações de consequência do anúncio podem envolver compras no site, cadastros ou conversões. Essas ações são atribuídas ao anúncio quando alguém viu ou clicou nele dentro de um certo período de tempo, que pode ser definido pela janela de atribuição.

Neste tutorial iremos mostrar:

A estrutura das tabelas de actions
Campos para filtrar ações e seus valores
Como extrair actions usando modelos da Kondado
Exemplo de query para extrair actions no BigQuery
Exemplo de query para extrair actions no PostgreSQL

Estrutura das tabelas de actions

Através das integrações de Estatísticas de Campanhas, Grupos de Anúncios e Anúncios da plataforma Kondado é possível obter os dados de ações, que estarão em tabelas separadas à tabela principal de cada integração.

Actions: A tabela de actions irá representar os dados em formato de contagem (por exemplo, número de compras realizadas no site);
Action_values: Representará os dados de valor (por exemplo, valor das compras realizadas no site).

As duas tabelas acima terão as mesmas dimensões da tabela principal da integração, além de campos que identificam as ações (tipo da ação) e as quantificam (valor ou contagem de determinada ação). A estrutura das tabelas de actions será parecida com esta (o exemplo abaixo refere-se a tabela de actions de uma integração de Estatísticas de Campanhas com detalhamento de região e país):

Ao contrário das tabelas principais das integrações de estatísticas (onde as métricas são organizadas por colunas), nas tabelas de actions as ações e seus valores estarão em linhas:

Atenção: Não confundir o campo “actions_value” com as tabelas de “Action Values” – o campo irá trazer o resultado (em contagem ou valor) da respectiva ação, enquanto a tabela irá trazer outros tipos de ações e também terá uma coluna de “actions_value” dentro dela.

Campos para filtrar ações e seus valores

Para pivotar os dados de ações e extrair suas métricas, é importante se atentar para as funções das colunas abaixo:

actions_action_type ou action_values_action_type: através desse campo será possível filtrar o tipo da ação que está sendo medida para obter o seu resultado no período e dimensões da integração. Por exemplo, quando o campo actions_action_type for igual a ‘comment’, iremos filtrar o número de comentários em um post no determinado período (metric_date), resultantes de determinada campanha, anúncio ou grupo de anúncio e quebrados por detalhamento (quando houver).
actions_value ou action_values_value: Trará o resultado atribuído à ação filtrada nos campos acima. Continuando no mesmo exemplo anterior, esse campo irá trazer o resultado do número de comentários no determinado período (metric_date), resultantes de determinada campanha, anúncio ou grupo de anúncio e quebrados por detalhamento (quando houver).
actions_1_click, actions_1d_view, actions_7d_click…: têm a mesma função dos campos actions_value ou action_values_value, e devem ser usados no lugar deles quando houver alguma configuração de atribuição diferente feita no Facebook.

Os valores filtráveis nos campos actions_action_type ou action_values_action_type podem ser encontrados em nossa documentação do Facebook e podem variar para cada caso (conta de anúncio, campanha, grupo de anúncio, detalhamento, etc…), portanto nem todas as integrações terão todas as actions ou action_values disponíveis.

Como extrair actions usando modelos da Kondado

Para que seja mais fácil visualizar os dados das ações em ferramentas de BI para criação de métricas e análises, recomendamos a pivotagem dos dados delas (que nativamente são organizadas em linhas) em colunas. Esse processo terá um resultado parecido com a operação de transposição do Excel, transformando cada actions_action_type ou action_values_action_type em uma coluna:

Usando a funcionalidade de modelos da Kondado é possível deixar esse processo automático, para que sempre que suas integrações de estatísticas do Facebook terminem de executar, uma nova tabela seja atualizada no seu banco com os dados das ações já pivotados. Essa funcionalidade é completamente gratuita.

Antes de seguir para os próximos tópicos do tutorial, siga o tutorial de criação de modelos na Kondado, clicando aqui, ou se preferir assista o vídeo tutorial clicando aqui.

No passo 5 do tutorial escrito você chegará no momento de escrever a sua query em SQL, que será o comando utilizado para fazer a transformação dos dados. Nos próximos tópicos nós montamos 2 modelos de como escrever essa query em destinos do tipo PostgreSQL e do tipo BigQuery.

Mesmo que o seu destino não seja de um desses tipos, a sintaxe de bancos relacionais costuma ser bem parecida, bastando poucas alterações para adaptar sua query.

Exemplo de query para extrair actions no BigQuery

Caso não queira entender o passo a passo que como a query foi criada, pule diretamente para a query final aqui.

Filtrando uma ação

Para começar, criamos um SELECT filtrando apenas a ação “comment” e seus valores por dia.

Na query abaixo estamos selecionando a data (metric_date), o nome da campanha (campaign_name), o tipo da ação (actions_action_type) e o número de comentários que estará no campo actions_value. Caso sua integração tenha outras dimensões como detalhamentos, grupos de anúncios ou anúncios, inclua elas no SELECT:

SELECT
  metric_date AS data,
  campaign_name AS campanha,
  actions_action_type AS tipo_acao,
  actions_value AS comentarios
FROM `nome_do_dataset.nome_tabela_facebook_campaign_insights_actions` 
WHERE actions_action_type =  "comment"

A query acima retornará um resultado parecido com o abaixo no seu destino BigQuery:

Pivotando várias ações em colunas

Para pivotar várias actions em colunas, criamos uma estrutura de SELECT em que cada action_type é uma subquery (que é uma query que está contida dentro de outro SELECT).

Cada subquery terá uma estrutura parecida com essa:

SELECT
SUM(actions_value) 
FROM `nome_do_dataset.nome_tabela_facebook_campaign_insights_actions` AS sum_comments 
WHERE sum_comments.actions_action_type = "comment" 
AND sum_comments.metric_date = actions.metric_date 
AND sum_comments.campaign_name = actions.campaign_name

Dentro do SELECT irá apenas a soma do campo de valor da action, enquanto na cláusula WHERE fazemos os filtros necessários para que os valores da soma sejam respectivos à data da métrica e a campanha. Caso sua tabela tenha mais dimensões, para que os resultados sejam corretos, é necessário incluir todas elas dentro da cláusula WHERE de cada subquery.

A query final para a pivotagem de várias actions em colunas irá trazer dados distintos de metric_date, campaign_name (e/ou outras dimensões que a sua tabela possua) dentro do SELECT, junto com as várias subqueries – não esqueça de substituir os valores abaixo pelas actions que deseja buscar e pelo nome do seu dataset e tabela dentro do BigQuery:

SELECT DISTINCT
  actions.metric_date AS data,
  actions.campaign_name AS campanha,
  (SELECT SUM(actions_value) FROM `nome_do_dataset.nome_tabela_facebook_campaign_insights_actions` AS sum_comments WHERE sum_comments.actions_action_type = "comment" AND sum_comments.metric_date = actions.metric_date AND sum_comments.campaign_name = actions.campaign_name ) AS comentarios,
  (SELECT SUM(actions_value) FROM `nome_do_dataset.nome_tabela_facebook_campaign_insights_actions` AS sum_views WHERE sum_views.actions_action_type = "video_view" AND sum_views.metric_date = actions.metric_date AND sum_views.campaign_name = actions.campaign_name ) AS visualizacoes_video,
  (SELECT SUM(actions_value) FROM `nome_do_dataset.nome_tabela_facebook_campaign_insights_actions` AS sum_engagement WHERE sum_engagement.actions_action_type = "post_engagement" AND sum_engagement.metric_date = actions.metric_date AND sum_engagement.campaign_name = actions.campaign_name ) AS engajamento
FROM `nome_do_dataset.nome_tabela_facebook_campaign_insights_actions`AS actions

Essa query terá um resultado parecido com o mostrado abaixo – note que todas as ações filtradas já estão no formato colunar, com seus respectivos resultados para cada metric_date e campaign_name:

Juntando as várias ações pivotadas com a tabela principal de Estatísticas

Essa query irá trazer dados da tabela principal de Estatísticas (de campanha, no nosso exemplo) já com os tipos de actions pivotados em colunas e seus respectivos resultados para cada ocorrência:

SELECT DISTINCT
    insights.metric_date AS Data,
    insights.frequency AS Frequencia,
    insights.impressions AS Impressoes,
    insights.campaign_name AS Nome_da_Campanha,
    insights.objective AS Objetivo,
    insights.clicks AS Cliques,
    insights.reach AS Alcance,
    insights.spend AS Investimento,
    (SELECT SUM(actions.actions_value) FROM `nome_do_dataset.nome_tabela_facebook_campaign_insights_actions` AS actions WHERE insights.account_id = actions.account_id AND insights.metric_date = actions.metric_date AND insights.campaign_id = actions.campaign_id AND actions.actions_action_type = "post_engagement") AS Engajamento,
    (SELECT SUM(actions.actions_value) FROM `nome_do_dataset.nome_tabela_facebook_campaign_insights_actions` AS actions WHERE insights.account_id = actions.account_id AND insights.metric_date = actions.metric_date AND insights.campaign_id = actions.campaign_id AND actions.actions_action_type = "video_view") AS video_view,
    (SELECT SUM(actions.actions_value) FROM `nome_do_dataset.nome_tabela_facebook_campaign_insights_actions` AS actions WHERE insights.account_id = actions.account_id AND insights.metric_date = actions.metric_date AND insights.campaign_id = actions.campaign_id AND actions.actions_action_type = "post_reaction") AS Reacao_post,
    (SELECT SUM(actions.actions_value) FROM `nome_do_dataset.nome_tabela_facebook_campaign_insights_actions` AS actions WHERE insights.account_id = actions.account_id AND insights.metric_date = actions.metric_date AND insights.campaign_id = actions.campaign_id AND actions.actions_action_type = "comment") AS comentarios
FROM `nome_do_dataset.nome_tabela_facebook_campaign_insights` AS insights;

Não esqueça de se atentar e adaptar os seguintes pontos:

`nome_do_dataset.nome_tabela_facebook_campaign_insights_actions` – nome do seu dataset e nome da sua tabela de actions no seu destino BigQuery;
`nome_do_dataset.nome_tabela_facebook_campaign_insights` – nome do seu dataset e nome da sua tabela de Estatísticas (tabela principal) no seu destino BigQuery;
Substituir o campo actions.actions_action_type = “XXXX” sendo “XXXX” o tipo da action que deseja filtrar;
Você poderá incluir outras actions apenas copiando e colando as subqueries (lembre-se de separá-las por vírgula e mudar o alias da coluna final);
Se a sua tabela de estatísticas tiver mais dimensões (como grupo de anúncios, anúncio e/ou outros detalhamentos) não esqueça de incluí-las dentro da cláusula WHERE de cada subquery. Exemplo: WHERE insights.account_id = actions.account_id AND insights.metric_date = actions.metric_date AND insights.campaign_id = actions.campaign_id AND actions.actions_action_type = “comment” AND insights.ad_group_id = actions.ad_group_id AND insights.country = actions.country (aqui incluímos a dimensão “ad_group” e o detalhamento “country”). Além disso, não esqueça de incluir essas novas dimensões no SELECT inicial.

Com a query adaptada e pronta, basta colá-la no editor de query do seu modelo e continuar a criação dele do passo 6 em diante. Após a criação e execução do modelo, você terá uma nova tabela no seu destino já pivotada e juntando os principais dados dos seus anúncios 🙂

Exemplo de query para extrair actions no PostgreSQL

Caso queira entender o passo a passo de como a query foi criada, volte ao tópico anterior clicando aqui. Neste tópico iremos apenas adaptar a sintaxe da query para o destino PostgreSQL.

A query abaixo irá trazer dados da tabela principal de Estatísticas (de campanha, no nosso exemplo) já com os tipos de actions pivotados em colunas e seus respectivos resultados para cada ocorrência:

SELECT DISTINCT
    insights.metric_date AS Data,
    insights.frequency AS Frequencia,
    insights.impressions AS Impressoes,
    insights.campaign_name AS Nome_da_Campanha,
    insights.objective AS Objetivo,
    insights.clicks AS Cliques,
    insights.reach AS Alcance,
    insights.spend AS Investimento,
    (SELECT SUM(actions.actions_value) FROM nome_tabela_facebook_campaign_insights_actions AS actions WHERE insights.account_id = actions.account_id AND insights.metric_date = actions.metric_date AND insights.campaign_id = actions.campaign_id AND actions.actions_action_type = 'post_engagement') AS Engajamento,
    (SELECT SUM(actions.actions_value) FROM nome_tabela_facebook_campaign_insights_actions AS actions WHERE insights.account_id = actions.account_id AND insights.metric_date = actions.metric_date AND insights.campaign_id = actions.campaign_id AND actions.actions_action_type = 'video_view') AS video_view,
    (SELECT SUM(actions.actions_value) FROM nome_tabela_facebook_campaign_insights_actions AS actions WHERE insights.account_id = actions.account_id AND insights.metric_date = actions.metric_date AND insights.campaign_id = actions.campaign_id AND actions.actions_action_type = 'post_reaction') AS Reacao_post,
    (SELECT SUM(actions.actions_value) FROM nome_tabela_facebook_campaign_insights_actions AS actions WHERE insights.account_id = actions.account_id AND insights.metric_date = actions.metric_date AND insights.campaign_id = actions.campaign_id AND actions.actions_action_type = 'comment') AS comentarios
FROM nome_tabela_facebook_campaign_insights AS insights

Não esqueça de se atentar e adaptar os seguintes pontos:

nome_tabela_facebook_campaign_insights_actions – nome da sua tabela de actions no seu destino PostgreSQL;
nome_tabela_facebook_campaign_insights – nome da sua tabela de Estatísticas (tabela principal) no seu destino PostgreSQL;
Substituir o campo actions.actions_action_type = “XXXX” sendo “XXXX” o tipo da action que deseja filtrar;
Você poderá incluir outras actions apenas copiando e colando as subqueries (lembre-se de separá-las por vírgula e mudar o alias da coluna final);
Se a sua tabela de estatísticas tiver mais dimensões (como grupo de anúncios, anúncio e/ou outros detalhamentos) não esqueça de incluí-las dentro da cláusula WHERE de cada subquery. Exemplo: WHERE insights.account_id = actions.account_id AND insights.metric_date = actions.metric_date AND insights.campaign_id = actions.campaign_id AND actions.actions_action_type = “comment” AND insights.ad_group_id = actions.ad_group_id AND insights.country = actions.country (aqui incluímos a dimensão “ad_group” e o detalhamento “country”). Além disso, não esqueça de incluir essas novas dimensões no SELECT inicial.