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.…
When website owners compare citation-monitoring exports, analytics referrals, and customer relationship management (CRM) outcomes, they combine records of three different events. A citation observation records a page shown as a source in a monitored answer. A referral session records a visitor arriving from a tracked source. A CRM outcome records a defined business event, such as a won opportunity or paid order.
Keep each kind of record at its own level of detail, and join records only with a key that supports the comparison; for example, an analytics session ID stored with a form submission links it to a CRM outcome. A normalized URL and reporting week let you compare citations with visits to the same content. That comparison does not show that a citation caused a visit or conversion.
The main way counts get inflated is by joining raw records before deduplicating and summarizing them. One page can appear in multiple answers, one session can generate many analytics events, and one opportunity can be linked to several contacts. If those rows multiply in a join, a single conversion can appear several times.
A dataset’s grain is what each row represents. State the grain before joining tables:
| Table | One row represents | Useful key |
|---|---|---|
citation_observation |
A citation recorded by a monitoring export | Source row ID, or a documented vendor-specific key |
web_session |
One analytics session | GA4 user_pseudo_id plus ga_session_id |
crm_outcome |
One conversion object being counted | CRM object type plus object ID |
conversion_touchpoint |
The selected analytics session associated with a conversion | CRM object type plus object ID |
Citation exports do not share a universal schema or deduplication key. Keep the original file and its row identifiers when available. If the export lacks stable identifiers, store details about the import, such as the file hash, import time, and source row number. These details show where each observation came from and help prevent a second import of the same file from adding duplicate observations.
Decide what your citation count measures before removing duplicates. To count distinct cited pages per answer, keep one row for each unique provider-and-run combination. Add the answer, prompt, and normalized URL to define each row. To count every citation occurrence, keep repeated appearances of the same URL within an answer. These are different measures, so retain records of each occurrence if you may need both.
A GA4 event export has one row per event, and each row can contain repeated event parameters, which are extra values recorded for that event. Summarize the event rows into one row per session before joining session details to CRM outcomes. Google documents the BigQuery export schema and uses user_pseudo_id with ga_session_id to identify sessions in its unique-session guidance. Before writing extraction SQL, check which fields your property’s export includes. The export schema and configuration determine which attribution fields are available.
The strongest conversion join links a CRM outcome to the analytics session in which the visitor submitted a form or triggered the defined conversion event. Store the session identifiers with the submission, then keep the link in a touchpoint table or on the CRM record. Google’s Measurement Protocol guidance describes identifiers such as client_id and session_id for online and offline measurement. These identifiers link records at the session level. They do not link an unrelated citation observation to the visitor.
Count the CRM record that matches the business question. To measure won opportunities, count each opportunity by its object ID, not by a contact ID. A contact can have several opportunities. A lead can also become a contact or be linked to an opportunity. Salesforce documents these relationships in its lead-conversion API reference. Counting each related record as a separate conversion would inflate the total. CRM record IDs provide stable identifiers. HubSpot also documents record IDs and other identifiers for deduplicating records.
An email address may be missing or shared, and it may change over time, making email a less reliable fallback for joining records. Prefer a stable internal person key or a direct link between a session and a CRM record. Use email only when your data rules allow it and specify how to handle duplicate or changed addresses.
A URL and reporting period let you compare citation observations with sessions for the same page and week. This comparison supports content reporting, but it does not show that the same person saw the citation and visited the site. A citation observation is not a click.
This BigQuery-style query joins separate summaries, with one row per URL and week. The table names, fields, and sample rules are hypothetical. The query assumes mart.web_session already has one row per session and mart.conversion_touchpoint has one selected session for each CRM outcome.
The example counts each normalized URL once per answer in a run. It counts each CRM object once if its latest stored status is won. The touchpoint table must hold the session chosen by your attribution rule, such as the session in which the visitor submitted the form that led to the conversion.
-- Hypothetical article setup. -- NORMALIZE_AND_CANONICALIZE_URL is a project-specific placeholder, -- not a built-in BigQuery function. WITH citation_dedup AS ( SELECT provider, run_id, answer_id, prompt_id, NORMALIZE_AND_CANONICALIZE_URL(cited_url) AS page_url, observed_at, ROW_NUMBER() OVER ( PARTITION BY provider, run_id, answer_id, prompt_id, NORMALIZE_AND_CANONICALIZE_URL(cited_url) ORDER BY ingested_at DESC, source_row_id ) AS row_num FROM <code>raw.citation_exportWHERE cited_url IS NOT NULL ), citations_by_url_week AS ( SELECT page_url, DATE_TRUNC(DATE(observed_at, @reporting_timezone), WEEK(MONDAY)) AS week_start, COUNT(*) AS cited_answer_observations FROM citation_dedup WHERE row_num = 1 GROUP BY page_url, week_start ), sessions_by_url_week AS ( SELECT NORMALIZE_AND_CANONICALIZE_URL(landing_url) AS page_url, DATE_TRUNC(DATE(session_start, @reporting_timezone), WEEK(MONDAY)) AS week_start, COUNT(*) AS sessions, COUNTIF(LOWER(medium) = 'referral') AS referral_sessions FROMmart.web_sessionGROUP BY page_url, week_start ), outcomes_dedup AS ( SELECT object_type, object_id, status, converted_at, ROW_NUMBER() OVER ( PARTITION BY object_type, object_id ORDER BY updated_at DESC, ingested_at DESC ) AS row_num FROMmart.crm_outcome), qualified_outcomes AS ( SELECT object_type, object_id, converted_at FROM outcomes_dedup WHERE row_num = 1 AND status = 'won' ), outcomes_by_url_week AS ( SELECT NORMALIZE_AND_CANONICALIZE_URL(s.landing_url) AS page_url, DATE_TRUNC(DATE(o.converted_at, @reporting_timezone), WEEK(MONDAY)) AS week_start, COUNT(*) AS won_outcomes FROM qualified_outcomes o JOINmart.conversion_touchpointt ON t.object_type = o.object_type AND t.object_id = o.object_id JOINmart.web_sessions ON s.user_pseudo_id = t.user_pseudo_id AND s.ga_session_id = t.ga_session_id GROUP BY page_url, week_start ), report_keys AS ( SELECT page_url, week_start FROM citations_by_url_week UNION DISTINCT SELECT page_url, week_start FROM sessions_by_url_week UNION DISTINCT SELECT page_url, week_start FROM outcomes_by_url_week ) SELECT k.page_url, k.week_start, COALESCE(c.cited_answer_observations, 0) AS cited_answer_observations, COALESCE(s.sessions, 0) AS sessions, COALESCE(s.referral_sessions, 0) AS referral_sessions, COALESCE(o.won_outcomes, 0) AS won_outcomes FROM report_keys k LEFT JOIN citations_by_url_week c USING (page_url, week_start) LEFT JOIN sessions_by_url_week s USING (page_url, week_start) LEFT JOIN outcomes_by_url_week o USING (page_url, week_start);
Set URL-normalization rules for your site. Decide whether to remove tracking parameters, standardize trailing slashes, follow redirects, and map alternate URLs to a canonical page. Keep the original URL with the normalized URL so you can check how the query grouped records.
The query links each CRM outcome to the landing URL of its selected conversion session, then groups it by the week the outcome occurred. It groups citations by the week they were observed and sessions by the week they started. Because outcome, observation, and session dates answer different questions, decide which week to use for each measure in a cohort comparison and align the dates. Also set the reporting time zone explicitly because analytics and search exports may use different date conventions.
GA4 reports traffic sources at two scopes: session-scoped sources describe a visit, while user-scoped sources describe a visitor across visits. Its Traffic acquisition report documentation describes session-scoped reporting, while the User acquisition and Traffic acquisition comparison explains the difference in scope. State whether your report uses the converting session’s source, the visitor’s first source, a CRM source field, or another attribution rule. Do not group these definitions under one label such as “source of conversion.”
Keep observed UTM values, which are campaign tags in URLs, and click identifiers when available. Google documents how manual tagging and auto-tagging affect traffic-source dimensions, including cases where auto-tagging takes precedence. Analytics attribution rules and the information recorded during a visit determine its referral values.
One CRM outcome can link to several sessions or contacts. For single-touch reporting, choose one session using a stated rule and count the CRM object once. For multi-touch analysis, keep every session link in a bridge table and assign credit with an explicit model. Do not count the full outcome for each linked session.
Citation and referral URLs may not match. A cited URL may redirect, include parameters, or resolve to a canonical page, while the visitor lands on another URL. Keep both the original and normalized URLs, and treat joins by URL as descriptive comparisons. Google Search Console click totals and analytics sessions are different measures. Its documentation explains how impressions and clicks are counted.
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