BigQuery tables behind Looker studio dashboard for the Precis starter report | Learning Center

If you want to work with the data output directly in BigQuery, getting started is straightforward. Below is an example query that retrieves the total cost and conversions for a specific conversion name across different platforms:

SELECT    
  platform,    
  SUM(cost),    
  SUM(conversions)  
FROM `project.dataset.reporting_base_all_marketing_data_{table_suffix}`  WHERE date >= '2023-01-01'  
AND conversion_name = "Purchase" 
GROUP BY platform

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

Table 1: reporting_base_all_marketing_data_{table_suffix}

Full name	Type	Description
date	DATE	The date of the record.
currency	STRING	The currency used for financial metrics.
platform	STRING	The platform where the data was collected (e.g., Google Ads, Bing, Facebook).
channel_grouping	STRING	The grouping of marketing channels.
market_name	STRING	The name of the market.
source_currency	STRING	The currency used by the source platform.
account_name	STRING	The name of the advertising account.
account_id	STRING	The ID of the advertising account.
campaign_name	STRING	The name of the campaign.
campaign_id	STRING	The ID of the campaign.
campaign_type	STRING	The type of the campaign.
campaign_sub_type	STRING	The subtype of the campaign.
conversion_name	STRING	The name of the conversion event.
conversion_id	STRING	The ID of the conversion event.
conversion_category	STRING	The category of the conversion event.
impressions	FLOAT	The number of impressions.
viewable_impressions	FLOAT	The number of viewable impressions.
video_views	FLOAT	The number of video views.
clicks	FLOAT	The number of clicks.
cost	FLOAT	The cost of the ads.
conversions	FLOAT	The number of conversions.
conversion_value	FLOAT	The value of conversions.

Table 2: reporting_base_all_analytics_data_{table_suffix}

Full name	Type	Description
date	DATE	The date of the record.
currency	STRING	The currency used for financial metrics.
ga4_migration_date	DATE	The date of migration to Google Analytics 4 (GA4).
platform	STRING	The platform where the data was collected (e.g., website, app).
channel_grouping	STRING	The grouping of marketing channels.
market_name	STRING	The name of the market.
source_currency	STRING	The currency used by the source platform.
account_name	STRING	The name of the advertising account.
account_id	STRING	The ID of the advertising account.
campaign_name	STRING	The name of the campaign.
campaign_id	STRING	The ID of the campaign.
source	STRING	The source of the traffic (e.g., google, facebook).
medium	STRING	The medium of the traffic (e.g., cpc, organic).
source_medium	STRING	The combined source and medium of the traffic.
hostname	STRING	The hostname of the site where the data was collected.
stream_name	STRING	The name of the data stream.
stream_id	STRING	The ID of the data stream.
conversion_name	STRING	The name of the conversion event.
conversion_category	STRING	The category of the conversion event.
conversions	FLOAT	The number of conversions.
conversion_value	FLOAT	The value of conversions.
sessions	FLOAT	The number of sessions.
engagedSessions	FLOAT	The number of engaged sessions.
firstTimePurchasers	FLOAT	The number of first-time purchasers.
newUsers	FLOAT	The number of new users.
screenPageViews	FLOAT	The number of screen page views.

Table 3: reporting_base_{table_suffix}

Schema description for base report.

Full name	Type	Description
date	DATE	The date of the record.
market_name	STRING	The name of the market.
channel_grouping	STRING	The grouping of marketing channels (e.g., gads, bing, facebook).
currency	STRING	The currency used for financial metrics.
ga4_migration_date	DATE	The date of migration to Google Analytics 4 (GA4).
conversion_name	STRING	The name of the conversion event.
conversion_source	STRING	The source of the conversion (e.g., website, app).
source_conv_name	STRING	The original name of the conversion source.
pseudo_cost	FLOAT	The aggregated cost across multiple ad platforms, divided or selected to prevent duplicate rows due to conversion names.
pseudo_clicks	FLOAT	The aggregated number of clicks across multiple ad platforms, divided or selected to prevent duplicate rows.
pseudo_impressions	FLOAT	The aggregated number of impressions across multiple ad platforms, divided or selected to prevent duplicate rows.
pseudo_viewable_impressions	FLOAT	The aggregated number of viewable impressions across multiple ad platforms, divided or selected to prevent duplicate rows.
pseudo_video_views	FLOAT	The aggregated number of video views across multiple ad platforms, divided or selected to prevent duplicate rows.
pseudo_sessions	FLOAT	The aggregated number of sessions across multiple ad platforms, divided or selected to prevent duplicate rows.
pseudo_engagedSessions	FLOAT	The aggregated number of engaged sessions across multiple ad platforms, divided or selected to prevent duplicate rows.
pseudo_firstTimePurchasers	FLOAT	The aggregated number of first-time purchasers across multiple ad platforms, divided or selected to prevent duplicate rows.
pseudo_newUsers	FLOAT	The aggregated number of new users across multiple ad platforms, divided or selected to prevent duplicate rows.
pseudo_screenPageViews	FLOAT	The aggregated number of screen page views across multiple ad platforms, divided or selected to prevent duplicate rows.
conversions	FLOAT	The number of conversions, divided or selected to prevent duplicate rows.
conversion_value	FLOAT	The value of conversions, divided or selected to prevent duplicate rows.
target_roas	INTEGER	The target return on ad spend (ROAS).
target_cpa	INTEGER	The target cost per acquisition (CPA).
daily_budget	FLOAT	The daily budget for the campaign.
daily_target	FLOAT	The daily target for the campaign.

Table 4: reporting_base_gads_{table_suffix}

Full name	Type	Description
date	DATE	The date of the record.
channel_grouping	STRING	The grouping of marketing channels.
market_name	STRING	The name of the market.
account_name	STRING	The name of the advertising account.
customer__id	STRING	The ID of the customer.
source_currency	STRING	The currency used by the source platform.
currency	STRING	The currency used for financial metrics.
campaign_name	STRING	The name of the campaign.
campaign_id	STRING	The ID of the campaign.
advertising_channel_type	STRING	The type of advertising channel (e.g., Search, Display).
conversion_name	STRING	The name of the conversion event.
pseudo_campaign_budgets	FLOAT	The pseudo campaign budgets, when querying, ensure to select only one conversion action or divide by the number of distinct conversion actions to avoid duplicate rows.
pseudo_impressions	FLOAT	The aggregated number of impressions, when querying, ensure to select only one conversion action or divide by the number of distinct conversion actions to avoid duplicate rows.
pseudo_clicks	FLOAT	The aggregated number of clicks, when querying, ensure to select only one conversion action or divide by the number of distinct conversion actions to avoid duplicate rows.
pseudo_cost	FLOAT	The aggregated cost, when querying, ensure to select only one conversion action or divide by the number of distinct conversion actions to avoid duplicate rows.
pseudo_video_views	FLOAT	The aggregated number of video views, when querying, ensure to select only one conversion action or divide by the number of distinct conversion actions to avoid duplicate rows.
search_impression_share	FLOAT	The share of search impressions received.
search_absolute_top_impression_share	FLOAT	The share of absolute top impressions in search results.
search_click_share	FLOAT	The share of clicks received from search impressions.
conversions	FLOAT	The number of conversions.
conversion_value	FLOAT	The value of conversions.
view_through_conversions	FLOAT	The number of view-through conversions.
conversions_by_date	FLOAT	The number of conversions attributed to the date of the interaction.
conversion_value_by_date	FLOAT	The value of conversions attributed to the date of the interaction.

Table 5: reporting_base_bing_{table_suffix}

Full name	Type	Description
date	DATE	The date when the data was recorded or logged.
channel_grouping	STRING	Classification of marketing channels (e.g., organic, paid search, social media).
market_name	STRING	Name of the market or region where the data applies (e.g., US, UK, APAC).
source_currency	STRING	The currency in which the original data or cost was recorded.
customer_id	STRING	Unique identifier for the customer or client within the system.
customer_name	STRING	The name of the customer or client associated with the data.
account_name	STRING	The name of the advertising account being tracked.
campaign_name	STRING	The name of the specific marketing or advertising campaign.
campaign_id	STRING	Unique identifier for the campaign within the advertising platform.
AdDistribution	STRING	Type of ad distribution (e.g., Search, Audience).
Network	STRING	The network or platform where the ad was displayed (e.g., Microsoft sites and select traffic, Syndicated search partners, Bing and Yahoo! search).
conversion_name	STRING	The name given to the conversion event (e.g., 'Purchase', 'Sign Up').
currency	STRING	The currency used for reporting purposes (may differ from the source currency).
pseudo_cost	FLOAT	The aggregated cost. When querying, ensure to select only one conversion action or divide by the number of distinct conversion actions to avoid duplicate rows.
pseudo_impressions	FLOAT	The aggregated total number of impressions. When querying, ensure to select one conversion action or divide by the number of distinct conversion actions to avoid duplication.
pseudo_clicks	FLOAT	The aggregated total number of clicks. When querying, ensure to select one conversion action or divide by the number of distinct conversion actions to avoid duplication.
conversions	FLOAT	The total number of conversion events recorded.
conversion_value	FLOAT	The total value associated with the conversions, based on the predefined conversion value settings.

Table 6: reporting_base_facebook_{table_suffix}

Full name	Type	Description
date	DATE	The date when the data was recorded or logged.
channel_grouping	STRING	Classification of marketing channels (e.g., social media, paid search).
market_name	STRING	Name of the market or region where the data applies (e.g., US, UK, APAC).
account_name	STRING	The name of the Facebook advertising account being tracked.
account_id	STRING	Unique identifier for the Facebook advertising account.
source_currency	STRING	The currency in which the original data or cost was recorded.
currency	STRING	The currency used for reporting purposes (may differ from the source currency).
campaign_name	STRING	The name of the specific marketing or advertising campaign on Facebook.
campaign_id	STRING	Unique identifier for the Facebook campaign.
adset_name	STRING	The name of the ad set within the Facebook campaign.
adset_id	STRING	Unique identifier for the Facebook ad set.
ad_name	STRING	The name of the individual ad within the ad set.
ad_id	STRING	Unique identifier for the Facebook ad.
objective	STRING	The marketing objective of the campaign (e.g., Traffic, Conversions, Lead Generation).
conversion_name	STRING	The name given to the conversion event (e.g., 'Purchase', 'Sign Up').
quality_ranking	FLOAT	Facebook's ad quality ranking, indicating the perceived quality of the ad compared to others targeting the same audience.
pseudo_social_spend	FLOAT	The aggregated spend across Facebook campaigns. When querying, ensure to select only one conversion action or divide by the number of distinct conversion actions to avoid duplicate rows.
pseudo_cost	FLOAT	The aggregated cost. When querying, ensure to select only one conversion action or divide by the number of distinct conversion actions to avoid duplicate rows.
pseudo_clicks	FLOAT	The aggregated total number of clicks. When querying, ensure to select one conversion action or divide by the number of distinct conversion actions to avoid duplication.
pseudo_link_clicks	FLOAT	The aggregated total number of link clicks. When querying, ensure to select one conversion action or divide by the number of distinct conversion actions to avoid duplication.
pseudo_video_views	FLOAT	The aggregated total number of video views. When querying, ensure to select one conversion action or divide by the number of distinct conversion actions to avoid duplication.
pseudo_unique_clicks	FLOAT	The aggregated total number of unique clicks. When querying, ensure to select one conversion action or divide by the number of distinct conversion actions to avoid duplication.
pseudo_impressions	FLOAT	The aggregated total number of impressions. When querying, ensure to select one conversion action or divide by the number of distinct conversion actions to avoid duplication.
pseudo_reach	FLOAT	The aggregated total reach. When querying, ensure to select one conversion action or divide by the number of distinct conversion actions to avoid duplication.
conversions	FLOAT	The total number of conversion events recorded.
conversion_value	FLOAT	The total value associated with the conversions, based on the predefined conversion value settings.

Table 7: reporting_base_linkedin_{table_suffix}

Full name	Type	Description
date	DATE	The date when the data was recorded or logged.
channel_grouping	STRING	Classification of marketing channels (e.g., social media, paid search).
market_name	STRING	Name of the market or region where the data applies (e.g., US, UK, APAC).
source_currency	STRING	The currency in which the original data or cost was recorded.
currency	STRING	The currency used for reporting purposes (may differ from the source currency).
account_id	STRING	Unique identifier for the LinkedIn advertising account.
account_name	STRING	The name of the LinkedIn advertising account being tracked.
campaign_group_id	STRING	Unique identifier for the LinkedIn campaign group.
campaign_group_name	STRING	The name of the campaign group in LinkedIn.
campaign_name	STRING	The name of the specific marketing or advertising campaign on LinkedIn.
campaign_id	STRING	Unique identifier for the LinkedIn campaign.
campaign_type	STRING	The type of LinkedIn campaign (e.g., Sponsored Content, Text Ads).
campaign_objective_type	STRING	The marketing objective of the campaign (e.g., Brand Awareness, Lead Generation).
conversion_name	STRING	The name given to the conversion event (e.g., 'Sign Up', 'Lead Form').
pseudo_videoViews	FLOAT	The aggregated total number of video views. When querying, ensure to select only one conversion action or divide by the number of distinct conversion actions to avoid duplication.
pseudo_totalEngagements	FLOAT	The aggregated total number of engagements (likes, comments, shares). When querying, ensure to select one conversion action or divide by the number of distinct conversion actions to avoid duplication.
pseudo_shares	FLOAT	The aggregated total number of shares. When querying, ensure to select only one conversion action or divide by the number of distinct conversion actions to avoid duplication.
pseudo_cost	FLOAT	The aggregated cost. When querying, ensure to select only one conversion action or divide by the number of distinct conversion actions to avoid duplicate rows.
pseudo_impressions	FLOAT	The aggregated total number of impressions. When querying, ensure to select one conversion action or divide by the number of distinct conversion actions to avoid duplication.
pseudo_clicks	FLOAT	The aggregated total number of clicks. When querying, ensure to select one conversion action or divide by the number of distinct conversion actions to avoid duplication.
conversions	FLOAT	The total number of conversion events recorded.
conversion_value	FLOAT	The total value associated with the conversions, based on the predefined conversion value settings.

Table 8: reporting_base_tiktok_{table_suffix}

Full name	Type	Description
date	DATE	The date when the data was recorded or logged.
channel_grouping	STRING	Classification of marketing channels (e.g., social media, paid search).
market_name	STRING	Name of the market or region where the data applies (e.g., US, UK, APAC).
source_currency	STRING	The currency in which the original data or cost was recorded.
currency	STRING	The currency used for reporting purposes (may differ from the source currency).
account_name	STRING	The name of the TikTok advertising account being tracked.
campaign_type	STRING	The type of TikTok campaign (e.g., Reach, Engagement, Conversion).
campaign_sub_type	STRING	The sub-type of the TikTok campaign (e.g., WEB_CONVERSIONS, TRAFFIC).
account_id	STRING	Unique identifier for the TikTok advertising account.
advertiser_id	STRING	Unique identifier for the advertiser on TikTok.
campaign_name	STRING	The name of the specific marketing or advertising campaign on TikTok.
campaign_id	STRING	Unique identifier for the TikTok campaign.
conversion_name	STRING	The name given to the conversion event (e.g., 'Sign Up', 'Purchase').
pseudo_viewable_impressions	FLOAT	The aggregated total number of viewable impressions. When querying, ensure to select only one conversion action or divide by the number of distinct conversion actions to avoid duplication.
pseudo_impressions	FLOAT	The aggregated total number of impressions. When querying, ensure to select one conversion action or divide by the number of distinct conversion actions to avoid duplication.
pseudo_video_views	FLOAT	The aggregated total number of video views. When querying, ensure to select one conversion action or divide by the number of distinct conversion actions to avoid duplication.
pseudo_clicks	FLOAT	The aggregated total number of clicks. When querying, ensure to select one conversion action or divide by the number of distinct conversion actions to avoid duplication.
pseudo_cost	FLOAT	The aggregated cost. When querying, ensure to select only one conversion action or divide by the number of distinct conversion actions to avoid duplicate rows.
conversions	FLOAT	The total number of conversion events recorded.
conversion_value	FLOAT	The total value associated with the conversions, based on the predefined conversion value settings.

Table 9: reporting_base_ga_{table_suffix}

Full name	Type	Description
date	DATE	The date of the record.
source_currency	STRING	The currency used by the source platform.
currency	STRING	The currency used for financial metrics.
ga4_migration_date	DATE	The date of migration to Google Analytics 4 (GA4).
source	STRING	The source of the traffic (e.g., google, facebook).
medium	STRING	The medium of the traffic (e.g., cpc, organic).
source_medium	STRING	The combined source and medium of the traffic.
campaign	STRING	The name of the campaign.
hostname	STRING	The hostname of the site where the data was collected.
streamName	STRING	The name of the data stream.
streamId	STRING	The ID of the data stream.
market_name	STRING	The name of the market.
channel_grouping	STRING	The grouping of marketing channels.
conversion_name	STRING	The name of the conversion event.
pseudo_sessions	FLOAT	The aggregated number of sessions, when querying, ensure to select only one conversion action or divide by the number of distinct conversion actions to avoid duplicate rows.
pseudo_engagedSessions	FLOAT	The aggregated number of engaged sessions, when querying, ensure to select only one conversion action or divide by the number of distinct conversion actions to avoid duplicate rows.
pseudo_firstTimePurchasers	FLOAT	The aggregated number of first-time purchasers, when querying, ensure to select only one conversion action or divide by the number of distinct conversion actions to avoid duplicate rows.
pseudo_newUsers	FLOAT	The aggregated number of new users, when querying, ensure to select only one conversion action or divide by the number of distinct conversion actions to avoid duplicate rows.
pseudo_screenPageViews	FLOAT	The aggregated number of screen page views, when querying, ensure to select only one conversion action or divide by the number of distinct conversion actions to avoid duplicate rows.
conversions	FLOAT	The number of conversions.
conversion_value	FLOAT	The value of conversions.

BigQuery tables behind Looker studio for the Creative insights report

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

BigQuery tables behind Looker studio dashboard for the Performance Max breakdown report v2

BigQuery tables behind Looker studio dashboard for the Shopping insights report v2

BigQuery tables behind Looker studio dashboard for the Search synergies report