One reason it's so good is it doesn't rely on twists like other mystery type stories. You always see the crime happening in the first few minutes with full confirmation on who did it. So the audience has all the information already, you're watching Columbo put it together and trap the criminal. So you're not figuring out the mystery alongside Columbo, rather, it's like he's breaking down the whole story for you. It's really formulaic, but it's usually fun and varied.

He's also just not a normal cop. He's a weird small man who's annoying and talks about eating chili. He's usually only shown solving crimes committed by completely vile smug assholes. My favorite episode is the one where Leonard Nimoy plays a surgeon who wants to steal his colleague's research out of a lust for fame. So Doctor Nimoy comes up with a plan to purposefully botch his colleague's heart surgery, but a nurse finds evidence of the plan, so Doctor Nimoy kills her with a tire iron and makes it look like a random heroin addict killed her. I love this episode because multiple times Columbo drops the dopey goofy detective thing and lapses into anger. He tells the surgeon just how despicable the whole thing is. I guess that's the good thing about Columbo, is that he has the outer vibe of an everyman. He's just some gremlin man from New York who talks funny and has an invisible wife. But at his core he has a sense of justice and what he despises most are abuses of power.

He's not a real cop is what's good about it. He's a heroic character who doesn't have a real life analogue, so he's a cop out of writing convenience. He's more like a whimsical spirit of justice than an LAPD pig.