Data Management and Visualization
I've had the pleasure of delving into the dynamic world of Data Management and Visualization. Throughout my course, I've honed my skills using an array of powerful tools including Microsoft Excel, Caspio, Google Analytics, and Tableau. Additionally, I've delved into the art of storytelling with data, enriching my understanding of its narrative potential. Here are the various projects I completed this quarter:
In this lab, I utilized the Faker Python Library to generate realistic datasets. Subsequently, I meticulously refined the data using key Excel functions including:
Implemented fundamental Excel functions including RIGHT, LEN, SUM, TRIM, subtraction, and concatenation.
Employed IF statements effectively.
Utilized SUBSTITUTE formula to consolidate address details into a single line.
Leveraged TEXT TO COLUMNS with delimiters to split information from one cell into two distinct cells.
Mastered LEFT and RIGHT formulas for data manipulation.
Applied Flash Fill for efficient data formatting.
Developed lists and filtered out contact information containing specific items from the list using formulas.
Download my files here:
To simulate paper type estimation for printing jobs, I harnessed the power of Excel functions to execute the necessary calculations for the estimation process.
Created and named lists/arrays.
Implemented data validation to craft intuitive menus.
Employed nested IF statements to execute calculations based on menu selections.
Utilized ROUNDUP function for precision.
Implemented robust error handling mechanisms such as IFERROR statements.
Download my files here:
For this lab, I learned VLOOKUP formulas to calculate costs of different types of ink. To accomplish this workbook, I:
Learned to implement VLOOKUP functions to search data in separate sheets.
Implemented data validation techniques.
Incorporated error handling strategies.
Utilized MATCH statements within VLOOKUP functions for enhanced data retrieval.
Used calculations for estimating ink prices.
Download my files here:
In order to complete this workbook for estimating book graphic design job orders, I:
Employed VLOOKUP formulas proficiently.
Developed lookup arrays and tables for efficient data retrieval.
Utilized nested IF statements for intricate logic.
Implemented data validation to construct intuitive menus.
Applied conditional formatting techniques to enhance data visualization.
Executed complex calculations to ascertain the pricing of each job.
Managed and worked across five distinct worksheets.
Deployed AND statements for nuanced conditional formatting.
Download my files here:
For this function point analysis workbook, I:
Mastered both VLOOKUP and HLOOKUP formulas to efficiently retrieve data.
Developed comprehensive lookup arrays and tables for seamless data access.
Employed nested IF statements to handle intricate logical conditions.
Utilized data validation tools.
Implemented advanced conditional formatting.
Conducted complex calculations to accurately determine the price of each job.
Utilized AND statements to refine conditional formatting rules.
Incorporated MATCH statements within HLOOKUP formulas for precise data retrieval.
Handled inputs, outputs, and storage (FTR) efficiently, while carefully monitoring hours and costs.
Download my files here:
Using Excel, I created a budgeting workbook to manage my expenses and financial decisions. To make the workbook, I:
Employed conditional formatting to highlight key insights.
Crafted intricate formulas, including AND statements, for advanced conditional formatting.
Established lists and applied data validation to streamline menu creation.
Leveraged IF statements to calculate budget balances accurately.
Download my files here:
Using 600 survey data inputs, I created 5 different pivot tables for different storytelling points of view. For instance, I created a pivot table for pizza preference by concentration and one for students' social media preference by year. To accomplish this, I:
Created a worksheet with university survey inputs.
Implemented pivot table feature for data.
Filtered pivot tables by rows and values.
Download my files here:
For this project, I had to find Digital Advertising calculation outputs based on provided input edge cases. To complete this task, I used formulas in excel:
Calculated cost per M impressions (ad costs per thousand times an ad is served)
Calculated units sold per M impressions (number of sales per thousand time an ad is served)
Calculated cost per acquisition (search cost for a single unit sale)
Calculated profit per M impressions (profit per thousand times ad is served after subtracting ad costs).
Download my files here:
For this task, I had to make a custom dashboard in Google Analytics to answer some questions about the Google Merchandise Store.
Found which dates had fewest visits from January 1, 2023 to January 31, 2023.
Found how many items were purchased during this period.
Found which item had the greatest revenue during this time period.
Found which Google Ad Campaign drove the most Google Ad Clicks to the store during this time period.
Used insightful information to gain comprehensive knowledge about Google Merchandise items.
In this lab session, I employed Tableau Prep to meticulously refine several datasets. Throughout the lab, I accomplished the following tasks:
Executed cleaning procedures on five distinct datasets.
Refactored field names and removed redundant columns/rows.
Consolidated three datasets through a union operation.
Conducted a pivot operation on a designated dataset.
Merged two datasets seamlessly.
Finalized the process with output procedures.
Following the meticulous data cleaning and preparation in Tableau Prep, I seamlessly transitioned the flow to Tableau Desktop, where I crafted insightful visualizations for the datasets. Throughout this lab, I achieved the following:
Acquired proficiency in Tableau Desktop fundamentals.
Leveraged publicly available Cal Poly data.
Constructed a visually engaging tree map to represent the largest graduating majors.
Applied principles of data storytelling to enhance the narrative.
Developed an informative bubble map showcasing the distribution of the largest majors.
During this lab, I handled a vast dataset comprising over 1,000 data points related to Airbnbs. Prior to visualization in Tableau Desktop, I diligently refined the data using a combination of Tableau Prep and Excel. Throughout the lab, I accomplished the following tasks:
Generated a comprehensive scatterplot illustrating the relationship between the number of beds and bedrooms in each residence.
Developed an informative bubble map highlighting the average number of bedrooms across different property types.
Constructed an interactive map showcasing the pricing distribution of Airbnbs across various locations.
Created a dynamic line plot depicting the trend in Airbnb prices from 2022 to 2024.