Skip to main content

Snowflake Example Queries

You can use SnowSQL queries in Snowflake to analyze your MediaGuard and FraudSensor 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;