Report on Marketing Email Recipient Events?

[KLowe1]

Hello! 

I am currently working on building a power bi report to show what emails have had the most impact on our contacts. I want to know which contacts have been sent / opened /clicked on which emails.  

I'm looking for a way to pull Marketing Email Events (Delivered, Opened, Clicked, etc.), contact email address, and name of Marketing Email/campaign for each new Marketing Campaign.

Currently we are exporting each Campaign's Marketing emails "Recipient List" as a CSV, but this is tedious and prone to error. 

Has anyone accomplished this using the Custom Report builder, an API, or some 3rd party service? Looking for recommendations. I've seen "Datawarehouse Power Bi connector" mentioned in some other posts.

[KLowe1]

Thanks again for this helpful answer @PamCotton .

Do you or @Josh / @Crystal_Hopper  have any advice for Power bi consistenly timing out with "Expression.Error: Evaluation resulted in a stack overflow and cannot continue." when trying to pull in Click Events?

Have been using this solution from Micrsoft's forums so far (in a blank query on Bi advanced query editor):

Get all CLICK events

let
GetPages = (queryParams)=>
let
Host = "https://api.hubapi.com",
Source = Json.Document(
Web.Contents(
Host,
[RelativePath = "email/public/v1/events", Query = queryParams, Headers=[#"Content-Type"="application/json", Authorization="Bearer INSERT-YOUR-TOKEN-HERE"]]
)),
LL= @Source[events],
Next = [limit="1000", eventType = "CLICK", offset = Source[#"offset"]],
result = try @LL & @GetPages(Next) otherwise @LL
in
result,

Fullset = GetPages([limit="1000", eventType = "CLICK"]),
#"Converted to Table" = Table.FromList(Fullset, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in

#"Converted to Table"

[KLowe1]

Thanks @PamCotton  - That is really helpful!

I've been reading up on Hubspot's Email Event APIs, but I'm having some confusion on how to access it through Power Bi.

I've created a Private App and confirmed that the scope is "marketing-email (Read)." I've used that Private App to generate an API token key.  

Through looking online, I tried this Connection query in Power Bi (in a blank query), but to no avail:

let
    Source =
        OData.Feed(
            "https://api.hubapi.com/marketing/v3/email/events",
            null,
            [Headers = [
                #"ApiKey" = "<YOUR API KEY>"
            ]]
        )
in
    Source 

I've also tried the third party service Windsor Ai to skip some of the API/connection stuff, but have not been able to get it to go granular enough to specific email events per email address.

Really appreciate the insight! 

[PamCotton]

Hey @KLowe1, thank you for posting in our Community.

You can use HubSpot’s Email Events API to pull, send, open, and click data into Power BI automatically. 

The Custom Report Builder can also help but has limitations.

To our top experts @Crystal_Hopper and @Josh any other recommendations for @KLowe1?

Thank you,

Pam