I recently had the following demo scenario that I needed to present:
Create a Marketing Cloud Journey sending out SMSes from data in Health Cloud.
The SMS's should also reference data stored in Sales Cloud for personalisation.
The Journey audience will be patients that are to be admitted to the hospital in 5 days and need to be communicated with in the days leading up to their appointment
Sounds pretty straightforward right? I envisioned it going something like this:
Setup Marketing Cloud Connect
Setup Synchronized Data Extensions for the Objects required out of Salesforce CRM
Create Queries and Data Extensions to find the segment we need
Use the output Data Extension as the entry into Journey Builder
Setup MobileConnect Messages to be used in Journey Builder
Enjoy the rest of my day
Well, it turns out that this was a slightly more complex process than I anticipated, so I'll break it down in case anyone ever needs reference on how to do this.
Marketing Cloud Connect
This part was surprisingly the easiest part of the setup. I simply followed the Trailhead for Marketing Cloud Connect and it worked first time. It took an hour, but it worked. I prefer the Trailhead version to the help documentation as it's got a bit more context around the installation instructions.
Synchronised Data Extensions
Once I had Marketing Cloud Connect running, I started Synchronising some Data Extensions. The Object I needed was "Account", so I had to first Sync Leads, Contacts and Users to get access to Account. I will note that I needed to refresh the browser at some point to have Marketing Cloud grant me access to Account.
Mobile Connect
This is where things got a bit tricky, as I had to first figure out which fields are required in a Data Extension for use in Mobile Connect.
I thought to myself, let's try importing into MobileConnect first before attempting to use in a Journey.
Well, this turned out to be quite an experience to say the least!
MobileConnect Basics
I have learned the hard way with Marketing Cloud to never take shortcuts, but rather implement and test each part of the build before attempting the final test. It turned out that this was to be a valuable lesson in how MobileConnect functions.
Let's start off with the basics: How do I send an SMS using MobileConnect?
Uploading an Audience
I uploaded an audience into a MobileConnect List via a CSV with the following format:
SubscriberKey
MobileNumber
Locale
I was puzzled by what I should insert as "Locale". The help documentation had no example (as usual). I attempted to import with "aus" and was met with import errors.
ValidationErrors: Invalid Country Code: 1Great.
After a long and tedious process, I uncovered that a 2 digit country code ("au") should be used when importing into MobileConnect Lists, and that the documentation here is of no use but will be useful later when using MobileConnect with Data Extensions. In fact I had already written a Q&A to myself on Stack Overflow previously on how to use MobileConnect which proved to be useful in this brave new world.
The Phone Number must include the country code but should NOT have the "+".
Message Creation
OK, so we finally have a list to use as an Audience. I created the Outbound Message ("hello world!") and attempted to perform my first send.
Checking Tracking, I am greeted with these results.
Little wonder I didn't receive my SMS.
I created another Outbound Message (can't re-use the same one for some odd reason, nor delete any of them) and tried using "Test Send" to my own phone number instead of "deploying", hoping that this will somehow work.
No luck either as I didn't receive the SMS (but no tracking due to it being a Test Send without deployment)
Hrmm...
Just as a test, I sent to an Australian number (a team member) instead of a New Zealand number and he received the SMS perfectly.
It turns out that because I'm using an Australian Long Code (phone number), New Zealand carriers block all international SMSes. This prevents me from sending test messages to myself, but luckily I have many Australian colleagues that can assist.
Using a Data Extension in MobileConnect
Right! Onwards and upwards then. MobileConnect works when sending a normal SMS to a MobileConnect list, but I need to get Data Extensions to work to be used in Journey Builder.
I noticed that when choosing my Audience in MobileConnect I could select Data Extensions. There was even a hidden tooltip (which I realised much much later) explaining what the requirements of the Data Extension are:
The tooltip states that Data Extensions appear in MobileConnect if they meet the following criteria:
Contain only one phone type field
Contain only one locale type field
Subscriber key set as the primary key
Data Extension marked as sendable
Seems pretty straightforward, so I created the Data Extension exactly as mentioned.
However, when importing into the Data Extension I was hit with errors over and over again around the "Locale".
I tried using the locale I used previously ("au") when importing into a list in MobileConnect but it just wouldn't accept it.
ValidationErrors: The country code you specified: {0} is not valid: 1Validation results have been logged to a delimited text file and attached to this message.After scouting around the internet for what seemed like a unreasonable amount of time, I discovered this list of acceptable Locales to be used in a Data Extension.
Since I was sending to an Australian number, I decided on "en-au" as the value.
This worked like magic (which I'm sure is how Marketing Cloud is held together sometimes)
So, now to send an SMS to the Data Extension:
So far so good - it finds the subscribers in my Data Extension.
I was able to send an SMS to this audience successfully. Looks like I've (finally) got the correct Data Extension format to be used as an audience.
Great success! 4 hours later!
Query Activity
Back to creating the Query Activity again, now that I know what the output of the Data Extension should look like in order to send an SMS to it.
I'll use the previously created MobileConnect Data Extension as the output to my Query Activity.
Final Query (set to Overwrite):
SELECT c.id AS 'SubscriberKey'
,c.Email AS 'EmailAddress'
,a.Patient_Phone_Number__c AS 'Phone'
,'en-au' AS 'Locale'
FROM ent.Account_Salesforce_6 a
INNER JOIN ent.HealthCloudGA__EhrEncounter__c_Salesforce e ON e.HealthCloudGA__Account__c = a.ID
INNER JOIN ent.Contact_Salesforce_7 c ON c.AccountId = a.ID
WHERE cast(E.HealthCloudGA__HospitalizePeriodEnd__c AS DATE) = cast(dateadd(hh, - 1, getdate()) - 5 AS DATE)
AND e.HealthCloudGA__HospitalizePeriodEnd__c IS NOT NULLNow that I have the audience, I need to create the appropriate SMS's and perform the lookups to Health Cloud.
Interactive Messages
The message type I needed to create was "Outbound" but with the Send Type being "Interaction"
This allowed me to use the SMS in a Journey.
For the content creation, I found this link which indicated that SMS's could use AMPscript. In order to speed the process up, I created a Microsite to test and debug the AMPscript without having to rely on MobileConnect.
I proceeded to create the AMPscript, making sure to refer to the MOBILE_NUMBER as the lookup key.
SET @phone = MOBILE_NUMBERThe final AMPscript in the Landing Page was as follows.
%%[SET @phone = MOBILE_NUMBERSET @accountrs = RetrieveSalesforceObjects("Account", "ID,Name", "Patient_Phone_Number__c", "=", @phone)IF (RowCount(@accountrs) > 0) THEN SET @accountID = Field(Row(@accountrs, 1), "ID") SET @accountName = Field(Row(@accountrs, 1), "Name")ENDIFSET @appointmentrs = RetrieveSalesforceObjects("Appointment__c", "Appointment_Time__c", "Account__c", "=", @accountID)IF (RowCount(@appointmentrs) > 0) THEN SET @appointmentDT = Field(Row(@appointmentrs, 1), "Appointment_Time__c") SET @DateFieldAdjusted = DATEADD(@appointmentDT,-1,"H")ENDIF]%%Hello %%=v(@accountName)=%%. Your %%=v(@appointmentName)=%% appointment at Private Hospital is scheduled for %%=v(@DateFieldAdjusted)=%%. Do you have any dietary requirements?However, when inserting this into the SMS Message page, I realised that the form doesn't recognise line breaks, which reuslted in many of the statements running on without spaces. I inserted the code into my trusty Notepad++ and performed a quick removal of all line breaks and carriage returns.
Make sure to replace with [space] and to select Regular Expression in the Search Mode.
This could be used correctly within the Message window of MobileConnect now and the SMS's sent successfully.
Journey Builder
With all the parts built and ready to go, I created the Journey using Journey Builder and Automation Studio
Automation Studio is setup first, with the Query Activity on a scheduled basis of every day at 9am
Once the Automation has been saved, I created a new Journey with Data Extension as the Entry Source
This allowed me to choose the output Data Extension from the Query Activity.
When choosing the Schedule of the Journey, I selected Automation
And chose the Automation that I previously created
Note that the associated Data Extension is the same one from the Query Activity output
I chose "Evaluate all records" because the Query Activity will overwrite the Data Extension results every time
The Automation Status is paused, because I hadn't started it yet
Next I added the Journey Activities into the Journey
And before starting the Journey, I made sure to adjust the Settings
It's important that the re-entry mode is set to "Re-Entry anytime", else subscribers won't be able to re-enter the Journey.
Also, make sure the default Email Address and Default Mobile Number is set to "Entry Source". This ensures that any new data coming from Salesforce is used instead of existing Contact data that you might already have for your subscribers, which is very important in the case of someone updating their email address or mobile number.
And with that, it was time for me to start my Journey.
Journey Activation
I activated the Journey, then ran the Automation "once off" for testing purposes.
All went well, and my Journey started up within 5 minutes, and ran without any errors.
Conclusion
Using Journey Builder with MobileConnect is definitely easy once you know how, but the help documentation is poor at this point in time. Most of the minor issues such as Locale can take up to an hour to fix, simply because there are different values for Locales in Data Extensions and MobileConnect for some strange reason. If this was simply stated in the help guide somewhere, or even provide some sample values so much time could be saved.
I hope someone out there can find this guide useful. I know I will the next time I have to work on a similar project.
Comments