Corporate Data Analyst | Business Analyst | Leadership Skills | Self-motivated Quick learner | Good team player 


github

Aatif Khan  • Peerlist

Data Analytics professional at Schenider Electric having 2+ years of experience with Excel,SQL, Python,Tableau

<h3 class="rich-heading">Rotten Tomatometer Movie ratings Analysis</h3>" To provide detailed information in regards to movies' critic reviews and the users' vs critics' ratings, and it can be combined with other movie datasets publicly available”&nbsp;<h3 class="rich-heading">Tomatometer Movies Ratings</h3>&nbsp;<h3 class="rich-heading">Summary:</h3>This report analyzes a dataset containing ratings and reviews for 15,000+ movies reviewed by Rotten Tomatoes. The dataset includes film details, featured reviews, "Tomatometer" status, and audience ratings. The analysis reveals insights about the distribution of ratings, genres, studios, and release dates, and discrepancies between Tomatometer ratings and audience ratings. The report concludes with recommendations for studios, filmmakers, and critics.&nbsp;<h3 class="rich-heading">Key Findings:</h3><ul><li> The Tomatometer ratings count between 1 to 100 and the highest count is 6137 under the "R" rating category.</li><li>The comedy genre count is the highest among others, with a count of 3540. 43.47% rating received from certified fresh, and 17.77% ratings Received from rotten.</li><li> The highest movies released in 2014 and the count is 743, and October month has the highest count for new release movies, with a count of 85.</li><li>There are discrepancies in some movies between Tomatometer rating and audience ratings.</li><li>The lowest rating studio is 59 movies count and highest rating studio is 176 movie count.</li><li>From 8309 critics consensus comments, 62% that is 5148 is positive.</li></ul>&nbsp;<h3 class="rich-heading">Insights:</h3>The analysis reveals that comedy is the most popular genre among moviegoers, receiving the highest count of ratings. Filmmakers and studios should consider investing in comedies to attract more audiences. The analysis also shows that October is the month with the highest count of new releases, indicating that moviegoers are more likely to watch movies during this month. This insight can help studios and filmmakers plan their release schedules accordingly.&nbsp;The analysis also reveals that there are discrepancies between Tomatometer ratings and audience ratings. Filmmakers and studios should take into account the opinions of both critics and audiences when evaluating the success of their movies. Additionally, the low rating of Sony Pictures Home Entertainment indicates a need for the studio to improve the quality of its movies.&nbsp;<h3 class="rich-heading">Conclusion:</h3><ul><li> Identify patterns and trends in the data, such as the highest count of ratings for the comedy genre and the discrepancies between Tomatometer ratings and audience ratings for some movies.</li><li> Identified the lowest rating studio and the highest rating studio, which can help studios benchmark their performance and improve their ratings.</li><li>Overall, the analysis provides valuable insights into the factors that contribute to high Tomatometer ratings for movies in the "R" rating category and can help studios improve their performance and achieve greater success in the movie industry.</li></ul>&nbsp;&nbsp;&nbsp;

Tomatometer Movies Ratings

Problem &amp; background&nbsp;A Telecom Customer Company providing a data related to their customers (Data Source- Maven Analytics). The data includes a unique Id for each &amp; every customer called as Customer ID, demographic information of the customers, and various services used by the customer, customer’s monthly charges and average revenue generated by the customer, also provide data about customer’s status I_e Churned, Stayed, Joined. The Data provided is recorder within the last quarter Q2-2022.Here we are going to analyze the data, clean the data and then use it to generate insights. Here we suppose to find out is the company losing high value customer and will also provide some suggestion to reduce the churning. Will also sneak deep to understand which are the top 12 cities where the customer churning is high and what will be the major reason for&nbsp;SolutionI will prefer to use Microsoft excel to clean the data, transforming into desired data and visualize it. The data we are using is comparatively small and can be handle by Microsoft Excel hence it is most easy choice to select within different software. We will use slicer, Pivot table and various charts to generates insights and a good Looking dashboard.&nbsp;Methodology &amp; Project scopeOur final motive of this analysis is to calculate and answer some of the analysis questions and make this calculation logical and understandable.•&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; • Firstly, the most important thing is to prepare a data dictionary, without understanding the data dictionary, it will be difficult to know about data, understanding the column names and what actual story the data tells you, this can only happen after knowing the data dictionary•&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; • Data dictionary contains 3 columns and a lot of important information about the data.•&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; • For initializing the calculation or analysis part, I am going to generate pivot tables and will perform required mathematical operations to understand the data and generate insights.•&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; • Visualization is generated right after the pivot table and also answered some of the important questions of my analysis.&nbsp;Goals &amp; KPIs● Goal 1: Create data understandable● Goal 2: Creating pivot tables and generate a structured data by using which we can visualize the data and generate insights● Goal 3: Visualizing the data and answering the question on the basis of our analysis.Concepts UsedWhich concepts have you used which have been taught in the Module (Any formula or technique)?● Concept 1: PIVOT Table, Filters, Slicers● Concept 2: Grouping, Average, Max, Min● Concept 3: ChartsConclusionThis analysis will help the Telecom company to know more about their customer, why customer churn is happening and also will provide some crucial suggestion to minimize the Customer churn.

Telecom Cutomer Churn

<h3 class="rich-heading">Problem &amp; background</h3>A Telecom Customer Company providing a data related to their customers (Data Source- Maven Analytics). The data includes a unique Id for every each customer called as Customer ID, demographic information of the customers, and various services used by the customer, customer’s monthly charges and average revenue generated by the customer, also provide data about customer’s status i.e. Churned, Stayed, Joined. The Data provided is recorder within the last quarter Q2-2022.Here we are suppose to analyze the data, clean the data and then use it to generate insights. Here we suppose to find out is the company losing high value customer and will also provide some suggestion to reduce the churning.&nbsp; Will also sneak deep to understand which are the top 12 cities where the customer churning is high and what will be the major reason for<h3 class="rich-heading">Solution</h3>I will prefer to use Microsoft excel to clean the data, transforming into desired data and visualize it. The data we are using is comparatively small and can be handle by Microsoft Excel hence it is most easy choice to select within different software.&nbsp; We will use slicer, Pivot table&nbsp; and various charts to generates insights and a good looking dashboard. Methodology &amp; Project scopeOur final motive of this analysis is to calculate and answer some of the analysis questions and make this calculation logical and understandable.<ul><li>Firstly, the most important thing is to prepare a data dictionary, without understanding the data dictionary, it will be difficult to know about data, understanding the column names and what actual story the data tells you, this can only happen after knowing the data dictionary</li><li>Data dictionary contains 3 columns and a lot of important information about the data.</li><li> For initializing the calculation or analysis part, I am going to generate pivot tables and will perform required mathematical operations to understand the data and generate insights.</li><li>Visualization is generated right after the pivot table and also answered some of the important questions of my analysis.<h3 class="rich-heading">Conclusion</h3></li></ul>This analysis will help the Telecom company to know more about their customer , why customer churn is happening and also will provide some crucial suggestion to minimize the Customer churn.

Telecom Customer Churn

Pizza Place Sale Detail Analysis:-<ul><li>Background: Year 2015 worth of sales from a fictitious pizza place, including the date and time of each order and the pizzas, served, with additional details on the type, size, quantity, price, and ingredients.</li><li>Solution: A year’s worth of sales data from customer &nbsp;are given from fictitious pizza place. The problem can be solved in MS Excel workbook to carry out the recommended analysis. All analysis can be solved with the help of pivot table.</li><li>Aim : find out the required analysis in easy way and less number of steps, &nbsp;</li><li>Excel Tolls used: COUNT, AVERAGE, MATCH, Pivot Table and Pivot chart, VLOOKUP</li><li>&nbsp;Data given: Data is given in 4 tables with columns</li></ul>1) Pizza( Pizza_id, Pizza_name, size, price)2)Pizza_type ( pizza_id, pizza_type, ingredient)3) order:- (order_id, date, time, quantity)4)order_detail:-(order_detail_id, Order_id, pizza_id_quantity<ul><li>Recommended analysis:</li></ul>1) How many customers do we have each day? Are there any peak hours?2) How many pizzas are typically in order? Do we have any bestsellers?3) How much money did we make this year? Can we identify any seasonality in the sales?4) Are there any pizzas we should take off the menu, or any promotions we could leverage?<ul><li>Solutions:-</li></ul>1) How many customers do we have each day? Are there any peak hours?Analytics:-As an average 60 pizzas can sales each day and peck hours are 12&nbsp; Pm to 1pm normally at lunch time and 5pm to 6pm at eveningTables Used: OrderStep 1:-Pivot chart is inserted on all data set and set date in row and order_id&nbsp; in values and count function is applied on order_id and = AVERAGE() function used to find the average pizzas sale each day. which show that the approximately 60 pizzas sold per day.Step 2:- Pivot chart is inserted on all data set and set time in row and order_id&nbsp; in values and count function is applied on order_id and&nbsp;&nbsp; insert pivot chart which show that 12&nbsp; Pm to 1pm normally at lunch time and 5pm to 6pm at evening2)How many pizzas are typically in order? Do we have any bestsellers?Analytics:Generally 2-3 pizzas are ordered in a order. The Big_Meat_Pizzza is ordered Maximum number of time that is 1811 times there for The big_meat_pizza in small size is the bestsellers.Table used: Order_detailStep 1:- pivot table inserted on full table data at the row labels order_id and&nbsp; order_detail-id is drag and drop at the values, Summarized values by count, average calculated by using average function average come 2.70 i.e&nbsp; 2-3 typicallyStep 2:- Pivot table inserted on full table data at the row labels pizzas_id and order_detail-id is drag and drop at values, Summarized value by count, sort the count of order_detai_id coloumn the max value show in first row i.e.1811&nbsp; along with pizza_id big_meat_s find the name from pizzas_id table the name of pizza of such id is The big meat pizza small in size.And pivot chart inserted for good visualization.3) How much money did we make this year? Can we identify any seasonality in the sales?Analytics:-Pizza place sales make&nbsp; $801944.70&nbsp; in this year 2015 For looking graph of total month wise sale the July month showing the Maximum sale so we&nbsp; can conclude that in&nbsp; July month is theTable used: Order, order_detail, pizzaStep 1:- pivot table inserted on full table data at the row labels&nbsp; month of date and&nbsp; order_id is drag and drop at the values, Summarized values by count,&nbsp; sort data A-Z, The result showing that July month is having large sales.Step 2:- Pivot table inserted on full table data at the row labels pizzas_id and order_detail-id is drag and drop at values, Summarized value by count, copy price list from pizza table to fetch price of each pizza and new price column is added name as price_per_unit.per unit price is fetch by vlookup formula, and total sales for each pizzas calculated by multiply the total sold unit with per unit price, then do the final sum of all pizzas sales. Get answer $801944.704)Are there any pizzas we should take off the menu, or any promotions we could leverage?<ul><li>Analytics:-Big_meat_m ( Big meat in medium size and large size) Five_cheese_s,Five_Chees_m( Five Chees in small and in medium size) and four_Cheese_S&nbsp; pizzas never get sold in a year 2015. We can think to off them. But if&nbsp; we keep Big_meat_m and big_meat l,Fivecheese_m in offer price and&nbsp; Five_cheese_s and&nbsp; and four_cheese_s&nbsp; free on bbq-ckn_l and ital-cpcllo_l&nbsp; for few days to increase sales</li></ul>Table used: Order_detail, PizzaStep 1 :- Pizzas name list copy from pizza table, write match formula to check the occurrence of pizza_id in order table. Some places&nbsp;show 0 result i.e that particular pizzas not get sold. Find name of that pizzas from pizza table