How to generate a fake hand painted texture (Blizzard like), Borderlands effect or Pixel Art effect
Hand painted effect

One texture is needed: a normal map. I recommend you to bake it in 32 or 16 bits float in order to get more depth on your curvature map and to bake it at a minimum resolution of 2048*2048.

Once it's baked you will need to generate two maps from it:


Generate a curvature map from a normal map: https://blender.stackexchange.com/a/72602/23134
Generate a smooth curvature map from a curvature map: https://blender.stackexchange.com/a/100637/23134
to the second slot. We don't use Multiply so the value of your color will have no effect on your texture.


The node setup is simple, you must overlay the two maps, the order makes a subtle difference:


if the curvature smooth map is in the first input of the overlay node the details from the curvature map will be shadowed so it's more realistic I recommend it.
if the curvature is plugged on the first input, the details on it won't be shadowed making the result less realistic.


Add a value node, for the first input add a color ramp in between your curvatures and the value node, on the second input the curvatures. This will allow to use the curvature to change the brightness of the color so you don't have to worry about the value of your colors.

On the color ramp for the left cursor pick a cold color, it's a color closer to blue and saturated (= a lot of color), for the right cursor pick a warm color closer to yellow and desaturated (0.5 saturation maximum, you can go higher for effects like lava).

The result:



Top lighting

To create a top lighting effect for stylized games Diffuse/Color map (PBR games do not use lighting informations in their albedo textures) you can bake an Object Space normal. You can get more informations here on how to bake it: https://blender.stackexchange.com/a/72664/23134

Once it's baked you will need to separate the Green channel (it depends on your axis it can be another channel) to get a top lighting effect. Then you must overlay it to the curvature. Adjust the value, the default 1 adds too much lighting informations.



Texture a game character or creature (and how to make it a sprite)

To add some SSS on your albedo map you must bake a thickness map: https://blender.stackexchange.com/a/100725/23134

Like before all your map must be imported in Linear mode and you must again overlay the curvature smooth with the curvature map for your grayscale then add a value node and use a color ramp in HSV mode to pick the analogous colors. For the thickness map mix it with the base albedo, use a color ramp with R: 0.7 GB: 0.1 for the blood color, you don't need to touch the right cursor. For the factor of the mix node add a color ramp with the left cursor in RGB: 0.1 (you can adjust this value for the amount of blood visible, brighter = more blood visible) and move the right cursor to the middle (position 0.5). Then you can connect the alpha of the thickness map to the alpha of the output node for the transparency if you are baking a sprite.



Drawn lines effect

You can use a color ramp node in constant mode to fake a texture with drawn lines. On the color ramp the left cursor has a dark value the right cursor has the background color. The more you move the right cursor to the left the more lines disappears. Then add a relative blur. You can adjust the Pixel Width for the thickness of the lines.



You can overlay the curvature with the lines with the curvature smooth at 0.5 for example to get more color variations:



Retro texture effect

For a retro effect GIMP is needed. Import your fake hand painted texture, I recommend you the one with the top lighting effect.

Change the saturation, old games had saturated colors. Go in Color > Saturation..., you can increase is by 25% by entering 1.250.

Reduce the scale of the image, go in Image > Scale Image..., you can set it to 512x512, bellow too much details are lost for this texture. For the interpolation select None, using an interpolation will make the image blurry.

To reduce the number of color go in Image > Mode Indexed, enter the number of color you want. Use a very low number like for example 5 (at 4 the black color for the shadows wasn't visible). I suggest you to use a dithering effect it create a smooth color transition by adding points of the same color, the positioned effect gives a more retro style by placing the pixel more like a checkerboard.

Reset the scale of the image, if you export it in 512x512 in PNG the image will be blurred even with no compression. Set it back to 2048x2048 without interpolation.

Here is the result:



Should I export a Diffuse or Albedo map?


A diffuse map is a texture for non-PBR games (also means less real time lighting and less ressources used ideal for mobile games) so the map can include some lighting informations like using a multiply with an ambient occlusion or a soft light with a color channel of an object space normal map to add top lighting. You can also use them with an unlit shader so they do not receive any lighting and shadows for better performances.
In PBR we use an albedo that contains no lighting information (or a little amount for stylized textures) to make the asset works in every real time lighting conditions. Stay with the curvature and thickness map only. The ambient occlusion must be imported separately in the shader so it is only visible in the shadowed part of your asset and disappear when hit by direct lighting (for that you must set your game engine in deferred lighting instead of forward). Also use Linear color space in your game engine instead of gamma to make your PBR textures more realistic.


Where to use them?

Fake hand painted textures are popular on mobile games, MOBA and MMORPG. Those textures can be used as an hand painting base to finish them manually.

Those textures are popular on the Dota 2 Workshop, you can learn how to create an asset for the game here: https://blender.stackexchange.com/a/101830/23134