Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

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

Avatar

Level 2

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.

 

1 Accepted Solution

Avatar

Correct answer by
Employee Advisor

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

View solution in original post

3 Replies

Avatar

Community Advisor

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

Avatar

Level 2

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.

 

sirikambampati_2-1653068440968.png

 

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

 

Avatar

Correct answer by
Employee Advisor

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