Don’t let a recall become a social media storm!

With food recalls increasing and averaging one a day over at the FDA website, and a steady trickle of consumer product safety recalls as well, it’s mind boggling that so many CPG companies are handling recall response so poorly.  By poorly, I mean that consumers are not getting quick resolution from official corporate channels such as the corporate website or the consumer care toll free number–but are airing their frustration on Facebook and Twitter. Within 24 hours, the frustration has gone viral and turned into a social media storm.

In many recent cases, a recall has generated so much traffic that jams the phone lines and/or crashes the brand’s website. Sometimes, the website is down, but the social media team is still directing their angry commenters to log a complaint over at the website. It is painful to watch the frustration unfold.

Then, the consumer care team stokes the fire of consumer anger by sending rebate coupons that don’t work at the supermarket

Nicole's Comment

Or don’t line up with the products that the consumers had to toss, or don’t work in the sales channel of choice or the state of residence of the consumer who receives them.

<complaint 4

It doesn’t have to be this way!

Most companies have the means of capturing the product, quantity, state of residence and retailer in their CRM systems. Whether their systems have the capacity to handle the increased load during a recall is another story.

In a food safety situation, lot or batch traceability is critical, and required by domestic and international regulations. Full traceability enables manufacturers to limit the recall to only those production lots with quality issues. The ERP system must provide full forward traceability through the distribution channels, and backward traceability into the supply chain.

Process recall readiness gaps exist in the area of documented processes, roles and responsibilities, and the pre-existence of a disaster response handling project plan/timeline.

If your business faces the threat of a product recall or another similar crisis in consumer confidence, are you really ready to handle it? Take a short self-assessment, and see how you score across the key readiness categories.

Daily Matching and Merging of Guest Data to Make the Guest Unique

In the hospitality industry, storing information about guests and their stays is critical to tailoring offers to each guest. Information regarding the guest and the stay is stored in a system called the property management system at the hotel franchise’s corporate headquarters.  The property management system stores information for each guest such as: (This is a sample list of attributes, not a complete list):

  • Prefix (Mr., Mrs., Miss, Dr., etc.)
  • Full name • Company name
  • Physical address
  • One or more e-mail addresses
  • One or more telephone numbers
  • Gender

The property management system stores information such as the following for each reservation that a guest makes (this is a sample list of attributes, not a complete list):

  • Booked date
  • Arrival date
  • Departure date
  • Folio status (booked, checked-in, checked-out, cancelled, no show, etc.)
  • Property where the guest will stay
  • How the reservation was made
  • Promotion response code (this is the code that is captured if the reservation was made after receiving promotional e-mail such as providing a discount if a reservation is booked in the next 5 days)
  • Room type booked
  • Length of stay
  • Method of payment
  • Was the reservation an advance purchase

Property management systems can be setup with centralized or decentralized data storage architecture. Let’s discuss what each of these means to storing guest profile and stay data.

Centralized PMS Data Repository:

With this type of storage architecture, all of the guest profile and guest stay data is stored in one location. Therefore, the franchise properties for a particular franchise will access all of the guest profile and stay data from a single location. What does this mean? It means that this system:

  • Reduces the amount of duplicate data entered as searching for a guest can be done against a single source
  • Records all guest stay data to a single guest profile record
  • Eliminates the need to cleanse the data by a data processing company

Decentralized PMS Data Repository:

With this type of storage architecture, the guest profile and guest stay data is stored in multiple locations. Each franchise property has its own storage and the data from each of these sources of rolls up to a single storage located at the franchise headquarters. This type of operating environment makes it very difficult to provide for the daily matching and merging of guest data to make guest data unique. Therefore, there is a definite need to cleanse the data and remove the duplicates by a data processing company. This can be done on a daily, weekly, or monthly basis.  However, frequent cleansing may not be very cost effective. So, what are potential causes for the duplicated data?:

  • There is no central database of guest profile data
  • There is no visibility into guest stays at other properties
  • Lack of a single unique identifier for the guest

While there are three main causes for duplicate data, there are also guest data anomalies/concerns that can contribute to the duplicate and inaccurate data. Here are some of the anomalies/concerns:

  • Guest data inconsistently entered across properties
  • Guest data from external sources (Hotwire, Priceline, etc.)
    • Not providing guest personal data (i.e. generic address, generic email)
  • Administrators at a company may be making the reservations for a group of people with different names and email addresses
    • Front desk staff are not updating the correct guest profile information
  • Data quality issues:
    • Guest name example: first name was blank and last name was Hotel Beds (Wholesale reservation channel)
    • Incorrect guest address, which impacts guest match rate
    • Email address of hotel property entered instead of that of the guest, with insufficient guest profile information
  • Operational Data Collection Issues:
    • The field ‘Email’ is not required to be completed
    •  Mobile phone numbers are not in PMS with proper masking and numeric fields
  • Guest data cleanup initiative is required

As one can see, a decentralized PMS architecture tends to have challenges around the uniqueness of guest profile data. The rest of this blog will focus on processes surrounding how to make a guest unique in an operating environment that has a decentralized PMS system.

The following section depicts an example solution created with Microsoft Dynamics CRM 2011 on making a guest unique in a decentralized PMS environment.  This solution includes the following components and processes:

  • PMS Database Tables: these are the tables in the PMS System that store the guest profile information such as name, address, e-mail, etc. and the reservations made by each guest
  • Guest Profile:
    • Definition: this is the record in CRM that stores basic information about the user and is a cumulative rollup of certain guest stay information
    • The basic information stored is:
      • First name
      • Last name
      • Physical address
      • Zip code
      • Telephone number(s)
      • E-mail address(s)
      • Opt-in
      • Gender
    • The cumulative rollup information stored is the following:
      • What was the method used to book the last reservation?
      • What is the frequent method of booking reservations?
      • What is the total room nights stayed across all reservations?
      • What is the total sum of revenue spent?
      • What is the total number of stays?
      • What was the last date stayed?
      • Is the guest in-house or does he/she have a pending reservation?
      • What was the average daily rate paid by the guest?
      • What is the average length of stay?
  • Guest Stay:
    • Definition: this is the record in CRM that stores information about each guest stay. A guest stay record is created anytime a guest books a reservation at a particular franchise property.  Therefore multiple guest stays can be associated to a single guest profile record
    • The information captured on the guest stay form is:
      • The date the reservation was booked
      • The date the guest plans on arriving
      • The date the guest plans on departing
      • The franchise property where the guest will be staying
      • The folio status (booked, checked-in, checked-out, cancelled, no show, etc.)
      • The type of room booked
      • The average daily rate for each particular stay
      • The length of stay for each particular stay
      • The method of payment used to book the reservation
      • Was the reservation an advance purchase?
  • Now that we know what the components are, lets discuss how these components are used in the following three processes:
    • Daily loading
    • Cleansing
    • Reconciliation

Figure 1: Guest Data LifeCycle

guest data 5-20-13

 Daily Loading Process:

The first process we will discuss is the daily loading of the reservations. For each reservation loaded into Dynamics CRM, a guest profile record is created and a corresponding guest stay record is created which is associated to the guest profile record.  For example, if John Smith makes two reservations (regardless of the reservation channel) for two rooms, he will have two guest profile records and two guest stay records in the PMS. Each guest stay record will be associated to one guest profile record.  Since the data is stored in this manner in the PMS, it is loaded from the PMS into Dynamics CRM in the same way. This means that every time a guest makes a reservation, there will be a guest profile record and an associated guest stay record created for each reservation, even if the guest stays multiple times in a short period such as a month at a franchise property. This is referred to as un-cleansed data.

Cleansing Process:

On a regularly scheduled basis, such as a weekly or monthly period, the guest profile data from the PMS is compiled into a file and sent to a data processing company. The data processing company reviews for duplicates based on the following criteria:

  • First name
  • Last name
  • E-mail address
  • Physical address:
    • Street 1
    • Street 2
    • City
    • State
    • Zip code

Reconciliation process:

(this can be weekly or monthly basis. For this discussion the reconciliation process will be monthly at the end of each month)

  • Step 1: Once the duplicate records are removed, and the cleansed file is returned, the new guest profile data is loaded into a staging database and in this database all the cleansed guest profile records are associated to their guest stay records. For example, a file containing un-cleansed guest profile records for the entire month of January was sent to the data processing company.  If there were two John Smith’s in the un-cleansed file, and then based on the above criteria, the two matching records will be compared and the one that has the most completed information would be kept.  With the returned cleansed file containing only the one John Smith guest profile, the next step is to associate all the guest stays that John Smith had for the month of January to his cleansed guest profile record.  For example, if John Smith stayed five times in the month of January, then all five stay records would now be associated with his cleansed guest profile record.
    In a nutshell when the un-cleansed file is sent to the data processing company at the end of each month it, the data being cleansed is for the month that just ended. Once the cleansed file is returned with unique guests for the month, then the guest stays for the each of those guests for that particular month is associated to each unique guest profile record.
  • Step 2: Once step 1 is completed, then the identified unique guest profiles with their associated guest stays for the previous month are loaded into Dynamics CRM 2011.  This is performed in two separate steps:
    • First the unique guest profiles are loaded
    • Then the guests’ stays are loaded and associated to the guest profile records
  • Step 3: Once step 2 is completed, then the un-cleansed guest profile and guest stay data loaded in the previous month is purged. This process will only delete all the un-cleansed guest profile and guest stay data. The cleansed guest profile and guest stay data will remain in Dynamics CRM permanently.

As one can see, when a PMS has a decentralized storage architecture, there are some processes that need to be put in place to make a guest unique.

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.

What happens when tracked records are removed between Outlook and Dynamics CRM?

Here is a small summary describing what occurs when records tracked between Outlook and Dynamics CRM are removed regardless of whether it is removed from Outlook or Dynamics CRM.

Deletions through Microsoft Dynamics CRM:

Type of Record

Dynamics CRM

Microsoft Outlook

Emails

Deleted*

Email Stays

Active(Open) Appointments

Deleted*

When the appointment is deleted in CRM, it is deleted in outlook for all users if the appointment start time is in the future.

Canceled/Completed Appointments

Deleted*

Appointment Stays

Active (Open) Tasks

Deleted*

Deleted as well

Cancelled/Completed Tasks

Deleted*

Task Stays

Contacts

Deleted*

The contact is deleted for all users except the record owner

Deletions through Outlook:

Type of Record

Microsoft Outlook

Dynamics CRM

Emails

Deleted

Email Stays

Active(Open) Appointments

Deleted

Will be Deleted* when done by Organizer. However the appointment will still remain in Dynamics CRM if deleted from one of the Attendees Outlook

Canceled/Completed Appointments

Deleted

Appointment Stays

Active (Open) Tasks

Deleted

Deleted* as well

Cancelled/Completed Tasks

Deleted

Task Stays

Contacts

Deleted

Contact Stays and will synchronize back to record owner’s Outlook when modified in the future.

Deleted* means the ‘Delete’ permission has been granted in one of the user’s security roles and this allows for the removal of CRM records.