Caso do banco Raiffeisen: Como combater fraudes em redes CPA

Publicados: 2022-05-25

À medida que o marketing digital está evoluindo, o mesmo acontece com a fraude de anúncios de afiliados digitais que está se tornando um desafio fascinante para os profissionais de marketing online. É realizado enviando tráfego indesejado para sites ou usando bots para acionar recompensas de afiliados. Não importa o método exato, ainda custa US$ 1 de cada US$ 3 que é feito por meio de publicidade online. Em outras palavras, é uma quantidade significativa de receita roubada sendo comprometida todos os dias.

Neste caso, descrevemos a solução fornecida pela equipe OWOX BI para o banco Raiffeisen que será útil para ambos os bancos e qualquer outro usuário da rede CPA.

Índice

  • Desafio
  • Solução
    • Etapa 1. Colete dados brutos
    • Etapa 2. Processe os dados
    • Etapa 3. Crie relatórios
  • Resultados

Desafio

Os especialistas em marketing da Raiffeisen descobriram um aumento dramático nos custos de tráfego de afiliados, com a receita permanecendo a mesma. Outro problema era que os clientes do banco tinham curtos intervalos de sessão ao inserir dados para o formulário de inscrição no site.

É por isso que Raiffeisen assumiu que alguns de seus afiliados CPA poderiam ter substituído o valor da fonte de tráfego na página de checkout do banco. Funciona assim: por exemplo, um usuário instala uma extensão do navegador para obter descontos. Quando o usuário acessa seu site e abre o checkout, a extensão exibe uma janela pop-up com uma oferta de desconto. Se houver algum clique no link da janela pop-up, a extensão reescreverá automaticamente os dados de origem de tráfego no cookie com os dados de origem de tráfego dos afiliados.

Solução

A equipe de marketing decidiu começar coletando dados brutos de comportamento do usuário para provar a hipótese sobre os afiliados reescreverem os dados da fonte de tráfego em seu benefício. Em seguida, foram coletados os dados sobre os clientes específicos. Esses clientes tiveram duas sessões na mesma página em menos de 60 segundos, e a origem do tráfego da segunda sessão foi alterada para o afiliado. Esses dados sobre esses clientes ajudariam a identificar os parceiros afiliados que atribuíram o tráfego de outros canais a si mesmos. Com esses dados fornecidos, pode ser possível interromper a cooperação com webmasters que agem de má fé e otimizar o orçamento para marketing.

A equipe OWOX BI ajudou a coletar e processar os dados necessários. Aqui está um esquema para demonstrar o fluxo de dados:

Etapa 1. Colete dados brutos

O Google Analytics amostra os dados quando o número de sessões excede o limite de amostragem. É por isso que os analistas de BI da OWOX sugeriram coletar os dados no Google BigQuery. Além disso, este data warehouse na nuvem atende aos mais altos padrões de segurança, o que é muito importante para o banco.

Para configurar a importação de dados do site para o Google BigQuery, Raiffeisen usou o OWOX BI Pipeline. Dessa forma, os especialistas da empresa obtiveram dados não amostrados em tempo quase real e coletaram o registro de data e hora de cada ocorrência. Tal solução permitiu rastrear todas as possíveis sequências de ações do usuário entre sessões, em um único relatório. Por exemplo, você precisa de um relatório sobre os usuários que visitaram a página promocional da sua empresa, retornaram ao site via CPC e, finalmente, compraram algo. Mais uma coisa necessária é exibir essas visitas para a data selecionada no relatório. Aqui está a consulta para obter o relatório que você precisa:

    SELECT cp.promo.date AS Date, cp.promo.clientId AS ClientId, cp.promo.time AS Promo_time, cp.cpc.time AS CPC_time, send.time AS SEND_time FROM ( SELECT promo.date, promo.clientId, promo.time, cpc.time FROM ( SELECT date, clientId, MIN(time) AS time FROM TABLE_DATE_RANGE({dataSetName}.{tableName},TIMESTAMP('{startDate}'), TIMESTAMP('{endDate}')) WHERE page.pagePath CONTAINS '/promo/' AND type = 'pageview' AND traffic.medium != 'cpc' GROUP BY date, clientId, ORDER BY clientId ASC) AS promo LEFT JOIN ( SELECT date, clientId, traffic.medium, time FROM TABLE_DATE_RANGE({dataSetName}.{tableName},TIMESTAMP('{startDate}'), TIMESTAMP('{endDate}')) WHERE traffic.medium = 'cpc' AND type = 'pageview' GROUP BY date, clientId, traffic.medium, time ORDER BY clientId ASC) AS cpc ON promo.clientId=cpc.clientId WHERE promo.time < cpc.time) AS cp LEFT JOIN ( SELECT date, clientId, time FROM TABLE_DATE_RANGE({dataSetName}.{tableName},TIMESTAMP('{startDate}'), TIMESTAMP('{endDate}')) WHERE eventInfo.eventCategory = 'send_ok' AND type = 'event' GROUP BY date, clientId, time ORDER BY clientId ASC) AS send ON cp.promo.clientId = send.clientId WHERE cp.cpc.time < send.time
SELECT cp.promo.date AS Date, cp.promo.clientId AS ClientId, cp.promo.time AS Promo_time, cp.cpc.time AS CPC_time, send.time AS SEND_time FROM ( SELECT promo.date, promo.clientId, promo.time, cpc.time FROM ( SELECT date, clientId, MIN(time) AS time FROM TABLE_DATE_RANGE({dataSetName}.{tableName},TIMESTAMP('{startDate}'), TIMESTAMP('{endDate}')) WHERE page.pagePath CONTAINS '/promo/' AND type = 'pageview' AND traffic.medium != 'cpc' GROUP BY date, clientId, ORDER BY clientId ASC) AS promo LEFT JOIN ( SELECT date, clientId, traffic.medium, time FROM TABLE_DATE_RANGE({dataSetName}.{tableName},TIMESTAMP('{startDate}'), TIMESTAMP('{endDate}')) WHERE traffic.medium = 'cpc' AND type = 'pageview' GROUP BY date, clientId, traffic.medium, time ORDER BY clientId ASC) AS cpc ON promo.clientId=cpc.clientId WHERE promo.time < cpc.time) AS cp LEFT JOIN ( SELECT date, clientId, time FROM TABLE_DATE_RANGE({dataSetName}.{tableName},TIMESTAMP('{startDate}'), TIMESTAMP('{endDate}')) WHERE eventInfo.eventCategory = 'send_ok' AND type = 'event' GROUP BY date, clientId, time ORDER BY clientId ASC) AS send ON cp.promo.clientId = send.clientId WHERE cp.cpc.time < send.time

Abaixo segue a tabela com todos os dados obtidos como resultado da consulta.

Etapa 2. Processe os dados

A lista dos valores necessários foi criada para identificar os valores de origem de tráfego que foram alterados com o afiliado:

  • ID do usuário.
  • Fonte e meio da primeira e das próximas sessões.
  • O tempo entre as sessões.
  • O primeiro e o URL final de cada sessão.
  • Eventos em cada sessão.
  • Evento de transação na sessão final.

Em seguida, para garantir que os valores da fonte de tráfego fossem definitivamente reescritos, os analistas de BI da OWOX optaram por filtrar os dados considerando estas condições:

  • O período de tempo entre as duas sessões deve ser de até 60 segundos.
  • A página do site deve permanecer a mesma quando a fonte for alterada.
  • Deve haver uma transação na sessão final.
  • O meio de tráfego na sessão final deve ser um afiliado.

Aqui está a consulta SQL que foi usada para obter os dados necessários:

    SELECT * FROM ( SELECT traff.clientId clientId, traff.page.pagePath pagePath, traff.traffic.source startSource, traff.traffic.medium startMedium, traff.time startTime, aff.evCategory eventCategory, aff.evlabel eventLabel, aff.evSource finishSource, aff.evMedium fifnishMedium, aff.time finishTime, aff.isTransaction isTransaction, aff.pagePath link, traff.time - aff.time AS diff FROM ( SELECT clientId, page.pagePath, traffic.source, traffic.medium, date, INTEGER(time) time FROM TABLE_DATE_RANGE({dataSetName}.{tableName},TIMESTAMP('{startDate}'), TIMESTAMP('{endDate}')) WHERE traffic.medium != 'affiliate')AS traff JOIN ( SELECT total.date date, total.time time, total.clientId clientId, total.eventInfo.eventCategory evCategory, total.eventInfo.eventLabel evlabel, total.traffic.source evSource, total.traffic.medium evMedium, tr.eventInfo.eventCategory isTransaction, total.page.pagePath pagePath FROM ( SELECT clientId, page.pagePath, eventInfo.eventCategory, eventInfo.eventLabel, traffic.source, traffic.medium, date, INTEGER(time) time FROM TABLE_DATE_RANGE({dataSetName}.{tableName},TIMESTAMP('{startDate}'), TIMESTAMP('{endDate}')) WHERE traffic.medium = 'affiliate') AS total LEFT JOIN ( SELECT clientId, date, eventInfo.eventCategory, INTEGER(time) time FROM TABLE_DATE_RANGE({dataSetName}.{tableName},TIMESTAMP('{startDate}'), TIMESTAMP('{endDate}')) WHERE eventInfo.eventCategory = 'send_ok' GROUP BY 1, 2, 3, 4) AS tr ON total.clientId = tr.clientId AND total.date = tr.date WHERE tr.eventInfo.eventCategory = 'send_ok' AND tr.time>total.time)AS aff ON traff.clientId = aff.clientId) WHERE diff >-60 AND diff<0 GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13 ORDER BY clientId, finishTime
SELECT * FROM ( SELECT traff.clientId clientId, traff.page.pagePath pagePath, traff.traffic.source startSource, traff.traffic.medium startMedium, traff.time startTime, aff.evCategory eventCategory, aff.evlabel eventLabel, aff.evSource finishSource, aff.evMedium fifnishMedium, aff.time finishTime, aff.isTransaction isTransaction, aff.pagePath link, traff.time - aff.time AS diff FROM ( SELECT clientId, page.pagePath, traffic.source, traffic.medium, date, INTEGER(time) time FROM TABLE_DATE_RANGE({dataSetName}.{tableName},TIMESTAMP('{startDate}'), TIMESTAMP('{endDate}')) WHERE traffic.medium != 'affiliate')AS traff JOIN ( SELECT total.date date, total.time time, total.clientId clientId, total.eventInfo.eventCategory evCategory, total.eventInfo.eventLabel evlabel, total.traffic.source evSource, total.traffic.medium evMedium, tr.eventInfo.eventCategory isTransaction, total.page.pagePath pagePath FROM ( SELECT clientId, page.pagePath, eventInfo.eventCategory, eventInfo.eventLabel, traffic.source, traffic.medium, date, INTEGER(time) time FROM TABLE_DATE_RANGE({dataSetName}.{tableName},TIMESTAMP('{startDate}'), TIMESTAMP('{endDate}')) WHERE traffic.medium = 'affiliate') AS total LEFT JOIN ( SELECT clientId, date, eventInfo.eventCategory, INTEGER(time) time FROM TABLE_DATE_RANGE({dataSetName}.{tableName},TIMESTAMP('{startDate}'), TIMESTAMP('{endDate}')) WHERE eventInfo.eventCategory = 'send_ok' GROUP BY 1, 2, 3, 4) AS tr ON total.clientId = tr.clientId AND total.date = tr.date WHERE tr.eventInfo.eventCategory = 'send_ok' AND tr.time>total.time)AS aff ON traff.clientId = aff.clientId) WHERE diff >-60 AND diff<0 GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13 ORDER BY clientId, finishTime

Etapa 3. Crie relatórios

Para uma análise mais aprofundada, foi aplicado o complemento para importar os dados selecionados do Google BigQuery para o Planilhas Google. Utilizando os dados importados, a tabela foi criada com os IDs de cada cliente cuja sessão foi encerrada e a nova sessão foi aberta na mesma página, em menos de um minuto entre as duas sessões.

Em seguida, os dados foram reunidos em uma tabela dinâmica para demonstrar que os afiliados agiram de má fé. Os números na captura de tela abaixo foram alterados e são dados como exemplo:

Por exemplo, o relatório demonstra o número de transações com um valor de origem reescrito, bem como quais afiliados substituíram as origens de tráfego por suas próprias. O relatório também mostra quais canais foram roubados de transações: CPC e orgânico.

Resultados

A equipe de BI da OWOX ajudou a identificar e eliminar rapidamente os pontos fracos nas redes de CPA do banco. Graças à solução fornecida, o banco pôde monitorar as estatísticas dos afiliados (atribuir com mais precisão as conversões e vendas aos canais de tráfego) e trazer à tona os casos de fraude nas redes CPA. A equipe de marketing conseguiu otimizar o orçamento do anúncio ao cessar a cooperação com dois parceiros desonestos que reescreveram as fontes de tráfego e faturaram excessivamente o banco Raiffeisen.