Actually there are some equations and results that make the Weyl tensor more intuitively clear, and that also makes the intuitive description that it denotes the deformation of a sphere, whereas the trace of the Riemann tensor is more closely related to its volume growth.
That deformation of the shapes, is due to the tidal forces, and more technically to the shear in the spacetime. The Weyl tensor describes it.
First is the fact that it is also called the conformal tensor, or the conformal Weyl tensor. That is, for two metrics that are conformally related, i.e., $g' = fg$, with $f$ a conformal transformation function of the spacetime coordinates, then, $C' = C$, with $g$ the metric and $C$ the Weyl tensor.
That means that a conformal (or simplistically) 'volume' change leaves the Weyl tensor invariant. The transformations or variations which affect the Weyl tensor are more the deformations than the 'volume' changes. This is explained a little better in Wikipedia at https://en.m.wikipedia.org/wiki/Weyl_tensor.
But one can do better, and understand what it denotes in spacetime.
In essence, the Weyl tensor describes the shear of null geodesics.
Thus, how geodesic balls deform. From a nice description in
https://www.physicsforums.com/threads/geometrical-meaning-of-weyl-tensor.708383/
and with mostly a copy/paste below while leaving out unnecessary
parts,
'Let $k^a$ represent the tangent vector field to a congruence of null
geodesics. We want to find a way to describe the behavior of a
neighboring collection of null geodesics in the congruence relative to
each other and for this we need a spatial deviation vector connecting
a given null geodesic in the congruence to infinitesimally nearby ones
in the congruence.' The equation turns out to be (also from the same
reference)
$$k^{c}\nabla_{c}\sigma_{ab} = -\theta {\sigma}_{ab}+
> C_{cbad}k^{c}k^{d} $$
where $\sigma_{ab}$ is the shear, and describes the deformation of a
ball into an ellipse for instance, and $\theta$ is the expansion
factor of the volume. C is the Weyl tensor. Those are in the subspace
orthogonal to $k_a$. If $\theta$ = 0, then the C term in the equation
above is the rate of change of the shear along the null geodesic. This
is described for instance in Wald.
It has some very useful properties to describe and analyze how a shape in spacetime changes along null geodesics, I.e., along gravitational waves.
It is useful in analyzing gravitational waves. One is the classification of Petrov types, with a general Weyl tensor asymptotically (as we go towrds infinity, ie, further from the course) able to be described as a sum of successively faster decaying Weyl Tensors, as higher inverse powers of u, with u a parameter along the null geodesics towards infinity. This is called the Peeling Theorem, and allows for the classification of Ricci flat spacetimes, and analysis of gravitational waves in vacuum. See a simple description at https://en.m.wikipedia.org/wiki/Peeling_theorem
Another useful tool derived from the Weyl tensor, the Newman-Penrose formulation, uses the Weyl scalars, 5 complex scalars which are formed from the 10 independent components of the Weyl tensor. One of those complex scalars is the outgoing gravitational wave, another is incoming, one is a static term (like the static Schwarzschild field), and two define gauges, at large distances (i.e., as u gets large). See https://en.m.wikipedia.org/wiki/Weyl_scalar
Best Answer
If the metric is Riemannian (positive) your conjecture (a maximally symmetric spacetime is a constant curvature spacetimes) is a known theorem: Theorem 3.1 in Transformation Groups in Differential Geometry by S. Kobayashi. From the proof, it seems to me that the result should hold in the Lorenzian case too, but without a closer scrutiny I am not completely sure.