How do I report on total opens and clicks per person for each campaign?
Mailchimp Email Activity stores one row per event, so the same person can have several open and click records for a single campaign. Counting those rows in a Salesforce report usually means grouping by Action, and since a summary report only allows three groupings, you end up spending one just to get a count.
To avoid that, Email Activity records include two number formula fields, Opens and Clicks. Each returns 1 when the record's Action is an open or a click respectively, and 0 otherwise, so you can sum them instead of counting rows. They are formula fields, so they add nothing to your data storage.
Using the packaged report
We include a report called Mailchimp Opens & Clicks by Person in the Mailchimp report folder. It is a summary report built on the Mailchimp Email Activities report type, grouped by email address and then by campaign title, with a sum of Opens, a sum of Clicks, and a filter of Action equals open, click.
Because only two groupings are used, you still have one spare for a chart or another field. Read access to both fields is included in the Mailchimp Admin, Standard, Limited and Guest Site User permission sets.
Adapting it to your own reports
Adding the fields yourself:
Opens and Clicks are both available on the Mailchimp Email Activities report type but are not selected by default. Add each one as a column, then set Summarize to Sum.
Grouping by Contact or Lead:
Swap the email grouping for the Contact or Lead reached through the Mailchimp Member lookup if you would rather report on the Salesforce record than the address.
Keeping your changes:
Use Save As to keep your own copy in a folder you control, rather than editing the packaged report in place.
Volume and accuracy
Note: Salesforce displays the first 2,000 groupings in a report. If you have a large volume of email activity, filter to a single campaign or a date range before running it.
The totals reflect the activity stored in Salesforce, not Mailchimp's own reporting figures. By default we sync up to ten repeat opens or clicks per recipient each time the primary sync runs, and older records are cleared based on your Days to Keep setting. If your numbers look lower than Mailchimp's, check your repeat activity limits and storage settings.
