SQL Query to Pull Email recipeint List

[RedKnight]

Hi i am currently working on building a power bi report that reports on one of our email campaigns. Im using the Datawarehouse Power Bi connector to query our instance of HubSpot. I have found the list of events associated to the particular email campaign in question however im struggling to match the list up with the recipient list in HubSpot. It looks like HubSpot is applying some kind of filtering, im assuming removing any recipients that dont have a reliable chain ie. Sent,Delivered and Opened as opposed to just having an open event on its own. However im unable to decipher this , has anyone got any ideas of how HubSpot chooses what recipients to list when looking at a emails analytics such as open rate, click rate etc.Has anyone done any SQL that might help with matching the numbers with HubSpot.

[RedKnight]

Hi Grey thank you for this will this give me the numbers that will match are on the front end, I've not really had any issue in pulling the event data for each of the receipients and the matching them to the recipient the problem has been the numbers of the front end seem to be alot lower than what the back end is producing which seems to suggest there is some addtional filtering going on beyond just selecting the appropriate campaign id. I just dont know what that is nor is there any indicaton though HubSpot does seem to suggest if you have bot filtering on which ours does then it filters out any opens that may deem not be done by a human using some form of algorythm. 

[GreyHamilton · Accepted solution]

Hey @RedKnight 

See if this SQL gets you closer to what you're looking for. The top 100 is only for testing since it can sometimes be a rather large table. The key here is the WHERE clause to filter to the specific email you mentioned. You could also swap out the emailcampaignid for the Email Name if that's easier.

SELECT top 100

CE.name AS "Email Name",
CONCAT(C.firstname, ' ',C.lastname) AS "Contact Name",
C.email AS "Contact Email",
CEE.EmailCampaignId AS "Campaign ID",
CEE.Type AS "Email Type"

from dbo.Contact C

LEFT JOIN dbo.CampaignEmailEvent CEE
ON C.email = CEE.Recipient

LEFT JOIN dbo.CampaignEmail CE
ON CE.EmailCampaignId = CEE.EmailCampaignId

WHERE CEE.EmailCampaignId = 266612345

This should output every interaction that happened with a Contact with that email. Sent, Delivered, Processed, Opens, Clicks, etc. Then you can utilize PBI to select or filter to the counts of each if needed.

If you're only looking for the Contacts that the email went to then you can add some group logic:

SELECT top 100

CE.name AS "Email Name",
CONCAT(C.firstname, ' ',C.lastname) AS "Contact Name",
C.email AS "Contact Email",
CEE.EmailCampaignId AS "Campaign ID"

from dbo.Contact C

LEFT JOIN dbo.CampaignEmailEvent CEE
ON C.email = CEE.Recipient

LEFT JOIN dbo.CampaignEmail CE
ON CE.EmailCampaignId = CEE.EmailCampaignId

WHERE CEE.EmailCampaignId = 266612345

GROUP BY 
CE.name,
CONCAT(C.firstname, ' ',C.lastname),
C.email,
CEE.EmailCampaignId

You could also filter to this to the specific event if needed like:

WHERE CEE.EmailCampaignId = 266612345 AND CEE.type = 'Delivered'

Hope this steers you in the right direction!

[johnelmer]

Thanks @GreyHamilton for assisting with this support issue. @RedKnight  Grey is a senior technical consultant at Bayard Bradford and works regularly with Datawarehouse.io SQL databases. Please DM me if you have any other questions.

[johnelmer]

@RedKnight  I have forwarded your question to our technical consulting team to see what they recommend. Stay tuned...