What a CSM is
A named contact and adviser who stays with you across the whole lifecycle of your infrastructure, from introduction through to continuing development. Not an escalation contact, but someone who knows the estate before anything goes wrong.
What it is actually good for
The practical benefit is usually not the advice, it is the clearing up. Software moves on, and features that needed a separate product three years ago are included in the contract you already hold. Finding those point solutions and retiring them is the most common saving that comes out of this.
Then there is the proactive half: raising problems before they escalate, and keeping the estate current continuously rather than renewing it in a project every three years.
When it pays
When the estate is large enough that nobody holds all of it in their head, and when years pass between projects. That gap is exactly where licences end up running that nobody can account for.