Skip to main content

Snowflake Example Queries

You can use SnowSQL queries in Snowflake to analyze your HUMAN data. Although you can use any custom queries that you'd like, we've compiled some suggested queries to help maximize the information you receive from HUMAN's analysis.

MediaGuard

The following sample queries are for MediaGuard (pre-bid) data.

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(ivt_bots) AS ivt,
ivt / total AS percent_ivt,
sum(sivt_bots) AS sivt,
sivt / total AS percent_sivt,
sum(givt_bots) AS givt,
givt / total AS percent_givt,
sum(valid_requests) as valid,
valid / total AS percent_valid
FROM mediaguard_prebid
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(ivt_bots) AS ivt,
ivt / total AS percent_ivt,
sum(sivt_bots) AS sivt,
sivt / total AS percent_sivt,
sum(givt_bots) AS givt,
givt / total AS percent_givt,
sum(valid_requests) as valid,
valid / total AS percent_valid
FROM mediaguard_prebid
GROUP BY 1;

App ID report (sampled traffic)

This query returns the app bundles that are driving the highest rates of IVT (as a relative percentage of their overall traffic). This query uses sampled data to perform its analysis.

SELECT
app_id,
sum(requests) as total,
sum(ivt_bots) AS ivt,
ivt / total AS percent_ivt,
sum(sivt_bots) AS sivt,
sivt / total AS percent_sivt,
sum(givt_bots) AS givt,
givt / total AS percent_givt,
sum(valid_requests) as valid,
valid / total AS percent_valid
FROM mediaguard_prebid_sampled
GROUP BY 1;

FraudSensor

The following sample queries are for FraudSensor (post-bid) data.

CTV overview

This query returns a detailed breakdown of CTV advertising traffic.

SELECT
environment,
ua_os_name,
ct:"de" as device_type,
count(*) as total,
count(*)/SUM(count(*)) OVER() AS percent_total,
sum(iff(bot, 1, 0)) AS ivt,
ivt / total AS percent_ivt,
sum(iff(sivt, 1, 0)) AS sivt,
sivt / total AS percent_sivt,
sum(iff(givt, 1, 0)) AS givt,
givt / total AS percent_givt,
sum(iff(ivt, 0, 1)) as valid,
valid / total AS percent_valid
FROM mediaguard_postbid
WHERE platform = '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,
ua_os_name,
ct:"de" as device_type,
count(*) as total,
count(*)/SUM(count(*)) OVER() AS percent_total,
sum(iff(bot, 1, 0)) AS ivt,
ivt / total AS percent_ivt,
sum(iff(sivt, 1, 0)) AS sivt,
sivt / total AS percent_sivt,
sum(iff(givt, 1, 0)) AS givt,
givt / total AS percent_givt,
sum(iff(ivt, 0, 1)) as valid,
valid / total AS percent_valid
FROM mediaguard_postbid
WHERE platform = '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
domains:"href_domain" as href_domain,
count(*) as total,
sum(iff(bot, 1, 0)) AS ivt,
bot / total AS percent_ivt,
sum(iff(sivt, 1, 0)) AS sivt,
sivt / total AS percent_sivt,
sum(iff(givt, 1, 0)) AS givt,
givt / total AS percent_givt,
sum(iff(bot, 0, 1)) as valid,
valid / total AS percent_valid
FROM mediaguard_postbid
GROUP BY 1
HAVING percent_sivt > 0.49;

BotGuard for Growth Marketing

The following sample queries are for BotGuard for Growth Marketing data.

Campaign-level data

This query returns the campaigns that are driving the highest rates of invalid traffic.

SELECT
UTM_CAMPAIGN,
COUNT(*) AS total_sessions,
SUM(iff(bot, 1, 0)) AS ivt_sessions,
ivt_sessions / total_sessions AS percent_ivt_sessions,
SUM(iff(sivt, 1, 0)) AS sivt_sessions,
sivt_sessions / total_sessions AS percent_sivt_sessions,
SUM(iff(givt, 1, 0)) AS givt_sessions,
givt_sessions / total_sessions AS percent_givt_sessions,
SUM(IFF(bot, 0, 1)) AS valid_sessions,
valid_sessions / total_sessions AS percent_valid_sessions
FROM botguard_growth_marketing
GROUP BY 1
ORDER BY 1 nulls FIRST;
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,
SUM(iff(bot, 1, 0)) AS ivt_sessions,
ivt_sessions / total_sessions AS percent_ivt_sessions,
SUM(iff(sivt, 1, 0)) AS sivt_sessions,
sivt_sessions / total_sessions AS percent_sivt_sessions,
SUM(iff(givt, 1, 0)) AS givt_sessions,
givt_sessions / total_sessions AS percent_givt_sessions,
SUM(IFF(bot, 0, 1)) AS valid_sessions,
valid_sessions / total_sessions AS percent_valid_sessions
FROM botguard_growth_marketing
GROUP BY 1
ORDER BY 1 nulls FIRST;

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,
SUM(iff(bot, 1, 0)) AS ivt_sessions,
ivt_sessions / total_sessions AS percent_ivt_sessions,
SUM(iff(sivt, 1, 0)) AS sivt_sessions,
sivt_sessions / total_sessions AS percent_sivt_sessions,
SUM(iff(givt, 1, 0)) AS givt_sessions,
givt_sessions / total_sessions AS percent_givt_sessions,
SUM(IFF(bot, 0, 1)) AS valid_sessions,
valid_sessions / total_sessions AS percent_valid_sessions
FROM botguard_growth_marketing
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
domains:"page" as page,
COUNT(*) AS total_sessions,
SUM(iff(bot, 1, 0)) AS ivt_sessions,
ivt_sessions / total_sessions AS percent_ivt_sessions,
SUM(iff(sivt, 1, 0)) AS sivt_sessions,
sivt_sessions / total_sessions AS percent_sivt_sessions,
SUM(iff(givt, 1, 0)) AS givt_sessions,
givt_sessions / total_sessions AS percent_givt_sessions,
SUM(IFF(bot, 0, 1)) AS valid_sessions,
valid_sessions / total_sessions AS percent_valid_sessions
FROM botguard_growth_marketing
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,
environment,
ua_os_name,
COUNT(*) AS total_sessions,
SUM(iff(bot, 1, 0)) AS ivt_sessions,
ivt_sessions / total_sessions AS percent_ivt_sessions,
SUM(iff(sivt, 1, 0)) AS sivt_sessions,
sivt_sessions / total_sessions AS percent_sivt_sessions,
SUM(iff(givt, 1, 0)) AS givt_sessions,
givt_sessions / total_sessions AS percent_givt_sessions,
SUM(IFF(bot, 0, 1)) AS valid_sessions,
valid_sessions / total_sessions AS percent_valid_sessions
FROM botguard_growth_marketing
GROUP BY 1,2,3,4,5;

BotGuard for Applications

The following sample queries are for BotGuard for Applications data.

Event type overview

This query returns a list of event types that are driving the highest levels of invalid traffic.

SELECT
    ct:"et" AS event_type,
    SUM(iff(threat_profile = 'VAL', 1, 0) AS total_valid,
    SUM(iff(threat_profile = 'NSD', 1, 0) AS total_nonstandard,
    SUM(iff(threat_profile = 'BOT', 1, 0) AS total_bot,
    COUNT(*) AS total_events,
    total_valid / total_events AS percent_valid,
    total_nonstandard / total_events AS percent_nonstandard,
    total_bot / total_events AS percent_bot
FROM botguard_apps
GROUP BY 1
ORDER BY 1 nulls FIRST;

Threat categories overview

This query returns an overview of the most common threat categories detected in your overall traffic.

SELECT
    threat_categories_str,
    SUM(iff(threat_profile = 'VAL', 1, 0) AS total_valid,
    SUM(iff(threat_profile = 'NSD', 1, 0) AS total_nonstandard,
    SUM(iff(threat_profile = 'BOT', 1, 0) AS total_bot,
    COUNT(*) AS total_events,
    total_valid / total_events AS percent_valid,
    total_nonstandard / total_events AS percent_nonstandard,
    total_bot / total_events AS percent_bot
FROM botguard_apps
GROUP BY 1
ORDER BY 1 nulls FIRST;