It always seemed odd that the ambassador class had such a short and undistinguished service life. It seems like the longevity of the excelsior class and the introduction of the Nebula class left the ambassador class without a role to play.
I'm basing my observation on the fact that when ever you need a less than galaxy class vessel for a segment in the show you either saw a nebula class or excelsior class. Unless I am wrong the only time they use the ambassador model on the show was in the Tasha Year episode with the Ec . My memory COULD be flawed. BTW I am not taking any reference in written media just on screen visuals. I always liked the looks of the ambassador class EXCEPT for the blunt aspects of the nacelle mounts...
Perhaps we could describe the Ambassador class as akin to the nuclear aircraft carrier enterprise. It was a traditional ship that spanned the gulf between conventional aircraft carriers and the Nuclear powered Nimitz class. Many of the design flaws (including the 8 undersized submarine class generators) were corrected before the Nimitz slipped off the docks. Perhaps these theoretical flaws in the Ambassador class were answered in the comparable sized Nebula class and the much larger Galaxy class vessels
Definitely agree that the Ambassador-class should have been more prominently featured after it was introduced in "Yesterday's Enterprise" - certainly should have made appearances during the Dominion War (or more, anyway - not sure I recall it ever appearing in any of the fleet shots). Probably its absence was due in part to the studio model being relatively small (at the time only broadcast TV resolution was necessary) and no high-resolution CGI model was ever made, making it unsuitable for the sort of close-in beauty shots as the Enterprise-D and -E got.
The Ambassador also suffered from a kind of "morphing" design, intended to be an intermediate step between the established Excelsior-class and Galaxy-class, rather than being designed independently as an advanced (for early 24th century) Starfleet explorer. Excelsior was such a popular design because it looked like the design team had taken the original Constitution and redesigned it from scratch to make it more advanced. Sovereign, Defiant, Akira, Intrepid and Vesta are successful for similar reasons, each looks to be designed for a specific missions with appropriate features and innovations. Ambassador had to be "devolved" from Galaxy, and was a lesser design because of it.
I agree I always liked the design even if it was a intermediary design stage. With generous retrofitting it could have been a very good combat-capable support unit for say science or maybe a triage chip before the dedicated hospital vessels actually arrive. On a gaming front I'm glad you can get one of these in STO for free by completing an early mission and yet are able to arm it to spec the same way by doing another campaign mission. It's about the only mid-early game vessel I kept.