Overview

You have a campaign that is no longer active, and its uncompleted items are piling up in the queue. You would like to set this campaign as inactive and delete these uncompleted events, but keep the events that are completed.

Solution

Please contact our Support Team and include the following information:

• Approval to access your instance (and instructions of how to access, if needed)
• The name of the campaign that you wish to set as 'Inactive'
• The cutoff date on which all prior campaigns should be deactivated, if applicable (e.g. all 'CD Calls' campaigns prior to 2021-09-25). 

A support agent will access your production environment and take the necessary actions. Once the request is completed, you will receive a confirmation from the agent.

<supportagent>

Agents only:

You must access the customer's production environment, and then perform the following steps:

1. Run the SQL query to find the campaigns and events:

   select * from Campaign where Campaign_Name like '%campaign_name%'
   
   select  * from Campaign_Event_Template where Campaign_ID in
   (select Campaign_ID from Campaign where Campaign_Name like '%campaign_name%')

    Copy the campaign_id.
2. Run the following queries to find, group, backup, and delete events:
   

   -- Count and group Events
   select 
   count (1) QTY, C.Campaign_Name 
   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 like '%campaign_name%')
   and IsCompleted = 'N' and End_Date < 'cutoff date in yyyy-mm-dd 00:00:00 format'
   group by C.Campaign_Name
   
   -- Backup Events
   select 
   * into Events_BK <backup_date_yyyymmdd>
   from Events E
   where E.Campaign_ID in
   (select Campaign_ID from Campaign where Campaign_Name like '%campaign_name%')
   and IsCompleted = 'N' and End_Date < 'cutoff date in yyyy-mm-dd 00:00:00 format'
   
   -- Delete Events
   delete
   from Events 
   where Campaign_ID in
   (select Campaign_ID from Campaign where Campaign_Name like '%campaign_name%')
   and IsCompleted = 'N' and End_Date < 'cutoff date in yyyy-mm-dd 00:00:00 format'

3. Run the following queries to backup and deactivate the campaigns:
   

   -- Backup Campaigns
   select * 
   into Campaign_BK<backup_date_yyyymmdd>
   from Campaign where Campaign_ID in (
   select Campaign_ID from Campaign_Event_Template where Template_End_Date <'cutoff date in yyyy-mm-dd format')
    and Campaign_Name like '%campaign_name%'
   
   -- Set campaigns Inactive
   Update Campaign
   set Active = 0
   where Campaign_ID in (
   select Campaign_ID from Campaign_Event_Template where Template_End_Date <'cutoff date in yyyy-mm-dd format')
    and Campaign_Name like '%campaign_name%'
   
   -- count modified Campaigns
   select Campaign_Name from Campaign
   where Campaign_ID in(
   select Campaign_ID from Campaign_BK20210415 where Active =1)
   
   -- Fix null status
   select * from Campaign where Active is null
   
    Update Campaign
   set Active = 0
    where Active is null

    

4. Run the following queries to check for other uncompleted events to inform the customer:
   

   -- Check others
   select
   count (1) QTY, C.Campaign_Name 
   from Events E
   join Campaign C on C.Campaign_ID = E.Campaign_ID
   and IsCompleted = 'N' and End_Date < 'cutoff date in yyyy-mm-dd 00:00:00 format'
   group by C.Campaign_Name
   
   select 
   count (1) QTY, Campaign_Name 
   from Campaign where Campaign_ID in (
   select Campaign_ID from Campaign_Event_Template where Template_End_Date <'cutoff date in yyyy-mm-dd 00:00:00 format')
   and active = 1
   group by Campaign_Name

Once you have finished, inform the customer of which campaigns have been deactivated and the count of uncompleted events that were deleted.

</supportagent>