In this CRM How-To, we will outline how to tie revenue associated with offline sales to an individual keyword used in a paid search marketing campaign using Microsoft adCenter, Microsoft Dynamics CRM 2011, and PowerObject’s PowerWebForm.



The Holy Grail of marketing is the ability to track revenue generated from a campaign as well as the associated costs to determine a true return on investment (ROI).  Measuring ROI is critical for marketing departments with limited resources as it allows them to spend money where it will have the biggest impact.

Determining what marketing campaign was responsible for the revenue can be a daunting process regardless of how and where you sell products.  Most companies use multiple channels and multiple messages to promote their products.  In many cases, a customer may hear several promotional messages over time before deciding to purchase.

With the advent of search engine marketing in the late 90’s, tracking revenue became much easier as for all web-based transactions.  Unfortunately for products or services with longer sales cycles that are typically tracked via customer relationship management systems, we need to use a different approach to track revenue by keywords.

Summary of Actions

For experienced developers, the following is a quick list of the steps required to track opportunity revenue by keyword.  More detailed step-by-step instructions follow below.

  1. Add query string to ad in AdCenter
  2. Customize the lead and opportunity entities in Dynamics CRM 2011 to add a field for ad id and keyword id
  3. Create a new PowerWebForm and create two hidden fields for ad id and keyword ID
  4. Retrieve the PowerWebForm html source code
  5. Convert the html code to an page and modify the code to grab the query string and insert it into the value field of the hidden fields.
  6. Deploy the lead page to your website
  7. Update the Lead to Opportunity mapping to make sure the fields are moved to opportunities when qualified.
  8. Verify the ad id and keyword id are visible in the opportunity.

Add Query String Parameters

To identify the ad and keyword associated with a click, we need to use query string parameters in Microsoft adCenter.  Query strings are appended to the Destination URL automatically by adCenter when a user clicks on the link.  Add the query string as follows:

  1. Locate the ad you wish to track and click the ad title to edit the ad
  2. In the Destination URL field, add “?Adid={AdID}&keywordid={OrderItemID}”  to the end of the URL.
  3. Save the ad

track opportunity revenue by keyword

Customize Lead and Opportunity Entities

We need a placeholder in the lead and opportunity entities to store the ad id and keyword id.

  1. Create new fields in the lead and opportunity entities for ad ID and Keyword ID.

microsoft adcenter and keyword tracking in CRM

  1. Add these fields to the lead and opportunity forms so you can ensure that the id’s are moving across correctly

track revenue in crm

Associating Leads with the Keyword

When the potential customer clicks on your ad, they are directed to a landing page on your site and the query string contains the values for ad id and keyword id. In order to make the connection to your CRM lead record, you will need to pass these values back to CRM whenever you capture information about your potential customer.  There are several different techniques you can use:

Option #1 – PowerWebForm as your landing page

Some companies prefer to use a lead capture form as their landing page.  This technique works well when the ad text has a specific offer that is actionable by the customer.  For example, “Register for our weekly newsletter”.  In this example, the user has the expectation that they are registering for a newsletter so asking for personal information is expected.  Create the PowerWebForm as follows:

  1. From Web Forms view, click New and add a new Web form.
    1. Make sure to enter Lead in the Related Entity field
    2. Click Save & Close

track revenue in crm

  1. After saving the new form, click inside the “Form Fields” sub grid and click Add New Form Field in the ribbon bar.
    1. Enter “0” in the Display Order field to indicate that First Name is the first field you want displayed
    2. Enter “First Name” for the field label
    3. Choose “Text” as the Field Type and enter “100” in the field length value
    4. Change the “Field Required” box to match the requirements of your entity in CRM.
    5. Click Lookup Field” and choose the CRM entity field that matches your field.  For example, “firstname”
    6. Click Save & New
    7. Repeat this process for each field you want in your lead capture form.

track revenue in crm

  1. After you have finished creating your standard fields, add two hidden fields for ad ID and keyword ID.
    1. Enter a number in the  in the Display Order field that is one greater than the last field you added
    2. Enter “Ad ID” for the field label
    3. Choose “Hidden” as the Field Type
    4. Make sure the “Field Required” box to is set to no
    5. Click Lookup Field” and choose your Ad ID field
    6. Click Save & New
    7. Repeat the process for keyword ID

track revenue in crm

  1. After you have finished creating the hidden fields, click Save & Close

Option #2 – Store query string values in a cookie for later use

For companies that want to use a more traditional landing page, we need to create a cookie to store the query string.  Using a cookie allows us to grab the query string if the user comes back to our site at a future date and completes the lead capture form. In addition, if the user clicks on a second ad with a different query string parameter, you will want to overwrite the current cookie to reflect the latest ad they clicked.     You can find more information on ASP.Net cookies here (

Once you have completed the process of creating the cookies, follow the instructions in option #1 to create the lead capture form via the Power Web Form. 

Retrieve Web Form Source Code

  1. After you have saved the last hidden field, click the Preview and Deploy icon in the ribbon bar of the Web Form record.

track revenue in crm

  1. In the Preview and Deploy Dialog Window, click Copy URL

track revenue in crm

  1. Open Internet Explorer, paste the URL into the address window and press Enter

track revenue in crm

  1. Right-click on the page and select View Source

track revenue in crm

  1. Select the code and copy the contents to your buffer.

Convert to Page and Grab Query String

To fill the hidden fields with the values from our query string we need to alter the page code.

  1. In Visual Studio, create a new ASP.Net web project called “KeywordToLead”

track revenue in crm

  1. Add a new web form to the project named “LeadCapture”

track revenue in crm

  1. Replace the HTML code in the newly created web form with the code you copied from the CRM Web Form.  Make sure you don’t erase the @ Page declaration in the first line.

track revenue in crm

  1. If you are using your lead capture form as your landing page, you can insert code to read the query string and insert into the value field of the hidden form elements
    1. Find the Ad ID form element
    2. Inert “<%=request.querystring(“adID”) %>” between the quotes in the value field for the Ad ID form element.
    3. Inert “<%=request.querystring(“keywordID”) %>” between the quotes in the value field for the keyword ID form element
    4. Save  your changes and close

track revenue in crm

  1. If you are using cookies or another programming language, you will want to make the proper modifications.  The goal here is to set the original query string to the value of the hidden field so it is passes to Dynamics CRM.

Deploy and Test Web Page

At this point, you should be able to test the web page in Visual Studio.

  1. In the start options section of Visual Studio, enter a test query string and click “Specific Page”.   Press F5 or Run to start testing

track revenue in crm

  1. Make sure the query string is appended to the url
  2. Enter “Joe” for the first name and “CRM” for the last name and click Submit

track revenue in crm

  1. After the submission is successful, you may close the window.

Passing ID’s from Lead to Opportunity

Now that our ad ID and keyword id’s are stored in the lead entity, we need to make sure these entities are passed to opportunities whenever a lead is converted.  To accomplish this, we need to edit the relationship between leads and opportunities and add mapping elements for our customer id fields.

  1. In the customization window, click 1:N Relationships under the Lead entity.
  2. Find the record for related entity = Opportunity and double-click.

track revenue in crm

  1. In the Lead to Opportunity window, click Mappings in the navigation menu

track revenue in crm

  1. In the Mappings view, click New in the toolbar just above the list of mappings

track revenue in crm

  1. In the Create Field Mapping window, find “new_adid” in the source and target lists.  Check both and click OK.  Repeat for “new_fieldID”

track revenue in crm

  1. After adding both mappings, save and close.


Open Microsoft Dynamics CRM 2011 and proceed to your leads view.  You should see a record for Joe CRM

track revenue in crm

  1. Double-click on the record and scroll down to where you added the custom ad id and keyword id fields to the lead form. You should see your ad id and keyword id values.

track revenue in crm

  1. Click the Qualify button
  2. Check account and opportunity
  3. Click OK

track revenue in crm

  1. Go to your open opportunities view and find the new opportunity.  You should see Joe CRM as the original lead as well as your ad id and keyword id.

track revenue in crm


So in Part I of Tracking Opportunity Revenue by Keyword, we discovered how we can instruct Microsoft’s adCenter to include ad id’s and keyword id’s into the destination URL for our ad.  We also learned how to use PowerObjects’ PowerWebForm and ASP.NET to associate these values to leads generated through a lead capture form.  And lastly, we learned how to map the id’s from lead to opportunity during the lead qualification process.  By doing so, we can begin to build analytics around how much revenue is generated by a click for individual keywords.

In Part II, we will take the next step and dig deeper into how we can evaluate not only the value of a keyword, but the value of ad, campaign or even the differences between revenue generated by search engines.

Happy CRM’ing!

Avatar for Joe D365

Joe D365

Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.