Is there a way to see the date when a prospect/suspect became a customer? I see EstDate in the customer table, but that seems to hold just the date when the entry was added to the system. We want a record of all new “customers” (not prospects) in the last 30 days.
I don’t see this customer conversion date captured anywhere, but maybe someone else has more information. One simple modification to get what you’re seeking would be to add a pre-processing method directive on Customer.Update that sets the EstDate to DateTime.Today when the CustomerType field has been changed from “any” to “CUS”. This date is changeable through the front-end on the billing tab, so I don’t believe there are conflicts, but I haven’t tested this. Obviously, if you want to keep both dates preserved, you can create a new UD field with the same BPM and modify your query/report accordingly.
As a forward fix, you can also enable the Change Log on Customer (as an In-Transaction Data Directive) and capture changes made to CustomerType.
You can review the change log from the Customer screen in Epicor or you can query Ice.ChgLog in SQL.
I’m just guessing, but I’d think the date of the first order the customer was specified on, would be that date.
So if you do create a UD field and DD for capturing future changes, you could back fill that field by making a BAQ that returns the earliest OrderHed.OrderDate per customer.
Good ideas. Thank you. We are also interested in reviewing the percentage and average time it takes for a prospect/suspect to turn into a customer.
I actually like @ckrusen’s idea that this can be solved entirely with reporting on a customer’s first order’s OrderDate rather than on CustomerType. You can report on the difference between Customer.EstDate and the first OrderHed.OrderDate for all of the data elements you’re seeking. Obviously, this depends on your business perception of what defines when a customer becomes a customer.
While I agree with @mshier that a change log will capture the information at the point of change and can be viewed through reporting, I think Ice.ChgLog is a bit messy to report on especially over large date ranges and joined to other tables.
Does anyone know the logic E10 uses to make the customer type change?