I shoot in manual mode most of the time. I make my choices about the aperture based on several factors: 1) composition and subject, 2) the lens I'm using, and 3) length of exposure.

To me, what sets a great photo apart from a good photo is the composition. If the background is cluttered or full of things that might be distracting from the focal point (like a sign or other people I don't want to shoot), I'll open it up all the way to make sure my subject pops out against a soft background. Depth of field is important to me if I want it to look intimate. Less so if the background needs more presence for context and texture. I tend to ride the settings a bit varying between a f/2.8 and 5.6 for the intimate shots. The context of the subject is the deciding factor before I stop down to 5.6-11. For example, if I'm shooting a runner finishing a marathon, I usually want just that one runner so I'm wide open getting her in her moment with a nice soft background--but if the background is her friends or family, I'll reduce the depth of field and frame the shot to include her cheer section.

A lens with great image stabilization makes all the difference, too. Prime lenses are great for portraits and landscapes but they tend to be difficult for action shots without advanced IS. I'm going to want higher shutter speeds for a non-IS lens so I mitigate the blur as much as I can so I'll be experimenting with aperture, ISO, and shutter speed to find the best setting for that lens (and keep notes on it). I'll adjust my ISO and shutter speed to work with the aperture setting I want.

Light is always the foil for these settings for me, though. If the light it low, I'm simply going to have to open up the aperture. I'll always choose a lens that opens to at least 2.8 for low light.

Experimenting and practicing are the best ways to learn to get what you want out of a shot.