Why is $\pi$ used when calculating the value of $g$ in pendulum motion?

Does it have something to do with the curvature of the Earth which is assumed to be spherical

You'll probably groan when you read this answer since it isn't nearly as complicated as you might think.

Essentially, there is factor of $\pi$ since the angular frequency $\omega = 2\pi f = \frac{2\pi}{T}$

A well know result from the linearized pendulum problem is that, for small angular displacements, the angular frequency is

$$\omega = \sqrt{\frac{g}{L}}$$

which follows from the differential equation in the angular displacement $\theta$:

$$\ddot \theta + \frac{g}{L}\sin \theta = 0 \approx \ddot \theta + \frac{g}{L}\theta\;,\quad \theta \ll 1$$

Thus,

$$\left(\frac{2\pi}{T}\right)^2 = \frac{g}{L} \Rightarrow g = \frac{4 \pi^2 L}{T^2}$$

First notice that simply by considering the dimension of the parameters involved, one can deduce that the time period of oscillations should go like

$$T\propto\sqrt{\frac{\ell}{g}}. $$ This is because $g$ is acceleration hence has the dimensions of Length over Time squared and so the only way the quotient can have the dimension of time is to have the quotient under a square root sign.

The proportionality constant of $2\pi$ cannot be deduced in this manner. For that one has to solve the involved differential equation for the motion. Which happens to have the "circular" functions $\sin$ and $\cos$ as solutions. So I guess one could say that the $\pi$ comes from those function.

I'll try to explain it like I would to my kid, as soon as he gets there.

If you make the same pendulum swing in a horizontal circle, and look at it from the side, you see the same harmonic motion. The only difference is, it stays at the same height all the time, but since we are dealing only with small angles, that's not much of a difference.

Now comes, from above (I mean: from the adults), if you are a kid, the centripetal acceleration:

$$a_c=v^2/r$$

As an adult you can check dimensions: $m/s^2=(m/s)^2/m$ (SI). As a kid, you may be interested in the fact that a U turn at twice the speed is as hard as one with one quarter the radius. (The ability to derive this equation comes a bit later than the ability to understand the proportionalities it expresses.)

Anyways, the (vertical) gravitational acceleration $g$ and the (horizontal) centripetal acceleration $a_c$ pull the pendulum in the same ratio as the length $L$ (for small angles!) and the radius $r$:

$$\frac{g}{a_c} = \frac{L}{r}$$

(Of course, it's the forces that pull, but the mass $m$ cancels out immediately – an excursus in the equivalence of gravitational and inertial mass could be interesting, though.)

Since the movement is on a circle with radius $r$, the speed is

$$v=2\pi r/T$$

so that

$$a_c=4\pi^2 r/T^2$$

which substituted into the above gives

$$g=\frac{4\pi^2 L}{T^2}$$

q.e.e. (quod erat explicandum)

In essence, $\pi$ appears because harmonic motion can be seen as a projection of circular motion.