I have been looking into offloading a WordPress database to Google CloudSQL and the media folder to Google Cloud Storage.
What are the performance benefits of doing so? At what point is it worth it?
For this answer I assume your Wordpress is running on a GCE instance.
Moving database and static resources to Cloud SQL and GCS is likely to have a slight increase in the amount of traffic your instance can handle before becoming overloaded. Moving the database to Cloud SQL is likley to slightly reduce speed of requests, as database hits take a round trip.
Where Cloud SQL and GCS will help you is scalability and potentially reliability:
scalability will increase as with static resources and data moved to a shared service, you no longer need to keep state in the GCE instance itself. This means you can add new GCE instances serving wordpress from the same database behind a load balancer and handle lots more traffic than a single instance could.
If you add multiple instances, you will gain reliability as you no longer have a single point of failure. If one GCE instance goes down, the other GCE will handle the traffic and the LB will ensure no downtime occurs. With HA set up on Cloud SQL, you also gain database reliability. GCS also has lots of redundancy built in. More importantly, you can spread your instances across zones and avoid impact from single-zone issues in GCP.