0

I have a synchronized data extension that SQL queries Lead data into another data extension that is being used in a Journey.

The Journey sends out an initial email based on the status in the SQL query. A few days later, the journey makes a decision split where it looks at the status (via Contact Data) and sends a second email if its different from the first one.

The synchronized extension updates the status when it changes, but I see that when the query runs, it doesn't not make that status update in the other data extension.

Example: Lead A has a status of "Example A" in the synched extension. The query looks for those with "Example A" as their status and adds them to the "New" Data Extension and sends an email. Several days later Lead A has a status of "Example B" in the synched extension. This status is never updated in the "New" data extension even though the SQL query has an UPDATE data action. Lead A therefore does not move through the Journey to get the second email.

In this case, is the query wrong? Should be the Add or Overwrite action instead? I'm pretty stumped on what's going on.

2 Answers 2

0
  • Your query has a WHERE clause with something like - WHERE Status = 'Example A' and an Update Data Action.
  • When the status is changed in the Synced DE to the value 'Example B', your query will not update the status, since it filtering out records by Status = 'Example A'

What options you have:

  1. Populate records that follow this rule - WHERE Status = 'Example A' into Data Extension #1 and then WHERE Status = 'Example B' into Data Extension #2. You would need to connect DE#2 to the Attribute Group and then do an Attribute to Attribute comparison in the Decision Split.
  2. Update your WHERE clause to filter out records by some other rules, so the Status field would be able to receive different values.
  3. Change data action to the Overwrite and remove WHERE Status = 'Example A' condition in the query but then filter out records within the journey and in the Decision Split, again, have an Attribute to Attribute comparison where the left input would be Contact Data and the right one - Journey Data. This way you would be able to track the changes of values within the Status field.

Lastly, take into consideration known issues of Attribute to Attribute Comparison:

-1

If you using "Add and Update", it will only update an existing row with the new data. However, if you are looking to remove an old record after running the SQL Query, you need to use "Overwrite".

7
  • That doesn't work. All it does is removes those who no longer have "Status A" from the "New" data extension and sends them the email again, even when leads already got one.
    – Joe M
    Jul 21, 2021 at 19:35
  • Can you share the query that's failing
    – Lijo Johns
    Jul 21, 2021 at 19:49
  • Changed the status/source names for obvious reasons but you get the idea: SELECT FirstName, Email, Status, LeadSource, Id FROM Lead_Salesforce WHERE Status = 'STATUS A' AND (LeadSource = 'SOURCE1' OR LeadSource = 'SOURCE2' OR LeadSource = 'SOURCE3' OR LeadSource = 'SOURCE4' OR LeadSource = 'SOURCE5')
    – Joe M
    Jul 21, 2021 at 19:52
  • Since your looking for the new status 'Example B' as mentioned above, the where clause should be WHERE Status = 'STATUS B'
    – Lijo Johns
    Jul 21, 2021 at 19:57
  • I need those with Status A to enter the data extension. I'm only looking for the new status in the journey builder's decision split, but the query never updates the status field so those who have a status change in Salesforce are never going further in the journey like they should. (Before this I used a Salesforce Data Entry in the Journey with Contact Data being used for the DS, but it had the same result.)
    – Joe M
    Jul 21, 2021 at 20:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .