Ya'aleh Veyavo is said on three occasions: in the third blessing of Birchas Hamazon, in the third to last blessing of Shmoneh Esrei on Rosh Chodesh and Chol Hamoed, and in the Kedushas Hayom (middle blessing of Shmoneh Esrei) of Yom Tov. The common denominator is that it's said whenever you need something generic to say about the day.
However, it's hard to figure out what it really is about. A summary might be: "May HaShem remember us for good on this holiday and bless us." There is no mention of why the holiday is important, and in fact the holiday seems almost incidental to the rest of the prayer.
ArtScroll explains that on holidays we think about Jerusalem, so we ask HaShem to remember it. But this only explains a few words in the prayer.
So what exactly is Ya'aleh Veyavo all about?