Reporting on Active Customers

[RevJoel]

I am attempting to create a report to track active customers by month for the previous 12 months so we can monitor customer retention. I have a report that gives me the total number of clients as of a specific date, however what I am looking for is a count by month of the active clients by  month for the past 12 months.

This will be going into a dashboard for monitoring purposes. Any ideas how I can do this? 

[Stevemo]

How are you defining what an "active" customer is?  Are you using Opportunities and/or Contracts, or something else?

Message Edited by Stevemo on 08-17-2009 12:32 PM

[RevJoel]

I have a field called 'Type'. If a customer is active, they are assigned the value of 'Customer'. If they are inactive, they are assigned the value of 'Previous Customer'. Thanks for your assistance.

[Stevemo]

Okay, if you're working strictly from the Account Object, then you might be able to get there with an Account History Report using   "Field / Event equals Type" in your Filter criteria.

[RevJoel]

Yes. I did get this far. That report actually turned up zero results. I believe that report actually is for changes in field type or field name not actual data. The search continues...

[Stevemo]

Are you sure?  I just tested it on both my Dev and Prod SFDC Org, and it worked fine. 

Do you have field history tracking enabled on your SFDC Org?

[RevJoel]

Field Tracking History was not turned on, but I did so now. It should work in the future.

For now, the question remains How do I create a report like this one described below... (the forum will not allow me to attach an image)

 The Total Active col is the total active as of the first day of the first month (ie. 01/01/08). The Total # lost is as of the first day of the last month of the range(ie. 01/01/09). Total # lost is the total # accts with a Date Terminated Service field set to a  month within the range and the % is the total lost / total active accounts.

I hope this makes sense. 

[Stevemo]

Can you capture a screen shot and attach it?  It's kinda buried, but there's an upload attachment link in the bottom left corner of the chat/dialog box.

[RevJoel]

Here is a screenshot of the type of report I am looking for. Thanks so much for your help with this. My hairline is receding.

[Stevemo]

You might wanna consider creating custom checkbox fields for "Active" and "Former Client" similar to the standard IsClosed and IsWon opportunity fields, that might give you a little more flexability in your reports when comparing Count of Active against Count of Lost over a given period of time.  (but that's just my 2 Pesos)