> ## 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.
# Microsoft Ads as a data source
> Bring data from Microsoft Ads to Nekt.
Microsoft Ads (formerly Bing Ads) is Microsoft's advertising platform that allows businesses to create and manage digital advertising campaigns across the Microsoft Search Network, including Bing, Yahoo, and partner sites. It provides tools for targeting audiences, tracking performance, and optimizing ad spend for maximum return on investment.
## Configuring Microsoft 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 Microsoft 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 Microsoft Ads data. Click on the `Microsoft Authorization` button and log in with your Microsoft account. Grant the necessary permissions for the ad accounts you want to extract data from.
If you are already logged in on a Microsoft Ads account that is different from the one you want to sync data from, make sure to switch to the correct account before authorizing Nekt.
The following configurations are required:
* **Manager Account ID**: The Manager Account ID identifies the specific manager account you want to sync data from. See the [Finding Your Manager Account ID and Customer ID](#finding-your-manager-account-id-and-customer-id) section below for detailed instructions.
* **Customer ID**: The Customer ID identifies your account in Microsoft Ads. This is different from the Manager Account ID and is required for API authentication. See the [Finding Your Manager Account ID and Customer ID](#finding-your-manager-account-id-and-customer-id) section below for detailed instructions.
* **Report Timezone**: Select the timezone to use for report generation. This determines how dates and times are recorded in your performance reports.
* **Start Date**: (Optional) The earliest date from which records will be synced. Records created or updated after this date will be extracted.
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 a name for each table (which will contain the fetched data) and the type of sync.
* **Table name**: we suggest a name, but feel free to customize it. You have the option to add a **prefix** 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 determine when to execute a [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.
## Finding Your Manager Account ID and Customer ID
To connect Microsoft Ads to Nekt, you'll need two important identifiers: your **Manager Account ID** and your **Customer ID**. Here's how to find them:
### Step-by-Step Guide
Go to [ads.microsoft.com](https://ads.microsoft.com) and sign in with your Microsoft account.
Once logged in, click on **Settings** in the left menu.
Select **Account settings** in the left menu, in page you'll see your Customer ID. It's a numeric identifier (e.g., `12345678`).
Alternatively, you can find the Customer ID in the URL when you're viewing a specific account. Look for the `cid=` parameter in the URL.
Select **Manager account settings** in the left menu, in page you'll see your Manager account ID. It's a numeric identifier (e.g., `12345678`).
Alternatively, you can find the Account ID in the URL when you're viewing a specific account. Look for the `aid=` parameter in the URL.
# Streams and Fields
Below you'll find all available data streams from Microsoft Ads and their corresponding fields:
Stream for retrieving account information and settings.
**Key Fields:**
* `Id` - Unique identifier for the account
* `Name` - Name of the account
* `Number` - Account number
* `CurrencyCode` - Account currency code
* `AccountLifeCycleStatus` - Lifecycle status of the account
* `AccountFinancialStatus` - Financial status of the account
* `TimeZone` - Account timezone
**Account Details:**
* `ParentCustomerId` - Parent customer ID
* `PrimaryUserId` - The primary user ID for the account
* `BillToCustomerId` - Bill to customer ID
* `PaymentMethodId` - Payment method ID
* `PaymentMethodType` - Type of payment method
**Business Information:**
* `BusinessAddress` - Business address details including city, country, postal code, and state
* `BillingThresholdAmount` - Billing threshold amount
* `Language` - Preferred language of the account
**Metadata:**
* `LastModifiedByUserId` - ID of the user who last modified the account
* `LastModifiedTime` - The last modified timestamp
* `TimeStamp` - The time stamp of the account
* `PauseReason` - Reason the account was paused (if applicable)
Stream for managing ad campaigns and their settings.
**Key Fields:**
* `id` - Unique identifier for the campaign
* `name` - Name of the campaign
* `status` - Campaign status
* `campaign_type` - Type of campaign
* `sub_type` - Campaign sub type
* `parent_id` - Account ID
**Budget Settings:**
* `budget` - Budget amount
* `budget_id` - Budget ID
* `budget_name` - Budget name
* `budget_type` - Budget type
**Bid Strategy Settings:**
* `bid_strategy_type` - Bid strategy type
* `bid_strategy_id` - Bid strategy ID
* `bid_strategy_name` - Bid strategy name
* `bid_strategy_maxcpc` - Max CPC for bid strategy
* `bid_strategy_targetcpa` - Target CPA for bid strategy
* `bid_strategy_targetroas` - Target ROAS for bid strategy
* `bid_strategy_targetadposition` - Target ad position
* `bid_strategy_targetimpressionshare` - Target impression share
* `bid_adjustment` - Bid adjustment
* `multi_media_ad_bid_adjustment` - Multi media ad bid adjustment
**Targeting & Language:**
* `language` - Language
* `target_setting` - Target setting
* `country_code` - Country code
**URLs & Tracking:**
* `tracking_template` - Tracking template
* `custom_parameter` - Custom parameters
* `final_url_suffix` - Final URL suffix
* `url_expansion_opt_out` - URL expansion opt out
**Dynamic Search Ads:**
* `website` - Website domain for dynamic search ads
* `domain_language` - Domain language for dynamic search ads
* `dynamic_description_enabled` - Dynamic description enabled
* `source` - Source for dynamic search ads
* `page_feed_ids` - Page feed IDs
* `feed_label` - Feed label
**Quality Metrics:**
* `keyword_relevance` - Keyword relevance score
* `landing_page_relevance` - Landing page relevance score
* `landing_page_user_experience` - Landing page user experience score
* `quality_score` - Quality score
**Additional Settings:**
* `modified_time` - Last modified time
* `experiment_id` - Experiment ID
* `priority` - Campaign priority
* `store_id` - Store ID
Stream for managing ad groups, which control targeting, budget, and scheduling for a group of ads.
**Key Fields:**
* `id` - Unique identifier for the ad group
* `name` - Name of the ad group
* `parent_id` - Campaign ID
* `campaign` - Campaign name
* `status` - Ad group status
* `ad_group_type` - Ad group type (SearchStandard, SearchDynamic, HotelAds)
**Scheduling:**
* `start_date` - Start date
* `end_date` - End date
* `ad_schedule_use_searcher_time_zone` - Use searcher time zone
**Network & Distribution:**
* `network_distribution` - Network distribution
* `ad_rotation` - Ad rotation
* `privacy_status` - Privacy status
**Bidding:**
* `cpc_bid` - CPC bid amount
* `bid_strategy_type` - Bid strategy type
* `inherited_bid_strategy_type` - Inherited bid strategy type
* `bid_boost_value` - Bid boost value
* `bid_option` - Bid option
* `maximum_bid` - Maximum bid
* `bid_adjustment` - Bid adjustment
**Targeting:**
* `language` - Language
* `target_setting` - Target setting
* `use_predictive_targeting` - Use predictive targeting
**URLs & Tracking:**
* `tracking_template` - Tracking template
* `custom_parameter` - Custom parameters
* `final_url_suffix` - Final URL suffix
* `frequency_cap_settings` - Frequency cap settings
**Quality Metrics:**
* `keyword_relevance` - Keyword relevance score
* `landing_page_relevance` - Landing page relevance score
* `landing_page_user_experience` - Landing page user experience score
* `quality_score` - Quality score
**Metadata:**
* `modified_time` - Last modified time
* `client_id` - Client ID for tracking
Stream for managing individual ads within an ad set. Supports multiple ad types including Responsive Ads, Responsive Search Ads, Expanded Text Ads, Dynamic Search Ads, Product Ads, App Install Ads, and Text Ads.
**Key Fields:**
* `id` - Unique identifier for the ad
* `type` - Ad type (Responsive Ad, Responsive Search Ad, etc.)
* `status` - Ad status
* `parent_id` - Ad group ID
* `campaign` - Campaign name
* `ad_group` - Ad group name
**Editorial Fields:**
* `editorial_status` - Editorial status
* `editorial_appeal_status` - Editorial appeal status
* `editorial_location` - Editorial location
* `editorial_reason_code` - Editorial reason code
* `editorial_term` - Editorial term
* `publisher_countries` - Publisher countries
**URLs & Tracking:**
* `final_url` - Final URL
* `mobile_final_url` - Mobile final URL
* `tracking_template` - Tracking template
* `custom_parameter` - Custom parameters
* `final_url_suffix` - Final URL suffix
* `destination_url` - Destination URL
* `display_url` - Display URL
**Text Ad Fields:**
* `title` - Title (Text Ad, App Install Ad)
* `title_part_1` - Title part 1 (Expanded Text Ad)
* `title_part_2` - Title part 2 (Expanded Text Ad)
* `title_part_3` - Title part 3 (Expanded Text Ad)
* `text` - Ad text/description
* `text_part_2` - Second description
* `path_1` - Path 1
* `path_2` - Path 2
* `domain` - Domain
**Responsive Search Ad Fields:**
* `headlines` - Headlines JSON
* `descriptions` - Descriptions JSON
**Responsive Ad Fields:**
* `business_name` - Business name
* `call_to_action` - Call to action
* `headline` - Headline
* `long_headline` - Long headline
* `long_headlines` - Long headlines JSON
* `images` - Images JSON
* `landscape_image_media_id` - Landscape image media ID
* `square_image_media_id` - Square image media ID
* `landscape_logo_media_id` - Landscape logo media ID
* `square_logo_media_id` - Square logo media ID
**App Install Ad Fields:**
* `app_id` - App ID
* `app_platform` - App platform
**Metadata:**
* `modified_time` - Last modified time
* `device_preference` - Device preference
* `ad_format_preference` - Ad format preference
Stream for managing keywords within ad groups.
**Key Fields:**
* `id` - Unique identifier for the keyword
* `keyword` - Keyword text
* `match_type` - Match type (Exact, Phrase, Broad)
* `status` - Keyword status
* `parent_id` - Ad group ID
* `campaign` - Campaign name
* `ad_group` - Ad group name
**Editorial Fields:**
* `editorial_status` - Editorial status
* `editorial_appeal_status` - Editorial appeal status
* `editorial_location` - Editorial location
* `editorial_reason_code` - Editorial reason code
* `editorial_term` - Editorial term
* `publisher_countries` - Publisher countries
**Bidding:**
* `bid` - Bid amount
* `bid_strategy_type` - Bid strategy type
* `inherited_bid_strategy_type` - Inherited bid strategy type
**Dynamic Text Parameters:**
* `param1` - Dynamic text parameter 1
* `param2` - Dynamic text parameter 2
* `param3` - Dynamic text parameter 3
**URLs & Tracking:**
* `final_url` - Final URL
* `mobile_final_url` - Mobile final URL
* `tracking_template` - Tracking template
* `custom_parameter` - Custom parameters
* `final_url_suffix` - Final URL suffix
* `destination_url` - Destination URL (deprecated)
**Quality Metrics:**
* `quality_score` - Quality score
* `keyword_relevance` - Keyword relevance
* `landing_page_relevance` - Landing page relevance
* `landing_page_user_experience` - Landing page user experience
**Metadata:**
* `modified_time` - Last modified time
* `client_id` - Client ID for tracking
Daily performance report for campaigns, including impressions, clicks, spend, conversions, and quality metrics.
**Time & Identifiers:**
* `time_period` - Time period of the report (YYYY-MM-DD format)
* `account_id` - Account ID
* `account_name` - Account name
* `campaign_id` - Campaign ID
* `campaign_name` - Campaign name
* `campaign_type` - Campaign type
* `campaign_status` - Campaign status
**Dimensions:**
* `device_type` - Device type (Computer, Smartphone, Tablet)
* `network` - Network (Bing and Yahoo! search, Syndicated search partners)
**Performance Metrics:**
* `impressions` - Number of impressions
* `clicks` - Number of clicks
* `ctr` - Click-through rate
* `average_cpc` - Average cost per click
* `spend` - Total spend
* `average_position` - Average ad position
**Conversion Metrics:**
* `conversions` - Number of conversions
* `conversion_rate` - Conversion rate
* `cost_per_conversion` - Cost per conversion
* `revenue` - Revenue from conversions
* `return_on_ad_spend` - Return on ad spend (ROAS)
* `revenue_per_conversion` - Average revenue per conversion
**Quality Metrics:**
* `quality_score` - Quality score (1-10)
* `expected_ctr` - Expected CTR rating
* `ad_relevance` - Ad relevance rating
* `landing_page_experience` - Landing page experience rating
**Additional Metrics:**
* `assists` - Number of assists
* `cost_per_assist` - Cost per assist
* `impression_share_percent` - Impression share percentage
* `exact_match_impression_share_percent` - Exact match impression share percentage
Daily performance report for ad groups, with detailed metrics including impression share and quality scores.
**Time & Identifiers:**
* `time_period` - Time period of the report (YYYY-MM-DD format)
* `account_id` - Account ID
* `account_name` - Account name
* `campaign_id` - Campaign ID
* `campaign_name` - Campaign name
* `campaign_status` - Campaign status
* `ad_group_id` - Ad Group ID
* `ad_group_name` - Ad Group name
* `status` - Ad Group status
**Dimensions:**
* `device_type` - Device type (Computer, Smartphone, Tablet)
* `network` - Network (Bing and Yahoo! search, Syndicated search partners)
**Performance Metrics:**
* `impressions` - Number of impressions
* `clicks` - Number of clicks
* `ctr` - Click-through rate
* `average_cpc` - Average cost per click
* `spend` - Total spend
**Conversion Metrics:**
* `conversions_qualified` - Number of qualified conversions
* `conversion_rate` - Conversion rate
* `cost_per_conversion` - Cost per conversion
* `revenue` - Revenue from conversions
* `return_on_ad_spend` - Return on ad spend (ROAS)
* `revenue_per_conversion` - Average revenue per conversion
**Quality Metrics:**
* `quality_score` - Quality score (1-10)
* `expected_ctr` - Expected CTR rating
* `ad_relevance` - Ad relevance rating
* `landing_page_experience` - Landing page experience rating
**Additional Metrics:**
* `assists` - Number of assists
* `cost_per_assist` - Cost per assist
* `impression_share_percent` - Impression share percentage
* `exact_match_impression_share_percent` - Exact match impression share percentage
Daily performance report for individual ads, with ad-level metrics and dimensions.
**Time & Identifiers:**
* `time_period` - Time period of the report (YYYY-MM-DD format)
* `account_id` - Account ID
* `account_name` - Account name
* `customer_id` - Customer ID
* `customer_name` - Customer name
**Campaign & Ad Group:**
* `campaign_id` - Campaign ID
* `campaign_name` - Campaign name
* `campaign_type` - Campaign type
* `campaign_status` - Campaign status
* `ad_group_id` - Ad Group ID
* `ad_group_name` - Ad Group name
* `ad_group_status` - Ad Group status
**Ad Details:**
* `ad_id` - Ad ID
* `ad_title` - Ad title
* `ad_description` - Ad description
* `ad_type` - Ad type
* `ad_status` - Ad status
* `destination_url` - Destination URL
* `display_url` - Display URL
**Dimensions:**
* `device_type` - Device type (Computer, Smartphone, Tablet)
* `network` - Network (Bing and Yahoo! search, Syndicated search partners)
* `language` - Language
**Performance Metrics:**
* `impressions` - Number of impressions
* `clicks` - Number of clicks
* `ctr` - Click-through rate
* `average_cpc` - Average cost per click
* `spend` - Total spend
**Conversion Metrics:**
* `conversions_qualified` - Number of qualified conversions
* `conversion_rate` - Conversion rate
* `cost_per_conversion` - Cost per conversion
* `revenue` - Revenue from conversions
* `return_on_ad_spend` - Return on ad spend (ROAS)
* `revenue_per_conversion` - Average revenue per conversion
**Additional Metrics:**
* `assists` - Number of assists
* `cost_per_assist` - Cost per assist
* `top_impression_rate_percent` - Top impression rate percentage
* `absolute_top_impression_rate_percent` - Absolute top impression rate percentage
Daily performance report for keywords, including quality scores, match types, and historical metrics.
**Time & Identifiers:**
* `time_period` - Time period of the report (YYYY-MM-DD format)
* `account_id` - Account ID
* `account_name` - Account name
* `campaign_id` - Campaign ID
* `campaign_name` - Campaign name
* `campaign_status` - Campaign status
* `ad_group_id` - Ad group ID
* `ad_group_name` - Ad group name
* `ad_group_status` - Ad group status
**Keyword Details:**
* `keyword_id` - Keyword ID
* `keyword` - Keyword text
* `keyword_status` - Keyword status
* `bid_match_type` - Bid match type (Exact, Phrase, Broad)
* `delivered_match_type` - Delivered match type
**Dimensions:**
* `device_type` - Device type (Computer, Smartphone, Tablet)
* `network` - Network (Bing and Yahoo! search, Syndicated search partners)
**Performance Metrics:**
* `impressions` - Number of impressions
* `clicks` - Number of clicks
* `ctr` - Click-through rate
* `average_cpc` - Average cost per click
* `spend` - Total spend
* `average_position` - Average ad position
**Conversion Metrics:**
* `conversions` - Number of conversions
* `conversion_rate` - Conversion rate
* `cost_per_conversion` - Cost per conversion
* `revenue` - Revenue from conversions
* `return_on_ad_spend` - Return on ad spend (ROAS)
**Current Quality Metrics:**
* `quality_score` - Quality score (1-10)
* `expected_ctr` - Expected CTR rating
* `ad_relevance` - Ad relevance rating
* `landing_page_experience` - Landing page experience rating
**Historical Quality Metrics:**
* `historical_quality_score` - Historical quality score
* `historical_expected_ctr` - Historical expected CTR rating
* `historical_ad_relevance` - Historical ad relevance rating
* `historical_landing_page_experience` - Historical landing page experience rating
**Bid Metrics:**
* `current_max_cpc` - Current maximum CPC bid
Daily budget tracking report showing monthly budget, daily spend, and month-to-date expenditure.
**Account Dimensions:**
* `account_name` - Account name
* `account_number` - Account number
* `account_id` - Account ID
**Campaign Dimensions:**
* `campaign_name` - Campaign name
* `campaign_id` - Campaign ID
**Budget Metrics:**
* `date` - Date of the budget data (YYYY-MM-DD format)
* `currency_code` - Currency code
* `monthly_budget` - Monthly budget amount
* `daily_spend` - Daily spend amount
* `month_to_date_spend` - Month-to-date spend amount
Daily geographic performance report with location-based metrics and breakdown by country, state, city, and more.
**Time & Identifiers:**
* `time_period` - Time period of the report (YYYY-MM-DD format)
* `account_id` - Account ID
* `account_name` - Account name
* `account_number` - Account number
* `campaign_id` - Campaign ID
* `campaign_name` - Campaign name
* `campaign_type` - Campaign type
* `ad_group_id` - Ad group ID
* `ad_group_name` - Ad group name
**Location Dimensions:**
* `location_id` - Location ID
* `country` - Country
* `state` - State/Province
* `metro_area` - Metro area
* `city` - City
* `county` - County
* `postal_code` - Postal code
**Device & Network Dimensions:**
* `device_type` - Device type (Computer, Smartphone, Tablet)
* `network` - Network (Bing and Yahoo! search, Syndicated search partners)
* `language` - Language
**Performance Metrics:**
* `impressions` - Number of impressions
* `clicks` - Number of clicks
* `ctr` - Click-through rate
* `average_cpc` - Average cost per click
* `spend` - Total spend
* `average_position` - Average ad position
**Conversion Metrics:**
* `conversions` - Number of conversions
* `conversion_rate` - Conversion rate
* `cost_per_conversion` - Cost per conversion
* `revenue` - Revenue from conversions
* `return_on_ad_spend` - Return on ad spend (ROAS)
* `revenue_per_conversion` - Average revenue per conversion
**Additional Metrics:**
* `assists` - Number of assists
* `cost_per_assist` - Cost per assist
# Data Model
The following diagram illustrates the relationships between the core data streams in Microsoft 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"
Accounts("Accounts");
Campaigns("Campaigns");
AdGroups("Ad Groups");
Ads("Ads");
Keywords("Keywords");
end
subgraph "Performance Reports"
CampaignReport("Campaign Performance Report");
AdGroupReport("Ad Group Performance Report");
AdReport("Ad Performance Report");
KeywordReport("Keyword Performance Report");
LocationReport("User Location Performance Report");
BudgetReport("Budget Summary Report");
end
Campaigns -- "parent_id" --> Accounts;
AdGroups -- "parent_id" --> Campaigns;
Ads -- "parent_id" --> AdGroups;
Keywords -- "parent_id" --> AdGroups;
CampaignReport -- "campaign_id" --> Campaigns;
AdGroupReport -- "ad_group_id" --> AdGroups;
AdGroupReport -- "campaign_id" --> Campaigns;
AdReport -- "ad_id" --> Ads;
AdReport -- "ad_group_id" --> AdGroups;
KeywordReport -- "keyword_id" --> Keywords;
KeywordReport -- "ad_group_id" --> AdGroups;
LocationReport -- "campaign_id" --> Campaigns;
BudgetReport -- "campaign_id" --> Campaigns;
```
# Use Cases for Data Analysis
This guide outlines valuable business intelligence use cases when consolidating Microsoft Ads data, along with ready-to-use SQL queries that you can run on [Explorer](https://app.nekt.ai/explorer).
## Campaign Performance Analysis
### 1. Campaign Performance Overview
Track the overall performance of your campaigns, including key metrics and ROI.
**Business Value:**
* Identify which campaigns deliver the best value for money
* Understand the relationship between quality scores and performance
* Optimize budget allocation based on performance metrics
```sql theme={null}
WITH
campaign_metrics AS (
SELECT
c.name AS campaign_name,
c.campaign_type,
c.status,
SUM(cpr.impressions) AS total_impressions,
SUM(cpr.clicks) AS total_clicks,
SUM(cpr.spend) AS total_spend,
SUM(cpr.conversions) AS total_conversions,
SUM(cpr.revenue) AS total_revenue,
AVG(cpr.ctr) AS avg_ctr,
AVG(cpr.average_cpc) AS avg_cpc,
AVG(cpr.quality_score) AS avg_quality_score
FROM
nekt_raw.microsoft_ads_campaigns c
LEFT JOIN nekt_raw.microsoft_ads_campaign_performance_report cpr
ON c.id = cpr.campaign_id
WHERE
date_parse(cpr.time_period, '%Y-%m-%d') >= CURRENT_DATE - INTERVAL '30' DAY
GROUP BY
c.name,
c.campaign_type,
c.status
)
SELECT
campaign_name,
campaign_type,
status,
total_spend,
total_impressions,
total_clicks,
total_conversions,
total_revenue,
avg_ctr,
avg_cpc,
avg_quality_score,
CASE
WHEN total_spend > 0 THEN total_revenue / total_spend
ELSE 0
END AS roas
FROM
campaign_metrics
ORDER BY
total_spend DESC
```
```sql theme={null}
WITH
campaign_metrics AS (
SELECT
c.name AS campaign_name,
c.campaign_type,
c.status,
SUM(cpr.impressions) AS total_impressions,
SUM(cpr.clicks) AS total_clicks,
SUM(cpr.spend) AS total_spend,
SUM(cpr.conversions) AS total_conversions,
SUM(cpr.revenue) AS total_revenue,
AVG(cpr.ctr) AS avg_ctr,
AVG(cpr.average_cpc) AS avg_cpc,
AVG(cpr.quality_score) AS avg_quality_score
FROM
`nekt_raw.microsoft_ads_campaigns` c
LEFT JOIN `nekt_raw.microsoft_ads_campaign_performance_report` cpr
ON c.id = cpr.campaign_id
WHERE
PARSE_DATE('%Y-%m-%d', cpr.time_period) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY
c.name,
c.campaign_type,
c.status
)
SELECT
campaign_name,
campaign_type,
status,
total_spend,
total_impressions,
total_clicks,
total_conversions,
total_revenue,
avg_ctr,
avg_cpc,
avg_quality_score,
SAFE_DIVIDE(total_revenue, total_spend) AS roas
FROM
campaign_metrics
ORDER BY
total_spend DESC
```
| campaign\_name | campaign\_type | status | total\_spend | total\_impressions | total\_clicks | total\_conversions | total\_revenue | avg\_ctr | avg\_cpc | avg\_quality\_score | roas |
| ---------------- | -------------- | ------ | ------------ | ------------------ | ------------- | ------------------ | -------------- | -------- | -------- | ------------------- | ---- |
| Summer Sale 2024 | Search | Active | 8,542.30 | 1,245,890 | 42,156 | 1,245 | 24,890.00 | 3.38 | 0.20 | 7.5 | 2.91 |
| Brand Awareness | Audience | Active | 5,686.00 | 3,892,450 | 28,430 | 567 | 11,340.00 | 0.73 | 0.20 | 6.8 | 1.99 |
| Product Launch | Search | Active | 4,998.50 | 892,340 | 31,245 | 892 | 17,840.00 | 3.50 | 0.16 | 8.2 | 3.57 |
| Retargeting | Audience | Active | 3,784.00 | 2,145,670 | 18,920 | 423 | 8,460.00 | 0.88 | 0.20 | 7.1 | 2.24 |
### 2. Keyword Performance Analysis
Analyze keyword performance to identify top-performing keywords and optimization opportunities.
**Business Value:**
* Identify high-performing keywords for increased investment
* Find underperforming keywords for optimization or removal
* Understand match type effectiveness
```sql theme={null}
SELECT
kpr.keyword,
kpr.bid_match_type,
kpr.campaign_name,
kpr.ad_group_name,
SUM(kpr.impressions) AS total_impressions,
SUM(kpr.clicks) AS total_clicks,
SUM(kpr.spend) AS total_spend,
SUM(kpr.conversions) AS total_conversions,
SUM(kpr.revenue) AS total_revenue,
AVG(kpr.ctr) AS avg_ctr,
AVG(kpr.average_cpc) AS avg_cpc,
AVG(kpr.quality_score) AS avg_quality_score,
AVG(kpr.average_position) AS avg_position,
CASE
WHEN SUM(kpr.clicks) > 0 THEN SUM(kpr.conversions) * 100.0 / SUM(kpr.clicks)
ELSE 0
END AS conversion_rate,
CASE
WHEN SUM(kpr.conversions) > 0 THEN SUM(kpr.spend) / SUM(kpr.conversions)
ELSE 0
END AS cost_per_conversion
FROM
nekt_raw.microsoft_ads_keyword_performance_report kpr
WHERE
date_parse(kpr.time_period, '%Y-%m-%d') >= CURRENT_DATE - INTERVAL '30' DAY
GROUP BY
kpr.keyword,
kpr.bid_match_type,
kpr.campaign_name,
kpr.ad_group_name
HAVING
SUM(kpr.clicks) > 10
ORDER BY
total_conversions DESC,
total_clicks DESC
LIMIT 50
```
```sql theme={null}
SELECT
kpr.keyword,
kpr.bid_match_type,
kpr.campaign_name,
kpr.ad_group_name,
SUM(kpr.impressions) AS total_impressions,
SUM(kpr.clicks) AS total_clicks,
SUM(kpr.spend) AS total_spend,
SUM(kpr.conversions) AS total_conversions,
SUM(kpr.revenue) AS total_revenue,
AVG(kpr.ctr) AS avg_ctr,
AVG(kpr.average_cpc) AS avg_cpc,
AVG(kpr.quality_score) AS avg_quality_score,
AVG(kpr.average_position) AS avg_position,
SAFE_DIVIDE(SUM(kpr.conversions) * 100.0, SUM(kpr.clicks)) AS conversion_rate,
SAFE_DIVIDE(SUM(kpr.spend), SUM(kpr.conversions)) AS cost_per_conversion
FROM
`nekt_raw.microsoft_ads_keyword_performance_report` kpr
WHERE
PARSE_DATE('%Y-%m-%d', kpr.time_period) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY
kpr.keyword,
kpr.bid_match_type,
kpr.campaign_name,
kpr.ad_group_name
HAVING
SUM(kpr.clicks) > 10
ORDER BY
total_conversions DESC,
total_clicks DESC
LIMIT 50
```
| keyword | bid\_match\_type | campaign\_name | ad\_group\_name | total\_impressions | total\_clicks | total\_spend | total\_conversions | total\_revenue | avg\_ctr | avg\_cpc | avg\_quality\_score | avg\_position | conversion\_rate | cost\_per\_conversion |
| ----------------- | ---------------- | ---------------- | --------------- | ------------------ | ------------- | ------------ | ------------------ | -------------- | -------- | -------- | ------------------- | ------------- | ---------------- | --------------------- |
| buy laptop online | Exact | Electronics Sale | Laptops | 45,670 | 2,340 | 468.00 | 156 | 15,600.00 | 5.12 | 0.20 | 9 | 1.2 | 6.67 | 3.00 |
| best laptop deals | Phrase | Electronics Sale | Laptops | 38,920 | 1,890 | 378.00 | 98 | 9,800.00 | 4.86 | 0.20 | 8 | 1.5 | 5.19 | 3.86 |
| laptop sale | Broad | Electronics Sale | Laptops | 89,340 | 3,120 | 624.00 | 87 | 8,700.00 | 3.49 | 0.20 | 7 | 2.1 | 2.79 | 7.17 |
### 3. Geographic Performance Analysis
Analyze performance by user location to identify high-performing regions and optimize geographic targeting.
**Business Value:**
* Identify top-performing geographic regions
* Optimize location-based bid adjustments
* Discover new market opportunities
```sql theme={null}
SELECT
ulpr.country,
ulpr.state,
ulpr.city,
SUM(ulpr.impressions) AS total_impressions,
SUM(ulpr.clicks) AS total_clicks,
SUM(ulpr.spend) AS total_spend,
SUM(ulpr.conversions) AS total_conversions,
SUM(ulpr.revenue) AS total_revenue,
AVG(ulpr.ctr) AS avg_ctr,
AVG(ulpr.average_cpc) AS avg_cpc,
CASE
WHEN SUM(ulpr.clicks) > 0 THEN SUM(ulpr.conversions) * 100.0 / SUM(ulpr.clicks)
ELSE 0
END AS conversion_rate,
CASE
WHEN SUM(ulpr.spend) > 0 THEN SUM(ulpr.revenue) / SUM(ulpr.spend)
ELSE 0
END AS roas
FROM
nekt_raw.microsoft_ads_user_location_performance_report ulpr
WHERE
date_parse(ulpr.time_period, '%Y-%m-%d') >= CURRENT_DATE - INTERVAL '30' DAY
GROUP BY
ulpr.country,
ulpr.state,
ulpr.city
HAVING
SUM(ulpr.impressions) > 1000
ORDER BY
total_conversions DESC,
total_spend DESC
LIMIT 25
```
```sql theme={null}
SELECT
ulpr.country,
ulpr.state,
ulpr.city,
SUM(ulpr.impressions) AS total_impressions,
SUM(ulpr.clicks) AS total_clicks,
SUM(ulpr.spend) AS total_spend,
SUM(ulpr.conversions) AS total_conversions,
SUM(ulpr.revenue) AS total_revenue,
AVG(ulpr.ctr) AS avg_ctr,
AVG(ulpr.average_cpc) AS avg_cpc,
SAFE_DIVIDE(SUM(ulpr.conversions) * 100.0, SUM(ulpr.clicks)) AS conversion_rate,
SAFE_DIVIDE(SUM(ulpr.revenue), SUM(ulpr.spend)) AS roas
FROM
`nekt_raw.microsoft_ads_user_location_performance_report` ulpr
WHERE
PARSE_DATE('%Y-%m-%d', ulpr.time_period) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY
ulpr.country,
ulpr.state,
ulpr.city
HAVING
SUM(ulpr.impressions) > 1000
ORDER BY
total_conversions DESC,
total_spend DESC
LIMIT 25
```
| country | state | city | total\_impressions | total\_clicks | total\_spend | total\_conversions | total\_revenue | avg\_ctr | avg\_cpc | conversion\_rate | roas |
| ------------- | ---------- | ----------- | ------------------ | ------------- | ------------ | ------------------ | -------------- | -------- | -------- | ---------------- | ---- |
| United States | California | Los Angeles | 156,234 | 5,892 | 1,178.40 | 234 | 4,680.00 | 3.77 | 0.20 | 3.97 | 3.97 |
| United States | New York | New York | 142,890 | 5,234 | 1,046.80 | 198 | 3,960.00 | 3.66 | 0.20 | 3.78 | 3.78 |
| United States | Texas | Houston | 98,450 | 3,890 | 778.00 | 145 | 2,900.00 | 3.95 | 0.20 | 3.73 | 3.73 |
| United States | Florida | Miami | 87,340 | 3,245 | 649.00 | 112 | 2,240.00 | 3.72 | 0.20 | 3.45 | 3.45 |
## Implementation Notes
### Data Quality Considerations
* Performance reports are aggregated daily by default. Use the `time_period` field for trend analysis.
* Quality scores range from 1-10, with higher scores indicating better ad relevance and landing page experience.
* The User Location Performance Report provides granular geographic data down to the postal code level.
### API & Performance
* Microsoft Ads uses the Bulk API for entity data (campaigns, ad groups, ads, keywords) and the Reporting API for performance reports.
* Reports are generated asynchronously, which may result in slight delays during extraction.
* For optimal performance, select only the streams necessary for your analysis.
### Microsoft Search Network
* Microsoft Ads serves ads across the Microsoft Search Network, including Bing, Yahoo, AOL, and partner sites.
* The `network` dimension in reports indicates where your ads appeared (e.g., "Bing and Yahoo! search" or "Syndicated search partners").
## Skills for agents
Microsoft Ads connector documentation as plain markdown, for use in AI agent contexts.