DataBull Search
advertising_marketing_small/ip_2_cluster.view.lookml +3 18 dims4 measures 3 warnings Repo files

LookML view · advertising-marketing-small

IP cluster views: two- and three-cluster labels with behaviour ratios per IP

At a glance

Does
Four views over clustering output. ip_2_cluster.class and ip_3_cluster.class label each IP. The model views add features: ip_model_2_clusters.geos, ip_model_2_clusters.pids, ip_model_2_clusters.rel_domains, ip_model_2_clusters.rel_req and ip_model_2_clusters.rel_uas.Trust: Source-defined
Why
Segments IPs into two or three behaviour groups so analysts can separate suspicious from normal traffic. The features count distinct geos and publishers per IP, plus relative domain, request and UA spread.Trust: Inferred
Breaks
3 warnings. Top: Cluster numbers have no stable meaning
Grain
One row per IP per clustering run. Each view is its own explore, so labels cannot be attached to impressions inside Looker.Trust: Inferred
Source
advertising_marketing_small/ip_2_cluster.view.lookml, advertising_marketing_small/ip_3_cluster.view.lookml, advertising_marketing_small/ip_model_2_clusters.view.lookml, advertising_marketing_small/ip_model_3_clusters.view.lookml 78 lines
Source: ip_2_cluster.view.lookml L1-10
advertising_marketing_small/ip_2_cluster.view.lookmlL1-10
- view: ip_2_cluster  fields:  - dimension: class    type: int    sql: ${TABLE}.class  - dimension: ip    sql: ${TABLE}.ip  - measure: count    type: count    detail: detail*  sets:    detail: null

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

Shape

Trust: Source-defined

Every field as declared, with the dashboard tiles that read it. Computed from the source; nothing here is edited.

ip_2_cluster ip_2_cluster.view.lookml

Table: defaults to ip_2_cluster (no sql_table_name).

Dimensions 2
NameTypeSQLUsed byTags
classL3int${TABLE}.class-
ipL6string${TABLE}.ip-

Measures 1
NameTypeSQLUsed byTags
countL8countCOUNT(*) of rows-

ip_3_cluster ip_3_cluster.view.lookml

Table: defaults to ip_3_cluster (no sql_table_name).

Dimensions 2
NameTypeSQLUsed byTags
classL3int${TABLE}.class-
ipL6string${TABLE}.ip-

Measures 1
NameTypeSQLUsed byTags
countL8countCOUNT(*) of rows-

ip_model_2_clusters ip_model_2_clusters.view.lookml

Table: defaults to ip_model_2_clusters (no sql_table_name).

Dimensions 7
NameTypeSQLUsed byTags
classL3int${TABLE}.class-
geosL6int${TABLE}.geos-
ipL9string${TABLE}.ip-
pidsL11int${TABLE}.pids-
rel_domainsL14number${TABLE}.rel_domains-
rel_reqL17number${TABLE}.rel_req-
rel_uasL20number${TABLE}.rel_uas-

Measures 1
NameTypeSQLUsed byTags
countL23countCOUNT(*) of rows-

ip_model_3_clusters ip_model_3_clusters.view.lookml

Table: defaults to ip_model_3_clusters (no sql_table_name).

Dimensions 7
NameTypeSQLUsed byTags
classL3int${TABLE}.class-
geosL6int${TABLE}.geos-
ipL9string${TABLE}.ip-
pidsL11int${TABLE}.pids-
rel_domainsL14number${TABLE}.rel_domains-
rel_reqL17number${TABLE}.rel_req-
rel_uasL20number${TABLE}.rel_uas-

Measures 1
NameTypeSQLUsed byTags
countL23countCOUNT(*) of rows-

Relationships

Trust: Source-defined

No joins, no upstream views and no dashboard tiles reference these views. Each one is an island in the model.

Relationships of ip_2_cluster, ip_3_cluster, ip_model_2_clusters, ip_model_3_clusters
Relationships of ip_2_cluster, ip_3_cluster, ip_model_2_clusters, ip_model_3_clusters. Text version follows the diagram.These viewsUsed by: explores, views, dashboardsbase viewbase viewbase viewbase view ip_2_cluster explore in odd_browser_and_os_analysis ip_3_cluster explore in odd_browser_and_os_analysis ip_model_2_clusters explore in odd_browser_and_os_analysis ip_model_3_clusters explore in odd_browser_and_os_analysis
  • This Block
Text version
  • ip_2_cluster is the base view of ip_2_cluster: base view
  • ip_3_cluster is the base view of ip_3_cluster: base view
  • ip_model_2_clusters is the base view of ip_model_2_clusters: base view
  • ip_model_3_clusters is the base view of ip_model_3_clusters: base view

Worth noticing

The 2- and 3-cluster views are identical in shape

Trust: Inferred

ip_model_2_clusters and ip_model_3_clusters declare the same seven dimensions. Only the table differs, which suggests two runs of one model with a different k.

Source: ip_model_2_clusters.view.lookml L1-22
advertising_marketing_small/ip_model_2_clusters.view.lookmlL1-22
- view: ip_model_2_clusters  fields:  - dimension: class    type: int    sql: ${TABLE}.class  - dimension: geos    type: int    sql: ${TABLE}.geos  - dimension: ip    sql: ${TABLE}.ip  - dimension: pids    type: int    sql: ${TABLE}.pids  - dimension: rel_domains    type: number    sql: ${TABLE}.rel_domains  - dimension: rel_req    type: number    sql: ${TABLE}.rel_req  - dimension: rel_uas    type: number    sql: ${TABLE}.rel_uas  - measure: count    type: count    detail: detail*

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

Ratios are typed number, counts are typed int

Trust: Inferred

The rel_* features use type: number while ip_model_2_clusters.geos and ip_model_2_clusters.pids use type: int. That fits normalised ratios next to raw distinct counts.

Source: ip_model_2_clusters.view.lookml L6-22
advertising_marketing_small/ip_model_2_clusters.view.lookmlL6-22
  - dimension: class    type: int    sql: ${TABLE}.class  - dimension: geos    type: int    sql: ${TABLE}.geos  - dimension: ip    sql: ${TABLE}.ip  - dimension: pids    type: int    sql: ${TABLE}.pids  - dimension: rel_domains    type: number    sql: ${TABLE}.rel_domains  - dimension: rel_req    type: number    sql: ${TABLE}.rel_req  - dimension: rel_uas    type: number    sql: ${TABLE}.rel_uas  - measure: count    type: count    detail: detail*

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

What can change the answer 3

Caution: Cluster numbers have no stable meaning

Trust: Inferred

ip_2_cluster.class is an integer with no labels. Cluster ids are arbitrary per run, and nothing maps class 1 in one run to class 1 in another.

What goes wrong: A saved look that filters class = 1 can switch from the suspicious group to the normal one after a rerun.

Source: ip_2_cluster.view.lookml L3-5
advertising_marketing_small/ip_2_cluster.view.lookmlL3-5
- view: ip_2_cluster  fields:  - dimension: class    type: int    sql: ${TABLE}.class  - dimension: ip    sql: ${TABLE}.ip  - measure: count

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

Caution: Labels never reach the impressions

Trust: Source-defined

No explore joins a cluster view to other_browser_os_impressions on ip. Cluster-level traffic or revenue is not source-defined.

What goes wrong: Per-cluster revenue built outside Looker depends on whoever joins the exports, 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
- explore: click_interactions_by_ip_for_useable_campaigns- explore: conversions_by_ip_for_useable_campaigns- explore: geo_pid_facts- explore: ip_2_cluster- explore: ip_3_cluster- explore: ip_model_2_clusters- explore: ip_model_3_clusters- explore: other_browser_os_impressions- explore: useable_campaign_ids

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

Note: Feature definitions are missing

Trust: Missing

What rel_domains, rel_req and rel_uas are relative to is not documented anywhere in the repo.

Read the guide: Missing tables and stale references

Source: ip_model_3_clusters.view.lookml L14-22
advertising_marketing_small/ip_model_3_clusters.view.lookmlL14-22
  - dimension: pids    type: int    sql: ${TABLE}.pids  - dimension: rel_domains    type: number    sql: ${TABLE}.rel_domains  - dimension: rel_req    type: number    sql: ${TABLE}.rel_req  - dimension: rel_uas    type: number    sql: ${TABLE}.rel_uas  - measure: count    type: count    detail: detail*

advertising-marketing-small, ip_model_3_clusters.view.lookml, lines 14-22

Parser checks 4

Trust: Source-defined

Mechanical findings from parsing the LookML. They are facts about the files. The cards above explain which ones matter.

Show all 4 checks
  • no-primary-key 4
    • ip_2_cluster.view.lookml:1 ip_2_cluster declares no primary_key.
    • ip_3_cluster.view.lookml:1 ip_3_cluster declares no primary_key.
    • ip_model_2_clusters.view.lookml:1 ip_model_2_clusters declares no primary_key.
    • ip_model_3_clusters.view.lookml:1 ip_model_3_clusters declares no primary_key.

Related

Guides built on this file

Guide Using IP clusters to investigate suspicious ad traffic How a real ad-tech model groups IPs by how widely they spread across geos, publishers and user agents, and why its cluster labels never reach the traffic. Acquisition Guide Calculating CTR from separate impression and click tables When clicks and impressions live in separate tables, a rate needs one population and one grain. How to check, with real tables that share neither. Best practicesAcquisition Guide Catching missing tables and stale references before a dashboard breaks Models can name tables, fields and explores that do not exist, and nothing fails until a chart runs. Real examples, and how to check yours before release. Best practices

Related source files

Source file other_browser_os_impressions Exposes raw ad impression rows with browser, OS, user agent, IP, geography, subid, campaign, creative and b... view4 warnings Source file campaign clicks and conversions Two views hold a stored click_interactions_by_ip_for_useable_campaigns.count per IP, one for clicks and one... 3 views2 warnings

Source model

Source model Inside an AdTech LookML project: traffic quality, publisher revenue and IP clustering AdTech traffic quality 9 views0 joins