It all depends on the hacker and the person playing.
We make this mistake of macrocosmically calling anything interactive, entertaining and electronic a "video game"; and in the microcosm of ROMhacking Pokémon, I see too often the mistake of comparing completely different hacks. FireRed Omega, ShinyGold, AshGrey, FloraSky and Snakewood are all very different hacks working toward different ends, to give a decent sampling of the spectrum. You can't say that AshGrey sucks because it doesn't have the gripping atmosphere of FloraSky, that FireRed Omega sucks because it lacks the plot of AshGrey, FloraSky or Snakewood, Snakewood sucks because of the lack of gyms, FireRed Omega sucks because it's not "different" enough... Each is a good hack, for different reasons. I have enjoyed all immensely.
I think as a general rule, if you change the plot, it should not resemble a canned GameFreak-style "collect the eight badges, fill the dex and defeat this team" thing, especially if you've just, say, swapped some names and changed Kyogre and Groudon to sand and hail beasts or something. If it's a very original or offbeat plot, I think a certain amount of campiness is actually beneficial; Of the above, I think Snakewood is the best example. It's ridiculous and farcical, but has a decent plot and is fun to play. Another thing is that people like a certain amount of fanservice, but sticking a trainer with Red's sprite named Ash with a Pikachu at level 100 in the game is just cheesy and will often illicit little more than a sigh. Writing an original plot or original facet for a canon character (without making it too cliché fanfiction "meeting my Mary-Sue") is a good tactic, though.
On a final note, I think people are too afraid of Fakémon. I don't know if it's the spriting (some of the Quartz designs were great, but fell short in spriting, for example) or what, but people seem to both detest making and playing with fakémon; Personally, I love them.