so basically i'm trying to track email performance.
I'm sending out automated emails to a certain population type, and counting whether they converted into a customer.
The problem I'm having is these emails are automated and send about 4 times over a 2 week period. These emails will incorporate newly added prospects who also fall into the category.
I can easily count the number of total emails going out, but I'm struggling with counting the number of unique prospects in that population.
in other words I might send out 10000 emails , but 7500 of them might be one of the 4 automated emails going to the same prospect if that makese sense.
Now I have an activity date of when the email goes out, and also a close date when they become a customer.
Thoughts on how to add this to my query??
Query below:
a.primary_attribute_value end
,DATE_TRUNC('month', a.activity_date) Date
,COUNT(DISTINCT CASE WHEN b.activity_name = 'Send Email' THEN A.id ELSE NULL END) Emails
,COUNT(DISTINCT CASE WHEN b.activity_name = 'Email Delivered' THEN A.id ELSE NULL END) Emails_Delivered
, COUNT(DISTINCT CASE WHEN b.activity_name = 'Email Delivered' THEN c172424.id ELSE NULL END) Emails_Delivered_Customers
,SUBSTRING((COUNT(DISTINCT CASE WHEN b.activity_name = 'Email Delivered' THEN A.id ELSE NULL END)::float / COUNT(DISTINCT CASE WHEN b.activity_name = 'Send Email' THEN A.id ELSE NULL END)::float),1,6) Delivery_Rate
,COUNT(DISTINCT CASE WHEN b.activity_name = 'Open Email' THEN A.ID ELSE NULL END) Opens
,SUBSTRING((COUNT(DISTINCT CASE WHEN b.activity_name = 'Open Email' THEN A.ID ELSE NULL END)::float / COUNT(DISTINCT CASE WHEN b.activity_name = 'Email Delivered' THEN A.id ELSE NULL END)::float),1,6) Open_Rate
FROM
reporting.os_marketo_activities a
LEFT JOIN reporting.os_marketo_leads z ON z.id = a.lead_id
LEFT JOIN reporting.os_marketo_activity_mapping b ON b.activity_type_id = a.activity_type_id
LEFT JOIN reporting.os_marketo_activity_attributes d ON d.id = a.id
LEFT JOIN sf_contact c ON c.id = z.sfdc_contact_id
LEFT JOIN sf_lead l ON l.id = z.sfdc_lead_id
LEFT JOIN sf_opportunity o ON o.accountid = c.accountid
LEFT JOIN sf_opportunity o2 ON o2.id = l.convertedopportunityid
WHERE
a.activity_date >= '2015-05-12'
AND a.primary_attribute_value iLIKE '%acquisition campaign%'
GROUP BY 1,2
having COUNT(DISTINCT CASE WHEN b.activity_name = 'Send Email' THEN A.id ELSE NULL END) > 0
ORDER BY 1,2
This post was edited by noflexzone on Jun 25 2015 12:30pm