2023 Week 17 | Open IT Tickets

Introduction

This week we’re going to be building a recipe to create a dataset that we’ll use in a future dashboard. The focus is on IT tickets. We have a data set with information about open & closed tickets and some SLA data. We’re going to be combining these together in CRMA. 

Before we do that, we want to think about how we want to visualize our data. A common problem with data in this format is that it’s difficult to see how many open tickets we have at any given point in time, so we must scaffold our ticket data. If you are unfamiliar with scaffolding, there are plenty of resources out there to help you. This should be a fun data prep challenge!

Requirements

• Import all three datasets into your CRMA instance.
• Focus on the tickets data first, there is some cleaning we need to do:
  • Our department_id field contains a department code and ticket_id, we must separate these and form full department titles
  • Our ticket owner field is incorrect, we need to parse out the full name of the ticket owner from the owner_email field
  • Finally, dealing with the dates. First, we must determine whether a ticket is open or closed. Next, if a ticket is open, we want our close date to become ‘today’ 
• Once our cleanup is done, you need to find a way to scaffold your data set, so we have a row for every day a ticket is open.
• We must also combine our SLA data to calculate whether the ticket was within the SLA or not.
• You should ultimately end up with 20759 rows and 11 columns, you can see the output in this Google Sheet.

The resulting dataset should look something like this… happy data prepping!

Tipps

Tipps…

Dataset

This week you can download the three data sets from data.world.

Share

After you finish your workout, share a screenshot of your solutions or interesting insights on Twitter or LinkedIn using the hashtags #WOW2023 and #CRMA and tag @genetis, @PreenzJ, @LaGMills and @JaackParry. (Or you can use this handy link to do that)

Also make sure to fill out the Submission Tracker to track your progress and help us judge the difficulty of our challenges.

Solution

Coming soon…