I think the two biggest problems with Moat are its dead-ness and its lack of reliability. Moat being a dead card when drawn with another terminal means that it's usually not very good early in the game when you have few to no villages in your deck and literally almost every other terminal is better (usually this is the attack that Moat is supposed to be defending against). This also means that Moat is not a great defense against junking attacks much of the time because these attacks are most powerful near the beginning of the game and become weaker near the end of the game because the junk runs out.
There's also no way to ensure that Moat will even adequately defend against an opponent attack because it has to appear in the initial 5-card hand for its reaction to work. +card durations do not alleviate this at all because they don't draw in the clean-up phase. Cards such as Courtyard, Mandarin, or Count can top-deck Moat from hand to hand, but of course these are all terminal. Top-of-deck sifters such as Cartographer, Apothecary, or Scout can increase the likelihood of Moat being in the next hand. There's also Scheme, but that requires having played the Moat.
So when Moat is good, it's typically when these two problems get addressed. If your opponent is attacking you every turn with an attack that can do something every turn, and if the attack significantly decreases the likelihood of you reaching some goal either immediately or in the future, then Moat becomes more useful. Additionally, if there is a way to accommodate multiple Moats in your deck with low opportunity cost, or if one of the above interactions can be executed with low opportunity cost, then Moat becomes more useful.
The game that I posted above satisfied both criteria. Ambassador continues to attack throughout the entire game, and the attack threatens a player's ability to control the endgame. I could gain multiple Moats because its action component increased the reliability of my deck and it's the best purchase in low-economy hands where I used Ambassador to return Coppers. However, gaining the Moat early would have been a very poor decision because even though it would defend against the occasional Ambassador, it would also be a completely dead card otherwise.