Sure there are some nice texts in the Imperial Library, and a lot of conjecture about souls, but almost all this theory of mine is based on experiences only the Dragonborn has gone trough. He goes to a plane of Aetherius(Sovngarde) and returns, he goes to at least one plane of Oblivion(Soul Cairn) and returns, he goes inside a soul gem and returns. So as I see it, the Dragonborn is the only being who could arrive at a conclusion about what happens to souls. Cuz you know, he's been there.

Bearing that in mind, if you have something to write, please base your arguments with facts from the official lore pages and/or in-game content like I did with this theory. Don't go around dropping opinion statements without a shred of evidence to it.

I know there are different types of souls, care to explain?

"Every soul in Skyrim is one of six possible strengths: Petty, Lesser, Common, Greater, Grand, and Black."

"all NPCs, regardless of level, have black souls, which are the same magnitude as Grand souls but can only be captured in black soul gems or The Black Star. Dremora and ghosts are NPCs."

I see... so arguably, sentient beings(humanoid) have black souls and creatures have white souls. Upon death, do they go to the same place?

"It(Aetherius) is the plane of pure magicka, and it's the realm where magicka originates. Common belief is that souls ascend to this location upon death."

So with the exception of Dremora and ghosts, black souls go to some plane of Aetherius, a paradise of sorts. You also know this is not just common belief. Sovngarde is a plane of Aetherius you get to visit at some point. You don't see any creatures there, nor there is any information about animal souls at an Aetherius plane, but you also know they don't disappear, How?

Ok this is where it gets kinda weak, but I can give you 2 good reasons

1- There is a conjuration spell to summon an animal soul from Oblivion. It's called Conjure Familiar and it summons a wolf's ghost.

2- For the sake of the argument let's assume there is no difference between the soul of a wolf and a chicken. If you were to kill them and their souls disappeared, what would be the point of having a soul? NO. it's stands to reason they would go to some plane, but which one?

"The Hunting Grounds are the realms of Hircine in his plane of Oblivion. Being seemingly endless, the plane features puzzling rooms and mazes inhabited by vicious creatures such as bears, wolves, werecreatures, and Daedra."
"the beasts roam the heavenly lands, hunting prey for the rest of eternity."

Got it, humanoids go to Aetherius, creatures go to Oblivion. But what happens when I trap a soul inside a soul gem?

"Trapping souls in soul gems requires the conjuration spell Soul Trap or a soul trap enchanted weapon. Killing an enemy with the spell cast upon them or using the enchantment draws their soul into one gem of the appropriate size."

Ok, so soul trap an entity, kill it, its soul go into the gem (See The Black Star (Quest)). However, what happens when I use this soul gem to power/enchant an weapon/armor?

"When a soul is trapped in a Black Soul Gem and its energy used to power an enchantment, the remnant—the soul itself—is sent to the Soul Cairn. It is a place devoid of life—the only beings that exist there are souls, undead and the rulers of the realm, the Ideal Masters."

Hm... so trapped black souls, when used, are sent to Soul Cairn. Even though it's better than inside a soul gem, that doesn't seem a very nice place to spend eternity, compared to Aetherius paradise. What about trapped white souls used in enchanting?

"There are luminescent cracks in the ground throughout the Cairn(Soul Fissures). Interacting with one will fill the largest empty soul gem on hand(including white soul gems), but soul fissures will not fill the Black Star or Azura's Star."

Ah.. so creatures used in enchanting become the pretty light that comes out the Soul Fissures, I wonder if this is such a bad fate? In this state, do they even retain consciousness?