DataBull Search
3 pitfalls

Revenue

Checking whether a few publishers earn most of the revenue

How concentrated is bid revenue across publishers, and do the top publishers also send most of the impressions?

Short answer

Trust: Inferred

Measure each source's share of the total, not just its rank. This project ranks publishers by bid sum in a dashboard tile, so concentration has to be read off a bar chart, and the publisher id is parsed from a string at query time, so malformed ids become publishers of their own.

Decision it supports: Which publishers to audit first.

What to measure

Bid revenue

Trust: Source-defined

Summed bid amount, per publisher when grouped by pid.

Defined as other_browser_os_impressions.sum_of_bids

SUM(other_browser_os_impressions.bid_amount)Trust: Translated

Show the SQL: Bid revenue
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

Impressions

Trust: Source-defined

Impression rows per publisher.

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

Publisher id

Trust: Source-defined

Parsed out of subid at query time.

Defined as other_browser_os_impressions.pid

substring_index(substring_index(other_browser_os_impressions.subid, '-', 2), '-', -1)Trust: Translated

Show the SQL: Publisher id
MySQL SQLTrust: Translated
substring_index(substring_index(other_browser_os_impressions.subid, '-', 2), '-', -1)

Translated to MySQL SQL from other_browser_os_impressions.view.lookml L35-36, L43-44. Not run against a warehouse.

Show the original LookML: other_browser_os_impressions.view.lookml L43-44
advertising_marketing_small/other_browser_os_impressions.view.lookmlL43-44
    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 43-44

Top-N revenue share

Trust: Suggested

Share of bid sum from the top 10 publishers. Needs a window sum or a derived table; the tile only ranks.

Revenue per impression

Trust: Inferred

Bid sum over impressions per publisher. Both come from one table, so it is safe to compute, but the source does not define it.

The query

Top publishers by bid revenue

Trust: Translated

The dashboard's ranking: bid sum and impressions per publisher, empty domains left out. Despite its title it returns 25 rows.

Show the SQL: top publishers by bid revenue
MySQL SQLTrust: Translated
SELECT  substring_index(substring_index(other_browser_os_impressions.subid, '-', 2), '-', -1) AS pid,  COUNT(*) AS count,  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 25
  • 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 L48-68, odd_browser_and_os_analysis.model.lookml L12 and other_browser_os_impressions.view.lookml L22-23, L35-36, L43-54. Not run against a warehouse.

Show the original LookML: default.dashboard.lookml L48-68
advertising_marketing_small/default.dashboard.lookmlL48-68
    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    explore: other_browser_os_impressions    dimensions:    - other_browser_os_impressions.pid    measures:    - other_browser_os_impressions.count    - other_browser_os_impressions.sum_of_bids    filters:      other_browser_os_impressions.domain: -EMPTY    sorts:    - other_browser_os_impressions.sum_of_bids desc    limit: 25    show_null_labels: false    column_group_spacing_ratio: 0.25    stacking: ''    width: 10    y_axis_labels:    - ImpressionCount    - Bid Sum ($)  - name: unusual_requests_by_ip    title: Top IPs That Send Unusual UAs    type: table

advertising-marketing-small, default.dashboard.lookml, lines 48-68

The data behind it

Trust: Inferred
Entities
publisher (pid), impression, geo
Grain
Impressions, grouped by the parsed pid at query time. geo_pid_facts is pre-aggregated per geo and pid.
Time
Not available as a dimension group.
Key fields
other_browser_os_impressions.pidother_browser_os_impressions.subidother_browser_os_impressions.bid_amountgeo_pid_facts.geogeo_pid_facts.ip_count

What can change the answer 3

Caution: Malformed subids become publishers

Trust: Inferred

The parse returns a value for any non-NULL subid, so a subid in another format becomes its own publisher in the ranking.

Read the guide: Parsing ids from strings

Show the SQL: pid
MySQL SQLTrust: Translated
substring_index(substring_index(other_browser_os_impressions.subid, '-', 2), '-', -1)

Translated to MySQL SQL from other_browser_os_impressions.view.lookml L35-36, L43-44. Not run against a warehouse.

Show the original LookML: other_browser_os_impressions.view.lookml L43-44
advertising_marketing_small/other_browser_os_impressions.view.lookmlL43-44
    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 43-44

Caution: The top 10 tile returns 25 rows

Trust: Source-defined

"Revenue and Impressions for Top 10 PIDs" has limit: 25 and drops empty domains, while the top 10 by volume tile does not.

Read the guide: Filters that change metric meaning

Source: default.dashboard.lookml L48-61
advertising_marketing_small/default.dashboard.lookmlL48-61
    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    explore: other_browser_os_impressions    dimensions:    - other_browser_os_impressions.pid    measures:    - other_browser_os_impressions.count    - other_browser_os_impressions.sum_of_bids    filters:      other_browser_os_impressions.domain: -EMPTY    sorts:    - other_browser_os_impressions.sum_of_bids desc    limit: 25    show_null_labels: false    column_group_spacing_ratio: 0.25    stacking: ''

advertising-marketing-small, default.dashboard.lookml, lines 48-61

Note: Two pid types

Trust: Source-defined

geo_pid_facts.pid is an integer, the parsed pid is a string.

Read the guide: Parsing ids from strings

Show the SQL: pid
MySQL SQLTrust: Translated
geo_pid_facts.pid

Translated to MySQL SQL from geo_pid_facts.view.lookml L11-13. Not run against a warehouse.

Show the original LookML: geo_pid_facts.view.lookml L11-13
advertising_marketing_small/geo_pid_facts.view.lookmlL11-13
  - dimension: ip_count    type: int    sql: ${TABLE}.ip_count  - dimension: pid    type: int    sql: ${TABLE}.pid  - measure: count    type: count    detail: detail*

advertising-marketing-small, geo_pid_facts.view.lookml, lines 11-13

How the real implementation does it

other_browser_os_impressions

Trust: Source-defined

Parses the publisher id out of subid at query time.

Show the SQL: publisher id
MySQL SQLTrust: Translated
substring_index(substring_index(other_browser_os_impressions.subid, '-', 2), '-', -1)

Translated to MySQL SQL from other_browser_os_impressions.view.lookml L35-36, L43-44. Not run against a warehouse.

Show the original LookML: other_browser_os_impressions.view.lookml L43-44
advertising_marketing_small/other_browser_os_impressions.view.lookmlL43-44
    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 43-44

default (Unusual UAs)

Trust: Source-defined

Ranks PIDs by bid sum next to impression count, excluding empty domains.

Source: default.dashboard.lookml L48-61
advertising_marketing_small/default.dashboard.lookmlL48-61
    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    explore: other_browser_os_impressions    dimensions:    - other_browser_os_impressions.pid    measures:    - other_browser_os_impressions.count    - other_browser_os_impressions.sum_of_bids    filters:      other_browser_os_impressions.domain: -EMPTY    sorts:    - other_browser_os_impressions.sum_of_bids desc    limit: 25    show_null_labels: false    column_group_spacing_ratio: 0.25    stacking: ''

advertising-marketing-small, default.dashboard.lookml, lines 48-61

geo_pid_facts

Trust: Source-defined

A pre-aggregated geo by PID table with distinct IP counts, unused by any tile.

Source: geo_pid_facts.view.lookml L1-18
advertising_marketing_small/geo_pid_facts.view.lookmlL1-18
- view: geo_pid_facts  fields:  - dimension: count    type: int    sql: ${TABLE}.count  - dimension: geo    sql: ${TABLE}.geo  - dimension: ip_count    type: int    sql: ${TABLE}.ip_count  - dimension: pid    type: int    sql: ${TABLE}.pid  - measure: count    type: count    detail: detail*  sets:    detail: null

advertising-marketing-small, geo_pid_facts.view.lookml, lines 1-18

Other ways to measure it

Compute the share in SQL

Trust: Suggested

Divide each publisher's bid sum by the total, so concentration is a number, not a reading of a bar chart.

Show the suggested SQL: publisher revenue share
Suggested, not from source
SELECT  substring_index(substring_index(subid, '-', 2), '-', -1) AS pid,  SUM(bid_amount) AS bid_sum,  SUM(bid_amount) / (SELECT SUM(bid_amount) FROM other_browser_os_impressions) AS bid_shareFROM other_browser_os_impressionsGROUP BY 1ORDER BY bid_sum DESC

Parse once and store it

Trust: Suggested

Write the parsed pid to the impressions table upstream, typed like geo_pid_facts.pid, so both views group by the same value.