from my experience - it largely depends whether the studio you work for can (and want to) support procedural modelling workflows or not. in last 5 years or so i've done a fair amount of procedural modelling work on films and commercials, despite i'm mainly focused at lookdev/lighting. the reason for this is that if executed properly, it can be extremely efficient and a huge time saver. a couple recent projects i was working on, required square kilometers of CG city (for feature film). everything was decided to be modelled a textured procedurally, basically with only one person needed to do all the heavy lifting in Houdini, and maintaining alone the whole library of 100+ assets and it's lookdev/texturing. Any changes needed in modelling or shading were typically a matter of minutes, or hours if it was a substantial update. Another examples would be complicated industrial structures, or any big natural or manmade structures really, landscapes of any kind... you get the idea.
For that kind of things, procedural approach is just awesome and there is potentially a big demand. Obviously, if you aim to fall into a general "modelling artist" box, you probably will do much better with Maya (even thou it's totally possible with Houdini too - it's just studios typically don't support it as a modelling platform). As others said, this area is already quite oversaturated and there are many excellent modellers out there. With procedural modelling on the other hand, there is too few people that have mastered it on good enough level, so it's not as wide-spread between big studios who still rather model stuff by hand as there is guaranteed constant stream of skillful people.
Having said that, for me personally, procedural modelling and texturing was my most used skill over the last few years. I'm not sure how much of it is just a coincidence and how much it actually reflects a raising demand for this kind of skills. Right now I feel like I could solely focus on that and there would be plenty of work. if you wanna go that way thou, I'd recommend you to start actively seeking opportunities in this area (and build a solid showreel), and companies that are keen using Houdini, rather than hoping for being able to utilize your procedural modelling skills as a "modelling artist" in a random VFX studio.
as for other of your questions:
- Substance apps - definitely yes, it's very much in demand now
- Mantra is great for procedural shading and rendering super heavy weird stuff. Also the best integration with all the Houdini features (obviously). If you like it, then it's good for you (I like it).
- How often and what other skills to learn? All the time and whatever interests you. There is no limit to what your skill set and you will never know everything so don't worry and just keep going as far as it satisfies you
- as for workflows, i don't have any fixed paths i always go. i tend to asses each project individually, thinking about what it needs, where i will likely need to do changes later, and what is safe to have more hard-coded. that's always different. then i maybe steal bits and pieces from another projects and put it together in a way that makes the most sense in that particual case. not mentioning that every project teaches me something new so i always try to improve and iterate my techniques, rather that using the same approach over and over again.