When someone is given “A for effort,” it is usually a backhanded compliment meaning “even though what you did sucked, we know you gave it your best!”
In elementary schools, “A for effort” is used so as not to discourage failing students or their parents.
The reason A is used instead of E is found in the common A-F school grading system where there is no E:
A = excellent; B = good; C = fair or average; D = poor, but just barely passing; and F = failure.