Still, this type of oversight seems all too common even in large companies. I remember several cases from Fortune 500 companies in the past few years alone. What would be a good way to automate checking for them? Has anyone developed a tool designed specifically to avoid certificate expiry disasters?
I've never seen a bulletproof solution for organizational tasks that need to be done yearly.
If someone's in charge... and both they and their manager happen to leave in the same year... and whatever system they had in place to remember (probably their personal calendars) is gone... and the manager's manager has 1,000 other things to remember...
...how does an organization ensure the task still gets done?
With something almost stupidly simple and low-tech: checklists.
(I'm reading "The Checklist Manifesto" right now, and the points it makes seem to fit perfectly with everything you mention.)
Once you do this, the only checklist that matters are procedural checklists to add a new client or new cert to the renewal notification list. When you use a standard group email for all cert purchases, that one becomes tough to miss.
In my 7 years of being involved, we never missed a cert renewal with this process for ~300 client sites with multiple or wildcard certs.