Here’s a problem I’ll bet you struggle with all the time. You’ve got sites in your network with problems. High handover failures, high dropped calls, other failing KPIs. It might include sites on the edge of a coverage area with no handover neighbors, or sites which are close to lakes and other bodies of water, where RF skip from distant sites is a problem, or maybe sites with dodgy microwave backhaul links. There are tons of situations on a mobile network which can cause these persistent, hard-to-fix problems.
The image shows a list of “Top 10” sites with high dropped call rates. On the left, is the current list. This is before exclusion has been applied.
On the right is the list after the troublesome sites have been excluded. Now all the sites on the list are problems that the team can actually solve.
If your cluster or network has enough of these problematic sites, then the awful statistics they produce can clutter your dashboards and recurring reports, masking other problems which could be fixed and really should. If your reports display a “Top 10” worst-performing sites, these known, unfixable problems will always appear, hiding other problems that you could be fixing.
I’ve seen many teams manually remove these sites from each report. This improves the usefulness of the reports by hiding problems which cannot be fixed. But manually removing these sites is a laborious and time-consuming manual process. It’s a tremendous waste of time.
Just What You Need
What you really need is an automated way to remove selected sites from your reports, without altering or deleting the raw data. This removes the problem sites, so they don’t clutter your Dashboards and recurring reports.
This image shows a “Top 10” list with the first 5 sites with terrible dropped call rates. In this example, these 5 sites would show up in the first 5 places every month. The impact is that your Top 10 is really a Top 5. This is before the troublesome sites have been excluded.
If you could find an automated way to hide these sites, a way that does not require time-consuming manual intervention, your Top 10 would truly be a Top 10.
This Excel Telecom Trick is exactly what you need.
Follow Along
I’ve created a workbook which shows you exactly how to do what I’ve written about. You can download my workbook to follow along with this article. The workbook will help you understand what I’ve written, and allows you to copy/paste into your own workbooks.
I do ask for your email address so that we can keep in touch. I dislike SPAM as much as you, and promise I won’t ever send any. You can unsubscribe at any time.
Approach
Solving this problem and removing the known trouble sites from the “Top 10” list of sites in reports, is a 3-step process.
- Create a Lookup Table of troublesome sites.
- Add to the raw data input table a Synthetic Field which substitutes a low-impact value for the troublesome sites.
- Use a Pivot Table to create your “Top 10” based upon the Synthetic Field
For this article, I mocked up Dropped Call data for 24 sites in a fictitious network during the month of October 2017. (I use these 24 sites frequently in my articles. Do you think I should give this network a name and a more formal existence, such as a coverage area?) Download the workbook to follow along for yourself.
In the next sections, I’ll cover each step in detail. I’ll use the “Dropped Call Rate” as the performance statistic or KPI to override. The steps are pretty much the same for each KPI. Simply repeat for each the 3 steps for each KPI.
Create Exclusion Table
First, we’ll create a 2-column Exclusion Table. We’ll add to this table the troublesome sites, the ones having known, cannot-be-fixed problems. These are the site to exclude from Dashboards and recurring reports.
The first column of the Exclusion Table is the table index. Excel requires this to be a unique identifier, such as the site number. I’ll call this Site_ID.
In your network, that unique identifier might include the sector as well as the Site_ID. For this article, I’ll use only the Site_ID, to avoid getting bogged down figuring out how best to have a 2-part unique identifier, Site_ID, and Sector. Let me know in the comments if you think I should write about that in a separate #ExcelTelecomTricks article.
The second column in this lookup table will be a value which is used to override the troublesome performance statistic. I call this the “override value”. For “Dropped Call” rate, a lower value is better. So to override the raw value I’ve set the override value to 0. Note that this does not change the raw value in any way.
See worksheet “Exclusion Table” in the downloaded workbook.
To make this table easy to use, I made it a named range, Excluded_Drop_Rate. I’ll use the named range in the next step. This table will be used in an Excel VLOOKUP() function inside a Synthetic Field.
If you have several KPIs to which you want to apply this technique, you could create a separate exclusion table for each KPI. Then assign a name to each exclusion table.
Add Synthetic Field to Raw Input Table
Next, let’s add a Synthetic Field to the input table of raw data. The input table of raw data is where you routinely import the latest site performance statistics. I’ve done this in worksheet “Raw Performance Data” in the downloaded workbook.
This Synthetic Field contains an IFERROR() function which tests for this row’s Site_ID in the named range Excluded_Drop_Rate. If the Site_ID is present in the exclusion table, it means that this row is a troublesome site. So the override value is displayed in the Synthetic Field.
If the Site_ID is not present in the exclusion table, it means this is NOT a troublesome site, and the actual value of this KPI is displayed.
I already wrote all about Synthetic Fields. This Synthetic Field will be used by the Pivot Table in the next section to derive the “Top 10” list of sites.
The only thing left to do is to create a Pivot table which shows the Top 10 sites with the highest Dropped Call rates. I’ve actually created 2 Pivot Tables on worksheet “Pivot”.
Use Synthetic Field in Pivot Table
The Top 10 based on the first Pivot Table was shown earlier in the article. It’s shown again here on the left side of this image. It shows the Top 10 before the bad sites have been excluded.
On the right side of the image is the new Top 10. Gone are the previous Top 5. The sites previously occupying places 6-10 are now your top 5, and there are 5 new sites.
So now, your “Top 10” really is your top 10 addressable issues. Get to work!
Using Exclusion in Your Network
In your situation, you might want to apply this exclusion technique to several KPIs at once. To do this, apply the above 3 steps for each KPI. Create separate Exclusion Tables and separate Synthetic Fields. Each KPI will already have its own Pivot Table. I needed 2 Pivot tables for this article so I could show both before and after. You won’t need to do that.
After trying it yourself, if you still have questions or troubles, comment on this article, or send me a DM. I’ll be happy to help.
It’s up to you to incorporate this Excel Telecom Trick into your existing Dashboards and reports. I’ve shown Exclusion using a single performance statistic, or KPI, “Dropped Call Rate”.
Conclusion
Our objectives in this article were:
- Remove troublesome sites from our Top 10 Drop Call list,
- do so automatically, without requiring manual intervention, and
- without altering the underlying raw performance data
So, how did we do? I’ve shown you a simple way to satisfy all the above objectives. There is the one-time task of creating the exclusion table and Synthetic Field.
Once setup is complete no more work is required to exclude these sites. Whenever you have new performance data, simply paste it into worksheet “Raw Performance Data”, fill down to extend the formulas if necessary, refresh the Pivot Table, and that’s it!
Once again, here is the link to download the workbook.
If you’d like to see more of my Telecoms Tools and Career-Building tips, connect with me on LinkedIn
or follow me on Twitter