0

I need to create a daily automation to query contacts who's status in All subscribers changed to Unsubscribed last 24 hours. I am totally new to query, but I tried this..

SELECT 
EmailAddress,
SubscriberKey,
Status,
EventDate as 'UnsubscribeDate',
FROM
_ListSubscribers 
WHERE
ListID = 305
AND
Status = 'Unsubscribed'
AND
EventDate > dateadd(hh,-24,getdate())

I received 'Errors: Incorrect syntax near the keyword 'FROM'.' message. Could someone please guide me through what is wrong? Thank you!

1

First run the below without filter DateUnsubscribed > dateadd(hh,-24,getdate())

SELECT 
EmailAddress,
SubscriberKey,
Status,
DateUnsubscribed as 'UnsubscribeDate'
FROM
_ListSubscribers 
WHERE
ListID = 305
AND
Status = 'Unsubscribed'

Once it returns the result then try running below

SELECT 
EmailAddress,
SubscriberKey,
Status,
DateUnsubscribed as 'UnsubscribeDate'
FROM
_ListSubscribers 
WHERE
ListID = 305
AND
Status = 'Unsubscribed'
AND
DateUnsubscribed > dateadd(hh,-24,getdate())
1
  • Happy to help!!
    – Chandan
    Mar 8 '18 at 10:34
0

Errors: Incorrect syntax near the keyword 'FROM'.'

take out the , before from (i.e. after 'UnsubscribeDate')

SELECT 
EmailAddress,
SubscriberKey,
Status,
EventDate as 'UnsubscribeDate'
FROM
_ListSubscribers 
WHERE
ListID = 305
AND
Status = 'Unsubscribed'
AND
EventDate > dateadd(hh,-24,getdate())
6
  • Thank you @Data_Kid, now I have another error message...An error occurred while checking the query syntax. Errors: Invalid column name 'EventDate'.
    – HRJ
    Mar 8 '18 at 1:28
  • Are data views enabled in your account?
    – Chandan
    Mar 8 '18 at 4:34
  • Seems like you may need to use DateUnsubscribed instead of EventDate.
    – Chandan
    Mar 8 '18 at 4:37
  • I've tried like this and it ran successfully, but returned zero records. SELECT l.EmailAddress, l.SubscriberKey, l.Status, u.EventDate as 'UnsubscribeDate' FROM [_ListSubscribers] l JOIN [_Unsubscribe] u on l.SubscriberKey = u.SubscriberKey WHERE l.ListID = 305 AND l.Status = 'Unsubscribed' AND u.EventDate > dateadd(hh,-24,getdate())
    – HRJ
    Mar 8 '18 at 5:05
  • @Chandan, do we need to enable data view?
    – HRJ
    Mar 8 '18 at 5:06

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.