Are Non-Primary Email Addresses Reportable?

It's been over 9 years since this question was originally posted. Has there been any new development in this area to allow reporting on non-primary emails?

if I manually export a record, I get a column with "Non-primary emails". I want to include that column in a report to verify ALL the emails we have for our people/contacts. But this column doesn't seem to be an option to select when creating reports.

We need to verify that all the emails (including non-primary emails) were migrated properly.

Hi Max Cortez ,

Unfortunately the option to report on non-primary email addresses on standard reports is not available.

There is an internal Idea documented (#68555) which you could ask to get escalated via our Support Portal.

In the meantime, you could get that information with a Custom Query in the Advanced Reports section (Reports -> Advanced Reports).

The query would look something like this for the Contacts module:

SELECT c.id AS contact_id, c.first_name, c.last_name, primary_emails.email_address AS primary_email, GROUP_CONCAT(non_primary_emails.email_address SEPARATOR ';') AS non_primary_emails 
FROM contacts c 
LEFT JOIN email_addr_bean_rel eabr_primary 
ON c.id = eabr_primary.bean_id 
AND eabr_primary.bean_module = 'Contacts' 
AND eabr_primary.primary_address = 1 
LEFT JOIN email_addresses primary_emails 
ON eabr_primary.email_address_id = primary_emails.id 
LEFT JOIN email_addr_bean_rel eabr_non_primary 
ON c.id = eabr_non_primary.bean_id 
AND eabr_non_primary.bean_module = 'Contacts' 
AND eabr_non_primary.primary_address = 0 
LEFT JOIN email_addresses non_primary_emails 
ON eabr_non_primary.email_address_id = non_primary_emails.id 
GROUP BY c.id, c.first_name, c.last_name, primary_emails.email_address 
ORDER BY c.last_name, c.first_name;

If you then create a Custom Data Format and use it to run your query, you will get something like this (image from a demo environment):

I hope it helps but let me know if you have additional questions.

Best,

Francesc del Moral

Thank you Francesc Del Moral Farrarons .  Would it be possible to add the Opted Out and Invalid Email indicator to this query For both the Primary and non primary emails?

So it looks somewhat like the manual export?

We're trying to verify over 500k emails from our migration process and that will help tremendously.  

Thank you Francesc Del Moral Farrarons .  Would it be possible to add the Opted Out and Invalid Email indicator to this query For both the Primary and non primary emails?

So it looks somewhat like the manual export?

We're trying to verify over 500k emails from our migration process and that will help tremendously.  

Hi Max Cortez , 

for that result, your query would look like this:

SELECT 
    c.id AS contact_id,
    c.first_name,
    c.last_name,
    primary_emails.email_address AS primary_email_address,
    IFNULL(primary_emails.opt_out, 0) AS primary_opt_out,
    IFNULL(primary_emails.invalid_email, 0) AS primary_is_valid,
    GROUP_CONCAT(CONCAT(non_primary_emails.email_address, ',', IFNULL(non_primary_emails.opt_out, 0), ',', IFNULL(non_primary_emails.invalid_email, 0)) SEPARATOR ';') AS non_primary_emails
FROM 
    contacts c
LEFT JOIN 
    email_addr_bean_rel eabr_primary 
    ON c.id = eabr_primary.bean_id 
    AND eabr_primary.bean_module = 'Contacts' 
    AND eabr_primary.primary_address = 1
LEFT JOIN 
    email_addresses primary_emails 
    ON eabr_primary.email_address_id = primary_emails.id
LEFT JOIN 
    email_addr_bean_rel eabr_non_primary 
    ON c.id = eabr_non_primary.bean_id 
    AND eabr_non_primary.bean_module = 'Contacts' 
    AND eabr_non_primary.primary_address = 0
LEFT JOIN 
    email_addresses non_primary_emails 
    ON eabr_non_primary.email_address_id = non_primary_emails.id
GROUP BY 
    c.id, c.first_name, c.last_name, primary_emails.email_address, primary_emails.opt_out, primary_emails.invalid_email
ORDER BY 
    c.last_name, c.first_name;

When I run the query, these are the results I get:

I hope this is helps

Francesc del Moral

Thank you Francesc Del Moral Farrarons  This awesome, works great and just what we needed.

Thanks for the feedback Max Cortez ! Happy to hear this is what you needed.