SELECT
c.ID as SubscriberKey,
i.Cat_Campaign_Most_Recent__c,
i.Email__c as EmailAddress,
i.ID as Interaction_ID,
i.CreatedDate,
i.First_Name__c,
i.Last_Name__c,
i.Business_Phone__c,
i.Company_Name__c,
i.Mailing_Country__c,
i.Mailing_Zip_Postal_Code__c,
i.Purchase_Timeframe__c,
i.Level_of_Interest__c,
i.System_Opt_in_for_eMail__c,
i.Contact_Source_Details_Most_Recent__c,
i.System_Language__c,
i.Industry__c,
i.Industry_Level_2_Master__c,
i.Job_Role__c,
i.Serial_Number__c,
i.Ci_Fleet_Size__c,
i.Business_Unit__c,
c.AMC_Status__c,
c.AMC_Last_Activity_Record_ID__c
FROM ent.interaction__c_salesforce i
JOIN ent.Contact_Salesforce_1 c ON c.Email = i.Email__c
WHERE i.Email__c IS NOT NULL
AND (i.System_Language__c LIKE 'en_%' OR i.System_Language__c IS NULL)
AND (i.Mailing_Country__c = 'US' OR i.Mailing_Country__c = 'CA')
AND (i.Mailing_State_Province__c NOT LIKE 'QC' OR i.Mailing_State_Province__c IS NULL)
AND i.Business_Unit__c IN (
'CISD',
'GASD',
'Product Support',
'PS'
)
AND i.Cat_Campaign_Most_Recent__c NOT IN (
'PCC eNews',
'OHTE Offers',
'Mid America Trucking Show',
'Product Support On-Highway Legacy Engines Initiate Dealer',
'Million Miler',
'Iron Planet Auction - On Highway Legacy Machine',
'Product_Support_ECommerce_Contact',
'Iron Planet Auction - On Highway Legacy Engine',
'EP Spec Sizer Nurture',
'Parts.cat.com Account Registration',
'EP General Nurture',
'Drivecat.com - Mini Survey',
'OHTE Independent Shop Sweepstakes',
'Product Support Million Miler',
'OHTE Trucking Is Life eNews'
)
AND i.Id IN (
SELECT max(i2.Id)
FROM ent.interaction__c_salesforce i2
GROUP BY i2.Email__c
)
Comments