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.