To progress the campaign, i would suggest to stay in the roots of the campaign. I dont' see how to properly had a "cult of Rovagug" element in the story.
I would suggest the follow:
0) give the pcs some space to enjoy themselves, being heroes and such. Sothis should become their new city of operations and the current pharaoh should have them as very trusty servants of allies.
1) make the pharaoh wife the true "power behind the throne" by making her a mummy lord too and a servant of some dread power (nyarlatothep works well for me). She could be the one pulling the string of the history by using the elemental powers of his husband bloodline to control the shory artifacts. Make here survive her first confrontation with the pcs and plot against them, gathering allies of his husband (the mega sphinx, or the lesser minions from the other pyramids).
2)put some thing of inner menace inside the pyramid of the sky pharaoh: maybe if the pyramid is deactivated, a powerful elemental force is released or a disaster may shock the entire orision. Or make it the pcs propriety and allow them to defend it against other factions (the pharaoh's wife, the cult of rovagug, the undeads from geb, a blue dragon that wishes to steal it has his throne to name some ...)
3) have the pyramid being usable to fly/go somewhere throught space and time (maybe it is needed to go to another planet, in the past or in the future) in order to oppose a dire treat (awakening of a rovagug's spawn, the coming of a kaiju), or possibly to hide it from the current powers that seeks it (surely Orision would use such a powerfull battle station against any close enemy neighbour)
Yes! You nailed the plot twist I am using with the Queen being the real power behind The Sky Pharaoh. She is after all an Oracle fo the Dark Tapestry. She is actually trying to regain control of the Shory technology. However in my story thread it is the Sacrosanct Order of the Blue Feather that has the knowledge she needs, she orders the Mythic Elder Sphinx (in the last book of the MMAP) to kidnap the governor of Tephu (and Ptemenib who is unfortunately is present during the abduction). The party needs to find where these two NPC's have been taken and need to utilize the series of gates in the Market of Souls. The clues among the "most civilized" layer of the dungeon complex that is the Pyramid of Kamaria require them to use the gates to get to the prison in the Darklands. Hence the need to interact with the Cult of Rovagug, they are not an essential over arcing plot element.