It certainly wasn't outrageously bad, but just very bland. It isn't even remotely scary. Almost all the scares are fleeting (jump scares galore) and even when it tries to grow suspense, I didn't feel it. The original Insidious had a really tense, scary mood and while it has one of the greatest jump scares of all time, the suspenseful sequences were what really stuck with me. I'd say this is the least successful entry in the franchise, when it comes to inventive scares and creating a mood. The family story is pretty good though, even if the ending feels half-baked.
Ty Simpkins and Sinclair Daniel were both really good though, I wouldn't mind watching another adventure with those two. Rose Byrne's tiny amount of screen time and lackluster role was a disappointment. So was the Lipstick Demon!! This was supposed to be his big showcase, but absolutely not, that didn't happen in any capacity.
Overall, The Red Door feels less like an epic conclusion, than just another entry in a franchise that lost its way a long time ago.