Combinando Google Analytics e Shopify: criando um data warehouse de comércio eletrônico no BigQuery

O Google Analytics é uma ótima ferramenta que permite entender o comportamento do usuário de quem visita seu site. Visto que Shopify é fantástico em fornecer uma cobertura detalhada do desempenho de uma perspectiva de comércio eletrônico. Ambos são ótimos no que fazem, mas chega um ponto em que os dados da Shopify e do Google Analytics (GA) isoladamente não fornecem os insights mais profundos que você deseja. Embora cada um tenha a capacidade de fornecer informações poderosas, há um nível muito maior de percepção que pode ser obtido pela combinação dos dados.

Neste exemplo específico, abordaremos como conseguimos isso para nossos amigos da Pooch & Mutt, uma empresa com sede no Reino Unido que produz alimentos naturais e saudáveis ​​para cães. Queríamos aprimorar os insights pegando os dados coletados no Google Analytics e usando-os em conjunto com os dados de comércio eletrônico da Shopify, bem como os dados de assinatura do Recharge e unindo-os aos dados de assinatura herdados da Bold. Usando essa variedade de fontes de dados, poderíamos construir uma imagem mais rica e completa.

Este blog buscará fornecer uma visão geral de como agregamos esses dados e, em seguida, delinear os processos de pensamento por trás de combiná-los para extrair e visualizar esses insights orientados por dados. Especificamente, explicaremos como respondemos às três perguntas a seguir;

1. Quais canais geram assinaturas de assinatura?
2. Combinações de cesta além do GA; quais são os cabazes mais populares para subscritores e não subscritores e quais são os mais rentáveis?
3. Quanto tempo e quantas compras, em média, leva para um usuário se tornar um assinante?

Metodologia

Antes de respondermos a essas perguntas, vamos primeiro abordar como coletamos todos os dados de suas respectivas plataformas em um local centralizado e construímos uma tabela que poderíamos usar para agrupar esses dados.

Estrutura de dados

Optamos por usar o BigQuery (BQ) para centralizar os dados devido à sua versatilidade e capacidade de lidar com a combinação de conjuntos de dados. O fluxograma abaixo destaca as conexões gerais entre os dados e a maneira como isso foi alcançado:

Ponto

O Stitch é uma ferramenta acessível que permite extrair e carregar dados em data warehouses pré-construídos.

Conectamo-nos facilmente ao Shopify e ao Recharge através do Stitch, onde simplesmente selecionamos as mesas que queríamos. Nesse caso, as tabelas mais importantes são 'clientes' e 'assinaturas' do Recharge e 'pedidos' do Shopify.

Essas informações são sincronizadas com o BQ a cada 6 horas por meio do processo de agendamento automatizado do Stitch.

Audacioso

Para dados históricos de assinantes, fizemos uma exportação final e carregamos para o BQ por meio do Planilhas Google.

Google Analytics

Embora o Stitch também possa ser usado para isso, optamos por usar o RStudio para obter esses dados no BQ. Se você estiver interessado em saber mais sobre como fazer isso, leia um blog do nosso especialista em R Studio, Danny Smith, sobre como usar a API GA com R.

Construindo uma tabela mestra

Uma vez que tivéssemos todos os dados necessários no BQ, poderíamos começar a combiná-los. Os passos que seguimos para fazer isso são os seguintes:

1. Combinamos e alinhamos os dados de assinantes legados da Bold com os dados de assinantes atuais da Recharge.
2. Em seguida, extraímos os dados de comércio eletrônico do GA, que incluem IDs de pedidos (por exemplo, SPFYXXXXX), juntamente com a fonte, a mídia e o canal.
3. Criamos novas colunas com base nas tabelas de GA e dados de assinantes sindicalizados antes de ingressar nos dados de pedidos da Shopify. Nesse caso, algumas dessas colunas adicionais incluídas;
   1. Identificador de assinante 'ativo'
   2. Data original da assinatura
   3. Fonte, meio e canal de ordem
   4. Primeiro ou repita o pedido do cliente
4. Em seguida, selecionamos os campos relevantes dos dados do pedido da Shopify aos quais queríamos juntar esses novos dados. Alguns exemplos principais são;
   1. Identificação do Cliente
   2. ID do pedido
   3. Data do pedido
   4. SKU
   5. Quantidade do item
   6. Preço do item
   7. Preço total do pedido
   8. Código de desconto
   9. Valor do desconto
   10. Tag (usado como um método adicional para categorizar assinantes versus não assinantes)
5. O estágio final aqui foi criar mais algumas colunas adicionais para lidar mais facilmente com linhas duplicadas para itens diferentes na mesma ordem;
   1. Preço médio do item
   2. desconto médio
   3. Status do assinante (tags + se estiverem presentes na tabela Bold & Recharge)

Em seguida, programamos essa consulta para ser executada uma vez por dia e usamos o resultado como a tabela mestre para consultas adicionais. Isso significava que não precisávamos executar a consulta completa várias vezes, pois é uma tabela estática que só é atualizada quando programada, em vez de toda vez que uma solicitação é feita por meio do painel do Looker Studio.

Percepções

Agora que cobrimos a metodologia e descrevemos as etapas para obter todos os dados de que precisamos, podemos discutir como usamos esses dados para responder às nossas perguntas.

Quais canais geram assinaturas de assinatura?

1. Para calcular isso, analisamos os primeiros pedidos feitos pelos clientes, usando o campo que criamos na tabela mestre que retorna 'Primeiro' ou 'Repetir' com base na data do primeiro pedido.
2. Usando as colunas adicionais de agrupamento de canais e status do assinante na tabela principal, podemos ver os primeiros pedidos de pessoas que se tornaram assinantes, seja na primeira compra ou na compra repetida, e a qual canal eles foram atribuídos no GA .
3. Removendo a etapa de olhar apenas para os assinantes, também pudemos ver quais canais impulsionam predominantemente as primeiras compras.

Combinações de cesta além do GA; quais são as cestas mais populares para assinantes e não assinantes e quais são as mais lucrativas?

1. Usando a função STRING_AGG, conseguimos combinar os diferentes produtos de pedidos únicos em uma única linha, separados por ' | '. Isso basicamente cria uma cesta para cada pedido.
2. Juntamente com esta informação, somamos a receita de cada um destes itens como 'Total da cesta', com o objetivo de ter uma linha por pedido com todas as informações relevantes.
3. É claro que há muita variação entre os produtos (sabor, tamanho, etc.), então adicionamos uma coluna que eliminou essa variação para observar as combinações básicas da cesta de produtos.
4. Em seguida, usamos 'ROW_NUMBER() OVER (PARTITION BY Transaction_ID) AS Identifier' para atribuir valores sequenciais a cada pedido. Isso foi usado como um filtro no painel para isolar linhas únicas que ainda continham todas as informações.

Quanto tempo e quantas compras, em média, leva para um usuário se tornar um assinante?

1. Usando nossa tabela mestre, extraímos todos os IDs de clientes que sabíamos que eram assinantes e pegamos a data da primeira assinatura.
2. Em seguida, pegamos a data mínima do pedido e usamos a função DATE_DIFF para calcular a diferença entre essas datas em 'dias'.
3. A etapa final extraiu o canal e o número médio de dias antes da assinatura, o que resultou em uma tabela mostrando o número médio de dias antes que os clientes normalmente se tornassem assinantes.
4. Para calcular o número médio de pedidos que os clientes fizeram antes de se tornarem assinantes, simplesmente dividimos a contagem de IDs de pedidos exclusivos pela contagem de IDs de clientes exclusivos onde eles eram 'Assinantes' e tinham datas de pedidos anteriores à data mínima de assinatura.

Estes são apenas alguns exemplos superficiais das visualizações que geramos usando esses relatórios. Com os dados, existem infinitas possibilidades – outros exemplos deste projeto incluem observar o valor vitalício do cliente de várias perspectivas e avaliar o uso de códigos de desconto e seu impacto no comportamento vitalício dos clientes. Com esses insights, eles podem ser usados ​​para complementar as decisões de negócios. Por exemplo, sabendo que são necessários em média 3 pedidos antes de se tornar um assinante, você pode compartilhar ofertas e incentivos que incentivem os clientes a se tornarem assinantes após 2 compras. Os resultados disso podem ser medidos posteriormente.

Resumo

Este estudo de caso demonstra o potencial para insights baseados em dados incrivelmente valiosos que podem ser obtidos por meio da combinação de várias fontes de dados, do GA ao Shopify e além. Depois que essa infraestrutura é criada, ela pode ser mantida dinamicamente e usada para relatar vários aspectos diferentes do negócio.

Se as questões que respondemos neste blog forem semelhantes às que lhe interessam para o seu negócio, pode entrar em contacto connosco preenchendo o nosso formulário de contacto.