We are enabling 'Double Opt-in' feature in our Marketing Cloud application and wanted to capture 'Date/time' each subscriber clicks on the confirmation Email in a static field in SFMC. Is it possible to implement this feature completely in Marketing Cloud? Can we use Journey Builder to do that? Suggestions please. (Without using API's?)
There's two ways to achieve this:
- If the link leads to a cloudpage, you can use ampscript to stamp the current date/time
- Alternatively, you need to use SQL to update the value
Solving it by ampscript I'm assuming that the subscriber hits a confirmation page. here you could have a script as follows:
%%[ VAR @SubscriberKey, @ConfirmationTime, @SubscriberDE, @UpdateResult SET @SubscriberKey = _subscriberKey SET @ConfirmationTime = Now() SET @SubscriberDE = "DataExensionName" SET @UpdateResult = UpsertData(@SubscriberDE,1,"SubscriberKey",@SubscriberKey,"OptInTime",@ConfirmationTime) ]%%
The exact implementation of this, however, depends on your data model. For instance, if you have a static field AND you need to capture an opt in history as well, you need two data extensions.
For the SQL solution, it would be something like this:
SELECT SubscriberKey EventDate AS OptInDate /* Change name to reflect your field name */ FROM _Click WHERE LinkName = "OptInConfirmation" /* Change to reflect your link name */ AND EventDate > DATEADD(hour, -25, GetDate())
This can be run in automation studio - and let it run once a day, or whatever frequency you choose.
Personally, i would go for the ampscript / cloudpage solution.