A combination of engineering and marketing for your online success

Using adCenter Reporting Service API for a large number of keywords

Introduction I usually use the adCenter Reporting Service API when I need to investigate or analyze KPI’s for a large advertiser or an agency. It is very straight forward and easy

and I refer to the online adCenter Reporting Service API documentation at http://msdn.microsoft.com/en-us/library/aa983013.aspx A very good and simple example is available at http://msdn.microsoft.com/en-US/library/bb671680(v=msads.60).aspx  Why API? I am sure many folks might be wondering why to use the API when the UI is available. If you wanted flexibility with the reports including automation and custom queries:

  • The UI only allows you to run a report for 500 accounts but the API allows you to run reports for 1000 accounts at a time.
  • Through the UI, you can only get files in a csv or tsv format but through the API you can get files in csv, tsv or XML format.
  • You have more options available through the API for the output column compared to the UI.
  • If you want more control over your reports, you would want to use the API

 Get a dev token Before you start coding away to get KPI’s for an advertiser, you need a dev token that you will need to get from Microsoft and you will need the username and password that you access adCenter UI with. More of this in a future post.  Using yield and LINQ One of the restrictions in the current API is that it limits the number of keywords that you can filter on to 75. I needed to write code to get the information for 1 account that had over 10,000 keywords. With the help of some nice yield, skip and take magic, I got the code ready in C#.

private static IEnumerable GetKeywords(string[] masterKeywordList, int count)
{
int cnt = 0;
while (cnt < masterKeywordList.Length)
{
var keywordSubset = masterKeywordList.Skip(cnt).Take(count).ToArray();
cnt = cnt + count;
yield return keywordSubset;
}
}

Input
As input, I take in the username/password to adCenter. You will need to update the devtoken key in app.config for the API call the work correctly. You need to provide the accounts for which you want the reports in the file acct.txt (1 account per line). You also need to provide the keywords on which to filter in the file kw.txt (1 account per line). Download the code here. I also use DotNetZip library to unzipping the reports and extracting the csv files.  For those who love to see code, my solution looks like:

ReportingAPIClientSolnExplorer

Here is how the main program looks like:

using System;
using System.Configuration;
using System.IO;
using System.Linq;
using System.Windows.Forms;
using System.Reflection;
using System.Collections.Generic;
using System.Collections;

namespace Reporting_API_Client
{
public class EntryProgram
{
public static void Main(string[] args)
{
try
{
string username = args[0];
string passwd = args[1];
string devToken = ConfigurationManager.AppSettings["DevToken"];

//Output file name
string currentAssemblyDirectoryName = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);

//Account file
string accountIdsFile = Path.Combine(currentAssemblyDirectoryName, "acct.txt");

//Read accounts from the files
List<long> accountList = new List<long>();
using (StreamReader r = new StreamReader(accountIdsFile))
{
string line;
while ((line = r.ReadLine()) != null)
{
long acctId;
if (long.TryParse(line, out acctId))
accountList.Add(acctId);
}
}

//keyword file name
string kwtxtFile = Path.Combine(currentAssemblyDirectoryName, "kw.txt");

//Read keywords from kw file
string[] keywordsFromFile = File.ReadAllLines(kwtxtFile);

List<string> reportRequestIdList = new List<string>();
// process 75 keywords at a time
foreach (string[] kl in GetKeywords(keywordsFromFile, 75))
{
//call the Keyword Performance Report
string repReqId = KeywordPerformance.GetKeywordPerformanceReport(username, passwd, devToken, accountList, kl);
reportRequestIdList.Add(repReqId);
}

// now that we have queued all requests, poll them and download the completed ones
do
{
reportRequestIdList = KeywordPerformance.CheckAndDownloadReports(username, passwd, devToken, reportRequestIdList);
} while (reportRequestIdList.Count != 0);

// todo: now that all reports are done, merge all csv files
}
catch (Exception e)
{
Console.WriteLine(e);
}

Console.WriteLine("Hit any key to close...");
Console.ReadKey();
}

private static IEnumerable GetKeywords(string[] masterKeywordList, int count)
{
int cnt = 0;
while (cnt < masterKeywordList.Length)
{
var keywordSubset = masterKeywordList.Skip(cnt).Take(count).ToArray();
cnt = cnt + count;
yield return keywordSubset;
}
}
}
}


The real magic of calling the API’s to schedule the report, poll and download happens as shown below:

using System;
using System.IO;
using System.Net;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ServiceModel;
using Reporting_API_Client.ReportingService;
using Ionic.Zip;

namespace Reporting_API_Client
{
class KeywordPerformance
{
public static string GetKeywordPerformanceReport(string username, string password, string devToken, List<long> accountIds, string[] keywordList)
{
var reportRequest = CreateRequest(accountIds, keywordList);

// Create and initialize the ReportingServiceClient object.
var service = new ReportingServiceClient();

string reportRequestId = null;

try
{
// Submit the report request.
Console.WriteLine("Submitting report...");
string trackingId = service.SubmitGenerateReport(null, null, null, devToken, password, username, reportRequest, out reportRequestId);
Console.WriteLine("Report Submitted. Report request id: {0},\n\tTracking Id: {1}", reportRequestId, trackingId);
}
catch (FaultException fault)
{
// Catch generic errors, such as an authentication error
var faultDetail = fault.Detail;
foreach (var opError in faultDetail.Errors)
{
Console.WriteLine(String.Format("Error {0}:", opError.Code));
Console.WriteLine(String.Format("\tMessage: \"{0}\"", opError.Message));
}
}
catch (FaultException fault)
{
// Catch web service specific errors, such as when the request message contains incomplete or invalid data.
var faultDetail = fault.Detail;
foreach (var opError in faultDetail.OperationErrors)
{
Console.Write("Operation error");
Console.WriteLine(" '{0}' ({1}) encountered.", opError.Message, opError.Code);
}

foreach (var batchError in faultDetail.BatchErrors)
{
Console.Write("Batch error");
Console.Write(" '{0}' ({1}) encountered", batchError.Message, batchError.ErrorCode);
}
}
catch (Exception e)
{
// Catch client related exceptions.
Console.WriteLine("Error '{0}' encountered.", e.Message);
}
finally
{
// Make sure you close the service.
service.Close();
}

return reportRequestId;
}

private static KeywordPerformanceReportRequest CreateRequest(List<long> accountIds, string[] keywordList)
{
// Create and initialize the report request object.
var reportRequest = new KeywordPerformanceReportRequest();

reportRequest.ReportName = "Keyword Performance Report";
reportRequest.Format = ReportFormat.Csv;
reportRequest.ReturnOnlyCompleteData = false;
reportRequest.Aggregation = ReportAggregation.Monthly;

// Create and initialize a ReportTime object.
reportRequest.Time = new ReportTime();

// TODO: change the time period if you want a different month
reportRequest.Time.CustomDateRangeStart = new Date { Day = 1, Month = 4, Year = 2012 };
reportRequest.Time.CustomDateRangeEnd = new Date { Day = 30, Month = 4, Year = 2012 };

// Specify the columns that will be in the report.
reportRequest.Columns = new[] {
KeywordPerformanceReportColumn.TimePeriod,

KeywordPerformanceReportColumn.AccountName,
KeywordPerformanceReportColumn.CampaignName,
KeywordPerformanceReportColumn.AdGroupName,

KeywordPerformanceReportColumn.AdDistribution,
KeywordPerformanceReportColumn.Keyword,
KeywordPerformanceReportColumn.CurrentMaxCpc,
KeywordPerformanceReportColumn.Impressions,
KeywordPerformanceReportColumn.Clicks,
KeywordPerformanceReportColumn.Ctr,

KeywordPerformanceReportColumn.AverageCpc,
KeywordPerformanceReportColumn.Spend,
KeywordPerformanceReportColumn.AveragePosition,
};

// Specify the scope of the report.
long[] acctIds = accountIds.ToArray();
reportRequest.Scope = new AccountThroughAdGroupReportScope { AccountIds = acctIds, AdGroups = null, Campaigns = null };

// Specify the filter for the report.
reportRequest.Filter = new KeywordPerformanceReportFilter
{
AdDistribution = AdDistributionReportFilter.Search, //set distribution to search
LanguageAndRegion = LanguageAndRegionReportFilter.UnitedStates, //set country to US
Keywords = keywordList //get the keyword seperated list
};
return reportRequest;
}

public static List<string> CheckAndDownloadReports(string username, string password, string devToken, List<string> reportRequestIdList)
{
List<string> needsProcessing = new List<string>();

using (var service = new ReportingServiceClient())
{
foreach (string s in reportRequestIdList)
{
Console.WriteLine("Requesting the status of the report...");
ReportRequestStatus reportRequestStatus;
string trackingId = service.PollGenerateReport(null, null, null, devToken, password, username, s, out reportRequestStatus);
Console.WriteLine("\tTracking ID: {0}", trackingId);

if (reportRequestStatus.Status == ReportRequestStatusType.Success)
{
DownloadReport(reportRequestStatus.ReportDownloadUrl, "report.zip");
}
else
{
// still needs processing time
needsProcessing.Add(s);
}
}
}

return needsProcessing;
}

private static void DownloadReport(string downloadUrl, string fileName)
{
// download zip
WebClient wc = new WebClient();
wc.DownloadFile(downloadUrl, fileName);

// extract the files
using (ZipFile zip1 = ZipFile.Read(fileName))
{
foreach (ZipEntry e in zip1)
{
e.Extract("reports", ExtractExistingFileAction.OverwriteSilently);
}
}
}
}
}


If you run into issues, feel free to reach out to me about it.
 

1 Comment
  1. Thanks for the great article..

Leave a Reply