The biggest problem with it was simply that it primarily ended up in the hands of Western consumers and dev houses during the height of the FMV craze, the PC Engine CD did much better comparatively speaking because most of the things on it were actual games and it had (within Japan) wide support from nearly all major software development houses.
The main mistake with the Sega CD was adding a bunch of expensive new hardware to it. It gave the Genesis a second, faster Motorola 68k and a special ASIC to handle sprite scaling and rotation effects (to compete with the SNES), but these inclusions effectively doubled the price of the add-on and were seldom used by developers. The Turbo-Grafx CD, on the other hand, just added a CD-ROM drive to the Turbo and cost half as much. The Turbo CD was also much more necessary since the console used HuCards that were much more limited in space than the cartridge ROMs used by the Genesis.
That said, the Sega CD was reasonably successful for what it was, and I don't think it contributed much to Sega's downfall (if anything, it helped Sega foster an impression with customers of being on the cutting edge of things back then). Far more egregious were the 32X, since it confused customers about the company's direction after the Genesis and was abandoned so quickly, and the Saturn's architecture being so much more expensive and complicated than the PlayStation, especially for 3D games. The cost let Sony undercut them because they knew Sega couldn't match them without losing money on each console, and the complexity was such a hurdle that even Sega's first-party developers had trouble figuring it out (they had to release "do-over" ports of Virtua Fighter and Daytona USA because the first versions were so rushed and buggy). Sega of America also pulled the plug on the Saturn way too early (because of Bernie Stolar's infamous interview where he said the console wasn't the company's future, which caused third parties to immediately cancel their releases for it), which left the company with basically no presence in their biggest market for about two years.
