Well a SP mod would will have to have 4 things to be awesome. Great level design. I wouldn't care if the mod was about eating a snark for lunch, as long as it was a graphical masterpiece. I might mind. Depends on if getting pieces stuck in your teeth was implemented or not.
Plot is one of the most nessisary parts of making a great mod.
If you lack plot you lack players. I should HOPE that someone who took the time to make a Single-Player mod focused on the plot. Games like System Shock 2 have shown that a fantastic plot and heavy immersion can be one of the best experiences out there. Like in They Hunger. Scripted events are sweet.