Acquisition and traffic quality

Using IP clusters to investigate suspicious ad traffic

Which IPs behave like automated or pooled traffic, judged by how many geos, publishers, domains and user agents they touch?

Based on this implementation

advertising-marketing-small: AdTech traffic quality. LookML, MySQL SQL

5 source files

Code excerpts are short and verbatim. SQL is translated from them and has not been run against a warehouse.

Trust key

Trust: Source-defined: Directly observable in the repo.
Trust: Translated: Generated from the source definitions automatically; not run against a warehouse.
Trust: Inferred: Interpretation based on names, usage or structure.
Trust: Suggested: Not in the source. An alternative or extension.
Trust: Missing: Needed to establish correctness but not in the repo.

See the full advertising-marketing-small source model

Short answer

Inferred

Profile each IP by how widely it spreads (geos, publishers, domains, user agents), group similar IPs, then check which group carries your traffic and spend. This project stops halfway: it stores a cluster label per IP but never joins it to impressions, and cluster numbers change meaning between runs, so a label alone says nothing about risk.

Decision it supports: Which IP groups to exclude or review before paying publishers.

What to measure

IPs per cluster

Source-defined

IP rows, grouped by class.

Defined as ip_model_2_clusters.count

COUNT(*)Trust: Translated

Show the SQL: IPs per cluster

MySQL SQLTranslated

COUNT(*)

Translated to MySQL SQL from ip_model_2_clusters.view.lookml L23-25. Not run against a warehouse.

Show the original LookML: ip_model_2_clusters.view.lookml L23-25

advertising_marketing_small/ip_model_2_clusters.view.lookmlL23-25 Show context

20  - dimension: rel_uas21    type: number22    sql: ${TABLE}.rel_uas23  - measure: count24    type: count25    detail: detail*26  sets:27    detail: null

advertising-marketing-small, ip_model_2_clusters.view.lookml, lines 23-25

IPs per label

Source-defined

IP rows in the label-only view.

Defined as ip_2_cluster.count

COUNT(*)Trust: Translated

Show the SQL: IPs per label

MySQL SQLTranslated

COUNT(*)

Translated to MySQL SQL from ip_2_cluster.view.lookml L8-10. Not run against a warehouse.

Show the original LookML: ip_2_cluster.view.lookml L8-10

advertising_marketing_small/ip_2_cluster.view.lookmlL8-10 Show context

5    sql: ${TABLE}.class6  - dimension: ip7    sql: ${TABLE}.ip8  - measure: count9    type: count10    detail: detail*11  sets:12    detail: null

advertising-marketing-small, ip_2_cluster.view.lookml, lines 8-10

Impressions per cluster

Missing

Needs a join from cluster labels to impressions on ip. The project has none.

Which cluster is risky

Inferred

Not recorded. It has to be read from the average features per class.

The query

IPs per cluster

Suggested

How many IPs fall in each class of the two-cluster run. It is as far as the project's own fields go; the profile and the traffic per cluster need the suggested SQL below.

Show the SQL: IPs per cluster

MySQL SQLSuggested

SELECT  ip_model_2_clusters.class AS class,  COUNT(*) AS countFROM ip_model_2_clustersGROUP BY 1ORDER BY class

ip_model_2_clusters declares no sql_table_name, so it reads a table named ip_model_2_clusters.

Suggested query. Fields translated to MySQL SQL from odd_browser_and_os_analysis.model.lookml L10 and ip_model_2_clusters.view.lookml L3-5, L23-25. Not run against a warehouse.

The data behind it

Inferred

Entities: IP, cluster run
Grain: One row per IP per clustering run.
Time: None. Runs are not dated.
Key fields: ip_model_2_clusters.ipip_model_2_clusters.classip_model_2_clusters.geosip_model_2_clusters.pidsip_model_2_clusters.rel_uas

What can change the answer 3

Caution: Labels are not joined to traffic

Source-defined

No explore joins a cluster view to impressions, clicks or conversions.

What goes wrong: Cluster-level traffic or revenue is built by hand outside Looker, with no grain check.

Read the guide: CTR from separate tables

Source: odd_browser_and_os_analysis.model.lookml L8-12

advertising_marketing_small/odd_browser_and_os_analysis.model.lookmlL8-12 Show context

5- explore: click_interactions_by_ip_for_useable_campaigns6- explore: conversions_by_ip_for_useable_campaigns7- explore: geo_pid_facts8- explore: ip_2_cluster9- explore: ip_3_cluster10- explore: ip_model_2_clusters11- explore: ip_model_3_clusters12- explore: other_browser_os_impressions13- explore: useable_campaign_ids

advertising-marketing-small, odd_browser_and_os_analysis.model.lookml, lines 8-12

Caution: Class ids change between runs

Inferred

Cluster numbers are arbitrary per run. Class 1 in the 3-cluster view has no relation to class 1 in the 2-cluster view.

Show the SQL: class

MySQL SQLTranslated

ip_3_cluster.class

Translated to MySQL SQL from ip_3_cluster.view.lookml L3-5. Not run against a warehouse.

Show the original LookML: ip_3_cluster.view.lookml L3-5

advertising_marketing_small/ip_3_cluster.view.lookmlL3-5 Show context

1- view: ip_3_cluster2  fields:3  - dimension: class4    type: int5    sql: ${TABLE}.class6  - dimension: ip7    sql: ${TABLE}.ip8  - measure: count

advertising-marketing-small, ip_3_cluster.view.lookml, lines 3-5

Note: IP is an untyped string

Inferred

Every view stores ip as a string. Formatting differences or IPv6 forms can silently break a future join.

Show the SQL: ip

MySQL SQLTranslated

other_browser_os_impressions.ip

Translated to MySQL SQL from other_browser_os_impressions.view.lookml L27-28. Not run against a warehouse.

Show the original LookML: other_browser_os_impressions.view.lookml L27-28

advertising_marketing_small/other_browser_os_impressions.view.lookmlL27-28 Show context

24  - dimension: domain_id25    type: int26    sql: ${TABLE}.domain_id27  - dimension: ip28    sql: ${TABLE}.ip29  - dimension: new_subid30    sql: ${TABLE}.new_subid31  - dimension: os

advertising-marketing-small, other_browser_os_impressions.view.lookml, lines 27-28

How the real implementation does it

ip cluster views

Source-defined

The model view carries the features per IP next to its class.

Source: ip_model_2_clusters.view.lookml L1-22

advertising_marketing_small/ip_model_2_clusters.view.lookmlL1-22 Show context

1- view: ip_model_2_clusters2  fields:3  - dimension: class4    type: int5    sql: ${TABLE}.class6  - dimension: geos7    type: int8    sql: ${TABLE}.geos9  - dimension: ip10    sql: ${TABLE}.ip11  - dimension: pids12    type: int13    sql: ${TABLE}.pids14  - dimension: rel_domains15    type: number16    sql: ${TABLE}.rel_domains17  - dimension: rel_req18    type: number19    sql: ${TABLE}.rel_req20  - dimension: rel_uas21    type: number22    sql: ${TABLE}.rel_uas23  - measure: count24    type: count25    detail: detail*

advertising-marketing-small, ip_model_2_clusters.view.lookml, lines 1-22

ip cluster views

Source-defined

The label view keeps only IP and class, one per run.

Source: ip_2_cluster.view.lookml L1-10

advertising_marketing_small/ip_2_cluster.view.lookmlL1-10 Show context

1- view: ip_2_cluster2  fields:3  - dimension: class4    type: int5    sql: ${TABLE}.class6  - dimension: ip7    sql: ${TABLE}.ip8  - measure: count9    type: count10    detail: detail*11  sets:12    detail: null

advertising-marketing-small, ip_2_cluster.view.lookml, lines 1-10

other_browser_os_impressions

Inferred

Impressions carry the same ip string, so a join is possible in principle.

Show the SQL: the impression ip

MySQL SQLTranslated

other_browser_os_impressions.ip

Translated to MySQL SQL from other_browser_os_impressions.view.lookml L27-28. Not run against a warehouse.

Show the original LookML: other_browser_os_impressions.view.lookml L27-28

advertising_marketing_small/other_browser_os_impressions.view.lookmlL27-28 Show context

24  - dimension: domain_id25    type: int26    sql: ${TABLE}.domain_id27  - dimension: ip28    sql: ${TABLE}.ip29  - dimension: new_subid30    sql: ${TABLE}.new_subid31  - dimension: os

advertising-marketing-small, other_browser_os_impressions.view.lookml, lines 27-28

Other ways to measure it

Profile each cluster

Suggested

Average the features per class so you can tell which cluster spreads across many geos, publishers and user agents.

Show the suggested SQL: cluster profile

Suggested, not from source

SELECT  class,  COUNT(*) AS ips,  AVG(geos) AS avg_geos,  AVG(pids) AS avg_publishers,  AVG(rel_domains) AS avg_rel_domains,  AVG(rel_uas) AS avg_rel_user_agentsFROM ip_model_2_clustersGROUP BY class

Join labels to the traffic

Suggested

Attach one clustering run to impressions so traffic and bids can be grouped by cluster. It assumes one row per IP in that run; in LookML the same join is a many_to_one on ip.

Show the suggested SQL: impressions and bids per cluster

Suggested, not from source

SELECT  c.class,  COUNT(*) AS impressions,  SUM(i.bid_amount) AS bidsFROM other_browser_os_impressions AS iJOIN ip_model_2_clusters AS c ON c.ip = i.ipGROUP BY c.class

Name the clusters

Suggested

Store a label per class and run, such as suspicious and normal, in a small mapping table, and join it to the label views.

Short answer

What to measure

IPs per cluster

IPs per label

Impressions per cluster

Which cluster is risky

The query

IPs per cluster

The data behind it

What can change the answer 3

Caution: Labels are not joined to traffic

Caution: Class ids change between runs

Note: IP is an untyped string

How the real implementation does it

ip cluster views

ip cluster views

other_browser_os_impressions

Other ways to measure it

Profile each cluster

Join labels to the traffic

Name the clusters

Related questions