BigQuery tables behind Looker studio dashboard for the Search synergies report | Learning Center

If you want to work directly with the data output in BigQuery, getting started is straightforward. Here’s an example of a query you can use to analyse performance metrics from your SEO/Paid search synergy report.

This query retrieves and summarises data, focusing on search term performance by comparing paid and organic metrics. It groups the data by search term and country to provide a clear summary of performance metrics across different search terms and regions.

SELECT 
  date, 
  search_term, 
  country, 
  SUM(paid_clicks) AS total_paid_clicks, 
  SUM(paid_impressions) AS total_paid_impressions, 
  SUM(paid_conversions) AS total_paid_conversions, 
  SUM(cost) AS total_cost, 
  SUM(paid_conversion_value) AS total_paid_conversion_value,       AVG(paid_conversion_value / NULLIF(cost, 0)) AS avg_roas,             SUM(organic_clicks) AS total_organic_clicks, 
  SUM(organic_impressions) AS total_organic_impressions,           AVG(organic_position) AS avg_organic_position, 
  SUM(query_clicks) AS total_query_clicks, 
  SUM(query_impressions) AS total_query_impressions, 
  AVG(query_position) AS avg_query_position 
FROM `project.dataset.search_synergies_report__search_term_opportunities_{table_suffix}` WHERE date >= '2023-01-01' 
GROUP BY 
  date, 
  search_term, 
  country 
ORDER BY 
  total_paid_clicks DESC

Below is the detailed schema of the output tables in BigQuery:

Table 1: search_synergies_report__search_term_opportunities_{table_suffix}

Full name	Type	Description
date	DATE	The date of the record.
search_term	STRING	The search term used.
primary_paid_landing_page	STRING	The primary landing page for paid search.
campaign__name	STRING	The name of the campaign.
market	STRING	The market in which the campaign is run.
ad_group__id	STRING	The ID of the ad group.
device	STRING	The device type (e.g., mobile, desktop).
paid_clicks	FLOAT	The number of clicks from paid search.
paid_impressions	FLOAT	The number of impressions from paid search.
paid_conversions	FLOAT	The number of conversions from paid search.
cost	FLOAT	The cost of paid search.
paid_conversion_value	FLOAT	The conversion value from paid search.
country	STRING	The country where the search term was used.
organic_page	STRING	The landing page for organic search.
organic_clicks	FLOAT	The number of clicks from organic search.
organic_impressions	FLOAT	The number of impressions from organic search.
organic_position	FLOAT	The average position in organic search results.
query_clicks	FLOAT	The total number of clicks from both paid and organic search.
query_impressions	FLOAT	The total number of impressions from both paid and organic search.
query_position	FLOAT	The average position in search results for both paid and organic search.

Table 2: search_synergies_report__cost_{table_suffix}

Full name	Type	Description
date	DATE	The date of the record.
search_term	STRING	The search term used.
device	STRING	The device type (e.g., mobile, desktop).
market	STRING	The market in which the data is collected.
organic_clicks	FLOAT	The number of clicks from organic search.
organic_impressions	FLOAT	The number of impressions from organic search.
country	STRING	The country where the search term was used.
page	STRING	The landing page for the search term.
position	FLOAT	The average position in search results.
est_paid_clicks	FLOAT	The estimated number of clicks from paid search.
est_cost	FLOAT	The estimated cost of paid search.

Table 3: search_synergies_report__landing_page_experience_{table_suffix}

Full name	Type	Description
date	DATE	The date of the record.
search_term	STRING	The search term used.
primary_paid_landing_page	STRING	The primary landing page for paid search.
ad_group__id	STRING	The ID of the ad group.
segments__keyword__info__text	STRING	The keyword text information from segments.
segments__search_term_match_type	STRING	The match type of the search term.
device	STRING	The device type (e.g., mobile, desktop).
campaign__name	STRING	The name of the campaign.
market	STRING	The market in which the campaign is run.
ad_group_criterion__negative	STRING	Indicates if the ad group criterion is negative.
metrics__historical_creative_quality_score	STRING	The historical creative quality score.
metrics__historical_landing_page_quality_score	STRING	The historical landing page quality score.
metrics__historical_search_predicted_ctr	STRING	The historical search predicted click-through rate (CTR).
paid_clicks	FLOAT	The number of clicks from paid search.
paid_impressions	FLOAT	The number of impressions from paid search.
paid_conversions	FLOAT	The number of conversions from paid search.
cost	FLOAT	The cost of paid search.
paid_conversion_value	FLOAT	The conversion value from paid search.
organic_page	STRING	The landing page for organic search.
organic_country	STRING	The country of the organic search.
organic_clicks	FLOAT	The number of clicks from organic search.
organic_impressions	FLOAT	The number of impressions from organic search.
organic_position	FLOAT	The average position in organic search results.
query_clicks	FLOAT	The total number of clicks from both paid and organic search.
query_impressions	FLOAT	The total number of impressions from both paid and organic search.
query_position	FLOAT	The average position in search results for organic search.

Table 4: search_synergies_report__paid_organic_search_term_performance_gads_{table_suffix}

Full name	Type	Description
date	DATE	The date of the record.
campaign__name	STRING	The name of the campaign.
market	STRING	The market in which the campaign is run.
search_engine_results_page_type	STRING	The type of search engine results page (SERP).
search_term	STRING	The search term used.
customer__descriptive_name	STRING	The descriptive name of the customer.
customer__id	STRING	The ID of the customer.
average_cpc	FLOAT	The average cost per click (CPC).
combined_clicks	FLOAT	The total number of clicks from both paid and organic search.
combined_impressions	FLOAT	The total number of impressions from both paid and organic search.
paid_clicks	FLOAT	The number of clicks from paid search.
paid_impressions	FLOAT	The number of impressions from paid search.
organic_clicks	FLOAT	The number of clicks from organic search.
organic_impressions	FLOAT	The number of impressions from organic search.

BigQuery tables behind Looker studio dashboard for the Channel grouping performance report v2

BigQuery tables behind Looker studio for the DV360 performance report

BigQuery tables behind Looker studio dashboard for the Facebook visual creative report

BigQuery tables behind Looker studio dashboard for the Black Friday report

BigQuery tables behind Looker studio dashboard for the Precis starter report