Installing mods can never be too easy imo.
The best implementation I've seen so far is in OpenTTD, it has a built-in system that's really convenient and simple. (At least for the end user, I've never looked at the creator side there)
If you want to play with mods in singleplayer or for your own server you can search/apply mods from within the game client, it supports collections you can easily switch between. When you load a savegame it'll automatically use whatever that game was started with without messing with your other settings. When you join a server it'll tell you exactly which mods you're missing for it and lets you download them all with a single click.
Easy to use, no technical knowledge or annoying manual steps required, no headaches with manually having to use the right combination for existing saves (Cough, steamworkshop, cough) or other headaches that usually come with modded content.
And if for whatever reason the content isn't on their main repository the game supports local content just fine, drop it in a folder and itll integrate with the regular system without any issues.