Further out from Coventry is better. The Coventry schools are very hit and miss.
Your son will be in year two which is the third year of primary school. The first year of secondary school is year six, (they move aged 11) so he'll have about 4/4.5 years left in primary school.
I can tell you about Clapham Terrace, Sydenham, St. Anthony's and Shrubland Street primary schools in Leamington - these are south of the river AKA "the dodgy end" - it's a mixture of run down, deprived areas and really pretty older houses which are occupied by a mixture of hippy types and students (mostly). It's a nice place to live - I loved it there. North of the river is very middle class, it's such a contrast I've even heard a local comedian make a joke about it. Main problem with the bottom end of town is that the secondary school, Campion, is not great especially if your child is academic. If they are more into vocational subjects then it might be alright - I heard they had good links with industry etc, although not sure how that's working now they've changed the school leaving age to 18. However, North Leamington school is great and there are also good secondaries in Warwick, Myton is excellent. There are grammar schools (entry test non-fee-paying selective academic schools) in Rugby and Stratford upon Avon if that appeals to you.
Southam has a nice primary school - my nephew has just left there. However the secondary school is struggling to cope with his SEN, despite lots of promises. Southam is a medium sized village but to me it's always felt very villagey - everyone knows what's going on with everyone else, all the shops are in a time warp from 1983, everyone's life rotates around one or another of the three pubs, etc.
Kenilworth is closer to Coventry and pretty much all of it is nice - not as busy as Leamington, but you can easily drive to Leamington or Coventry for shopping, activities etc. The schools are good, there are a few shops and restaurants in the town centre which will meet almost all of your everyday needs, there's a market, and a ruined castle (which does a really good fireworks display).
Would probably avoid Rugby. It's cheap and there are some nice areas but the town centre is awful and some of the areas really scummy. (Sorry to any Rugby people 
I should say one of my best friends lives in Rugby!)
What kind of place do you want to live in (in terms of busy-ness, neighbours, noise levels, closeness to town/amenities, etc.) and what sort of school do you think you're looking for (in terms of arts, sport, academia, after school clubs, SEN support etc?)