Sprawa banku Raiffeisen: Jak walczyć z oszustwami w sieciach CPA

Opublikowany: 2022-05-25

Wraz z rozwojem marketingu cyfrowego, podobnie jak oszustwa związane z cyfrowymi reklamami afiliacyjnymi, które stają się fascynującym wyzwaniem dla marketerów internetowych. Odbywa się to poprzez wysyłanie śmieciowego ruchu na strony internetowe lub używanie botów do wyzwalania nagród partnerskich. Bez względu na dokładną metodę, nadal pobiera 1 USD z każdych 3 USD uzyskanych dzięki reklamie online. Innymi słowy, każdego dnia jest to znaczna kwota skradzionych dochodów.

W tym przypadku opisujemy rozwiązanie dostarczone przez zespół OWOX BI dla banku Raiffeisen, które przyda się zarówno bankom, jak i innym użytkownikom sieci CPA.

Spis treści

  • Wyzwanie
  • Rozwiązanie
    • Krok 1. Zbierz surowe dane
    • Krok 2. Przetwarzaj dane
    • Krok 3. Twórz raporty
  • Wyniki

Wyzwanie

Specjaliści ds. marketingu Raiffeisen odkryli dramatyczny wzrost kosztów ruchu afiliacyjnego przy niezmienionych dochodach. Kolejnym problemem było to, że klienci banku mieli krótkie przerwy w sesjach podczas wprowadzania danych do wniosku aplikacyjnego na stronie internetowej.

Dlatego Raiffeisen założył, że niektórzy z ich oddziałów CPA mogli zastąpić wartość źródła ruchu na stronie kasowej banku. Działa to tak: na przykład użytkownik instaluje rozszerzenie przeglądarki, aby uzyskać zniżki. Gdy użytkownik przejdzie do Twojej witryny i otworzy kasę, rozszerzenie wyświetli wyskakujące okienko z ofertą rabatową. W przypadku jakichkolwiek kliknięć w link z wyskakującego okienka, rozszerzenie automatycznie przepisuje dane o źródle ruchu w pliku cookie z danymi o źródle ruchu podmiotów stowarzyszonych.

Rozwiązanie

Zespół marketingowy postanowił rozpocząć od zebrania surowych danych o zachowaniach użytkowników, aby udowodnić hipotezę, że partnerzy przepisują dane o źródle ruchu na swoją korzyść. Następnie zebrano dane o konkretnych klientach. Klienci ci mieli dwie sesje na tej samej stronie w czasie krótszym niż 60 sekund, a źródło ruchu z drugiej sesji zostało przełączone na partnerskie. Takie dane o tych klientach pomogłyby zidentyfikować partnerów afiliacyjnych, którzy przypisali sobie ruch z innych kanałów. Mając te dane, można by przerwać współpracę z webmasterami działającymi w złej wierze i zoptymalizować budżet na marketing.

Zespół OWOX BI pomógł zebrać i przetworzyć niezbędne dane. Oto schemat demonstrujący przepływ danych:

Krok 1. Zbierz surowe dane

Google Analytics próbkuje dane, gdy liczba sesji przekracza próg próbkowania. Dlatego analitycy OWOX BI zaproponowali zbieranie danych w Google BigQuery. Co więcej, ta chmurowa hurtownia danych spełnia najwyższe standardy bezpieczeństwa, co jest bardzo ważne dla banku.

Aby skonfigurować import danych ze strony internetowej do Google BigQuery, Raiffeisen wykorzystał OWOX BI Pipeline. W ten sposób specjaliści firmy otrzymywali niespróbkowane dane w czasie zbliżonym do rzeczywistego i zbierali znacznik czasu każdego trafienia. Takie rozwiązanie pozwoliło na śledzenie wszystkich możliwych sekwencji działań użytkowników w sesjach, w ramach jednego raportu. Na przykład potrzebujesz raportu o użytkownikach, którzy odwiedzili stronę Twojej firmy/promocyjną, wrócili do witryny przez CPC i na koniec coś kupili. Jeszcze jedną niezbędną rzeczą jest wyświetlenie tych wizyt w wybranym terminie w raporcie. Oto zapytanie umożliwiające uzyskanie potrzebnego raportu:

    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

Poniżej tabela ze wszystkimi danymi uzyskanymi w wyniku zapytania.

Krok 2. Przetwarzaj dane

Lista niezbędnych wartości została stworzona w celu zidentyfikowania wartości źródeł ruchu, które zostały zmienione wraz z afiliowanym:

  • Identyfikator użytkownika.
  • Źródło i medium pierwszej i kolejnych sesji.
  • Czas między sesjami.
  • Pierwszy i końcowy adres URL każdej sesji.
  • Wydarzenia w każdej sesji.
  • Zdarzenie transakcyjne w ostatniej sesji.

Następnie, aby upewnić się, że wartości źródeł ruchu zostały na pewno przepisane, analitycy OWOX BI zdecydowali się na filtrowanie danych z uwzględnieniem następujących warunków:

  • Czas między dwiema sesjami powinien wynosić do 60 sekund.
  • Strona internetowa powinna pozostać taka sama po zmianie źródła.
  • W ostatniej sesji powinna nastąpić transakcja.
  • Medium ruchu w ostatniej sesji powinien być partner.

Oto zapytanie SQL, które zostało użyte do uzyskania niezbędnych danych:

    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

Krok 3. Twórz raporty

Do dalszej analizy zastosowano dodatek do importowania wybranych danych z Google BigQuery do Arkuszy Google. Korzystając z zaimportowanych danych, utworzono tabelę z identyfikatorami każdego klienta, którego sesja została zamknięta i nowa sesja została otwarta na tej samej stronie, w czasie krótszym niż minuta między sesjami.

Następnie dane zostały zebrane w tabeli przestawnej, aby wykazać, że marketerzy afiliacyjni działali w złej wierze. Liczby na poniższym zrzucie ekranu zostały zmienione i są podane jako przykład:

Na przykład raport pokazuje liczbę transakcji z przepisaną wartością źródłową, a także, które podmioty stowarzyszone zastąpiły źródła ruchu własnymi. Raport pokazuje również, które kanały zostały okradzione z transakcji: CPC i organiczne.

Wyniki

Zespół OWOX BI pomógł szybko zidentyfikować i wyeliminować słabości w bankowych sieciach CPA. Dzięki dostarczonemu rozwiązaniu bank mógł monitorować statystyki dotyczące podmiotów stowarzyszonych (dokładniej przypisywać konwersje i sprzedaż do kanałów ruchu) oraz ujawniać przypadki nadużyć w sieciach CPA. Zespołowi marketingowemu udało się zoptymalizować budżet reklamowy poprzez zerwanie współpracy z dwoma nieuczciwymi partnerami, którzy przepisali źródła ruchu i bezpodstawnie przeliczyli rachunek Raiffeisen.