0

I would like to have "Add to Calendar" in the Email. My idea is to write the code to Javascript to generate file.ICS in CloudPage and link it with the button. When the Email receiver clicks on the button, the file.ICS will be downloaded.

enter image description here

I have a Journey Builder which is triggered from Sales Cloud. I have a CloudPage and I would like to retrieve the information of the "Date field" from the triggered record. I quite have no idea how to write the code to retrieve the "Date field" information in CloudPage. Below is the code that I have

    %%[
VAR @sid, @jid, @reason, @lue, @lue_prop, @lue_statusCode, @overallStatus, @requestId, @Response, @Status, @Error, @curEmail

SET @sid = AttributeValue("_subscriberkey")
SET @jid = AttributeValue("jobid")
SET @listid = AttributeValue("listid")
SET @batchid = AttributeValue("_jobsubscriberbatchid")
SET @reason = "One Click Unsubscribe"

VAR @subscriberRows,@result

SET @subscriberRows = RetrieveSalesforceObjects(
   "Contact",
   "AccountId, Email",
   "Id", "=", @sid )

if RowCount(@subscriberRows) == 1 then
  VAR @subscriberRow, @AccountId
  SET @subscriberRow = Row(@subscriberRows, 1)
  SET @AccountId = Field(@subscriberRow, "AccountId")
  SET @curEmail = Field(@subscriberRow, "Email")
endif

]%%
2
  • You need to pass it in the button URL using the CloudPagesURL function and then retrieve it on the CloudPage using RequestParameter function
    – zuzannamj
    Feb 2 at 9:21
  • ... or you write an AMPScript lookup into a data extension, which could be the sendout DE. The difference in the approach is that if you use a lookup, the date would be pulled at the time of click, which is the most up-to-date you can get this to be - if it's in the Button URL as encrypted parameter, then it is "frozen" from the time of email sendout. What's "better" here is more or less usecase dependent. Feb 2 at 9:24

1 Answer 1

1

Bit of an overreach here, but I can provide a pretty generic reference draft for the whole "generate .ics calendar invite" workflow. I figured it would be easier to share it whole than dissect it to illustrate specifically the part about sending data, which is of course the integral question.

Both relevant ways (sending data in an encrypted link & looking data extension data up a cloudpage) are used in the solution.

The base .ics creation solution for this was presented at Dreamforce 2019 by someone else (thanks!!), so I cannot by any means take full credit, just expanded upon it. Just take that as a bonus.

This "calendar invite" solution has the following parts:

A) assets anywhere in the system (parent or child) that want to link to the "inviteGenerator" cloudpage using encryption. Assets could be an email, could be a cloudpage. So we will just set up a code block (type code snippet) that we can reuse anywhere. The code snippet creates the encrypted link and sends data to B. You can use it in emails or cloudpages alike.

B) A cloudpage (the "inviteGenerator") that creates the .ics event invite based on encrypted input data. This is a code resource of type text. It is in Parent BU. It is accesses through the link created in A, wherever that is placed.

C) A data extension with the metadata about your event. Let's assume it has the following fields:

  • campaignId (primary key, a unique ID for your event)
  • startTime (date, in servertime UTC-6)
  • endtime (date, in servertime UTC-6)
  • eventDescription
  • eventLink
  • eventLocation
  • invitationReplyAddress
  • invitationReplyCompanyName

these are all text fields, you can of course get more creative with what you want to show in the ics invite.

Note that the dates in this data extension are meant to be in Marketing Cloud system time (UTC-6). UTC-6 is also how dates come through MC Connect, in case you get the dates from Sales Cloud. The invitegenerator takes care of switching times to UTC, which is how an .ics file wants a datetime to be. It is in the same BU as A.

[ D) a location where you have your userdata - not gonna talk about this much]

===

general workflow: assets send encrypted input as URL parameters. Based on the encrypted base input it receives, the cloudpage will do a lookup for the event metadata (startdate, enddate etc) from DE C, and potentially, personal Data from D;

Reason, as per revised comment under the OP:

that way the data would be pulled at the time of click, which is the most up-to-date you can get this to be - in contrast: data sent in the Button URL as encrypted parameter is "frozen" from the time of email sendout and changes after sendout will not reflect. What's "better" here is more or less usecase dependent. You can of course just send more encrypted variables in the link instead of the lookup, if you think that suits your case better. In a connected SFSC / SFMC setup, what would be EVEN FASTER is to retrieve the data directly from an SFSC object on the cloudpage (RetrieveSalesForceObjects() function). But that assumes you have an object for this, and if, I am generally a fan of retrieving data this way as little as possible. I'd likely just deal with a 15 minute delay from MC Connect data stream instead and lookup from (synchronized) data extensions.

===

some more background reasoning and preparations on the encryption:

I want the inviteGenerator cloudpage in the parent BU so I have it in the central admin layer.

But: I want emails or pages anywhere in the system (parent BU, all child BUs) to be able to call it.

So I am not going to use CloudPagesURL for encryption. That is totally feasible and an easy shortcut, but would require the inviteGenerator to be in the same BU as each asset - that means I might have to duplicate the inviteGenerator code and store it in two BUs at some point. And no one likes managing copies of the same thing.

So instead of CloudpagesURL() I am using encryptSymmetric in all assets linking to the inviteGenerator, and a matching decryptSymmetric() function in the inviteGenerator itself.

Here is how this is done across BU borders: AES Encryption in Ampscript

Ensure that the same exact keys with the same customerKeys are in the location of the asset and the service, in case asset and inviteGenerator are in two different BUs.

Note that this is not meant for bulk sendouts in the millions, where you would want to hardcode the encryption params in both A) and B) instead of looking them up by their external keys for better performance. In a one-to-one journey it's totally fine.

===

DE preparation:

Set you a DE "myEvents" which holds the dates and all relevant metadata for your event. Place it in the Parent BU. It will be accessed from within the Parent BU (Cloudpage B), so whether it's in a shared or non-shared folder doesn't matter.

See above for rough field schema.

==

Pt. 1 of 2: Code block of type code snippet

create an encrypted link sending customer specific data to the "inviteGenerator" at https://mcxxx-731pw3vrp4.pub.sfmc-content.com/xxxxxxxxxx

/* switch to test / live */ 
SET @test = "true"

SET @testCalendarInviteGenerator = "https://mcxxx-731pw3vrp4.pub.sfmc-content.com/xxxxxxxxxx"
SET @liveCalendarInviteGenerator = ""

SET @pw = "pwExternalKey"
SET @salt = "saltExternalKey"
SET @IV = "ivExternalKey"

/* static test data, see comment for the "real" value */

SET @subscriberKey = "003xxxxxxxx" /* _subscriberKey */
SET @campaignId = "123" /* AttributeValue("campaignId")*/

/* end test data */

/* data extension containing the date and metadata for the event */
SET @dataExtension = "myEvents"


IF @test == true THEN
 SET @handler = @testCalendarInviteGenerator
ELSE 
 SET @handler = @liveCalendarInviteGenerator
ENDIF
SET @encryptedSubscriberKey = URLEncode(encryptSymmetric(@subscriberKey, 'AES', @pw, @null, @salt, @null, @IV, @null),1,1)
SET @encryptedCampaignId = URLEncode(encryptSymmetric(@campaignId, 'AES', @pw, @null, @salt, @null, @IV, @null),1,1)
SET @encryptedDataExtension = URLEncode(encryptSymmetric(@dataExtension, 'AES', @pw, @null, @salt, @null, @IV, @null),1,1)

SET @calendarUrl = concat(@handler,"?cid=",@encryptedCampaignId,"&sk=",@encryptedSubscriberKey,"&de=",@encryptedDataExtension)
/*%%=RedirectTo(@calendarUrl)=%% */
]%%</span>%%[/*calendar Link encryption TEST*/]%%

Put this into an email or cloudpage. Below it, you can just use %%=RedirectTo(@calendarUrl)=%% inside a href in emails or %%=v(@calendarUrl)=%% inside a href in cloudpages and you have your encrypted link.

That link in @calendarUrl sends the inviteGenerator three encrypted things.

  • The subscriberKey which denotes your recipient
  • The CampaignId which denotes your event
  • The data extension name, which tells it where to lookup the event information, including dates (based on CampaignId)

part 2 of 2 - Cloudpage of type code resource/txt:

  • Go to Parent BU:
  • Create a Cloudpage, Type Code Resource / Text; Copy its URL. go back to code sample 1 and replace the dummy value of @testCalendarInviteGenerator variable with the actual URL.

This becomes your inviteGenerator. It will take encrypted data from the link created above, decrypt it and create an .ics event from the information that is downloaded upon click on your link:

%%[ /* Cloudpage (Code Resource TXT) */
SET @pw = "pwExternalKey"
SET @salt = "saltExternalKey"
SET @IV = "ivExternalKey"

SET @campaignId = RequestParameter("cid")
SET @subscriberKey = RequestParameter("sk")
SET @dataExtension = RequestParameter("de")

SET @campaignId = decryptSymmetric(@campaignId, 'AES', @pw, @null, @salt, @null, @IV, @null)
SET @subscriberKey = decryptSymmetric(@subscriberKey, 'AES', @pw, @null, @salt, @null, @IV, @null)
SET @dataExtension = decryptSymmetric(@dataExtension, 'AES', @pw, @null, @salt, @null, @IV, @null)

IF substring(@dataExtension,1,4) == "ENT." THEN
 SET @dataExtension = substring(@dataExtension,5)
ENDIF

SET @eventName = Lookup(@dataExtension,"Name","Id",@campaignId)
SET @startTimeServertime = Lookup(@dataExtension,"StartTime","Id",@campaignId)
SET @endTimeServertime = Lookup(@dataExtension,"EndTime","Id",@campaignId)
SET @utcOffsetHours = 6

If not empty(@startTimeServertime) THEN
 SET @startTime = DateAdd(@startTimeServertime,@utcOffsetHours,"H")
ENDIF
IF not empty(@endTimeServertime) THEN
 SET @endTime = DateAdd(@endTimeServertime,@utcOffsetHours,"H")
ELSE 
/* fallback */
 SET @endtime = DateAdd(@startTime,1,"H")
ENDIF

SET @eventDescription = Lookup(@dataExtension,"EventDescription","Id",@campaignId)
SET @eventLink = Lookup(@dataExtension,"EventLink","Id",@campaignId)
SET @eventLocation = Lookup(@dataExtension,"EventLocation","Id",@campaignId)
SET @invitationReplyAddress = Lookup(@dataExtension,"InvitationReplyAddress","Id",@campaignId) 
SET @invitationReplyCompanyName = Lookup(@dataExtension,"InvitationReplyCompanyName","Id",@campaignId)


/*
SET @firstname = Lookup some DE by @subscriberKey
SET @lastname = Lookup some DE by @subscriberKey
*/

IF empty(@firstname) THEN
 SET @firstname = "valued"
ENDIF
IF empty(@lastname) THEN
 SET @lastname = "customer"
ENDIF

IF EMPTY(@campaignId) 
or EMPTY(@subscriberKey) 
or EMPTY(@dataExtension) 
THEN
 ]%%
 Sorry, this page cannot be accessed directly.
 %%[
ELSE
 SET @startDay = datePart(@startTime, "D")
 SET @startMonth = datePart(@startTime, "M")
 SET @startYear = datePart(@startTime, "Y")
 SET @startHour = FormatDate(@startTime,"","hh")
 SET @startMinute = FormatDate(@startTime,"","mm")
 SET @startDate = Concat(@startYear,@startMonth,@startDay,'T',@startHour,@startMinute,'00Z')
 
 SET @endDay = datePart(@endTime, "D")
 SET @endMonth = datePart(@endTime, "M")
 SET @endYear = datePart(@endTime, "Y")
 SET @endHour = FormatDate(@endTime,"","hh")
 SET @endMinute = FormatDate(@endTime,"","mm")
 SET @endDate = Concat(@endYear,@endMonth,@endDay,'T',@endHour,@endMinute,'00Z')
 SET @formattedNow = FormatDate(Now(),"yyyyMMddThhmmssZ")
]%%<script runat="server">
Platform.Load('Core', '1');
HTTPHeader.SetValue('Content-Type', 'text/calendar; method=REQUEST; charset=utf-8');
HTTPHeader.SetValue('Content-Disposition', 'attachment; filename=cal.ics');
HTTPHeader.SetValue('Content-Transfer-Encoding', 'base64');
try {
 </script>BEGIN:VCALENDAR
VERSION:2.0
PRODID:-//%%=v(@invitationReplyCompanyName)=%%//NONSGML v1.0//EN
CALSCALE:GREGORIAN
METHOD:PUBLISH
BEGIN:VEVENT
CATEGORIES: APPOINTMENT
DTSTAMP:%%=v(@formattedNow)=%%
UID:%%=v(@subscriberKey)=%%%%=v(@campaignId)=%%%%=v(@formattedNow)=%%%%=v(@invitationReplyCompanyName)=%%
SUMMARY:%%=v(@eventName)=%%
DESCRIPTION:%%=v(@eventDescription)=%%
DTSTART:%%=v(@startDate)=%%
DTEND:%%=v(@endDate)=%%
SEQUENCE:1
ORGANIZER;CN=%%=v(@invitationReplyCompanyName)=%%:MAILTO:%%=v(@invitationReplyAddress)=%%
ATTENDEE;CN=%%=v(@firstName)=%% %%=v(@lastName)=%%;RSVP=TRUE:MAILTO:%%=v(@email)=%%
LOCATION:%%=v(@eventLocation)=%%
END:VEVENT
END:VCALENDAR<script runat="server">
} catch (e) {
 Write(Stringify(e));
}</script>%%[ ENDIF ]%%

note that this solution doesnt cover how to get the firstname and lastname variables - it'll always be "valued customer", see comment in the code for part 2. That would just need two simple lookups into Data extension "D" to be improved.

4
  • Should the location be Latitude and Longitude? or will it also work with google map link?
    – Ben Inm
    Feb 3 at 5:45
  • No idea, all my events so far were just plaintext...at the location "online" :D - try it out, it will generate ics events and you can just check what outlook recognizes and how it makes the recognized things (addresses, lat/long) look and feel. Outlook behavior is what matters in the end. Please mark the question as accepted if it solved your problem - it's better to open a new, specific question if something else comes up, more people will read it than our little chat here :) Feb 3 at 7:44
  • Thank you very much for your information. It helps me a lot :)
    – Ben Inm
    Feb 3 at 7:58
  • Sweet - the way "marking as accepted" works is to click the check mark symbol next to the answer and it's marked as solved. Feb 3 at 8:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.