
Context: The Two Dashboards That Never Join
A mid-sized South African e-commerce store has an SEO lead reporting impressions and average position from Search Console, and a paid lead reporting ROAS from Google Ads. Neither report answers the real question: which converting paid search terms are also organic queries where the brand sits two positions from a profitable click? This guide is about data plumbing - building one joinable dataset from SEO and Google Ads sources so decisions come from reconciled numbers.
Why SEO and Google Ads Data Disagrees Before You Touch It
Three structural reasons the numbers never match exactly: Search Console anonymises low-volume queries and caps export rows, so tail terms vanish. The Ads search-terms report aggregates by match type and omits low-volume terms. GA4 attributes sessions and conversions by its own model, on different clocks and rules.
| Source | Shows | Hides |
|---|---|---|
| Search Console | Query, page, impressions, clicks, avg position | Low-volume queries; capped rows; no conversion value |
| Ads search-terms | Matched terms, cost, clicks, conversions | Low-volume terms; aggregated by match type; no organic data |
| GA4 events | Session source, landing page, engagement, key events | Pre-consent detail; organic keyword (not provided) |
The Synergy Layer: Designing a Query × Page × Channel Table
The deliverable is one table where each row is a query, a landing page, and a channel, with shared columns:
query_page_channel
query -- normalised, lowercased
landing_page -- path only
channel -- organic | paid
clicks
impressions
cost_zar -- paid only
sessions
key_events
revenue_zar
avg_position -- organic only
Choosing Your Join Key: Why Landing Page Beats Keyword String
Joining on the keyword string is tempting but brittle - paid match types, close variants and organic phrase variations break string joins. Join on landing page path plus a normalised query instead.
Tip: lowercase queries and strip trailing slashes on pages before the join. Document the rule so future analysts reproduce it.
Where the Plumbing Breaks: Consent Mode, Server-Side Tagging and Lost Rows
Consent Mode changes what arrives in your dataset. Users who decline send modelled or cookieless pings, and if GA4 or Ads is not set up to handle this, rows never appear - your join looks clean but silently under-counts. Server-side tagging through a cloud container improves accuracy and first-party resilience, but only if consent signals pass through consistently.
Warning: a clean join on incomplete data is worse than a messy join on complete data. Audit consent state distribution before trusting any synergy metric.
POPIA and GDPR: What Happens to the Synergy Layer When Consent Strips Query Data
Under POPIA in South Africa and GDPR in the UK/EU, query-level and user-level data must be handled lawfully. Aggregate at the query × page × channel grain rather than the individual user grain, keep retention periods defensible, and document your lawful basis. Do not attempt to re-identify anonymised Search Console queries - treat the row limit as a compliance feature, not a bug.
The Paid-to-SEO Loop: Turning Converting Search Terms into an Organic Page Roadmap
Once the query × page × channel table exists, the first loop becomes obvious. Filter rows where paid shows conversions and organic shows an average position stuck on page two. That intersection is your organic roadmap - funded by evidence the searcher already converts. For an illustrative SA store, a paid term driving R450 (illustrative) of revenue per conversion with an organic page at position 14 is a clear candidate for a content and internal-link sprint. Track organic share of total revenue for those query-page pairs, and the paid clicks you can eventually reduce on them - as a hypothesis with a measurement window, not a timeline.
The SEO-to-Ads Loop: Using Organic Query and Engagement Data to Sharpen Copy, Sitelinks and Audiences
The reverse loop is often more valuable and less talked about. Search Console surfaces organic queries with strong click-through and healthy engagement that Ads does not bid on. A B2B SaaS query captured organically at 8% CTR with strong scroll depth is a candidate for a tightly themed ad group, sitelinks matched to the winning page section, and an audience built from those visitors. GA4 engagement data - scroll depth, key events, engaged sessions - tells you which organic pages deserve promotion into paid, and which paid landing pages should borrow from their organic counterparts.
Building the Warehouse Path: GA4, BigQuery Export and Search Console Limits Worth Knowing
Spreadsheets hit row limits and sampling. The warehouse path: enable the GA4 BigQuery export for unsampled event-level data; bulk-export Search Console via the API or a connector; pull Ads search-terms through the Google Ads API or a scheduled BigQuery Data Transfer. GA4's built-in Search Console integration has freshness and retention caveats worth verifying against current documentation, and standard property retention is limited - check the current figure before assuming multi-year history.
Tip: model the warehouse views on the query_page_channel schema from the start. If the table shape changes mid-project, every downstream report breaks.
What 'Worked' Looks Like: Measuring Synergy Without Inventing Numbers
Resist quoting a synergy ROAS. Report direction of travel on defensible metrics: blended CAC movement, MER, paid clicks on queries that moved into organic top positions, and conversion rate on pages receiving both channels. Prebo's custom reporting approach treats these as reconciled indicators, not a single composite score.
How to Sequence the Build: A 30/60/90 View for In-House Teams
| Window | Focus | Deliverable |
|---|---|---|
| 30 days | Audit consent, tracking, export permissions | Data quality log + schema sign-off |
| 60 days | Build query_page_channel in Sheets or BigQuery | First reconciled view + synonym mappings |
| 90 days | Run both loops, measure directional impact | First roadmap and audience update cycle |
Sources
- Google Search Console Performance report - data limitations and row limits
- About the search terms report - Google Ads Help
- GA4 Search Console integration
- Consent mode overview - Google Ads / Google tag
- GA4 BigQuery Export - unsampled data path
- Information Regulator South Africa - Protection of Personal Information Act



