Raiffeisen銀行の事例：CPAネットワークでの不正に取り組む方法

公開: 2022-05-25

デジタルマーケティングが進化するにつれて、オンラインマーケターにとって魅力的な課題になりつつあるデジタルアフィリエイト広告詐欺も進化しています。 これは、ジャンクトラフィックをWebサイトに送信するか、ボットを使用してアフィリエイト報酬をトリガーすることによって実行されます。 正確な方法に関係なく、オンライン広告で作成された3ドルごとに1ドルかかります。 言い換えれば、それは毎日コミットされているかなりの量の盗まれた収入です。

この場合、銀行と他のCPAネットワークユーザーの両方に役立つ、ライファイゼン銀行向けにOWOXBIチームが提供するソリューションについて説明します。

目次

  • チャレンジ
  • 解決
    • ステップ1.生データを収集する
    • ステップ2.データを処理する
    • ステップ3.レポートを作成する
  • 結果

チャレンジ

Raiffeisenのマーケティングスペシャリストは、アフィリエイトトラフィックのコストが劇的に増加していることを発見しましたが、収益は同じままです。 もう1つの問題は、銀行の顧客がWebサイトの申請フォームにデータを入力するときに短いセッション休憩があったことでした。

そのため、Raiffeisenは、CPAアフィリエイトの一部が、銀行のチェックアウトページのトラフィックソース値を代用できた可能性があると想定しました。 これは次のように機能します。たとえば、ユーザーがブラウザ拡張機能をインストールして割引を受けます。 ユーザーがWebサイトにアクセスしてチェックアウトを開くと、拡張機能は割引オファーを含むポップアップウィンドウを表示します。 ポップアップウィンドウからリンクをクリックすると、拡張機能はCookie内のトラフィックソースデータをアフィリエイトのトラフィックソースデータで自動的に書き換えます。

解決

マーケティングチームは、アフィリエイトがトラフィックソースデータを自分たちの利益のために書き換えるという仮説を証明するために、生のユーザー行動データを収集することから始めることにしました。 次に、特定の顧客に関するデータが収集されました。 これらの顧客は、60秒以内に同じページで2つのセッションを行い、2番目のセッションのトラフィックソースはアフィリエイトセッションに切り替えられました。 これらの顧客に関するこのようなデータは、他のチャネルからのトラフィックを自分自身に帰したアフィリエイトパートナーを特定するのに役立ちます。 このデータが提供されると、悪意を持って行動するウェブマスターとの協力をやめ、マーケティングの予算を最適化することが可能になる可能性があります。

OWOX BIチームは、必要なデータの収集と処理を支援しました。 データフローを示すスキーマは次のとおりです。

ステップ1.生データを収集する

Google Analyticsは、セッション数がサンプリングしきい値を超えたときにデータをサンプリングします。 そのため、OWOXBIアナリストはGoogleBigQueryでデータを収集することを提案しました。 さらに、このクラウドデータウェアハウスは、銀行にとって非常に重要な最高のセキュリティ基準を満たしています。

ウェブサイトからGoogleBigQueryへのデータインポートを設定するために、RaiffeisenはOWOXBIPipelineを使用しました。 このようにして、会社のスペシャリストはほぼリアルタイムでサンプリングされていないデータを取得し、すべてのヒットのタイムスタンプを収集しました。 このようなソリューションにより、単一のレポート内で、セッション全体で可能なすべてのユーザーアクションのシーケンスを追跡できました。 たとえば、会社の/プロモーションページにアクセスし、CPCを介してWebサイトに戻り、最後に何かを購入したユーザーに関するレポートが必要です。 もう1つ必要なのは、レポートで選択した日付のこれらの訪問を表示することです。 必要なレポートを取得するためのクエリは次のとおりです。

    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

以下は、クエリの結果として取得されたすべてのデータの表です。

ステップ2.データを処理する

必要な値のリストは、アフィリエイトで変更されたトラフィックソース値を識別するために作成されました。

  • ユーザーID。
  • 最初と次のセッションのソースとメディア。
  • セッション間の時間。
  • 各セッションの最初と最後のURL。
  • 各セッションのイベント。
  • 最終セッションのトランザクションイベント。

次に、トラフィックソースの値が確実に書き換えられるようにするために、OWOX BIアナリストは、次の条件を考慮してデータをフィルタリングすることを選択しました。

  • 2つのセッション間の時間は最大60秒である必要があります。
  • ソースが変更されても、Webサイトページは同じままである必要があります。
  • 最終セッションでトランザクションが発生するはずです。
  • 最終セッションのトラフィックメディアはアフィリエイトである必要があります。

必要なデータを取得するために使用されたSQLクエリは次のとおりです。

    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

ステップ3.レポートを作成する

さらに分析するために、選択したデータをGoogleBigQueryからGoogleスプレッドシートにインポートするアドオンを適用しました。 インポートされたデータを使用して、2つのセッション間で1分以内に、セッションが閉じられ、新しいセッションがまったく同じページで開かれた各顧客のIDを使用してテーブルが作成されました。

次に、アフィリエイトマーケターが悪意を持って行動したことを示すために、データがピボットテーブルにまとめられました。 以下のスクリーンショットの番号は変更されており、例として示されています。

たとえば、レポートには、ソース値が書き換えられたトランザクションの数や、トラフィックソースを独自のアフィリエイトに置き換えたアフィリエイトが示されます。 レポートには、トランザクションが奪われたチャネル（CPCとオーガニック）も表示されます。

結果

OWOX BIチームは、銀行のCPAネットワークの弱点をすばやく特定して排除するのに役立ちました。 提供されたソリューションのおかげで、銀行はアフィリエイトの統計を監視し（より正確には、コンバージョンと売上をトラフィックチャネルに関連付ける）、CPAネットワークでの不正の事例を明らかにすることができました。 マーケティングチームは、トラフィックソースを書き直し、Raiffeisen銀行に不当に過剰請求した、2つの不正なパートナーとの協力をやめることで、広告予算を最適化することに成功しました。