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, referral, and export records often contain multiple spellings of a page URL, such as a hostname with different capitalization or an added campaign tag. Systems need to match equivalent URL spellings while keeping pages for different languages, regions, editions, and product variants separate.
URL normalization standardizes a URL’s spelling. Deduplication decides whether two records refer to the same page. Keep these decisions separate. Preserve the submitted URL and create a cautious comparison key. Merge records only when evidence supports treating the pages as equivalent.
Store the submitted URL unchanged so you can reproduce the source and audit later decisions. Create a separate comparison key, a normalized URL form that matches URLs differing only in safe ways, such as capitalization. Keep the reader-facing citation URL separately as well, since it may need a useful fragment such as #specifications.
When a system fetches the page, record the final destination and any canonical URL separately from the submitted URL. A canonical URL is a page’s preferred representative URL. Google treats canonical declarations, redirects, and sitemap entries as signals for Search; they do not establish a universal rule for deduplicating citations.
Parse each URL into its components before changing it. The URI standard supports several safe normalizations: lowercase the scheme and hostname, remove a scheme’s default port, use uppercase letters in percent-encoded triplets, decode percent-encoded unreserved characters, and resolve complete . and .. path segments.
Preserve path case and structure. Do not collapse repeated slashes or remove trailing slashes unless the site’s behavior or a verified redirect shows that the URLs lead to the same resource. A server can distinguish /EN/guide from /en/guide, and /product/edition-a from /product/edition-b. Google also notes that URL paths are case-sensitive.
Do not treat http and https, separate hostnames, or different paths as equivalent based on appearance alone. Keep them distinct unless a verified redirect or other site-specific evidence supports the match.
Paths and query parameters can identify a page variant. A query parameter is a name-value pair after ?, such as color=blue. A site might use a parameter to track a referral. It might use one to select a product or language, identify an edition, or choose a page. The URL standard defines how a query appears in a URL; it does not say which parameters a particular site uses to change content.
Remove parameters from the comparison key only when an explicit, maintained policy identifies them as tracking-only. Keep the original URL intact even when the key omits a known tracking parameter. Preserve unknown parameters unless you have verified that the site treats them as interchangeable. Apply the same rule to repeated parameters, their values, and their order.
A URL can use parameters such as lang=fr or locale=en-GB to identify different content or choices. It can use sku=, color=, edition=, or page= for the same purpose. Google’s ecommerce guidance recognizes product variants in either the path or query string, such as /t-shirt/green and /t-shirt?color=green. That makes blanket removal of query parameters unsafe for citation records.
A fragment begins with # and points to a location within a page. It is not part of the HTTP request sent to the server, so a page-level comparison key can omit it. Keep it in the reader-facing citation URL when it points to a useful passage. If the citation refers to distinct passages on the same page, retain each fragment at the citation-record level even if the records share a page-level key.
A conservative key-generation outline looks like this:
record.original_url = input
parts = parse_url(input)
key.scheme = lowercase(parts.scheme)
key.host = lowercase(parts.host)
key.port = remove_default_port(parts.scheme, parts.port)
key.path = normalize_dot_segments_and_unreserved_encoding(parts.path)
key.query = preserve_query_order_and_repeated_values(parts.query)
remove only approved tracking parameters from key.query
exclude the fragment from the page-level key
record.display_url = input
record.normalized_key = serialize(key)
The URI standard defines syntax-level normalization, but it does not define an application’s tracking-parameter policy. Document that policy and change it deliberately rather than applying broad string substitutions.
A matching comparison key identifies records to review; it does not prove that they describe the same page. Before merging, compare any identity fields you know how to extract, including language or locale, product ID or SKU, and edition or version. Keep records separate when those values conflict, unless you have clear evidence that the difference does not identify a distinct page.
| Difference between candidate URLs | Default action |
|---|---|
| Language or locale in the host, path, or query | Keep separate |
| Product ID, SKU, color, size, or model | Keep separate |
| Edition, release, or version | Keep separate |
| Path case, path segments, or trailing slash | Keep separate until site behavior or a verified redirect establishes equivalence |
| Fragment only | Share a page-level key if appropriate; preserve each citation fragment |
| Known tracking parameter only | Remove it from the comparison key under documented policy; retain the original URL |
A permanent redirect can support treating its destination as the lasting URL. HTTP also defines temporary redirects, so record the redirect status and do not treat a temporary redirect as a lasting identity rule. A page’s canonical declaration also provides useful evidence, but investigate any language, product, or edition conflict before allowing it to determine URL identity. Google describes canonicalization as a Search preference, and its product guidance recognizes that some sites publish separate, equally important pages for product variants.
When evidence conflicts, keep the records separate and flag them for review. A mistaken merge can erase a useful citation target, while records marked as possible duplicates remain recoverable.
Hypothetical example: A source export contains these URLs:
https://shop.example/en-us/products/model-x?edition=2025&color=blue&utm_source=journal#specifications
https://shop.example/en-gb/products/model-x?edition=2025&color=blue&utm_source=journal#specifications
If utm_source is on the site’s approved tracking list, remove it from each comparison key. Keep the en-us and en-gb path segments, as well as the edition and color parameters. The URLs are not duplicates simply because they otherwise look alike. Preserve both original URLs and their fragments in the citation records.
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