To me, SSIDs are used for only a few purposes.
1) Security. A private wireless WLAN that requires a form of secure authentication to access LAN resources, and a public WLAN for non-trusted users that routes out straight to the Internet and cannot touch internal LAN resources. This secondary SSID also has the benefit that you can govern its bandwidth (so that untrusted users don't attempt to consume 30Mbps watching the latest Netflix HD movie), ensuring that your priority users on the private WLAN have the resources they need. You might also have built-in web filtering to increase security on the public WLAN, or a captive portal that makes your public/guest users acknowledge that they agree not to break any laws while on your network and agree to your rules prior to access.
2) VLAN of traffic (not always for security). You may have a VoIP network you want on a separate VLAN for QoS prioritization, or to keep your IP subnets organized, and that VoIP network may use some wireless phones. The same could be true for a network of cameras. You can stay neat and organized by having an SSID for s subset of devices that talks only to this particular VLAN. And you could hide the SSID, not for security, but because your guests or staff simply don't need to know those SSIDs exist.
3) I'm not a fan of SSIDs for different radio frequency. If you have a good network, that's what band-steering on your access points is for, so that devices are steered to the frequency best suited to their needs. Having SSIDs for different frequencies is a regrettable lash-up; there may be instances where one has to do it, but it's preferable not to, and if you're using it to work around issues with devices, it's an indicator either of issues with your wireless network or with those devices.
To me, the maximum number of SSIDs you should have is the bare minimum needed to do the job in an organized, efficient (in terms of network traffic) manner. Less is more, and getting crazy with SSIDs leads to confusion. Also know that with some access points, piling on SSIDs lowers performance.