Generally speaking, the implication of "RPG" is that it is story and character based. It's a Role Playing Game. A game where you are playing a role. So, the definition of this genre basically determines what sorts of players will be flocking to it. This is important, so keep it in mind.
So, how good does a story actually have to be?
Much as I love a good story and wonderful characters... the answer is "not much". Tastes are going to play pretty heavily in what stories will do well and the quality of those stories.
Personally, my tastes run the gamut, and are going to be heavily influenced by things I can relate to, or things that are "well told". I also have a soft spot for "new twists on old things" or "unique concepts".
I liked the story of Final Fantasy Mystic Quest (much maligned as that game is). I liked the story of Final Fantasy X. I liked the story of Secret of Mana. I liked the story of Deus Ex: Human Revolution (the first of the reboot, I think?). All easily "low hanging fruit" or decently maligned story experiences by the masses. But, they kept me engaged in the story.
Meanwhile, I didn't like the story of Skyrim. I didn't like the story of Final Fantasy XII. I didn't like the story of Final Fantasy XV. I didn't like the story of Rune Factory IV. The premise behind "Bravely Default" means I never gave the game a shot, because it didn't sound interesting.
On and on.
These would be catered to my particular tastes in what a story is and what is on offer. I deeply enjoyed the story of Pokemon Red and Blue, simplistic as it is. I burned with hatred through every single moment in Pokemon Sun/Moon and Sword/Shield at their stories.
So, that begs the question... do you NEED a good story? Not necessarily. Much as I appreciate well-written stories and amazing characters, there are games where I never needed them to enjoy my time with the experience. In fact, I think there are even a few RPG's I've played where story was basically non-existent and I enjoyed my time with them.
I have fond memories of "Dragon Quest Monsters" and its story (which is basically non-existent as the whole game is basically a better version of Pokemon filled with fanservice for Dragon Quest games). I don't have any fondness for Dragon Quest 1-3 and found their stories to just be boring. I have found a deep enjoyment in the story of Dragon Quest Builders 2, but couldn't get into Dragon Quest XI (or was it 9? Lord, I don't even know which one of them I played!).
Do you need a good story?
Sometimes, yes. Sometimes, no. Your story needs a hook. Just like any good song. It needs to remain consistent on that hook. That hook needs to be executed well. It can't overstay it's welcome. It can't be too frequent. It can't be too infrequent. You need to master Pacing.
If you can't write a good story, you can still create a good story. You can do so with game mechanics and level design just as well as with words and characters (I suggest playing Metroid 2 and Super Metroid for the absolute BEST WAYS to tell a story with mechanics and level design. Both of those games are MASTERS at the craft and they aren't even RPG's!).
There are hundreds of ways to tell a story. Not all of them have to be done with words and choices and characters. Find the way you excel at telling the story, and then just do that.