Despite not setting the world ablaze, I have a lot of fond memories with the N64, Gamecube, and Wii U.

N64: Super Mario 64, The Legend of Zelda: Ocarina of Time, The Legend of Zelda: Majora's Mask, Goldeneye 007, Super Smash Bros., Wave Race 64.

Gamecube: Super Smash Bros. Melee, Pokémon Colosseum, Pokémon XD Gale of Darkness, Mario Kart Double Dash, The Legend of Zelda: The Wind Waker, Geist.

Wii U: Super Mario Maker, Super Smash Bros. for Wii U, Mario Kart 8, Splatoon, Donkey Kong Country Tropical Freeze, Super Mario 3D World.

Gamecube. Amazing titles and good variety but never really gained market share.

Nokia N-Gage

That thing was a friggin' beast. Not only did it have great exclusives like Pathway to Glory. It also hat games like Tomb Raider and Tony Hawk which I believe you couldn't play anywhere else on mobile at the time. On top of that, it had a great OS with a fuckton of options. Many things that people considered to be revolutionary when Apple came around with the iPhone were already possible on the N-Gage. Like installing Apps and all that stuff. I also really liked the idea of having my gaming console and my cellphone in one device.

It didn't deserve to fail so miserably.

Sega Mega-CD, N64, Gamecube, Dreamcast, 3DO, Xbox Series X

The first 5 each had their moments with some great games that on release couldn't be played anywhere else, or to the same quality.
The last is simply an accomplished console that has no real weak spots in terms of hardware and what it is/delivers, just let down by not having the exciting software releases that drove prior generations (with the same affliction for the PS5, but contrary to the Series X it doesn't get *everything* else right.. namely its visual appearance lets it down. And of course contrary to the Series X it *is* a successful console, its lack of inspiring new unique/generational games having not retarded its performance as measurably as for the Series consoles).

And Worst "unsuccessful" consoles: Atari Jaguar, Apple Pippin, Philips CD-i, Amiga CD32, Nintendo Virtua-Boy, Google Stadia

Probably depends on where one lives, as for Europe this could already be true for the Sega Master System to some degree. After Burner, Outrun, Fantasy Zone, Space Harrier, Wonder Boy...

Edit: As for the best unsuccessful consoles, I'd say Wii U, Gamecube and Dreamcast

Zodiac Tapwave had Tonk Hawk 4. I'm assuming you are not counting the GBA version of a Tony Hawk game (never played myself) But sorry no. That thing deserved the grave it got.  Supposedly the design was based on GOATSE. I am not kidding from a frustrated designer as management kept demanding more and more buttons. Again supposedly.  Nokia was clueless about games and kept bashing Game Boy in hype with the old Nintendo is kiddy nonsense.  The screen was awful. Side talking. You have to remove the battery to insert a game. Also, smartphones date back to the early 90s.

PS Vita. The Xbox One wasn't too bad either.

I think it has to be Dreamcast, so many great games in such a short amount of time, shame it didn't use DVDs, it would've held its own against the PS2. Only the Switch managed a comparable quality library to the Dreamcast in a couple short years!

PS Vita... killed by Sony.