INTRODUCTION
Data science and analysis is the process of using data to understand what is happening,find patterns,solve problems and make better decisions,In some cases most of the available data is usualy dirty, Excel and power Bi are some of the tools that can be used in data cleaning
Stick with me as I try to answer the following questions
- Whether larger reviews are associated with more reviews
- Whether highly rated products attract stronger engagements
- Whether pricing and ratings move together
- Which products perform best based on ratins
- Which product may need a different pricing or marketing strategy
Initial data-quality audit
From the above picture we can see a number of issues with the data
- The numbers have been kept in text form
- The product colomn needs to be trimmed to get rid of the unnessesary spaces
- Blanks and errors are visible in the dataset 4.Some of the words have been misspelled
Cleaning and preparation decisions
Inorder for our file to be cleaned the following need to be done
As highlited in the picture above elimination of the out 5 is neccesary to enable the conversion of the texts to decimal values
A trim is also needed in the product category to enable the elimination of unwanted and uncessary spacing
Excel formulas and enrichment fields
In Excel formulae are used to calculate,analyze, and manipiulate data automatically and a formulae usually starts with an = sign
formulae and what it does
=sum, Adds numbers
=Average,finds the average
=Max,finds the largest value
=Min,find the smallest value
=count,counts numbers
PivotTable and analysis workflow
A pivotTable is an Excel tool used to summerize,organize,and analyze alarge amount of data quickly without changing the original data
In my case i got to use a number of pivot tables to summerize my data for easier understanding and communication,Example of pivot tables i learnt
- Bar graph
- Column chart
- Pie chart, Donut chart
- Area chart
an example of column and line chart
Dashboard design and slicer connections
The picture above is an example of a dashboard where the slicers are interconnected to the data presented
in the picture above it illustrates that in the occurrence of a price drop from 900 to 450, the reviews came down to
Key findings
Below are some of the findings that I found out
- Power Query is a tool in excel that is easier to navigate and clean data with
- The functions in excel are used to help to make your work easier during the process of handling data.ie VLOOKUP which handles data vertically and HLOOKUP which handles data horizontally
- Logical functions which are used to automate decision making enabling dynamic instruction i.e IF,AND,OR . In a case where we are handling more data we use a nested "IF"
Business recommendations
Below are some of the recommendations from the above data
- Promote the products that are rated 4.8-5.0, because they have stronger customer satisfaction
2.Review low rated products which are from 3.3-3.8 before increasing their promotion
3.Products with significant price reductions can be used to attract customers,but you should check whether the reduced price still provides a good profit margin
Limitations and lessons learned
Limitations
- Ratings does not necessarily indicates the sales perfomance i,e a product with the highest ratings doesn't necessarily symbolizes that it is the best selling
-No time period provided, there is no clear indication of when the prices,discounts,reviews and ratings were recorded.Product perfomance may change over time
Lessons learnt
- Customer ratings are usually useful for identifying product popularity
- Discount can make a product more attractive
- Low rated products need attention. Products with ratings such as 3.3-3.8 should be investigated to understand customer complaints and improve product quality
- Price comparison is important cause it helps identify products with significant price reductions and can help evaluate pricing strategies
- High ratings alone are not enough for decision making hence the need to combine ratings,reviews,discounts ,price,salesvolume and profit before deciding which products to promote
Top comments (0)