To my understanding, support is largely local. You'd be hard pressed to find a Hannover fan in Essen unless they or their family is actually from Hannover. It's not really like the Premier League where there are overlapping "spheres of influence". The only exception to this is Bayern really, whose appeal definitely transcends locality.

Naturally the larger, more historic teams definitely have a larger following but I don't personally think that the size of the city itself immediately lends itself to greater support. As you said, neither Hertha nor Union in Berlin are really as "big" as one might expect, so that metric isn't really the best to rely upon when gauging levels of fan support. Same goes for Frankfurt, but they–like the Ruhr area–are surrounded by a lot of other mid-level clubs with histories of their own so the support isn't as clear-cut.

I've never lived even remotely close to NRW though so I couldn't say who is most supported but considering how many similar-sized clubs there are in that region I'm sure the fractures are there just like in Frankfurt. Wouldn't be surprised if Dortmund was beginning to edge out the rest though