I think the Creation Club could have worked. What it needs are some properly cureated mods that offer worth playing additions to the game that justify the price by Bethesda making sure that it's good, has no bugs and maximum compability.
You'd still have free mods, you'd have worthwhile offical additions, good modders could get themself a job, and there would probably more content in general.
But they released the CC in probably the most idiotic way that is possible. Instead of making the first mods examples of how quality content could look like they did exactly what people didn't want from the CC. I'm baffled how they could handled it with this much incompetenz.
So while I think paid "mods" could work fine, I think Bethesda just has no idea how to make them work and hopefully never ever try it another time.
fun fact: CC's mods are already stored on your disk. You only need to add some little script file to make them work and you can just manually "mod" that in. visit /r/ModPiracy for more info.