Breakdown projects by skill (i.e., “product design”); build visualization to show Average project cost and average project timeline for top 10 most common projects

The key thing was to understand where the data about roles is stored and how we get to them starting with the project table. There are a couple of ways to get to the roles from projects from initial though:

Using the contractor’s ID to join to the role table and find roles associated with the contractor

Using the client’s ID to join to the role table and find roles associated with the client

Using the term’s author user ID to connect to the role table and find the roles associated with that term

I believe the best approach is to use the contractor’s ID table because when a contractor is hired, they are hired majorly for their skill set and those skills (roles) would be the best way to assign a skill to the project they are working on.

A new CTE called top_skills is used to extract the top 10 skills based on the total number of projects that are associated with a given skill. The way the data is structured, it is possible for projects to be associated with multiple skills and since there is no way to tell if a contractor was hired for their primary role, I decided to not filter on that column (that would help with associating only one skill for the project). Once the top 10 skills were established, I used that to gather the project IDs for the projects associated with those skills using the ‘top_projects’ table.

For calculating the average cost, ‘paid_project_fee’ table was used as that has the amount that each project costs. Whereas, the duration (timeline) was calculated using the time difference between when the project starts and ends.

The best way to show these I believe is the above scatter plot. The Y axis shows the average cost in dollars and X axis shows the average time for each skill’s projects.

Insights

Projects with a higher cost take more time to finish on average

The average cost is clustered together in the $1000-$1500 range

Graphic design projects lost the least on average but are also the quickest on average

Logo Design takes the most time and has the highest average project cost (in the top 10)

Product Design is a little away from the norm that it is on average being completed within two weeks but also has a higher than average cost