‘Tis the Season for Reporting

James Cousins | Senior Statistical Analyst

‘Twas the week before break, when all through the college
Not a student was stirring, too filled up with knowledge
But for IT and IR, the work was not done
As year-end reporting always comes before fun

I was recently working with a customer, Robin DePolo, of Mount Vernon Nazarene University. We were working on preparing some data for a predictive model, but the conversation shifted to all of the other (countless) things that she needs to get done before she can kick back and enjoy the holiday season. Sadly, Robin didn’t mean that she had lots of decorating, or shopping to do either- she meant required reporting. And Robin’s not alone in her end of year crunch. To be fair, required reporting can be tedious, when it doesn’t have to be. There’s a better solution.

One of the ways to lessen the work load is by establishing a repeatable work flow. Shortly after my conversation with Robin, I had the opportunity to follow this advice. I was recently asked to help create a report that pulls data from social media sources. Social media holds so much promise for analytics and predictive modeling, but in a lot of cases, it can be difficult to gather the data and analyze it. So, my work began.

I set out with the goal of making it as automatic as I possibly could. We knew what we wanted to learn, and how we wanted to analyze the data. A lot needed to happen to the data before it ended up in a report though. Specifically, the data arrived preformatted, and not in a good way. So instead of manually moving and adjusting cells, I made use of Veera Construct’s “Fix Excel” node, which automatically trimmed out the extra columns and rows of our excel files. Now that I’ve set that process up once, it will always do the work for me- truly warming my heart in this chilly December weather.

The next task for us to tackle was to parse out tweets and statuses for any hashtags. With the transpose node, Veera Construct can systematically pull out hashtags from plain text tweets, meaning that I don’t have to trawl through every cell to manually type out new values. The result is more time saved and a second repeatable process. Another step closer to holiday bliss.

The Tweet we started with – notice it has two hashtags we want to pull from it:

This is the end result in structured format where the hashtags have been extracted:

While reporting is required for many different reasons, whether it be social media or the surveys and accreditation that Robin is doing for MVNU, automation and systematic data-prep make the task much faster. This will allow you a lot more time for all your holiday activities!

Best of luck with all of your end of year projects!