Sitecore Personalization using Customer Order History

Sitecore experience platform allows the ability to personalize content using aggregate data from a external system. To setup Sitecore Commerce to personalize you need to create a condition under /sitecore/system/Settings/Rules/Definitions/Elements/Order History. In the below example the condition will be "Product purchased in past".

Below are the values for /sitecore/system/Settings/Rules/Definitions/Elements/Order History

Text: where the [ProuctId,Tree,root=/sitecore/Content/Product Repository/Products,specific] was purchase.

Type: XCommerce.Analytics.Rules.Conditions.HasProductPurchased,XCommerce

Next we need to create the C# class. The class will call the ERP database to see if the product has been purchased. The class will inherit the WhenCondition and RuleContext.

using Sitecore.Analytics;
using Sitecore.Data;
using Sitecore.Data.Items;
using Sitecore.Diagnostics;
using Sitecore.Rules;
using Sitecore.Rules.Conditions;
using System;
using System.Data.SqlClient;

namespace XCommerce.Analytics.Rules.Conditions
 {
    public class HasProductPurchased : WhenCondition where T : RuleContext
    {
    }
}

The user will have the ability to select the product. To support this we will add the ProductID property. The main logic will be located in the Execute function. If the function return true the DMS rule will be applied.

        public Guid ProductId { get; set; }

        public HasProductPurchased()
        {
        }

        protected override bool Execute(T ruleContext)
        {
        }

To lookup the product data we will need to get the customer id and the product id. The logic to get the product id will be in a seperate function. The customer id will be pulled from the Tracker.Current.Contact.

            Guid productExternalId = GetProductId();
            Guid contactId = Tracker.Current.Contact.ContactId;

The GetProductId function will lookup the Sitecore item using the ProductID property. The External ID value will be used to identify the ERP ID.

        private Guid GetProductId()
        {
            Item productItem = Sitecore.Context.Database.GetItem(new ID(ProductId));

            Guid productExternalId;

            if (productItem != null)
            {
                if (Guid.TryParse(productItem["External ID"], out productExternalId))
                {
                    return productExternalId;
                }
            }
            return Guid.Empty;
        }

The next step will be calling a SQL query to get the order history for a specific product. For security reasons properties are used to stop SQL injunction attacks.

            string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["Erp"].ConnectionString;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(
                  @"SELECT COUNT(Distinct c.Id) 
                    From CustomerOrder c INNER JOIN OrderLine ol
                       ON c.Id = ol.CustomerOrderId
                    WHERE
                       CustomerId = @customerId AND
                       ol.ProductId = @productId;", connection))
                {
                    connection.Open();
                    command.Parameters.AddWithValue("@customerId",contactId);
                    command.Parameters.AddWithValue("@productId",productExternalId);
                    int? ordersMatchCount = (int?)command.ExecuteScalar();
                    connection.Close();

                    return (ordersMatchCount != null && ordersMatchCount > 0);
                }
            }

Below is the working example.

using Sitecore.Analytics;
using Sitecore.Data;
using Sitecore.Data.Items;
using Sitecore.Diagnostics;
using Sitecore.Rules;
using Sitecore.Rules.Conditions;
using System;
using System.Data.SqlClient;

namespace XCommerce.Analytics.Rules.Conditions
 {
    public class HasProductPurchased : WhenCondition where T : RuleContext
    {
        public Guid ProductId { get; set; }

        public HasProductPurchased()
        {
        }

        protected override bool Execute(T ruleContext)
        {
            Assert.ArgumentNotNull((object)ruleContext, "ruleContext");
            Assert.IsNotNull((object)Tracker.Current, "Tracker.Current is not initialized");
            Assert.IsNotNull((object)Tracker.Current.Contact, "Tracker.Current.Contact is not initialized");

            Guid productExternalId = GetProductId();
            Guid contactId = Tracker.Current.Contact.ContactId;
            
            string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["Erp"].ConnectionString;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(
                  @"SELECT COUNT(Distinct c.Id) 
                    From CustomerOrder c INNER JOIN OrderLine ol
                       ON c.Id = ol.CustomerOrderId
                    WHERE
                       CustomerId = @customerId AND
                       ol.ProductId = @productId;", connection))
                {
                    connection.Open();
                    command.Parameters.AddWithValue("@customerId",contactId);
                    command.Parameters.AddWithValue("@productId",productExternalId);
                    int? ordersMatchCount = (int?)command.ExecuteScalar();
                    connection.Close();

                    return (ordersMatchCount != null && ordersMatchCount > 0);
                }
            }
        }

        private Guid GetProductId()
        {
            Item productItem = Sitecore.Context.Database.GetItem(new ID(ProductId));

            Guid productExternalId;

            if (productItem != null)
            {
                if (Guid.TryParse(productItem["External ID"], out productExternalId))
                {
                    return productExternalId;
                }
            }
            return Guid.Empty;
        }
    }
}

The above steps show how to setup DMS to personalize the website using the customers Order History. This will allow the ability to show specific promotions based on the users order history.