I am a Data enthusiast and a fresher seeking to build a career in a Data analyst role. Experience in Excel and Python

Harshal Kasat  • Peerlist

The INC 5000 Companies 2019 dataset is a comprehensive collection of information about each company listed on the 2019 edition of the prestigious INC 5000 list. The INC 5000 list is an annual ranking of the fastest-growing private companies in the United States, and the 2019 edition includes companies from a wide range of industries, including technology, healthcare, manufacturing, and retail.The dataset consist of 5013 rows and 11 columns which includes several key fields of information for each company, including the company name, industry, founding year, website, and location. In addition, the dataset provides valuable insights into the financial performance and growth of each company, with fields for 2019 revenue, % growth, number of workers (year-over-year), and the number of years on the list. The dataset provides many possible areas of analysis that includes relationship between revenue growth and staffing changes, identification of trends and patterns in the growth and performance of private companies in the United States and to distinguish the companies that are outperforming their peers and gaining a competitive advantage in their markets.RECOMMENDED ANALYSIS :<ol><li>What's the average revenue among companies on the list? Broken down by industry?</li><li>Which industries are most and least represented in the list?</li><li>Do you notice any interesting geographic trends?</li><li>Which industries saw the largest average growth rate?</li><li>Which companies had the largest increase in staff/new hires?</li><li>Did any companies increase revenue while reducing staff?</li><li>Which is the most frequent state appearing in Top 1000 values of companies?&nbsp;</li></ol>CONCLUSION :Through analyzing this dataset, we can explore various insights and trends related to company growth, staff discrepancy, and revenue increase. Using Excel, we can conduct analyses on companies and industries based on their name, revenue, % growth, workers, and location. The specific findings and conclusions will depend on the recommended analysis questions and goals of the analysis.

INC 5000 Companies 2019

For this project, I analysed a dataset provided by Rotten Tomatoes, which consisted of ratings and reviews of more than 15,000 movies that had been reviewed by the website Rotten tomatoes&nbsp;(source: Kaggle). Each record contains information about the movie (title, synopsis, rating, genre, cast, directors, studio, runtime, etc.), along with highlighted reviews, "Tomatometer" status, and audience ratings. The "Tomatometer" status is a unique feature of Rotten Tomatoes, which is a rating system that aggregates the reviews of professional critics to produce a score representing the percentage of positive reviews. The audience ratings, on the other hand, represent the average rating given by the general public. With this information, it is possible to conduct various analyses on the dataset. The dataset consist of 16,614 rows and 17 columns. Overall, the dataset provides a rich and diverse collection of information about movies that can be used to explore various recommended analysis questions, such as the relationship between ratings and box office success, the impact of different genres on ratings, and the influence of cast and directors on movie ratings. The dataset also provides opportunities for developing predictive models to forecast the success of a movie based on its details and ratings.RECOMMENDED ANALYSIS ON THE DATASET :<ol><li>What does the distribution of films look like by rating? By primary genre? </li><li>What % of films received a Certified Fresh Tomatometer rating? What about Rotten?</li><li>Explore new film releases over time. How has the volume of releases by month trended over time? What year/month were the most new films released?</li><li>Compare average Tomatometer ratings by Studio. Which studios produce the highest rated films, on average? The lowest?</li><li>Compare the Tomatometer ratings against audience ratings. Which films showed the largest discrepancies between audiences and critics?</li><li>Explore the critics consensus rating: what language is used most often?</li></ol>CONCLUSION :The dataset provides a wealth of information that can be used to gain insights into the movie industry, and to develop predictive charts that can inform decision-making in the field. Therefore, the findings of such an analysis can have important implications for the movie industry and help inform decision-making related to movie production, marketing, and distribution.

Movie Ratings

For this project, I analyzed a dataset provided by a telecom company (source: Maven Analytics) that included information about their customers such as demographics, location, services, usage patterns, and churn status. The dataset had a total of 7,043 customer records of California, out of which 1,869 customers had churned, 454 customers had joined recently in the last quarter, and the rest (4,720) had stayed with the company.To start my analysis, I first cleaned and preprocessed the data to remove any missing values, duplicates, or irrelevant data. I then performed exploratory data analysis to gain insights into the characteristics of the customer dataset, including their demographic profiles and usage patterns. Next, I conducted a comparative analysis of the customers that churned, joined, and stayed with the company. I looked at key demographic factors such as age, gender, marriage, income level, and location.I also identified the key drivers of customer churn by performing a correlation analysis between various customer attributes and churn status. This helped me to understand which factors were most strongly associated with customer churn feedback, such as Attitude, Competitor, Dissatisfaction, Price and other types of drivers.Furthermore, I analyzed the reasons for customer churn by creating a pivot table with a pie chart that showed the top reasons why customers churned. These reasons included dissatisfaction with the quality of service, pricing, competitor's bonanzas offers and customer support.I also conducted a geographical analysis to identify the top cities in California that churned, which helped the telecom company to focus their retention efforts on customers in those areas.Finally, I analyzed the effectiveness of retention offers by identifying which offers were most preferable to customers who were at risk of churning. This analysis helped the telecom company to develop targeted retention strategies and improve their customer retention rates.RECOMMENDED ANALYSIS :<ol><li>How many customers joined the company? How many customers joined?Ans : "454 customers" joined the company during the last quarter (Q2-2022) in which male count was 243 and female count was 211. The analysis was carried out on the basis of tenure of months, customer status and gender.</li><li>What is the customer profile for a customer that churned, joined, and stayed? Are they different?Ans : Customer profile is dependent on its gender, age, married, unmarried and customer status data for a customer that churned, joined and stayed. They are different from each other on the basis of the charts been showed. In churn data, 1869 customers have churned whereas in joined data, 454 customers have joined and lastly, in stayed data, 4720 of customers have been styed for a long time and also being loyal to the company. Other part of analysis is been showed in churned, joined and stayed section.</li><li>What seem to be the key drivers of customer churn?Ans : Attitude, Competitor, Dissatisfaction, Price and other types of drivers are been responsible for Customer churn but the key drivers from the data we get is "Competitor". There can be many reasons like services, offers, bonuses, etc.</li><li>Is the company losing high value customers? If so, how can they retain them?Ans : As per the revenue generated from the data, we come to know that around 1869 (34%) of the customer have churned from the total of 7,043 customers. So, somehow it is in big percentages and we can say the company is loosing high value customer(not that much). The company can retain it by giving better referral bonuses, range of support, good offers with greater cashbacks, etc.</li><li>Out of the 3 customer status, stayed, churned and joined, which has the highest percentage?Ans : Out of the 3 customer status, stayed, churned and joined, "Stayed (67%)" customer status has the highest%.</li><li>What payment method was preferred by churned users?Ans : "Bank Withdrawal" payment method was preferred by churned users.</li><li>What churn offers were more preferable by the customers?Ans : Total churn customer count was 1869 in which 1051 customers didn’t respond or preferred to the offers. So, from the data we received, we can say that 818 customers preferred for offers in which "Offer E" were more preferable by the customer</li></ol>CONCLUSION:The telecom companies can use data analytics algorithms to identify customers who are at a higher risk of churn and implement targeted retention strategies. Reducing customer churn is critical for telecom companies to maintain their customer base and revenue. By implementing effective retention strategies, telecom companies can improve customer satisfaction, loyalty, and ultimately, their bottom line by doing the perfect analysis for their loyal customer (few enhancement) and preparing to join or buy their services.Overall, this Excel project provided valuable insights into the customer churn behavior of the telecom company and helped them to identify key areas for improvement in their retention efforts.

Telecom Customer Churn

BACKGROUND :The purpose of marketing campaign is to reach and engage with a specific target audience and persuade them to take a desired action, such as purchasing a product, signing up for a service, or increasing their loyalty to a brand. The problem of this project is to explore and analyze the data to discover key understandings such as various channels performance, products performances, customers visit and which marketing campaign was more successful. Thus, the problem is quite tactical and a big project which can be solved by given data from source Maven Analytics.SOLUTION :Marketing campaign data of 2,240 customers of Maven Marketing were given. The problem can be solved by taking use of excel to carry out the recommended analysis. All the analysis are executed on basis of customer profiles, product preferences, campaign successes/ failures, and channel performance which can be solved in excel. So, that any person can calculate the result of previous year’s campaign and judge weather to visit, buy or not.METHODOLOGY : Final data should be calculated and easy to understand is the main focus behind the work of this project<ol><li>Firstly, you have to bring all the data in one excel worksheet and find out the null values or outliers present in marketing data.</li><li>After doing this, find out the factors which are significantly related to the number of web purchases by considering better oriented approaches for this.</li><li>Then, carry out the calculation for remaining recommended analysis using via pivot table by using various excel functions.</li><li>Hence, a dashboard is prepared in which all the recommended analysis part is calculated and solved in excel.</li></ol>GOALS &amp; KPIs : The success of my project is measured and carried out in following goals which includes:<ul><li>Goal 1: Appropriate result on marketing campaign carried out in previous years.</li><li>Goal 2: Gives better results on various channels or medium performances (going good or bad).</li><li>Goal 3: The results show how an average customer looks like and come on our channels.</li><li>Goal 4: It help us to know which product has been performed well in last two years of the campaign.</li></ul>EXCEL CONCEPTS USED :<ul><li>Concept 1: COUNT, COUNTBLANK</li><li>Concept 2: SUM</li><li>Concept 3: NESTED SORTING</li><li>Concept 4: PIVOT TABLE, GROUPING</li><li>Concept 5: MAX, MIN</li><li>Concept 6: AVERAGE</li></ul>RECOMMENDED ANALYSIS :After working on this datasets, we can conclude the following things:<ol><li>Are there any null values or outliers? How will you handle them?Ans: There are total "24" null values or outliers present in one specific column i.e. "Income" column throughout the dataset. It can be found by selecting all the columns and using "COUNTBLANK" formula. It can be handled by using winsorization method which involves replacing extreme values with the nearest non-outlier value or mean, median &amp; mode methods or it can be directly replaced with 0 values.</li><li>What factors are significantly related to the number of web purchases?Ans: Factors such as customer's "Birth year, Education level, Marital status &amp; Income" are significantly related to no. of web purchases.</li><li>Which marketing campaign was the most successful?Ans: "Last Campaign" has more responses than other campaign. Therefore, Last campaign was the most successful.</li><li>What does the average customer look like?Ans: An average customer looks like a "Married, graduated" type person &amp; lies in between the Birth year range of "1973-1992".</li><li>Which products are performing best?Ans: In last 2 years, "Wine" products are performing best.</li><li>Which channels are underperforming?Ans: "Deal &amp; Catalog" purchases channels are found to be underperforming.</li></ol>CONCLUSION :Anyone can process this data and make an understandable campaign result dashboard. The result showcases better product performances, channel purchases execution, campaign responses and customer signing engagement. Therefore, it helps the customer to know the insights of any marketing campaign and would also get awareness of such campaign carried out if such kind of data being provided.NOTE: If viewers endeavor to open the Google Drive link towards the excel spreadsheet, some graphs and data will be scattered or disappear due to the features&nbsp;not being available on it. Simply download the file and you will be able to see the data and graphs from my project work.

Marketing Campaign Results

BACKGROUND :Airbnb Inc. is an American company that operates an online marketplace for lodging, primarily homestays for vacation rentals, and tourism activities. The problem of this project is to explore and analyze the data to discover key understandings such as better city option to travel, trends or seasonality as per the review and major differences as per the listing &amp; review prices. Thus, the problem is quite tactical and a big project which can be solved by given data from source Maven Analytics.SOLUTION :A years of listing and reviews data from customer insights are given within the range of year 2009-2021 from an online lodging company. The problem can be solved by taking use of excel to carry out the recommended analysis. All the analysis are carried on basis of cities, listing price, review scoring result, review score on location and value which can be solved with the help of pivot table in excel. So, that any person can travel to any city by calculating the city wise, price wise and overall review wise data to know which city is best to travel in which season on excel.METHODOLOGY &amp; PROJECT SCOPE : Final data should be calculated and easy to understand is the main focus behind the work of this project<ol><li>Firstly, you have to bring all the data in one excel worksheet and carry out data cleaning in listing and review data given.</li><li>Then, figure out the unwanted data, exclude it from the listing excel and its dictionary data provided, which would helpful to carry out the solution for the recommended analysis problems.</li><li>Then, carry out the calculation using pivot table via using various excel functions like COUNT, AVERAGE, MAX function and using some nested sorting and filters options to solve the problems.</li><li>Hence, a dashboard is prepared in which all the recommended analysis part is calculated and solved in excel.</li></ol>GOALS &amp; KPIs : The success of my project is measured and carried out in following goals which includes:<ul><li>Goal 1: It helps us to know which city is better option to travel.</li><li>Goal 2: Show’s an understandable charts for different reviews attributes.</li><li>Goal 3: It helps us to know the trend of seasonality, in which month to travel in which city.</li><li>Goal 4: It help us to understand the price difference as per the city</li></ul>EXCEL CONCEPTS USED :<ul><li>Concept 1: COUNT, AVERAGE</li><li>Concept 2: NESTED FILTER</li><li>Concept 3: NESTED SORTING</li><li>Concept 4: PIVOT TABLE, GROUPING</li><li>Concept 5: MAX</li><li>Concept 6: Data Cleaning</li></ul>RECOMMENDED ANALYSIS :After working on this dataset, we can conclude the following things:<ol><li>Can you spot any major differences in the Airbnb market between cities?Ans: Major differences in the Airbnb market between cities is been carried out by its "review_score_ratings" data. From the dataset, "Paris" has major difference as compared to other cities in terms of overall review ratings. </li><li>Which attributes have the biggest influence in price?Ans: "Cities" attribute have the biggest influence in price. From the dataset, we can say that Airbnb price listing is done between "Rs. 0-99,999" for major cities.</li><li>Are you able to identify any trends or seasonality in the review data?Ans: There is trends of seasonality in the month of "September - December" from the year of 2009 - 2021.</li><li>Which city offers a better value for travel?Ans: "Paris" city offers a better value for travel.</li></ol>CONCLUSION :Anyone can process this data and make an understandable listing and review data of any online lodging or home staying company for better stay in affordable city as per the customer requirement. It also helps in commencing the better overall review scoring result, to know which city, stay place, season and price is better option to travel. Therefore, it helps the customer to know the insights (reviews &amp; listings) of that company if such kind of data being provided.NOTE: If viewers endeavor to open the Google Drive link towards the excel spreadsheet, some graphs and data will be scattered or disappear due to the features&nbsp;not being available on it. Simply download the file and you will be able to see the data and graphs from my project work.

Airbnb Listings & Reviews

BACKGROUND :“A year's worth of sales from a fictitious pizza place” in which various data are given like date and time of each order, type of pizzas served and some of additional details with it. The reason behind solving this problem is to know how many customers visit &amp; order each day, what is the peak hours, which pizza is one of the bestseller and how much sales is been carried out each month of year 2015. The problem is quite tactical and a small project which can be solved by given data from source Maven Analytics.SOLUTION : A year's worth of sales data from customer insights are given from a fictitious pizza place. The problem can be solved by taking use of excel to carry out the recommended analysis. All the analysis are dependent on each other and can be solved with the help of pivot table in excel. So, that any person can calculate the sales of any pizza place on excel.METHODOLOGY &amp; PROJECT SCOPE : Final data should be calculated and easy to understand is the main focus behind the work of this project<ol><li>Firstly, you have to carry out the data dictionary from the source which includes 4 different table, different fields and its description.</li><li>According to this data dictionary, we can solve the recommended analysis part from the other data provided.</li><li>For execution of calculations part we need primary data, for this a data window is presented which collects the data for customer orders details, pizzas types, date, time, size and price of pizzas and then, calculations are carried out on excel.</li><li>From each data collected we can make required pivot table from it and use excel functions to solved the calculation part and then create a understanding pivot charts for the recommended analysis part of this project to provide solution.</li><li>Hence, a dashboard is prepared in which all the recommended analysis part is calculated and solved in excel.</li></ol>GOALS &amp; KPIs : The success of my project is measured and carried out in following goals which includes:<ul><li>Goal 1: Making a sales data understandable.</li><li>Goal 2: Can calculate the peak hours, bestsellers and seasonality of pizzas from the data.</li><li>Goal 3: It can calculate the revenue of any pizza place from its sales data.</li></ul>EXCEL CONCEPTS USED :<ul><li>Concept 1: COUNT, AVERAGE</li><li>Concept 2: SUMPRODUCT</li><li>Concept 3: NESTED SORTING, FILTERS</li><li>Concept 4: PIVOT TABLE, GROUPING</li><li>Concept 5: MAX, MIN</li></ul>RECOMMENDED ANALYSIS :After working on this dataset, we can conclude the following things:<ol><li>How many customers do we have each day? Are there any peak hours?Ans: It has around 60-65 customers each day. Yes, there are peak hours which is in between 12 PM - 1 pm &amp; 5 pm - 7 pm.</li><li>How many pizzas are typically in order? Do we have any bestsellers?Ans: By counting and averaging the Order_details_id (Unique identifier for each pizza placed within each order (pizzas of the same type and size are kept in the same row, and the quantity increases)) and Orders_id (Unique identifier for each order placed by a table), we come to know that, there are typically 3 pizzas in order and Big_Meat_S pizza is found to be one of the bestseller.</li><li>How much money did we make this year? Can we identify any seasonality in the sales?Ans: In 2015, total revenue of 8,01,945 USD was generated. It has been found that a drastic changes occurs in month of October (12.32%), November (14.76%), December (15.12%). Therefore, "oct-nov-dec" are been identified as an seasonality in sales.</li><li>Are there any pizzas we should take off the menu, or any promotions we could leverage?Ans: The pizzas which sold between the range of 1-500 pizzas in year 2015 should take off the menu, or for these pizzas promotions should be leveraged.</li></ol>CONCLUSION :Anyone can process their data and make an understandable sales data of any pizza company to know how many particular customers order each day, which type of pizza is the bestselling of that company, and how much revenue is been generated from its sales data. This data can be beneficial for customers, as they can make informed decisions about their orders based on the popularity of certain menu items and the company's overall sales performance. Therefore, it helps the customer to know the insights of that company if such kind of data is provided.NOTE: If viewers endeavor to open the Google Drive link towards the excel spreadsheet, some graphs and data will be scattered or disappear due to the features&nbsp;not being available on it. Simply download the file and you will be able to see the data and graphs from my project work.