I am attempting to automate our payment failures to alert consumers their subscription payment hasn't processed. My initial thought was to run a SQL activity once a day and inject new records into a payment journey that has a wait step at the end allowing them to reenter after a certain period of time. My question is, will the "Update" SQL activity removed records from the data extension when the requirement isn't satisfied?
For example, run query on 5/1 and payment failed. Run again on 5/2 and the payment has been resolved, will that user still be in the original table or removed? Here is the query i am working from:
SELECT pay.payment_status
, pay.lead_id
, pay.[Subscription Number]
, pay.draft_date
, pay.stop_notification
, pay.Email
, pay.Phone
, pay.[Phone Formatted]
, account.Status
FROM "Payment History" pay
LEFT JOIN "Subscription Data" account
ON account.[Subscription Number] = pay.[Subscription Number]
WHERE
pay.payment_status <> ''
AND pay.payment_status <> ' '
AND NOT pay.payment_status IS NULL
AND LEN(pay.payment_status) > 0
AND NOT pay.payment_status LIKE '%Approved%'
AND account.Status in ('Active', 'ACTIVE')
My current work around is to overwrite the table every run, but, that isnt the ideal way to go.