You can use SQL queries in Snowflake to analyze your HUMAN data. Although you aren’t limited to the following configurations, we've compiled some suggested queries to help maximize the information you receive from HUMAN's analysis.
MediaGuard
Publisher Report
This query returns the ad publishers that are driving the highest rates of IVT (as a relative percentage of their overall traffic).
SELECT publisher_id,
sum(requests) as total,
sum(requests)/SUM(sum(requests)) OVER() AS percent_total,
sum(ivt_requests) AS ivt,
iff(sum(requests)=0, 0, sum(ivt_requests) / SUM(sum(ivt_requests)) OVER()) AS percent_ivt,
sum(sivt_requests) AS sivt,
iff(total=0, 0, (sum(sivt_requests) / sum(requests))) AS percent_sivt,
sum(givt_requests) AS givt,
iff((sum(requests))=0, 0, (sum(givt_requests)/(SUM(sum(givt_requests)) OVER()))) AS percent_givt,
sum(valid_requests) as valid,
iff((sum(requests))=0, 0, (sum(valid_requests)/(SUM(sum(valid_requests)) OVER()))) AS percent_valid
from advertising_integrity_mediaguard
group by 1;Supplier Report
This query returns the ad suppliers that are driving the highest rates of IVT (as a relative percentage of their overall traffic).
SELECT supplier_id,
sum(requests) as total,
sum(requests)/SUM(sum(requests)) OVER() AS percent_total,
sum(ivt_requests) AS ivt,
iff(sum(requests)=0, 0, sum(ivt_requests) / SUM(sum(ivt_requests)) OVER()) AS percent_ivt,
sum(sivt_requests) AS sivt,
iff(total=0, 0, (sum(sivt_requests) / sum(requests))) AS percent_sivt,
sum(givt_requests) AS givt,
iff((sum(requests))=0, 0, (sum(givt_requests)/(SUM(sum(givt_requests)) OVER()))) AS percent_givt,
sum(valid_requests) as valid,
iff((sum(requests))=0, 0, (sum(valid_requests)/(SUM(sum(valid_requests)) OVER()))) AS percent_valid
from advertising_integrity_mediaguard
group by 1;FraudSensor
CTV Overview
This query returns a detailed breakdown of CTV advertising traffic.
SELECT
environment,
os,
device_type,
count(*) as total,
count(*)/SUM(count(*)) OVER() AS percent_total,
sum(is_ivt) AS ivt,
iff((count(*))=0, 0, (sum(is_ivt)/(count(*)))::number(38,8)) AS percent_ivt,
sum(is_sivt) AS sivt,
iff((count(*))=0, 0, (sum(is_sivt)/(count(*)))::number(38,8)) AS percent_sivt,
sum(is_givt) AS givt,
iff((count(*))=0, 0, (sum(is_givt)/(count(*)))::number(38,8)) AS percent_givt,
sum(iff(is_ivt=1, 0, 1)) as valid,
iff((count(*))=0, 0, (sum(iff(is_ivt=1, 0, 1))/(count(*)))::number(38,8)) AS percent_valid
from advertising_integrity_fraudsensor
where platform_type = 'CTV'
GROUP BY 1, 2, 3
ORDER BY 1 nulls FIRST;Mobile Overview
This query returns a detailed breakdown of mobile advertising traffic.
SELECT
environment,
os,
device_type,
count(*) as total,
count(*)/SUM(count(*)) OVER() AS percent_total,
sum(is_ivt) AS ivt,
iff((count(*))=0, 0, (sum(is_ivt)/(count(*)))::number(38,8)) AS percent_ivt,
sum(is_sivt) AS sivt,
iff((count(*))=0, 0, (sum(is_sivt)/(count(*)))::number(38,8)) AS percent_sivt,
sum(is_givt) AS givt,
iff((count(*))=0, 0, (sum(is_givt)/(count(*)))::number(38,8)) AS percent_givt,
sum(iff(is_ivt=1, 0, 1)) as valid,
iff((count(*))=0, 0, (sum(iff(is_ivt=1, 0, 1))/(count(*)))::number(38,8)) AS percent_valid
from advertising_integrity_fraudsensor
where platform_type = 'Mobile'
GROUP BY 1, 2, 3
ORDER BY 1 nulls FIRST;Sophisticated IVT Hotspots
This query returns an overview of the href domains that drove high levels of Sophisticated Invalid Traffic (SIVT). If a domain appears in the results of this query, at least 49% of its total traffic has been flagged as SIVT. These fraudulent traffic sources can drain your advertising budget without delivering results, so you may wish to prevent further losses by directing your budget towards more lucrative sources.
SELECT
href_domain,
count(*) as total,
count(*)/SUM(count(*)) OVER() AS percent_total,
sum(is_ivt) AS ivt,
iff((count(*))=0, 0, (sum(is_ivt)/(count(*)))::number(38,8)) AS percent_ivt,
sum(is_sivt) AS sivt,
iff((count(*))=0, 0, (sum(is_sivt)/(count(*)))::number(38,8)) AS percent_sivt,
sum(is_givt) AS givt,
iff((count(*))=0, 0, (sum(is_givt)/(count(*)))::number(38,8)) AS percent_givt,
sum(iff(is_ivt=1, 0, 1)) as valid,
iff((count(*))=0, 0, (sum(iff(is_ivt=1, 0, 1))/(count(*)))::number(38,8)) AS percent_valid
from advertising_integrity_fraudsensor
GROUP BY 1 having percent_sivt > 0.49;BotGuard for Growth Marketing
Campaign Level Data
This query returns the campaigns that are driving the highest rates of invalid traffic.
SELECT UTM_CAMPAIGN,
COUNT(*) AS total_sessions,
total_sessions/SUM(total_sessions) OVER() AS percent_total_sessions,
SUM(ivt) AS ivt_sessions,
IFF(total_sessions=0, 0, ivt_sessions/total_sessions::number(38,8)) AS percent_ivt_sessions,
SUM(sivt) AS sivt_sessions,
IFF(total_sessions=0, 0, sivt_sessions/total_sessions::number(38,8)) AS percent_sivt_sessions,
SUM(givt) AS givt_sessions,
IFF(total_sessions=0, 0, givt_sessions/total_sessions::number(38,8)) AS percent_givt_sessions,
SUM(IFF(ivt=1, 0, 1)) AS valid_sessions,
IFF(total_sessions=0, 0, valid_sessions/total_sessions::number(38,8)) AS percent_valid_sessions
FROM (
SELECT utm_campaign, ivt, givt, sivt,
ROW_NUMBER() OVER(PARTITION BY session_id ORDER BY timestamp ASC) AS row_num
FROM marketing_integrity
)
WHERE row_num=1
GROUP BY 1
ORDER BY 1 nulls FIRST;SELECT UTM_CAMPAIGN,
COUNT(*) AS total_pageviews,
total_pageviews/SUM(total_pageviews) OVER() AS percent_total_pageviews,
SUM(ivt) AS ivt_pageviews,
IFF(total_pageviews=0, 0, ivt_pageviews/total_pageviews::number(38,8)) AS percent_ivt_pageviews,
SUM(sivt) AS sivt_pageviews,
IFF(total_pageviews=0, 0, sivt_pageviews/total_pageviews::number(38,8)) AS percent_sivt_pageviews,
SUM(givt) AS givt_pageviews,
IFF(total_pageviews=0, 0, givt_pageviews/total_pageviews::number(38,8)) AS percent_givt_pageviews,
SUM(IFF(ivt=1, 0, 1)) AS valid_pageviews,
IFF(total_pageviews=0, 0, valid_pageviews/total_pageviews::number(38,8)) AS percent_valid_pageviews
FROM marketing_integrity
GROUP BY 1
ORDER BY 1 nulls FIRST;Source Level Data
This query returns the marketing sources that are driving the highest rates of invalid traffic.
SELECT UTM_SOURCE,
COUNT(*) AS total_sessions,
total_sessions/SUM(total_sessions) OVER() AS percent_total_sessions,
SUM(ivt) AS ivt_sessions,
IFF(total_sessions=0, 0, ivt_sessions/total_sessions::number(38,8)) AS percent_ivt_sessions,
SUM(sivt) AS sivt_sessions,
IFF(total_sessions=0, 0, sivt_sessions/total_sessions::number(38,8)) AS percent_sivt_sessions,
SUM(givt) AS givt_sessions,
IFF(total_sessions=0, 0, givt_sessions/total_sessions::number(38,8)) AS percent_givt_sessions,
SUM(IFF(ivt=1, 0, 1)) AS valid_sessions,
IFF(total_sessions=0, 0, valid_sessions/total_sessions::number(38,8)) AS percent_valid_sessions
FROM (
SELECT utm_source, ivt, givt, sivt,
ROW_NUMBER() OVER(PARTITION BY session_id ORDER BY TIMESTAMP ASC) AS row_num
FROM marketing_integrity
)
WHERE row_num=1
GROUP BY 1
ORDER BY 1 nulls FIRST;SELECT UTM_SOURCE,
COUNT(*) AS total_pageviews,
total_pageviews/SUM(total_pageviews) OVER() AS percent_total_pageviews,
SUM(ivt) AS ivt_pageviews,
IFF(total_pageviews=0, 0, ivt_pageviews/total_pageviews::number(38,8)) AS percent_ivt_pageviews,
SUM(sivt) AS sivt_pageviews,
IFF(total_pageviews=0, 0, sivt_pageviews/total_pageviews::number(38,8)) AS percent_sivt_pageviews,
SUM(givt) AS givt_pageviews,
IFF(total_pageviews=0, 0, givt_pageviews/total_pageviews::number(38,8)) AS percent_givt_pageviews,
SUM(IFF(ivt=1, 0, 1)) AS valid_pageviews,
IFF(total_pageviews=0, 0, valid_pageviews/total_pageviews::number(38,8)) AS percent_valid_pageviews
FROM marketing_integrity
GROUP BY 1
ORDER BY 1 nulls FIRST;IVT Trends Over Time
This query returns a summary of IVT rates (as a relative percentage of overall traffic) on a day-by-day basis.
SELECT to_date(timestamp) AS date,
COUNT(*) AS total_sessions,
total_sessions/SUM(total_sessions) OVER() AS percent_total_sessions,
SUM(ivt) AS ivt_sessions,
IFF(total_sessions=0, 0, ivt_sessions/total_sessions::number(38,8)) AS percent_ivt_sessions,
SUM(sivt) AS sivt_sessions,
IFF(total_sessions=0, 0, sivt_sessions/total_sessions::number(38,8)) AS percent_sivt_sessions,
SUM(givt) AS givt_sessions,
IFF(total_sessions=0, 0, givt_sessions/total_sessions::number(38,8)) AS percent_givt_sessions,
SUM(IFF(ivt=1, 0, 1)) AS valid_sessions,
IFF(total_sessions=0, 0, valid_sessions/total_sessions::number(38,8)) AS percent_valid_sessions
FROM (
SELECT timestamp, ivt, givt, sivt,
ROW_NUMBER() OVER(PARTITION BY session_id ORDER BY TIMESTAMP ASC) AS row_num
FROM marketing_integrity
)
WHERE row_num=1
GROUP BY 1
ORDER BY 1 nulls FIRST;SELECT to_date(timestamp) AS date,
COUNT(*) AS total_pageviews,
total_pageviews/SUM(total_pageviews) OVER() AS percent_total_pageviews,
SUM(ivt) AS ivt_pageviews,
IFF(total_pageviews=0, 0, ivt_pageviews/total_pageviews::number(38,8)) AS percent_ivt_pageviews,
SUM(sivt) AS sivt_pageviews,
IFF(total_pageviews=0, 0, sivt_pageviews/total_pageviews::number(38,8)) AS percent_sivt_pageviews,
SUM(givt) AS givt_pageviews,
IFF(total_pageviews=0, 0, givt_pageviews/total_pageviews::number(38,8)) AS percent_givt_pageviews,
SUM(IFF(ivt=1, 0, 1)) AS valid_pageviews,
IFF(total_pageviews=0, 0, valid_pageviews/total_pageviews::number(38,8)) AS percent_valid_pageviews
FROM marketing_integrity
GROUP BY 1
ORDER BY 1 nulls FIRST;Page Level Data
This query returns an overview of how fraudulent traffic traverses your site on a page-by-page basis.
SELECT PAGE,
COUNT(*) AS total_sessions,
total_sessions/SUM(total_sessions) OVER() AS percent_total_sessions,
SUM(ivt) AS ivt_sessions,
IFF(total_sessions=0, 0, ivt_sessions/total_sessions::number(38,8)) AS percent_ivt_sessions,
SUM(sivt) AS sivt_sessions,
IFF(total_sessions=0, 0, sivt_sessions/total_sessions::number(38,8)) AS percent_sivt_sessions,
SUM(givt) AS givt_sessions,
IFF(total_sessions=0, 0, givt_sessions/total_sessions::number(38,8)) AS percent_givt_sessions,
SUM(IFF(ivt=1, 0, 1)) AS valid_sessions,
IFF(total_sessions=0, 0, valid_sessions/total_sessions::number(38,8)) AS percent_valid_sessions
FROM (
SELECT PAGE, ivt, givt, sivt,
ROW_NUMBER() OVER(PARTITION BY session_id ORDER BY TIMESTAMP ASC) AS row_num
FROM marketing_integrity
)
WHERE row_num=1
GROUP BY 1
ORDER BY 1 nulls FIRST;SELECT PAGE,
COUNT(*) AS total_pageviews,
total_pageviews/SUM(total_pageviews) OVER() AS percent_total_pageviews,
SUM(ivt) AS ivt_pageviews,
IFF(total_pageviews=0, 0, ivt_pageviews/total_pageviews::number(38,8)) AS percent_ivt_pageviews,
SUM(sivt) AS sivt_pageviews,
IFF(total_pageviews=0, 0, sivt_pageviews/total_pageviews::number(38,8)) AS percent_sivt_pageviews,
SUM(givt) AS givt_pageviews,
IFF(total_pageviews=0, 0, givt_pageviews/total_pageviews::number(38,8)) AS percent_givt_pageviews,
SUM(IFF(ivt=1, 0, 1)) AS valid_pageviews,
IFF(total_pageviews=0, 0, valid_pageviews/total_pageviews::number(38,8)) AS percent_valid_pageviews
FROM marketing_integrity
GROUP BY 1
ORDER BY 1 nulls FIRST;Platform, Environment, and OS Level Data
This query returns an overview of the device types (including environment and OS data) that are most commonly associated with IVT per marketing campaign.
SELECT utm_source, utm_medium, utm_campaign, device_type, os,
COUNT(*) AS total_sessions,
total_sessions/SUM(total_sessions) OVER() AS percent_total_sessions,
SUM(ivt) AS ivt_sessions,
IFF(total_sessions=0, 0, ivt_sessions/total_sessions::number(38,8)) AS percent_ivt_sessions,
SUM(sivt) AS sivt_sessions,
IFF(total_sessions=0, 0, sivt_sessions/total_sessions::number(38,8)) AS percent_sivt_sessions,
SUM(givt) AS givt_sessions,
IFF(total_sessions=0, 0, givt_sessions/total_sessions::number(38,8)) AS percent_givt_sessions,
SUM(IFF(ivt=1, 0, 1)) AS valid_sessions,
IFF(total_sessions=0, 0, valid_sessions/total_sessions::number(38,8)) AS percent_valid_sessions
FROM (
SELECT utm_source, utm_medium, utm_campaign, device_type, os, ivt, givt, sivt,
ROW_NUMBER() OVER(PARTITION BY session_id ORDER BY TIMESTAMP ASC) AS row_num
FROM marketing_integrity
)
WHERE row_num=1
GROUP BY 1,2,3,4,5;SELECT utm_source, utm_medium, utm_campaign, device_type, os,
COUNT(*) AS total_pageviews,
total_pageviews/SUM(total_pageviews) OVER() AS percent_total_pageviews,
SUM(ivt) AS ivt_pageviews,
IFF(total_pageviews=0, 0, ivt_pageviews/total_pageviews::number(38,8)) AS percent_ivt_pageviews,
SUM(sivt) AS sivt_pageviews,
IFF(total_pageviews=0, 0, sivt_pageviews/total_pageviews::number(38,8)) AS percent_sivt_pageviews,
SUM(givt) AS givt_pageviews,
IFF(total_pageviews=0, 0, givt_pageviews/total_pageviews::number(38,8)) AS percent_givt_pageviews,
SUM(IFF(ivt=1, 0, 1)) AS valid_pageviews,
IFF(total_pageviews=0, 0, valid_pageviews/total_pageviews::number(38,8)) AS percent_valid_pageviews
FROM marketing_integrity
GROUP BY 1,2,3,4,5;BotGuard for Applications
Event Type Data
This query returns a list of event types that are driving the highest levels of invalid traffic.
SELECT event_type,
SUM(valid) AS total_valid,
SUM(nonstandard) AS total_nonstandard,
SUM(bot) AS total_bot,
COUNT(*) AS total_events,
IFF(total_events=0, 0, total_valid/total_events::number(38,8)) AS percent_valid,
IFF(total_events=0, 0, total_nonstandard/total_events::number(38,8)) AS percent_nonstandard,
IFF(total_events=0, 0, total_bot/total_events::number(38,8)) AS percent_bot
FROM application_integrity
GROUP BY 1
ORDER BY 1 nulls FIRST;Threat Categories Data
This query returns an overview of the most common threat categories detected in your overall traffic.
SELECT threat_categories,
SUM(valid) AS total_valid,
SUM(nonstandard) AS total_nonstandard,
SUM(bot) AS total_bot,
COUNT(*) AS total_events,
IFF(total_events=0, 0, total_valid/total_events::number(38,8)) AS percent_valid,
IFF(total_events=0, 0, total_nonstandard/total_events::number(38,8)) AS percent_nonstandard,
IFF(total_events=0, 0, total_bot/total_events::number(38,8)) AS percent_bot
FROM application_integrity
GROUP BY 1
ORDER BY 1 nulls FIRST;