The problem with the Sega CD was that some developers just didn’t know what to do with it. They should have made the same types of games already available for the Genesis and Super Nintendo but with improvements based on the enhanced power and much greater (but slow) storage of the Sega CD. Platformers with more levels and improved graphics, much larger RPGs, compilations with multiple good games, etc. And a few developers did just that. However, too many developers felt that full motion video was the future and made things like graphic adventures only with FMV clips and stuff like this.
As an example of what not to do with the Sega CD, here is an ad for two “Make My Video” titles for the Sega CD. One featuring C + C Music Factory and the other featuring Kris Kross (ok, they used to be more famous). The idea is to piece together music and video clips and make your own video. As a tech demo it isn’t bad but for something with actual replayability (if “playability” even applies here) it’s pretty terrible.
The Sega CD didn’t have a huge library and too much of it consisted of crappy FMV games. In my opinion that (plus the large initial cost of the Sega CD) were why it didn’t do better commercially. I know I really looked forward to this CD add-on but was really disappointed with what was available when it finally arrived.