Someone explain the charm of this mon as a fossil to me.
It's just a sea turtle.
>but it's actually based on an extinct turtle
Yeah, Archelon. You know why it is noteworthy among extinct turtles in the first place? Because it was fucking huge, the largest sea turtle in fact.
Take the size away and you're left with pretty much a regular sea turtle.
Carracosta is not big.

And I'm saying this as someone who likes turtles. If they wanted to use a testudine for a fossil they could have used some of the more interesting ones, like Meiolania(or other meiolaniid tortoises) or Ocepechelon to name a few.
The worst part is that the dex speculates that it is the ancestor of all turtle pokémon, which is ridiculous since Archelon is a pretty advanced sea turtle. If they wanted to go down that route they could have easily used some really rad animals like Cyamodus, given that turtle evolution is still somewhat of an enigma.