We make lots of reports in Telecoms. Telecoms seems to be built on reports. Reports and acronyms. Reports for every piece of equipment, for every circuit, for every service, for every product. We make reports for forecasting, budgeting, both CapEx and OpEx, analyses, models. Tons of reports. Reports of all the reports.
One of the most common types of a report in Telecoms is a seasonality report. What is Seasonality?
It’s easy to find examples of seasonality. Here are some:
- The traffic Busy Hour is an example of daily seasonality. A graph of traffic load looks pretty much the same day by day.
- Weekend data traffic might be higher than weekdays because people have more time to stream movies. This is weekly seasonality
- Government or military employees might cause revenue seasonality by buying more phones and prepaid service when they get paid on the 1st and 15th every month. This is monthly seasonality
- Some US carriers experience a boom in Smartphone sales in March-April-May when people receive their government tax refund. That’s a form of yearly seasonality.
Let’s explore seasonality by taking a closer look at a Busy Hour report.
I’m writing a series of articles about tricks Telecoms Technologists can and should use in their day-to-day activities. You can see all the articles at Excel Telecom Tricks
Follow Along
You can download my workbook to follow along with this article. The workbook will help you understand what I’ve written, and you can copy/paste to your own workbooks. I do ask for your email address so that I can send updates. I dislike SPAM as much as you, and promise I won’t ever send any. You can unsubscribe at any time.
What is seasonality?
Here is the Wikipedia definition of Seasonality (https://en.wikipedia.org/wiki/Seasonality). This is my definition: Seasonality is a regular and predictable change over a given period of time.
We want to use Excel to analyze data looking for patterns which are due to seasonality. Telecoms Engineers experience seasonality all the time. An example everyone knows is the Busy Hour. Typically the Busy Hour (BH) is a daily cycle, with performance varying by the hour in a predictable pattern.
Since you’re already familiar with BH, let’s create a BH report while thinking about seasonality. Then, having discussed seasonality using an example you already know, we’ll try a couple more examples to illustrate the common elements to using Excel for a Seasonality Analysis.
Don’t be overly concerned that my data doesn’t match a typical busy hour. It’s mocked up data, so it does not come from actual traffic.
Create a Busy Hour Report
The accompanying workbook has Ethernet throughput data for several months from a variety of network elements. That’s worksheet “Source Data”. The source for this data was a Cacti server, running in a client network, collecting performance statistics from a variety of network elements. A custom Perl script ran as a daily cron job collecting data from the Cacti server for the previous 24 hours. The collected data was appended to a flat file, which was then uploaded monthly into an Excel workbook similar to the one you’ve just downloaded. I altered the data to shield the identity of the network.
Creating a BH report is pretty simple. First, I created a new worksheet called “Bh – All Time”. Then, I inserted a Pivot Table using the source data. I dropped the “hour” field into the Rows area and the “tput” field into the Values area. I set the tput as an Average value. That’s it, you’ve just completed your first seasonality report! Here is an image to show you how that looks.
The key components of seasonality are the hour of each measurement and the average throughput value.
Note that the source data includes an “hour” field. If your data has a timestamp field instead, similar to “DD-MM-YYYY HH:MM”, you’ll need to create a Synthetic Field called “hour” using Excel’s HOUR() function, then use that in your Pivot Table.
Busy Hour Options
To simplify the graph a little, add the group field to the Filter area, then set its value to “ISP”.
You could also highlight the busy hour using the technique in this article Excel Telecom Tips – 2-Color Series. You could also use different definitions of Busy Hour is described in Excel Telecom Tricks – Abstraction.
So that’s a BH report, which is a form of seasonality report. Our BH report typically shows a chart displaying the “seasons”, which are the hours of the day, as the horizontal axis. The vertical axis displays the average throughput on the channel.
The chart in this image shows throughput for the ISP group. Its chart is created automatically by a 3-step process: Update, Refresh, Share. No exgtra work is required.
Now let’s up our game. Let’s compare the average throughput for a single element group month-by-month.
Compare BH by Month
You might want to see how your busy hour changes over time. That’s easy to do with the data we have. I’ve created a separate worksheet to show this, to keep it distinct from the first report. This worksheet is called BH – Month-by-Month. Insert a Pivot Table, use the same source data, and again drop the “hour” field on the Rows area.
Now have another look at the Source Data. I’ve created a new column in the worksheet called “Monthly Date”. This is a Synthetic Field that associates all entries on this table with the first day of the month it was recorded. So When this field is used by our Pivot Table, all values during each month are averaged together. This is a very common technique. Here is a link to my tutorial on Synthetic Fields.
To use this field in our Pivot Table, click on the Pivot Table, open the field list, and drop the “Monthly Date” field on the Columns Area.
Busy Hour Seasonality, Second Try
Although the previous approach works, you probably wouldn’t use it for a serious analysis. I only showed it to you to illustrate how easy is it to do seasonality using Excel.
For a more credible analysis, let’s try again. Create a new pivot table, same source data, drop “monthly Date” onto the Rows area, drop Group and Hour fields onto the filter, and drop field “tput” onto the Values area. To consider only the ISP throughput, set the Pivot Table filters group to the value “ISP”. Assuming our Busy Hour is 8 PM, set the Hour filter to 20. This analysis looks better as a Column chart. This is a more useful seasonality analysis of BH over time.
If you want to consider alternate ways to define your Busy Hour, have a look at What’s Abstract About Excel.
Day of Week Seasonality
So let’s try one last approach. Consider the question “how does the traffic load vary day by day during the week?” Again, it’s easy to answer this question using the same data in Excel.
I’ve added a Synthetic Field called “DOW” to theSource Data. Now create a pivot table using that source data. I’ve added a worksheet for this called “BH – Day of Week”. This time, drop the DOW field onto the Rows area, the tput field into the Values area summarized by average, and group and hour fields dropped onto the filter area. Set the group to ISP and the hour to 21.
Conclusion
There it is, we quickly created 4 charts to illustrate seasonality. Using this general approach, combined with a little creativity to make new Synthetic Fields, and you’ll be creating your own Seasonality reports. You’ve just seen for yourself how easy it is.
The important things to look for are a cycle, or a period over which the pattern reports, and the “seasons”, the units which repeat in the cycle.
Once again, here is the link to download the workbook.
If you’d like to see more of my Telecoms technology and career-building tips, connect with me on LinkedIn Russell Lundberg or follow me on Twitter TelecomVoices