Microsoft Ads

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

Prerequisites
Scheduling
Report Types
Switchboard Script Syntax
Parameters

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";
};