There are myriad organic traffic insights that can be drawn from Google Search Console.
In fact, many SEOs I meet confess that they spend the better part of their time on Google Search Console analyzing the SERP behavior of the website.
Because that is the first point of contact the user makes with the site.
In fact, users will make the contact with the site from the SERP if at all the website is ranking on Google’s first page for relevant keywords that you are proud of ranking.
In this post, let’s decode how you can do weekly organic traffic analysis with Google Sheets & Google Search Console and what useful organic weekly insights you can uncover.
It begins with Google Search Console Export on Google Sheets
This step is an absolute no-brainer, just export in G Sheets and go to the Dates tab, it will show how organic clicks and impressions performed on a daily basis from the date range that you exported.
In the next, step what you have to do is use WEEKDAY spreadsheet formula to convert dates into day of the week.
It would numerically represent it, like if it’s Sunday then it would represent Sunday as 1.
Use Find and replace function to replace numbers into names of the day.
That's it! Now you can begin preparing beautiful Charts for Analysis and drawing meaningful insights
In this chart, we can see on what day of the week traffic is spiking and what day of the week traffic is taking a dip.
P.S. It can also be the case that there is no trend, the niche can turn out to be so wherein traffic is random through the week.
I can also see that it is taking the worst dip on Saturday and Sunday and has the worst instances on Saturday.
I can see that traffic is spiking on Monday and Tuesday has maximum instances on Tuesday.
You can go the extra mile and aggregate data with the median to see the overall performance of a day of the week.
With Median data aggregation now you can clearly see what day is best or worst.
We can clearly see here, that Saturday seems to be a loser and Monday on the other hand seems to be the winner.
What happens when we use Average instead of Median?
When we take the average for data aggregation we can see that Saturday is the biggest loser and Tuesday appears to be a winner.
You can also do trend analysis of a day of the week
Here we can see how has Saturday Organic clicks performed over the last 3 months.
To do this from the sheets Day column, create a filter and let it only have Saturday value in it.
Once you do that, the graph will get represented automatically because of that.
Similarly, you can also do CTR analysis like what day of the week gets maximum CTR.
Query analysis can be conducted to see how branded queries are performing through the week. Or how a specific non-branded search term is performing through the week.
It does not have to stay restricted to weekly data analysis.
You can do monthly organic traffic analysis too, take several years into account to spot seasonality patterns.
Or let’s say particularly through the years you want to see how has holiday month performed for your e-commerce store.
Here is another use case
Here we are seeing an exact match search query clicks performance through the week.
This exact match query seems to be receiving more clicks on Monday whereas remains pretty much stagnant on other days of the week.
To execute upon this, the standard GSC export won’t help.
For this, you need an add-on called, Search Analytics for Sheets where you have to group the export by adding the date and query label.
By doing so you can see on what date what number of clicks and impressions took place on the query.
This gives you an insight about on what day of the week what keyword is your audience searching the most or least.
Hope this provided some value, if it did then do share 
Kunjal Chawhan founder of Decode Digital Market, a Digital Marketer by profession, and a Digital Marketing Niche Blogger by passion, here to share my knowledge