How to Categorize Data by Week of the Year

Whether you’re working in sales and marketing, or running your own business, you have to look at heaps of data to make strategic decisions. 

And breaking down months (or years) of data into granular weekly or daily insights is as tedious as it gets!

Instead of spending hours manually dividing years into months and weeks, I saved myself all the hassle with Gigasheet’s Explode Date feature. It can split any dataset into daily, weekly, or monthly records within seconds and fast-track any analysis.

In this article, I’ll give you a brief walkthrough of how you can explode dates in Gigasheet to analyze granular data. 

How to extract weekly insights from years of data with Explode Date

I was working on this eCommerce dataset to understand customer behavior when I realized the need to dissect shopping patterns during specific times of the year. This sheet records over 400,000 orders for an eCommerce store placed during eight years from 2012 to 2020. 

I wanted to divide this entire data into week-wise records and study how order sizes and buyer behavior varied during particular weeks in this timeline. 

Here are the exact steps I followed to study this massive dataset and find meaningful insights by digging deeper into the data. 

Step #1: Select “Explode Date” to segment by date, week, month, year

If you want to study any dataset with dates, the Explode Date function is the best starting point to expand your data. 

You have to first go to Insert, then choose the Explode Date function. 

Then choose the date column you want to expand. You can also choose the right time zone for your dates to categorize them in the right days and weeks. 

Once you hit Apply, Gigasheet will take just a few seconds (depending on the size of your dataset) to create new columns with the date, day, month, week, and year. 

In my example of the eCommerce data, Gigasheet created nine new columns to record every order’s year, month, week, date, day, hour, minute, seconds, and time zone. Now, I can analyze buyer behavior based on exactly when they placed orders. 

Step #2: Group orders by week to find goods sold and revenue

For the next step, I categorized all the orders into different weeks. This helped me analyze the peak shopping week of the year for these eight years. 

Here’s a list of all the weeks going from highest to lowest orders. I used the Sort function to arrange this dataset from high to low points and place the weeks bringing the highest number of orders at the top of the list. 

This made it clear that shoppers spent the most in the 44th week of the year—that’s October end and November first week. 

Then, I decided to sum up the total number of items sold every week and compare it with the average unit prices. 

I clicked on the drop-down icon in the Quantity column and chose the Sum function to find this value for all weeks. 

Summing up the quantities and finding the average unit prices gave me an estimate of which weeks earned bigger profits. 

For example, from this analysis, I understood that while people shopped for more items in week 44, they bought higher-valued items in week 31. 

I further divided this data into another group for countries. This division helped me examine shoppers’ purchasing patterns in different weeks of the year across specific countries.

For example, in week 37, people in France placed more orders than those in Germany and Spain. 

Step #3: Filter the data based on various parameters

I found some more interesting observations about my dataset by applying some filters. 

I first decided to only look at orders with 5 or more items purchased. So, I filtered the weekly dataset by quantities of 5 or above. 

This filter was applied across the entire dataset and I could only see orders with 5 items or more every week. Here’s a snippet of the results:

I also wanted to check how many of these higher-quantity orders came over the weekend. So, I added another filter limiting the orders placed only on Saturday and Sunday.

As I expected, several of these orders disappeared, and I could only see the records meeting these two specific criteria. 

Step #4: Hypothesizing with IF/THEN function

At the end, I wanted to identify how many of these orders came from the Black Friday sales each year. So, I used the IF/THEN function in Gigasheet to create a new column based on my specified conditions. 

This function created a new column titled Black Friday Orders. 

If the order was placed in weeks 47 or 48 (November-end), then they’ll be marked Yes otherwise No. 

The results helped me locate all the orders received during the Black Friday weekend. I could use this data to compare how Black Friday sales increased or decreased over eight years. 

Dig deeper into your data with Gigasheet’s Explode Date function!

Gigasheet’s Explode Date function helps you segment a database into smaller records based on timelines. But like we saw in this eCommerce example, exploding the date is just the first step. 

You can do so much with Gigasheet to analyze your data and extract useful insights. Want to give it a spin? 

Sign up for free and explore different features to make data analytics a breeze.