Deduplicating Joins Across Citation, Referral, and CRM Data

Website owners comparing citation reports with referrals and customer relationship management (CRM) outcomes need to keep the three record types separate. A citation is a business listing or mention. A referral shows that a visitor arrived from another site. A conversion is a defined business outcome, such as an accepted lead, a closed deal, or a paid order.

To count conversions reliably, choose one stable CRM or order ID for each outcome and count it once. Keep citation listings, web sessions, and CRM outcomes in separate tables, with each table holding the details for that kind of record. Then use a separate attribution table to link outcomes to possible sources. A directory domain in a web referrer can suggest a source, but it does not identify a specific listing or prove that the listing caused a conversion.

The table designs and SQL below are examples. Map them to the actual column names in your citation export, GA4 data, and CRM records.

Keep listings, sessions, and conversions at separate grains

A table’s grain is what one row represents. A citation export may show the same listing in several report snapshots. GA4 exports event rows, so one session can have many rows. A CRM export may show an outcome in several rows as its status changes. Joining these raw rows multiplies records and inflates counts.

Use a separate table for each purpose. Apply a consistent rule to standardize listing URLs, so the same listing has the same URL key:

A bridge table links records without copying a conversion into every matching row. It lets you keep several touchpoints, or recorded interactions, for attribution while counting the conversion only once for its CRM outcome.

Decide what counts as a conversion before choosing its ID. Count a closed-won deal by deal ID, an order by order ID, and a submitted lead by a stable submission or lead ID. A contact ID identifies a person’s CRM record, but it does not necessarily identify a deal, order, or submission. Keep different outcome types separate instead of combining them into one count.

BrightLocal documents how to download Citation Tracker reports as CSV files and describes citation URLs and listing status. Its available documentation does not specify one fixed set of columns or a stable row ID for every export. Check each downloaded file, map its actual column names into your staging table, and keep the original URL and report run. BrightLocal’s Citation Tracker report instructions explain the report and CSV download.

Choose join keys by strength

Use an exact key when one is available. A shared order ID, form submission ID, or CRM conversion ID gives the clearest link between records. If the website captures analytics session IDs when someone submits a form and stores them with the CRM outcome, those IDs link the outcome to a web session.

A referral hostname provides weaker evidence. A maintained mapping table, or crosswalk, can connect a referrer host to a citation provider. But the host alone does not show which listing, business location, or citation URL brought the visitor. Treat the result as a link to the provider, not as a definite link between a citation and a conversion.

Decide separately whether to use email addresses, phone numbers, or names to match records to the same person. These details can be missing, shared, changed, or duplicated, and none automatically identifies a conversion. If you use them to link CRM and analytics records, document how you match them and follow the privacy and consent policies that apply to the personal data.

Deduplicate citation snapshots before counting listings

Treat each repeated observation as a snapshot of the same listing. Use the location, provider, and standardized listing URL together as a possible listing key. Keep the report run or observation date in the snapshot table so you can track changes without counting each export as a separate citation.

Set URL standardization rules for each provider. Keep the original URL and decide which URL differences still point to the same listing. Lowercasing a host generally has a different effect from lowercasing a path, and query parameters can sometimes distinguish pages. Do not remove parameters or combine host aliases until you have checked that the resulting URLs point to the same listing.

This query assumes your staging step has populated normalized_listing_url according to that policy:

WITH ranked AS (
  SELECT
    run_id,
    location_id,
    provider_id,
    citation_url,
    normalized_listing_url,
    status,
    as_of_date,
    ROW_NUMBER() OVER (
      PARTITION BY location_id, provider_id, normalized_listing_url
      ORDER BY as_of_date DESC, run_id DESC
    ) AS row_rank
  FROM <code>project.dataset.stg_citation_export
  WHERE normalized_listing_url IS NOT NULL
)
SELECT
  location_id,
  provider_id,
  normalized_listing_url,
  citation_url AS latest_source_url,
  status AS latest_status,
  as_of_date AS latest_observed_at
FROM ranked
WHERE row_rank = 1;

This returns the latest observed state for each listing key. Keep the underlying snapshots when you need to report listing changes over time.

Reduce GA4 events to sessions

GA4’s BigQuery export stores one row per event, and each row can include repeated event parameters. Extract each parameter with a subquery that returns one value. The subquery keeps the event rows from multiplying when you expand the parameter array. Then select one landing-page event per session. Google’s BigQuery export schema describes the exported fields, including user_pseudo_id and repeated event_params.

The following BigQuery example uses illustrative field names and query parameters for the date range:

WITH page_views AS (
  SELECT
    stream_id,
    user_pseudo_id,
    event_timestamp,

    (SELECT value.int_value
     FROM UNNEST(event_params)
     WHERE key = 'ga_session_id') AS ga_session_id,

    (SELECT value.string_value
     FROM UNNEST(event_params)
     WHERE key = 'page_referrer') AS page_referrer,

    (SELECT value.string_value
     FROM UNNEST(event_params)
     WHERE key = 'page_location') AS page_location

  FROM <code>project.analytics_123456.events_*
  WHERE _TABLE_SUFFIX BETWEEN @start_date AND @end_date
    AND event_name = 'page_view'
),
ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY stream_id, user_pseudo_id, ga_session_id
      ORDER BY event_timestamp
    ) AS page_rank
  FROM page_views
  WHERE user_pseudo_id IS NOT NULL
    AND ga_session_id IS NOT NULL
)
SELECT
  stream_id,
  user_pseudo_id,
  ga_session_id,
  TIMESTAMP_MICROS(event_timestamp) AS landing_event_at,
  page_referrer,
  NET.HOST(page_referrer) AS referrer_host,
  page_location AS landing_url
FROM ranked
WHERE page_rank = 1;

The query returns referrer evidence from the first recorded page view in each session, but it does not reproduce GA4’s session-source reports, which apply GA4’s own rules for assigning traffic sources and defining report scope. Document whether your warehouse uses raw referrer evidence or a traffic-source field processed by GA4. Google describes its rules in the campaign and traffic-source documentation.

Use stream_id, user_pseudo_id, and ga_session_id to identify a session only when all three fields are present and captured consistently. Otherwise, leave rows unmatched rather than guess which session they belong to.

Map referral hosts to providers without multiplying sessions

Maintain a mapping table from standardized hostnames to provider IDs. Do not guess provider names from the last two parts of a domain. Providers can use subdomains, aliases, or shared parent domains.

WITH provider_domains AS (
  SELECT DISTINCT
    provider_id,
    normalized_host
  FROM <code>project.dataset.dim_provider_domain
),
candidate_referrals AS (
  SELECT
    s.stream_id,
    s.user_pseudo_id,
    s.ga_session_id,
    s.landing_event_at,
    p.provider_id,
    'referrer_host_match' AS match_method
  FROM project.dataset.fct_web_session AS s
  JOIN provider_domains AS p
    ON LOWER(s.referrer_host) = p.normalized_host
  WHERE s.referrer_host IS NOT NULL
)
SELECT *
FROM candidate_referrals;

This links a session to a provider when the referrer host matches the mapping table. It does not show that a particular citation listing brought the visitor. If the provider has several listings, keep the link at the provider level unless the site captures a stronger identifier, such as a controlled referral ID.

Deduplicate CRM outcomes, then attach touchpoints

A CRM export can contain several versions of an outcome as its status changes. First choose the version that matches your reporting rule. The example uses the latest CRM snapshot and counts outcomes whose current status is qualified. If you count a change in stage instead, use the record of that status change as the conversion, rather than the latest snapshot.

WITH ranked_versions AS (
  SELECT
    conversion_id,
    crm_object_id,
    conversion_type,
    occurred_at,
    updated_at,
    status,
    amount,
    captured_stream_id,
    captured_user_pseudo_id,
    captured_ga_session_id,
    ROW_NUMBER() OVER (
      PARTITION BY conversion_id
      ORDER BY updated_at DESC
    ) AS version_rank
  FROM <code>project.dataset.stg_crm_conversion
  WHERE NULLIF(TRIM(conversion_id), '') IS NOT NULL
),
conversions AS (
  SELECT *
  FROM ranked_versions
  WHERE version_rank = 1
    AND status = 'qualified'
),
touch_candidates AS (
  SELECT
    c.conversion_id,
    c.crm_object_id,
    c.conversion_type,
    c.occurred_at,
    c.amount,
    r.provider_id,
    r.match_method,
    r.landing_event_at,
    ROW_NUMBER() OVER (
      PARTITION BY c.conversion_id
      ORDER BY r.landing_event_at DESC, r.provider_id
    ) AS touch_rank
  FROM conversions AS c
  LEFT JOIN project.dataset.bridge_citation_referral_session AS r
    ON c.captured_stream_id = r.stream_id
   AND c.captured_user_pseudo_id = r.user_pseudo_id
   AND c.captured_ga_session_id = r.ga_session_id
   AND r.landing_event_at <= c.occurred_at
)
SELECT
  provider_id,
  COUNT(DISTINCT conversion_id) AS conversions,
  SUM(amount) AS attributed_amount
FROM touch_candidates
WHERE touch_rank = 1
GROUP BY provider_id;

The example selects the most recent eligible touchpoint before the CRM outcome. This is an attribution choice for the warehouse, not a GA4 rule. The LEFT JOIN keeps qualifying outcomes even when no session matches, but those outcomes have no provider attribution. In production, set an explicit lookback window, the maximum time before an outcome when a touchpoint can receive credit, and report outcomes outside that window.

For multi-touch reporting, keep each eligible touchpoint in the bridge and assign a share of credit to each conversion-touchpoint pair. Make sure the shares for each conversion add up to one before summing credited conversions:

SELECT
  provider_id,
  SUM(credit_weight) AS credited_conversions
FROM <code>project.dataset.bridge_conversion_touchpoint
GROUP BY provider_id;

A multi-touch report can divide credit for one conversion among several providers. The credit totals for each provider therefore do not show exclusive conversion counts. Keep the total CRM conversion count separate.

Check totals before publishing attribution

Compare the total number of CRM outcomes with the number that have captured session evidence. Count distinct conversion IDs in both cases. Do not use the number of outcomes with attribution evidence as the total for all conversions:

SELECT
  conversion_type,
  COUNT(DISTINCT conversion_id) AS total_conversions,
  COUNT(DISTINCT IF(
    captured_ga_session_id IS NOT NULL,
    conversion_id,
    NULL
  )) AS conversions_with_session_key
FROM <code>project.dataset.fct_crm_conversion
WHERE status = 'qualified'
  AND NULLIF(TRIM(conversion_id), '') IS NOT NULL
GROUP BY conversion_type;

Check conversion IDs for duplicates or blanks, and citation keys for repeats across snapshots. Also check for sessions missing identifiers and conversions with no matched touchpoint. GA4 daily exports can receive late events for up to three days. Set a data freshness rule that accounts for changes to recent dates; Google describes this behavior in its BigQuery export guidance. For purchases, GA4’s transaction_id helps remove duplicate purchase events when the ID is unique and present. It does not replace the CRM conversion key or link a CRM outcome to a web session. See Google’s transaction ID guidance.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com