AE can really be thought of as Photoshop for video/motion. Almost anything you can do with Photoshop, you can do in AE (and sometimes, far more). It's got layers, visual effects, masking, etc. all right on the timeline. It really just depends on what it is you actually want to accomplish with a video. If you have an idea, like say "For this part of my video, I want to animate some original graphics on top of the video" or "I have some cool color correction stuff I want to do that I can't (or don't know how to) properly accomplish in Premiere" or "I want to mask out an animated sequence from one show and composite it into another" then open up AE and start figuring out how to do it.
What you don't want to do is bring your video into AE and just start adding random things to it, thinking that more = better. Plus, a lot of the time, you CAN accomplish a lot with just Premiere. I mean, a lot of Nostromo's "effects heavy" (quotations on purpose) videos were done entirely in Premiere, with no AE.
As far as tutorials go, you can always check out
Video Copilot. A lot of people poopoo these tutorials because you end up seeing the results of the tutorials used everywhere (like, frame for frame, the same thing). But if you approach it the way you're supposed to, they're worthwhile. Don't watch a tutorial, follow it, then slap exactly what you did into a video and upload it saying "Look how good I am at AE!" Tutorials are meant to explain HOW the tools work, not "you should do this specific look in your work." Take the knowledge you learned from following the tutorial and apply it to your own ideas. And if you don't HAVE any ideas, then don't do it in the first place.