Here's what I need to do. When a new account gets created or updated within the first 30 days since creation, it needs to have a forecasted revenue field populated based on an average of all similar accounts from the last 6 months.
For example, if the new account is region = US and Segment = SMB, it should be based on the average of the revenue field from all accounts with those criteria from the past 6 months.
Is there some way to pull reporting data into APEX without having to rerun the report every single time (maybe refresh daily)? Or is there a better way to store that data in some kind of supplemental data object? Or is it better just to pull those accounts with a soql query and do the math in the trigger?
I'm just really having a hard time figuring out a good way to do this.