The Gumshoe RPG was designed to address this issue. The way it does this is by shifting the focus from finding clues to interpreting clues. In a nutshell it assumes when it comes to finding a clue, if the players have the skill they will find the clue. What to do with the clue is a different matter altogether.
Fortunately this concept can translate over easily to D&D 5e. The way I handle situations like yours is that absent an adverse consequence for failure, time constraints or some other circumstance that make the result uncertain, assume the PC succeed given enough time.
Now how does this help you with the Mad Mansion? Looking over the module, the author has a lot of interesting things going on, however, he has a lot of unnecessary checks as well. If the players are unlucky outside of combat even if they are doing the right things then they will be unhappy with the module.
For example in room F3 Study there is this
There is one of Gerardus’ journals in a drawer. It could be found with
a successful DC 10 Intelligence (Investigation) check.
My view is that unless the character rolls a 1 for sheer bad luck, that journal should be found provided that the player thinks of searching the drawer in the first place. And that is the key to the Gumshoe method.
In Gumshoe a character with Forensics has to say that he is sweeping the room for fingerprints before he obtain any clues about fingerprints. However the problem with this is that the session can easily turn into a game of twenty question which can be unsatisfying in its own way. The way Gumshoe avoids this is by giving a lot of advice about how to construct a train of clues that offer alternative paths to solving the main mystery.
So to make the Mad Mansion more interesting, I would go through the module and make a list of everything that is important to find, and seed the rest of the module with at least two different clues to where a individual item could be found. As the players continue their search build them a picture of how Gerardus lived and where he kept stuff. The good news is that the skeleton for doing this is already in the module. Your focus should be seeding the other rooms with clues. Remember, you want to avoid having an item that can only be found by saying the one right thing in the right room.