> ## Documentation Index
> Fetch the complete documentation index at: https://docs.nekt.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Google Ads as a data source
> Bring data from Google Ads to Nekt.
Google Ads is Google's online advertising platform that allows businesses to display ads on Google's search results pages and across the Google Display Network. It provides tools for creating, managing, and optimizing digital advertising campaigns to reach target audiences and drive conversions.
## Configuring Google Ads as a Source
In the [Sources](https://app.nekt.ai/sources) tab, click on the "Add source" button located on the top right of your screen. Then, select the Google Ads option from the list of connectors.
Click **Next** and you'll be prompted to add your access.
### 1. Add account access
You'll need to authorize Nekt to access your Google Ads data. Click on the `Google Authorization` button and log in with your Google account. Grant the necessary permissions for the ad accounts you want to extract data from.
The following configurations are available:
* **Customer Account ID**: The Google Ads customer ID from the account you want to fetch data from. Check [Google Ads' documentation](https://support.google.com/google-ads/answer/1704344?hl=en) to discover where you can find this parameter.
* **Start Date**: The earliest date from which records will be synced.
* **Lookback Window**: (Default: 0 days) This parameter allows you to define the time window during which you consider that a conversion was influenced by an ad. For example, sometimes a click conversion happens X days after an ad is launched. The lookback retrieves data from X days ago to ensure it captures all conversions.
* **Manager Account ID**: Fill this in case you authenticated using a manager account that is different from the informed Customer Account ID.
* **Enable Hotel Insights**: (Default: `false`) When enabled, this option unlocks the `Hotel Insights` stream, providing performance data specific to hotel campaigns.
Once you're done, click **Next**.
### 2. Select streams
Choose which data streams you want to sync. For faster extractions, select only the streams that are relevant to your analysis. You can select entire groups of streams or pick specific ones.
> Tip: The stream can be found more easily by typing its name.
Select the streams and click **Next**.
### 3. Configure data streams
Customize how you want your data to appear in your catalog. Select the desired layer where the data will be placed, a folder to organize it inside the layer, a name for each table (which will effectively contain the fetched data) and the type of sync.
* **Layer**: choose between the existing layers on your catalog. This is where you will find your new extracted tables as the extraction runs successfully.
* **Folder**: a folder can be created inside the selected layer to group all tables being created from this new data source.
* **Table name**: we suggest a name, but feel free to customize it. You have the option to add a **prefix** to all tables at once and make this process faster!
* **Sync Type**: you can choose between INCREMENTAL and FULL\_TABLE.
* Incremental: every time the extraction happens, we'll get only the new data - which is good if, for example, you want to keep every record ever fetched.
* Full table: every time the extraction happens, we'll get the current state of the data - which is good if, for example, you don't want to have deleted data in your catalog.
Once you are done configuring, click **Next**.
### 4. Configure data source
Describe your data source for easy identification within your organization, not exceeding 140 characters.
To define your [Trigger](https://docs.nekt.com/runs/scheduling-and-triggers), consider how often you want data to be extracted from this source. This decision usually depends on how frequently you need the new table data updated (every day, once a week, or only at specific times).
Optionally, you can define some additional settings:
* Configure Delta Log Retention and determine for how long we should store old states of this table as it gets updated. Read more about this resource [here](https://docs.nekt.com/get-started/core-concepts/resource-control).
* Determine when to execute an **Additional [Full Sync](https://docs.nekt.com/get-started/core-concepts/types-of-sync#additional-full-sync)**. This will complement the incremental data extractions, ensuring that your data is completely synchronized with your source every once in a while.
Once you are ready, click **Next** to finalize the setup.
### 5. Check your new source
You can view your new source on the [Sources](https://app.nekt.ai/sources) page. If needed, manually trigger the source extraction by clicking on the arrow button. Once executed, your data will appear in your Catalog.
For you to be able to see it on your [Catalog](https://app.nekt.ai/catalog), you need at least one successful source run.
# Streams and Fields
## Available streams
The table below lists every stream, its **slug** (the exact identifier to pass when creating the source via API) and a short description. Streams marked *optional* are only discovered when the corresponding setting is enabled.
| Stream | Slug | Description |
| --------------------------------- | --------------------------------- | --------------------------------------------------------------------------------------- |
| Campaigns | `campaigns` | Ad campaigns and their settings (status, channel type, budget, bidding). |
| Ad Groups | `ad_groups` | Ad groups within campaigns. |
| Ads | `ad` | Individual ads and their metadata and settings. |
| Account Budget | `account_budget` | Account-level budget configuration. |
| Campaign Budget | `campaign_budget` | Budgets assigned to campaigns. |
| Conversion Actions | `conversion_actions` | Conversion actions configured for the account. |
| Campaign Conversion Actions | `campaign_conversion_actions` | Conversion metrics broken down at the campaign level. |
| Ad Group Conversion Actions | `ad_group_conversion_actions` | Conversion metrics broken down at the ad group level. |
| Ad Conversion Actions | `ad_conversion_actions` | Conversion metrics broken down at the ad level. |
| Keywords | `keywords` | Keywords (ad group criteria) and their attributes. |
| Geo Target Constant | `geo_target_constant` | Reference table of geographic targeting constants (locations). |
| Campaign Performance | `campaign_performance` | Daily performance metrics per campaign (impressions, clicks, cost, conversions). |
| Campaign Reach & Frequency | `campaign_reach_frequency` | Reach and frequency metrics per campaign. |
| Ad Group Performance | `ad_group_performance` | Daily performance metrics per ad group. |
| Ad Performance | `ad_performance` | Daily performance metrics per ad. |
| Keyword Performance | `keyword_performance` | Daily performance metrics per keyword. |
| Geographic Performance | `geographic_performance` | Performance metrics broken down by geographic location. |
| User Location Performance | `user_location_performance` | Performance metrics broken down by the user's physical location. |
| Videos | `videos` | Video assets used in campaigns. |
| Video Performance | `video_performance` | Performance metrics for video assets. |
| Campaign Performance (Hourly) | `campaign_performance_hourly` | *Optional (deprecated).* Campaign performance segmented by hour of day. |
| Geographic Performance (Detailed) | `geographic_performance_detailed` | *Optional.* Like `geographic_performance`, additionally broken down by region and city. |
| Hotel Insights | `hotel_insights` | *Optional.* Insights for hotel campaigns. |
## Fields by stream
Below you'll find all available data streams from Google Ads and their corresponding fields:
Stream for managing ad campaigns and their settings.
**Key Fields:**
* `id` - Unique identifier for the campaign
* `name` - Name of the campaign
* `status` - The status of the campaign (e.g., ENABLED, PAUSED, REMOVED)
* `resource_name` - The resource name of the campaign
* `start_date` - The start date of the campaign
* `end_date` - The end date of the campaign
**Advertising Settings:**
* `advertising_channel_type` - The primary channel type (e.g., SEARCH, DISPLAY, VIDEO, SHOPPING)
* `advertising_channel_sub_type` - The sub-type of the advertising channel
* `ad_serving_optimization_status` - Ad serving optimization status
* `primary_status` - The primary status of the campaign
* `primary_status_reasons` - Array of reasons for the primary status
* `serving_status` - The serving status of the campaign
**Budget & Bidding:**
* `campaign_budget` - Reference to the campaign budget
* `bidding_strategy` - Reference to the bidding strategy
* `bidding_strategy_type` - The type of bidding strategy (e.g., TARGET\_CPA, TARGET\_ROAS, MAXIMIZE\_CONVERSIONS)
* `bidding_strategy_system_status` - System status of the bidding strategy
* `accessible_bidding_strategy` - Reference to an accessible bidding strategy
* `target_cpa` - Target CPA settings including:
* `target_cpa_micros` - Target CPA in micros
* `cpc_bid_ceiling_micros` - Maximum CPC bid limit
* `cpc_bid_floor_micros` - Minimum CPC bid limit
* `target_roas` - Target ROAS settings including:
* `target_roas` - Target return on ad spend
* `cpc_bid_ceiling_micros` - Maximum CPC bid limit
* `cpc_bid_floor_micros` - Minimum CPC bid limit
* `target_spend` - Target spend settings
* `maximize_conversions` - Maximize conversions settings
* `maximize_conversion_value` - Maximize conversion value settings
* `target_impression_share` - Target impression share settings
* `target_cpm` - Target CPM settings with frequency goals
* `manual_cpc` - Manual CPC settings with enhanced CPC option
* `percent_cpc` - Percent CPC settings
* `payment_mode` - Payment mode for the campaign
**Network Settings:**
* `network_settings` - Network targeting settings including:
* `target_google_search` - Target Google Search network
* `target_search_network` - Target search network partners
* `target_content_network` - Target display network
* `target_partner_search_network` - Target partner search network
* `target_youtube` - Target YouTube
* `target_google_tv_network` - Target Google TV network
**Geo Targeting:**
* `geo_target_type_setting` - Geo targeting settings including:
* `positive_geo_target_type` - Positive geo target type
* `negative_geo_target_type` - Negative geo target type
**Shopping Settings:**
* `shopping_setting` - Shopping campaign settings including:
* `merchant_id` - Merchant Center ID
* `feed_label` - Feed label
* `campaign_priority` - Campaign priority
* `enable_local` - Enable local inventory ads
* `use_vehicle_inventory` - Use vehicle inventory
* `advertising_partner_ids` - Partner IDs
* `disable_product_feed` - Disable product feed
**App Campaign Settings:**
* `app_campaign_setting` - App campaign settings including:
* `app_id` - App ID
* `app_store` - App store (e.g., GOOGLE\_APP\_STORE, APPLE\_APP\_STORE)
* `bidding_strategy_goal_type` - Bidding strategy goal type
**Dynamic Search Ads Settings:**
* `dynamic_search_ads_setting` - DSA settings including:
* `domain_name` - Domain name
* `language_code` - Language code
* `use_supplied_urls_only` - Use only supplied URLs
**Hotel Settings:**
* `hotel_setting` - Hotel campaign settings
* `hotel_property_asset_set` - Hotel property asset set
**Travel Settings:**
* `travel_campaign_settings` - Travel campaign settings
**Local Campaign Settings:**
* `local_campaign_setting` - Local campaign settings
* `local_services_campaign_settings` - Local services settings
**Performance Max:**
* `performance_max_upgrade` - Performance Max upgrade status
**Advanced Settings:**
* `optimization_score` - Campaign optimization score
* `optimization_goal_setting` - Optimization goal settings
* `experiment_type` - Experiment type
* `frequency_caps` - Frequency capping settings
* `targeting_setting` - Targeting settings with restrictions
* `selective_optimization` - Selective optimization settings
* `real_time_bidding_setting` - Real-time bidding settings
* `demand_gen_campaign_settings` - Demand Gen campaign settings
* `audience_setting` - Audience settings
* `asset_automation_settings` - Asset automation settings
* `vanity_pharma` - Vanity pharma settings
* `video_brand_safety_suitability` - Video brand safety setting
**Tracking & URLs:**
* `tracking_url_template` - Tracking URL template
* `tracking_setting` - Tracking settings
* `final_url_suffix` - Final URL suffix
* `url_custom_parameters` - Custom URL parameters
* `url_expansion_opt_out` - URL expansion opt-out setting
**Labels & Metadata:**
* `labels` - Array of labels attached to the campaign
* `base_campaign` - Base campaign reference
* `campaign_group` - Campaign group reference
* `listing_type` - Listing type
* `excluded_parent_asset_field_types` - Excluded parent asset field types
* `excluded_parent_asset_set_types` - Excluded parent asset set types
Stream for managing ad groups, which control targeting and bidding for a group of ads within a campaign.
**Key Fields:**
* `id` - Unique identifier for the ad group
* `name` - Name of the ad group
* `status` - The status of the ad group (e.g., ENABLED, PAUSED, REMOVED)
* `campaign` - Reference to the parent campaign
* `resource_name` - The resource name of the ad group
* `type` - The type of ad group (e.g., SEARCH\_STANDARD, DISPLAY\_STANDARD)
* `primary_status` - The primary status of the ad group
* `primary_status_reasons` - Array of reasons for the primary status
* `base_ad_group` - Base ad group reference
**Bidding:**
* `cpc_bid_micros` - CPC bid in micros
* `cpm_bid_micros` - CPM bid in micros
* `cpv_bid_micros` - CPV bid in micros
* `percent_cpc_bid_micros` - Percent CPC bid in micros
* `target_cpa_micros` - Target CPA in micros
* `target_cpm_micros` - Target CPM in micros
* `target_roas` - Target return on ad spend
* `effective_target_cpa_micros` - Effective target CPA
* `effective_target_cpa_source` - Source of effective target CPA
* `effective_target_roas` - Effective target ROAS
* `effective_target_roas_source` - Source of effective target ROAS
**Targeting:**
* `targeting_setting` - Targeting settings including:
* `target_restrictions` - Array of targeting restrictions with:
* `targeting_dimension` - Dimension being restricted
* `bid_only` - Whether it's bid-only targeting
* `optimized_targeting_enabled` - Whether optimized targeting is enabled
* `display_custom_bid_dimension` - Custom bid dimension for display
**Rotation & URLs:**
* `ad_rotation_mode` - Ad rotation mode (e.g., OPTIMIZE, ROTATE\_FOREVER)
* `tracking_url_template` - Tracking URL template
* `final_url_suffix` - Final URL suffix
* `url_custom_parameters` - Custom URL parameters
**Labels & Metadata:**
* `labels` - Array of labels attached to the ad group
* `excluded_parent_asset_field_types` - Excluded parent asset field types
* `excluded_parent_asset_set_types` - Excluded parent asset set types
Stream for managing individual ads within an ad group, including all ad formats and creative content.
**Key Fields:**
* `id` - Unique identifier for the ad
* `ad_group` - Reference to the parent ad group
* `status` - The status of the ad (e.g., ENABLED, PAUSED, REMOVED)
* `resource_name` - The resource name of the ad group ad
* `ad_strength` - Ad strength indicator (e.g., EXCELLENT, GOOD, AVERAGE, POOR)
* `primary_status` - The primary status of the ad
* `primary_status_reasons` - Array of reasons for the primary status
* `action_items` - Suggested action items for the ad
**Ad Content (nested under `ad` object):**
*Common Fields:*
* `ad.name` - Name of the ad
* `ad.type` - Type of ad (e.g., RESPONSIVE\_SEARCH\_AD, RESPONSIVE\_DISPLAY\_AD, VIDEO\_AD)
* `ad.resource_name` - Resource name of the ad
* `ad.display_url` - Display URL
* `ad.final_urls` - Array of final URLs
* `ad.final_mobile_urls` - Array of final mobile URLs
* `ad.final_app_urls` - Array of final app URLs
* `ad.final_url_suffix` - Final URL suffix
* `ad.tracking_url_template` - Tracking URL template
* `ad.url_custom_parameters` - Custom URL parameters
* `ad.url_collections` - URL collections
* `ad.added_by_google_ads` - Whether the ad was added by Google Ads
* `ad.device_preference` - Device preference
* `ad.system_managed_resource_source` - System managed resource source
*Responsive Search Ad:*
* `ad.responsive_search_ad.headlines` - Array of headlines with text and performance labels
* `ad.responsive_search_ad.descriptions` - Array of descriptions with text and performance labels
* `ad.responsive_search_ad.path1` - First path
* `ad.responsive_search_ad.path2` - Second path
*Responsive Display Ad:*
* `ad.responsive_display_ad.marketing_images` - Marketing images
* `ad.responsive_display_ad.square_marketing_images` - Square marketing images
* `ad.responsive_display_ad.logo_images` - Logo images
* `ad.responsive_display_ad.square_logo_images` - Square logo images
* `ad.responsive_display_ad.headlines` - Headlines
* `ad.responsive_display_ad.long_headline` - Long headline
* `ad.responsive_display_ad.descriptions` - Descriptions
* `ad.responsive_display_ad.youtube_videos` - YouTube videos
* `ad.responsive_display_ad.business_name` - Business name
* `ad.responsive_display_ad.main_color` - Main color
* `ad.responsive_display_ad.accent_color` - Accent color
* `ad.responsive_display_ad.allow_flexible_color` - Allow flexible color
* `ad.responsive_display_ad.call_to_action_text` - Call to action text
* `ad.responsive_display_ad.price_prefix` - Price prefix
* `ad.responsive_display_ad.promo_text` - Promotional text
* `ad.responsive_display_ad.format_setting` - Format setting
* `ad.responsive_display_ad.control_spec` - Control specifications
*Video Ad:*
* `ad.video_ad.video` - Video asset reference
* `ad.video_ad.in_stream` - In-stream video settings
* `ad.video_ad.bumper` - Bumper ad settings
* `ad.video_ad.out_stream` - Out-stream video settings
* `ad.video_ad.non_skippable` - Non-skippable video settings
* `ad.video_ad.in_feed` - In-feed video settings
*Video Responsive Ad:*
* `ad.video_responsive_ad.headlines` - Headlines
* `ad.video_responsive_ad.long_headlines` - Long headlines
* `ad.video_responsive_ad.descriptions` - Descriptions
* `ad.video_responsive_ad.call_to_actions` - Call to actions
* `ad.video_responsive_ad.videos` - Videos
* `ad.video_responsive_ad.companion_banners` - Companion banners
* `ad.video_responsive_ad.breadcrumb1` - First breadcrumb
* `ad.video_responsive_ad.breadcrumb2` - Second breadcrumb
*Expanded Text Ad (Legacy):*
* `ad.expanded_text_ad.headline_part1` - First headline
* `ad.expanded_text_ad.headline_part2` - Second headline
* `ad.expanded_text_ad.headline_part3` - Third headline
* `ad.expanded_text_ad.description` - First description
* `ad.expanded_text_ad.description2` - Second description
* `ad.expanded_text_ad.path1` - First path
* `ad.expanded_text_ad.path2` - Second path
*Call Ad:*
* `ad.call_ad.country_code` - Country code
* `ad.call_ad.phone_number` - Phone number
* `ad.call_ad.business_name` - Business name
* `ad.call_ad.headline1` - First headline
* `ad.call_ad.headline2` - Second headline
* `ad.call_ad.description1` - First description
* `ad.call_ad.description2` - Second description
* `ad.call_ad.call_tracked` - Whether calls are tracked
* `ad.call_ad.disable_call_conversion` - Disable call conversion
* `ad.call_ad.phone_number_verification_url` - Phone verification URL
* `ad.call_ad.conversion_action` - Conversion action reference
* `ad.call_ad.conversion_reporting_state` - Conversion reporting state
*Image Ad:*
* `ad.image_ad.image_asset` - Image asset reference
* `ad.image_ad.image_url` - Image URL
* `ad.image_ad.pixel_width` - Pixel width
* `ad.image_ad.pixel_height` - Pixel height
* `ad.image_ad.mime_type` - MIME type
* `ad.image_ad.name` - Image name
* `ad.image_ad.preview_image_url` - Preview image URL
* `ad.image_ad.preview_pixel_width` - Preview pixel width
* `ad.image_ad.preview_pixel_height` - Preview pixel height
*App Ads:*
* `ad.app_ad` - App ad content
* `ad.app_engagement_ad` - App engagement ad content
* `ad.app_pre_registration_ad` - App pre-registration ad content
*Shopping Ads:*
* `ad.shopping_product_ad` - Shopping product ad
* `ad.shopping_smart_ad` - Shopping smart ad
* `ad.shopping_comparison_listing_ad` - Shopping comparison listing ad
*Demand Gen Ads:*
* `ad.demand_gen_product_ad` - Demand gen product ad
* `ad.demand_gen_carousel_ad` - Demand gen carousel ad
* `ad.demand_gen_multi_asset_ad` - Demand gen multi-asset ad
* `ad.demand_gen_video_responsive_ad` - Demand gen video responsive ad
*Other Ad Types:*
* `ad.local_ad` - Local ad content
* `ad.smart_campaign_ad` - Smart campaign ad content
* `ad.text_ad` - Legacy text ad content
* `ad.display_upload_ad` - Display upload ad
* `ad.expanded_dynamic_search_ad` - Expanded dynamic search ad
* `ad.legacy_responsive_display_ad` - Legacy responsive display ad
**Policy & Approval:**
* `policy_summary.approval_status` - Approval status
* `policy_summary.review_status` - Review status
**Labels:**
* `labels` - Array of labels attached to the ad
Stream for retrieving daily performance metrics at the campaign level.
**Identifiers:**
* `id` - Unique identifier for the record (hash of campaign\_id + date)
* `campaign_id` - The ID of the campaign
* `campaign_name` - Name of the campaign
* `campaign_status` - Status of the campaign
* `date` - The date of the metrics
**Basic Metrics:**
* `metrics_impressions` - Number of times ads were shown
* `metrics_clicks` - Number of clicks
* `metrics_ctr` - Click-through rate
* `metrics_interactions` - Number of interactions
* `metrics_interaction_rate` - Interaction rate
* `metrics_engagements` - Number of engagements
* `metrics_engagement_rate` - Engagement rate
**Cost Metrics:**
* `metrics_cost_micros` - Total cost in micros (divide by 1,000,000 for actual value)
* `metrics_average_cost` - Average cost per interaction
* `metrics_average_cpc` - Average cost per click
* `metrics_average_cpe` - Average cost per engagement
* `metrics_average_cpm` - Average cost per thousand impressions
* `metrics_average_cpv` - Average cost per view
**Conversion Metrics:**
* `metrics_conversions` - Number of conversions
* `metrics_conversions_value` - Total value of conversions
* `metrics_conversions_by_conversion_date` - Conversions by conversion date
* `metrics_conversions_value_by_conversion_date` - Conversion value by conversion date
* `metrics_conversions_from_interactions_rate` - Conversion rate from interactions
* `metrics_cost_per_conversion` - Cost per conversion
* `metrics_all_conversions` - All conversions (including cross-device)
* `metrics_all_conversions_value` - All conversions value
* `metrics_all_conversions_from_interactions_rate` - All conversions rate from interactions
* `metrics_cost_per_all_conversions` - Cost per all conversions
* `metrics_cross_device_conversions` - Cross-device conversions
* `metrics_view_through_conversions` - View-through conversions
**Value Metrics:**
* `metrics_value_per_conversion` - Value per conversion
* `metrics_value_per_all_conversions` - Value per all conversions
* `metrics_value_per_conversions_by_conversion_date` - Value per conversion by conversion date
* `metrics_value_per_all_conversions_by_conversion_date` - Value per all conversions by conversion date
* `metrics_new_customer_lifetime_value` - New customer lifetime value
* `metrics_all_new_customer_lifetime_value` - All new customer lifetime value
**Search Impression Share:**
* `metrics_search_impression_share` - Search impression share
* `metrics_search_absolute_top_impression_share` - Absolute top impression share
* `metrics_search_top_impression_share` - Top impression share
* `metrics_search_click_share` - Search click share
* `metrics_search_exact_match_impression_share` - Exact match impression share
* `metrics_search_budget_lost_impression_share` - Budget lost impression share
* `metrics_search_budget_lost_absolute_top_impression_share` - Budget lost absolute top impression share
* `metrics_search_budget_lost_top_impression_share` - Budget lost top impression share
* `metrics_search_rank_lost_impression_share` - Rank lost impression share
* `metrics_search_rank_lost_absolute_top_impression_share` - Rank lost absolute top impression share
* `metrics_search_rank_lost_top_impression_share` - Rank lost top impression share
**Content Network Metrics:**
* `metrics_content_impression_share` - Content network impression share
* `metrics_content_budget_lost_impression_share` - Content budget lost impression share
* `metrics_content_rank_lost_impression_share` - Content rank lost impression share
**Position Metrics:**
* `metrics_top_impression_percentage` - Top impression percentage
* `metrics_absolute_top_impression_percentage` - Absolute top impression percentage
**Video Metrics:**
* `metrics_video_views` - Number of video views
* `metrics_video_view_rate` - Video view rate
* `metrics_video_quartile_p25_rate` - 25% quartile rate
* `metrics_video_quartile_p50_rate` - 50% quartile rate
* `metrics_video_quartile_p75_rate` - 75% quartile rate
* `metrics_video_quartile_p100_rate` - 100% quartile rate (completed views)
**Active View Metrics:**
* `metrics_active_view_impressions` - Active View impressions
* `metrics_active_view_cpm` - Active View CPM
* `metrics_active_view_ctr` - Active View CTR
* `metrics_active_view_viewability` - Active View viewability
* `metrics_active_view_measurability` - Active View measurability
* `metrics_active_view_measurable_impressions` - Active View measurable impressions
* `metrics_active_view_measurable_cost_micros` - Active View measurable cost
**Gmail Metrics:**
* `metrics_gmail_forwards` - Gmail forwards
* `metrics_gmail_saves` - Gmail saves
* `metrics_gmail_secondary_clicks` - Gmail secondary clicks
**Phone Metrics:**
* `metrics_phone_calls` - Phone calls
* `metrics_phone_impressions` - Phone impressions
* `metrics_phone_through_rate` - Phone through rate
**Website Engagement:**
* `metrics_average_page_views` - Average page views
* `metrics_average_time_on_site` - Average time on site
* `metrics_bounce_rate` - Bounce rate
* `metrics_percent_new_visitors` - Percent new visitors
**Shopping & Commerce:**
* `metrics_orders` - Number of orders
* `metrics_average_cart_size` - Average cart size
* `metrics_average_order_value_micros` - Average order value in micros
* `metrics_units_sold` - Units sold
* `metrics_revenue_micros` - Revenue in micros
* `metrics_cost_of_goods_sold_micros` - Cost of goods sold
* `metrics_gross_profit_micros` - Gross profit
* `metrics_gross_profit_margin` - Gross profit margin
* `metrics_cross_sell_revenue_micros` - Cross-sell revenue
* `metrics_cross_sell_units_sold` - Cross-sell units sold
* `metrics_lead_revenue_micros` - Lead revenue
* `metrics_lead_units_sold` - Lead units sold
**Attribution:**
* `metrics_current_model_attributed_conversions` - Current model attributed conversions
* `metrics_current_model_attributed_conversions_value` - Current model attributed conversions value
* `metrics_cost_per_current_model_attributed_conversion` - Cost per current model attributed conversion
**Click Quality:**
* `metrics_invalid_clicks` - Invalid clicks
* `metrics_invalid_click_rate` - Invalid click rate
* `metrics_relative_ctr` - Relative CTR
**Mobile & App:**
* `metrics_sk_ad_network_installs` - SKAdNetwork installs
* `metrics_sk_ad_network_total_conversions` - SKAdNetwork total conversions
Stream for retrieving daily performance metrics at the ad group level.
**Identifiers:**
* `id` - Unique identifier for the record (hash of campaign\_id + ad\_group\_id + date)
* `campaign_id` - The ID of the campaign
* `campaign_name` - Name of the campaign
* `ad_group_id` - The ID of the ad group
* `ad_group_name` - Name of the ad group
* `date` - The date of the metrics
**Metrics:**
All metrics from Campaign Performance are available at the ad group level, prefixed with `metrics_`. This allows for granular analysis of performance within each ad group.
Key metrics include:
* `metrics_impressions`, `metrics_clicks`, `metrics_ctr`
* `metrics_cost_micros`, `metrics_average_cpc`, `metrics_average_cpm`
* `metrics_conversions`, `metrics_conversions_value`, `metrics_cost_per_conversion`
* `metrics_video_views`, `metrics_video_view_rate`
* All impression share and position metrics
* All conversion and value metrics
Stream for retrieving daily performance metrics at the individual ad level.
**Identifiers:**
* `id` - Unique identifier for the record (hash of campaign\_id + ad\_group\_id + ad\_id + date)
* `campaign_id` - The ID of the campaign
* `campaign_name` - Name of the campaign
* `campaign_start_date` - Campaign start date
* `campaign_end_date` - Campaign end date
* `campaign_status` - Campaign status
* `ad_group_id` - The ID of the ad group
* `ad_group_name` - Name of the ad group
* `ad_group_ad_ad_id` - The ID of the ad
* `ad_group_ad_ad_name` - Name of the ad
* `date` - The date of the metrics
**Metrics:**
All metrics from Campaign Performance are available at the ad level, prefixed with `metrics_`. This provides the most granular view of ad performance.
Key metrics include:
* `metrics_impressions`, `metrics_clicks`, `metrics_ctr`
* `metrics_cost_micros`, `metrics_average_cpc`, `metrics_average_cpm`
* `metrics_conversions`, `metrics_conversions_value`, `metrics_cost_per_conversion`
* `metrics_video_views`, `metrics_video_view_rate`, `metrics_video_quartile_p*_rate`
* All engagement and interaction metrics
* All conversion and value metrics
Stream for retrieving performance metrics specific to hotel campaigns. This stream is only available when "Enable Hotel Insights" is turned on in the source configuration.
**Identifiers:**
* `id` - Unique identifier for the record
* `date` - The date of the metrics
* `hotel_performance_view_resource_name` - Resource name of the hotel performance view
**Hotel Segments:**
* `segments_hotel_booking_window_days` - Number of days between booking and check-in
* `segments_hotel_center_id` - Hotel Center ID
* `segments_hotel_check_in_date` - Check-in date
* `segments_hotel_check_in_day_of_week` - Day of week for check-in
* `segments_hotel_city` - City of the hotel
* `segments_hotel_class` - Hotel class (star rating)
* `segments_hotel_country` - Country of the hotel
* `segments_hotel_date_selection_type` - Date selection type
* `segments_hotel_length_of_stay` - Length of stay in days
* `segments_hotel_state` - State/region of the hotel
**Hotel-Specific Metrics:**
* `metrics_hotel_average_lead_value_micros` - Average lead value for hotel
* `metrics_hotel_eligible_impressions` - Hotel eligible impressions
* `metrics_hotel_price_difference_percentage` - Price difference percentage
**Standard Metrics:**
* `metrics_impressions`, `metrics_clicks`, `metrics_ctr`
* `metrics_cost_micros`, `metrics_average_cpc`, `metrics_average_cpm`
* `metrics_conversions`, `metrics_conversions_value`, `metrics_cost_per_conversion`
* `metrics_all_conversions`, `metrics_all_conversions_value`
* `metrics_cross_device_conversions`
* All search impression share metrics
* `metrics_value_per_conversion`, `metrics_value_per_all_conversions`
Stream containing all conversion actions configured in your Google Ads account. Conversion actions define the types of conversions you want to track.
**Key Fields:**
* `id` - Unique identifier for the conversion action
* `name` - Name of the conversion action
* `status` - Status of the conversion action (e.g., ENABLED, REMOVED)
* `type` - Type of conversion action (e.g., WEBPAGE, PHONE\_CALL, UPLOAD\_CLICKS)
* `category` - Category of the conversion (e.g., PURCHASE, LEAD, SIGN\_UP)
* `primary_for_goal` - Whether this is a primary conversion for the goal
* `include_in_conversions_metric` - Whether this action is included in the main conversions metric
* `origin` - Origin of the conversion action (e.g., WEBSITE, APP)
Stream containing campaign budget configurations and recommendations.
**Key Fields:**
* `id` - Unique identifier for the budget
* `name` - Name of the budget
* `resource_name` - The resource name of the budget
* `status` - Status of the budget (e.g., ENABLED, REMOVED)
* `type` - Type of budget (e.g., STANDARD, FIXED\_CPA)
* `period` - Period for the budget (e.g., DAILY)
* `delivery_method` - Delivery method (e.g., STANDARD, ACCELERATED)
**Budget Amounts:**
* `amount_micros` - Budget amount in micros (divide by 1,000,000 for actual value)
* `total_amount_micros` - Total budget amount in micros
**Sharing & Reference:**
* `explicitly_shared` - Whether the budget is explicitly shared across campaigns
* `reference_count` - Number of campaigns using this budget
* `aligned_bidding_strategy_id` - ID of the aligned bidding strategy
**Budget Recommendations:**
* `has_recommended_budget` - Whether Google has a budget recommendation
* `recommended_budget_amount_micros` - Recommended budget amount in micros
* `recommended_budget_estimated_change_weekly_clicks` - Estimated weekly click change with recommended budget
* `recommended_budget_estimated_change_weekly_cost_micros` - Estimated weekly cost change
* `recommended_budget_estimated_change_weekly_interactions` - Estimated weekly interaction change
* `recommended_budget_estimated_change_weekly_views` - Estimated weekly view change
Stream for retrieving conversion metrics broken down by conversion action at the campaign level. This allows you to analyze which types of conversions each campaign is driving.
**Identifiers:**
* `id` - Unique identifier for the record (hash of campaign\_id + date + conversion\_action)
* `campaign_id` - The ID of the campaign
* `campaign_name` - Name of the campaign
* `campaign_status` - Status of the campaign
* `date` - The date of the metrics
* `conversion_action_category` - Category of the conversion action
* `conversion_action_name` - Name of the conversion action
**Conversion Metrics:**
* `metrics_conversions` - Number of conversions
* `metrics_conversions_by_conversion_date` - Conversions by conversion date
* `metrics_conversions_value` - Total value of conversions
* `metrics_conversions_value_by_conversion_date` - Conversion value by conversion date
* `metrics_all_conversions` - All conversions (including cross-device)
* `metrics_all_conversions_by_conversion_date` - All conversions by conversion date
* `metrics_all_conversions_value` - All conversions value
* `metrics_all_conversions_value_by_conversion_date` - All conversions value by conversion date
**Value Metrics:**
* `metrics_value_per_conversion` - Value per conversion
* `metrics_value_per_conversions_by_conversion_date` - Value per conversion by conversion date
* `metrics_value_per_all_conversions` - Value per all conversions
* `metrics_value_per_all_conversions_by_conversion_date` - Value per all conversions by conversion date
* `metrics_view_through_conversions` - View-through conversions
Stream for retrieving conversion metrics broken down by conversion action at the ad group level. This provides granular insight into which ad groups are driving specific types of conversions.
**Identifiers:**
* `id` - Unique identifier for the record (hash of campaign\_id + ad\_group\_id + date + conversion\_action)
* `campaign_id` - The ID of the campaign
* `campaign_name` - Name of the campaign
* `ad_group_id` - The ID of the ad group
* `ad_group_name` - Name of the ad group
* `date` - The date of the metrics
* `conversion_action_category` - Category of the conversion action
**Conversion Metrics:**
* `metrics_conversions` - Number of conversions
* `metrics_conversions_by_conversion_date` - Conversions by conversion date
* `metrics_conversions_value` - Total value of conversions
* `metrics_conversions_value_by_conversion_date` - Conversion value by conversion date
* `metrics_all_conversions` - All conversions (including cross-device)
* `metrics_all_conversions_by_conversion_date` - All conversions by conversion date
* `metrics_all_conversions_value` - All conversions value
* `metrics_all_conversions_value_by_conversion_date` - All conversions value by conversion date
**Value Metrics:**
* `metrics_value_per_conversion` - Value per conversion
* `metrics_value_per_conversions_by_conversion_date` - Value per conversion by conversion date
* `metrics_value_per_all_conversions` - Value per all conversions
* `metrics_value_per_all_conversions_by_conversion_date` - Value per all conversions by conversion date
* `metrics_view_through_conversions` - View-through conversions
Stream for retrieving conversion metrics broken down by conversion action at the individual ad level. This provides the most granular view of which ads are driving specific types of conversions.
**Identifiers:**
* `id` - Unique identifier for the record
* `campaign_id` - The ID of the campaign
* `campaign_name` - Name of the campaign
* `campaign_start_date` - Campaign start date
* `campaign_end_date` - Campaign end date
* `campaign_status` - Campaign status
* `ad_group_id` - The ID of the ad group
* `ad_group_name` - Name of the ad group
* `ad_group_ad_ad_id` - The ID of the ad
* `ad_group_ad_ad_name` - Name of the ad
* `date` - The date of the metrics
* `conversion_action_category` - Category of the conversion action
**Conversion Metrics:**
* `metrics_conversions` - Number of conversions
* `metrics_conversions_by_conversion_date` - Conversions by conversion date
* `metrics_conversions_value` - Total value of conversions
* `metrics_conversions_value_by_conversion_date` - Conversion value by conversion date
* `metrics_all_conversions` - All conversions (including cross-device)
* `metrics_all_conversions_by_conversion_date` - All conversions by conversion date
* `metrics_all_conversions_value` - All conversions value
* `metrics_all_conversions_value_by_conversion_date` - All conversions value by conversion date
**Value Metrics:**
* `metrics_value_per_conversion` - Value per conversion
* `metrics_value_per_conversions_by_conversion_date` - Value per conversion by conversion date
* `metrics_value_per_all_conversions` - Value per all conversions
* `metrics_value_per_all_conversions_by_conversion_date` - Value per all conversions by conversion date
* `metrics_view_through_conversions` - View-through conversions
# Data Model
The following diagram illustrates the relationships between the core data streams in Google Ads. The arrows indicate the join keys that link the different entities, providing a clear overview of the data structure.
```mermaid theme={null}
graph TD;
subgraph "Core Entities"
Campaigns("Campaigns");
AdGroups("Ad Groups");
Ads("Ads");
CampaignBudget("Campaign Budget");
ConversionActions("Conversion Actions");
end
subgraph "Performance Data"
CampaignPerformance("Campaign Performance");
AdGroupPerformance("Ad Group Performance");
AdPerformance("Ad Performance");
HotelInsights("Hotel Insights");
end
subgraph "Conversion Action Reports"
CampaignConvActions("Campaign Conversion Actions");
AdGroupConvActions("Ad Group Conversion Actions");
AdConvActions("Ad Conversion Actions");
end
AdGroups -- "campaign" --> Campaigns;
Ads -- "ad_group" --> AdGroups;
Campaigns -- "campaign_budget" --> CampaignBudget;
CampaignPerformance -- "campaign_id" --> Campaigns;
AdGroupPerformance -- "campaign_id" --> Campaigns;
AdGroupPerformance -- "ad_group_id" --> AdGroups;
AdPerformance -- "campaign_id" --> Campaigns;
AdPerformance -- "ad_group_id" --> AdGroups;
AdPerformance -- "ad_group_ad_ad_id" --> Ads;
CampaignConvActions -- "campaign_id" --> Campaigns;
CampaignConvActions -- "conversion_action_name" --> ConversionActions;
AdGroupConvActions -- "campaign_id" --> Campaigns;
AdGroupConvActions -- "ad_group_id" --> AdGroups;
AdConvActions -- "campaign_id" --> Campaigns;
AdConvActions -- "ad_group_id" --> AdGroups;
AdConvActions -- "ad_group_ad_ad_id" --> Ads;
```
# Use Cases for Data Analysis
This guide outlines valuable business intelligence use cases when consolidating Google Ads data, along with ready-to-use SQL queries that you can run on [Explorer](https://app.nekt.ai/explorer).
### 1. Campaign Performance Overview
Track the overall performance of your campaigns with key metrics including spend, conversions, and efficiency ratios.
**Business Value:**
* Identify which campaigns deliver the best return on investment
* Compare performance across different campaign types
* Optimize budget allocation based on efficiency metrics
```sql theme={null}
WITH campaign_metrics AS (
SELECT
c.name AS campaign_name,
c.advertising_channel_type,
c.status AS campaign_status,
c.bidding_strategy_type,
SUM(cp.metrics_impressions) AS total_impressions,
SUM(cp.metrics_clicks) AS total_clicks,
SUM(cp.metrics_cost_micros) / 1000000.0 AS total_spend,
SUM(cp.metrics_conversions) AS total_conversions,
SUM(cp.metrics_conversions_value) AS total_conversion_value,
AVG(cp.metrics_ctr) AS avg_ctr,
SUM(cp.metrics_cost_micros) / NULLIF(SUM(cp.metrics_clicks), 0) / 1000000.0 AS avg_cpc,
SUM(cp.metrics_cost_micros) / NULLIF(SUM(cp.metrics_conversions), 0) / 1000000.0 AS cost_per_conversion,
SUM(cp.metrics_conversions_value) / NULLIF(SUM(cp.metrics_cost_micros) / 1000000.0, 0) AS roas
FROM
nekt_raw.google_ads_campaigns c
LEFT JOIN nekt_raw.google_ads_campaign_performance cp
ON CAST(c.id AS VARCHAR) = cp.campaign_id
WHERE
cp.date >= CURRENT_DATE - INTERVAL '30' DAY
GROUP BY
c.name,
c.advertising_channel_type,
c.status,
c.bidding_strategy_type
)
SELECT
campaign_name,
advertising_channel_type,
campaign_status,
bidding_strategy_type,
total_impressions,
total_clicks,
ROUND(total_spend, 2) AS total_spend,
CAST(total_conversions AS INTEGER) AS total_conversions,
ROUND(total_conversion_value, 2) AS total_conversion_value,
ROUND(avg_ctr * 100, 2) AS ctr_percent,
ROUND(avg_cpc, 2) AS avg_cpc,
ROUND(cost_per_conversion, 2) AS cost_per_conversion,
ROUND(roas, 2) AS roas
FROM
campaign_metrics
ORDER BY
total_spend DESC
```
```sql theme={null}
WITH
campaign_metrics AS (
SELECT
c.name AS campaign_name,
c.advertising_channel_type,
c.status AS campaign_status,
c.bidding_strategy_type,
-- Sum core metrics
SUM(cp.metrics_impressions) AS total_impressions,
SUM(cp.metrics_clicks) AS total_clicks,
-- Convert cost_micros to standard currency (BigQuery handles division on BigInts smoothly)
SUM(cp.metrics_cost_micros) / 1000000.0 AS total_spend,
SUM(cp.metrics_conversions) AS total_conversions,
SUM(cp.metrics_conversions_value) AS total_conversion_value,
-- Average of daily CTRs
AVG(cp.metrics_ctr) AS avg_ctr,
-- Calculated average CPC: Total Cost / Total Clicks. Using SAFE_DIVIDE for BigQuery.
SAFE_DIVIDE (
SUM(cp.metrics_cost_micros),
SUM(cp.metrics_clicks)
) / 1000000.0 AS avg_cpc,
-- Calculated Cost Per Conversion: Total Cost / Total Conversions.
SAFE_DIVIDE (
SUM(cp.metrics_cost_micros),
SUM(cp.metrics_conversions)
) / 1000000.0 AS cost_per_conversion,
-- Calculated ROAS: Conversion Value / Total Spend.
SAFE_DIVIDE (
SUM(cp.metrics_conversions_value),
SUM(cp.metrics_cost_micros) / 1000000.0
) AS roas
FROM
`nekt_raw.google_ads_campaigns` c
LEFT JOIN `nekt_raw.google_ads_campaign_performance` cp ON CAST(c.id AS STRING) = cp.campaign_id
WHERE
DATE(cp.date) >= DATE_SUB (CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY
1,
2,
3,
4
)
SELECT
campaign_name,
advertising_channel_type,
campaign_status,
bidding_strategy_type,
total_impressions,
total_clicks,
ROUND(total_spend, 2) AS total_spend,
CAST(total_conversions AS INT64) AS total_conversions,
ROUND(total_conversion_value, 2) AS total_conversion_value,
ROUND(avg_ctr * 100, 2) AS ctr_percent,
ROUND(avg_cpc, 2) AS avg_cpc,
ROUND(cost_per_conversion, 2) AS cost_per_conversion,
ROUND(roas, 2) AS roas
FROM
campaign_metrics
ORDER BY
total_spend DESC
```
| campaign\_name | advertising\_channel\_type | campaign\_status | bidding\_strategy\_type | total\_impressions | total\_clicks | total\_spend | total\_conversions | total\_conversion\_value | ctr\_percent | avg\_cpc | cost\_per\_conversion | roas |
| -------------------- | -------------------------- | ---------------- | ----------------------- | ------------------ | ------------- | ------------ | ------------------ | ------------------------ | ------------ | -------- | --------------------: | ---: |
| Summer Sale 2024 | SEARCH | ENABLED | TARGET\_ROAS | 1,245,890 | 42,156 | 8,542.30 | 856 | 42,800.00 | 3.38 | 0.20 | 9.98 | 5.01 |
| Brand Awareness | DISPLAY | ENABLED | TARGET\_CPA | 3,892,450 | 28,430 | 5,686.00 | 312 | 9,360.00 | 0.73 | 0.20 | 18.22 | 1.65 |
| Product Launch Q4 | SEARCH | ENABLED | MAXIMIZE\_CONVERSIONS | 892,340 | 31,245 | 4,998.50 | 423 | 21,150.00 | 3.50 | 0.16 | 11.82 | 4.23 |
| Retargeting Campaign | DISPLAY | ENABLED | TARGET\_CPA | 2,145,670 | 18,920 | 3,784.00 | 245 | 12,250.00 | 0.88 | 0.20 | 15.44 | 3.24 |
| Video Promos | VIDEO | PAUSED | MAXIMIZE\_CONVERSIONS | 567,230 | 8,456 | 1,691.20 | 89 | 2,670.00 | 1.49 | 0.20 | 19.00 | 1.58 |
### 2. Time-Based Performance Trends
Track performance trends over time to identify patterns, seasonality, and optimization opportunities.
**Business Value:**
* Understand daily and weekly performance patterns
* Identify performance trends and anomalies
* Optimize campaign scheduling and budget pacing
* Compare current performance against historical averages
```sql theme={null}
WITH daily_metrics AS (
SELECT
DATE(cp.date) AS report_date,
DATE_FORMAT(DATE(cp.date), '%W') AS day_of_week,
DATE_FORMAT(DATE(cp.date), '%Y-%m') AS month,
SUM(cp.metrics_impressions) AS impressions,
SUM(cp.metrics_clicks) AS clicks,
SUM(cp.metrics_cost_micros) / 1000000.0 AS spend,
SUM(cp.metrics_conversions) AS conversions,
SUM(cp.metrics_conversions_value) AS conversion_value,
COUNT(DISTINCT cp.campaign_id) AS active_campaigns
FROM
nekt_raw.google_ads_campaign_performance cp
WHERE
cp.date >= CURRENT_DATE - INTERVAL '30' DAY
GROUP BY
DATE(cp.date),
DATE_FORMAT(DATE(cp.date), '%W'),
DATE_FORMAT(DATE(cp.date), '%Y-%m')
),
weekly_averages AS (
SELECT
day_of_week,
AVG(impressions) AS avg_impressions,
AVG(clicks) AS avg_clicks,
AVG(spend) AS avg_spend,
AVG(conversions) AS avg_conversions,
AVG(conversion_value) AS avg_conversion_value
FROM
daily_metrics
GROUP BY
day_of_week
),
daily_with_comparison AS (
SELECT
dm.*,
wa.avg_spend AS typical_daily_spend,
wa.avg_conversions AS typical_daily_conversions,
ROUND((dm.spend - wa.avg_spend) / NULLIF(wa.avg_spend, 0) * 100, 1) AS spend_vs_typical_pct,
ROUND((dm.conversions - wa.avg_conversions) / NULLIF(wa.avg_conversions, 0) * 100, 1) AS conversions_vs_typical_pct,
-- Calculate 7-day moving averages
AVG(dm.spend) OVER (ORDER BY dm.report_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS spend_7day_avg,
AVG(dm.conversions) OVER (ORDER BY dm.report_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS conversions_7day_avg,
-- Calculate CPA and ROAS
dm.spend / NULLIF(dm.conversions, 0) AS cpa,
dm.conversion_value / NULLIF(dm.spend, 0) AS roas
FROM
daily_metrics dm
JOIN weekly_averages wa ON dm.day_of_week = wa.day_of_week
)
SELECT
report_date,
day_of_week,
impressions,
clicks,
ROUND(spend, 2) AS spend,
ROUND(conversions, 2) AS conversions,
ROUND(conversion_value, 2) AS conversion_value,
active_campaigns,
ROUND(cpa, 2) AS cpa,
ROUND(roas, 2) AS roas,
ROUND(spend_7day_avg, 2) AS spend_7day_avg,
ROUND(conversions_7day_avg, 2) AS conversions_7day_avg,
spend_vs_typical_pct,
conversions_vs_typical_pct,
CASE
WHEN conversions_vs_typical_pct > 20 THEN 'Above Average'
WHEN conversions_vs_typical_pct < -20 THEN 'Below Average'
ELSE 'Normal'
END AS performance_indicator
FROM
daily_with_comparison
ORDER BY
report_date DESC
```
```sql theme={null}
WITH
daily_metrics AS (
SELECT
DATE(cp.date) AS report_date,
FORMAT_DATE ('%A', DATE(cp.date)) AS day_of_week,
FORMAT_DATE ('%Y-%m', DATE(cp.date)) AS MONTH,
SUM(cp.metrics_impressions) AS impressions,
SUM(cp.metrics_clicks) AS clicks,
SUM(cp.metrics_cost_micros) / 1000000.0 AS spend,
SUM(cp.metrics_conversions) AS conversions,
SUM(cp.metrics_conversions_value) AS conversion_value,
COUNT(DISTINCT cp.campaign_id) AS active_campaigns
FROM
`nekt_raw.google_ads_campaign_performance` cp
WHERE
DATE(cp.date) >= DATE_SUB (CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY
1,
2,
3
),
weekly_averages AS (
SELECT
day_of_week,
AVG(impressions) AS avg_impressions,
AVG(clicks) AS avg_clicks,
AVG(spend) AS avg_spend,
AVG(conversions) AS avg_conversions,
AVG(conversion_value) AS avg_conversion_value
FROM
daily_metrics
GROUP BY
day_of_week
),
daily_with_comparison AS (
SELECT
dm.*,
wa.avg_spend AS typical_daily_spend,
wa.avg_conversions AS typical_daily_conversions,
ROUND(
SAFE_DIVIDE (dm.spend - wa.avg_spend, wa.avg_spend) * 100,
1
) AS spend_vs_typical_pct,
ROUND(
SAFE_DIVIDE (
dm.conversions - wa.avg_conversions,
wa.avg_conversions
) * 100,
1
) AS conversions_vs_typical_pct,
AVG(dm.spend) OVER (
ORDER BY
dm.report_date ROWS BETWEEN 6 PRECEDING
AND CURRENT ROW
) AS spend_7day_avg,
AVG(dm.conversions) OVER (
ORDER BY
dm.report_date ROWS BETWEEN 6 PRECEDING
AND CURRENT ROW
) AS conversions_7day_avg,
SAFE_DIVIDE (dm.spend, dm.conversions) AS cpa,
SAFE_DIVIDE (dm.conversion_value, dm.spend) AS roas
FROM
daily_metrics dm
JOIN weekly_averages wa ON dm.day_of_week = wa.day_of_week
)
SELECT
report_date,
day_of_week,
impressions,
clicks,
ROUND(spend, 2) AS spend,
ROUND(conversions, 2) AS conversions,
ROUND(conversion_value, 2) AS conversion_value,
active_campaigns,
ROUND(cpa, 2) AS cpa,
ROUND(roas, 2) AS roas,
ROUND(spend_7day_avg, 2) AS spend_7day_avg,
ROUND(conversions_7day_avg, 2) AS conversions_7day_avg,
spend_vs_typical_pct,
conversions_vs_typical_pct,
CASE
WHEN conversions_vs_typical_pct > 20 THEN 'Above Average'
WHEN conversions_vs_typical_pct < -20 THEN 'Below Average'
ELSE 'Normal'
END AS performance_indicator
FROM
daily_with_comparison
ORDER BY
report_date DESC
```
| report\_date | day\_of\_week | impressions | clicks | spend | conversions | conversion\_value | active\_campaigns | cpa | roas | spend\_7day\_avg | conversions\_7day\_avg | spend\_vs\_typical\_pct | conversions\_vs\_typical\_pct | performance\_indicator |
| ------------ | ------------- | ----------- | ------ | -------- | ----------- | ----------------- | ----------------- | ----- | ---- | ---------------- | ---------------------- | ----------------------- | ----------------------------- | ---------------------- |
| 2024-11-27 | Wednesday | 156,234 | 5,892 | 1,178.40 | 78 | 3,900.00 | 8 | 15.11 | 3.31 | 1,145.20 | 72.5 | 4.2 | 12.8 | Normal |
| 2024-11-26 | Tuesday | 148,920 | 5,234 | 1,046.80 | 65 | 3,250.00 | 8 | 16.10 | 3.10 | 1,132.50 | 70.2 | -2.5 | -8.3 | Normal |
| 2024-11-25 | Monday | 142,560 | 4,987 | 997.40 | 58 | 2,900.00 | 8 | 17.20 | 2.91 | 1,125.80 | 68.4 | -7.8 | -18.5 | Normal |
| 2024-11-24 | Sunday | 98,450 | 3,245 | 649.00 | 42 | 2,100.00 | 7 | 15.45 | 3.24 | 1,098.60 | 65.8 | -22.4 | -28.6 | Below Average |
| 2024-11-23 | Saturday | 112,340 | 3,890 | 778.00 | 52 | 2,600.00 | 7 | 14.96 | 3.34 | 1,112.40 | 67.2 | -6.8 | -11.2 | Normal |
| 2024-11-22 | Friday | 178,560 | 6,234 | 1,246.80 | 95 | 4,750.00 | 8 | 13.12 | 3.81 | 1,156.30 | 74.8 | 8.5 | 32.4 | Above Average |
This analysis provides valuable insights into:
* Daily performance trends with moving averages
* Day-of-week patterns for optimization
* Performance against typical metrics
* CPA and ROAS trends over time
You can use these insights to:
* Adjust campaign scheduling for high-performing days
* Optimize budget allocation across the week
* Identify and investigate performance anomalies
* Improve budget pacing strategies
## Implementation Notes
### Data Quality Considerations
* All cost and value metrics are provided in micros (divide by 1,000,000 for actual values in queries)
* Use the lookback window configuration to ensure conversion data is complete before analysis
* For accurate trend analysis, ensure you have at least 30 days of data
### API Limits & Performance
* Selecting all streams and metrics can increase extraction times
* For faster extractions, select only the streams necessary for your analysis
* The Hotel Insights stream is only available for hotel campaigns and requires enabling it in the source configuration
## Skills for agents
Google Ads connector documentation as plain markdown, for use in AI agent contexts.