Overview

You want to delete all the campaigns that contain a specific name and their events

Information

There are two ways of deleting campaigns: using the ResourceOne UI or only via the SSMS.

Using the ResourceOne UI

1. Go to Marketing > Campaign

   
   
   

2. From the campaign list, open the desired campaign by clicking the following button:

   
   
   
   The CAMPAIGN DATA screen will open:
   
   
   
   

3. Click the ellipsis on the upper left side of the screen, and click on the Delete (Including Distributed Items) option.
   
   
   
   
   
   You will get a DELETE CONFIRMATION dialog box stating that Campaign and all pipelines and events linked to the Campaign are going to be deleted.
   
   
   
   
4. Click on the Delete button.

   
   
   Once you delete the Campaign and its events, you will get redirected to the campaign screen, and you will be able to confirm that the campaign disappeared from there.

Via SQL Server Management Studio (SSMS) 

Deleting information from the backend involves database modification scripts that might damage your database and, in consequence, your data and the product.

If you want to proceed with the queries mentioned in the following section, please make sure you back up your entire database, and more precisely, the dbo.Campaign and dbo.Events tables, which can be found in the BANK DB, because once you execute the queries listed below, the data will be lost and will be unrecoverable. You can use the method described in the Backing Up Tables for Security KB Article for this purpose.

Input information

To get these records eliminated from the database, you must first reunite the following information:

• The name of the Campaign(s) where all these records exist. (it will be called <NOTC>)

Query to execute

First, let's check that the record exists in this Campaign table with that name:

Select * 
from Campaign 
where Campaign_Name = '<NOTC>';

For this example, it would be:

Select *
from Campaign
where Campaign_Name = 'Loans > 50k';

It should fetch the Campaign name if it exists on the Campaign table.


Now, we will link this table with the Events table to get the number of records that will be affected during the deletion process. To do that, we execute this query:

Select
E.campaign_ID, 
C.Campaign_Name, 
E.IsCompleted
from Events E
join Campaign C 
on C.Campaign_ID = E.Campaign_ID
where E.Campaign_ID in 
(
Select Campaign_ID from Campaign
where Campaign_Name = '<NOTC>'
)

In our example, it would be:

Select
E.campaign_ID, 
C.Campaign_Name, 
E.IsCompleted
from Events E
join Campaign C 
on C.Campaign_ID = E.Campaign_ID
where E.Campaign_ID in 
(
Select Campaign_ID from Campaign
where Campaign_Name = 'Loans > 50k'
)

That is enough to get ALL records that will be deleted. It is a good practice to count the records to be deleted so you can provide a report to the customer once you execute the process.

If you are able to confirm that this is the same list of records you want to get rid of, then you can apply this query to delete permanently the records:

Delete
from Events
where Campaign_ID in 
(
Select Campaign_ID 
from Campaign 
where Campaign_Name like '<NOTC>'
and IsCompleted = 'n'
)

In our example, it would be:

Delete
from Events
where Campaign_ID in
(
Select Campaign_ID
from Campaign
where Campaign_Name like 'Loans > 50k'
and IsCompleted = 'n'
)

Finally, you can delete the campaign if desired using the following query:

Delete
from Campaign 
where Campaign_Name like '<NOTC>'

In our example, it would be:

Delete
from Campaign
where Campaign_Name like 'Loans > 50k'

Adapt this query based on the explanation previously given to your needs, to delete the list of records you want to delete. Once you confirm the events have been deleted, inform the customer listing all the campaigns and the count of the deleted events (you can confirm that the query deleted the campaign you wanted to delete by checking the GUI, as mentioned in the Using the ResourceOne UI section).

Contacting Support

If you want support to execute this backend modification on your behalf, please contact the Support staff and provide them with the information mentioned in the Input information section of this guide. A customer support representative will contact you back with the result of the execution of the aforementioned query.