It works okay, but it's a little clunky. Discoverability is weird. It's concept of "recently trending" etc produces some dubious recommendations.

Actually installing mods is kind of okay, but it doesn't handle pre-requisites very well. It'll tell you that you're missing pre-reqs, but it doesn't offer to install them.

It's a positive feedback issue, where because games don't tend to use workshop, it doesn't get much love from valve, and therefore games avoid it.

I don't know how much workshop allows developers to do curation either, so perhaps games would rather partner with a platform they can better influence mod curation.

It's definitely preferable as a user compared to the worst-of-all-worlds that is Stardew Valley modding. There you have a combination of "Here, download this exe, it's fine we promise" (SMAPI), and nexus mods for the discoverability / install / updates of the mods themselves.