How to serve customized content to each user, based on targeting criteria?

Question

In my PHP Laravel application, I want to display special offers to users, depending on the quality of their house(s).

There are 4 Models: User, House, Offer, OfferTargeting.

• Each User can have many Houses.
• Each House record has details like size, type, city, country etc.
• Each Offer records holds the details of the offer like title, description etc.
• Each OfferTargeting defines the requirements that a House has to have, in order to show the respective offer to the user.

Currently, I run a comparison each time a user visits the offers page, which takes a while since there are many targeting criteria that need to be considered. The problem is that it takes too long and uses too many resources.

What's the correct way to determine which user should see what offer? I already thought of a background job that runs the comparison hourly and stores the results in a new table, like:

id	user_id	offer_id
1	156	2
2	156	3
3	157	2
4	158	16

But I have ~20k users with ~30k houses and each comparison would take at least ~1.5 seconds, so it would be too huge.

What's the correct approach to this? How do companies like Facebook realize their targeting mechanism?

Answer 1

How do companies like Facebook realize their targeting mechanism?

With a big cluster of machines; this is an embarrassingly parallel problem in that you can trivially shard it by user (or by house, whichever is easier for your workflow). Now just spin up enough machines to make it complete in whatever your desired runtime is.

Answer 2

You don't say how you run a comparison of offers to houses, but if it's taking a long time, you're probably iterating through every possible combination of House and OfferTargeting. I think this is a mistake! You want to use some kind of fast lookup mechanism, either a hash table for fast exact matches (city is 'Omaha'), or a binary search tree for less-than/greater-than comparisons. Now you don't have to iterate through every combination, you just have to pre-generate the lookups for one, and you can do process one (say, houses) one at a time. It's still a lot of work to pre-generate those lookups, invalidate them when data in the system changes, etc.

The good news is, SQL can probably do that for you!

SELECT offer.*

FROM OfferTargeting targeting 
LEFT JOIN Offer offer on targeting.offer_id = offer.id
CROSS JOIN House house
WHERE house.user_id = ? AND house.city = targeting.city AND house.country = targeting.country AND targeting.type = house.type AND targeting.min_size <= house.size AND targeting.max_size >= house.size

You will have to do some work with choosing table indexes to make this optimally fast, but even without, it probably be faster than running code to do all the comparisons yourself.