Valerica is the estranged wife of Lord Harkon, leader of the Volkihar clan. They have a daughter named Serana, whom Valerica kept hidden in a tomb for centuries to protect her from her father. Lord Harkon plots to end the tyranny of the sun, but to do so he requires Auriel's Bow and Serana's blood for a ritual sacrifice.
She, along with Harkon and Serana, were once devout followers of Molag Bal. Tradition of their cult dictated that the females be offered to Molag Bal on his summoning day. Being selected as an offering was considered to be an honor that was not rejected lightly. Few survived the ordeal and those who did emerged as pure-blooded vampires. Such confluences were known as the "Daughters of Coldharbour". As such, both Valerica and Serana underwent this horrific and degrading ritual.
Sometime before the events of Skyrim, Valerica fled her home of Castle Volkihar with two Elder Scrolls after Harkon became obsessed with the Tyranny of the Sun prophecy. She believed that vampires should stay in the shadows, otherwise it would bring too much attention and ultimately lead to their extinction. She locked Serana and one Elder Scroll in Dimhollow Crypt for her protection and escaped to the Soul Cairn with the other.
When she entered the Soul Cairn, she intended to strike a bargain with the custodians of the realm, the Ideal Masters. She requested refuge, and in return, Valerica would provide them with the souls that they craved. The Ideal Masters had other plans, as they valued Valerica's soul much more. They sent their Keepers to kill her, however, she was able to retreat into a fortress and keep them at bay. This, however, turned into a stalemate as neither side had the upper hand. The Ideal Masters constructed a barrier around the fortress that could not be breached in order to contain Valerica and decided to wait. As the passage of time has little meaning in the Soul Cairn, the waiting game continued for thousands of years.
The Dragonborn may travel with Serana to Soul Cairn to confront Valerica and obtain an Elder Scroll. She explains that the Elder Scrolls are merely a means to an end, and that Serana is the key to the Tyranny of the Sun prophecy. It's revealed that Serana's scroll tells of Auriel's Bow, and scroll in Valerica's possession declares that "The Blood of Coldharbor's Daughter will blind the eye of the Dragon".
She offers her scroll to the Dragonborn if the Boneyard Keepers, located at the tallest spires in the Soul Cairn, are defeated. She also warns that a fearsome Dragon named Durnehviir is roaming this plane and is charged with overseeing the Keepers.
Once Durnehviir is slain she will remark about how she thought she would never see the death of that dragon, but realizes that his physical form is just displaced.
Though Serana spent a lot of time with her mother before her exile, Serana comes to realise, in the Soul Cairn, that Valerica is as bad as her husband in the sense that she uses her daughter to further her plans.
If the Dragonborn kills Harkon, it's possible to travel back to the Soul Cairn and tell her Harkon is dead. She will then return to her study about a day later. After Valerica returns to the castle, she will plant new herbs in the garden in the courtyard, provided Garan Marethi is asked to repair the corridor that leads there.
The Dragonborn can ask her to make Bloodcursed Elven Arrows, but she replies that even though Harkon is dead, she wants to stay as far away from completing the prophecy that Harkon envisioned.