Python code to Request and Download Microsoft Advertising Report

Get Microsoft Advertising (Formerly Bing Ads) insights with Python.

Hi Everyone, hope you are keeping well. I have put together this article to help you guys in building python logic to use Microsoft Advertising APIs.

The following is the general flow for requesting any report.

Create a credentials file to store credentials.
Authenticate yourself using an OAuth token.
Create a request with the report parameters.
Download the report.

What is Microsoft Advertising API?

Microsoft Advertising API provides access to all your campaign settings, including ads, keywords, ad extension, target, and many more.

In this article, we will be using Microsoft Advertising APIs to request and download campaign reports and also to request and download ad reports. The logic of getting a report discussed in this article remains the same only the report type changes. I will paste in the link for different report types available in the article, so keep your focus on it.

So, if the requirement is to use Microsoft Advertising API for different purposes, which requesting and downloading reports can’t meet – you can take a look here (Bing Ads API). I still recommend going through this article so that you get an idea for OAuth Authentication and Requesting and downloading any kind of report.

Before diving into implementing the Python code for Microsoft Advertising API to retrieve Microsoft Advertising Insights.

If you are just starting with Microsoft Advertising API research and figuring out what credentials you need to make Microsoft Advertising API work? How to generate credentials for OAuth authentication? Refer to the Microsoft Advertising API Setup Guide.

Without wasting any time let’s get started.

Let me begin by giving you a high-level overview. We are going to

Create a JSON file to store all Microsoft Advertising API authentication credentials.
Create the main python file which will work as initiator and flow controller.
Create python modules that will have functions for requesting and downloading Microsoft Advertising Reports.

Python code to request and download Bing report using Bing Ads API

1. Storing API credentials in JSON file.

First, create a JSON file to store all authentication-related credentials -like Client ID, Client Secret, Access Token, Refresh Token, and Developer Token. Creating a JSON file to store credentials makes it easy to maintain, update and track credentials as needed. Save the JSON file as “ms_ads_cred.json”.

Also if you don’t have the below credential, check out my article on Microsoft Advertising account setup and getting OAuth Credentials – it’s important, without these credentials the python code in here not going to work.

{
    "client_id":"Replace with Client ID",
    "access_token":"Replace with Access Token ",
    "refresh_token":"Replace with Refresh Token",
    "developer_token" :"Replace with Developer Token",
    "client_secret" : "Replace with Client Secret"
}

2. Main Python File:

Let’s create a main file, which will work as an initiator and will be calling all functions as needed from this main python file.

The main file will look something like the below code for this project. Note that the below code is not yet complete, it is in its very beginning stage. I mean with just one function call. As we proceed to different python functions and python module implementations, we will add that function call to this python file. Save the file as “ms_ads_main.py”.

#!/usr/local/bin/python3
# command to run this code $ python3 ./python/ms_ads_main.py -s 2020-11-22 -e 2020-11-28 -q week/day
import getopt
import sys
import json
from datetime import datetime, timedelta
#import custom python module
from ms_authentication import *
 
def readfile(argv):
    global s_date
    global e_date
    global qry_type
    try:
        opts, args = getopt.getopt(argv,"s:e:q:")
    except getopt.GetoptError:
        usage()
    for opt, arg in opts:
        if opt == '-s':
            s_date = arg
        elif opt == '-e':
            e_date = arg
        elif opt == '-q':
            qry_type = arg
        else:
            print("Invalid Option in command line")
 
if __name__ == '__main__':
    try:
        timestamp = datetime.strftime(datetime.now(),'%Y-%m-%d : %H:%M')
        print("DATE : ",timestamp,"\n")
        print("Microsoft Advertising data extraction process Starts")

        #getting the command line parameter.
        readfile(sys.argv[1:])
      
        #loading and reading credentials from JSON file.
        ms_cred_file = "./ms_ads_cred.json"
        ms_cred= open(ms_cred_file, 'r')
        cred_json = json.load(ms_cred)
        client_id = cred_json["client_id"]
        client_secret = cred_json["client_secret"]
        developer_token = cred_json["developer_token"]
        access_secret = cred_json["access_token"]
        refresh_token = cred_json["refresh_token"]
 
        #call authentication function
        authorization_data = ms_auth(refresh_token,client_id,client_secret,developer_token)
 
        print("MICROSOFT_ADVERTISING_MAIN : data extraction Process Finished \n")
    except:
        print("MICROSOFT_ADVERTISING_MAIN : data extraction processing Failed !!!!:", sys.exc_info())

3. Python Logic for OAuth Authentication:

Here we are going to read the credentials from the above JSON file for authenticating ourselves so that we can request and download Microsoft Advertising Reports.

If you have noticed, the above code shows how to extract credentials from the JSON file we created in the first step.

Let’s understand how to use this credential for authentication from the below code. Consider going through the code, and try to get a basic understanding of what’s going on. Don’t forget to save the code file as “ms_authentication.py”.

#!/usr/local/bin/python3
import sys
from bingads.authorization import *
from bingads.service_client import ServiceClient
 
def ms_auth(refresh_token,client_id,client_secrect,developer_token):
    try:
        authorization_data=AuthorizationData(
        account_id=None,
        customer_id=None,
        developer_token=developer_token,
        authentication=None,
        )
        authentication=OAuthDesktopMobileAuthCodeGrant(
        client_id=client_id,
        env='production'
        )
 
        authentication.state='bld@bingads_amp'
        authentication.client_secret=client_secrect
        # Assign this authentication instance to the authorization_data. 
        authorization_data.authentication=authentication  
 
        authorization_data.authentication.request_oauth_tokens_by_refresh_token(refresh_token)
 
        print("MS_AUTHENTICATION: authentication process finished successfully\n")
        
        return authorization_data
    except:
        print("\nMS_AUTHENTICATION: authentication process Failed : ",sys.exc_info())

Also if you are thinking about how to import this python module and use its python function. Don’t worry, in the main code demonstrated in step 2, you will see how this module function is called by passing needed parameters.

4. Creating a Python module to get Microsoft Advertising Reports.

So we have completed a prerequisite of the Microsoft Advertising Report request and report download project. Now we are going to move forward by creating a file named “get_report.py”. This file would be used to define functions to request reports for Microsoft Advertising(Bing Ads) campaigns and Microsoft Advertising(Bing Ads) ads, as well as to download Microsoft Advertising camping and ads reports.

#!/usr/bin/python3
import sys
import io
import pandas as pd
from urllib import parse
from datetime import datetime, timedelta
from bingads.service_client import ServiceClient
from bingads.v13 import *
from bingads.v13.reporting import *
from suds import WebFault
from suds.client import Client

In the “get_report.py” file, we will define a function to validate dates. This function will check the format of the date in “yyyy-mm-dd”. Just to maintain a standard date formatting across the code.

#Function for date validation
def date_validation(date_text):
    try:
        while date_text != datetime.strptime(date_text, '%Y-%m-%d').strftime('%Y-%m-%d'):
            date_text = input('Please Enter the date in YYYY-MM-DD format\t')
        else:
            return datetime.strptime(date_text,'%Y-%m-%d').date()
    except:
        raise Exception('linkedin_campaign_processing : year does not match format yyyy-mm-dd')

4.1. Request Campaign Report:

The report type we are going to use here is “CampaignPerformanceReportRequest”. This report gives you high-level performance statistics and quality attributes for each campaign or account. One can use a wide range of parameters available to configure report data according to your requirement.

Some common request parameters are impressions, clicks, spend, and the average cost per click for each campaign or account. Once downloaded, this data can be sorted by the campaign, campaign status, and quality score. To know more about this report type you can take a look at Bing Campaign Performance Report.

Define a function – named “get_campaign_report”, the code for which is below. We are going to add this function in the “get_report.py” file.

def get_campaign_report(authorization_data,account_id,s_date,e_date,qry_type):
        try:
            startDate = date_validation(s_date)
            dt = startDate+timedelta(1)
            week_number = dt.isocalendar()[1]
            endDate = date_validation(e_date)
 
            reporting_service = ServiceClient(
                service='ReportingService', 
                version=13,
                authorization_data=authorization_data, 
                environment='production',
                )
            if qry_type in ["day","daily"]:
                aggregation = 'Daily'
            elif qry_type in ["week","weekly"]:
                aggregation = 'Weekly'
 
            exclude_column_headers=False
            exclude_report_footer=False
            exclude_report_header=False
            time=reporting_service.factory.create('ReportTime')
            # You can either use a custom date range or predefined time.
            #time.PredefinedTime='Yesterday'
            start_date=reporting_service.factory.create('Date')
            start_date.Day=startDate.day
            start_date.Month=startDate.month
            start_date.Year=startDate.year
            time.CustomDateRangeStart=start_date
 
            end_date=reporting_service.factory.create('Date')
            end_date.Day=endDate.day
            end_date.Month=endDate.month
            end_date.Year=endDate.year
            time.CustomDateRangeEnd=end_date
            time.ReportTimeZone='PacificTimeUSCanadaTijuana'
            return_only_complete_data=False
            
            report_request=reporting_service.factory.create('CampaignPerformanceReportRequest')
            report_request.Aggregation=aggregation
            report_request.ExcludeColumnHeaders=exclude_column_headers
            report_request.ExcludeReportFooter=exclude_report_footer
            report_request.ExcludeReportHeader=exclude_report_header
            report_request.Format='Csv'
            report_request.ReturnOnlyCompleteData=return_only_complete_data
            report_request.Time=time    
            report_request.ReportName="Campaign Performance Report"
            scope=reporting_service.factory.create('AccountThroughCampaignReportScope')
            scope.AccountIds={'long': [account_id] }
            scope.Campaigns=None
            report_request.Scope=scope     
 
            report_columns=reporting_service.factory.create('ArrayOfCampaignPerformanceReportColumn')
            report_columns.CampaignPerformanceReportColumn.append(['AccountName','AccountId','TimePeriod','CampaignId',
                'CampaignName','Impressions','Clicks','Conversions' ,'Spend'])
            report_request.Columns=report_columns
 
            #return campaign_performance_report_request
            return report_request
        except:
                print("\nMS_ADS_CAMPAIGN_REPORT : report processing Failed : ", sys.exc_info())

4.2. Download Campaign Report:

Define a function named “download_campaign_report”, the code for which is below. This will be downloading the above-requested report. We are going to add this function in the “get_report.py” file.

def download_campaign_report(report_request,authorization_data,s_date,e_date,qry_type):
    try:
        startDate = date_validation(s_date)
        dt = startDate+timedelta(1)
        week_number = dt.isocalendar()[1]
        endDate = date_validation(e_date)
 
        reporting_download_parameters = ReportingDownloadParameters(
                report_request=report_request,
                result_file_directory = "./data/", 
                result_file_name = "campaign_report.csv", 
                overwrite_result_file = True, # value true if you want to overwrite the same file.
                timeout_in_milliseconds=3600000 # cancel the download after a specified time interval.
            )
        
        reporting_service_manager=ReportingServiceManager(
            authorization_data=authorization_data, 
            poll_interval_in_milliseconds=5000, 
            environment='production',
        )
        
        report_container = reporting_service_manager.download_report(reporting_download_parameters)
        
        if(report_container == None):
            print("There is no report data for the submitted report request parameters.")
            sys.exit(0)
 
        campaign_analytics_data = pd.DataFrame(columns=["account_id","campaign_name","campaign_id","start_date","end_date",
                                    "cost","impressions","clicks"])
 
        if "Impressions" in report_container.report_columns and \
            "Clicks" in report_container.report_columns and \
            "Spend" in report_container.report_columns and \
            "CampaignId" in report_container.report_columns:
 
            report_record_iterable = report_container.report_records
 
            for record in report_record_iterable:
                tmp_dict = {}
                tmp_dict["impressions"] = record.int_value("Impressions")
                tmp_dict["clicks"] = record.int_value("Clicks")
                tmp_dict["cost"] = float(record.value("Spend"))
                #print(float(record.value("Spend")))
                tmp_dict["conversions"] = record.int_value("Conversions")
                tmp_dict["campaign_name"] = record.value("CampaignName")
                tmp_dict["campaign_id"] = record.int_value("CampaignId")
                tmp_dict["account_name"] = record.value("AccountName")
                tmp_dict["account_id"] = record.int_value("AccountId")
                
                
                campaign_analytics_data = campaign_analytics_data.append(tmp_dict,ignore_index = True)
                campaign_analytics_data["start_date"] = startDate
                campaign_analytics_data["end_date"] = endDate
                
                if qry_type in ["week","weekly"]:
                    campaign_analytics_data["week"] = week_number
                elif qry_type in ["month","monthly"]:
                    campaign_analytics_data["month"] = startDate.month
                elif qry_type in ["day","daily"]:
                    campaign_analytics_data["week"] = week_number
        
        #Be sure to close the report.
        report_container.close()
 
        return campaign_analytics_data
    except:
        print("\nDOWNLOAD_CAMPAIGN_REPORT : processing Failed : ", sys.exc_info())

Note: You need to create a folder “data” in your working/current directory

4.3. Request Ads Report:

The report type we are going to use here is “AdsPerformanceReportRequest”. This report gives you insight, which will help you determine which ads lead to click and conversion and which are not performing. An account with highly optimized ads will pull up the quality of your campaigns. This Report type provides a wide range of parameter options that can be used as filters to download reports with data that really matters to your requirement.

You can use parameters like impressions, clicks, spend and averages cost per click for each ad in the ads report request. After downloading the report of this ad, you can use the ad id, ad status, ad title, display URL, and destination URL to sort the data. To know about this report type you can take a look at Bing Ads Performance Report.

Define a function – named “get_ads_report”, the code for which is below. We are going to add this function in the “get_report.py” python module. Will be calling this function from the main python file created in step 1.

def get_ads_report(authorization_data,account_id,s_date,e_date,qry_type):
        try:
            startDate = date_validation(s_date)
            dt = startDate+timedelta(1)
            week_number = dt.isocalendar()[1]
            endDate = date_validation(e_date)
 
            reporting_service = ServiceClient(
                service='ReportingService', 
                version=13,
                authorization_data=authorization_data, 
                environment='production',
                )
 
            if qry_type in ["day","daily"]:
                aggregation = 'Daily'
            elif qry_type in ["week","weekly"]:
                aggregation = 'Weekly'
            exclude_column_headers=False
            exclude_report_footer=False
            exclude_report_header=False
            time=reporting_service.factory.create('ReportTime')
            start_date=reporting_service.factory.create('Date')
            start_date.Day=startDate.day
            start_date.Month=startDate.month
            start_date.Year=startDate.year
            time.CustomDateRangeStart=start_date
 
            end_date=reporting_service.factory.create('Date')
            end_date.Day=endDate.day
            end_date.Month=endDate.month
            end_date.Year=endDate.year
            time.CustomDateRangeEnd=end_date
            time.ReportTimeZone='PacificTimeUSCanadaTijuana'
            return_only_complete_data=False
            
            report_request=reporting_service.factory.create('AdPerformanceReportRequest')
            report_request.Aggregation=aggregation
            report_request.ExcludeColumnHeaders=exclude_column_headers
            report_request.ExcludeReportFooter=exclude_report_footer
            report_request.ExcludeReportHeader=exclude_report_header
            report_request.Format='Csv'
            report_request.ReturnOnlyCompleteData=return_only_complete_data
            report_request.Time=time    
            report_request.ReportName="Ads Performance Report"
scope=reporting_service.factory.create('AccountThroughAdGroupReportScope')
            scope.AccountIds={'long': [account_id] }
            scope.Campaigns=None
            report_request.Scope=scope     
 
            report_columns=reporting_service.factory.create('ArrayOfAdPerformanceReportColumn')
            report_columns.AdPerformanceReportColumn.append(['AccountId','TimePeriod','CampaignId',
                'CampaignName','AdId','Impressions','Clicks','Conversions','Spend',
                'FinalUrl','CurrencyCode'])
            report_request.Columns=report_columns
 
            #return campaign_performance_report_request
            return report_request
        except:
                print("\nMS_ADS_REPORT : report processing Failed : ", sys.exc_info())

4.4. Download Ads Report:

Define a function named “download_ads_report”, the code for which is below. We are going to add this function in the “get_report.py” python module. Will calling this function soon after the Ads report request function completes successfully.

def download_ads_report(report_request,authorization_data,s_date,e_date,qry_type):
    try:
        startDate = date_validation(s_date)
        dt = startDate+timedelta(1)
        week_number = dt.isocalendar()[1]
        endDate = date_validation(e_date)
 
        reporting_download_parameters = ReportingDownloadParameters(
                report_request=report_request,
                result_file_directory = "./data/", 
                result_file_name = "ads_report.csv", 
                overwrite_result_file = True, # Set this value true if you want to overwrite the same file.
                timeout_in_milliseconds=3600000 # You may optionally cancel the download after a specified time interval.
            )
        
        #global reporting_service_manager
        reporting_service_manager=ReportingServiceManager(
            authorization_data=authorization_data, 
            poll_interval_in_milliseconds=5000, 
            environment='production',
        )
        
        report_container = reporting_service_manager.download_report(reporting_download_parameters)
        
        if(report_container == None):
            print("There is no report data for the submitted report request parameters.")
            sys.exit(0)
 
        ads_analytics_data = pd.DataFrame(columns=["account_id","campaign_name","campaign_id","start_date","end_date",
            "ad_id","cost","impressions","clicks","final_url","currency"])
 
        if "Impressions" in report_container.report_columns and \
            "Clicks" in report_container.report_columns and \
            "Spend" in report_container.report_columns and \
            "AdId" in report_container.report_columns:
 
            report_record_iterable = report_container.report_records
 
            total_impressions = 0
            total_clicks = 0
            distinct_devices = set()
            distinct_networks = set()
            for record in report_record_iterable:
                tmp_dict = {}
                tmp_dict["impressions"] = record.int_value("Impressions")
                tmp_dict["clicks"] = record.int_value("Clicks")
                tmp_dict["cost"] = float(record.value("Spend"))
                tmp_dict["conversions"] = record.int_value("Conversions")
                tmp_dict["campaign_name"] = record.value("CampaignName")
                tmp_dict["campaign_id"] = record.int_value("CampaignId")
                tmp_dict["account_id"] = record.int_value("AccountId")
                tmp_dict["ad_id"] = record.int_value("AdId")
                tmp_dict["currency"] = record.value("CurrencyCode")
 
                try:
                    utm_campaign = None
                    utm_source = 'bing'
                    o = parse.urlparse(record.value("FinalUrl"))
                    query_url = parse.parse_qs(o.query)
                    url = o._replace(query=None).geturl()
                    #utm_campaign = query_url["utm_campaign"][0]
                    #print(utm_campaign)
                    utm_campaign = "MS "+ record.value("CampaignName")
                except:
                    print("\n***UTM data extraction Failed: ",sys.exc_info())
                    pass
                
                tmp_dict["final_url"] = url
                tmp_dict["utm_campaign"] = utm_campaign
                tmp_dict["utm_source"] = utm_source
    
                ads_analytics_data = ads_analytics_data.append(tmp_dict,ignore_index = True)
 
            ads_analytics_data = ads_analytics_data.append(tmp_dict,ignore_index = True)
            ads_analytics_data["start_date"] = startDate
            ads_analytics_data["end_date"] = endDate
 
            if qry_type in ["week","weekly"]:
                ads_analytics_data["week"] = week_number
            elif qry_type in ["month","monthly"]:
                ads_analytics_data["month"] = startDate.month
            elif qry_type in ["day","daily"]:
                #ads_analytics_data["day_name"] = startDate.strftime('%A')
                ads_analytics_data["week"] = week_number
            
        #Be sure to close the report.
        report_container.close()
        return ads_analytics_data
    except:
        print("\nDOWNLOAD_ADS_REPORT : processing Failed : ", sys.exc_info())

To see what are the other report types you can request and download visit Ads Report Type. As I mentioned in the beginning the logic remains the same only the report type and parameter to request changes.

5. Final ms_ads_main.py file:

Heads up: don’t forget to replace the demo string for the account_id object with the actual Microsoft advertising account for which you wish to retrieve data.

#!/usr/local/bin/python3
# command to run this code $ python3 ./python/bing_ads_main.py -s 2020-11-22 -e 2020-11-28 -q week/day
import getopt
import sys
import os.path
import json
from datetime import datetime, timedelta
from get_report import *
from ms_authentication import *
 
def readfile(argv):
    global s_date
    global e_date
    global qry_type
    try:
        opts, args = getopt.getopt(argv,"s:e:q:")
    except getopt.GetoptError:
        usage()
    for opt, arg in opts:
        if opt == '-s':
            s_date = arg
        elif opt == '-e':
            e_date = arg
        elif opt == '-q':
            qry_type = arg
        else:
            print("Invalid Option in command line")
 
if __name__ == '__main__':
    try:
        timestamp = datetime.strftime(datetime.now(),'%Y-%m-%d : %H:%M')
        print("DATE : ",timestamp,"\n")
        print("Microsoft Advertising data extraction process Starts")

        #getting the command line parameter.
        readfile(sys.argv[1:])
 
        #loading and reading crdentials from JSON file.
        ms_cred_file = "./ms_ads_cred.json"
        ms_cred= open(ms_cred_file, 'r')
        cred_json = json.load(ms_cred)
        client_id = cred_json["client_id"]
        client_secret = cred_json["client_secret"]
        developer_token = cred_json["developer_token"]
        access_secret = cred_json["access_token"]
        refresh_token = cred_json["refresh_token"]
 
        #call authentication function
        authorization_data = ms_auth(refresh_token,client_id,client_secret,developer_token)
                account_id = "Replace with Microsoft Advertising Account ID"
        report_request = get_campaign_report(authorization_data,account_id,s_date,e_date,qry_type)
        campaign_analytics_data = download_campaign_report(report_request, authorization_data,s_date,e_date,qry_type)
        print("\ncampaign_analytics_data :\n",campaign_analytics_data)
 
        report_request = get_ads_report(authorization_data,account_id,s_date,e_date,qry_type)
        ads_analytics_data = download_ads_report(report_request, authorization_data,s_date,e_date,qry_type)
        print("\nads_analytics_data :\n",ads_analytics_data)
 
        print("MICROSOFT_ADVERTISING_MAIN : data extraction Process Finished \n")
    except:
        print("MICROSOFT_ADVERTISING_MAIN : data extraction processing Failed !!!!:", sys.exc_info())

Run the ms_ads_main.py file using command python3 ./ms_ads_main.py -s 2020-11-22 -e 2020-11-28 -q week. Where -s -> Start Date, -e -> End date and -q -> query type/date range type (i.e. Week or Day).

After a successful run, the output should look like the below screengrab.

Hope I have made the entire process of Building Python code to get Microsoft Advertising insight using Microsoft Advertising API simple enough, especially to get a campaign or ads reports.

If you have any questions or comments feel free to reach me at ->

Python code to request and download Microsoft Advertising report using Microsoft Advertising API