Here’s the plan, so far:
Load the customer’s collection.
Load the collection of games (and ratings) of the user. All users are gamers, but the person we’re building the recommendation for is the customer. We’ll build a Collections table with a row per game.
Per game, load the ratings.
For each game in the customer’s collection, get the gamers that also rated it. Row per gamer/rating.
Rank gamers that are like the customer.
Per ranking, load the gamer’s collections.
For gamers that are similar to the customer, load their collections. Games in their collections that are unknown to the customer are possible recommendations.
This is probably in the range of 10 to 200 games. Note that for some customers, the number of columns might be different. If a customer has a game with wishlist=1, then for that row the column “wishlistpriority” is added.
## # A tibble: 6 x 14
## gamer game game.id rating own
## <chr> <chr> <int> <int> <lgl>
## 1 mikec 1830: The Game of Railroads and Robber Barons 421 8 FALSE
## 2 mikec 1st & Goal 95105 4 TRUE
## 3 mikec 7 Wonders 68448 7 TRUE
## 4 mikec Acquire 5 6 TRUE
## 5 mikec Ad Astra 38343 NA FALSE
## 6 mikec Advanced Heroquest 1758 NA TRUE
## # ... with 9 more variables: prevowned <lgl>, fortrade <lgl>, want <lgl>,
## # wanttoplay <lgl>, wanttobuy <lgl>, wishlist <lgl>, preordered <lgl>,
## # lastmodified <date>, wishlistpriority <int>For each game in the collection, we’ll collect the data and ratings. The data are things like number of players and year published. Ratings is a list of potentially thousands of ratings between 1 and 10.
Sizing the data: Each game could have 10,000 or more ratings. The API delivers 100 ratings at a time, so it could take 100 queries to load all for a game. If the customer has 100 games, that’s 10,000 queries!
I’ve written a simple cache system so that subsequent reads come from local storage.
## # A tibble: 6 x 14
## gamer game game.id rating own
## <chr> <chr> <int> <int> <lgl>
## 1 mikec 1830: The Game of Railroads and Robber Barons 421 8 FALSE
## 2 mikec 1st & Goal 95105 4 TRUE
## 3 mikec 7 Wonders 68448 7 TRUE
## 4 mikec Acquire 5 6 TRUE
## 5 mikec Ad Astra 38343 NA FALSE
## 6 mikec Advanced Heroquest 1758 NA TRUE
## # ... with 9 more variables: prevowned <lgl>, fortrade <lgl>, want <lgl>,
## # wanttoplay <lgl>, wanttobuy <lgl>, wishlist <lgl>, preordered <lgl>,
## # lastmodified <date>, wishlistpriority <int>## [1] "1 of 160, loading 3503 comments for 421, 1830: The Game of Railroads and Robber Barons"
## [1] "2 of 160, loading 1256 comments for 95105, 1st & Goal"
## [1] "3 of 160, loading 53975 comments for 68448, 7 Wonders"
## [1] "4 of 160, loading 15786 comments for 5, Acquire"
## [1] "5 of 160, loading 2606 comments for 38343, Ad Astra"
## [1] "6 of 160, loading 1020 comments for 1758, Advanced Heroquest"
## [1] "7 of 160, loading 709 comments for 140865, Agent Hunter"
## [1] "8 of 160, loading 5549 comments for 90419, Airlines Europe"
## [1] "9 of 160, loading 1841 comments for 154458, Akrotiri"
## [1] "10 of 160, loading 11583 comments for 48726, Alien Frontiers"
## [1] "11 of 160, loading 647 comments for 1770, Aliens"
## [1] "12 of 160, loading 5301 comments for 110277, Among the Stars"
## [1] "13 of 160, loading 1191 comments for 153999, ...and then, we held hands."
## [1] "14 of 160, loading 3351 comments for 39339, Android"
## [1] "15 of 160, loading 4337 comments for 19600, Antike"
## [1] "16 of 160, loading 30727 comments for 15987, Arkham Horror"
## [1] "17 of 160, loading 777 comments for 92120, Atlantis Rising"
## [1] "18 of 160, loading 4813 comments for 39351, Automobile"
## [1] "19 of 160, loading 1 comments for 225766, Awful Fantasy Adventures"
## [1] "20 of 160, loading 67 comments for 6705, Batman: Gotham City Mystery"
## [1] "21 of 160, loading 675 comments for 130911, Batman: Gotham City Strategy Game"
## [1] "22 of 160, loading 9 comments for 9544, Batman: The Animated Series – 3D Board Game"
## [1] "23 of 160, loading 26063 comments for 37111, Battlestar Galactica"
## [1] "24 of 160, loading 905 comments for 12350, Battlestations"
## [1] "25 of 160, loading 12 comments for 196712, Battlestations: Second Edition"
## [1] "26 of 160, loading 261 comments for 40, Borderlands"
## [1] "27 of 160, loading 8179 comments for 131835, Boss Monster: The Dungeon Building Card Game"
## [1] "28 of 160, loading 1542 comments for 805, Buffy the Vampire Slayer: The Game"
## [1] "29 of 160, loading 465 comments for 30057, Burger Joint"
## [1] "30 of 160, loading 798 comments for 159503, The Captain Is Dead"
## [1] "31 of 160, loading 5062 comments for 171131, Captain Sonar"
## [1] "32 of 160, loading 70229 comments for 13, Catan"
## [1] "33 of 160, loading 13195 comments for 926, Catan: Cities & Knights"
## [1] "34 of 160, loading 3055 comments for 4101, Catan: Cities & Knights – 5-6 Player Extension"
## [1] "35 of 160, loading 22285 comments for 18602, Caylus"
## [1] "36 of 160, loading 38690 comments for 478, Citadels"
## [1] "37 of 160, loading 5310 comments for 201808, Clank!: A Deck-Building Adventure"
## [1] "38 of 160, loading 32577 comments for 178900, Codenames"
## [1] "39 of 160, loading 3384 comments for 98351, Core Worlds"
## [1] "40 of 160, loading 880 comments for 40529, Cosmic Encounter"
## [1] "41 of 160, loading 22021 comments for 131357, Coup"
## [1] "42 of 160, loading 961 comments for 147, Crude: The Oil Game"
## [1] "43 of 160, loading 222 comments for 128137, Damage Report"
## [1] "44 of 160, loading 130 comments for 99130, Dawn of the Zeds (First edition)"
## [1] "45 of 160, loading 24377 comments for 150376, Dead of Winter: A Crossroads Game"
## [1] "46 of 160, loading 3077 comments for 169654, Deep Sea Adventure"
## [1] "47 of 160, loading 10108 comments for 483, Diplomacy"
## [1] "48 of 160, loading 209 comments for 2949, Dune: Spice Harvest"
## [1] "49 of 160, loading 4109 comments for 102548, Dungeon Fighter"
## [1] "50 of 160, loading 20921 comments for 72125, Eclipse"
## [1] "51 of 160, loading 15929 comments for 100423, Elder Sign"
## [1] "52 of 160, loading 605 comments for 98347, Empires of the Void"
## [1] "53 of 160, loading 58 comments for 183649, Escape the Nightmare"
## [1] "54 of 160, loading 11906 comments for 113294, Escape: The Curse of the Temple"
## [1] "55 of 160, loading 4164 comments for 1159, Evo"
## [1] "56 of 160, loading 7578 comments for 155703, Evolution"
## [1] "57 of 160, loading 1197 comments for 122842, Exodus: Proxima Centauri"
## [1] "58 of 160, loading 2 comments for 220792, The Expanse Board Game"
## [1] "59 of 160, loading 414 comments for 176371, Explorers of the North Sea"
## [1] "60 of 160, loading 1219 comments for 135779, A Fake Artist Goes to New York"
## [1] "61 of 160, loading 7242 comments for 138161, Firefly: The Game"
## [1] "62 of 160, loading 14290 comments for 100901, Flash Point: Fire Rescue"
## [1] "63 of 160, loading 5571 comments for 175914, Food Chain Magnate"
## [1] "64 of 160, loading 4687 comments for 121, Frank Herbert's Dune"
## [1] "65 of 160, loading 21375 comments for 31481, Galaxy Trucker"
## [1] "66 of 160, loading 167 comments for 41032, Galaxy's Edge"
## [1] "67 of 160, loading 14594 comments for 37046, Ghost Stories"
## [1] "68 of 160, loading 898 comments for 110524, Goblins, Inc."
## [1] "69 of 160, loading 6331 comments for 171668, The Grizzled"
## [1] "70 of 160, loading 1436 comments for 38765, Ground Floor"
## [1] "71 of 160, loading 25148 comments for 98778, Hanabi"
## [1] "72 of 160, loading 76 comments for 1307, Haunted Castle"
## [1] "73 of 160, loading 19532 comments for 35677, Le Havre"
## [1] "74 of 160, loading 482 comments for 92666, Hegemonic"
## [1] "75 of 160, loading 8494 comments for 699, HeroQuest"
## [1] "76 of 160, loading 142 comments for 62809, Jump Gate"
## [1] "77 of 160, loading 296 comments for 137900, Jupiter Rescue"
## [1] "78 of 160, loading 5432 comments for 257, Kill Doctor Lucky"
## [1] "79 of 160, loading 2107 comments for 55690, Kingdom Death: Monster"
## [1] "80 of 160, loading 1359 comments for 115105, Kittens in a Blender"
## [1] "81 of 160, loading 888 comments for 173064, Leaving Earth"
## [1] "82 of 160, loading 8314 comments for 127398, Legends of Andor"
## [1] "83 of 160, loading 1496 comments for 137649, Level 7 [Omega Protocol]"
## [1] "84 of 160, loading 9251 comments for 125618, Libertalia"
## [1] "85 of 160, loading 3049 comments for 249, Lifeboats"
## [1] "86 of 160, loading 12081 comments for 823, Lord of the Rings"
## [1] "87 of 160, loading 130 comments for 284, Lords of the Sierra Madre (second edition)"
## [1] "88 of 160, loading 30339 comments for 110327, Lords of Waterdeep"
## [1] "89 of 160, loading 38162 comments for 129622, Love Letter"
## [1] "90 of 160, loading 3759 comments for 199, Manhattan"
## [1] "91 of 160, loading 2109 comments for 232, Méditerranée"
## [1] "92 of 160, loading 19977 comments for 10630, Memoir '44"
## [1] "93 of 160, loading 2445 comments for 230, Merchant of Venus"
## [1] "94 of 160, loading 9927 comments for 25292, Merchants & Marauders"
## [1] "95 of 160, loading 1446 comments for 531, Merchants of Amsterdam"
## [1] "96 of 160, loading 11312 comments for 124708, Mice and Mystics"
## [1] "97 of 160, loading 4605 comments for 176920, Mission: Red Planet (Second Edition)"
## [1] "98 of 160, loading 11697 comments for 118, Modern Art"
## [1] "99 of 160, loading 13554 comments for 181304, Mysterium"
## [1] "100 of 160, loading 3475 comments for 194607, Mystic Vale"
## [1] "101 of 160, loading 10160 comments for 21241, Neuroshima Hex!"
## [1] "102 of 160, loading 1762 comments for 5406, New England"
## [1] "103 of 160, loading 6395 comments for 1234, Once Upon a Time: The Storytelling Card Game"
## [1] "104 of 160, loading 1500 comments for 179275, One Deck Dungeon"
## [1] "105 of 160, loading 4183 comments for 71836, Onirim"
## [1] "106 of 160, loading 65360 comments for 30549, Pandemic"
## [1] "107 of 160, loading 19356 comments for 161936, Pandemic Legacy: Season 1"
## [1] "108 of 160, loading 491 comments for 124047, Pirate Dice: Voyage on the Rolling Seas"
## [1] "109 of 160, loading 4107 comments for 180974, Potion Explosion"
## [1] "110 of 160, loading 1217 comments for 87890, Prêt-à-Porter"
## [1] "111 of 160, loading 35320 comments for 28143, Race for the Galaxy"
## [1] "112 of 160, loading 71 comments for 114, Ransom"
## [1] "113 of 160, loading 6926 comments for 36946, Red November"
## [1] "114 of 160, loading 3461 comments for 1513, The Republic of Rome"
## [1] "115 of 160, loading 16806 comments for 128882, The Resistance: Avalon"
## [1] "116 of 160, loading 62 comments for 42789, Restaurant Row"
## [1] "117 of 160, loading 535 comments for 91189, Ristorante Italia"
## [1] "118 of 160, loading 19794 comments for 18, RoboRally"
## [1] "119 of 160, loading 1306 comments for 1569, RoboRally: Armed and Dangerous"
## [1] "120 of 160, loading 15413 comments for 132531, Roll for the Galaxy"
## [1] "121 of 160, loading 11619 comments for 102652, Sentinels of the Multiverse"
## [1] "122 of 160, loading 11595 comments for 2807, The Settlers of Catan: 5-6 Player Expansion"
## [1] "123 of 160, loading 20737 comments for 15062, Shadows over Camelot"
## [1] "124 of 160, loading 2465 comments for 35704, Shadows over Camelot: Merlin's Company"
## [1] "125 of 160, loading 130 comments for 178450, Side Quest"
## [1] "126 of 160, loading 389 comments for 3895, Silent Death"
## [1] "127 of 160, loading 6083 comments for 92415, Skull"
## [1] "128 of 160, loading 44714 comments for 40692, Small World"
## [1] "129 of 160, loading 12100 comments for 38453, Space Alert"
## [1] "130 of 160, loading 889 comments for 61492, Space Alert: The New Frontier"
## [1] "131 of 160, loading 2780 comments for 123096, Space Cadets"
## [1] "132 of 160, loading 300 comments for 66781, Space Infantry"
## [1] "133 of 160, loading 3697 comments for 155624, Specter Ops"
## [1] "134 of 160, loading 1013 comments for 1194, Speed Circuit"
## [1] "135 of 160, loading 11194 comments for 166384, Spyfall"
## [1] "136 of 160, loading 3554 comments for 102104, Star Fluxx"
## [1] "137 of 160, loading 810 comments for 178613, Star Trek: Five-Year Mission"
## [1] "138 of 160, loading 4441 comments for 1897, Starfarers of Catan"
## [1] "139 of 160, loading 844 comments for 4100, Starfarers of Catan: 5-6 Player Expansion"
## [1] "140 of 160, loading 31594 comments for 34635, Stone Age"
## [1] "141 of 160, loading 11576 comments for 146508, T.I.M.E Stories"
## [1] "142 of 160, loading 12424 comments for 167791, Terraforming Mars"
## [1] "143 of 160, loading 15631 comments for 25613, Through the Ages: A Story of Civilization"
## [1] "144 of 160, loading 9107 comments for 53953, Thunderstone"
## [1] "145 of 160, loading 237 comments for 311, Time Agent"
## [1] "146 of 160, loading 982 comments for 128664, Timeline"
## [1] "147 of 160, loading 5329 comments for 85256, Timeline: Inventions"
## [1] "148 of 160, loading 6778 comments for 163967, Tiny Epic Galaxies"
## [1] "149 of 160, loading 617 comments for 135243, Triassic Terror"
## [1] "150 of 160, loading 11760 comments for 73439, Troyes"
## [1] "151 of 160, loading 15830 comments for 16992, Tsuro"
## [1] "152 of 160, loading 1804 comments for 170416, Vast: The Crystal Caverns"
## [1] "153 of 160, loading 1004 comments for 109125, Wallenstein (second edition)"
## [1] "154 of 160, loading 2028 comments for 1634, Warhammer Quest"
## [1] "155 of 160, loading 3212 comments for 181521, Warhammer Quest: The Adventure Card Game"
## [1] "156 of 160, loading 7287 comments for 25821, The Werewolves of Miller's Hollow"
## [1] "157 of 160, loading 934 comments for 71655, Wok Star"
## [1] "158 of 160, loading 3652 comments for 82222, Xia: Legends of a Drift System"
## [1] "159 of 160, loading 1398 comments for 144826, Zombie 15'"
## [1] "160 of 160, loading 12847 comments for 2471, Zombies!!!"## # A tibble: 6 x 9
## game yearpublished minplayers maxplayers
## <chr> <int> <int> <int>
## 1 1830: Railways & Robber Barons 1986 2 7
## 2 1st & Goal 2011 2 4
## 3 7 Wonders 2010 2 7
## 4 Acquire 1964 3 6
## 5 Ad Astra 2009 3 5
## 6 Advanced Heroquest 1989 1 5
## # ... with 5 more variables: minage <int>, minplaytime <int>,
## # maxplaytime <int>, comments <int>, description <chr>## # A tibble: 6 x 4
## gamer game game.id rating
## <chr> <chr> <int> <int>
## 1 HamHam2000 1830: Railways & Robber Barons 421 10
## 2 GRYPHUS 1830: Railways & Robber Barons 421 10
## 3 chamel 1830: Railways & Robber Barons 421 10
## 4 wkilgore 1830: Railways & Robber Barons 421 10
## 5 jhammer76 1830: Railways & Robber Barons 421 10
## 6 Pielso 1830: Railways & Robber Barons 421 10The general formula is:
* For the games we have rated, find gamers who have rated them similarly
* The more games they agree with us on, the better
We should make a unique list of gamer IDs that have rated games, then load their collections.