Microsoft Ads

The Switchboard Microsoft Ads connector provides automated ingestion of ad, campaign, and geographic performance reports.

Prerequisites

Microsoft Client Id
Your registered application ID
Microsoft Client Secret
Set it to the value provisioned in Register an application.

Scheduling

The Microsoft Ads Connector can be scheduled to ingest data once per day at a user-defined hour and timezone.

By default, the connector will run once at 6am PT. See Daily Scheduling for more information.

Report Types

The following reports are implemented:

Ad Performance Report
Use the import type microsoft_ads:performance
Campaign Performance Report
Use the import type microsoft_ads:conversion
Geographic Performance Report
Use the import type microsoft_ads:geographic

Parameters

customer_id
The identifier of the manager account (customer) the user is accessing or operating from. See Get Your Account and Customer IDs
account_ids
The identifier of the ad account that owns or is associated with the entities in the request. See Get Your Account and Customer IDs
aggregation
See Microsoft’s documentation on the ReportAggregation Value Set
columns
For the set of columns available for the Ad Performance Report see Microsoft’s documentation on the AdPerformanceReportColumn Value Set
For the set of columns available for the Campaign Performance Report see Microsoft’s documentation on the CampaignPerformanceReportColumn Value Set
For the set of columns available for the Geographic Performance Repor see Microsoft’s documentation on the GeographicPerformanceReportColumn Value Set

Switchboard Script Syntax

Ad Performance Report

import microsoft_ads_performance from {
    type: "microsoft_ads:performance";
    key: "microsoft_ads_key";
   customer_id: "123";  // Get from UI
   account_ids: [123, 456, 789];  // Get from UI
    aggregation: "Daily";  // Various options supported
    columns: ["TimePeriod", "Clicks", "Impressions", "AllRevenue"];  // Columns differ by report.
    api_version: "v13"; // Not required has this default.
    lookback_*: 123 // Lookback supported.
}

Geographic Performance Report


download microsoft_ads_geo_raw from {
    type: "microsoft_ads:geographic";
    key: "microsoft_ads_key";
    customer_id: "x";
    account_ids: [x];
    aggregation: "Daily";
    columns: [
        "AbsoluteTopImpressionRatePercent",
        "AccountId",
        "AccountName",
        "AccountNumber",
        "AccountStatus",
        "AdDistribution",
        "AdGroupId",
        "AdGroupName",
        "AdGroupStatus",
        "AllConversionRate",
        "AllConversions",
        "AllConversionsQualified",
        "AllCostPerConversion",
        "AllReturnOnAdSpend",
        "AllRevenue",
        "AllRevenuePerConversion",
        "Assists",
        "AverageCpc",
        "AverageCpm",
        "AveragePosition",
        "BaseCampaignId",
        "BidMatchType",
        "CampaignId",
        "CampaignName",
        "CampaignStatus",
        "City",
        "Clicks",
        "ConversionRate",
        "Conversions",
        "ConversionsQualified",
        "CostPerAssist",
        "CostPerConversion",
        "Country",
        "County",
        "Ctr",
        "CurrencyCode",
        "DeliveredMatchType",
        "DeviceOS",
        "DeviceType",
        "Goal",
        "GoalType",
        "Impressions",
        "Language",
        "LocationId",
        "LocationType",
        "MetroArea",
        "MostSpecificLocation",
        "Neighborhood",
        "Network",
        "PostalCode",
        "ProximityTargetLocation",
        "Radius",
        "ReturnOnAdSpend",
        "Revenue",
        "RevenuePerAssist",
        "RevenuePerConversion",
        "Spend",
        "State",
        "TimePeriod",
        "TopImpressionRatePercent",
        "TopVsOther",
        "ViewThroughConversions",
        "ViewThroughConversionsQualified"];
} using { 
         AbsoluteTopImpressionRatePercent: string;
         AccountId: integer;
         AccountName: string;
         AccountNumber: string;
         AccountStatus: string;
         AdDistribution: string;
         AdGroupId: integer;
         AdGroupName: string;
         AdGroupStatus: string;
         AllConversionRate: string;
         AllConversions: integer;
         AllConversionsQualified: float;
         AllCostPerConversion: string;
         AllReturnOnAdSpend: float;
         AllRevenue: float;
         AllRevenuePerConversion: string;
         Assists: integer;
         AverageCpc: float;
         AverageCpm: float;
         AveragePosition: float;
         BaseCampaignId: integer;
         BidMatchType: string;
         CampaignId: integer;
         CampaignName: string;
         CampaignStatus: string;
         City: string;
         Clicks: integer;
         ConversionRate: string;
         Conversions: integer;
         ConversionsQualified: float;
         CostPerAssist: string;
         CostPerConversion: string;
         Country: string;
         County: string;
         Ctr: string;
         CurrencyCode: string;
         DeliveredMatchType: string;
         DeviceOS: string;
         DeviceType: string;
         Goal: string;
         GoalType: string;
         Impressions: integer;
         Language: string;
         LocationId: integer;
         LocationType: string;
         MetroArea: string;
         MostSpecificLocation: string;
         Neighborhood: string;
         Network: string;
         PostalCode: string;
         ProximityTargetLocation: string;
         Radius: integer;
         ReturnOnAdSpend: float;
         Revenue: float;
         RevenuePerAssist: string;
         RevenuePerConversion: string;
         Spend: float;
         State: string;
         TimePeriod: date;
         TopImpressionRatePercent: string;
         TopVsOther: string;
         ViewThroughConversions: integer;
         ViewThroughConversionsQualified: string;
};

table microsoft_ads_geo is
    select 
        <<raw.$c as "$c" for c in raw,
        processing_datetime() as switchboard_processing_datetime
    from microsoft_ads_geo_raw as raw;

upload microsoft_ads_geo to {
    type: "bigquery:load";
    key: "x";
    project: "x";
    dataset: "x";
    table: "microsoft_geo_YYYYMMDD";
    primary_source_name: "microsoft_ads_geo_raw";
    truncate: true;
    test_table: "test_microsoft_geo";
};