This past week, I was approached by an organisation to provide some data analysis on some particular metrics. I personally, at this point, enjoy ad-hoc data analysis. Given my previous experience as a teacher and music producer, providing a product or way to receive information is easier when the objective is extremely clean, measurable, and obtainable. I received an email with the following instructions and an Excel file:

‘simple line graph showing the number of placements each week as a percentage across the year.

maybe state by state graphs overlapping over the national? not sure yet. 

only need to use data from the following columns: number placed, state, and date of placement[.]’

Within the previous month, I had completed the udemy course Complete Introduction to Business Data Analysis.

Within this course, it featured the use and various applications of Pivot tables and charts. After reviewing the spreadsheet, I noticed a discrepancy between a column they suggested from another (that they didn’t), and suggested the change over for the data representation accuracy. Within this same meeting some draft drawings were depicted, to help assist the vision the brief required. As it turns out, the visuals were dramatically important as this information was going to be passed onto a Marketing team.

The data that I received was relatively clean; there were no nulls, incomplete, or mismatched data values that would impact the creation on the charts, yet there were many erroneous columns that would have taken up more processing time when adding the dimensions to the Pivot charts.

After receiving the additional, second year, Excel file I noticed that all of the columns weren’t in the same order. I took to PowerQuery to transform this newly acquired data set, named all of the changes, in the event that I might receive subsequent files with the same transformation requirements. They were interested in analysing the same months, across the two years, but averaging the numbers. Using this figure will allow them to provide an understanding to people new to their organisation the statistical probability of knowing certain information at differing times of the year.

Note: The third row of numbers are the averages to the first two numbered rows. However, since it is following Excel’s rounding rules, we see liberties taken for certain averages like (0%+7%)/2 = 3%, (8%+9%)/2 = 8%, and (30%+39%)/2 = 34%, etc.

Overall, the team was extremely happy and impressed with the turnaround time with this job. The variety in chart images allowed them to see the various depicted outcomes- pressing to the Marketing team their preferred style of imagery.

What I found extremely interesting was the fact that I immediately took to Pivot charts, given the few metrics that were necessary, over another process, like SQL to Tableau. However, given the time that I had available for this project, I still cleaned and processed the data using SQL, mainly to further develop some cleaning skills and develop a carry count metric.