Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

I am new to Salesforce reporting. I have what seems like a simple requirement to report on, but I am not sure if it's possible to do in Salesforce or, if it is possible, how to do it.

The requirements were presented to me as (bullets literally copied-n-pasted):

  • number of inbound emails
  • number of outbound emails
  • average time to respond to email (team and each rep)
  • what percentage of email responses are less than 10 business hours.

Essentially Customer Support wants an email turnaround time report so they can track how effectively they are responding to our customers.

I tried playing around with the Cases with Emails report type. It does give me some useful information, but I don't know how to format the information as requested in the requirement.

Any Salesforce Report gurus want to take a stab and help me out?

share|improve this question

migrated from stackoverflow.com Jan 21 '14 at 13:34

This question came from our site for professional and enthusiast programmers.

2 Answers 2

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:

enter image description here

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.

enter image description here

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.

    enter image description here

  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:

enter image description here

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.

enter image description here

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).
share|improve this answer
    
Thanks for your help eyescream. I ended up implementing a custom solution and was able to trim the requirements when I explained the level of effort. –  Mark Bouchard Feb 19 '14 at 13:39
    
Haha, fair enough :) I'll keep the answer hanging here in case somebody has similar problem. Maybe you'd like to share what you ended up doing - exporting the data to some helper database? –  eyescream Feb 19 '14 at 15:11
    
Good point... I ended up creating a custom object to capture a "snapshot" of EmailMessage and a trigger on EmailMessage. I added a lookup back to Case on the new custom object. Finally, I created a custom report type starting with Case joining my new custom object. All special calculations happen within a class which the trigger calls out to. –  Mark Bouchard Feb 24 '14 at 19:28
    
:D I meant to say that you should make your own answer (and even accept it, it's perfectly OK to move the flag). That way there's higher chance people will encounter it (comments aren't indexed by Google I think) + you'll have higher chance of getting swag (reputation points). –  eyescream Feb 24 '14 at 20:43
    
You're right! I'll write up the answer and mark it later. Thanks for your help! –  Mark Bouchard Feb 24 '14 at 21:53
up vote 2 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.