The mana cost of this card is reduced by 1 at the start of each of the controlling player's turn. In particular:
If Nerubian Prophet is in your opening hand, its cost will be reduced by (1) at the start of your first turn of the game. This means that you can play it for 3 mana on your third turn.
If you draw Nerubian Prophet at the start of a turn, its cost will not be reduced, since "start of turn" effects trigger before a card is drawn for that turn.
This card has a triggered effect that creates a cost-modification enchantment attached to the card itself at the start of each of the controlling player's turn.
The mana cost of this card cannot be reduced below 0. However, further cost-reductions can help in countering cost-increasing effects like Freezing Trap.
This card has an in-hand effect: it triggers only while in the player's hand, and not while the card is anywhere else.
Ideally, players should wait at least three turns before playing their Nerubian Prophet, even if it is not in their starting hand. Even a late-game Nerubian Prophet can have an impact on the game because it increases a player's tempo when being played in succession with other cards.
However, if you are in top deck-mode (having no other cards to play), don't hesitate to play your Prophet. Waiting for it to become cheaper will usually not help your situation.
Because Nerubian Prophet has a printed Mana Cost of (6) in all zones but your hand, it synergizes with effects that care about high card costs.
For instance, it helps Joust-decks to win jousts more often while not necessarily being expensive to play while in your hand.
In summation, Nerubian Prophet is a very limited card. It can be useful for decks that keep cards in their hand very long (e.g. Handlock), or for decks built around Evolve. In most other cases however, it is too unwieldy to be of much use.