Below is a mock analysis of book sales data for a book seller in the United States. The analysis attempts to derive some meaningful insights for the business to assess their current sales and make strategic decisions to improve future sales:
The data was supplied ‘as is’ by the business in CSV format.
## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr 1.1.4 ✔ readr 2.1.5
## ✔ forcats 1.0.0 ✔ stringr 1.5.1
## ✔ ggplot2 3.5.1 ✔ tibble 3.2.1
## ✔ lubridate 1.9.3 ✔ tidyr 1.3.1
## ✔ purrr 1.0.2
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag() masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors## Rows: 2000 Columns: 4
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (3): book, review, state
## dbl (1): price
##
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.# How big is the dataset?
df_size<-dim(df)
print(paste('Rows : ',df_size[1],' Columns: ',df_size[2]))## [1] "Rows : 2000 Columns: 4"# What are the column names & types?
for (c in colnames(df)) print(paste('Column: ',c,' [',typeof(df[[c]]),']'))## [1] "Column: book [ character ]"
## [1] "Column: review [ character ]"
## [1] "Column: state [ character ]"
## [1] "Column: price [ double ]"The dataFrame(df) contains 2000 rows and 4 columns
The column headers details are:
book - The title of the book,
review - The book review,
state - Where the books were sold
price - The sale price
## [1] "R Made Easy" "R For Dummies"
## [3] "Secrets Of R For Advanced Students" "Top 10 Mistakes R Beginners Make"
## [5] "Fundamentals of R For Beginners"
## [1] "Excellent" "Fair" "Poor" "Great" NA "Good"
## [1] "TX" "NY" "FL" "Texas" "California"
## [6] "Florida" "CA" "New York"
## [1] 19.99 15.99 50.00 29.99 39.99We can see in the above output that the review data contains some missing (NA) values. These may skew the final output so we’ll need to remove incomplete data.
## [1] 1794 4206 rows have been removed from the original dataFrame. This is approximately 10% of the supplied data. The remaining 1794 rows of data is enough to continue the analysis and produce some meaningful results.
The business should be notified about the missing reviews and encouraged to try and complete the missing review data, if possible.
No that we’ve imported, cleaned and standardised the data, we can manipulate the remaining 1794 rows to extract some meaningful information.
# Import Scales library to display values as %
suppressMessages(library(scales))
# Create a new dataFrame
suppressMessages(summary_df<-no_NA_df %>%
# Group the original data by book and price
group_by(book,price) %>%
# Summarize the data to get frequency and total data
summarize(
# Get the number of each book sold
Num_Sold = n(),
# Get the total revenue for each book by State
'$ Sales in TX' = sum((state=='TX')*price),
'$ Sales in NY' = sum((state=='NY')*price),
'$ Sales in FL' = sum((state=='FL')*price),
'$ Sales in CA' = sum((state=='CA')*price),
# Get the total revenue for each book
Total_Sales = sum(price),
# Get the book sales as a percentage of all sales combined
Revenue_Share = percent(Total_Sales/sum(no_NA_df$price),accuracy=2),
# Get the total number of the Poor or Fair reviews
Bad_Review = sum(
review=='Poor',
review=='Fair'
),
# Get the total number of the Good, Great and Excellent reviews
Good_Review = sum(
review=='Good',
review=='Great',
review=='Excellent'
),
# Create the weighting value for Bad Reviews
Bad_Weight = sum(case_when(
review=='Fair'~1,
review=='Poor'~2,
.default=0
)),
# Create the weighting value for Good Reviews
Good_Weight = sum(case_when(
review=='Good'~1,
review=='Great'~2,
review=='Excellent'~3,
.default=0
)),
# Calculate the difference of percentages
# Good vs Bad reviews - Higher the difference, the better
Review_Difference = round(
((Good_Weight/Num_Sold)*100)-((Bad_Weight/Num_Sold)*100),2)
)%>%
# Arrange the data in descending order of Rating
arrange(-Review_Difference)
)
# Display the new dataFrame
summary_df*Scroll through the columns using the black arrows <>
The data has been grouped by ‘book’ and summarized to extract the following:
The Book Title
The Book Price
Number of each book Sold (Num_Sold)
Revenue for each book by State ($ Sales in <state>)
Total Revenue for each book, across all States (Total_Sales)
The percentage of revenue each book contributed towards the businesses overall revenue (Revenue_Share)
The number of Bad Reviews (‘Fair or ’Poor’) and the number of Good Reviews (‘Good’, ‘Great’ and ‘Excellent’)
The weighting applied to ‘Good’ and ‘Bad’ reviews for each book (Bad_Weight & Good_Weight)
The ‘Review Difference’ or ‘Rating’ between ‘Good’ and ‘Bad’ reviews for each book (Review_Difference)
The amount of books soold appear to be similar for each title, ranging from 352 to 366.
The book that sold the most copies was “Fundamentals of R For Beginners”, selling 366 copies.
## [1] 56091.66The businesses total revenue from these book sales was: $56,091.66
If we look at the Total_Sales column, we can see that the top 3 books, in terms of revenue were:
We can also see that these books accounted for 76% of the sales revenue.
*Scroll through the columns using the black arrows <>
Here we can see that, although sales were similar across all 4 states. The State that brought in the most revenue was New York with $15,552.24 and the least, Florida with $12,708.80.
However, if the business wishes to look at which books might continue to sell at the same or an improved rate, we should take into account the book reviews - recommendation being a key sales driver.
As we can see, all of these books appear to have a similar amount of Good and Bad reviews so it’s difficult to draw any conclusion from just these numbers.
If we assign a number value to each review type… \[{GoodReview=(Good=1,Great=2,Excellent=3)}\] \[{BadReview=(Fair=1,Poor=2)}\] And take the sum of these values, divide them by the total number of reviews and multiply them both by 100 to get their respective percentages. Then subtract the Bad reviews from the Good reviews, this gives us the ‘Review Difference’, which we’ll use as a very basic Rating score.
\[{ Good Review\choose TotalReviews} 100 - {BadReview \choose TotalReviews}100 =Review Difference\]
*The higher the Rating, the higher the weighting of Good reviews there are in the total reviews left for each book.
We now have a much greater range of Rating values, based on actual customer preferences for each book.
The Rating tells us that top 3 reviewed books are:
This tells us that these books may increase sales at a more sustained or improved rate than the other books.
The book sales that are most likely to continue or improve in the future are:
Top 10 Mistakes R Beginners Make with a Rating of 67.61
The book sales that are most likely to diminish in the future are:
R For Dummies, with a Rating of just 36.57
For the business to make an informed decision on which books to concentrate their efforts, it would be good to know the businesses profit on each book. They may also wish to factor in other metrics, such as; when the book was published, the footfall at each store, the popularity of the author and even the socio-economic backgrounds of the people who left reviews - if determinable.
As we don’t have that information, all we can do is present our findings and see if the business would like further analysis to be carried out.