Create a Report showing “HTML email status” by Leads Campaign

Question

I need to generate a report showing parent/child Lead campaigns (and the email template used) with "Opens" from the HTML email status report.

The report would show the Parent Campaign, summarizing the child campaigns. The column headings would include: Number of Leads, Number of Opens and Number of Converted.

I tried creating a joined report with HTML email status, but found every report I tried to join to be an incompatible report type with zero common fields.

Perhaps I could auto-export the original HTML status report to an FTP site, and then try to reimport into a different object?

Can I add in a 'common field' with Leads or Contacts into the HTML Status report?

Any thoughts would be much appreciated!

Answer 1

Short Answer

Unfortunately this doesn't appear possible without some excel work and even then imperfectly

Object Limitations

The HTML Email Status info is stored in an object called EmailStatus which has very limited features (sometimes referred to as a second class object). You can't query it or export it with the dataloader. You can only report on it via the HTML Email Status report and that only has a limit set of fields available (no WhoId to figure out the related Lead/Contact).

Hacky Option

To get a basic report of this in excel you'll need to the export the results of an HTML status report and your campaign data. Then you'll need to use a vlookup to keep the campaign member to the HTML Email status based on the email, phone, or name of the Lead. Since none of these are unique you may have issues if one email has multiple Leads associated with it. Not perfect, and labor intensive, but will get you closer to what you need.