It could be a good thing or a bad thing, heavily dependent on how they handle it. First of all, they do say that nothing big will happen until they release these tools to make filtering out content easier, and since it's Valve, it could be a while. I wouldn't expect anything until next year at the least.
If and when that happens, I expect we'll know soon enough whether they really meant what they said, about allowing anything that wasn't illegal or trolling. If they allow anything with incest or loli, then I'll be surprised. If the tools they have don't work properly or they don't go far enough, there will be backlash by lazy parents who refuse to properly monitor their children's activities. We have tools to ensure that kids aren't playing games they aren't supposed to, but parents used to rarely take advantage of them and would get mad when they saw their kid playing a game they bought and were notified of.
It used to be worse, or maybe it's that parents are more lenient. If the system works properly, I guarantee that some parent will ignore any and all warnings about content, or worse, let their kid set up the profile, will put in their CC info and save it and then be pissed about how Steam let their kids download some porn game.
A lot of gamers will probably say that these parents are idiots, but I bet a lot of gamers will also be on the parent's side and say that steam shouldn't even be selling it in the first place, even behind a robust age gate with a perfect filtering system. But on the other hand I think over time the controversy will fade out. Or not. Maybe it could be like that time that Shower with your Dad Simulator got some controversy years after it was released. But that controversy also died down just as quickly as it began. If Valve actually follows through without excuses and stands their ground. Sure there will be grumbles. People will whine and throw tantrums, but like any child, once you get it into their head that they won't win anymore just by crying, they'll stop. That's a lesson that is far overdue for some of these people.