Start Processing : 2024-09-20 02:26:53<BR>0 rows affectedupdate db_heloc_accounts
set _createdTS = STR_TO_DATE(_createdDate, '%c/%e/%Y')<BR>[1] 0.01 seconds -> -1 rows affected<BR>TRUNCATE db_heloc_waterfall<BR>[5] 0.19 seconds -> 0 rows affected<BR>insert ignore into db_heloc_waterfall (_accountName, _accountID, _industry, _type, _parentAccount, _parentAccountID, _accountOwner, _accountTier, _numberOfOpenOpps, _doNotCall, _website, _loanOriginationSystem)
select _accountName, _accountID, _industry, _type, _parentAccount, _parentAccountID, _accountOwner, _accountTier, _numberOfOpenOpps, _doNotCallListDnc, _website, _loanOriginationSystem from db_heloc_accounts;<BR>[10] 0 seconds -> -1 rows affected<BR>update db_heloc_waterfall
set _website = REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(_website, '/', 3), '://', -1), '/', 1), '?', 1), 'www.', '')
where length(_website) > 0<BR>[15] 0 seconds -> 0 rows affected<BR>UPDATE db_heloc_waterfall
SET _activeCustomer =
case when _type = 'Active Customer' then 1
else 0
END;<BR>[20] 0 seconds -> 0 rows affected<BR>UPDATE db_heloc_waterfall
SET _activeOpptys =
case when _numberOfOpenOpps > 0 && _type not in ('Active Customer', 'Partner', 'Potential Partner') then 1
else 0
END;<BR>[30] 0 seconds -> 0 rows affected<BR>UPDATE db_heloc_waterfall
SET _targetedProspects =
case when _numberOfOpenOpps = 0 && _type not in ('Active Customer', 'Partner', 'Potential Partner') then 1
else 0
end;<BR>[40] 0 seconds -> 0 rows affected<BR>update db_heloc_waterfall a 
inner join (select _account_ID, count(*) as _csRespondersYTD from abm_responders 
where _routed != '' && _posted = 1 && _utm_campaign LIKE '%HELOC%' && _utm_source not in ('email', 'linkedin') &&  YEAR(_date) = year(curdate()) 
group by 1) b 
on a._accountID = b._account_ID 
set a._csRespondersYTD = b._csRespondersYTD;<BR>[50] 0.01 seconds -> 0 rows affected<BR>update db_heloc_waterfall a 
inner join (select _company, count(*) as _csRespondersYTD from abm_responders 
where _routed != '' && _posted = 1 && _utm_campaign LIKE '%HELOC%' && _utm_source not in ('email', 'linkedin') &&  YEAR(_date) = year(curdate()) 
group by 1) b 
on a._accountName = b._company
set a._csRespondersYTD = b._csRespondersYTD
where a._accountName ='';<BR>[55] 0 seconds -> 0 rows affected<BR>update db_heloc_waterfall
set _csRespondersYTD = 0
where _csRespondersYTD is null;<BR>[60] 0 seconds -> 0 rows affected<BR>update db_heloc_waterfall
set _totalRespondersYTD = _csRespondersYTD<BR>[70] 0 seconds -> 0 rows affected<BR>update db_heloc_waterfall a
inner join 
(select _account_ID, _company, count(_company) as _surgingCSEngagement from abm_responders 
where _dl_date >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)  && _utm_campaign LIKE '%HELOC%' && _utm_source not in ('email', 'linkedin') 
group by 1, 2) b 
on a._accountID = b._account_ID
set a._surgingCSEngagement = b._surgingCSEngagement;<BR>[80] 0 seconds -> 0 rows affected<BR>update db_heloc_waterfall
set _surgingCSEngagement = '0'
where _surgingCSEngagement is null;<BR>[85] 0 seconds -> 0 rows affected<BR>update db_heloc_waterfall
set _surgingAccount = 
CASE
	WHEN _surgingCSEngagement >= 1 THEN 1
ELSE 0
END<BR>[90] 0 seconds -> 0 rows affected<BR>UPDATE db_heloc_waterfall
SET _waterfallStage = 
case
	when _type = 'Active Customer' then 'Active Customer'
    when _type like '%partner%' then 'Partner'
	when (_numberOfOpenOpps >= 1 && _type not in ('Active Customer', 'Partner', 'Potential Partner')) then 'Pipeline'
    when (_numberOfOpenOpps = 0 && _type not in ('Active Customer', 'Partner', 'Potential Partner') && _surgingAccount = 1) then 'Surging'
    when (_numberOfOpenOpps = 0 && _surgingCSEngagement > 1 && _type not in ('Active Customer', 'Partner', 'Potential Partner')) then 'Active Engaged'
    when (_numberOfOpenOpps = 0 && _surgingCSEngagement = 1 && _type not in ('Active Customer', 'Partner', 'Potential Partner')) then 'First Engaged'
	when (_numberOfOpenOpps = 0 && _surgingCSEngagement= 0 && _type not in ('Active Customer', 'Partner', 'Potential Partner')) then 'Unaware'
	else ''
END;<BR>[100] 0 seconds -> 0 rows affected<BR>update db_heloc_waterfall a 
inner join (select _accountID, _accountName, sum(_qualified) as _totalContacts from db_contacts where _validity = 'Valid'  group by 1, 2) b
on a._accountID = b._accountID set a._totalContacts = b._totalContacts;<BR>[110] 0 seconds -> 0 rows affected<BR>update db_heloc_waterfall
set _totalContacts = 0
where _totalContacts is null;<BR>[120] 0 seconds -> 0 rows affected<BR>update db_heloc_waterfall
set _assignment = 'EBQ'
where _assignment is NULL;<BR>[125] 0 seconds -> 0 rows affected<BR>TRUNCATE lookup_opp_db12;<BR>[130] 0.08 seconds -> 0 rows affected<BR>insert ignore into lookup_opp_db12(_accountID, _accountName, _opportunityName, _opportunityID, _stage, _totalACV, _closeTS, _year, _order)
select _accountID, _accountName, _opportunityName, _opportunityID, _stage, _annualAmount, _closeTS, YEAR(_closeTS), _order from db_opportunity order by 2, 8 desc, 9 desc;<BR>[140] 0.9 seconds -> 3651 rows affected<BR>update db_heloc_waterfall a
inner join lookup_opp_db12 b
on a._accountID = b._accountID
set a._opportunity = b._stage, a._totalACV = b._totalACV;<BR>[145] 0 seconds -> 0 rows affected<BR>update db_heloc_waterfall
set _opportunity = ''
where _opportunity is NULL;<BR>[150] 0 seconds -> 0 rows affected<BR>update db_heloc_waterfall
set _totalACV = ''
where _totalACV is null;<BR>[160] 0 seconds -> 0 rows affected<BR>update db_heloc_waterfall a, db_heloc_waterfall_baseline b 
SET a._waterfallSep22 = b._waterfallSep22, a._oppSep22 = b._oppSep22, a._ageSep22 = b._ageSep22, a._stageDurationSep22 = b._stageDurationSep22
WHERE a._accountID = b._accountID;<BR>[170] 0 seconds -> 0 rows affected<BR>update db_heloc_waterfall
set _wSep22 =
case
	when _waterfallSep22 LIKE 'Closed Lost%' then 0
	when _waterfallSep22 LIKE '%Closed ? No Decision/Not Now%' then 0
	when _waterfallSep22 LIKE '%Closed Duplicate%' then 0
	when _waterfallSep22 LIKE '%Unaware%' then 1
	when _waterfallSep22 LIKE '%First Engaged%' then 2
	when _waterfallSep22 LIKE '%Active Engaged%' then 3
	when _waterfallSep22 LIKE '%Surging%' then 4
	when _waterfallSep22 LIKE '%Pipeline%' then 5
	when _waterfallSep22 LIKE '%Active Customer%' then 6
   else ''
END<BR>[180] 0 seconds -> 0 rows affected<BR>update db_heloc_waterfall
set _wCurrent =
case
	when _waterfallStage = 'Closed Lost' then 0
	when _waterfallStage = 'Closed ? No Decision/Not Now' then 0
	when _waterfallStage = 'Closed Duplicate' then 0
	when _waterfallStage = 'Unaware' then 1
	when _waterfallStage = 'First Engaged' then 2
	when _waterfallStage = 'Active Engaged' then 3
	when _waterfallStage = 'Surging' then 4
	when _waterfallStage = 'Pipeline' then 5
	when _waterfallStage = 'Active Customer' then 6
    else ''
END<BR>[190] 0 seconds -> 0 rows affected<BR>Update db_heloc_waterfall
set _oSep22 =
case
	when _oppSep22 = '' then 0
	when _oppSep22 = '00 - Interest' then 1
	when _oppSep22 = '01 - Qualification' then 2
	when _oppSep22 = '01 - Preparation' then 2
	when _oppSep22 = '02 - Discovery' then 3
	when _oppSep22 = '03 - Solution' then 4
	when _oppSep22 = '04 - Proposal' then 5
	when _oppSep22 = '05 - Selection' then 6
	when _oppSep22 = '06 - Contract Negotiation' then 7
	when _oppSep22 = 'Closed Duplicate' then 0
	when _oppSep22 = 'Closed ? No Decision/Not Now' then 0
	when _oppSep22 = 'Closed Lost' then 0
	when _oppSep22 = 'Closed Won' then 8
	when _oppSep22 = 'Rejected - No Opportunity' then 0
   when _oppSep22 = '01 - Preparation' then 2
	when _oppSep22 = '01 - Qualification' then 2
	when _oppSep22 = 'Engage/Discovery' then 3
	when _oppSep22 = 'Solution Presentation' then 4
	when _oppSep22 = 'Proposal' then 5
	when _oppSep22 = 'Negotiation & Selection' then 6
	when _oppSep22 = 'Contracting' then 7
	when _oppSep22 = 'Closed Lost' then 0
	when _oppSep22 = 'Closed Won' then 8
    else ''
END<BR>[200] 0 seconds -> 0 rows affected<BR>update db_heloc_waterfall
set _oCurrent =
case
	when _opportunity = '' then 0
	when _opportunity = '00 - Interest' then 1
	when _opportunity = '01 - Qualification' then 2
	when _opportunity = '01 - Preparation' then 2
	when _opportunity = '02 - Discovery' then 3
	when _opportunity = '03 - Solution' then 4
	when _opportunity = '04 - Proposal' then 5
	when _opportunity = '05 - Selection' then 6
	when _opportunity = '06 - Contract Negotiation' then 7
	when _opportunity = 'Closed Duplicate' then 0
	when _opportunity = 'Closed ? No Decision/Not Now' then 0
	when _opportunity = 'Closed Lost' then 0
	when _opportunity = 'Closed Won' then 8
	when _opportunity = 'Rejected - No Opportunity' then 0
   when _opportunity = '01 - Preparation' then 2
	when _opportunity = '01 - Qualification' then 2
	when _opportunity = 'Engage/Discovery' then 3
	when _opportunity = 'Solution Presentation' then 4
	when _opportunity = 'Proposal' then 5
	when _opportunity = 'Negotiation & Selection' then 6
	when _opportunity = 'Contracting' then 7
	when _opportunity = 'Closed Lost' then 0
	when _opportunity = 'Closed Won' then 8
   else ''
END<BR>[210] 0.01 seconds -> 0 rows affected<BR>update db_heloc_waterfall
set _wMvmtYTD = _wCurrent - _wSep22, 
_wMvmtAT = _wCurrent - _wSep22,
_oMvmtYTD = _oCurrent - _oSep22,
_oMvmtAT = _oCurrent - _oSep22<BR>[230] 0 seconds -> 0 rows affected<BR>