DataBull Search
3 pitfalls

Acquisition and traffic quality

Using user agents to flag non-browser ad impressions

Which impressions come from clients that are not ordinary browsers, which publishers and IPs send them, and do they still earn bids?

Short answer

Trust: Inferred

Classify traffic once and measure everything against that one rule. This project flags user agents that do not start with Mozilla/5.0, but only one dashboard tile applies the rule, so the other unusual-traffic tiles count all traffic. The prefix also misses modern bots, which send Mozilla/5.0 like everyone else.

Decision it supports: Which publishers or IPs to review, block or stop paying for.

What to measure

Impressions

Trust: Source-defined

Impression rows.

Defined as other_browser_os_impressions.count

COUNT(*)Trust: Translated

Show the SQL: Impressions
MySQL SQLTrust: Translated
COUNT(*)

Translated to MySQL SQL from other_browser_os_impressions.view.lookml L45-48. Not run against a warehouse.

Show the original LookML: other_browser_os_impressions.view.lookml L45-48
advertising_marketing_small/other_browser_os_impressions.view.lookmlL45-48
    sql: ${TABLE}.url  - dimension: pid    sql: substring_index(substring_index(${subid}, '-', 2), '-', -1)  - measure: count    label: count    type: count    detail: detail*  - measure: sum_of_bids    label: bid sum    type: sum

advertising-marketing-small, other_browser_os_impressions.view.lookml, lines 45-48

Bid sum

Trust: Source-defined

Summed bid amount, used as revenue on the dashboard.

Defined as other_browser_os_impressions.sum_of_bids

SUM(other_browser_os_impressions.bid_amount)Trust: Translated

Show the SQL: Bid sum
MySQL SQLTrust: Translated
SUM(other_browser_os_impressions.bid_amount)

Translated to MySQL SQL from other_browser_os_impressions.view.lookml L49-54. Not run against a warehouse.

Show the original LookML: other_browser_os_impressions.view.lookml L49-54
advertising_marketing_small/other_browser_os_impressions.view.lookmlL49-54
    label: count    type: count    detail: detail*  - measure: sum_of_bids    label: bid sum    type: sum    sql: ${TABLE}.bid_amount    detail: drill_set_2*    decimals: 4  sets:    detail:    - pid

advertising-marketing-small, other_browser_os_impressions.view.lookml, lines 49-54

Unusual user-agent share

Trust: Suggested

Unusual impressions over all impressions. It needs the rule as a filtered measure; today the rule exists only as a tile filter.

Revenue at risk

Trust: Inferred

Bid sum on unusual user agents. A tile ranks user agents by bid sum, but no measure isolates the unusual part.

The query

Unusual user agents

Trust: Translated

The dashboard's Unusual UAs table: impressions per user agent that does not start with Mozilla/5.0, rarest first.

Show the SQL: unusual user agents
MySQL SQLTrust: Translated
SELECT  other_browser_os_impressions.ua AS ua,  COUNT(*) AS countFROM other_browser_os_impressionsWHERE (other_browser_os_impressions.ua NOT LIKE 'Mozilla/5.0%' OR other_browser_os_impressions.ua IS NULL)GROUP BY 1ORDER BY countLIMIT 50
  • other_browser_os_impressions declares no sql_table_name, so it reads a table named other_browser_os_impressions.

Translated to MySQL SQL from default.dashboard.lookml L35-47, odd_browser_and_os_analysis.model.lookml L12 and other_browser_os_impressions.view.lookml L39-40, L45-48. Not run against a warehouse.

Show the original LookML: default.dashboard.lookml L35-47
advertising_marketing_small/default.dashboard.lookmlL35-47
    width: 10    y_axis_labels:    - Count of Impressions  - name: unusual_uas_table    title: Unusual UAs    type: table    explore: other_browser_os_impressions    dimensions:    - other_browser_os_impressions.ua    measures:    - other_browser_os_impressions.count    sorts:    - other_browser_os_impressions.count    filters:      other_browser_os_impressions.ua: -Mozilla/5.0%    limit: 50  - name: revenue_and_impressions_top_10_pids    title: Revenue and Impressions for Top 10 PIDs    type: looker_column

advertising-marketing-small, default.dashboard.lookml, lines 35-47

The data behind it

Trust: Inferred
Entities
impression, user agent, publisher (pid), IP
Grain
One row per impression in other_browser_os_impressions.
Time
No time dimension group. time is a plain string.
Key fields
other_browser_os_impressions.uaother_browser_os_impressions.browserother_browser_os_impressions.osother_browser_os_impressions.ipother_browser_os_impressions.pid

What can change the answer 3

Caution: The UA rule lives in one tile

Trust: Source-defined

Only the Unusual UAs table applies the Mozilla rule. Other tiles that mention unusual UAs count all traffic.

What goes wrong: Top IPs and PIDs for unusual traffic are really the top IPs and PIDs across the whole impressions table, whatever population that table holds.

Read the guide: Filters that change metric meaning

Source: default.dashboard.lookml L69-82
advertising_marketing_small/default.dashboard.lookmlL69-82
    y_axis_labels:    - ImpressionCount    - Bid Sum ($)  - name: unusual_requests_by_ip    title: Top IPs That Send Unusual UAs    type: table    filters:      other_browser_os_impressions.ip: -EMPTY    explore: other_browser_os_impressions    dimensions:    - other_browser_os_impressions.ip    - other_browser_os_impressions.country    measures:    - other_browser_os_impressions.count    sorts:    - other_browser_os_impressions.count desc    limit: 25  - name: most_profitable_uas    title: Top 10 Most Profitable UAs    type: looker_column

advertising-marketing-small, default.dashboard.lookml, lines 69-82

Caution: Mozilla/5.0 is a weak bot signal

Trust: Inferred

Most crawlers and headless browsers also send a Mozilla/5.0 prefix. The rule finds old and non-browser clients, not bots in general.

Show the SQL: ua
MySQL SQLTrust: Translated
other_browser_os_impressions.ua

Translated to MySQL SQL from other_browser_os_impressions.view.lookml L39-40. Not run against a warehouse.

Show the original LookML: other_browser_os_impressions.view.lookml L39-40
advertising_marketing_small/other_browser_os_impressions.view.lookmlL39-40
    sql: ${TABLE}.subid  - dimension: time    sql: ${TABLE}.time  - dimension: ua    sql: ${TABLE}.ua  - dimension: url    sql: ${TABLE}.url  - dimension: pid

advertising-marketing-small, other_browser_os_impressions.view.lookml, lines 39-40

Note: The country filter is not applied

Trust: Source-defined

The dashboard shows country = US by default, but no tile listens to it.

Read the guide: Filters that change metric meaning

Source: default.dashboard.lookml L6-10
advertising_marketing_small/default.dashboard.lookmlL6-10
  layout: tile  tile_size: 100  filters:  - name: country    type: suggest_filter    explore: other_browser_os_impressions    dimension: other_browser_os_impressions.country    default_value: US  elements:  - name: browser_os_breakdown    title: Browser-OS Breakdown

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

How the real implementation does it

other_browser_os_impressions

Trust: Source-defined

Holds the raw signals per impression. These lines show the user agent and the publisher id parsed from subid; browser, OS, IP and referrer sit higher in the same file.

Source: other_browser_os_impressions.view.lookml L37-44
advertising_marketing_small/other_browser_os_impressions.view.lookmlL37-44
    sql: ${TABLE}.referrer_url  - dimension: subid    sql: ${TABLE}.subid  - dimension: time    sql: ${TABLE}.time  - dimension: ua    sql: ${TABLE}.ua  - dimension: url    sql: ${TABLE}.url  - dimension: pid    sql: substring_index(substring_index(${subid}, '-', 2), '-', -1)  - measure: count    label: count    type: count

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

default (Unusual UAs)

Trust: Source-defined

Defines unusual with a tile filter, ua: -Mozilla/5.0%, and sorts ascending so the rarest user agents come first.

Show the SQL: the unusual rule as a filter
MySQL SQLTrust: Translated
SELECT  other_browser_os_impressions.ua AS ua,  COUNT(*) AS countFROM other_browser_os_impressionsWHERE (other_browser_os_impressions.ua NOT LIKE 'Mozilla/5.0%' OR other_browser_os_impressions.ua IS NULL)GROUP BY 1ORDER BY countLIMIT 50
  • other_browser_os_impressions declares no sql_table_name, so it reads a table named other_browser_os_impressions.

Translated to MySQL SQL from default.dashboard.lookml L35-47, odd_browser_and_os_analysis.model.lookml L12 and other_browser_os_impressions.view.lookml L39-40, L45-48. Not run against a warehouse.

Show the original LookML: default.dashboard.lookml L35-47
advertising_marketing_small/default.dashboard.lookmlL35-47
    width: 10    y_axis_labels:    - Count of Impressions  - name: unusual_uas_table    title: Unusual UAs    type: table    explore: other_browser_os_impressions    dimensions:    - other_browser_os_impressions.ua    measures:    - other_browser_os_impressions.count    sorts:    - other_browser_os_impressions.count    filters:      other_browser_os_impressions.ua: -Mozilla/5.0%    limit: 50  - name: revenue_and_impressions_top_10_pids    title: Revenue and Impressions for Top 10 PIDs    type: looker_column

advertising-marketing-small, default.dashboard.lookml, lines 35-47

default (Unusual UAs)

Trust: Source-defined

Ranks all user agents by bid sum to show which clients cost the most. This tile does not apply the unusual rule.

Show the SQL: user agents by bid sum
MySQL SQLTrust: Translated
SELECT  other_browser_os_impressions.ua AS ua,  SUM(other_browser_os_impressions.bid_amount) AS sum_of_bidsFROM other_browser_os_impressionsWHERE (other_browser_os_impressions.domain IS NOT NULL AND other_browser_os_impressions.domain <> '')GROUP BY 1ORDER BY sum_of_bids DESCLIMIT 10
  • other_browser_os_impressions declares no sql_table_name, so it reads a table named other_browser_os_impressions.

Translated to MySQL SQL from default.dashboard.lookml L83-99, odd_browser_and_os_analysis.model.lookml L12 and other_browser_os_impressions.view.lookml L22-23, L39-40, L49-54. Not run against a warehouse.

Show the original LookML: default.dashboard.lookml L83-95
advertising_marketing_small/default.dashboard.lookmlL83-95
    sorts:    - other_browser_os_impressions.count desc    limit: 25  - name: most_profitable_uas    title: Top 10 Most Profitable UAs    type: looker_column    explore: other_browser_os_impressions    dimensions:    - other_browser_os_impressions.ua    measures:    - other_browser_os_impressions.sum_of_bids    filters:      other_browser_os_impressions.domain: -EMPTY    sorts:    - other_browser_os_impressions.sum_of_bids desc    limit: 10    show_null_labels: false    stacking: ''    y_axis_labels:

advertising-marketing-small, default.dashboard.lookml, lines 83-95

Other ways to measure it

Make unusual one shared definition

Trust: Suggested

Classify user agents once and compute the share from that flag, so every report uses the same rule. A missing user agent counts as unusual, as it does in the tile filter.

Show the suggested SQL: unusual user-agent share
Suggested, not from source
SELECT  substring_index(substring_index(subid, '-', 2), '-', -1) AS pid,  COUNT(*) AS impressions,  SUM(CASE WHEN COALESCE(ua, '') NOT LIKE 'Mozilla/5.0%' THEN 1 ELSE 0 END) AS unusual_impressions,  SUM(CASE WHEN COALESCE(ua, '') NOT LIKE 'Mozilla/5.0%' THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0) AS unusual_share,  SUM(CASE WHEN COALESCE(ua, '') NOT LIKE 'Mozilla/5.0%' THEN bid_amount ELSE 0 END) AS bids_on_unusualFROM other_browser_os_impressionsGROUP BY 1

Classify upstream with a maintained list

Trust: Suggested

Match UAs against a maintained bot and crawler list in the pipeline and store a classification column, instead of a prefix rule in Looker.