Creating Automated Emails in Dynamics CRM using ExactTarget

One of the key benefits of using a third party direct email marketing tool with Dynamics CRM is the ability to automate the sending of emails when some event happens or a period of time elapses. This blog discusses how to create an automated email sent from Dynamics CRM using ExactTarget. The email will be scheduled to be sent to a dynamic marketing list every 15 minutes. In practice you would use this method to send an email every 30 days or some similar time frame, or when some event, such as the creation of a particular record, occurs.

The first thing you need to do is create a dynamic marketing list in CRM. To do this, go to Marketing, Marketing Lists and click New.

On the Marketing List screen, enter a Name, set the Contact Type and set the Type field to Dynamic.

Save the marketing list record and click Manage Members.  The query screen will appear in which you can specify your criteria which will be executed each time the email is sent out.  Click the Use Query button when the query is ready.

Once you have your marketing list ready, go to File, ExactTarget, Create Marketing Automation ExactTarget Send.

On the Send ExactTarget Email screen, select a pre-defined Email template, Field Mapping Set, create a Subject, set the From value, select a Campaign if desired, check the “Return individual tracking results as ExactTarget Responses” and “I certify all email recipients have opted in” checkboxes and click OK.

Now that you have an ExactTarget Send record, emails will be sent through ExactTarget to the marketing list every time an ExactTarget Recipient record is created. To automate the sending of emails, you will us a CRM workflow to create the ExactTarget Recipient record when you want the email to go out. To do this, go to Settings, Processes and click New.

On the process screen, enter a Name, set the Entity Type to ExactTarget Send and the Category to workflow.  Leave the “New blank process” radio button selected.  Click OK to go to the Process definition screen.

On the Process definition screen, check the “As an on demand process” and “As a child process” checkboxes, clear the New Record checkbox and save the record.

Now you need to create the steps of the workflow.  Click Add Step and select the Create action.  Select ExactTarget Recipient as the entity type to create.  Click Set Properties to set the values of the ExactTarget Recipient record.  Set the Name, Type, Individual or Marketing List field depending on the value of the Type field, and select the ExactTarget Send record.

After setting the properties of the ExactTarget Recipient record, set the time period to wait until the next send.   To do this, click Add Step and select Wait Condition.

Click on “<condition> (click to configure)” to open the Wait Condition screen.

When the Wait Condition screen appears, select Process, Timeout, Equals, 15 minutes duration and Save and Close.

To run the workflow again after the specified time period has elapsed, add a step to call itself as a child workflow.  To do this, click Add Step and select Start Child Workflow.

Select the workflow itself as the child workflow and save the workflow.  It should now look similar to this:

Now activate the workflow and every fifteen minutes it will execute the marketing list query and create an ExactTarget Recipient record to send the email to the current members of the dynamic marketing list.

FetchXML versus Query Expression

Microsoft Dynamics CRM 4 provides two main mechanisms for querying data from Microsoft Dynamics CRM, FetchXML and QueryExpression.  In general, it is recommended to use QueryExpression over FetchXML because of its better performance and strongly typed results.  But, FetchXML is handy when you need attributes from multiple entities in a single query.  This blog will discuss each type of query and provide an example of each to return the same result.

QueryExpression is class based and is easy to use, although you can only query one entity type at a time.  Therefore, if you need to get data from a parent entity, such as a contact, and then get data from a child entity, such as a custom product entity, you have to perform two queries.  First you must retrieve the contact to get access to its attributes, then you must retrieve the product using the contact id that links the contact to the product.  With QueryExpression, you can specify the attributes you want returned, or indicate you want all attributes returned, you can specify “and” conditions or ”or” conditions, and you can specify a sort order.  To execute a QueryExpression query:

  • Call the Retrieve or RetrieveMultiple methods on the CrmService web service
  • Receive a BusinessEntityCollection containing the results of the query

With FetchXML you can query multiple entity types at the same time as long as there is a relationship between the types.  You can retrieve attributes from multiple entities in one query in this fashion. To execute a FetchXML query:

  • Create a fetch string specifying the query and pass it to the CrmService web service’s fetch method
  • The fetch string contains XML, which specifies the query criteria

Example of QueryExpression:

      QueryExpression query = new QueryExpression();
      query.EntityName = EntityName.contact.ToString();
      ColumnSet columns = new ColumnSet();
      columns.Attributes =
                         new string[] { “contactid”, “lastname”, “firstname” };

      ConditionExpression whereClause = new ConditionExpression();
      whereClause.AttributeName = “lastname”;
      whereClause.Operator = ConditionOperator.Equal;
      whereClause.Values = new string[] { “Jones” };

      FilterExpression filter = new FilterExpression();
      filter.FilterOperator = LogicalOperator.And;
      filter.Conditions = new ConditionExpression[] { whereClause };

      OrderExpression orderBy = new OrderExpression();
      orderBy.OrderType = OrderType.Descending;
      orderBy.AttributeName = “createdon”;

      query.ColumnSet = columns;
      query.Criteria = filter;
      query.Orders = new OrderExpression[] { orderBy };

BusinessEntityCollection retrieved =
crmService.RetrieveMultiple(query);

Example of FetchXML:

string fetch = @”
   <fetch mapping=””logical””>
       <entity name=””contact””>
            <attribute name=””contactid””/>
            <attribute name=””lastname””/>
            <attribute name=””firstname””/>
            <order attribute=””createdon””/>
                 <filter>
                     <condition attribute=””lastname”” operator=””eq””
                        value=””Jones””/>
                 </filter>
         </entity>
   </fetch>”;
   try
  { 
           string result = service.Fetch(fetch);
   }
   catch(System.Web.Services.Protocols.SoapException se)
   {
            // handle exception
   }

Marketing List Issues in Microsoft Dynamics CRM 4

There are two main issues I have encountered with marketing lists in Microsoft Dynamics CRM 4:

  1. There are limitations in using advanced find to identify contact, account or lead records to include in the marketing list.
  2. Marketing lists in Microsoft Dynamics CRM 4 only store the contact id.

Both make it difficult to use Microsoft Dynamics CRM 4 to create a marketing list and pull product specific data for each item in the list to include in marketing materials, such as letters or brochures.  I will discuss each limitation in more detail below.

Advanced Find

The main problem with using advanced find to identify contacts, accounts or leads to include in a marketing list lies in the fact that advanced find works from the parent entity (contact, account or lead) down to the child entities (products bought for example).  Many times, however, the search criteria for a marketing list needs to work from the child entities up to the parent entity.  For example, you might need to identify a product based on specific criteria and then navigate up the entity chain the find the contact who owns that product.  Using advanced find, you cannot identify a product based on specific criteria and work back up to the contact.

The second problem with using advanced find to indentify contacts, accounts or leads to include in a marketing list is that you can’t compare two records by an attribute value.  For example, if a contact purchased product A on January 1, 2008 and then bought replacement product B on June 6, 2010, you can’t search for Product A and try to find a matching product B based on serial number or some other attribute.  Many real world query criteria for generating marketing lists rely on such complex matching scenarios, which advanced find cannot handle.

Identification by contact ID

Microsoft Dynamics CRM 4 marketing lists only store the contact, account or lead id, no attribute fields.  You cannot modify a marketing list to include other fields, such as serial number, product description, or some other attribute.  This issue arises whenever there is a one-to-many relationship between the contact, account or lead and its child entity.  For example, suppose a customer owns two cars, a red Ford Mustang and silver Nissan Altima.  If some attribute of the Altima, such as the brand of tire on the car, results in the customer being on the marketing list, but the Mustang’s brand of tire would not place the customer on the marketing list, you cannot later run a program that will send a letter to the customer and include specific information about the Altima in the letter because all you have is the contact, account or lead id.  You have no way to determine which car should be used to fill out the letter.

Solution

To work around the issues, we used an SSRS report and stored procedure to identify the contact records to include in the list.  We then exported the report results to a comma separated file, which included the contact id as well as specific attribute data for the contacts child entities.  We called a web service to create a marketing list in CRM using the CRM APIs and wrote the attribute data to a database table that linked the marketing list and contact to the correct attribute data.  To process the marketing list, we got the marketing list and looped through the contacts.  For each contact record in the marketing list, we pulled the attribute data from the database table and were then able to include that data in marketing materials.