You get dozens of pre-built typography effects in Motion and if you have it, LiveType.
In both cases, you can modify those effects to create something more personal or even start from scratch.
In any case, you need to first look at what your text is for.
For the most part, it carries information -who is the person speaking, what they do or represent, where to get the product etc. If you build some dazzling effect, does it detract from the information that you are trying to convey?
Subtitles, for instance should be able to be read comfortably twice while it is on screen. The trick here is to paraphrase -not translate word for word.
If you are making a trailer, on air promo or sting, sure, go wild. Those things are short and should grab the attention, but not at the cost of the information.
A sure sign of an amateur is when their movie has some crazy transition or effect at every single scene change. It is annoying to the viewer and the only person who thinks it is good is the one who made it.