Raiffeisen bank case: How to tackle fraud in CPA networks

Vlada Malysheva, Creative Writer @ OWOX

Jun 4, 2018

As digital marketing is evolving, so does the digital affiliate ad fraud that is becoming a riveting challenge for online marketers. It’s performed by sending junk traffic to websites or using bots to trigger affiliate rewards. No matter the exact method, it still takes $1 out of every $3 that is made through online advertising. In other words, it’s a significant amount of stolen revenue being committed every day.

In this case, we describe the solution provided by the OWOX BI team for Raiffeisen bank that will come in handy for both banks and any other CPA network users.

Challenge

Raiffeisen’s marketing specialists discovered a dramatic increase in the affiliate traffic costs, with the revenue remaining the same. Another problem was that customers of the bank had short session breaks when entering data for the application form on the website.

That’s why Raiffeisen assumed that some of their CPA affiliates could have substituted the traffic source value on the bank’s checkout page. It works like this: for example, a user installs a browser extension to get discounts. When the user goes to your website and opens the checkout, the extension displays a pop-up window with a discount offer. If there are any clicks on the link from the pop-up window, the extension automatically rewrites the traffic source data in the cookie with the affiliates’ traffic source data.

Solution

The marketing team decided to start with collecting raw user behavior data to prove the hypothesis about the affiliates rewriting the traffic source data to their benefit. Next, the data about the specific customers was collected. These customers had two sessions on the same page within less than 60 seconds, and the traffic source of the second session was switched to the affiliate one. Such data about these customers would help identify the affiliate partners who attributed the traffic from other channels to themselves. With this data provided, it could be possible to stop the cooperation with webmasters acting in bad faith, and optimize the budget for marketing.

The OWOX BI team helped to collect and process the necessary data. Here’s a schema to demonstrate the data flow:

Step 1. Collect raw data

Google Analytics samples the data when the number of sessions exceeds the sampling threshold. That’s why OWOX BI analysts suggested collecting the data in Google BigQuery. Moreover, this cloud data warehouse meets the highest security standards, which is really important for the bank.

To set up the data import from the website to Google BigQuery, Raiffeisen used OWOX BI Pipeline. This way the company’s specialists got unsampled data in near-real time and collected the timestamp of every hit. Such a solution allowed to track all possible sequences of user actions across sessions, within a single report. For example, you need a report on the users who visited your company’s/promo page, got back to the website via CPC, and, finally, purchased something. One more thing necessary is to display these visits for the selected date in the report. Here’s the query to obtain the report you need:

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

Below is the table with all the data obtained as a result of the query.

Step 2. Process the data

The list of the necessary values was created to identify the traffic source values that were changed with the affiliate one:

User ID.
Source and medium of the first and the next sessions.
The time between sessions.
The first and the final URLs of each session.
Events in each session.
Transaction event in the final session.

Next, to make sure the traffic source values were definitely rewritten, the OWOX BI analysts chose to filter the data considering these conditions:

The time period between the two sessions should be up to 60 seconds.
The website page should remain the same when the source changes.
There should be a transaction in the final session.
The traffic medium in the final session should be an affiliate.

Here’s the SQL query that was used to obtain the necessary data:

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

Step 3. Build reports

For further analysis, the add-on to import the selected data from Google BigQuery to Google Sheets was applied. By using the imported data, the table was created with the IDs of each customer whose session was closed and the new session was opened on the very same page, within less than a minute between the two sessions.

Next, the data was brought together in a pivot table to demonstrate that affiliate marketers acted in bad faith. The numbers in the screenshot below were changed and are given as an example:

For example, the report demonstrates the number of transactions with a rewritten source value, as well as which affiliates have replaced the traffic sources with their own ones. The report also shows which channels were robbed of transactions: CPC and organic.

Results

The OWOX BI team helped quickly identify and eliminate weaknesses in the bank’s CPA networks. Thanks to the provided solution the bank could monitor statistics on affiliates (more accurately attribute conversions and sales to traffic channels) and bring to light the cases of fraud in CPA networks. The marketing team managed to optimize the ad budget by ceasing cooperation with two dishonest partners that rewrote the traffic sources and unreasonably overbilled Raiffeisen bank.