Start Processing : 2025-03-15 11:57:35
0 rows affected
[10] 0.1 seconds -> 0 rows affected
INSERT IGNORE INTO db_email_combined (_activityID, _email, _type, _utm_campaign, _datetime, _source) SELECT _id, _recipient, 'sends', _campaignName, STR_TO_DATE(_eventCreatedDateYourTimeZone, '%m/%d/%Y %H:%i'), 'Hubspot' AS _source FROM db_hubspot_campaign WHERE _recipient NOT REGEXP '2x.marketing|digitalhands|muneetam|student.curtin|osama.tahir' AND `_eventType` = 'DELIVERED' AND _campaignName IN (SELECT _code FROM db_airtable_email) AND _batchID IN (SELECT _batchID FROM atom_queue WHERE _started >= CURDATE() - INTERVAL 1 DAY);
[20] 0.12 seconds -> -1 rows affected
INSERT IGNORE INTO db_email_combined (_activityID, _email, _type, _utm_campaign, _datetime, _source) SELECT _id, _recipient, 'opens', _campaignName, STR_TO_DATE(_eventCreatedDateYourTimeZone, '%m/%d/%Y %H:%i'), 'Hubspot' AS _source FROM db_hubspot_campaign WHERE _recipient NOT REGEXP '2x.marketing|digitalhands|muneetam|student.curtin|osama.tahir' AND `_eventType` = 'OPEN' AND _campaignName IN (SELECT _code FROM db_airtable_email) AND _batchID IN (SELECT _batchID FROM atom_queue WHERE _started >= CURDATE() - INTERVAL 1 DAY);
[30] 0 seconds -> -1 rows affected
INSERT IGNORE INTO db_email_combined (_activityID, _email, _type, _utm_campaign, _datetime, _source, _url) SELECT _id, _recipient, 'clicks', _campaignName, STR_TO_DATE(_eventCreatedDateYourTimeZone, '%m/%d/%Y %H:%i'), 'Hubspot' AS _source, _clickUrl FROM db_hubspot_campaign WHERE _recipient NOT REGEXP '2x.marketing|digitalhands|muneetam|student.curtin|osama.tahir' AND `_eventType` = 'CLICK' AND _campaignName IN (SELECT _code FROM db_airtable_email) AND _batchID IN (SELECT _batchID FROM atom_queue WHERE _started >= CURDATE() - INTERVAL 1 DAY);
[40] 0 seconds -> -1 rows affected
INSERT IGNORE INTO db_email_combined (_activityID, _email, _type, _utm_campaign, _datetime, _source, _bounceReason) SELECT _id, _recipient, 'bounce', _campaignName, STR_TO_DATE(_eventCreatedDateYourTimeZone, '%m/%d/%Y %H:%i'), 'Hubspot' AS _source, _bounceMessage FROM db_hubspot_campaign WHERE _recipient NOT REGEXP '2x.marketing|digitalhands|muneetam|student.curtin|osama.tahir' AND `_eventType` = 'BOUNCE' AND _campaignName IN (SELECT _code FROM db_airtable_email) AND _batchID IN (SELECT _batchID FROM atom_queue WHERE _started >= CURDATE() - INTERVAL 1 DAY);
[50] 0 seconds -> -1 rows affected
INSERT IGNORE INTO db_email_combined (_activityID, _email, _type, _utm_campaign, _datetime, _source) SELECT _id, _recipient, 'unsubscribes', _campaignName, STR_TO_DATE(_eventCreatedDateYourTimeZone, '%m/%d/%Y %H:%i'), 'Hubspot' AS _source FROM db_hubspot_campaign WHERE _recipient NOT REGEXP '2x.marketing|digitalhands|muneetam|student.curtin|osama.tahir' AND `_eventType` = 'STATUSCHANGE' AND _campaignName IN (SELECT _code FROM db_airtable_email) AND _batchID IN (SELECT _batchID FROM atom_queue WHERE _started >= CURDATE() - INTERVAL 1 DAY);
[60] 0 seconds -> -1 rows affected
UPDATE IGNORE db_email_combined SET _unixtime = UNIX_TIMESTAMP(_datetime)
[70] 0 seconds -> 0 rows affected
UPDATE db_email_combined dec, db_airtable_email dae SET dec.`_emailID` = dae .`_id` WHERE dec.`_campaignCode` = dae.`_pardotID` AND dec.`_emailID` = 0;
[80] 0 seconds -> -1 rows affected
UPDATE db_email_combined dec, db_airtable_email dae SET dec.`_utm_campaign` = dae .`_code` WHERE dec.`_campaignCode` = dae.`_pardotID` AND length(dec.`_utm_campaign`) = 0;
[90] 0 seconds -> -1 rows affected
INSERT IGNORE INTO db_hubspot_click (`_email`, `_code`, `_clickedAt`) SELECT _recipient, `_emailCampaignID`, STR_TO_DATE(_eventCreatedDateYourTimeZone, '%Y-%m-%e %H:%i:%s') FROM db_hubspot_campaign WHERE `_eventType` = 'CLICK';
[100] 0.77 seconds -> 0 rows affected
INSERT IGNORE INTO db_hubspot_open (`_email`, `_code`, `_openedAt`) SELECT _recipient, `_emailCampaignID`, STR_TO_DATE(_eventCreatedDateYourTimeZone, '%Y-%m-%e %H:%i:%s') FROM db_hubspot_campaign WHERE `_eventType` = 'OPEN';
[110] 0.26 seconds -> 0 rows affected
INSERT INTO db_bots_identifier (_email, _campaignCode, _timeDiff) (SELECT a.`_email`, b.`_code`, TIME_TO_SEC(TIMEDIFF(b.`_clickedAt`, a.`_openedAt`)) as timediffs FROM db_hubspot_open a JOIN db_hubspot_click b ON a.`_email` = b.`_email` AND a.`_code` = b.`_code` HAVING timediffs <= 10)
[120] 0.03 seconds -> 262 rows affected
UPDATE db_email_combined SET `_isBot` = 1 WHERE `_type` IN ('opens', 'clicks') AND (`_email`, `_campaignCode`) IN (SELECT `_email`, `_campaignCode` FROM db_bots_identifier)
[130] 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_emai_combined WHERE _activityID > 0 GROUP BY _type ON DUPLICATE KEY UPDATE _maxID = VALUES(_maxID);
[140] 0 seconds -> -1 rows affected
INSERT INTO map_download (_prospectID, _downloadID, _datetime, _download_url,_email,_utm_campaign,_utm_source) SELECT _contactID, _id, _recentConversionDate, _formFillUrl, _email, _contentDownloaded, 'email' AS _utm_source FROM db_hubspot_campaign_mql WHERE _email NOT REGEXP '2x.marketing|digitalhands|osama.tahir|student.curtin|muneetam' ON DUPLICATE KEY UPDATE _prospectID = VALUES(_prospectID), _datetime = VALUES(_datetime), _download_url = VALUES(_download_url), _email = VALUES(_email),_utm_campaign = VALUES(_utm_campaign),_utm_source = VALUES(_utm_source);
[150] 0 seconds -> 0 rows affected
UPDATE map_download m, w_routables r SET m._persona = r._function, m._seniority = r._seniority WHERE m._email = r._email
[160] 0 seconds -> 0 rows affected
UPDATE map_download m, db_airtable_email e SET m._emailID = e._id WHERE m._utm_campaign = e._code
[170] 0.02 seconds -> 99 rows affected
UPDATE map_download m, db_emai_campaign r SET m._download_title = r._subject WHERE m._email = r._recipient AND m._emailID = r._emailCampaignID
[180] 0 seconds -> -1 rows affected

[190] 0.71 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
[200] 0.71 seconds -> 0 rows affected

[210] 0.35 seconds -> -1 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 '%digital%'
[220] 0.42 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
[230] 0.01 seconds -> 0 rows affected
UPDATE dashboard_engagement_export e, w_routables d SET e._name = d._name, e._industry = d._industry, e._companyName = d._companyName, e._jobTitle = d._title, e._seniority = d._seniority, e._persona = d._function, e._metroArea = d._state, e._phone = d._phone WHERE e._name IS NULL AND e._email = d._email
[240] 0.1 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')
[250] 0.13 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')
[260] 0.15 seconds -> 0 rows affected
UPDATE db_email_combined dec, db_airtable_email dae SET dec.`_utm_campaign` = dae .`_code` WHERE dec.`_campaignCode` = dae.`_pardotID`AND dec.`_utm_campaign`= '0';
[270] 0 seconds -> -1 rows affected
INSERT IGNORE INTO export_send_log (_id, _emailID, _campaign, _sent, _email) SELECT _id, _emailID, _utm_campaign, _datetime, _email FROM db_email_combined WHERE _type = 'sends' AND _datetime >= NOW() - INTERVAL 30 DAY
[280] 0 seconds -> 0 rows affected