Work Related "Puzzle"

[Vince, Pt. II]

I have been given an odd request at work, and I don't quite have the data I need to pull it off. Given the data points I DO have, can you come up with a better way to represent the data to fulfil the request?

Request: represent the average number of days a customer had to wait for an appointment in 2016 by zip code. We are held to a standard of making a customer wait 'no more than 5 days out' for incoming appointments, though they are welcome to schedule future appointments up to a month out.

Data available (all on a per-order basis):

• Date appointment was requested.
• Date of appointment.
• Zip Code.
• Area.*

*We divide our service territory by 7 areas; unfortunately the areas are not defined by simply measurable geographic areas, so there is no easy way to tie "Area" to Zip Codes.

The main issue is that we have no way of determining whether an appointment was made as soon as it was available - customers will often schedule an appointment a month or so in advance. My concern is providing the most accurate data I can. Here are the two solutions being discussed now:

1. Appointment Date - Appointment Request Date = Days Out. Eliminate all of these over 10 days (arbitrary number selection, this is open to interpretation) to avoid MOST future appointment issues, then simply average the results on a zip code basis.

2. Aggregate all requests by date of request and city/area. Take the most common requested appointment date on that day and use it as the 'days out' number. For example, on 01/04/2016 the most requested appointment date was 01/07/2017 in San Francisco, so that would be '3 days out' for all San Francisco zip codes on 01/04/2017.

Answer #1 is much easier to implement, though the quality of the data is questionable.

Answer #2 seems much more accurate, but associating the results by Zip Code would be extremely hard to do.

We are looking at a total population of about 260,000 appointments.

[Vince, Pt. II]

On a related note, anyone here do data analysis professionally? I am learning on the job, and having a hard time walking the line between providing what the customer requests and telling them they are asking to look at the wrong data.

[Logan]

Originally Posted by Vince, Pt. II

On a related note, anyone here do data analysis professionally? I am learning on the job, and having a hard time walking the line between providing what the customer requests and telling them they are asking to look at the wrong data.

I don't, but when I read your OP I figured that either approach would end up resulting in the bolded thanks to how your company has set up their system.

[henry296]

Originally Posted by Vince, Pt. II

On a related note, anyone here do data analysis professionally? I am learning on the job, and having a hard time walking the line between providing what the customer requests and telling them they are asking to look at the wrong data.

I do work as a data analyst role. My first advice is to try to ask about the problem they are trying to solve and how the intended to use the results of the data. At that point, provide a recommendation on how you would approach solving that problem with the data that is available.

[Radii]

Originally Posted by Logan

I don't, but when I read your OP I figured that either approach would end up resulting in the bolded thanks to how your company has set up their system.

+1

[Vince, Pt. II]

I could write a small book about my issues with data storage, access, and database structure here.

[SplitPersonality1]

Originally Posted by henry296

I do work as a data analyst role. My first advice is to try to ask about the problem they are trying to solve and how the intended to use the results of the data. At that point, provide a recommendation on how you would approach solving that problem with the data that is available.

+2

I too work in a data analyst role and I agree 100%.

[Vince, Pt. II]

Thanks for the feedback, by the way.

Since I got some people's attention, any tips for a fledgling analyst?

By virtue of being here I have a slightly better than rudimentary understanding of statistics, but I am hungry to learn more. My primary analysis tools are Business Objects and Excel, though I have access to Tableau and am teaching myself as I go along (company isn't springing for formal training, so I'm on my own there). I have a few co-workers who are Lean Six Sigma trained, and have been trying to get some training of my own in that regard, but as of yet have been unsuccessful.

[Logan]

Keep teaching yourself Tableau. As a user, it's pretty awesome.

I know the data guys in my company are doing a lot with Agile Project Management if you're looking for something different.

[JonInMiddleGA]

Originally Posted by henry296

My first advice is to try to ask about the problem they are trying to solve and how the intended to use the results of the data.

While my work isn't technically "data analyst", almost everything I do personally involves analyzing some data in some aspect (and then making decisions based on that analysis).

I love Henry's quoted part as a good rule of thumb for a lot of situations really.

Tell me what you want to do (and hopefully why you want to do it) and then let me try to help you do that. There's waaaay worse approaches ... as long as the client relationship can handle you causing them to frequently reveal that they don't really know what they want to do, nor have the slightest idea how to do it.