I'd say Goodra is quite good in-game. A wide movepool filled with great coverage, great special bulk and resisting all four basic elemental types as per being a Dragon-type, and good offensive stats on both sides combined with solid Speed make for a really good and splashable in-game Pokemon. In XY it's a great option for a team and is one of the most splashable and usable in-game pseudo-legendaries for an in-game playthrough in its debut game after Gible in DPP. It's not bad in subsequent games either and is a solid choice for a Dragon-type for in-game purposes. Its low competitive viability largely stems from the nature of competitive demanding that the best Pokemon there have to be exceptional or unique in some way, while Goodra suffers from jack-of-all-trades, master-of-none syndrome. But in-game it's an incredibly fun and splashable Pokemon.
That said, yeah, I do think it should've been part Water-type as its flavor just screams "Water-type", considering it learns Bubble/Water Gun, Rain Dance, Muddy Water, and Aqua Tail naturally and learns moves like Surf, Hydro Pump, and Scald (in SV) naturally. Or part Poison, which is reinforced by later gens giving the line Acid Spray and in SV, being one of the few non Poison-types that learns Toxic.
As for its stat spread, I feel Goodra was designed as something of an anti-weather mon, considering weather was a powerful force in Gen 5 VGC and is still prominent in later gens VGC/Doubles metas, and everything about how it's designed feels like a way to ensure there was no return of Gen 5 weather. It has Hydration to cure status in rain, not to mention it can sponge rain boosted Water moves and Thunders very comfortably (while Hydration can remove any Thunder paralysis), Sap Sipper which allows it to absorb Solar Beam against sun abusers (and Fire resistance+high Special bulk means it very comfortably sponges sun boosted Fire attacks), and Gooey allows it to slow sweepers down. And then it has a wide offensive movepool to be able to combat weather abusers of all kinds: Thunder to strike down rain sweepers, Fire Blast to take down Chlorophyll Grass sweepers, Earthquake to hit Fire mons and so on and so forth. It's perfectly capable of doing its job, it's just...not a job that's really needed nowadays, which is reflected in its low tier placements.
In XY VGC from what I've heard it had pretty good use at the time, especially since it also learns Feint which can break Protect. I don't think it's seen much use since but it did have its place in its debut era for its ability to blanket check Greninja, Ludicolo (rain), Mega Charizard Y, and a lot more. I think it saw use in SM VGC for much the same reasons.
Hisuian Goodra is definitely better overall, since Steel/Dragon is such an amazing typing. That said, I appreciate that SV has given base Goodra some tools to stand out like Toxic and Scald, things Hisuian Goodra doesn't have, on a paper level, even if that doesn't help it that much in the competitive scene. I do think both Goodras would greatly benefit from having some form of recovery, however.
Petition for base Goodra to get a Mega in Legends: Z-A lgi