top of page

Company Scorecard Data Analysis

Introduction:

This was a project for my IT Data Analyst position. The data is about company engagement with the University of Arizona in the years 2020-2024 via 16 contributions like dollars donated, interviews, career fairs, contracts, etc. The data for 2024 is the only real data, the rest of the years contain fake data generated by me in Excel. The main reason is because we only had real data for 1 year and needed more years in order to effectively analyze the data and create dashboards; then when the real data for 2020-2023 is available, I would have simply changed the data source to the real data. The main point of this analysis was to determine the total engagement score (out of 10) for each company across the 5 years. The project also aimed to determine what components affect this score for each company. This data consists of 1 worksheet which contains 28,000+ records.

Insights:

There were many important insights to uncover from analyzing this data:

  • What's the relationship between total dollars donated and total number of engagements (out of 16) for each company?

  • What's the 5-year trend for the engagement score (out of 10) for each company?

  • What's the year to year percent difference of the engagement score for each company?

  • Which companies have a number of engagements that's above average?

Tools:

This project was analyzed using SQL, Tableau, and Python:

  • SQL was used because I wanted to further analyze the data using the complex clauses to derive more insights. I started by cleaning the worksheet which included changing table and column names and adding new columns. Then I outlined a total of 11 questions and wrote queries for each one. The clauses used included: SELECT, FROM, WHERE, LIKE, GROUP BY, AVG, HAVING, ORDER BY, LIMIT, SUBQUEIRES.

  • Tableau was used because it was requested by my supervisor and stakeholder. I started by importing the Excel workbook. I created a total of 6 worksheets with visuals comprised of scatter plots, line graphs, text tables, and stacked column charts. Then I created a dashboard which I presented to 1 stakeholder interested in the 5,730 companies that engaged with the University of Arizona.

  • Python was used because it was requested by my supervisor due to its efficiency and large amount of libraries. The data was already clean from SQL, so I started analyzing the data by outlining a total of 6 questions and used Pandas to answer them and Matplotlib to visualize them. The main Pandas syntax used were groupby and sort_values. The main Matplotlib visuals used were bar graphs and line graphs.

Impact:

​The impact from this analysis was that stakeholders were able to understand the various aspects of the relationship between a company and the University of Arizona. They can identify key trends and high-performing companies for targeted outreach, optimized resource allocation, and overall improved decision-making. The stakeholders were able to make key decisions regarding which companies to reach out to because they were able to view the company's engagement score, and things like total dollars donated and other components that contribute to the overall score.

© 2025 by Mohammad Shiha. Powered and secured by Wix.

bottom of page