We have had a little experience with it. Here's some old documentation on how we originally went about handling transition tiles in our title: [LINK].
While in theory this sounds nice, it was really a pain to implement and support it. We've pretty much thrown away all support for this sort of programmable transition tile. We achieve the same effect now by simply using multiple tile layers and having transparency in several of our tiles that do the blending. Adding support to our editor to automatically detect the proper transition tiles and enter it accordingly is not worth the effort for us (we are a very small team of unpaid hobbyists). Instead, we focused on making our editor easy to use in other areas and add in the extra tiles necessary into our tilesets to achieve nice looking transitions.
Here's a long-ish video I released recently of several major changes that I made to our editor. You can kind of get an idea of how we build maps using tilesets, layers, and a feature we call "map contexts". If you're not interested in watching the whole thing, I would skim around and take a look at the different things we can do with it.
My opinion is that custom support for transition tiles in your editor/game is "nice to have", but probably shouldn't be a high priority. There's likely much more important things you can invest your teams time in, and I don't think adding any fancy transition tiles will save you much time in map design (at least not for a game like ours). But take my opinion with a grain of salt, as I haven't worked extensively with this sort of technology myself.