Struggling to Generate Chart From Existing Data

Question

This question is specific to Google Sheets—hopefully I’m in the right place.

https://docs.google.com/spreadsheets/d/161rtpjNK5363-U6y6MZYzqOIx7CQBCHDWInBXFtZYIg/edit?usp=sharing

As you’ll see above, I have a load of raw site-speed ‘milestone’ timing data for (TTFB, Load, etc.). The graphs I’ve generated so far are the dead easy ones: for each page type, show me all milestones from all countries. Each page type has a corresponding graph. This shows me useful things like ‘on the Shop page, load times are consistent across the world’ or ‘PDPs load much faster in SE Asia’.

The graphs I’m not sure how to generate are ones where each country has its own corresponding chart. For each country show me all milestones for all page types. This will show me information like ‘in Japan, X has the highest TTFB’ or ‘shop pages load much slower than the homepage in Y’. Does that make sense?

I’m worried I may have simply structured my raw data ineffectively in the Sheet, but I’m also pretty clueless about Sheets in general so it could be any number of things I’m getting wrong.

To make things a little clearer, this is the type of chart I’m looking to produce.

Answer 1

I tried something trying not to alter your data as much as possible.

I inserted a new column A and put each type of pages on the left. Then I made the graph as usual and added column A as Label data. Where will be the label depends on which cell they are. All the way up and they will be on the left on your graph, etc.. To ventilate between the type of pages on the graph you can add a space (I.E. cell A8, try to delete it and see on the graph).

To add color, you have the option to format data point in Customize > Series > Add a data point.

Edit : Added a new way to build the graph on the data-test tab. All the credit goes to this video who gave me the idea to format the data in another way (39:30)

With the=QUERY formula you can retrieve the data.

SELECT C (column with data) WHERE A (type of page) = page AND B = metric 

Final formula is =QUERY('reformated-data'!$A$2:$C$47,"SELECT C WHERE A = '"&B$1&"' AND B = '"&$A2&"'",-1)