I think of Frenzy as an "insanity" thing and not really a frenzy. I have no idea if this is the intent behind it, but I had been thinking of it as going so insane that your character attempts to kill himself/herself and that's what causes the huge damage. It fits the Lovecraft framing pretty well.
Frenzy feels like a poor localisation, they should have just called it Madness or Fugue or anything other than frenzy which is traditionally associated with beastly (no pun intended) behaviour
How does killing the moon presence make you a great one though? Never understood that. Like transcend the hunt makes sense, but that's not supposed to happen, it's not like the moon presence rewards you for killing it.
And it's not like killing a great one makes you one, because the entire rest of the game contradicts that
or does it?
My initial thought was that, given the cutscene before the true ending, specifically where you're "kissed", that the Moon Presence is a (semi) sentient celestial womb and by eating the umbilical cords we create a full one which allows us to attach ourselves to said womb thus birthing ourselves as a great one infant. Not super sure how beating the shit out of the moon presence ties into that, maybe thats us breaking through the cosmic placenta?
Theres a case to be made for that interpretation I think, to quote myself,
"... And when the Great Ones descend, a womb will be blessed with child."
So a Great One descends, a particular woman is impregnated by it, a woman who has corrupt blood that makes her suitable to carry a "Child of Blood" (and be impregnated by the Great One's emanations it seems, no tentacled turkey baster in sight). This child is lost, which leaves a) the Great One wanting a surrogate child and b) a dead great one infant. The dead infant great one gets us a Cord of the Eye:
"A great relic, also known as the Cord of the Eye. Every infant Great One has this precursor to the umbilical cord."
So we can see that when born (dead) the Great One infant doesnt have a full umbilical cord they just have something that could become one.
"Provost Willem sought the Cord in order to elevate his being and thoughts to those of a Great One, by lining his brain with eyes. The only choice, he knew, if man were to ever match Their greatness." -Iosefka
"Every Great One loses its child, and then yearns for a surrogate. The Third Umbilical Cord precipitated the encounter with the pale moon, which beckoned the hunters and conceived the hunter's dream." - Abandoned Workshop
Assuming the Pale Moon is the Moon Presence, the Cord of the Eye seems to be a pre-requisite to getting an audience with the Moon Presence. The Moon Presence is not itself a Great One but seems to be a sentient transformative force (or cosmic womb if you prefer), possibly a servant or avatar of Kos, that can bring about a new Great One, the surrogate child. It would appear that there have been three abortive great one birthing attempts given the reference above to it being the third cord. The reason none of them have worked is that the cord of the eye is only one part of the umbilical cord so those using it coudnt connect proper to the cosmic womb/moon presence. Though its certainlly the key to encountering the pale moon and to uplifting oneself so it seems reasonable to assume the two are synonymous.
Without a full umbilical cord the Moon Presence couldnt give birth to a new Great One so its basically in the process of birthing something (hence the Hunters Moon and the nearness of the Moon Presence). This half born thing is basically a dream without a dreamer (Great Old One). In this case the Hunters Dream, which seems to be both the hub area and the most dominant reality/closest reality to the "real" Yharnam. The Hunter's Dream is the cause of the beasts and they cant be "vanquished" until the reality of the Hunter's Dream is supplanted by someone successfully connecting to the Moon Presence
"Handwritten scrawl: Seek paleblood to transcend the hunt." / "To escape this dreadful Hunter's Dream, halt the source of the spreading scourge of beasts, lest the night carry on forever."
Later the game more or less explicitly tells you to kill some baby Great Ones to get those sweet sweet Cord of Eyes -
"Nightmarish rituals crave a newborn. Find one, and silence its harrowing cry."
I feel the "Great One descends -> Corrupted blood woman impregnated -> child lost -> Cord of Eyes available -> call down moon presence for a shot at ascension" cycle is basically right and I think the Moon Presence as a semi-sentient Great One womb works as well. If a Great One always loses their child then it stand to reason that there must be alternate routes to Great Ones increasing in number, which would be using a full umbilical cord to attach to the Moon Presence (the true ending) or having Kos take "mercy" and give you "eyes" (Rom). So the other Great Ones either came about by similar avenues or were fully extra-terrestrial in nature ( Ebrietas, Daughter of the Cosmos, her name implies she is one of the original or "pure" Great Ones birthed somehow from Kos).
I dont really think the theories which the Moon Presence a more active role (or involve it in a series of sinister machinations) hold up , it seems the most mindless of the Great Ones and I dont think theres any in game support of it being a Great One either. I think the Hunters Dream is a proto-great one brought about by Gherman's incomplete interaction with the Moon Presence.