Introduction

In Part I of Tracking Opportunity Revenue by Keyword, we discussed the mechanics of how to tie a keyword used in a search engine marketing campaign to an opportunity in Microsoft Dynamics CRM 2011. In Part II, we will discuss a method for determining the revenue generated by the specific entities of each search engine marketing campaign such as keywords, ads, ad groups and campaigns. To facilitate this level of reporting, we will need to retrieve SEM campaign structure data. The following instructions apply to Microsoft’s adCenter platform, but similar procedures can be used for Google’s Adwords product as well. In addition, this blog represents one method to achieve our goal which relies on periodic importing of data. It is also possible to use the SDK toolsets of both Microsoft Dynamics CRM 2011 and Microsoft adCenter to build a near-real time integration of these two systems should the need arise.

Summary of Actions

For experienced developers, the following is a quick list of the steps required to track the revenue generated against each level of detail in the search engine marketing campaign.

1. Create the following custom entities to support the details of your SEM Campaign
   1. SEM Campaigns
   2. SEM Ad Groups
   3. SEM Ads
   4. SEM Keywords
2. Update the opportunity entity and add lookup fields for SEM Ads and SEM Keywords
3. Download and install Microsoft’s AdCenter Desktop Application
4. Using SQL Server Management Studio, connect to the adCenter Desktop local database
5. Export the campaign structure to a csv file for each entity.
6. Import the campaign structure into Microsoft Dynamics CRM 2011 using the import data wizard
7. Write a JavaScript function to update the SEM ad and SEM Keyword lookup fields based on the ad id and the keyword id on Opportunity OnLoad
8. Create new views to report on revenue per each SEM entity

Create the required custom entities in Microsoft Dynamics CRM 2011

To track the SEM Campaign hierarchy, we need to create custom entities for each level of the SEM hierarchy (Campaign, Ad Group, Ad, and Keyword). Please create the following entities and add the new fields to the default form.

1. SEM Campaign
   1. Primary Key = Campaign Name (Text 200)
   2. Campaign ID (Text 50)
2. SEM Ad Group
   1. Primary Key = Ad Group Name (Text 200)
   2. Ad Group ID (Text 50)
3. SEM Ad
   1. Primary Key = Ad Title (Text 200)
   2. Ad ID (Text 50)
   3. SEM Ad Group (lookup)
   4. SEM Campaign (lookup)
4. SEM Keyword
   1. Primary Key = Keyword(Text 200)
   2. Keyword ID (Text 50)
   3. SEM Ad Group (lookup)
   4. SEM Campaign (lookup)

Update the Opportunity Entity with new fields for each level of the SEM Campaign Hierarchy

In Part I, the Keyword ID and Ad ID were written to the lead entity using PowerWebForms. These fields were cascaded down to the opportunity entity when the lead was qualified. During this process we will create two new lookup fields that will point directly to the SEM Add Entity and the SEM Keyword Entity. Add the following fields to the Opportunity Entity:

1. SEM Ad (Lookup to SEM Ad)
2. SEM Keyword (Lookup to SEM Keyword)

Download adCenter Desktop and Retrieve the Campaign Keyword Structure

The easiest way to retrieve the campaign structure is to use Microsoft’s adCenter Desktop application.  adCenter Desktop uses SQL Server Express which allows us to write several simples query to retrieve the entire campaign hierarchy.  When you have successfully installed adCenter Desktop, use the “Add Account” feature to configure your initial account and retrieve your campaign data.Once this is complete, use SQL Server Management Studio to run the following queries:

1. Campaign = SELECT CampaignId, CampaignName FROM Campaign
2. Ad Group = SELECT AdGroupId, AdGroupName FROM AdGroup
3. Ad = SELECT Ad.AdId, Ad.AdTitle, AdGroup.AdGroupId, Campaign.CampaignId FROM Ad INNER JOIN AdGroup ON Ad.AdGroupId = AdGroup.AdGroupId INNER JOIN Campaign ON AdGroup.CampaignId = Campaign.CampaignId
4. Keyword = SELECT Keyword.KeywordId, Keyword.Keyword, AdGroup.AdGroupId, Campaign.CampaignId FROM Keyword INNER JOIN AdGroup ON Keyword.AdGroupId = AdGroup.AdGroupId INNER JOIN Campaign ON AdGroup.CampaignId = Campaign.CampaignId

Import the Campaign Structure into Microsoft Dynamics CRM 2011

Once you have exported out each portion of the campaign hierarchy above, import these into Microsoft Dynamics CRM 2011.  For subsequent updates, be sure to turn on duplicate detection so the existing entries are skipped and only the new campaigns, ad groups, ads and keywords are added.  For the SEM Ad Entity and the SEM Keyword Entity, you will need to map the lookup fields for Ad group and Campaign.  In Search Engine marketing campaigns, both ads and keywords belong to an ad group.  This design allows for A/B testing of different ads.

The following screen shot identifies the mappings required for the SEM Ad data import.

Write a JavaScript function to update the SEM Ad field and the SEM Keyword Field

When the opportunity record is created during the lead qualification step, the ad ID and keyword ID are passed to the opportunity form from the initial lead.  As these are strictly ID text fields, we need a mechanism to convert these to the appropriate lookup tables.  On the opportunity OnLoad event add the following code:

[sourcecode language="javascript"]
function UpdateSEMAdandKeyword() {

//Try

try {

//If SEMAd is Null and Ad ID has a value, Get the lookup value.
if (Xrm.Page.getAttribute("po_semad").getValue() == null && Xrm.Page.getAttribute("po_adid").getValue() != null) {

var varAdId = Xrm.Page.getAttribute("po_adid").getValue();
var entityServiceCategory = Object();
var jsonEntity = this.parent.JSON.stringify(entityServiceCategory);
var createEntityReq = new XMLHttpRequest();
var ODataPath = Xrm.Page.context.getServerUrl() + "/XRMServices/2011/OrganizationData.svc";
createEntityReq.open("GET", ODataPath + "/" + "po_semadSet?$filter=po_AdID eq '" + varAdId + "'", false);
createEntityReq.setRequestHeader("Accept", "application/json");
createEntityReq.setRequestHeader("Content-Type", "application/json; charset=utf-8");
createEntityReq.send(jsonEntity);
var newSEMAd = this.parent.JSON.parse(createEntityReq.responseText).d;

var ctlookupValue = new Array();
ctlookupValue[0] = new Object();
ctlookupValue[0].id = "{" + newSEMAd.results[0].po_semadId + "}";
ctlookupValue[0].name = newSEMAd.results[0].po_AdName;
ctlookupValue[0].entityType = "po_semad";
Xrm.Page.getAttribute("po_semad").setValue(ctlookupValue);
}

//If SEMKeyword is Null and keyword ID has a value, Get the lookup value.
if (Xrm.Page.getAttribute("po_semkeyword").getValue() == null && Xrm.Page.getAttribute("po_keywordid").getValue() != null) {

var varKeywordId = Xrm.Page.getAttribute("po_keywordid").getValue();
var entityServiceCategory = Object();
var jsonEntity = this.parent.JSON.stringify(entityServiceCategory);
var createEntityReq = new XMLHttpRequest();
var ODataPath = Xrm.Page.context.getServerUrl() + "/XRMServices/2011/OrganizationData.svc";
createEntityReq.open("GET", ODataPath + "/" + "po_semkeywordSet?$filter=po_KeywordId eq '" + varKeywordId + "'", false);
createEntityReq.setRequestHeader("Accept", "application/json");
createEntityReq.setRequestHeader("Content-Type", "application/json; charset=utf-8");
createEntityReq.send(jsonEntity);
var newSEMKeyword = this.parent.JSON.parse(createEntityReq.responseText).d;

var ctlookupValue = new Array();
ctlookupValue[0] = new Object();
ctlookupValue[0].id = "{" + newSEMKeyword.results[0].po_semkeywordId + "}";
ctlookupValue[0].name = newSEMKeyword.results[0].po_Keyword;
ctlookupValue[0].entityType = "po_semkeyword";
Xrm.Page.getAttribute("po_semkeyword").setValue(ctlookupValue);
}

//Catch
} catch (e) {

}

return null;
}[/sourcecode]

Create a new Opportunity view to include the SEM campaign structure

1. Using Advanced Find, create a new view from the Opportunity Entity.
2. Add additional columns for each level of the SEM Campaign hierarchy
   1. SEM Campaign
   2. SEM Ad Group
   3. SEM Ad Name
   4. SEM Keyword

Conclusion

In Part II, we learned how to import our SEM Campaign hierarchy into Microsoft Dynamics CRM and how to tie this structure to the opportunities we created in Part I.  This allows us to view the revenue generated from closed opportunities against the keywords, ad’s and campaigns in our search engine marketing campaign.

In our next and last installment (Part III), we will learn how to import the expenses relating to the cost per each click and tie these back to revenue to get a evaluation of our marketing spend.

Just getting started with marketing for Microsoft Dynamics CRM? Check out this post on how to use CRM 2011 to track marketing ROI.

Happy CRM'ing!