What is the best practice method to summarize contact information to leads?

Question

Here's the business case. I want to have a reportable field on a lead that displays the number of contacts for, say, a city field match. This value could be used in reports, dashboards, snapshots, etc. The challenge is that we will have 100k+ leads and growing (and same for contacts). The second challenge is that we need the data to be ideally real time, worst case scenario a nightly process.

I've considered a trigger on both contact and lead trigger. Contact trigger would update lead(s) if the city field changed (or record deleted, etc). Lead trigger would populate the initial value. Seems reasonably smart but I have a lot of concern re: performance/etc given data volumes. Can't have this break at the wrong time. :)

Another is a straight up separate app that uses the API to query (ie can query a report on contacts or just use soql), then post back to leads if the number is different. Problem here is the API usage impact as the calls will be substantial.

I have toyed with remoteaction JS stuff but there are issues with subqueries and I remain concerned regarding performance there.

What are the common best practices for querying one object and posting back to another unrelated object, in larger volumes of data?

Thanks!

Answer 1

I think a direct trigger between Leads and Contact would not be feasible due to the large amount of data. What if you have custom object that stores the count for each city. I have outlined a solution below using the custom object. See if it works for you.

1. Create a custom object that has 2 fields - Name for City Name, Count for Number of Contacts with the City = Name.
2. On Contact insert/update/delete/undelete, write a trigger that will update the count on the Custom object. The best option here would have been to use Aggregate Query for the Count but due to the amount of data, that might not be good solution.
3. On Lead insert/update, write a trigger that will pick the count from the Custom object.
4. One case is still pending to be implemented. Update lead count when Contact is inserted/updated/deleted/undeleted. Our point 2 only updates the data in custom object. To achieve this, you can run a nightly batch job. The batch job can just iterate every lead and get the latest count from the Custom object. To make it more efficient, you can probably first query the custom object to get the set of cities updated in the last 24 hours. Then only query those leads whose City falls in that set.