I am trying to create an entry event for Contacts when the transaction date has been > 120 days or = equal to 120 days in the past.

Contacts are synced in a synchronized DE The transaction date is stored in TransactionHeader as synchronized DE from SF. Contact.Id is subscriber key and Contact.Id is related to TransactionHeader.BilltoContact.

Transaction date is stored in the following format: 06 March 2018 13:00

I have create a data based event for this:

Data Extension Name: TransactionHeader Contact Attributes: TransactionHeader.TransactionDate Re-entry: Yearly Date: 120 days after TransactionHeader.TransactionDate Time: 12:00 AM Time Zone (GMT) Greenwich Mean Time : Dublin, Edinburgh, Lisbon, London

Contacts are not entering the journey and I ran out of options to think of. Is this even possible to achieve or should I create a daily query as entry event?

  • I would recommend creating a query automation to trigger the journey. You mention the format of the data field above? The Transaction Date field needs to have a "date" datatype in order to correctly query or filter the records. – David Devoy Jul 5 '18 at 14:10
  • Thanks for you comment David. I have gathered the following functions. Would this work? SELECT Contact.Email, Contact.AccountId, Contact.Id, Contact.FirstName, Contact.LastName, Contact.OwnerId, Contact.ContactID__c, Contact.Salutation FROM ENT.Contact INNER JOIN ENT.TransactionHeader ON TransactionHeader.AccountId=Contact.AccountId WHERE TransactionHeader.TransactionDate__c = CURDATE() - INTERVAL 120 DAY – user1122843 Jul 5 '18 at 15:00
  • Close, but I don't think "CURDATE() - INTERVAL 120 DAY" is proper T-SQL syntax. – David Devoy Jul 5 '18 at 15:08
  • Also, your query will return multiple rows for the same contact if they have more than one transaction. Try using the 'distinct' in your select statement. – David Devoy Jul 5 '18 at 15:10

Your Answer

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

Browse other questions tagged or ask your own question.