กรณีธนาคาร Raiffeisen: วิธีจัดการกับการฉ้อโกงในเครือข่าย CPA

สารบัญ

• ท้าทาย
• สารละลาย
  • ขั้นตอนที่ 1 รวบรวมข้อมูลดิบ
  • ขั้นตอนที่ 2 ประมวลผลข้อมูล
  • ขั้นตอนที่ 3 สร้างรายงาน
• ผลลัพธ์

ท้าทาย

สารละลาย

ขั้นตอนที่ 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 ประมวลผลข้อมูล

รายการค่าที่จำเป็นถูกสร้างขึ้นเพื่อระบุค่าแหล่งที่มาของการเข้าชมที่เปลี่ยนแปลงไปพร้อมกับค่าของ Affiliate:

• รหัสผู้ใช้
• ที่มาและสื่อของช่วงแรกและช่วงต่อไป
• เวลาระหว่างเซสชัน
• URL แรกและสุดท้ายของแต่ละเซสชัน
• เหตุการณ์ในแต่ละช่วง
• เหตุการณ์การทำธุรกรรมในเซสชั่นสุดท้าย

ต่อไป เพื่อให้แน่ใจว่าค่าแหล่งที่มาของการเข้าชมถูกเขียนใหม่อย่างแน่นอน นักวิเคราะห์ OWOX BI เลือกที่จะกรองข้อมูลโดยพิจารณาจากเงื่อนไขเหล่านี้:

• ช่วงเวลาระหว่างสองเซสชันควรไม่เกิน 60 วินาที
• หน้าเว็บไซต์ควรเหมือนเดิมเมื่อแหล่งที่มาเปลี่ยนแปลง
• ควรมีการทำธุรกรรมในช่วงสุดท้าย
• สื่อการเข้าชมในเซสชั่นสุดท้ายควรเป็นพันธมิตร

นี่คือแบบสอบถาม 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 สร้างรายงาน

ผลลัพธ์