Source model LookML, MySQL SQL

Inside an AdTech LookML project: traffic quality, publisher revenue and IP clustering

A compact, dashboard-first project on a fraud_research connection. One impressions view carries the only dashboard. Click, conversion, campaign and IP cluster views sit beside it as separate explores with no joins between them. Trust: Source-defined

SQL on these pages is MySQL. Inferred from substring_index in the source. Trust: Inferred

9 views1 model9 explores1 dashboard0 joins8 tiles55 fields10 measures

Repo map

Source-defined

Each lane is an explore. None declares a join, so every view is an island. Lane headings name the dashboards that query them.

Explores, joins and dashboards in advertising-marketing-small

Text version

click_interactions_by_ip_for_useable_campaigns (odd_browser_and_os_analysis): click_interactions_by_ip_for_useable_campaigns
conversions_by_ip_for_useable_campaigns (odd_browser_and_os_analysis): conversions_by_ip_for_useable_campaigns
geo_pid_facts (odd_browser_and_os_analysis): geo_pid_facts
ip_2_cluster (odd_browser_and_os_analysis): ip_2_cluster
ip_3_cluster (odd_browser_and_os_analysis): ip_3_cluster
ip_model_2_clusters (odd_browser_and_os_analysis): ip_model_2_clusters
ip_model_3_clusters (odd_browser_and_os_analysis): ip_model_3_clusters
other_browser_os_impressions (odd_browser_and_os_analysis | read by default (8 tiles)): other_browser_os_impressions
useable_campaign_ids (odd_browser_and_os_analysis): useable_campaign_ids

What this repo models

Impression traffic

Inferred

Entity: Ad impression
Grain: One row per impression (no key declared)
Metrics: other_browser_os_impressions.countother_browser_os_impressions.sum_of_bids

Raw impression rows with browser, OS, user agent, IP, geography, subid and bid amount. Everything on the dashboard reads this view.

Open other_browser_os_impressions

Publisher (PID) performance

Inferred

Entity: Publisher id parsed from subid
Grain: Grouped at query time; geo_pid_facts is one row per geo and pid
Metrics: other_browser_os_impressions.sum_of_bidsgeo_pid_facts.count

Publishers are not a table here. They are a string parsed out of subid, plus a pre-aggregated geo by PID table that no tile uses.

Open geo_pid_facts

IP behaviour clusters

Inferred

Entity: IP address
Grain: One row per IP per clustering run
Metrics: ip_2_cluster.countip_model_3_clusters.count

Two- and three-cluster labels per IP, with the features that likely produced them: distinct geos and PIDs, and relative domain, request and UA spread.

Open ip cluster views

Campaign clicks and conversions

Inferred

Entity: IP address, campaign
Grain: One row per IP in each count table
Metrics: click_interactions_by_ip_for_useable_campaigns.countconversions_by_ip_for_useable_campaigns.count

Stored click and conversion counts per IP for campaigns judged usable. The rule for usable is not in the repo.

Open campaign clicks and conversions

Metric index 10

Source-defined

Every measure in the repo, as declared. Tile counts show which ones a dashboard actually uses.

Show 10 measures

Measure	View	Type	SQL	Tiles
`count`	`click_interactions_by_ip_for_useable_campaigns`	count	`COUNT(*) of rows`	0
`count`	`conversions_by_ip_for_useable_campaigns`	count	`COUNT(*) of rows`	0
`count`	`geo_pid_facts`	count	`COUNT(*) of rows`	0
`count`	`ip_2_cluster`	count	`COUNT(*) of rows`	0
`count`	`ip_3_cluster`	count	`COUNT(*) of rows`	0
`count`	`ip_model_2_clusters`	count	`COUNT(*) of rows`	0
`count`	`ip_model_3_clusters`	count	`COUNT(*) of rows`	0
`count`	`other_browser_os_impressions`	count	`COUNT(*) of rows`	7
`sum_of_bids`	`other_browser_os_impressions`	sum	`${TABLE}.bid_amount`	2
`count`	`useable_campaign_ids`	count	`COUNT(*) of rows`	0

Source files 11

Every source file, classified. Combined files share one page; inventory-only files have a reason.

Views 9

click_interactions_by_ip_for_useable_campaigns.view.lookml Combined page. Declares click_interactions_by_ip_for_useable_campaigns Click and conversion counts by IP for useable campaigns2 warnings conversions_by_ip_for_useable_campaigns.view.lookml Combined page. Declares conversions_by_ip_for_useable_campaigns Click and conversion counts by IP for useable campaigns2 warnings geo_pid_facts.view.lookml Own page. Declares geo_pid_facts geo_pid_facts: a pre-aggregated geo by publisher table with an ambiguous count2 warnings ip_2_cluster.view.lookml Combined page. Declares ip_2_cluster IP cluster views: two- and three-cluster labels with behaviour ratios per IP3 warnings ip_3_cluster.view.lookml Combined page. Declares ip_3_cluster IP cluster views: two- and three-cluster labels with behaviour ratios per IP3 warnings ip_model_2_clusters.view.lookml Combined page. Declares ip_model_2_clusters IP cluster views: two- and three-cluster labels with behaviour ratios per IP3 warnings ip_model_3_clusters.view.lookml Combined page. Declares ip_model_3_clusters IP cluster views: two- and three-cluster labels with behaviour ratios per IP3 warnings other_browser_os_impressions.view.lookml Own page. Declares other_browser_os_impressions How this LookML view models browser, OS, publisher and impression traffic4 warnings useable_campaign_ids.view.lookml Combined page. Declares useable_campaign_ids Click and conversion counts by IP for useable campaigns2 warnings

Models 1

odd_browser_and_os_analysis.model.lookml Inventory only Nine bare explores with no joins or filters. The repo map shows its whole structure, so it has no page of its own.

Dashboards 1

default.dashboard.lookml Own page. Declares default Unusual UAs dashboard: how eight tiles slice one impressions explore3 warnings

Risks to review 4

Blocker: No joins anywhere in the model

Source-defined

All nine explores are bare. Impressions, clicks, conversions and IP clusters cannot be combined in Looker, so CTR, CVR and per-cluster revenue are not source-defined.

What goes wrong: Rates built by exporting two explores and dividing them mix populations, date ranges and grains without any warning.

Read the guide: CTR from separate tables

Source: odd_browser_and_os_analysis.model.lookml L5-13

advertising_marketing_small/odd_browser_and_os_analysis.model.lookmlL5-13 Show context

2- scoping: true3- include: '*.view.lookml'4- include: '*.dashboard.lookml'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 5-13

Caution: The dashboard country filter is wired to nothing

Source-defined

The country filter defaults to US, but no tile has a listen binding, so every tile ignores it.

What goes wrong: The dashboard shows a US filter while reporting global numbers.

Read the guide: Filters that change metric meaning

Source: default.dashboard.lookml L6-10

advertising_marketing_small/default.dashboard.lookmlL6-10 Show context

3  layout: tile4  tile_size: 1005  filters:6  - name: country7    type: suggest_filter8    explore: other_browser_os_impressions9    dimension: other_browser_os_impressions.country10    default_value: US11  elements:12  - name: browser_os_breakdown13    title: Browser-OS Breakdown

advertising-marketing-small, default.dashboard.lookml, lines 6-10

Caution: Publisher ids are parsed out of a string

Source-defined

pid is the second dash-separated segment of subid. Subids in another format become invented publishers rather than NULL.

Read the guide: Parsing ids from strings

Source: other_browser_os_impressions.view.lookml L43-44

advertising_marketing_small/other_browser_os_impressions.view.lookmlL43-44 Show context

40    sql: ${TABLE}.ua41  - dimension: url42    sql: ${TABLE}.url43  - dimension: pid44    sql: substring_index(substring_index(${subid}, '-', 2), '-', -1)45  - measure: count46    label: count47    type: count

advertising-marketing-small, other_browser_os_impressions.view.lookml, lines 43-44

Note: No view declares a primary key

Source-defined

None of the nine views has a primary_key. That is safe while nothing is joined, and becomes a fanout risk the moment a join is added.

Read the guide: Primary keys and counts

Guides built on this model

Guide Using user agents to flag non-browser ad impressions How a real ad-tech model flags impressions from clients that are not ordinary browsers, which publishers send them, and where its user-agent rule misleads. Acquisition Guide Filters that quietly change what a metric means Why two reports disagree on one metric: hidden default filters, tile filters and unwired dashboard filters, with a checklist and real examples. Best practicesAcquisitionRetention 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 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 Checking the primary key before you trust a count A count is only as clear as the key behind it. How to state and test a table's grain, with real tables whose keys repeat, merge rows or are missing. Best practicesRevenueRetention Guide Checking whether a few publishers earn most of the revenue How a real ad-tech model ranks publishers by bid revenue, why a ranking is not a concentration measure, and what its parsed publisher id hides. Revenue Guide Why parsing ids out of strings at query time is risky An id parsed from a string at query time turns malformed values into fake ids. A real publisher id parsed from a subid, and SQL that returns NULL instead. Best practicesAcquisition

Parser checks 13

Source-defined

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

Show all 13 checks

no-primary-key 9
- click_interactions_by_ip_for_useable_campaigns.view.lookml:1 click_interactions_by_ip_for_useable_campaigns declares no primary_key.
- conversions_by_ip_for_useable_campaigns.view.lookml:1 conversions_by_ip_for_useable_campaigns declares no primary_key.
- geo_pid_facts.view.lookml:1 geo_pid_facts declares no primary_key.
- 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.
- other_browser_os_impressions.view.lookml:1 other_browser_os_impressions declares no primary_key.
- useable_campaign_ids.view.lookml:1 useable_campaign_ids declares no primary_key.
name-collision 3
- click_interactions_by_ip_for_useable_campaigns.view.lookml:8 click_interactions_by_ip_for_useable_campaigns.count is declared as both dimension and measure.
- conversions_by_ip_for_useable_campaigns.view.lookml:8 conversions_by_ip_for_useable_campaigns.count is declared as both dimension and measure.
- geo_pid_facts.view.lookml:14 geo_pid_facts.count is declared as both dimension and measure.
filter-unwired 1
- default.dashboard.lookml:6 Dashboard filter "country" is not wired to any tile through listen.