Is it possible to report on email turnaround time in Salesforce?

The solution eyescream provided is very good but didn't fit my use case exactly. I needed to calculate email response time for incoming messages so Customer Service could see how effective their team is at replying to customers.

To do this, I had to create a custom object to capture a "snapshot" of EmailMessage. I had to duplicate all the fields from EmailMessage plus add support fields like parent and reply message IDs, response time (hours, minutes, seconds fields), and a custom lookup to Case. I then created a trigger on EmailMessage which analyzed the messages and determined when to create a turnaround record. Finally, I created a custom report type so I could report on this new custom object. The report type's parent object was Case and I joined it with the new custom object.

This isn't programming-related question; you'll get SF administration stuff like that answered faster on http://salesforce.stackexchange.com :)

---

Inbound / outbound emails

1. In the report editor click next to the "Preview" word and select "Summary report".
2. Start typing "Is Incoming" in the search on the left, then drag the field from results to the blue grouping thingie.
3. Optionally in the same row as "Preview" you can deselect "Show -> Details".

You should get something like that:

Play with it more to ensure it does what you want. For example if case resulted in ping-pong-ping-pong it'll have 4 emails in total. When you're "debugging" your report and ensuring it does display what you want it's sometimes a good idea to add second axis.

Select "Matrix format", drag Case Number to left pane and move our "is incoming" to the top pane.

Cool, isn't it?

Average time to respond to email (team and each rep)

That one will be bit trickier. Out of the box the EmailMessage records under Case don't have any kind of "time between it was opened and replied to". You could report on Case creation date vs time the last outbound email was sent - but this includes the idle time of waiting on customer's reply plus if ping-pong... is taking 1 month to resolve it'd make the support people look bad...

The support people in my org are trained to mark case as Closed when they replied (even if it's a request for more info). Then when an email with reference to same case flies in my email2case piece of code locates the original one and reopens it. You might use any other status(es) in that place, the point is that we use report on Case status history, no a report on emails.

If statuses won't work for you you might have to look into some trigger or workflow solution that'd be updating some hidden counter on Case. (act of sending an email logs a completed Task for that Account-Contact-Case)...

Anyway:

1. Start making new report. You'll want to experiment either with "Case Lifecycle" or "Case History" report types.

   

2. (if the report preview appears to have no initial data - make sure "Show -> Details" is selected :))

3. Flip the format to summary, drag Case Number to summaries. Set Units = hours in the part where you define filters. Add a column called "Case History Last Modified Date" and sort by it (click the header). In the beginning the numbers will look weird:

On the left I've put fields from Case - so it's latest and greatest status (Closed, when + info that it was raised 1,600 hours ago).

What you want to report on are the numbers on the right - the duration between each status change, from start to "now". Yes, you've read that right - to "now". If you'll re-run your report you'll see that the last number (for me Jan 15th) went slightly up.

What I did is I've filtered out the "Closed" statuses. That gets rid of the last one that spans to "now" but also filters out all the idle time. Because don't ask me why customer decided to follow-up on a 2 months' old case but sure as hell I won't include that number in the rep's performance report, they'd skin me alive.

Final touches:

1. Use the dropdown on the duration column to use average function
2. Flip the format to matrix. Rows = team & case owner; Columns = for example case opened date. By default dates will be grouped by day but you can use the dropdown to pick month for example.

As I've written before - this might not work in your business. It's not directly tied to Email sending. But you might be able to pull something similar off on a Case + Activity report (that will include tasks that denote emails sent out).

What % of email responses are less than 10 business hours

I'm not going to make pics for that one, just some rough notes.

1. These 10 hours will have to be selected consistently - same method as in second report. I'm fine with status duration. If you'll do it by emails somehow - make sure you set the filters to is incoming = false. If you'll do it by tasks under case - check whether there are any that aren't email-related.
2. In the report editor there's a thing called "bucket field". You can build a bucket of duration. And then use that bucket as grouping thing, either in matrix or summary format.
3. Your numbers will be absolute (rep X has sent 50 replies, 37 of them within the SLA). You could leave them as is and make the calculation in your head... To get relative % you could make a formula field (not the standard formula made in setup, formula just for that report, created above the buckets).
4. The syntax of formulas is pretty weird and I have to send you in the direction of standard help pages ;) The stuff like "PARENTGROUPVAL" is pure weird magic for a beginner (it's a good thing it doesn't have to be used often).