smoke/fire type effects in games are made trough clever use of billboard spawning with animated textures and post-filters like glows running ontop.
You could use a 3D program, blender, max, maya, whatever to render out a loop that is displayed on your billboard element.
You'd render stuff like say a fireball turning into a smokepuff, but this alone you would not be used as a complete ready in game particle effect, unless you're looking for that classic Duke 3D feel.
Mess around a lot with sample textures and your ingame particle system to gain a feel for how it's done, shuriken in unity comes with lots of sample textures to try out.
How you spawn and animate your billboards, and how well you time it to look plausible will have a much more significant role than the quality of what you end up displaying on that particle though.