Buffy is a great fantasy adventure show with witty dialogue, characters worth rooting for, and a heavy dose of camp. It helped change people's perspectives on serialization in television, and featured one of the first lesbian main characters. What it is NOT, is prestige television. They were comfortable having bad cgi because the other option was putting people in giant monster suits. If you can't stand the occasional bad episode or cheesy moment, you probably won't care for it because it's more interested in wild storylines and bizarre character moments than it is in being "high quality."

Basically, turn off your critic hat and you'll be surprised how much it does well. But no, it's not the fucking Sopranos, it's Buffy The Vampire Slayer, they're not aiming for high art.

By the way, I remember the episode you're talking about and that snake is probably one of the worst effects in the whole series. They typically have pretty decent sfx makeup for their monsters and, like a lot of late 90s shows, underestimated how quickly cgi would get dated