WHEN IS THE BEST TIME TO VISIT THUNDER HOLE?

You want to plan your visit to Thunder Hole so you are there as the tide is coming in, not going out or at low tide.  This is why you read where people visited Thunder Hole and only heard whispers with no thunder to speak of. 
The best time is when the tide is coming in and the sea is rough due to bad weather, this is the time when you really hear loud thunder at Thunder Hole.
Other that that, if you can visit the area in early spring or late autumn when the sea is naturally rough, not only will you hear thunder but also see large  crashing waves.

THUNDER HOLE - ACADIA NATIONAL PARK