Ad Hoc Ergo Essential
A BI End User on Custom Reporting
07/06/2017 • by Nicole Hitner • 0 comments
What can one person — just one person — with ad hoc reporting capabilities do for a company?
Forrest Gordon works as a Data Analyst for McCloud Services, a mid-sized pest management company operating in several Midwestern states. McCloud manages its operations using PestPac, an industry-leading application developed by WorkWave, which includes a white labeled implementation of Exago BI called Report Writer.
In just one year, PestPac’s Report Writer has transformed Gordon’s career and saved his company tens of thousands of dollars. We interviewed him to learn how.
Tell me about your background and how you got your start at McCloud.
I went to school to get my associate’s in Engineering and started doing a little bit of programming before I took a position at McCloud as a service specialist. I held that position for about a year before switching into accounting.
I did accounting for about four months before my manager at the time went to a conference down in Florida and met one of the lead guys who handles the Report Writer aspect of PestPac. After the conference, she brought back just enough knowledge for me to hit the ground running. Since then, my role completely shifted from accounting to data analytics. We started slowly, unlocking what Report Writer and Exago can do, which ultimately created a position for me that the company didn’t really know it could fill, or had a need for, until we really started digging into the data we have.
So how long have you been an analyst?
Unofficially, since about March of 2016. February is when I started using Report Writer. I’d been used to PestPac for some months prior to that, but we didn’t start actually using Report Writer until about February. My role started shifting toward being more concentrated on data analytics in March, April, and May.
What does a typical day look like for you?
In the morning, I tend to get multiple requests for information that people have no other means of sourcing. Someone might ask for a whole group of accounts and say, “Hey, what’s our pricing looking like on these?” or, “How effective are the services we are performing?” Someone might come to me with a mistake that was made on a ticket and ask for me to determine how prolific an issue it is. Then I’ll hop in Report Writer, whip up a quick report, do some Excel magic, and send something back to him. In addition to pulling reports, there are other facets of my job that involve data analytics. I use other systems and analyze spreadsheets that come to me, but the lion’s share of it, especially about the middle of the month, is all report requests and requests to pull, mass-update, audit, or automate data. So, there are a lot of things I use Report Writer for. Sometimes the end result is just the report people are looking for, but a lot of times, it actually gives me data that I can then use to automate another part of someone’s job to make it easier.
What sorts of discoveries did Report Writer help you and your company make regarding operations?
Well, at first those discoveries were relatively simple. I was recreating some default reports that looked so bad in Excel that no one could use them. But then it started getting a little more complicated when we started doing things like market penetration analyses to see where we were strongest and where we had potential sales opportunities. I used Report Writer to pull location data and figure out what our most profitable sectors were. I also recreated reports that were being done manually and inefficiently, so I was able to improve upon a lot of those processes. Now reporting touches every facet of the business. If we have a question about how much it would cost to do something or a breakdown of the time spent on a project, Report Writer gives us a macro-level view that would otherwise be impossible to achieve.
Can you tell me about a time the data led you to a surprising insight?
That happens all the time!
I’ll give you a perfect example. In our system, we have these things called service setups, which basically create tickets prompting service specialists to perform services at a client location. There are two types of service setups: Invoiced and Production. Production just gives credit to the service specialist for being on site; it doesn’t send an invoice to the client. In cases where we only bill the client once per year, we use a Production setup to reflect the service specialist’s activity on non-billed visits. In one case, we found a client who had the wrong setup type and as a result, wasn’t being billed. We used this information, plus other common mistakes, to create reports that automatically audit accounts so that those mistakes never happen again.
The ability to catch these mistakes has had a huge impact on our ability to bill accurately.
We created a complete dashboard for auditing the 4,000 tickets that come in each week and have something like ten to twelve different issues that allow us to consistently flag, look into, validate, and release potentially problematic tickets.
How much money do you estimate your company has saved due to Report Writer?
Conservatively, tens of thousands of dollars per year.
How many reports do you have in your system?
This includes some test reports and such, but I have nearly 750 reports.
Not all embedded BI users have the ability to create their own reports from scratch. Can you tell me what having this capability means for you and for your work?
I find myself creating new reports from scratch daily, multiple times a day. Without that ability, I would not be a data analyst. Because there are always a lot of nuances to reporting, questions get more specific than default reports can really accommodate for. There are additional details that people want included, and other things they want excluded. If I’m trying to look up service production, a default report in PestPac might show me the service specialist code and how much they did each day, but it will be broken out into a bunch of categories that managers may or may not understand, and the report format won’t be friendly enough for them to delete what they don’t need and re-sum everything. Report Writer allows me to consolidate that entire process, set it and forget it. There are reports I scheduled out a year ago and haven’t heard complaints about.
What’s your favorite thing about Report Writer? Your least-favorite thing?
It’s hard to say what my favorite thing about it is because it’s very well put-together, and there’s not much about it that I don’t like. But I guess some top things would be how easy it is to probe different data points and summarize them. There’s just a lot of macro-level view stuff you just can’t do otherwise. I also like that there’s the option to do a lot of conditional row suppression and logical formulas, which help supplement filtering. Some gripes I have, though, are that cell references don’t update intelligently when you copy formulas over to new cells, and you cannot reference a cell from another report.
How long did it take you to learn Report Writer, and what advice do you have for anyone learning to use it for the first time?
It took me a few weeks to become proficient at using Report Writer and only a few months to feel completely at home in it. I would say that the two biggest things a beginner should learn are conditional functions and how they can be used in conjunction with aggregation.
Gordon continues to cultivate data-driven insights for McCloud and plans to explore one of Report Writer’s newer features, ExpressView, in coming weeks. He looks forward to updates to the Advanced Joins menu, which will soon include graphical representations of entity relationship diagrams.