AC Standard - delete profiles that are not updated or inserted

[#1]

Hi everybody,

I need some suggestions on how to solve this problem.

I have a workflow that imports a csv file containing the updated database (only users who have approved to be contacted) every week. In this workflow I set 3 activities:

Transfer file (download the file csv from SFTP) -> Load file -> Update data ( operation type: insert or update)

The problem is: every time the database is updated (indicatively once a week), some contacts that were uploaded in previous imports may no longer be present in the new csv (users who have changed the approval to be contacted). How can I delete users who are no longer in the database without deleting and re-importing all users every week?

My idea is to create a new workflow with 2 activities:

- Query that selects the contacts that haven't last modification date or creation date equal to the date of last import

- Update data with operation type Delete

But I think this solution is not good because there may be users that don't change any field between an import and the other and it will not be modified neither "Last modified" or "Created"

Does anyone have any suggestions?

Thanks

Eveline

[#2 · Accepted solution]

Hi Florent,

unfortunately I can't have a list of user who changed their approval. We don't manage the database, we only receive a csv on SFTP.

The only things we have available are the new csv and previously imported profiles.

In these days we have thought of a new workflows:

1. WKF to import new file and save all the imported profiles in an audience

2. WKF to intersect the profiles present on AC with the imported audience

This process seems to work.

Thanks,

Eveline

[#3]

Thanks Eveline for sharing your solution!

[#4]

Hi Eveline,

Easiest thing would be to be able to get the list of users who changed their approval and to pass that information to Campaign as well. This way, you'd know exactly who to delete. (I am assuming here that you just don't keep users who opted-out in your primary database).

What you can do as well is in your workflow, force a field to be updated. For example, extend your profile resource with a new date field where you'll pass the date of the latest import (updated by your import workflow everyweek). Then, all users referenced in the CSV would have this field updated anyway and you'd be able to finetune your deletion strategy.

Hope that makes sense.

Florent