0

I'm attempting to query when a value changed in a particular data e.g. when the email address for subscriber 12345 was changed, if ever. Is there a way to query this information?

5

One possible solution would be to write a query to copy everything from your data extension into a new data extension. You could then check the new data extension against the original to see if any values have changed.

This could all be run inside of an automation to run at an interval of your choosing. The first query in the automation will check to see if the copy data extension has any differences on the "emailaddress" field from the master/original data extension. The second query will copy the "master" data extension records to the new data extension (as overwrite) so that it can be compared against the master on the next run of the automation.

Step 1.1:

SELECT a.subscriberkey,
a.emailaddress,
a.otherfield1,
a.otherfield2
FROM Copy_Of_Master a
WHERE NOT EXISTS
(SELECT b.emailaddress
FROM Master_DE b
WHERE a.emailaddress = b.emailaddress)

The above example will pull any records from the "copy" data extension that no longer have an email match on the Master data extension, effectively giving you records that have had a change made to their emailaddress field. You would likely want to have this target a third data extension so that you can review the results.

The second query would then copy everything as it currently exists in your main data extension over to the copy.

Step 2.1

SELECT subscriberkey,
emailaddress,
otherfield1,
otherfield2
FROM Master_DE

As noted above, this can be run at any interval you'd like.

| improve this answer | |
  • Thank you! I think this should work. Also I have one question. In the Query language, I'm wondering what the 'a.' is before each field and it's purpose? I'm pretty new with SQL, so forgive me if that's a novice question. – chorlesbarkley7 Jan 28 at 14:15
  • 1
    a is an alias for the table Copy_Of_Master. (FROM Copy_Of_Master a - sets the alias) The a. before each field is an identifier to show which table those fields belong to. So basically if you did not set the alias, you would need to put Copy_Of_Master.emailaddress etc. for each field. This is to remove any conflicts where a field exists in multiple tables and it is not declared which one is the one to be pulled. – Gortonington Jan 28 at 14:20

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.