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

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!

Best Answer

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.

Related Solutions

[SalesForce] Show html email status under account

It looks like you cannot query the object directly but you could query the parent of the EmailStatus, for example the contact and it's children records and filter it by account:

Select Name, AccountId, (Select Id, EmailTemplateName From EmailStatuses) From Contact WHERE AccountId = '0018000000QbytrAAB'

[SalesForce] How to combine two reports

This isn't going to be possible the way you defined it

Lead History report primary object is Lead History
Lead Activity History report primary object is Task (Activity)

Hence, no common object to join

Because there is no common object, but a conceptual common Lead.Id, you might be better off writing a simple APEX VF controller and VF page to do the joins in memory into a single wrapper class. You'll need to think about what your layout is going to look like as for a given lead, there are n field histories and m activities