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.