Welcome to Mi Yodeya, thanks for the most excellent first question.
It seems that Kaddish didn't start out as a mourner's prayer. According to Rav David De Sola Pool in The Kaddish, it's origin is as closing tefilla after a Torah discourse. Indeed, it is still used in this way by many sefardim around the world, after a shiur or learning session or even a short vort, a mourner will stand up and say it if there is a minyan.
This is also how it is used in organised tefilla, which is composed of lots of different sections with different purposes. In order to make a clear ending of a section, the ancient practice of reciting Kaddish is employed as a divider between sections, some of which are recited by mourners.
Why is it recited so often? The more the better*. The primary gadlut of Kaddish is the "amen yehe Sheme rabba" that everyone answers in response. It's considered among the holiest moments in the whole of tefilla, because it's like saying Amen, but 1000 times greater. Rav Simcha Zissel of Kelm writes that the reward for saying it is 1000 times greater than saying amen (note, saying one word of Torah is 1000 times greater still!). The shulchan aruch states that one should run to hear kaddish. There are many great things stated about saying this amen, Chafetz Chaim said it affects yeshuot for klal Yisrael, the Ostrover Rebbe said that if we all say it properly, the geula will arrive. R' Meir Grunwald made it his mission to go around emphasising the importance of saying amen to kaddish.
So, what is so great about those words? The background starts in Berachot 3a:
אָמַר רַב יִצְחָק בַּר שְׁמוּאֵל מִשְּׁמֵיהּ דְּרַב: שָׁלֹשׁ
מִשְׁמָרוֹת הָוֵי הַלַּיְלָה, וְעַל כָּל מִשְׁמָר וּמִשְׁמָר יוֹשֵׁב
הַקָּדוֹשׁ בָּרוּךְ הוּא וְשׁוֹאֵג כַּאֲרִי, וְאוֹמֵר: ״אוֹי לִי
שֶׁחֵרַבְתִּי אֶת בֵּיתִי וְשָׂרַפְתִּי אֶת הֵיכָלִי וְהִגְלִיתִי אֶת
בָּנַי לְבֵין אוּמּוֹת הָעוֹלָם״.
Rav Yitzḥak bar Shmuel said in the name of Rav: The night consists of
three watches, and over each and every watch the Holy One, Blessed be
He sits and roars like a lion, because the Temple service was
connected to the changing of these watches (Tosefot HaRosh), and says:
“Woe to Me, that due to their sins I destroyed My house, burned My
Temple and exiled them among the nations of the world.”
תַּנְיָא, אָמַר רַבִּי יוֹסֵי: פַּעַם אַחַת הָיִיתִי מְהַלֵּךְ
בַּדֶּרֶךְ וְנִכְנַסְתִּי לְחוּרְבָּה אַחַת מֵחוּרְבוֹת יְרוּשָׁלַיִם
לְהִתְפַּלֵּל. בָּא אֵלִיָּהוּ זָכוּר לַטּוֹב וְשָׁמַר לִי עַל
הַפֶּתַח, (וְהִמְתִּין לִי) עַד שֶׁסִּייַּמְתִּי תְּפִלָּתִי. לְאַחַר
שֶׁסִּייַּמְתִּי תְּפִלָּתִי אָמַר לִי: ״שָׁלוֹם עָלֶיךָ, רַבִּי״.
וְאָמַרְתִּי לוֹ: ״שָׁלוֹם עָלֶיךָ, רַבִּי וּמוֹרִי״. וְאָמַר לִי:
בְּנִי, מִפְּנֵי מָה נִכְנַסְתָּ לְחוּרְבָּה זוֹ? אָמַרְתִּי לוֹ:
לְהִתְפַּלֵּל. וְאָמַר לִי: הָיָה לְךָ לְהִתְפַּלֵּל בַּדֶּרֶךְ.
וְאָמַרְתִּי לוֹ: מִתְיָרֵא הָיִיתִי שֶׁמָּא יַפְסִיקוּ בִּי עוֹבְרֵי
דְּרָכִים, וְאָמַר לִי הָיָה לְךָ לְהִתְפַּלֵּל תְּפִלָּה ..קְצָרָה.
Incidental to the mention of the elevated significance of the night
watches, the Gemara cites a related story: It was taught in a baraita
that Rabbi Yosei said: I was once walking along the road when I
entered the ruins of an old, abandoned building among the ruins of
Jerusalem in order to pray. I noticed that Elijah, of blessed memory,
came and guarded the entrance for me and waited at the entrance until
I finished my prayer. When I finished praying and exited the ruin,
Elijah said to me, deferentially as one would address a Rabbi:
Greetings to you, my Rabbi. I answered him: Greetings to you, my
Rabbi, my teacher. And Elijah said to me: My son, why did you enter
this ruin? I said to him: In order to pray. And Elijah said to me: You
should have prayed on the road. And I said to him: I was unable to
pray along the road, because I was afraid that I might be interrupted
by travelers and would be unable to focus. Elijah said to me: You
should have recited the abbreviated prayer instituted for just such
circumstances...
וְאָמַר לִי: בְּנִי, מָה קוֹל שָׁמַעְתָּ בְּחוּרְבָּה זוֹ?
וְאָמַרְתִּי לוֹ: שָׁמַעְתִּי בַּת קוֹל שֶׁמְּנַהֶמֶת כְּיוֹנָה
וְאוֹמֶרֶת: ״אוֹי לִי שֶׁחֵרַבְתִּי אֶת בֵּיתִי וְשָׂרַפְתִּי אֶת
הֵיכָלִי וְהִגְלִיתִי אֶת בָּנַי לְבֵין אוּמּוֹת הָעוֹלָם״. וְאָמַר
לִי: חַיֶּיךָ וְחַיֵּי רֹאשְׁךָ, לֹא שָׁעָה זוֹ בִּלְבַד אוֹמֶרֶת
כָּךְ, אֶלָּא בְּכָל יוֹם וָיוֹם, שָׁלֹשׁ פְּעָמִים אוֹמֶרֶת כָּךְ.
וְלֹא זוֹ בִּלְבַד אֶלָּא, בְּשָׁעָה שֶׁיִּשְׂרָאֵל נִכְנָסִין
לְבָתֵּי כְּנֵסִיּוֹת וּלְבָתֵּי מִדְרָשׁוֹת וְעוֹנִין ״יְהֵא שְׁמֵיהּ
הַגָּדוֹל מְבֹורָךְ״, הַקָּדוֹשׁ בָּרוּךְ הוּא מְנַעְנֵעַ רֹאשׁוֹ,
וְאוֹמֵר: אַשְׁרֵי הַמֶּלֶךְ שֶׁמְּקַלְּסִין אוֹתוֹ בְּבֵיתוֹ כָּךְ,
מַה לּוֹ לָאָב שֶׁהִגְלָה אֶת בָּנָיו, וְאוֹי לָהֶם לַבָּנִים
שֶׁגָּלוּ מֵעַל שׁוּלְחַן אֲבִיהֶם.
And after this introduction, Elijah said to me: What voice did you
hear in that ruin? I responded: I heard a Heavenly voice, like an echo
of that roar of the Holy One, Blessed be He (Maharsha), cooing like a
dove and saying: Woe to the children, due to whose sins I destroyed My
house, burned My Temple, and exiled them among the nations. And Elijah
said to me: By your life and by your head, not only did that voice cry
out in that moment, but it cries out three times each and every day.
Moreover, any time that God’s greatness is evoked, such as when Israel
enters synagogues and study halls and answers in the kaddish prayer,
May His great name be blessed, the Holy One, Blessed be He, shakes His
head and says: Happy is the king who is thus praised in his house.
When the Temple stood, this praise was recited there, but now: How
great is the pain of the father who exiled his children, and woe to
the children who were exiled from their father’s table, as their pain
only adds to that of their father (Rabbi Shem Tov ibn Shaprut).
When we say amen yehe Sheme rabba, we are praying that Hashem's Name of Yud and Heh should become rabba, great. It is a prayer that the incomplete Name should become the Ineffable name once again, once evil has been eradicated. By saying it, we are consoling and standing up for the suffering of the Shechina, who is with us in exile, who is inconsolable as much as the day the Beit Hamikdash was destroyed, and who longs for Her children's return.
It is a deep, emotional connection to Hashem, that arouses His compassion and brings the geula closer. The Vilna Gaon writes in his introduction to Tikkunei HaZohar: One who answers amen yehe Sheme rabba in the correct way will merit to be saved from chevlei Moshiach. It can be compared to a king who orders his soldiers, "Leave him alone. He already suffers my pain. He need not suffer extra agony".
The Chafetz Chaim illustrated the Shechina's suffering by asking someone to give a large sum of money to someone so poor he doesn't even have a chair to sit on. The man was unable to find someone so impoverished, despite seeing the most wretched in the town. The Chafetz Chaim said "you should know that Hashem is poorer than all the paupers you found. For His chair is not complete until the coming of Moshiach, and neither is His House, the dwelling place of His Shechina. Now Hashem is moaning like a dove, unable to rest in peace."
The level of pain Hashem endures for us exceeds human comprehension. Yet there is something that brings Him untold joy - when Hashem hears us answering amen yehe Sheme rabba.
Yaacov Avinu was the first to utter amen yehe Sheme rabba (Yerushalmi). The gemara in Pesachim states that he said Baruch Shem K'vod Malchuto in response to his children's "Shema Yisrael" at the end of his life, and the Yerushalmi states it was amen yehe Sheme rabba. The resolution is that they are the same, but we don't say in hebrew as it is a prayer of angels and we can only whisper it, whereas we wish to say amen yehe Sheme rabba loudly and often!
The Rokeach called Kaddish "Shir Hashirim".
I strongly recommend getting the book "Just One Word" by Esther Stern, and reading all the stories and mashalim on amen yehe Sheme rabba.
* That's not to say we should try and find as many opportunities to say it as we can. See this answer on the contrary. It means that the fact it has been inserted by the Anshe Knesset Hagedola into our tefilla in so many places is not surprising, as it is a very great and important prayer. The Arizal would spend precious time each morning touring the shuls of Tzfat so he could say more amens to people's birchot hashachar.
