Effect size interpretation If r between X and Y is 0.74, then r-squared is 55%.
According to many 'rules of thumb' and others, the effect size (in this case r) is considered 'moderate' or 'medium'. 
As such, only 55% of the variance in Y can be explained by X. The remaining 45% remain unexplained.
Question 1: Given the above, how good is r when it can only explain 55% of the variance? 
I know this depends on the context of the study but 45% of unexplained variance, by any measure, is huge. I am afraid this is the number (not 55%) that my (non-statistical) target audience will remember at the end of the day.
In other words, I may get a question such as "what is the point of the research when 45% of the result remain unexplained?"
Question 2: How should I answer this question?
Notwithstanding the above, I think 0.74 is a good and strong result!
 A: 
According to many 'rules of thumb' and others, the effect size (in this case r) is considered 'moderate' or 'medium'. 

No - in social sciences effect sizes of r > .50 are considered as large (Cohen, 1992). That already answers Q1 - an r of .74 would be considered as fantastic in many research scenarios. But that depends - as always - on the specifics of your research. If you explain one questionnaire scale with another scale which has more or less the same meaning/ wording, then this correlation would rather be considered as trivial (or would be taken as a sign of convergent validity, i.e. that both scales measure the same construct).

"what is the point of the research when 45% of the result remain unexplained?"

Again, it depends. If you are dealing with social or psychological research, virtually all phenomena are determined by a multitude of factors (like the weather outside, the mood of the participant, the childhood of the participant, his health, what happend the day before, etc.). In most cases, it would be highly unrealistic to expect that one single factor can explain any psychological phenomenon. Contrary, if one factor explains everything, I would be suspicious that the results are either wrong or trivial (ceterum censeo: also that depends, of course).
A: While some might initially question the value of research that delivers an r=.74 because much variablilty remains unexplained, if you offer your audience an opportunity to play in a dice game where 55% of the variability in outcomes could be predicted... well, those that eschew you are bound to fall victim to some form of Darwinian justice.  Give them the choice between dice that offer r=0.00 and dice that offer r=0.74... and see where they land. 
