Validating Search Console Bulk Export Partitions and Retries
When Search Console sends performance data to BigQuery, it writes each day's data into date-based partitions, which keep each date's rows together.…
Citation, referral, and source exports often contain URLs that look alike but lead to different pages. A language code in the path can change the page a reader sees. Product identifiers and edition numbers can do the same. A locale setting in the query (the part after ?) can change it too. If cleanup removes those distinctions, it can combine records for separate sources and erase information about where each record came from.
URL normalization means changing only safe formatting details so URLs that mean the same thing use the same spelling for comparison. Deduplication means deciding whether records refer to the same page or content. Keep those decisions separate: preserve each URL as received, create a cautious comparison key, and merge records only after checking that language, locale, product, and edition differences do not identify distinct pages.
Store the exact URL from each source record in an observed URL field. Generate a separate comparison key for finding likely duplicates, and choose a display URL only after checking the destination and page. This preserves the original citation or referral even when cleanup changes the URL used for comparison.
A comparison key is evidence for grouping, not proof that two records identify the same content. The URI standard defines URL components and some safe equivalences, while leaving the meaning of a particular path or query to the site. Google’s canonicalization guidance describes how Google selects representative URLs for search. That selection serves a different purpose from preserving source distinctions in an export.
Break each URL into its parts before changing it: its scheme (such as HTTPS), host (the domain), path (the part after the host), query (the settings after ?), and fragment (the part after #). Avoid broad text replacements: a rule that is safe for a hostname might change the meaning of a path or query.
| Component | Safe default | Preserve unless verified |
|---|---|---|
| Scheme and host | Lowercase them. Remove a default port when it matches the scheme. | Keep HTTP and HTTPS distinct unless redirects and destination behavior show they are equivalent. Do not merge separate subdomains or country domains by assumption. |
| Path | Resolve literal . and .. segments. Normalize percent-escape hex digits and decode percent-encoded unreserved characters. |
Preserve path case, trailing slashes, and encoded reserved characters unless the origin is confirmed to treat the alternatives identically. |
| Query | Remove only parameters documented as non-content tracking or session data for that host or URL pattern. | Keep unknown, repeated, or content-selecting parameters, including language, locale, product, edition, and version values. |
| Fragment | Keep it in the observed URL. Exclude it from a retrieval comparison only when the task compares the requested page rather than a cited section. | Preserve section references when they identify the specific passage being cited. |
The URI standard’s normalization rules support lowercasing the scheme and host. They also support resolving . and .. path segments. The rules allow decoding percent-encoded characters when they represent unreserved characters. Reserved characters can act as delimiters, so decoding them can change how a URL is read. Google also says paths are case-sensitive unless the site handles them otherwise, and advises removing only parameters that do not change content in its URL structure guidance.
Each site defines what its query parameters mean. A campaign parameter is removable only after confirming that it does not change the content, locale, redirect destination, or other relevant behavior. Do not sort parameters or collapse repeated keys unless the site’s behavior confirms that order and repetition do not matter. When their meaning is unknown, preserve them in the comparison key.
Keep language and region signals in the comparison key wherever the site places them: in the domain, subdomain, path, or query. For example, /en/manual/ and /fr/manual/ remain separate candidates because the paths may select pages in different languages. Google states that pages in different primary languages are not duplicates on the basis of language alone. It treats same-language regional pages as related variants for search, with guidance to use canonical signals alongside hreflang, a markup method for identifying language and regional alternatives. Those search practices do not automatically determine how a citation export should group records.
Apply the same caution to product and edition identifiers. Keep /manual/v2/ separate from /manual/v3/, and retain parameters such as sku, product, or edition until the site’s behavior and page content establish that the values do not distinguish resources. The URL standard treats path and query data as part of resource identification; it does not provide a universal test for whether two product editions are equivalent.
For a hypothetical export containing /en/manual/v2?utm_source=mail and /fr/manual/v2?utm_source=mail, remove utm_source only if the site has verified it as tracking-only. Keep /en/ and /fr/ in the keys. Likewise, do not merge /en/manual/v2 and /en/manual/v3 merely because their paths otherwise match.
After generating comparison keys, group records that share a key and inspect each group with more than one observed URL. Follow each URL through any redirects, then compare the final pages. Compare each page’s title and primary language. Then check its locale and product. Check its edition and version as well. Include the page’s declared preferred URL (its canonical URL) in the evidence; decide whether to merge from the full comparison.
A canonical link tells search engines which URL a site prefers as the representative for a page. Google says canonical links are signals, not guaranteed outcomes. Its canonical URL documentation recommends choosing the same canonical URL for duplicate pages. If a page declares a canonical in another language or for another product edition, inspect the destination and content before grouping the records. Keep the records separate when the evidence conflicts or remains unclear.
Before applying a new rule to an entire export, test it on a sample with known language, locale, product, and edition variants. Review the collisions, write down the parameter rules for each host, and keep the original URLs and merge decisions so later exports can use the same rules.
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