Snowflake Example Queries | HUMAN Documentation

You can use SnowSQL queries in Snowflake to analyze your MediaGuard and FraudSensor data. While you’re free to write custom queries, we’ve compiled some suggested queries to help you maximize the insights 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 who are driving the highest rates of IVT (as a relative percentage of their overall traffic).

1 SELECT
2     publisher_id,
3     sum(requests) AS total,
4     sum(ivt_bots) AS ivt,
5     ivt / total AS percent_ivt,
6     sum(sivt_bots) AS sivt,
7     sivt / total AS percent_sivt,
8     sum(givt_bots) AS givt,
9     givt / total AS percent_givt,
10     sum(valid_requests) AS valid,
11     valid / total AS percent_valid
12 FROM mediaguard_prebid
13 GROUP BY 1;

Supplier report

This query returns the ad suppliers who are driving the highest rates of IVT (as a relative percentage of their overall traffic).

1 SELECT
2     supplier_id,
3     sum(requests) AS total,
4     sum(ivt_bots) AS ivt,
5     ivt / total AS percent_ivt,
6     sum(sivt_bots) AS sivt,
7     sivt / total AS percent_sivt,
8     sum(givt_bots) AS givt,
9     givt / total AS percent_givt,
10     sum(valid_requests) AS valid,
11     valid / total AS percent_valid
12 FROM mediaguard_prebid
13 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.

1 SELECT
2     app_id,
3     sum(requests) AS total,
4     sum(ivt_bots) AS ivt,
5     ivt / total AS percent_ivt,
6     sum(sivt_bots) AS sivt,
7     sivt / total AS percent_sivt,
8     sum(givt_bots) AS givt,
9     givt / total AS percent_givt,
10     sum(valid_requests) AS valid,
11     valid / total AS percent_valid
12 FROM mediaguard_prebid_sampled
13 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.

1 SELECT
2     environment,
3     ua_os_name,
4     ct:"de" AS device_type,
5     count(*) AS total,
6     count(*)/SUM(count(*)) OVER() AS percent_total,
7     sum(iff(bot, 1, 0)) AS ivt,
8     ivt / total AS percent_ivt,
9     sum(iff(sivt, 1, 0)) AS sivt,
10     sivt / total AS percent_sivt,
11     sum(iff(givt, 1, 0)) AS givt,
12     givt / total AS percent_givt,
13     sum(iff(ivt, 0, 1)) AS valid,
14     valid / total AS percent_valid
15 FROM mediaguard_postbid
16 WHERE platform = 'CTV'
17 GROUP BY 1, 2, 3
18 ORDER BY 1 nulls FIRST;

Mobile overview

This query returns a detailed breakdown of mobile advertising traffic.

1 SELECT
2     environment,
3     ua_os_name,
4     ct:"de" AS device_type,
5     count(*) AS total,
6     count(*)/SUM(count(*)) OVER() AS percent_total,
7     sum(iff(bot, 1, 0)) AS ivt,
8     ivt / total AS percent_ivt,
9     sum(iff(sivt, 1, 0)) AS sivt,
10     sivt / total AS percent_sivt,
11     sum(iff(givt, 1, 0)) AS givt,
12     givt / total AS percent_givt,
13     sum(iff(ivt, 0, 1)) AS valid,
14     valid / total AS percent_valid
15 FROM mediaguard_postbid
16 WHERE platform = 'Mobile'
17 GROUP BY 1, 2, 3
18 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 want to prevent further losses by directing your budget towards more lucrative sources.

1 SELECT
2     domains:"href_domain" AS href_domain,
3     count(*) AS total,
4     sum(iff(bot, 1, 0)) AS ivt,
5     bot / total AS percent_ivt,
6     sum(iff(sivt, 1, 0)) AS sivt,
7     sivt / total AS percent_sivt,
8     sum(iff(givt, 1, 0)) AS givt,
9     givt / total AS percent_givt,
10     sum(iff(bot, 0, 1)) AS valid,
11     valid / total AS percent_valid
12 FROM mediaguard_postbid
13 GROUP BY 1
14 HAVING percent_sivt > 0.49;