To me, a killstreak should perform in a way that directly correlates to the difficulty of earning it. If it takes me only four kills to earn, it shouldn't be more durable and effective than something that took double that to earn (prime example... guard dog vs vulture).
Another thing that I should mention is the ability for controllable killstreaks to feel like you are making a difference. Other than the helo pilot from ghosts, nothing else from that game that was controllable felt like it did anything other than make noise.
If I had to define a single killstreak as being my baseline for being "exciting", I think back to the EMP from MW2. If took 15 kills to earn, but it not only destroyed all the other team's killsteaks (which were pretty nasty), but it made the most terrifying noise that made you respect it, and was accompanied with a cool aesthetic if you looked up.
People will all have different definitions for what makes something fun, but for me, it comes down to balance, accomplishment, and aesthetic/sensory appeal.
Controllable killstreak (dont like them getting kills for me) that is easy to use but hard to master.
If you ARE NOT good with the killstreak and can only get it in a care package because you dont have the skill to get it, you can only get 1-2 kills or none at all, however, if you master it you can get LOTS of kills with it.
I think the chopper gunner from BO1 was like this and i loved it.
I cant tell you what i find "fun" and "exiting" in a streak but i can tell you why i am looking forward to them more in AW.
1.) Joinable streaks (2 players control the same streak (some add support others add secondary assault roles)
2.) Upgradable streaks Say i like the sentry gun but feel it needs a little more punch, add 100pts to it and i can put a grenade launcher on
3.) Scorestreaks (actually this leads back to your original question) I find earning a score towards a streak alot more exciting and fun that just racking up kills for a notch on a ladder. I would rather gain 250pts for capturing a flag for my team than earning 2 notches on the ladder for getting 2 kills with one sniper bullet for instance.
Wow, this post received a late reply lol.
Regarding what makes a streak exciting, a YouTuber named "Driftor" recently answered my question (not directly). From what I can gather, he basically explains that killstreaks should feel rewarding, but at the same time, balanced and not overpowered ie with better counters. That great feeling of being rewarded shouldn't be from earning 20+ kills from the streak itself. Instead, the reward should be how the streak looks and sounds that makes the player feel accomplished and excited to earn it.
For example, what sounds better?
1. A simple missile that explodes on the ground...
2. A giant freakin' laser beam of doom!!!!
Both are the same in terms of effectiveness, but one is newer, more unique, and looks awesome.