DataBull Search
advertising_marketing_small/click_interactions_by_ip_for_useable_campaigns.view.lookml +2 5 dims3 measures 2 warnings Repo files

LookML view · advertising-marketing-small

Click and conversion counts by IP for useable campaigns

At a glance

Does
Two views hold a stored click_interactions_by_ip_for_useable_campaigns.count per IP, one for clicks and one for conversions. useable_campaign_ids.campaign_id lists campaign ids.Trust: Source-defined
Why
Prepared extracts for comparing click and conversion volume per IP on campaigns judged usable, probably to find IPs that click without converting.Trust: Inferred
Breaks
2 warnings. Top: No source-defined CTR or CVR
Grain
One row per IP in each count view, one row per campaign in the id list. The rule that defines usable is not in the repo.Trust: Missing
Source
advertising_marketing_small/click_interactions_by_ip_for_useable_campaigns.view.lookml, advertising_marketing_small/conversions_by_ip_for_useable_campaigns.view.lookml, advertising_marketing_small/useable_campaign_ids.view.lookml 34 lines
Source: click_interactions_by_ip_for_useable_campaigns.view.lookml L1-10
advertising_marketing_small/click_interactions_by_ip_for_useable_campaigns.view.lookmlL1-10
- view: click_interactions_by_ip_for_useable_campaigns  fields:  - dimension: count    type: int    sql: ${TABLE}.count  - dimension: ip    sql: ${TABLE}.ip  - measure: count    type: count    detail: detail*  sets:    detail: null

advertising-marketing-small, click_interactions_by_ip_for_useable_campaigns.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.

click_interactions_by_ip_for_useable_campaigns click_interactions_by_ip_for_useable_campaigns.view.lookml

Table: defaults to click_interactions_by_ip_for_useable_campaigns (no sql_table_name).

Dimensions 2
NameTypeSQLUsed byTags
countL3int${TABLE}.count-name reused
ipL6string${TABLE}.ip-

Measures 1
NameTypeSQLUsed byTags
countL8countCOUNT(*) of rows-name reused

conversions_by_ip_for_useable_campaigns conversions_by_ip_for_useable_campaigns.view.lookml

Table: defaults to conversions_by_ip_for_useable_campaigns (no sql_table_name).

Dimensions 2
NameTypeSQLUsed byTags
countL3int${TABLE}.count-name reused
ipL6string${TABLE}.ip-

Measures 1
NameTypeSQLUsed byTags
countL8countCOUNT(*) of rows-name reused

useable_campaign_ids useable_campaign_ids.view.lookml

Table: defaults to useable_campaign_ids (no sql_table_name).

Dimensions 1
NameTypeSQLUsed byTags
campaign_idL3int${TABLE}.campaign_id-

Measures 1
NameTypeSQLUsed byTags
countL6countCOUNT(*) 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 click_interactions_by_ip_for_useable_campaigns, conversions_by_ip_for_useable_campaigns, useable_campaign_ids
Relationships of click_interactions_by_ip_for_useable_campaigns, conversions_by_ip_for_useable_campaigns, useable_campaign_ids. Text version follows the diagram.These viewsUsed by: explores, views, dashboardsbase viewbase viewbase view click_interactions_by_ip_for_useable_campaigns explore in odd_browser_and_os_analysis conversions_by_ip_for_useable_campaigns explore in odd_browser_and_os_analysis useable_campaign_ids explore in odd_browser_and_os_analysis
  • This Block
Text version
  • click_interactions_by_ip_for_useable_campaigns is the base view of click_interactions_by_ip_for_useable_campaigns: base view
  • conversions_by_ip_for_useable_campaigns is the base view of conversions_by_ip_for_useable_campaigns: base view
  • useable_campaign_ids is the base view of useable_campaign_ids: base view

Worth noticing

Usable lives only in table names

Trust: Source-defined

The count tables were filtered to usable campaigns upstream. Nothing joins them to useable_campaign_ids.campaign_id, and neither count view has a campaign column.

Source: click_interactions_by_ip_for_useable_campaigns.view.lookml L1-12
advertising_marketing_small/click_interactions_by_ip_for_useable_campaigns.view.lookmlL1-12
- view: click_interactions_by_ip_for_useable_campaigns  fields:  - dimension: count    type: int    sql: ${TABLE}.count  - dimension: ip    sql: ${TABLE}.ip  - measure: count    type: count    detail: detail*  sets:    detail: null

advertising-marketing-small, click_interactions_by_ip_for_useable_campaigns.view.lookml, lines 1-12

The two count views are byte-for-byte twins

Trust: Source-defined

Apart from the view name, the click and conversion files are identical: a stored count, an ip, and a row-count measure with an empty drill set.

Source: conversions_by_ip_for_useable_campaigns.view.lookml L1-12
advertising_marketing_small/conversions_by_ip_for_useable_campaigns.view.lookmlL1-12
- view: conversions_by_ip_for_useable_campaigns  fields:  - dimension: count    type: int    sql: ${TABLE}.count  - dimension: ip    sql: ${TABLE}.ip  - measure: count    type: count    detail: detail*  sets:    detail: null

advertising-marketing-small, conversions_by_ip_for_useable_campaigns.view.lookml, lines 1-12

What can change the answer 2

Blocker: No source-defined CTR or CVR

Trust: Source-defined

Clicks, conversions and impressions are separate explores with no join. Dividing their counts mixes populations and grains.

What goes wrong: A per-IP conversion rate built from these views can exceed 100%, or divide conversions by clicks from other campaigns or dates.

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

Caution: The count measure counts IPs, not clicks

Trust: Source-defined

The measure count counts rows, which are IPs. The stored click total is the dimension count, and no measure sums it.

What goes wrong: Total clicks in the explore shows the number of distinct IPs that clicked.

Read the guide: Primary keys and counts

Source: click_interactions_by_ip_for_useable_campaigns.view.lookml L3-10
advertising_marketing_small/click_interactions_by_ip_for_useable_campaigns.view.lookmlL3-10
- view: click_interactions_by_ip_for_useable_campaigns  fields:  - dimension: count    type: int    sql: ${TABLE}.count  - dimension: ip    sql: ${TABLE}.ip  - measure: count    type: count    detail: detail*  sets:    detail: null

advertising-marketing-small, click_interactions_by_ip_for_useable_campaigns.view.lookml, lines 3-10

Parser checks 5

Trust: Source-defined

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

Show all 5 checks
  • no-primary-key 3
    • 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.
    • useable_campaign_ids.view.lookml:1 useable_campaign_ids declares no primary_key.
  • name-collision 2
    • 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.

Related

Guides built on this file

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 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

Related source files

Source file ip cluster views Four views over clustering output. ip_2_cluster.class and ip_3_cluster.class label each IP. The model views... 4 views3 warnings 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 model

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