In this webinar, our experts showcase a variety of demo use cases of how different components of the...
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.
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.
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.
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:
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:
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.
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]
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!