It depends. I have a ritual of watching a Star Trek series start to finish with my kids when they’re around 10-12. Currently watching Voyager season 5 with my 10 year old and we do savour that intro occasionally but not always. I’d say maybe one in five times we’ll watch it.
It’s a long intro at nearly two minutes but it’s such a good one. Great music, great graphics, super setting.