We don't have a lot of details as to the goals of the CIA in this — they're still classified (for probably no good reason). We know that what they wanted was "an intact [SS-N-4] nuclear missile, the codebooks, decoding machines, the burst transmitters." It's relatively easy to understand why they might want the latter, but why the missile? It's not because they wanted to add a (broken, waterlogged, probably out of date) Soviet warhead to the US stockpile, it's because getting to dissect a Soviet missile and warhead could carry useful intelligence information. What kind of information? I mean, they'd have to look at it to see, but one can imagine that they were hoping to learn about any flaws or design choices they might be able exploit, or information about missile targeting capabilities and systems, and other sorts of things that one could plausibly imagine might lend themselves towards something operational. At the very least, you might learn something useful about how they are made, how they differ (if they do) from American models, and other useful insights. There are some examples of them getting useful intelligence with other kinds of weaponry (like finding out whether a given missile was radar-guided or not, for example, which can help one design countermeasures).

Now whether all of that was worth the cost and difficulty... that's a harder question to answer. Even if the project had been truly successful (and it wasn't), one might ask whether the intelligence paid for itself. It's entirely possibly it might not have — that they'd get it up and say, "hey, it looks basically like we'd guessed it would, huh, except they've stenciled everything in Cyrillic." I get the sense — and this is just an impression — that half of the appeal of Azorian was just the showing they could do it, not about the specifics of the intelligence they might gather. There are far better ways to use sea-bottom intelligence operations; the CIA did some very impressive things with tapping underwater Soviet cables, for example, around the same time, with Operation Ivy Bells.

But large projects like this — very secret, very little oversight, etc. — tend to take on a life of their own, and are frequently host to sunk cost fallacies. But without more information released, it is hard to know.

I can recommend you David A. Sharp The CIA's Greatest Covert Operation: Inside the Daring Mission to Recover a Nuclear Armed Soviet Sub (Lawrence: University Press of Kansas, 2012).

But as has been stated earlier, we know little about the operation and most intelligence experts have hurt their brain over trying to figure out a 500 million USD project, or why Nixon - of all presidents - would approve such an enormous budget for a CIA operation.

The plan was to recover as much of the submarine as possible. This could in theory have provided a wealth of intelligence... if it had not been a completely outdated model, and the Soviet had not quickly changed all their codes after losing contact with it. It might have yielded some valuable information, but nothing worth the expenses.

Perhaps that Nixon recognized the potential for a propaganda victory here in raising a nuclear sub from the depths, but this would be inconsistent with his conciliatory approach and Detente policy towards the USSR that was reaching its peak when the plan got approved.

For the CIA at least, it offered the opportunity to lay their hands on Soviet hardware, which they had been pursuing for some years. For example, a retired CIA officer in Indonesia sold information to the KGB in the 1970's about Operation HA/BRINK, which attempted to gain SA-2 guidance systems, Whiskey Class submarine designs, Destroyer designs, and those of cruisers and a Tu-16 bomber. All of it had recently been sold to Indonesia and the CIA tried to obtain information on hardware from there.

This may begin to point to something of an explanation. As some intelligence scholars have pointed out, all intelligence in the Cold War was military intelligence. It was the only intelligence that really mattered, on which the intelligence community was the uncontested authority, and on which its value was judged and its budgets allocated. There was a constant thirst for better and more details about the weaponry, capabilities and methods of the other side, because military secrets could easily be quantified (bean counting), and be supplied to politicians as "facts". Politicians were usually not impressed by the political analysis or economic forecasts of their spymasters, things they could read in the New York Times too. What they wanted were military facts and figures, if only because they could be used to justify the newest defense budget or the modernization of such and such weapon system. Hence it was in the CIA's interest, especially given its declining reputation among Washington officials, to get any piece of Soviet military hardware they could, and restore its reputation with a new treasury full of facts about Soviet military capabilities.