Campaign Flow

Campaign Flow

Work flow document of Campaign

Sachin Patil 9/01/2009

Campaign work flow

Overview This document covers the detail flow of Campaign assignment, Campaign updating process.

Campaign Assignment: Campaign will be assigned by following 2 ways: 1. Lead is created/updated: 2. Campaign is inserted/updated:

When a lead is created in VPC, suppose that lead is entered with status “A” then the campaigns which have status “A” will be assigned to that lead. Automated campaign emails will be sending according to createddate of a lead. If status is not exclusive the “All” status campaign are also assigned to that lead. A record of campaign assignment to leads is maintained in crm_lead_template table in each company’s DB.

6

Campaign work flow

Campaign is inserted: When a campaign is inserted into VPC, suppose campaign is created with status “A” then leads which are having status “A” will be assigned to that campaign. Automated emails will be sending according to createddate of campaign.

Campaign is updated: When a campaign is updated i.e. if status of a campaign is updated from status “A” to status “B” then leads which are having status “B” will be assigned newly to campaign. The leads with status “A” will be updated in crm_lead_template i.e. the records with leads having status “A” and assigned campaign will be deactivated. Automated emails will be sending according to updated date of a campaign.

6

Campaign work flow

When a lead is updated i.e. if a status of a lead is updated from status “A” to status “B” then campaigns of status “B” will be assigned to those lead, campaigns of status “A” will be deactivated. Automated emails will be sending according to createddate of a lead.

We changed logic here i.e. when a lead is updated then we need to send automated campaign emails not from createddate, we need to assign campaigns from the date when a lead’s status will be changed.

6

Campaign work flow For example if campaign “Campaign A” has status “A”, “Campaign B” has status “B” and have templates scheduled as day1, day2 and day3 respectively. If “lead a” has inserted with status “A” and gets 3 templates. When “lead a” has changed to status “B” then it should receive day1 template of “Campaign B” on the next day after status assignment.

1. When a lead status change occurs, records are added to the crm_lead_template table a. Explain the following i. Why is there always an insert functionality and not update functionality, this bloats this table Leads associated to the existing Status are tracked through this table but if the status is changed there are some leads which are associated to the new status and which are not in crm_lead_template, so all earlier records are deactivated and to add all leads (associated with old status and associated with new status) we have written insert functionality instead of update. ii. Why is createdate NULL, this date always needs to have a value since it’s the baseline date If Createddate field is containing null value then campaign sending is base on the lead created date. When lead is updated then createddate field is set with the lead update date value. iii. What is the purpose of CampaignUID and why it is NULL when adding records, does that mean this is a system campaign or custom campaign To manage the relation with crm_campaign and crm_lead_template table we are having CampaignUID field. iv. Why is tempid NULL shouldn’t this field have the template ids associated Records for tempid having null values are old one new record may not have this issue. We can remove this field and make use of templateid. 2. Days to send campaign is the trigger when to send the campaign based on the created date of the crm_lead_template a. Explain the following

6

Campaign work flow i. In crm_camp_temp what does day_add = 82 or day_add = 83 mean Day_add = 83 is used for the fixed date campaigns (i.e. Valentine Day) Day_add = 82 is used for the birth date campaigns. ii. What is fixed date mean day = 83 1. Is this use only for system campaigns Day = 83 is set to know this is fixed date campaign; it is used for both System and Custom type campaigns. Such as Valentine Date campaigns. iii. What does birthday mean (in this context) day = 82 1. Is this use only for system campaigns 3. Day = 82 is set to know this is birth date campaigns. It is used for both System and Custom type campaigns. i. The following SQL is use to determine templates for fixed date or birthdates. Select TEMPID,DATE_SET,DAY_ADD from crm_camp_temp where (DAY_ADD=82 or DAY_ADD=83) and campid=" + campid + " and tempid in (select tempid from crm_template with (nolock) where active=1)

1. Are we only limiting these types of campaigns to be sent, are there other types of campaigns if so what are they There are two types of campaigns in the VPC, one is System Campaigns and other is Custom Campaigns. In both the types there are again tow types such as fixed date campaigns and birth date campaigns. DAY_ADD=83 is used for fetching all the fixed date campaigns in the VPC and DAY_ADD=82 is used for fetching all the birth date campaigns.

b. Specifically, please re-explain CheckTemplateValidity method i. Is this where you determine when the e-mail template for that campaign needs to go out CheckTemplateValidity (): a) If template is scheduled for fixed date i.e. 83 then check the month and date of date_set and today's date and month, if these matches then send that template to leads which we have already within dataset. DateTime dt_date_set = Convert.ToDateTime (date_set); if (DateTime.Now.Day == dt_date_set.Day && DateTime.Now.Month == dt_date_set.Month) { SendEmails (dsCampLeads.Tables[0].DefaultView, campid, tempid, companyid, ConnectionStr); return true; } b) If template is scheduled for birthday i.e. 82 then check the leads having birthday today i.e. month and date of today's date.

6

Campaign work flow DataView dvLeads = new DataView (dsCampLeads.Tables[0]); String month = string.Empty; String day = string.Empty; if (DateTime.Now.Month.ToString ().Length == 1) month = "0" + DateTime.Now.Month.ToString (); else month = DateTime.Now.Month.ToString (); if (DateTime.Now.Day.ToString().Length == 1) day = "0" + DateTime.Now.Day.ToString(); else day = DateTime.Now.Day.ToString (); string todaydate = month + day; try { dvLeads.RowFilter = "substring (birthdaydate, 5, 4) = '" + todaydate + "'"; SendEmails (dvLeads.ToTable().DefaultView, campid, tempid, companyid, ConnectionStr); } c) Check each record of column createddate in crm_lead_template table, if createddate is null then consider createddate of a lead. Calculate number of days i.e. createddate in crm_lead_template minus today's date. Check for schedule days for the templates assigned to particular campaign; get that template information from crm_camp_temp. If it matches then send that template to lead. if (forall != 1) { strtempid = DatabaseConnection.SqlScalar ("select tempid from crm_camp_temp where active =1 and campid=" + campid + " and day_add=" + id1 + "", ConnectionStr); } if (strtempid.Trim() != "") { DataView dvLeads = new DataView (dsCampLeads.Tables [0]); String todaydate = DateTime.Now.Year.ToString () + DateTime.Now.Month.ToString () + DateTime.Now.Day.ToString (); dvLeads.RowFilter = "leadid=" + lead_id; SendEmails (dvLeads.ToTable ().DefaultView, campid, Convert.ToInt32 (strtempid), companyid, ConnectionStr); } 4. The e-mail scheduler executable is run and loaded to CRM_QUEUE table a. Is this a correct statement Yes, this is correct statement.

6

Campaign work flow Scheduler loads all the records into the crm_queue table and base on the lead details it sends e-mails.

6