Congratulations! DH and I have well over 2 million miles and points. We have opened up a large number of cc over the last 3 or 4 years. Over the long term our credit scores have gone up. When I began travel hacking my score was in the low 7's and DH was high 7's. Currently, both of our scores are well into the 800's. Each time we apply for a card our scores will take a small hit from the hard pull by a few points but then goes back up. This is what all of the other bloggers and travel hackers report as well. While the new account application affects the score a little negatively, the new line of credit and lower overall utilization ratio affects it positively. Lastly, new apps information drops off your score factoring within a year.
There are cards we keep because the benefits outweigh the annual fee. For us those would be the Chase trifecta for collecting Ultimate Reward points (Sapphire preferred, Freedom, Ink business), Starwood Preferred Guest (points transfer to over 30 airlines), Marriott (free night each year), Club Carlson (40,000 points every year on anniversary). There are cards we apply for just to get the bonus and then cancel once the year rolls around too. We picked up an Amex Platinum, and two American Airlines Executive cards each. They all were 100,000 mile offers so we weren't going to pass it up. Each card also had a $450 annual fee so I wasn't planning to keep them either.
If I can downgrade the card to a no fee card or am offered a decent retention bonus then I will not cancel it. When I cancelled my Amex plat card I still had not used all of my points so I applied for a no fee Amex everyday card which also earns Amex membership rewards and then cancelled platinum card so I wouldn't lose my points.