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?)

1 Answer 1


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.

  • Interesting!! If you could elaborate, it really helps. We do use cloudpage but I am not sure how to use ampscript. Regarding SQL method, where should we write SQL? I mean what could be the triggering point?
    – Amulya
    Mar 14, 2017 at 13:29
  • sorry for the slow reply! I'm revising my reply. May 3, 2017 at 14:10
  • Hi Amulya - did my reply solve your question, or can i do more to help? :) Please mark answer as "best" if it resolved it :) Jun 15, 2017 at 7:58
  • Our Business team found some other way to handle this requirement so I din't implement anything. But your approach should work so marking as best answer.
    – Amulya
    Jun 20, 2017 at 13:55

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.