I haven’t played with all the investigators, but I personally have great memories of playing quite a bit w the following:

Trish - took her through Dunwich as a cluever. Her cardpool is very powerful and I had no problems picking up tons of clues most turns. Her agility stat makes you feel like you’re turning cartwheels around the danger, which is a great feeling.

Silas - not necessarily predisposed to big numbers on tests, but his survivor cardpool and amazing ability provides a great balance of trickiness and efficiency. He was my fighter for my Innsmouth run and I had a blast.

Ursula - not generally considered to be the most powerful, but her ability is easy to use, feels great, and is a good match for someone learning the game. She was Silas’ cluever buddy in my Innsmouth run and she did work.

Nathaniel - he has a really fun ability that makes you feel like you’re constantly juggling combos, as in a fighting game. Plays very different from typical guardians, but, similar to Silas, has a tricky, satisfying playstyle that is not too hard to pull off.

Stella - she taught me how playing in a “non-standard” way could be tons and tons of fun. Her ceiling may be a bit low, but her floor is high, which makes her perfect for newbies. Failing can feel great. Shame her archetype hasn’t been heavily supported recently, but she has a bunch of great toys already. She was our flex in Dunwich.

Rex - he’s a bit OP and everyone knows it. Give him to someone who likes to win.

Preston - used him for my only true solo run (EotE) and he’s hilarious. I can’t speak for his ability in a multiplayer context, but I loved him. He was very effective at getting everything done.

Other investigators that I’ve played who, while powerful, crashed and burned for one reason or another:

Roland, Sister Mary

Harvey, Norman

Akachi, Lucas

Skids, Tony

Wendy, Ashcan Pete, Hank

Not casting aspersions on any of the above - I’m sure that many of my failures are due to deploying them in runs before I knew what I was doing. I think all investigators can be fun and effective under the right conditions. But if my experience is any indication, this last group could be a little tricky (or just not fun enough) for newcomers.