Start Processing : 2025-06-21 05:08:51
0 rows affectedINSERT IGNORE INTO db_pardot_sent (_rowNumber, _batchID, _sentAt, _emailID, _email, _firstname, _lastname, _prospectID) SELECT '-999' _rowNumber, '-999' _batchID, MIN(a._datetime) _firstDateTime, a._emailID, a._email, c._firstname, c._lastname, c._token _prospectID FROM w_prospect_activity_ss a, w_email_ss b, w_routables c WHERE (a._emailID = b._emailID AND a._email = c._email) AND a._type = 'sends' GROUP BY 4,5,6,7,8
[2] 5.03 seconds -> 0 rows affected
UPDATE db_pardot_sent a, db_airtable_email b SET a._code = b._code WHERE a._emailID = b._pardotID
[3] 2.94 seconds -> 0 rows affected
INSERT IGNORE INTO db_pardot_open (_rowNumber, _batchID, _openedAt, _emailID, _email, _firstname, _lastname, _prospectID) SELECT '-999' _rowNumber, '-999' _batchID, MIN(a._datetime) _firstDateTime, a._emailID, a._email, c._firstname, c._lastname, c._token _prospectID FROM w_prospect_activity_ss a, w_email_ss b, w_routables c WHERE (a._emailID = b._emailID AND a._email = c._email) AND a._type = 'opens' GROUP BY 4,5,6,7,8
[4] 6.37 seconds -> 0 rows affected
UPDATE db_pardot_open a, db_airtable_email b SET a._code = b._code WHERE a._emailID = b._pardotID
[5] 0.04 seconds -> 0 rows affected
INSERT IGNORE INTO db_pardot_click (_rowNumber, _batchID, _clickedAt, _emailID, _email, _firstname, _lastname, _prospectID) SELECT '-999' _rowNumber, '-999' _batchID, MIN(a._datetime) _firstDateTime, a._emailID, a._email, c._firstname, c._lastname, c._token _prospectID FROM w_prospect_activity_ss a, w_email_ss b, w_routables c WHERE (a._emailID = b._emailID AND a._email = c._email) AND a._type = 'clicks' GROUP BY 4,5,6,7,8
[6] 0.32 seconds -> 0 rows affected
UPDATE db_pardot_click a, db_airtable_email b SET a._code = b._code WHERE a._emailID = b._pardotID
[7] 0.3 seconds -> 0 rows affected
INSERT IGNORE INTO db_pardot_click (_rowNumber,_batchID,_clickedAt,_code,_email,_firstName,_lastName,_prospectID) SELECT k._id,-999,DATE_FORMAT(_timestamp,'%c/%e/%Y %H:%i'),_campaign,_email, _firstname, _lastname, _pid FROM webtrack_hittrack k, w_routables r WHERE _pid > 0 AND k._pid = r._token AND _website NOT IN ('microsoft.com') AND LENGTH(_campaign)>0 AND r._email NOT LIKE '%2x.marketing%' AND r._email NOT LIKE '%atlas%' AND r._email NOT LIKE '%test%'
[9] 0.09 seconds -> 139 rows affected
INSERT IGNORE INTO db_pardot_combined (_activityID, _prospectID, _email, _type, _campaignCode, _datetime) SELECT _id, _prospectID, _email, 'sends', _code, _sentAt FROM db_pardot_sent WHERE _email NOT LIKE '%2x.marketing%' AND _email NOT LIKE '%test%' AND _id > ( SELECT COALESCE(MAX(_maxID), 0) FROM lookup_daily_maxID WHERE _type = 'sends' AND _date < DATE_FORMAT(NOW(),'%Y-%m-%d') )
[10] 0.01 seconds -> 0 rows affected
INSERT IGNORE INTO db_pardot_combined (_activityID, _prospectID, _email, _type, _campaignCode, _datetime) SELECT _id, _prospectID, _email, 'opens', _code, _openedAt FROM db_pardot_open WHERE _email NOT LIKE '%2x.marketing%' AND _email NOT LIKE '%test%' AND _id > ( SELECT COALESCE(MAX(_maxID), 0) FROM lookup_daily_maxID WHERE _type = 'opens' AND _date < DATE_FORMAT(NOW(),'%Y-%m-%d') )
[15] 0 seconds -> 0 rows affected
INSERT IGNORE INTO db_pardot_combined (_activityID, _prospectID, _email, _type, _campaignCode, _datetime) SELECT _id, _prospectID, _email, 'clicks', _code, _clickedAt FROM db_pardot_click WHERE _email NOT LIKE '%2x.marketing%' AND _email NOT LIKE '%test%' AND _id > ( SELECT COALESCE(MAX(_maxID), 0) FROM lookup_daily_maxID WHERE _type = 'clicks' AND _date < DATE_FORMAT(NOW(),'%Y-%m-%d') )
[20] 0.33 seconds -> 139 rows affected
UPDATE db_pardot_combined c, w_routables r SET c._routablesID = r._token WHERE c._email = r._email
[22] 1.67 seconds -> 139 rows affected
UPDATE IGNORE db_pardot_combined SET _unixtime = UNIX_TIMESTAMP(_datetime)
[24] 0.15 seconds -> 0 rows affected
UPDATE db_pardot_combined SET _trimCode = REPLACE(_campaignCode,'-','')
[26] 3.26 seconds -> 41786 rows affected
UPDATE db_pardot_combined SET _trimCode = REPLACE(_trimCode ,'(','')
[28] 0.16 seconds -> 0 rows affected
UPDATE db_pardot_combined SET _trimCode = REPLACE(_trimCode ,')','')
[29] 0.16 seconds -> 0 rows affected
UPDATE db_pardot_combined SET _trimCode = REPLACE(_trimCode ,'_','')
[30] 2.21 seconds -> 41786 rows affected
UPDATE db_pardot_combined c, db_airtable_email e SET c._emailID = e._id WHERE c._trimCode = e._trimCode AND c._emailID = 0
[35] 0.04 seconds -> 134 rows affected

[40] 0.07 seconds -> 0 rows affected
INSERT INTO map_download (_prospectID, _downloadID, _datetime, _download_url, _url_param, _persona, _seniority,_download_title,_email,_utm_campaign, _utm_medium,_utm_source) SELECT _prospectNativeID, _id, _timestamp, _download_url,_url_param, _persona, _seniority, _download_title,_email, _utm_campaign, _utm_medium,_utm_source FROM w_lead_downloads WHERE _email NOT LIKE '%2x.marketing%' AND _email NOT LIKE '%test%' AND _email NOT LIKE '%3si%' AND _utm_source = 'sharpspring' AND LENGTH(_url_param)>0 ON DUPLICATE KEY UPDATE _prospectID = VALUES(_prospectID), _datetime = VALUES(_datetime), _download_url = VALUES(_download_url), _url_param = VALUES(_url_param), _persona = VALUES(_persona), _seniority = VALUES(_seniority), _download_title = VALUES(_download_title), _email = VALUES(_email),_utm_medium = VALUES(_utm_medium),_utm_campaign = VALUES(_utm_campaign),_utm_source = VALUES(_utm_source)
[110] 0.1 seconds -> 0 rows affected
UPDATE map_download m, w_routables r SET m._persona = r._function, m._prospectID = r._token WHERE m._email = r._email
[120] 0.03 seconds -> 0 rows affected
UPDATE map_download SET _persona = 'C-Level' WHERE LENGTH(_persona) = 0 AND _seniority = 'C-Level'
[130] 0 seconds -> 0 rows affected
UPDATE map_download SET _persona = 'Others' WHERE LENGTH(_persona) = 0
[140] 0 seconds -> 0 rows affected
UPDATE map_download SET _seniority = 'Others' WHERE LENGTH(_seniority) = 0
[150] 0 seconds -> 0 rows affected
UPDATE w_lead_downloads d, map_download m SET d._persona = m._persona WHERE d._id = m._downloadID
[160] 0 seconds -> 0 rows affected
UPDATE map_download SET _trimCode = REPLACE(REPLACE(REPLACE(REPLACE(_utm_campaign,'-',''),'_',''),'(',''),')','')
[165] 0 seconds -> 0 rows affected
UPDATE map_download m, db_airtable_email e SET m._emailID = e._id WHERE m._trimCode = e._trimCode
[170] 0 seconds -> 0 rows affected
UPDATE w_lead_downloads d, w_routables r SET d._routable_status = r._state WHERE d._email = r._email
[180] 0 seconds -> 0 rows affected
UPDATE w_lead_downloads SET _routable_status = '' WHERE _routable_status = 'require enrichment'
[190] 0 seconds -> 0 rows affected

[200] 4.35 seconds -> 0 rows affected
UPDATE dashboard_email_performance p, db_airtable_email e SET p._url = e._landingPage, p._subject = e._subject, p._screenshot = e._screenshot, p._title = e._code WHERE p._emailID = e._id
[210] 4.39 seconds -> 0 rows affected
UPDATE dashboard_email_performance SET _wave = 1 WHERE _title LIKE '%W1%'
[220] 0 seconds -> 0 rows affected
UPDATE dashboard_email_performance SET _wave = 2 WHERE _title LIKE '%W2%'
[230] 0 seconds -> 0 rows affected
UPDATE dashboard_email_performance SET _wave = 3 WHERE _title LIKE '%W3%'
[240] 0 seconds -> 0 rows affected

[300] 0.15 seconds -> 0 rows affected
INSERT IGNORE INTO dashboard_engagement_export (_ssActivityID,_prospectID,_email,_type,_emailID,_date) SELECT _downloadID*-1,_prospectID,_email,'download',_emailID,_datetime FROM map_download WHERE _email NOT LIKE '%2x.marketing%' AND _email NOT LIKE '%test%' AND _email NOT LIKE '%3si%' AND _email NOT LIKE '%itedge%'
[310] 0.18 seconds -> 0 rows affected
UPDATE dashboard_engagement_export e, w_routables r SET e._name = r._name, e._whyTargeted = r._employees, e._industry = r._industry, e._companyName = r._companyName, e._jobTitle = r._title, e._revenue = r._revenueRange, e._seniority = r._seniority, e._persona = r._function, e._metroArea = r._state, e._tier = r._city, e._phone = r._phone WHERE e._name IS NULL AND e._email = r._email
[320] 0.01 seconds -> 0 rows affected
UPDATE dashboard_engagement_export e, w_routables r SET e._phone = r._companyHqPhone WHERE LENGTH(e._phone) = 0 AND e._email = r._email
[330] 0.01 seconds -> 0 rows affected
UPDATE dashboard_engagement_export SET _persona = 'Others' WHERE LENGTH(_persona) = 0
[335] 0.01 seconds -> 0 rows affected
UPDATE dashboard_engagement_export e, db_airtable_email a SET e._campaign = a._code, e._screenshot = a._screenshot WHERE e._emailID = a._id
[340] 0.02 seconds -> 0 rows affected
UPDATE dashboard_engagement_export e INNER JOIN (SELECT _email, _emailID FROM dashboard_engagement_export WHERE _type = 'download') o SET e._showExport = 0 WHERE e._email = o._email AND e._emailID = o._emailID AND e._type IN ('opens','clicks')
[350] 0.01 seconds -> 0 rows affected
UPDATE dashboard_engagement_export e INNER JOIN (SELECT _email, _emailID FROM dashboard_engagement_export WHERE _type = 'clicks') o SET e._showExport = 0 WHERE e._email = o._email AND e._emailID = o._emailID AND e._type IN ('opens')
[360] 0.02 seconds -> 0 rows affected
UPDATE dashboard_engagement_export e, lookup_state_code a SET e._metroArea = a._stateLong WHERE e._metroArea = a._stateCode
[370] 0 seconds -> 0 rows affected
UPDATE dashboard_engagement_export e, w_email_firstengaged s SET e._score = s._engagementScore WHERE e._email = s._email
[380] 0 seconds -> 0 rows affected
INSERT INTO lookup_daily_maxID (_date, _type, _maxID) SELECT DATE_FORMAT(NOW(),'%Y-%m-%d'),_type, MAX(_activityID) FROM db_pardot_combined WHERE _activityID > 0 GROUP BY _type ON DUPLICATE KEY UPDATE _maxID = VALUES(_maxID)
[400] 0.01 seconds -> 2 rows affected