Google Ads BigQuery Data Transfer — Query Guide

A self-contained reference for querying the FCR Google Ads Data Transfer dataset, joining to the subscriber catalogue, and cross-linking to GA4. Written for someone who knows SQL and the Google Ads product but is new to this BigQuery project.

Scope: this guide covers the raw Google Ads Data Transfer (GOOGLE_ADS_TRANSFER_EU), the subscriber mapping tables (GOOGLE_ACCOUNT_DATA.GoogleAccount_Subscr_MAP_*), the pre-joined Superset view (SUPERSET_MASTER.2_Ads_Data_view), and the GA4 export datasets. It does not cover Campaign Manager, Display & Video 360, or Search Ads 360.

1. Access

All queries below run against Google BigQuery project listingmanager-1529856313699 in the region-eu location. You have three options to run them:

Option A — BigQuery console (fastest to try things)

If you have a Google Cloud account with access to the project, open the BigQuery console and select listingmanager-1529856313699 as the current project. You can paste any query from this doc directly into the query editor. Ask Cathal for the IAM invite — you need at least BigQuery Data Viewer + BigQuery Job User on the project.

Option B — FCR Dashboard API (no GCP access needed)

Every query in this doc can be executed via the worker API:

curl -s -X POST "https://fcr-dashboard-api.cathaldempsey.workers.dev/dashboard-bq-execute" \
  -H "Content-Type: application/json" \
  -H "x-api-key: YOUR_API_KEY" \
  -d '{"sql": "SELECT 1"}'

The response is {"success": true, "rowCount": N, "rows": [...]}. The endpoint accepts any read-only BigQuery SQL — SELECT, CTEs, joins, window functions, INFORMATION_SCHEMA queries all work. It is capped at a safety limit per response so for very large result sets paginate with LIMIT/OFFSET or aggregate server-side.

Ask Cathal for the API key.

Option C — Claude Code (recommended for interactive work)

Install Claude Code (claude.ai/code) and point it at a local checkout of the fcr-dashboard repo. The repo's CLAUDE.md auto-loads project context (BQ paths, subscriber-lookup rules, API key location). From there you can type natural-language questions — "what were the top 20 PMax campaigns by conversions in March 2026" — and Claude will build the SQL, run it against the worker, and show you the results. All of the queries in this doc also work verbatim inside Claude Code via the /bq-query slash command.

Cathal can send a Claude Code Teams invite for this workflow.

2. Dataset map

Every table mentioned in this guide lives in one of four datasets inside listingmanager-1529856313699:

Two older datasets — analytics_373636310 (last event 2023-06-02) and GPI_GA_UA3 (Universal Analytics 3) — are dormant and should be ignored for any current analysis.

3. Google Ads Data Transfer schema

3.1 The _3833870882 suffix

Every table in GOOGLE_ADS_TRANSFER_EU is named like ads_CampaignStats_3833870882. The numeric suffix is the Google Ads MCC (Manager) customer ID — FCR's top-level Ads manager account. You do not choose the suffix or need to substitute it. One MCC = one set of tables. Client accounts are differentiated by the customer_id column inside each table.

3.2 Table families

The 109 ads_* tables divide into four family types. The Google Ads Data Transfer documentation describes them in full at https://cloud.google.com/bigquery/docs/google-ads-transfer-schema — what follows is the practical short version.

Entity tables — one row per entity, point-in-time state. These carry metadata but no metrics.

• ads_Customer_* — one row per ads customer (the clients). Includes customer_id, account name, currency, time zone.
• ads_Campaign_* — one row per campaign. campaign_id, campaign_name, campaign_status, campaign_advertising_channel_type (SEARCH / DISPLAY / SHOPPING / PERFORMANCE_MAX / etc.), bidding strategy.
• ads_AdGroup_*, ads_AdGroupCriterion_*, ads_Ad_*, ads_Keyword_*, ads_AssetGroup_*, ads_Asset_*, ads_Budget_* — same idea at each level.

Basic stats tables — daily metrics, excluding conversions. Use these when you want clicks/impressions/cost without conversion double-counting.

• ads_AccountBasicStats_*, ads_CampaignBasicStats_*, ads_AdGroupBasicStats_*, ads_KeywordBasicStats_*, ads_AdBasicStats_*.
• Key columns: customer_id, the entity id, segments_date, segments_ad_network_type, segments_device, metrics_impressions, metrics_clicks, metrics_cost_micros, metrics_interactions, metrics_ctr, metrics_average_cpc.

Stats tables (no "Basic") — same as Basic + join-through to conversion metrics. Behaves like the Google Ads UI default report.

• ads_CampaignStats_*, ads_AdGroupStats_*, ads_KeywordStats_*, ads_AdStats_*.
• Adds metrics_conversions, metrics_conversions_value, metrics_current_model_attributed_conversions (data-driven attribution), metrics_cost_per_conversion, metrics_value_per_conversion, metrics_conversions_from_interactions_rate.
• ⚠️ these tables use the account's default attribution model and default conversion action set — a conversion action toggled off "Include in Conversions" is not counted. If you need the full picture use the Conversion Stats family.

Conversion stats tables — one row per entity × conversion action × day. Use these when you need to know which conversion action fired.

• ads_AccountConversionStats_*, ads_CampaignConversionStats_*, ads_AdGroupConversionStats_*, ads_KeywordConversionStats_*, ads_AdConversionStats_*.
• Extra columns: segments_conversion_action, segments_conversion_action_name, segments_conversion_action_category (PHONE_CALL_LEAD / GET_DIRECTIONS / SUBMIT_LEAD_FORM / PURCHASE / etc.), segments_conversion_attribution_event_type.
• Only carries conversion fields (metrics_conversions, metrics_conversions_value). Do not sum metrics_clicks / metrics_impressions from this table — those columns don't exist here; you'll get nulls.

Cross-device conversion stats — the superset of "all conversions" including off-device attribution (uses metrics_all_conversions instead of metrics_conversions).

• ads_CampaignCrossDeviceConversionStats_*, ads_AdGroupCrossDeviceConversionStats_*, etc.
• Columns: metrics_all_conversions, metrics_all_conversions_value, metrics_cross_device_conversions.
• This table exposes conversion actions that are not set to "Include in Conversions" in the UI — useful if you want to see every tracked signal, but will disagree with the Google Ads UI headline number.

Non-click stats tables — engagement events that happened without a click (view-through, engagement). ads_CampaignAudienceNonClickStats_* etc.

Hourly stats — same shape as Stats but partitioned hourly. ads_HourlyCampaignStats_*, ads_HourlyCampaignConversionStats_*, ads_HourlyAccountConversionStats_*.

Cross-device stats (non-conversion) — ads_CampaignCrossDeviceStats_*. Clicks/impressions attributed across devices.

Specialty — ads_SearchQueryStats_* / ads_SearchQueryConversionStats_* (search terms report), ads_LandingPageStats_*, ads_GeoStats_*, ads_GeoConversionStats_*, ads_LocationsDistanceStats_*, ads_LocationsUserLocationsStats_*, ads_PaidOrganicStats_*, ads_ShoppingProductStats_*, ads_AgeRangeStats_*, ads_GenderStats_*, ads_ParentalStatusStats_*, ads_VideoStats_*, ads_PlacementStats_*, ads_DisplayVideoKeywordStats_*, ads_DisplayVideoTopicStats_*, ads_AssetGroupProductGroupStats_*, ads_AdGroupAudienceStats_*, ads_CampaignAudienceStats_*, ads_BidGoalStats_*.

3.3 ads_* vs p_ads_*

Every table has a sibling with a p_ prefix (p_ads_CampaignStats_3833870882). The p_ads_* variants are partitioned on _PARTITIONTIME and backfilled whenever Google Ads restates historical metrics. The non-p_ tables are continuously rolling. Both should return identical numbers for any given segments_date; the p_ads_* versions are cheaper to scan for historical queries because partition pruning kicks in.

Rule of thumb: use ads_* for most queries; use p_ads_* if you're scanning a tight historical window and want to minimise bytes processed.

3.4 Key segments_* dimensions

Every stats table lets you segment by a predictable set of dimensions. Adding a segments_* column to your GROUP BY creates additional rows — i.e. if you group by both segments_date and segments_device, a campaign that ran on all three devices on a given day will appear three times.

Common ones:

• segments_date (DATE) — always include this. Some tables also have segments_month, segments_week, segments_quarter, segments_year, segments_day_of_week.
• segments_ad_network_type — SEARCH / SEARCH_PARTNERS / CONTENT / YOUTUBE_SEARCH / YOUTUBE_WATCH / MIXED (Performance Max rolls up as MIXED; it's the multi-network bucket).
• segments_device — MOBILE / DESKTOP / TABLET / CONNECTED_TV.
• segments_click_type — CALLS, GET_DIRECTIONS, LOCATION_EXPANSION, URL_CLICKS, SITELINKS, CROSS_NETWORK (PMax), PRODUCT_LISTING_AD_CLICKS, etc.
• segments_slot — SEARCH_TOP / SEARCH_SIDE / SEARCH_OTHER / MIXED.
• segments_conversion_action / segments_conversion_action_name / segments_conversion_action_category — only exist on the Conversion Stats family.

4. Subscriber mapping — joining Ads data to FCR clients

Google Ads data is keyed by Google Ads customer_id (a 10-digit number). FCR data is keyed by subscriber_id (a 7–8 digit number from Dynamics). You map between them via:

GOOGLE_ACCOUNT_DATA.GoogleAccount_Subscr_MAP_GAds_native

GOOGLE_ACCOUNT_DATA.GoogleAccount_Subscr_MAP_GAds (the "friendly" view)

Same data with some additional display fields. Use _native for joins, _friendly for eyeballing.

Simple join pattern (copy-paste starter)

SELECT
  m.SUBSCR_ID_Match AS subscriber_id,
  m.title,
  m.campaign_manager,
  s.segments_date,
  s.campaign_id,
  s.metrics_impressions,
  s.metrics_clicks,
  ROUND(s.metrics_cost_micros / 1e6, 2) AS media_cost_eur,
  s.metrics_conversions
FROM `listingmanager-1529856313699.GOOGLE_ADS_TRANSFER_EU.ads_CampaignStats_3833870882` s
JOIN `listingmanager-1529856313699.GOOGLE_ACCOUNT_DATA.GoogleAccount_Subscr_MAP_GAds_native` m
  ON s.customer_id = m.ExternalCustomerId
WHERE m.SUBSCR_ID_Match = '10597556'  -- Borstal Gate Dental Surgery
  AND s.segments_date BETWEEN '2026-03-01' AND '2026-03-31'
ORDER BY s.segments_date;

Going the other way — subscriber ID from account name

SELECT DISTINCT subscriber_id, account_name
FROM `listingmanager-1529856313699.fcr_operations.active_clients`
WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM `listingmanager-1529856313699.fcr_operations.active_clients`)
  AND LOWER(account_name) LIKE LOWER('%borstal%');

Or pull Ads customer_id straight from the Ads customer table:

SELECT customer_id, customer_descriptive_name
FROM `listingmanager-1529856313699.GOOGLE_ADS_TRANSFER_EU.ads_Customer_3833870882`
WHERE LOWER(customer_descriptive_name) LIKE LOWER('%borstal%');

5. Pre-built view: SUPERSET_MASTER.2_Ads_Data_view

If you don't want to join the tables yourself, there's already a view that does the plumbing. It's what the FCR dashboard reads from.

What it joins

• ads_CampaignBasicStats_3833870882 (source of impressions / clicks / cost — no conversion double-counting)
• GoogleAccount_Subscr_MAP_GAds_native (subscriber + management fee)
• ads_BudgetStats_3833870882 (campaign_name lookup)
• SUPERSET_MASTER.1_Core_Listings (human-readable account name)
• SUPERSET_MASTER.1_Core_DATE (adds month_for_sorting, month_for_display, year_month_date)

What it adds

• subscriber_id — derived from the mapping table, cast to STRING.
• title — the client title from the mapping table.
• subscriber_display — "<Account Name> (ID: <subscriber_id>)" ready for display.
• campaign_name — joined in from BudgetStats (BudgetStats is chosen because it has a row per campaign without being bloated like CampaignStats).
• management_fee_decimal — the management_fee string ("45%") parsed to 0.45.
• retail_cost_micros — metrics_cost_micros / (1 - management_fee_decimal) → what the client pays FCR, inclusive of management fee.
• retail_cost_currency — same thing divided by 1,000,000, so it's in EUR.

When to use it vs the raw tables

• Use the view for most campaign-level performance work, MoM trends, per-AM rollups, and anything where you want retail cost (client-facing numbers).
• Use the raw Ads tables for:
  • Anything below campaign level (ad groups, keywords, search terms, ads).
  • Anything that needs conversion-action breakdowns (the view uses CampaignBasicStats which has no conversion columns).
  • Anything that needs non-Search network segmentation (the view does include segments_ad_network_type since it's on the Basic Stats table, so this one is actually fine from the view).
  • Hourly, cross-device, or audience segmentation.

Example — client MoM from the view

SELECT
  month_for_display,
  SUM(metrics_impressions) AS impressions,
  SUM(metrics_clicks) AS clicks,
  ROUND(SUM(retail_cost_currency), 2) AS retail_spend_eur,
  ROUND(SAFE_DIVIDE(SUM(metrics_clicks), SUM(metrics_impressions)) * 100, 2) AS ctr_pct,
  ROUND(SAFE_DIVIDE(SUM(retail_cost_currency), SUM(metrics_clicks)), 2) AS retail_cpc_eur
FROM `listingmanager-1529856313699.SUPERSET_MASTER.2_Ads_Data_view`
WHERE subscriber_id = '10597556'
  AND month_for_sorting >= FORMAT_DATE('%Y-%m', DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH))
GROUP BY month_for_display, month_for_sorting
ORDER BY month_for_sorting;

6. Conversion analysis — deeper dive

The most common "why don't the numbers match" question at FCR is about conversions. The Google Ads UI, Looker Studio, Adplorer, and our dashboard can all show different numbers for the same month. The rules:

1. metrics_conversions on any Stats table = the Google Ads UI "Conversions" column. This counts only conversion actions flagged "Include in 'Conversions'" in the Ads UI, using the account's default attribution model.
2. metrics_all_conversions on the Cross-Device Conversion Stats tables = the Ads UI "All conv." column. Includes every tracked conversion action regardless of the "Include" toggle.
3. Conversion restatement lag. Google Ads continues to attribute conversions back to a click for the full conversion window (default 30 days, can be longer for offline imports). Recent months in the BQ transfer will lag the live Ads UI by a handful of conversions until the window closes. Older than ~45 days reconciles exactly. This is not a bug in the transfer — it's the normal shape of the data.
4. Do not sum metrics_clicks from Conversion Stats tables — they're nullable there. Clicks live on the Stats / BasicStats family.

Example — per-conversion-action breakdown for a client

SELECT
  segments_conversion_action_name AS action,
  segments_conversion_action_category AS category,
  segments_ad_network_type AS network,
  segments_click_type AS click_type,
  ROUND(SUM(metrics_conversions), 2) AS conversions
FROM `listingmanager-1529856313699.GOOGLE_ADS_TRANSFER_EU.ads_AccountConversionStats_3833870882` c
JOIN `listingmanager-1529856313699.GOOGLE_ACCOUNT_DATA.GoogleAccount_Subscr_MAP_GAds_native` m
  ON c.customer_id = m.ExternalCustomerId
WHERE m.SUBSCR_ID_Match = '10597556'
  AND c.segments_date BETWEEN '2026-03-01' AND '2026-03-31'
GROUP BY action, category, network, click_type
ORDER BY conversions DESC;

Example — "All conversions" (including non-primary) for the same client

SELECT
  segments_conversion_action_name AS action,
  ROUND(SUM(metrics_all_conversions), 2) AS all_conversions
FROM `listingmanager-1529856313699.GOOGLE_ADS_TRANSFER_EU.ads_CampaignCrossDeviceConversionStats_3833870882` c
JOIN `listingmanager-1529856313699.GOOGLE_ACCOUNT_DATA.GoogleAccount_Subscr_MAP_GAds_native` m
  ON c.customer_id = m.ExternalCustomerId
WHERE m.SUBSCR_ID_Match = '10597556'
  AND c.segments_date BETWEEN '2026-03-01' AND '2026-03-31'
GROUP BY action
ORDER BY all_conversions DESC;

Example — MCC-wide conversion action catalogue

Useful to see what conversion actions exist across all FCR clients and how often each fires:

SELECT
  segments_conversion_action_category AS category,
  segments_conversion_action_name AS action,
  COUNT(DISTINCT customer_id) AS customers_using,
  ROUND(SUM(metrics_conversions), 0) AS total_conversions_last_90d
FROM `listingmanager-1529856313699.GOOGLE_ADS_TRANSFER_EU.ads_AccountConversionStats_3833870882`
WHERE segments_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY category, action
HAVING total_conversions_last_90d > 0
ORDER BY total_conversions_last_90d DESC;

7. Search terms, keywords, ad groups

Search terms (queries users actually typed)

SELECT
  s.segments_date,
  s.search_query,
  SUM(s.metrics_impressions) AS impressions,
  SUM(s.metrics_clicks) AS clicks,
  ROUND(SAFE_DIVIDE(SUM(s.metrics_clicks), SUM(s.metrics_impressions)) * 100, 2) AS ctr_pct,
  SUM(s.metrics_conversions) AS conversions,
  ROUND(SUM(s.metrics_cost_micros) / 1e6, 2) AS media_cost_eur
FROM `listingmanager-1529856313699.GOOGLE_ADS_TRANSFER_EU.ads_SearchQueryStats_3833870882` s
JOIN `listingmanager-1529856313699.GOOGLE_ACCOUNT_DATA.GoogleAccount_Subscr_MAP_GAds_native` m
  ON s.customer_id = m.ExternalCustomerId
WHERE m.SUBSCR_ID_Match = '10597556'
  AND s.segments_date BETWEEN '2026-03-01' AND '2026-03-31'
GROUP BY s.segments_date, s.search_query
ORDER BY clicks DESC
LIMIT 50;

Keyword-level performance with quality score

Quality score lives on ads_Keyword_* (the entity table), performance on ads_KeywordStats_*.

SELECT
  k.criterion_id,
  k.keyword_text,
  k.keyword_match_type,
  k.quality_info_quality_score,
  k.quality_info_creative_quality_score,
  k.quality_info_post_click_quality_score,
  k.quality_info_search_predicted_ctr,
  SUM(s.metrics_impressions) AS impressions,
  SUM(s.metrics_clicks) AS clicks,
  ROUND(SAFE_DIVIDE(SUM(s.metrics_clicks), SUM(s.metrics_impressions)) * 100, 2) AS ctr_pct,
  SUM(s.metrics_conversions) AS conversions,
  ROUND(SUM(s.metrics_cost_micros) / 1e6, 2) AS media_cost_eur
FROM `listingmanager-1529856313699.GOOGLE_ADS_TRANSFER_EU.ads_KeywordStats_3833870882` s
JOIN `listingmanager-1529856313699.GOOGLE_ADS_TRANSFER_EU.ads_Keyword_3833870882` k
  ON s.customer_id = k.customer_id
 AND s.campaign_id = k.campaign_id
 AND s.ad_group_id = k.ad_group_id
 AND s.criterion_id = k.criterion_id
JOIN `listingmanager-1529856313699.GOOGLE_ACCOUNT_DATA.GoogleAccount_Subscr_MAP_GAds_native` m
  ON s.customer_id = m.ExternalCustomerId
WHERE m.SUBSCR_ID_Match = '10597556'
  AND s.segments_date BETWEEN '2026-03-01' AND '2026-03-31'
GROUP BY k.criterion_id, k.keyword_text, k.keyword_match_type,
         k.quality_info_quality_score, k.quality_info_creative_quality_score,
         k.quality_info_post_click_quality_score, k.quality_info_search_predicted_ctr
ORDER BY media_cost_eur DESC;

Ad-group performance

SELECT
  a.ad_group_name,
  c.campaign_name,
  SUM(s.metrics_impressions) AS impressions,
  SUM(s.metrics_clicks) AS clicks,
  SUM(s.metrics_conversions) AS conversions,
  ROUND(SUM(s.metrics_cost_micros) / 1e6, 2) AS media_cost_eur
FROM `listingmanager-1529856313699.GOOGLE_ADS_TRANSFER_EU.ads_AdGroupStats_3833870882` s
JOIN `listingmanager-1529856313699.GOOGLE_ADS_TRANSFER_EU.ads_AdGroup_3833870882` a
  ON s.customer_id = a.customer_id AND s.ad_group_id = a.ad_group_id
JOIN `listingmanager-1529856313699.GOOGLE_ADS_TRANSFER_EU.ads_Campaign_3833870882` c
  ON s.customer_id = c.customer_id AND s.campaign_id = c.campaign_id
JOIN `listingmanager-1529856313699.GOOGLE_ACCOUNT_DATA.GoogleAccount_Subscr_MAP_GAds_native` m
  ON s.customer_id = m.ExternalCustomerId
WHERE m.SUBSCR_ID_Match = '10597556'
  AND s.segments_date BETWEEN '2026-03-01' AND '2026-03-31'
GROUP BY a.ad_group_name, c.campaign_name
ORDER BY media_cost_eur DESC;

8. Cross-linking to Google Analytics (GA4)

FCR manages ~2,889 GA4 properties across ~2,508 subscribers. Each property lands in its own BigQuery dataset via the GA4 linked export. Relevant datasets:

• listingmanager-1529856313699.analytics_354147656 — live, last export events_20260412
• listingmanager-1529856313699.analytics_260259351 — live, last export events_20260412
• listingmanager-1529856313699.analytics_373636310 — dormant (last data 2023)
• listingmanager-1529856313699.ga4forgpi — empty/unused

Both live datasets use the standard GA4 export schema — one partition per day (events_YYYYMMDD), event rows with nested event_params, user_properties, device, geo, etc.

Subscriber → GA4 property mapping

SELECT
  SUBSCR_ID_Match AS subscriber_id,
  accountId AS ga_account_id,
  propertyId AS ga4_property_id
FROM `listingmanager-1529856313699.GOOGLE_ACCOUNT_DATA.GoogleAccount_Subscr_MAP_GAnalytics_native`
WHERE SUBSCR_ID_Match = '10597556';

GA4 linked export datasets are named analytics_<propertyId> — so if the mapping returns propertyId = 354147656, the BigQuery dataset is analytics_354147656 and you can query it directly:

SELECT
  PARSE_DATE('%Y%m%d', event_date) AS date,
  COUNT(*) AS events,
  COUNTIF(event_name = 'page_view') AS page_views,
  COUNTIF(event_name = 'click') AS clicks,
  COUNTIF(event_name = 'generate_lead') AS leads,
  COUNTIF(event_name = 'form_submit') AS form_submits
FROM `listingmanager-1529856313699.analytics_354147656.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260301' AND '20260331'
GROUP BY date
ORDER BY date;

Joining Google Ads click data to GA4 session data

The GA4 export carries collected_traffic_source.gclid on events that originated from a Google Ads click. Example — tie Ads spend to GA4 engaged sessions for a client:

WITH ads AS (
  SELECT
    s.segments_date AS day,
    SUM(s.metrics_clicks) AS ads_clicks,
    ROUND(SUM(s.metrics_cost_micros) / 1e6, 2) AS media_cost_eur
  FROM `listingmanager-1529856313699.GOOGLE_ADS_TRANSFER_EU.ads_CampaignStats_3833870882` s
  JOIN `listingmanager-1529856313699.GOOGLE_ACCOUNT_DATA.GoogleAccount_Subscr_MAP_GAds_native` m
    ON s.customer_id = m.ExternalCustomerId
  WHERE m.SUBSCR_ID_Match = '10597556'
    AND s.segments_date BETWEEN '2026-03-01' AND '2026-03-31'
  GROUP BY day
),
ga AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS day,
    COUNT(DISTINCT (user_pseudo_id || CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) AS sessions,
    COUNTIF(event_name = 'form_submit' OR event_name = 'generate_lead') AS site_leads
  FROM `listingmanager-1529856313699.analytics_354147656.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260301' AND '20260331'
    AND collected_traffic_source.gclid IS NOT NULL
  GROUP BY day
)
SELECT
  ads.day,
  ads.ads_clicks,
  ads.media_cost_eur,
  ga.sessions AS ga4_sessions_from_ads,
  ga.site_leads AS ga4_leads_from_ads
FROM ads
LEFT JOIN ga USING (day)
ORDER BY ads.day;

⚠ Caveat: not every FCR-managed GA4 property is linked to BigQuery. The GA4 BQ linked export has to be explicitly enabled per-property, and many older FCR clients are not on the linked export. If a property isn't in analytics_354147656 / analytics_260259351, you're limited to GA4's own UI or the GA4 Data API. Ask Cathal which properties are linked — the list is maintained outside BigQuery.

9. Common pitfalls

9.1 Impression doubling when joining stats + entity tables

ads_CampaignBasicStats_* has one row per (customer_id, campaign_id, segments_date, segments_ad_network_type, segments_device, segments_slot, ...). If you join to ads_Campaign_* (the entity table) on customer_id + campaign_id only, that's fine — ads_Campaign_* has one row per campaign so no fan-out. But if you accidentally cross-join to another stats table or to a mapping table that has multiple rows per customer, you'll multiply. Always check your row count. The dashboard hit this exact bug on GSC two hours ago (6× duplication from multi-partition merges).

9.2 Cost is in micros

Every cost column is in micros — divide by 1,000,000 for euros: ROUND(metrics_cost_micros / 1e6, 2) AS cost_eur. Forgetting this gives you numbers like €55,830,000 for a €55.83 day.

9.3 Media cost vs retail cost

metrics_cost_micros is media cost — what Google charged the MCC. What the client pays FCR is retail cost = media_cost / (1 - management_fee_decimal). The management fee for each client lives in GoogleAccount_Subscr_MAP_GAds_native.management_fee as a string like "45%". The SUPERSET_MASTER.2_Ads_Data_view already computes this as retail_cost_currency. If you're presenting numbers to anyone client-facing or commercial, use retail. If you're benchmarking efficiency against the Google Ads UI, use media.

9.4 The MIXED network type is Performance Max

segments_ad_network_type = 'MIXED' means the row is from a campaign that served on multiple networks simultaneously — in the FCR mix this is almost always Performance Max. PMax conversion numbers look great next to Search but they also count view-throughs, auto-asset interactions, map clicks, GBP actions and cross-device attribution that Search does not. Before recommending a PMax budget increase, verify conversion-action parity (same actions set to "Include in conversions" on both campaign types) and ask the AM to check client lead quality — PMax is notorious for inflating conversion counts with low-intent events.

9.5 Recent-month conversion lag

The most recent complete month in the BQ transfer will often be a few conversions below the live Google Ads UI for the same month. The transfer is a daily snapshot; conversions continue to re-attribute back to clicks for the full conversion window. Older than ~45 days reconciles exactly. Do not report "conversions are down" on the latest month until at least 14 days after month end.

9.6 The Conversion Stats family doesn't carry clicks/impressions

If you see nulls for metrics_clicks or metrics_impressions in a query that looks correct, check whether you're reading from ads_*ConversionStats_*. Those tables only have conversion metrics. Join to the sibling ads_*Stats_* (without "Conversion") for volume metrics.

9.7 Date filter — partition pruning

BQ scan cost is proportional to bytes read. All ads_* stats tables are partitioned by segments_date, so always put a segments_date filter in the WHERE clause. Without it, a single query can scan tens of GB. Add AND segments_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) or similar even when prototyping.

9.8 customer_id is INT64 on the Ads side, STRING on the mapping side

The ExternalCustomerId column on GoogleAccount_Subscr_MAP_GAds_native is INT64. customer_id on the ads_* tables is also INT64. SUBSCR_ID_Match on the mapping table is STRING. When joining from the subscriber id to the mapping table, quote the literal: WHERE m.SUBSCR_ID_Match = '10597556'. When joining across to the ads tables, you can use the INT64 directly. No casts needed between customer_id and ExternalCustomerId.

9.9 Don't SELECT * from the hourly tables

ads_HourlyCampaignStats_* and friends are huge. Project only the columns you need and filter tightly on segments_date.

10. Quick reference

Most useful tables for day-to-day search marketing analysis

Subscriber mapping tables at a glance

Every mapping uses SUBSCR_ID_Match (STRING, matches active_clients.subscriber_id and SUPERSET_MASTER.1_Core_Listings.extracted_subscriber_id) so once you have a subscriber id, you can fan out across any Google product.

Pre-built views

11. Getting started — suggested first queries

Three warm-up queries to run before your first real analysis:

-- 1. How many customers are active in the last 7 days?
SELECT COUNT(DISTINCT customer_id) AS active_customers
FROM `listingmanager-1529856313699.GOOGLE_ADS_TRANSFER_EU.ads_CampaignStats_3833870882`
WHERE segments_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  AND metrics_impressions > 0;

-- 2. Top 20 accounts by retail spend last month
WITH last_full_month AS (
  SELECT FORMAT_DATE('%Y-%m', DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 DAY)) AS ym
)
SELECT
  subscriber_id,
  ANY_VALUE(subscriber_display) AS account,
  ANY_VALUE(campaign_manager) AS cm,
  ROUND(SUM(retail_cost_currency), 2) AS retail_spend_eur,
  SUM(metrics_clicks) AS clicks,
  SUM(metrics_impressions) AS impressions
FROM `listingmanager-1529856313699.SUPERSET_MASTER.2_Ads_Data_view`, last_full_month
WHERE month_for_sorting = last_full_month.ym
GROUP BY subscriber_id
ORDER BY retail_spend_eur DESC
LIMIT 20;

-- 3. Which conversion action categories dominate the portfolio?
SELECT
  segments_conversion_action_category AS category,
  COUNT(DISTINCT customer_id) AS customers,
  ROUND(SUM(metrics_conversions), 0) AS total_conversions_last_30d
FROM `listingmanager-1529856313699.GOOGLE_ADS_TRANSFER_EU.ads_AccountConversionStats_3833870882`
WHERE segments_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY category
ORDER BY total_conversions_last_30d DESC;

12. Support + next steps

• Schema questions / new tables appearing: the Google Ads Data Transfer schema is versioned and evolves. If a column disappears or a new one shows up, Google's upstream release notes are the source of truth: https://cloud.google.com/bigquery/docs/google-ads-transfer-schema.
• Cost questions: the BQ bill for listingmanager-1529856313699 is visible to the project billing admins. Use partition pruning and LIMIT while prototyping.
• New joins you think should be pre-built: talk to Cathal — we can extend SUPERSET_MASTER with additional views. The existing 2_Ads_Data_view is the template.
• Access issues / credentials: Cathal (cathaldempsey@fcrmedia.com) for API keys, BQ IAM, Claude Code invites.
• Feedback on this doc: this file lives in fcr-dashboard/docs/google-ads-bq-guide.md — raise a PR or tell Cathal what's missing.

FCR Dashboard documentation · generated from docs/ · keep counts verified, not guessed.