Additional "Lifetime" fields

[aligentjim]

I've been asked to investigate implementing some additional "Lifetime" fields, similar to the existing "Lifetime Sales Value" field. The fields the client requires seem quite generic and generally applicable, so we were considering doing this as a modification to OroCRM and submitting a PR to get the functionality back into the OroCRM core.

The customer would like:

• Lifetime Number of Sales - Number of times the customer has purchased. Use case: Identify customers who have made multiple purchases.
• Lifetime Refund Value - Total value of refunds issued to the customer. Use case: Identify customers who have received large refunds for follow up.
• Lifetime Number of Refunds - Number of times the customer has received a refund. Use case: identify customers who have received multiple refunds.

We're not asking for the Oro team to implement these additional fields (although you can if you like). What I'd like to do via this issue is to establish whether a Pull Request containing this functionality is likely to be accepted and merged (assuming it meets all applicable quality and style guidelines). We would implement the functionality in sympathy with the existing Oro code base using the "Lifetime Sales Value" field as a template. If a PR containing this functionality isn't likely to be merged we'd create our own bundle and implement it as a separate component instead.

[artml]

@aligentjim Hi Jim, thank you for raising this issue!

These metrics or "calculated fields" will indeed be useful for any Oro user, so your PR will be most welcomed.

One thing to keep in mind though: Currently, our own LTSV metric is part of a ChannelBundle, but we are planning to get rid of channels in one of the nearest releases. As a consequence, all metric-related logic will most likely be moved into its own bundle. It is hard to say when exactly it will happen, but please consider this when you will make a PR.

Thanks!

[AdamJHall]

Hi Guys,

While keeping in mind that the channel bundle is on its way out we thought it best to take the functionality out into a standalone bundle. At a high level the bundle would check other bundles for a config file (like datagrids) that defines a source (?) and value (?) entity

e.g.

#Resources/config/oro/lifetime.yml
config:
	magento-lifetime-sales:
		value(?):
			entity: Oro/../Customer 
			id: id                                    # primary key of this entity (defaults to id)
			field: lifetimeSales                      # field we will be aggregating to
		source:
			entity: Oro/../Order                      # entity we will be aggregating
			field: subtotal                           # field we will be aggregating
			id: id                                    # this entities primary key (defaults to id)
			source_id: customer_id                    # the id that relates to the source
			aggregate: SUM                            # Functions like AVG, MIN, MAX, COUNT etc

and then an event listener would watch for changes on the entity defined under the value key and update the source entity accordingly. We can use the existing history table (with some alterations) to track the changes.

This would allow the tracking of multiple lifetime metrics on the same entity not just lifetime sales. The stats could then be inserted using placeholders e.g. The MagentoBundle's customerStats.html.twig

Thoughts on this sort of functionality?

[artml]

@AdamJHall just to clarify: a developer will be able to define these metrics and create/configure as many as they need? So it will be a basic mechanism, not just ad hoc implementation of metrics listed in the first comment.

[AdamJHall]

That would be the idea yes, developers would specify what they need to keep track of in this file and then display it however they need.

My major concern with this approach would be the history table, it could grow quite large with multiple entities being tracked. Any other problems you could see in this implementation?

[artml]

@aligentjim I don't see any other trouble points, but I've asked our developers to check just in case :)

[artml]

@AdamJHall they see no trouble either, looks like your solution is fine.