In general, I'd like to first note that I'm not personally interested in paid modding, since this is usually one of the first things people ask about -- or worse, assume.
That out of the way, I believe that the option existing could have significant advantages. In particular, beyond the often-discussed ability for people to dedicate more time to modding, there are some potential secondary benefits which I have only rarely seen mentioned.
For one, there is the fact -- and you can certainly discuss whether this is a good thing and what it says about society, but it is a fact -- that the general public takes a paid pursuit more seriously and treats it with more respect than a hobby. Thus, modders could go from "people who spend way too much time at the computer" to "people who use their skills to create something valuable to others" in the eyes of those unfamiliar with modding, and this can certainly be a huge boon to motivation.
Secondly, from the perspective of game developers, supporting modding could go from a cost with nebulous benefits (from their perspective, I think the benefits are already clear) to something with a well-defined payoff. This could result in companies which have previously refrained from supporting mods, or even actively sought to prevent modding of their games, becoming more supportive of the practice.
That said, there were definitely some issues with how paid mods were introduced for Skyrim. First of all, it's probably not a good idea to make such a step at a point in time where the modding community is already well-established for a game. And then of course there is the issue of the relative revenue split. I'm no economist, and people more familiar with the situation have actually claimed convincingly that the conditions are favourable compared to e.g. licensing an existing IP, but 25% as a number just feels wrong. However, even with these issues in the system I was still more disappointed with the reactions from some members of the gaming community, who resorted to insulting or even threatening modders producing paid content, than in the initiative itself.
Anyway, if you ask me now how things *should* work, I would boil it down to one essential rule: both paid mods and free mods *must* be able to coexist, and they *must* be given the same space, access to features and care without discrimination. As long as this rule is held up I support adding new options for modders, and as soon as it is violated I'll be throwing torches with the mob.