How to load associated deliveries for any given campaign in a Query activity

[sirikambampati]

Hi,

I am using a query activity in a Targeting workflow to return all the campaigns which are 3 year and older.

I have to delete them but before that I have to delete the associated deliveries.

Like in SQL, I have to use inner join and get all the associated deliveries for any given Campaign,.

I should first delete the deliveries and then delete the campaigns.

I am not able to load the deliveries associated along with campaign in the query activity.

How can we do that in Campaign classic using the query activity.

TIA.

[CedricRey]

Hi @sirikambampati 

You can try to use the Change Axis activity. Query all the campaign or operation, the place a "change axis" activity, select the orange (if you have V7 instance) link named "Deliveries" in the targeting Document, and be sure to select the second option "keep all records".

This way you shoud get all deliveries of all the campaigns / operations you got.

Cedric

[sirikambampati]

Hi @CedricRey 

Thanks for the response.

Here is what I have to achieve. I have to delete a list of Campaigns, and also deliveries which are composed in that campaigns.

I am fetching all the campaigns in a query activity. First I have to delete the deliveries, I am using a Update DB activity and linking the operation Id from the result set of query activity to the operation id in deliveries schema. Which should delete all the deliveries with that particular operation id. 

After that I am using another Update DB activity and targeting Campaigns schema and linking the Id's.

But its not working, what is the problem with this.

I have to delete all the deliveries associated to a campaign first and then delete the campaigns too.

So which activities I should be using?

I have also tried using change dimension, but it didn't work.

Thanks,

~SK

[Denis_Bozonnet · Accepted solution]

Hi @sirikambampati 

I would query Delivery and using the Link to Campaign select all the deliveries belonging to Campaign older than 3 years old....

BUT YOU CAN'T USE the Delete Action for Deleting deliveries as none of the related entities would be deleted. You MUST put these deliveries on the right deleted status (@deleteStatus set to 2) with a deleted status date (@deletedStatusDate set to current Date). Then these deliveries will be picked up by the normal clean up.

2 considerations to take:

1. Deliveries in the deleted state are kept for 7 days prior being processed
2. Standard clean up is treating 1,000 deliveries per day, so be also very careful of how much deliveries you put in the Delete state

Hope this helps,

Thanks

Denis