You say this when someone does something really well. You say it to your children, your employees, your team mates, or anyone who you feel comfortable complimenting in this way.
It's more common to say "good job". So when you say "great job" it sounds like an even stronger compliment.
A slightly more proper way of saying this is:
"You did a great job."
But just "Great job" is more common in spoken conversation.
If you want to specify what you're complimenting the person for, you can say "great job on (something)":
Great job on that presentation, Betty.