DataBull Search
3 real examples

Best practice Acquisition

Calculating CTR from separate impression and click tables

Can you divide clicks by impressions from different tables?

The rule

Trust: Inferred

Only when both sides cover the same population at the same grain. Count impressions and clicks over the same campaigns, dates and filters, join them on a shared key, and compute the rate in one query. In this project the tables share only an IP and cover different campaign sets, so no impression-level CTR exists; the best it supports is a per-IP rate over one population.

Why it matters

Trust: Inferred

A rate needs a numerator and a denominator from the same population at the same grain. When impressions, clicks and conversions live in separate explores with no join, the repo does not define CTR or CVR at all, however natural the division looks.

If impressions cover all campaigns and clicks cover only usable ones, clicks divided by impressions mixes two populations. The rate moves when a filter changes, not when behaviour does, and it can exceed 100%.

Two IPs, two extracts, one naive CTR
IPImpressions (all campaigns)Clicks (usable campaigns)Naive CTR
203.0.113.71,200907.5%
198.51.100.44055137.5%

Trust: Suggested Illustrative numbers, not from the source.

How to check your own model

Trust: Suggested
  1. List the table each count comes from. If they differ, find the key that connects them, or stop.
  2. Confirm both sides share a key and a grain: one row per impression, click or IP.
  3. Check that the same filters apply to both sides: date range, campaign set, country.
  4. Compute numerator and denominator in one query, so they cannot drift apart.

A safer shape

Align population and grain, then divide

Trust: Suggested

Restrict impressions to the same usable campaigns the click table covers, bring both to one row per IP, and join. That is closer to like with like, but still only per IP: without an impression id a click cannot be tied to its impression, and the impressions table may itself be a narrower extract (its name suggests other browsers and operating systems only).

Show the suggested SQL: CTR per IP over one population
Suggested, not from source
WITH impressions AS (  SELECT ip, COUNT(*) AS impressions  FROM other_browser_os_impressions  WHERE campaign_id IN (SELECT campaign_id FROM useable_campaign_ids)  GROUP BY ip),clicks AS (  SELECT ip, SUM(`count`) AS clicks  FROM click_interactions_by_ip_for_useable_campaigns  GROUP BY ip)SELECT  i.ip,  i.impressions,  COALESCE(c.clicks, 0) AS clicks,  COALESCE(c.clicks, 0) / NULLIF(i.impressions, 0) AS ctrFROM impressions AS iLEFT JOIN clicks AS c ON c.ip = i.ip

The same fix in LookML

Trust: Suggested

In LookML the same rule means one explore: join clicks to impressions on a shared key and define the rate as a measure there. The impression_id key and the relationship are assumptions; this project has neither.

Show the suggested LookML: impressions.explore.lookml
Suggested, not from source
- explore: impressions  joins:  - join: clicks    sql_on: ${impressions.impression_id} = ${clicks.impression_id}    relationship: one_to_many# in the impressions view- measure: click_count  type: count_distinct  sql: ${clicks.click_id}- measure: ctr  type: number  sql: 1.0 * ${click_count} / NULLIF(${count}, 0)

Where it shows up in real projects 3

other_browser_os_impressions: Nine explores, zero joins

Trust: Source-defined

The model declares every view as its own explore. There is no path from an impression to a click or a conversion inside Looker.

Source: odd_browser_and_os_analysis.model.lookml L1-13
advertising_marketing_small/odd_browser_and_os_analysis.model.lookmlL1-13
- connection: fraud_research- 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 1-13

other_browser_os_impressions: Impressions carry no click signal

Trust: Source-defined

The impressions view has campaign, creative and IP, but no click flag or click id. Its only count is a row count of impressions.

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

campaign clicks and conversions: Clicks and conversions are separate IP tables

Trust: Source-defined

Both views hold a stored count per IP, filtered upstream to usable campaigns. Neither has a campaign or date column to align with impressions.

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

Other places it occurs

Warning ip cluster views: Labels never reach the impressions advertising-marketing-small Caution