I might be a little biased as I can only speak to the experience of being in Vancouver, but I think it made a very fine host city. Our stadium isn't the biggest or the fanciest, but it benefits from having one of the most centralized locations amongst the host cities - along with Seattle and Atlanta - being one of the few to have it right in the core.
It's well set up with access to rapid transit, hotels, public spaces for events, and other tourist infrastructure in close proximity, which allows for a very "compact" experience (an important consideration for a good visitor experience, IMO). And it definitely adds to the vibe when the fan marches and celebrations can weave through the city and spill out into the streets after games, rather than being isolated in far-flung arena islands.
https://ca.linkedin.com/company/bc-place
https://dailyhive.com/vancouver/bc-place-stadium-renaming-bids
https://www.instagram.com/reel/DZ_4nVLNUQR/
https://www.youtube.com/watch?v=cejcH1roaa0
In terms of broader benefit for the city though, I'm not convinced it did much. Downtown businesses benefited tremendously (and not a bad thing either, as they've been struggling a bit in recent years), but tourism as whole wasn't really any higher than usual. Vancouver's already a busy enough tourist destination, so any extra advertising wasn't really necessary. Still, these kinds of events can have certain intangibles.
Perhaps more importantly, this was the first time a World Cup has been hosted in Canada, and was by far Team Canada's best showing to date (first win, first knockout stage appearance, first knockout win). The combination of the two could be a big force to help grow the popularity of the game domestically.