Raiffeisen Tackles Fraud in CPA Networks

6
748

Our cooperation with Raiffeisen Bank has existed for a little longer than 4 months, but we’ve already dealt with a riveting challenge. The solution will come in handy for banks and any other CPA network users.

About Raiffeisen

Raiffeisen Bank in Russia, an affiliate of Raiffeisen Bank International, is a commercial bank for private and corporate customers that started operating in 1996. Today there are more than 180 Raiffeisen departments and 7,900 employees in Russia. Moreover, Raiffeisen customers can use the following remote bank services: cash machines, self-service terminals, online banking, a mobile app and SMS banking.

Challenge

A couple months ago Raiffeisen’s marketing specialists discovered a dramatic increase of 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 Raiffeisen’s 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. This is how it works: 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

To prove the hypothesis about the affiliates rewriting the traffic source data to their benefit, Raiffeisen’s marketing specialists decided to start with collecting raw user behavior data. Next, they collected the data about the specific customers. 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 information provided, Raiffeisen would stop the cooperation with webmasters acting in bad faith, and optimize the budget for marketing.

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

data flow diagram

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 solution engaged Raiffeisen to track all possible sequences of user actions across sessions, within a single report.

For instance, you would like 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 a selected date in the report. Below is 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 abovementioned query.

table with data in BigQuery

Step 2. Process the data

To identify the traffic source values that were changed with the affiliate one, the OWOX BI and Raiffeisen analysts created a list of the necessary values:

  • 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 affiliate.

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

                        
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 OWOX BI analysts used the add-on to import the selected data from Google BigQuery to Google Sheets. Thanks to the import, Raiffeisen received a table 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.

data imported to Google Sheets

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

example of report on webmasters from CPA network by traffic channels

For instance, the report above 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

Raiffeisen got a report that helps monitor statistics on affiliates and bring to light the cases of fraud in CPA networks. The company managed to optimize the ad budget by ceasing cooperation with two dishonest partners that rewrote the traffic sources and unreasonably overbilled Raiffeisen.

‘‘

We really appreciate the prompt response of the OWOX BI team. They helped us quickly identify and eliminate weaknesses in our CPA networks. It saved us a lot of money and helped us more accurately attribute conversions and sales to traffic channels. We also understand that such monitoring activity should become a regular thing. That’s why we’re working on the automation of monitoring and fighting fraud in CPA channels. Our main goal is not about reducing affiliate costs, it’s about learning to fight fraud, as well as letting our honest and effective partners earn money.

Dmitriy Berezin,
Head of Online Sales at Raiffeisen Bank
Dmitry Kudryavsky

Make sure that you’re working with honest and reliable CPA partners only, with the CPA reports in Smart Data.

TRY SMART DATA

You might also like