Brand Headline information
Compay Name- Petco
Profile Description
Petco is a leading specialty retailer that focuses on nurturing the powerful relationship between people and pets.They provide the products, services, advice and experiences that keep pets physically fit and emotionally happy.Petco facebook page has 2,923,613 likes and 2,792,082 followers at February 06, 2017
Analysis of most recent posts made by the brand- Counts of likes, comments, shares, and each type of reactions on these posts and profile name of People who liked the posts.
I used getPage to Extract list of posts from a public Facebook page. getPage(page , token, n = n): Extract list of posts from a public Facebook page
#Petco_page_large <- getPage(page = "Petco",token = my_oauth, n= 1000, reactions = T)
# head(Petco_page_large)
#write.csv(Petco_page_large, file="C:/Users/Amit/Desktop/MSDA/MSDA/DA6813/HW/Petco_page_large.csv", row.names= FALSE)
# Petco profile picture
Petco_page_large <- read.csv("C:/Users/Amit/Desktop/MSDA/MSDA/DA6813/HW/Petco_page_large.csv")
likes_freq <- table(Petco_page_large$likes_count,Petco_page_large$type)
l <- c('link','photo','status','video')
barplot(table(factor(Petco_page_large$type, levels = l)) , col = c('coral2', 'darkolivegreen4', 'darkturquoise', 'darkorchid1'), xlab = 'Type of Posts', ylab = 'Number Of Posts')
ggplot(Petco_page_large, aes(x = type, y = likes_count, fill = type )) + geom_boxplot() + scale_y_continuous(trans='log2')
par(c(1,4))## NULL plot(Petco_page_large$likes_count,Petco_page_large$shares_count, col="blue", main =" Likes count vs shares count")
# Coversion to log scale
plot(Petco_page_large$likes_count,Petco_page_large$shares_count, log = "xy", col="blue",main =" Likes count vs shares count on log scale")## Warning in xy.coords(x, y, xlabel, ylabel, log): 14 y values <= 0 omitted
## from logarithmic plot
par(mar=c(1,1,1,1))
head(Petco_page_large, n=2)## id likes_count from_id from_name
## 1 78697430614_10155328994610615 4331 78697430614 Petco
## 2 78697430614_10155333674030615 1834 78697430614 Petco
## message
## 1 With this ball, I will rule all of the dogs.\n\n(Photo: Clive)
## 2 "Look at me now! Ruthie suffered from persistent hairballs, like many indoor cats. After switching her to a hairball formula food and regularly brushing out her excess fur, she's living happy, healthy and hairball free. What we feed them matters - just ask Ruthie. Make a change and #SeeThemThrive." \n\nHave you entered our contest on Instagram? Join now for a chance to win a year of Repeat Delivery! Learn more: http://bit.ly/ContestRD
## created_time type
## 1 2015-03-11T15:01:15+0000 photo
## 2 2015-03-12T15:02:35+0000 photo
## link
## 1 https://www.facebook.com/Petco/photos/a.173370210614.235540.78697430614/10155328994610615/?type=3
## 2 https://www.facebook.com/Petco/photos/a.173370210614.235540.78697430614/10155333674030615/?type=3
## story comments_count
## 1 Petco with Magaline Galvan and Rosario Alejos. 41
## 2 <NA> 20
## shares_count love_count haha_count wow_count sad_count angry_count
## 1 211 0 0 0 0 0
## 2 58 0 0 0 0 0# Post with maximum likes count
Petco_page_large[which.max(Petco_page_large$likes_count), ]## id likes_count from_id from_name
## 485 78697430614_10156626159465615 46899 78697430614 Petco
## message
## 485 Make a statement & fight smoking. Pick up a truth collar charm at your local Petco store for free!
## created_time type
## 485 2016-03-07T16:04:00+0000 photo
## link
## 485 https://www.facebook.com/Petco/photos/a.10155232513745615.1073741931.78697430614/10156626158945615/?type=3
## story
## 485 Petco added 4 new photos <U+0097> with Lyss Aponte and 2 others.
## comments_count shares_count love_count haha_count wow_count sad_count
## 485 507 2638 1743 42 20 1
## angry_count
## 485 5# __Image in the post with maximum likes__
# Total Counts of likes, comments, shares, and each type of reactions on these posts
Petco_count <- colSums(Petco_page_large[ ,c(2,10:16)], na.rm=T)
Petco_count## likes_count comments_count shares_count love_count haha_count
## 1537039 48376 249088 51931 5531
## wow_count sad_count angry_count
## 3135 26492 9179# Average Counts of likes, comments, shares, and each type of reactions on these posts
Petco_means <- colMeans(Petco_page_large[ ,c(2,10:16)], na.rm =T)
round(Petco_means)## likes_count comments_count shares_count love_count haha_count
## 1537 48 249 52 6
## wow_count sad_count angry_count
## 3 27 9par(mar=c(6,6,4,4))
barplot(Petco_means, col= "orange", main=" Average Counts of likes, comments, shares, and each type of reactions on posts (n=1000)", xlab="Emotion",
ylab="Average Count", border="blue", log = "y")
We get a data frame from get page. The most popular post from last 1000 post (starting from January 04, 2015) received almost 52,353 likes and 1,017 comments, and was shared over 3,286 times
In last 1000 posts, total likes count is 1,925,989, comment count is 51,124 and shares count is 268,473. Average like count per post is 1,926, comment count is 51 and shares count is 268. Analysis of 1,000 post reveals that median number of likes per post is around 450. As expected, the message which didn’t receive much response were status messages like stored will be closed, lost dog alert and Free delivery on your first order over $10!.
Key observations from graphs
- More than 90% posts have photo and links
- Likes count are high for post with photo followed by post with a link. It is interesting to note that even video have less likes count compared to photo and links. Post with only status have e lowest likes count as compared with post with links, status and video.
- Average love count is around 50, sad count is around 25, whereas average angry, haha and wow count is less than 10.
- Average likes count is 1916 mainly due to some posts that that very large number of likes. Median count of likes is around 500.
- As observed on log scale, there is a strong correlation between likes and counts.
For further analysis, I sorted the page (with recent post on top) and saved the file so that I don’t have to download the data every time I run the program.
The code below illustrates how to aggregate the metrics by month in order to compute the mean count of likes/comments/shares per post: for example, in ………. the average post received around …….. likes.
Petco2016_2015 <- read.csv("C:/Users/Amit/Desktop/MSDA/MSDA/DA6813/HW/Petco_page_large.csv")
head(Petco2016_2015)## id likes_count from_id from_name
## 1 78697430614_10155328994610615 4331 78697430614 Petco
## 2 78697430614_10155333674030615 1834 78697430614 Petco
## 3 78697430614_10155338305930615 5082 78697430614 Petco
## 4 78697430614_10155342677170615 4474 78697430614 Petco
## 5 78697430614_10155346944295615 6924 78697430614 Petco
## 6 78697430614_10155351670370615 4524 78697430614 Petco
## message
## 1 With this ball, I will rule all of the dogs.\n\n(Photo: Clive)
## 2 "Look at me now! Ruthie suffered from persistent hairballs, like many indoor cats. After switching her to a hairball formula food and regularly brushing out her excess fur, she's living happy, healthy and hairball free. What we feed them matters - just ask Ruthie. Make a change and #SeeThemThrive." \n\nHave you entered our contest on Instagram? Join now for a chance to win a year of Repeat Delivery! Learn more: http://bit.ly/ContestRD
## 3 Contrary to popular belief, we think black cats bring good luck any day of the year. Happy Friday the 13th!
## 4 Call me fluffy one more time.
## 5 To the treat bar... now!\n\n(Photo: Delilah by Heather)
## 6 Working on our petiquette.\n\n(Photo: Kira)
## created_time type
## 1 2015-03-11T15:01:15+0000 photo
## 2 2015-03-12T15:02:35+0000 photo
## 3 2015-03-13T15:02:08+0000 photo
## 4 2015-03-14T15:01:29+0000 photo
## 5 2015-03-15T15:01:19+0000 photo
## 6 2015-03-16T15:02:05+0000 photo
## link
## 1 https://www.facebook.com/Petco/photos/a.173370210614.235540.78697430614/10155328994610615/?type=3
## 2 https://www.facebook.com/Petco/photos/a.173370210614.235540.78697430614/10155333674030615/?type=3
## 3 https://www.facebook.com/Petco/photos/a.173370210614.235540.78697430614/10155338305930615/?type=3
## 4 https://www.facebook.com/Petco/photos/a.173370210614.235540.78697430614/10155342677170615/?type=3
## 5 https://www.facebook.com/Petco/photos/a.173370210614.235540.78697430614/10155346944295615/?type=3
## 6 https://www.facebook.com/Petco/photos/a.173370210614.235540.78697430614/10155351670370615/?type=3
## story comments_count
## 1 Petco with Magaline Galvan and Rosario Alejos. 41
## 2 <NA> 20
## 3 <NA> 177
## 4 <NA> 77
## 5 <NA> 61
## 6 Petco with Helma Mamesah. 38
## shares_count love_count haha_count wow_count sad_count angry_count
## 1 211 0 0 0 0 0
## 2 58 0 0 0 0 0
## 3 498 0 0 0 0 0
## 4 179 0 0 0 0 0
## 5 257 0 0 0 0 0
## 6 120 0 0 0 0 0## convert Facebook date format to R date format
format.facebook.date <- function(datestring) {
date <- as.POSIXct(datestring, format = "%Y-%m-%dT%H:%M:%S+0000", tz = "GMT")
}
## aggregate metric counts over month
aggregate.metric <- function(metric) {
m <- aggregate(Petco2016_2015[[paste0(metric, "_count")]], list(month = Petco2016_2015$month),
mean)
m$month <- as.Date(paste0(m$month, "-15"))
m$metric <- metric
return(m)
}
# create data frame with average metric counts per month
Petco2016_2015$datetime <- format.facebook.date(Petco2016_2015$created_time)
Petco2016_2015$month <- format(Petco2016_2015$datetime, "%Y-%m")
df.list <- lapply(c("likes", "comments", "shares"), aggregate.metric)
df <- do.call(rbind, df.list)
# visualize evolution in metric
library(ggplot2)
library(scales)
ggplot(df, aes(x = month, y = x, group = metric)) + geom_line(aes(color = metric))+ theme_bw() + theme(axis.title.x = element_blank()) + ggtitle(" Petco Facebook Page Performance") +scale_y_log10("Average count per post", breaks =c(10, 100,250,500, 1000,2000,4000))
ggsave(file="C:/Users/Amit/Desktop/MSDA/MSDA/DA6813/HW/Petco2016_2015.png",dpi=100)
write.csv(Petco2016_2015, file="C:/Users/Amit/Desktop/MSDA/MSDA/DA6813/HW/Petco2016_2015.csv", row.names= FALSE)
View(Petco2016_2015)The most popular post in past 1000 post received almost 52,353 likes and 1017 comments, and was shared over 3286 times. We also observe that average number of likes were lower is 2016 as compared to 2015. This may be due to less post with very high likes in 2016 as compared to 2015. Also the trend indicates lower likes in second half of the year in both 2016 and 2015 # Trend analysis for the year 2016
First, I converted posts into a matrix so that it becomes easy to access the comments based on their position, and initialize the data frame allcomments as null. Traverse post by post and append all the comments of the posts to the data frame all comments. Using the for loop, I created a post number for each comment
#post_id<- head(page2016$id, n = 45)
#head(post_id, n=10)
#post_id<- as.matrix(post_id)
#allcomments<- data.frame(Doubles=double(),Ints=integer(),Factors=factor(),Logicals=logical(),Characters=character(),stringsAsFactors=FALSE)
#likes_names<- data.frame(Doubles=double(),Ints=integer(),Factors=factor(),Logicals=logical(),Characters=character(),stringsAsFactors= TRUE)
# Collecting all the commments from all the 45 posts
#for (i in 1:nrow(post_id))
# {
# Get upto 50 comments for each post
#post<- getPost(post_id[i,], token= my_oauth, n = 50,
#likes = TRUE, comments = TRUE)
#post
#write.csv(post, file="C:/Users/Amit/Desktop/MSDA/MSDA/DA6813/HW/post.csv", row.names= FALSE)
#post <- read.csv("C:/Users/Amit/Desktop/MSDA/MSDA/DA6813/HW/post.csv")
#comments<- post$comments
#names <- post$likes
#postnumber =i
#postcomments <- cbind(comments,postnumber)
# Append the comments to a single data frame
#allcomments<- rbind(allcomments, postcomments)
#likes_names <- rbind(likes_names, names)
# }
#View(allcomments)
#View(likes_names)
#write.csv(allcomments, file="C:/Users/Amit/Desktop/MSDA/MSDA/DA6813/HW/allcomments.csv", row.names= FALSE)
#write.csv(allcomments, file="C:/Users/Amit/Desktop/MSDA/MSDA/DA6813/HW/likes_names.csv", row.names= FALSE)I extracted 45 posts from post matrix. Likes_names is a dataframe with 2224 observation of 2 variable. Similarly, comments is a data. Frame with 980 observations of 8 variables. I have added one variable of post number. This variable is required to do post wise analysis of comments.
Identify the users that comment the most often
allcomments <- read.csv("C:/Users/Amit/Desktop/MSDA/MSDA/DA6813/HW/allcomments.csv")
head(allcomments)## from_id from_name
## 1 1.021192e+16 Franny Hagan
## 2 1.477384e+15 Michele Minery
## 3 1.020978e+16 Evie Fletcher
## 4 1.020972e+16 Cathy Breaux
## 5 1.021044e+16 Ruth McDaniel
## 6 1.021044e+16 Ruth McDaniel
## message
## 1 God Bless Pet Smart I will adopt a kitty from Pet Smart St. Francis very soon
## 2 Dogs are the best!
## 3 Got my Charlie kitty from PetSmart through a rescue group.
## 4 Warms my heart when I get reminded of how many people love animals.
## 5
## 6
## created_time likes_count comments_count
## 1 2014-05-27T04:50:02+0000 15 0
## 2 2014-05-27T04:04:12+0000 12 0
## 3 2014-05-29T16:33:03+0000 6 0
## 4 2014-05-30T05:17:19+0000 2 0
## 5 2014-05-29T17:47:15+0000 2 0
## 6 2014-05-29T17:46:54+0000 2 0
## id postnumber
## 1 10154175183020596_10154181733265596 1
## 2 10154175183020596_10154181643170596 1
## 3 10154175183020596_10154191783645596 1
## 4 10154175183020596_10154193962370596 1
## 5 10154175183020596_10154192008965596 1
## 6 10154175183020596_10154192007710596 1likes_names <- read.csv("C:/Users/Amit/Desktop/MSDA/MSDA/DA6813/HW/likes_names.csv")
head(likes_names)## from_id from_name
## 1 1.021192e+16 Franny Hagan
## 2 1.477384e+15 Michele Minery
## 3 1.020978e+16 Evie Fletcher
## 4 1.020972e+16 Cathy Breaux
## 5 1.021044e+16 Ruth McDaniel
## 6 1.021044e+16 Ruth McDaniel
## message
## 1 God Bless Pet Smart I will adopt a kitty from Pet Smart St. Francis very soon
## 2 Dogs are the best!
## 3 Got my Charlie kitty from PetSmart through a rescue group.
## 4 Warms my heart when I get reminded of how many people love animals.
## 5
## 6
## created_time likes_count comments_count
## 1 2014-05-27T04:50:02+0000 15 0
## 2 2014-05-27T04:04:12+0000 12 0
## 3 2014-05-29T16:33:03+0000 6 0
## 4 2014-05-30T05:17:19+0000 2 0
## 5 2014-05-29T17:47:15+0000 2 0
## 6 2014-05-29T17:46:54+0000 2 0
## id postnumber
## 1 10154175183020596_10154181733265596 1
## 2 10154175183020596_10154181643170596 1
## 3 10154175183020596_10154191783645596 1
## 4 10154175183020596_10154193962370596 1
## 5 10154175183020596_10154192008965596 1
## 6 10154175183020596_10154192007710596 1library(sqldf)
commenters <- sqldf("select from_name, COUNT(from_name) as Frequency from allcomments group by from_name")
write.csv(commenters, file="C:/Users/Amit/Desktop/MSDA/MSDA/DA6813/HW/commenters.csv", row.names= FALSE)
commenters <- read.csv("C:/Users/Amit/Desktop/MSDA/MSDA/DA6813/HW/commenters.csv")
# head(commenters)
topcommenters <- sqldf("select from_name, frequency from commenters group by from_name order by frequency desc ")
write.csv(topcommenters, file="C:/Users/Amit/Desktop/MSDA/MSDA/DA6813/HW/topcommenters.csv", row.names= FALSE)
commenters <- read.csv("C:/Users/Amit/Desktop/MSDA/MSDA/DA6813/HW/topcommenters.csv")
head(topcommenters, n=10)## from_name Frequency
## 1 Zenaida Medina 23
## 2 Christy Wedel' Durant 11
## 3 Barbara A. Sloan 8
## 4 Helen Akselrod 8
## 5 Judie Ann Sorrentino-Lipiec 8
## 6 Melissa Phetsomphou 6
## 7 Andrea Visconti 5
## 8 Ann-Marie Glynn 4
## 9 Billie Walker 4
## 10 Christine Haftl 4# Consolidating the like for each user.
influentialusers<- sqldf("select from_name, sum(likes_count) as
totlikes from allcomments group by from_name")
influentialusers$totlikes<- as.numeric(influentialusers$totlikes)
top<- influentialusers[order(- influentialusers$totlikes),]
head(top, n=10)## from_name totlikes
## 505 Lauren Ooten Yeary 104
## 806 Sheng Vwj 92
## 772 Sally Seashore 80
## 262 Elvira Bektas 74
## 177 Cindy Alexander 71
## 762 Roselle Enriquez Caperal 71
## 33 Amanda Rae 60
## 337 Jamie Lee Fultz 59
## 696 Patti Mysnyk 59
## 32 Amanda Palermo 54The above code gives top commenters and most influential person (based on total number of likes for his/her comments). We observe that in last 45 post top three commenters are Betty, Virginia and Marla. It is interesting to observe that none of the first top commenters are in top ten influential people. (based on number of likes).
Getting to know the top commenters and most influential person is a very useful task, that is very helpful for executing marketing campaigns and making them successful. Based on the sentiment analysis below, we observe that some top commenters have positive sentiment and some don’t.
Sentiment analysis
Word Cloud
library(stringr)
library(tm)
allcomments$message <- sapply(allcomments$message,function(row) iconv(row, "latin1", "ASCII", sub=""))
#head(allcomments$message)
#str(allcomments$message)
#clean the corpus
myCorpus <- Corpus(VectorSource(allcomments$message),readerControl=list(language="en"))
# convert to lower case
myCorpus <- tm_map(myCorpus, content_transformer(tolower))
# remove punctuation
myCorpus <- tm_map(myCorpus, removePunctuation)
# remove numbers
myCorpus <- tm_map(myCorpus, removeNumbers)
# remove stopwords from corpus
stop_words1 <- stopwords("SMART")
stop_words2<-stopwords("en")
stop_words <-c(stop_words1, stop_words2)
myCorpus <- tm_map(myCorpus, removeWords, stop_words)
# Strip white spaces
myCorpus <- tm_map(myCorpus, stripWhitespace)
#plain text
myCorpus<-tm_map(myCorpus, PlainTextDocument)
#Do stem
myCorpus<-tm_map(myCorpus,stemDocument)
# remove words
# myCorpus <- tm_map(myCorpus, removeWords, c("Petco"))
par(mfrow=c(1,1))
# Get a wordcloud
wordcloud(myCorpus, scale=c(5,0.5), sin.freq=3, max.words=Inf,
random.order=FALSE, rot.per=0.35,
use.r.layout=FALSE, colors=brewer.pal(8,"Dark2"))
Word cloud captures the expected words such as Petco, dog, cat and some positive word such as Love, good, happi, cute and lol etc and many neutral words such as sale, food, home, veteran, today etc.
Post-wise sentiment analysis
This is a technique for evaluating the overall positivity, negativity, or neutrality of a body of text. Positive words receive positive integers as a score, whereas negative words are represented by negative integers. The overall score for a body of text is the average of those numbers. To explore further I did the sentiment analysis using sentiment and syuzet package I used sqldf to group by post and then find out correlation between Facebook reactions and post wise comments.
Recommendation based on the analysis of Petco’s Facebook page:
Based on syuzet package observations, i found a very weak and correlation between Facebook reactions and syuzet package emotions. For example, correlation between Angry count and anger is - 0.03 Reason is that many comments are not related with posts and are general feedback:
Recommendation based on the analysis of Petco’s Facebook page:
Identifying sentiment for last two years- Average number of likes have decreased in last two years and hence it is critical to know movement of average sentiment in last two years. Since, Sentiment score does partly correlate with the sentiment that we observe in the reactions on the posts. Petco should devise a strategy to reduce this negative sentiment number.
Selection of type of post- As Post with interesting pictures and links have high positive reactions and high sentiment score as compared to post with status and videos, Petco should have expert person to recommend which pictures and link should be posted
Focus of quality of post content-We observe that one interesting post with picture may attract lot of eyeballs and can change the sentiment of comments of that post. As we saw in 2015, Post dated January 04, 2015 had more than 50,000 likes (as compared to median likes of 500). It may be noted that Post with interesting pictures and with words like congrats, woe, prize have high positive reactions and sentiment score.
Quick customer query/dispute resolution- We observe that first negative comment may trigger series of negative comment. For Example, In post 14 one negative comment also trigger other negative comments of the same post. This post 14 is on Travel smoothly, safely and comfortably with your dog this holiday season. It has no sad or angry Facebook reactions yet score high in sadness and surprise count,because many comments are not related with posts and are general feedback. All these comments were on same post within a period of 4 days, so a quick customer response by Petco may have reduced further comments.
Private message box on Facebook page for customer complaints. This will help customers not being misguided by a disgruntled customer. For example, Facebook comments may be analyzed to figure out which stores need more improvement in quality. For example, customers have complained about Bensalem, PA Store, Lake Street in Peoria, Illinois and one store in Nj. Customer feedback should be viewed as an opportunity for change. As a rule, the expression of empathy to the customer should come right after the apology. Also, it may be kept in mind that not all customers can be pleased. Some people are just complainers and nothing will change that. But everything we can correct about this situation will help with future events. Keep improving in areas over which you have control.
Leverage Social Media and have Focused Strategy for online marketing- Petco Facebook page has more than 2,920,064 likes. Petco should leverage this for all social media campaigns. It may be noted that Petco twitter page has 179,000 followers and has 8,986 likes. We also observe that average number of likes were lower is 2016 as compared to 2015. This may be due to less post with very high likes in 2016 as compared to 2015. Also, the trend indicates lower likes in second half of the year in both 2016 and 2015. We may do a granular analysis to know if this has an impact on company’s topline and bottom-line.