It's gotta be about the Journey for me. Have a central ship and a core cast of characters and go from A to B, essentially what Yamato or Bebop is. You can do so much with a journey type story. Encountering phenomena, factions, technical problems, crew issues, firing really big kinetic guns in space.

Otherwise some of my favorite stories are the archeological side ones from Stellaris. There was a mod I used last playthrough that added a bunch and one that stood out was there were 3 archeological sites in a single system. First one belonged to a fleet based raider civ that suddenly became aware of the damage they were doing, then turned inward and destroyed themselves. The second belonged to a coalition of alien races opposing the raiders but who's political leadership had been making strange decisions in the later course of the war. The coalition eventually fell to infighting and the end of the chain was the leaders being long dead, killed in one of the first strikes by the raiders. The third was of one of the coalition races, they were the weakest of the group suddenly coming to prominence because they survived the infighting. The strange thing is that there's little mention of them, it's recorded that they survived but then it just stops. It does mention that they were Psionic however. This opens up a forth site and you learn why. It turns out that the system was inhabited by a Memetic being, able to sway an entire populace that learns of it's existence. It taught the radiers about guilt, made deliberate mistakes in the war to result in infighting and then tried to hop into the collective consciousness of a psychic race before they all committed mass suicide to try and stop it.

Considering I usually go Psionic Ascension, this left me with two choices. Change the civ to a collective hive mind as it takes over my Chosen One Emperor or lose the scientist because they self terminated before it could infect the galaxy-spanning psionic network. I chose the second.