Database structuur "Andere bezoekers bekeken ook..."

[roy-t]

Ik ben op zoek naar een goede manier om een databae structuur op te zetten voor "Andere bezoekers bekeken ook" Zoals bijvoorbeeld amazon dat doet: http://www.amazon.com/Tus...2G1S0/ref=cm_cr_pr_sims_t .

Mijn eerste idee was een kruistabel van product x product, maar bij deze tabel moet je telkens rijen en kolommen toevoegen en ik weet niet of dat een groot probleem gaat zijn voor een database als je een tabel hebt van 2000x2000 (ook al select je hier uit natuurlijk alleen een top5 en alleen maar 1 kolom). Sowieso heb ik het idee dat je productie code het liefst alleen INSERT UPDATE en DELETE moet doen en niet ALTER maar waarschijnlijk heb ik het daar mis.

Ik heb even gegoogled op termen als "Database Structure other customers viewed" maar hier kwam niets nuttigs uit.

Nu vraag ik me dus af of een kruistabel een goed idee is maar met een datacomplexiteit van N^2 leek me dit toch niet de oplossingen.

Later kwam ik op het volgende idee dat een soort van "Landing" tabel is, waarin wordt opgeslagen

---PaginaID--VorigePaginaID--Count

Met een stored procedure wou ik dan een paginaID en vorigePaginaID toevoegen als deze combinatie nog niet in de database zit, en anders count ophogen.

Daarna zou ik een soort query kunnen lostlaten als "Select PaginaID from Landing where VorigePaginaID = myID Order By Count LIMIT 5" (Even snel uit het hoofd zullen wat foutjes in zitten).


Is dit een goede performante oplossing, het klinkt beter dan de kruistabel en de complexiteit is <=N^2 (alleen N^2 als alle pagina's direct na alle pagina's worden bezocht).

Dus ik vroeg me af landing tabel klinkt goed maar er zouden wat onvoorziene problemen mee kunnen komen die ik nu misschien nog niet zie, kruistabel klinkt slecht maar misschien is er toch iets voor te zeggen. Of zijn er nog andere opties?

[tonyisgaaf]

Ik vind de oplossing PaginaId-VorigePaginaId-Count prima klinken. Daar hoef je zelfs geen stored procedure voor te maken. Als je de Primary Key op de combinatie PaginaId,VorigePaginaId legt, dan kun je d.m.v. een INSERT ... ON DUPLICATE KEY UPDATE inserten of de count ophogen.

edit: kanttekening: wat je wellicht wilt is toch van elke pagina -> volgende pagina het tijdstip vastleggen. Stel dat de interesse van de gemiddelde bezoeker gedurende de tijd verandert, dan kun je je aanbevelingen laten meeveranderen. En het geeft je zinvolle analytics data.

[ Voor 33% gewijzigd door tonyisgaaf op 13-06-2009 13:07 ]

[kasper_vk]

Een functioneel vraagje: wil je alleen de rechtstreekse navigaties meetellen voor de 'Andere Bezoekers Bekeken Ook X' -statistieken?

Stel ik zoek iets op jouw site, klik op resultaat A, vervolgens zoek ik opnieuw met iets aangepaste criteria en vind dan pas wat ik zocht, namelijk B. Volgens mij zou je dan A en B aan elkaar willen kunnen relateren, omdat dát m.i. de meerwaarde is van zulke functionaliteit.
Of ik nagiveer van K naar L naar M; wil je geen verband tussen K en M leggen?

Wanneer je kiest voor een aanpak als vorige-volgende-count, dan raak je een deel van je waardevolle info kwijt, denk ik. Wanneer je echter kiest voor iets als gebruiker-pagina-tijdstip o.i.d., dan kun je dezelfde info eruit halen maar ook véél meer dan dat.
Je kunt dan ook kijken hoe lang iemand op een bepaalde pagina bleef, voor ie verder klikte. Dat kan veel waardevoller zijn dan wat de directe voorgaande / nakomende pagina was.

'T is maar een idee

[muksie]

Je zou de kruistabel ook kunnen implementeren met slechts 3 kolommen: product_x_id - product_y_id - count waarbij (product_x, product_y) de primary key is. Let er dan wel even op dat het niet mogelijk is dat bijv. (a,b) en (b,a) beide voor kunnen komen.

Dan kun je, bijv. aan het eind van de sessie de count ophogen voor alle combinaties van bekeken producten, dus niet alleen dat ze rechtstreeks van het ene naar het andere product doorklikten, maar ook indirect.

Voor het ophogen van de count kun je vervolgens allerlei algoritmes bedenken. De simpelste oplossing is om deze gewoon met 1 op te hogen voor iedere bekeken combinatie, maar je kunt ook bijv. de tijd dat iemand op de productpagina zat laten meewegen etc.

Wil je het nog wat uitgebreider, dan kun je nog zoiets overwegen: (id, product_x, product_y, date_viewed, weight) waarbij voor iedere keer dat een combinatie bij een bezoeker voorkwam je weer een rij toevoegt (en dus niet product_x, product_y de primary key is). Op deze manier kun je ook "Andere bezoekers bekeken recent ook..." implementeren d.m.v. je date veld. Je weight kan je weer gebruiken voor dingen als hoelang iemand de pagina bekeek etc. Je queries zullen dan echter wel wat complexer worden, bijv.

SQL:

SELECT SUM(weight) AS total_weight 
FROM product_relations 
WHERE 
    (product_x = XXX OR product_y = XXX) 
    AND date_viewed > (CURRENTDATE() - INTERVAL '7 DAYS') 
GROUP BY product_x, product_y
ORDER BY total_weight DESC
LIMIT 5

[roy-t]

kasper_vk schreef op zaterdag 13 juni 2009 @ 13:18:
Een functioneel vraagje: wil je alleen de rechtstreekse navigaties meetellen voor de 'Andere Bezoekers Bekeken Ook X' -statistieken?

Stel ik zoek iets op jouw site, klik op resultaat A, vervolgens zoek ik opnieuw met iets aangepaste criteria en vind dan pas wat ik zocht, namelijk B. Volgens mij zou je dan A en B aan elkaar willen kunnen relateren, omdat dát m.i. de meerwaarde is van zulke functionaliteit.
Of ik nagiveer van K naar L naar M; wil je geen verband tussen K en M leggen?

Wanneer je kiest voor een aanpak als vorige-volgende-count, dan raak je een deel van je waardevolle info kwijt, denk ik. Wanneer je echter kiest voor iets als gebruiker-pagina-tijdstip o.i.d., dan kun je dezelfde info eruit halen maar ook véél meer dan dat.
Je kunt dan ook kijken hoe lang iemand op een bepaalde pagina bleef, voor ie verder klikte. Dat kan veel waardevoller zijn dan wat de directe voorgaande / nakomende pagina was.

'T is maar een idee

Hmm iets ingewikkelder maar het bijhouden wat een bezoeker (geanomiseerd) bekeek in korte tijd na het bezoeken van een pagina of in een sessie geeft misschien erg waardevolle data de vraag is dan wel: hoe destilleer je die data zo simpel mogelijk uit een tabel.

Ik zou me een tabel kunnen voorstellen met:
sessie--tijd--product

Hier uit kun je halen: hoe lang iemand op een product pagina keek, waar hij heen ging, wat hij hiervoor en hierna bekeek. Maar ik zie zo 1,2,3 niet hoe je dit met simpele queries of stored procedures kan doen. Je zou een soort tabel moeten terugkrijgen van "van pagina's uit verschillende sessies die rond 15minuten van het bezoeken van pagina X bekeken werden" Maar ja wat is daar een goede query voor? Of hoe krijg je iig een subset van je data zodat je deze snel in PHP/ASP kunt verwerken?

@ tonyisgaaf, cool ik wist niet dat er een On Duplicate KEY commando was.

@Muskie, cool al bijna antwoord terwijl ik post, maar hoe houd jouw query/ontwerp er rekening mee dat verschillende users die views te weeg kunnen brengen in je laatste query zie ik dit niet terug daar lijk je alleen op datum te selecteren en zo dus iedereen die op dat moment op de site was en verbindingen aan het leggen was mee neemt ipv mensen die die pagina hadden bekeken.

[ Voor 8% gewijzigd door roy-t op 13-06-2009 14:25 ]

[djluc]

Ik denk dat dit wel een hele goede is:
sessie--tijd--product

Vervolgens kan je daarmee gaan matchen, daarvan zou ik een stukje denormaliseren. Denk bijvoorbeeld aan: Elke 24 uur bereken je 5 de meest naastliggende producten. Die sla je op in een simpele cache tabel:
product-relatedproduct
Hierin maak je dus bijvoorbeeld 5 rijen of 20 waarvan je er random 5 laat zien.

Het kan natuurlijk nog veel gekker want je kan het ook per gebruiker weer gaan aanpassen. Een gebruiker die 5x een boek bekijkt van psychologie zal daar blijkbaar interesse in hebben dus je kan bijvoorbeeld besluiten bijna alleen maar van dat soort boeken te laten zien, ookal kijken andere bezoekers ook veel naar boeken over filosofie.

[mithras]

Je bent er toch al als je een pagina / producten tabel hebt, een tabel maakt met sessies en die met een n:m koppeling koppelt? Je kan er de tijd nog bij meenemen mocht je dat willen, maar je kan dan al wel zien tijdens welke sessie welke producten zijn aangeschaft. Met een cron kan je dan de statistieken elke dag (of andere periode) opnieuw updaten

[muksie]

@Muskie, cool al bijna antwoord terwijl ik post, maar hoe houd jouw query/ontwerp er rekening mee dat verschillende users die views te weeg kunnen brengen in je laatste query zie ik dit niet terug daar lijk je alleen op datum te selecteren en zo dus iedereen die op dat moment op de site was en verbindingen aan het leggen was mee neemt ipv mensen die die pagina hadden bekeken.

Door voor XXX het weergegeven product te nemen. Voor het leggen van je verbindingen moet je dan wel even bijhouden welke producten allemaal al bekeken zijn. Dit zou je bijv. in je sessie kunnen doen, iedere keer dat de bezoeker een product bekijkt, voeg je deze toe aan een array in de sessie en voeg je de nieuwe verbindingen toe, dus als een gebruiker voor de N'de keer een product bekijkt, dan leg je verbindingen van product N naar product 0, 1, 2, ..., N-1.

Eventueel kun je ook nog de user-id bijhouden, zodat je daadwerkelijk op alleen andere gebruikers kunt selecteren. Maar dan moet je wel een user-id hebben natuurlijk, en waarschijnlijk wil je ook niet-ingelogde gebruikers meenemen.

[roy-t]

muksie schreef op zaterdag 13 juni 2009 @ 16:00:
[...]

Door voor XXX het weergegeven product te nemen. Voor het leggen van je verbindingen moet je dan wel even bijhouden welke producten allemaal al bekeken zijn. Dit zou je bijv. in je sessie kunnen doen, iedere keer dat de bezoeker een product bekijkt, voeg je deze toe aan een array in de sessie en voeg je de nieuwe verbindingen toe, dus als een gebruiker voor de N'de keer een product bekijkt, dan leg je verbindingen van product N naar product 0, 1, 2, ..., N-1.

Eventueel kun je ook nog de user-id bijhouden, zodat je daadwerkelijk op alleen andere gebruikers kunt selecteren. Maar dan moet je wel een user-id hebben natuurlijk, en waarschijnlijk wil je ook niet-ingelogde gebruikers meenemen.

Ah, ik ga morgen even uitproberen of ik dit lokaal goed kan laten werken, heel erg bedankt voor de tips, ook zal ik even andere opties uitproberen en kijken welke ik de beste resultaten vind geven (moeilijk te meten).