I think the best are those that set-up the video content before the intro, have a short-ish intro, then continues with the bones of the content.
It gives you a synopsis of the content, allows you to decide if you want to commit to spending time watching the rest of the video, then gives you a bit of breathing space before getting into it.
It's how TV episodes work. It's how we work when we decide if we're going to read a book (both reading the back cover, and then the foreword/preface/introduction/prologue)... Or watch a film (trailer/back of DVD case/etc initially, but then intro scene followed by titles for a lot of films)... Or TV (advert/bit in listings/bit before the first ad break followed by opening titles).... Or computer games (many you'll play a scene or chapter before the "opening cinematographic" style titles...
We're conditioned to the overall process though - so it seems familiar, puts us at ease and works.... As long as the quality in execution and content matches, it makes us subconsciously consider it as a professional video of value as it has a production value to it...