Using Amazon Search Data to Explore Customer Behavior (A Project in R)

Background

Amazon sellers that are part of the company’s Brand Registry program have access to Amazon’s Brand Analytics, a feature which provides valuable data to Amazon sellers on the larger Amazon product catalog, demographic data on a seller’s customers, and other useful information. One of the reports provided by Brand Analytics is the Amazon Search Terms report, which includes a list of the most popular search terms on Amazon for a selected time frame, ranked. In addition to providing the search frequency rank, the report also provides data on the top three clicked products for each search term, and the share that those products have of all the clicks and sales made when a customer searches that term. While this is useful for a seller tracking the position of their own products on the Amazon platform, it can also be used to observe trends concerning broader customer interests and their interaction with the Amazon product catalog. The full report, when downloaded, includes records on the top 1,000,000 search terms for a select time period. The short project below, done in R, explores this data for the week-long period of 5/22/22 - 5/28/22 and provides visualization of some of the trends that can be ascertained from this large dataset.

For a description of the Amazon report in question, refer here.

Loading libraries:

library(tidyverse)
library(ggplot2)
library(tidyr)
library(janitor)
library(ggwordcloud)
library(wordcloud)
library(tm)
library(NLP)

reading CSV file:

search_data <- read_csv("/Users/YourName/Desktop/File.csv", skip=1)

str(search_data)

search_data <- clean_names(search_data)

Preview the dataset:

head(search_data,10)

Let’s remove the % symbol and make the values numeric for all columns where a % symbol appears:

search_data$number_1_click_share <- as.numeric(sub("%", "", search_data$number_1_click_share))

search_data$number_2_click_share <- as.numeric(sub("%", "", search_data$number_2_click_share))

search_data$number_3_click_share <- as.numeric(sub("%", "", search_data$number_3_click_share))

search_data$number_1_conversion_share <- as.numeric(sub("%", "", search_data$number_1_conversion_share))

search_data$number_2_conversion_share <- as.numeric(sub("%", "", search_data$number_2_conversion_share))

search_data$number_3_conversion_share <- as.numeric(sub("%", "", search_data$number_3_conversion_share))

Let’s create a wordcloud to see what people are searching the most for in this time period. To make this more manageable, lets work with the top 30,000 searches, and words mentioned more than 50 times.

top_30k <- search_data %>% filter(search_frequency_rank<=30000)

wordcloud(top_30k$search_term, min.freq=50)

Products with a search rank of less than or equal to 30,000 represent some of the most searched (and purchased) Amazon products, and our list above only includes those search terms that were repeated more than 50 times in these twop 30,000 entries. The wordcloud above therefore gives us a quick glimpse into Amazon customers’ top search behavior during the time period looked at at the end of May. Given well-known sales trends, we unsurpisingly see “women” as our top searched term. We see other sales demographics noted as key search terms as well, including men, baby, and dog (i.e. pets). Given the summer time frame looked at in this search terms report, it is unsurprising to see terms such as “outdoor,” “sunglasses,” “summer,” “sunscreen” and “swimming” appear in our wordcloud.

Our data currently gives total click share and conversion share relative to all those products clicked for a certain search term by a customer, and gives this separately for each of the top 3 clicked products. Let’s create two columns to capture the summed numbers from all of these three products:

Create total_click_share column and total_conversion_share column:

search_data <- mutate(search_data, total_click_share = number_1_click_share + number_2_click_share + number_3_click_share)

search_data <- mutate(search_data, total_conversion_share = number_1_conversion_share + number_2_conversion_share + number_3_conversion_share)

See the right most columns for these added columns:

head(search_data,10)

Explore if there is any missing data in our new total_click_share column:

which(is.na(search_data$total_click_share))

integer(0)

There is no missing data in this column.

Explore if there is any missing data in our new total_conversion_share column:

str(which(is.na(search_data$total_conversion_share)))

 int [1:2709] 39125 56329 73720 75440 88342 117020 129896 140058 159939 167344 ...

There are close to ~3000 missing values.

Looks like total_conversion_share has missing values. Let’s explore further what the reason is:

na_total_conversion_share <- search_data %>% filter(is.na(search_data$total_conversion_share))

head(na_total_conversion_share,50)

Upon exploring the examples of the NA values above, they appear to be examples where a single product garners 100% of the clickshare for a search term, and zero sales are made. This is believable when we consider that practically all of the items in this list have a poor search frequency rank. Nothing in the top 30,000 appears. In many but not all the cases, the term that is searched is also not one that is done by a normal user. They are searches for specific Amazon identification numbers, also known as the Amazon “ASIN”, indicating that the customer was only looking for 1 possible search result (e.g., a specific make/brand for a product). This may also reflect the search of a software gathering data on Amazon products, or an attempt at artificially boosting a product search rank by a seller.

In some of the cases of a real search term, e.g., “silk pillowcase queen size”, it is possible that there was a flaw in the data creation, as these search terms, when independently searched on Amazon, give several relevant results where one would expect more than one product would be clicked. Thankfully the number of these examples is few relative to the dataset of 1,000,000 serach terms, and they will be automatically removed from graph we will plot below because they are null.

Let’s explore some of these outlier cases where a single product garnered 100% of the clickshare for a particular search term in the broader dataset:

outlier_no_1_click_share <- search_data %>% filter(search_data$number_1_click_share == 100)
head(outlier_no_1_click_share,80)

str(outlier_no_1_click_share)

tibble [9,063 × 17] (S3: tbl_df/tbl/data.frame)
 $ department               : chr [1:9063] "Amazon.com" "Amazon.com" "Amazon.com" "Amazon.com" ...
 $ search_term              : chr [1:9063] "silk pillowcase queen size" "campling light" "campling lights" "a. b. c." ...
 $ search_frequency_rank    : num [1:9063] 39125 56329 73720 74403 75440 ...
 $ number_1_clicked_asin    : chr [1:9063] "B07Y4QMHMQ" "B09YDP9989" "B09YDP9989" "B06Y2NHTH5" ...
 $ number_1_product_title   : chr [1:9063] "THXSILK Silk Pillowcase 22 Momme 100% Pure Mulberry Natural Soft Breathable Pillow Cover with Hidden Zipper Clo"| __truncated__ "4Pcs/ Set LED Camping Light ,Lotus Blue Portable LED Tent Lantern 4 Modes for Backpacking Camping Hiking Fishin"| __truncated__ "4Pcs/ Set LED Camping Light ,Lotus Blue Portable LED Tent Lantern 4 Modes for Backpacking Camping Hiking Fishin"| __truncated__ "Alphabet Train - Learn ABCs with Vehicles and Animals for Kids" ...
 $ number_1_click_share     : num [1:9063] 100 100 100 100 100 100 100 100 100 100 ...
 $ number_1_conversion_share: num [1:9063] NA NA NA 100 NA NA NA NA NA 100 ...
 $ number_2_clicked_asin    : chr [1:9063] "null" "null" "null" "null" ...
 $ number_2_product_title   : chr [1:9063] "null" "null" "null" "null" ...
 $ number_2_click_share     : num [1:9063] 0 0 0 0 0 0 0 0 0 0 ...
 $ number_2_conversion_share: num [1:9063] 0 0 0 0 0 0 0 0 0 0 ...
 $ number_3_clicked_asin    : chr [1:9063] "null" "null" "null" "null" ...
 $ number_3_product_title   : chr [1:9063] "null" "null" "null" "null" ...
 $ number_3_click_share     : num [1:9063] 0 0 0 0 0 0 0 0 0 0 ...
 $ number_3_conversion_share: num [1:9063] 0 0 0 0 0 0 0 0 0 0 ...
 $ total_click_share        : num [1:9063] 100 100 100 100 100 100 100 100 100 100 ...
 $ total_conversion_share   : num [1:9063] NA NA NA 100 NA NA NA NA NA 100 ...

There are only ~ 9000 of such cases (~0.9% of the data) in the larger data set, and they seem to fit the types of searches we just described: either they are poor ranked terms where purchases are infrequent and we could expect a single product gathering the entirety of a term’s clickshare, or the search terms appear to be unnatural attempts at zeroing in on a single product through an ASIN.

We can visually see where the ranking concentration of these cases where only a single product contains 100% of the click share in the scatter plot below. It is clear that examples of these cases are primarily found where search terms are ranked lower where this type of case would makes sense.

Here I create a scatter plot showing the concentration of these outlier cases in different keyword search rankings. I will set alpha for our points to 0.01 because we are dealing with several thousand data points:

ggplot(data=outlier_no_1_click_share) + geom_point(mapping=aes(x=search_frequency_rank, y=total_click_share),alpha=0.05) +
  labs(title="Click Share % of Top Clicked Product vs. Keyword Search Rank", 
       caption="A visual representation showing the outlier cases of one product with 100% clickshare for a keyword", x="Search Frequency Rank for Keyword (Bigger # Means Lower Search Volume)",
       y="% of Click Share by Top Clicked Product") + coord_cartesian(xlim =c(0, 1000000), ylim = c(0, 100))

We see how these cases are infrequent where search frequency rank is greater than 250,000. This makes sense with expected customer behavior. We expect that a search term that is more frequently seached by customers will have more relevant product offerings, and therefore a greater likelihood that more than one product will be clicked by a customer.

What about cases where the top clicked product controls 100% of conversion/sales for that search term?

outlier_no_1_conversion_share <- search_data %>% filter(search_data$number_1_conversion_share == 100)
head(outlier_no_1_conversion_share, 80)

str(outlier_no_1_conversion_share)

tibble [90,768 × 17] (S3: tbl_df/tbl/data.frame)
 $ department               : chr [1:90768] "Amazon.com" "Amazon.com" "Amazon.com" "Amazon.com" ...
 $ search_term              : chr [1:90768] "amazon.co.uk" "bosch legacy season 1" "bulletproof bag" "mens sunglasses duco" ...
 $ search_frequency_rank    : num [1:90768] 4263 7123 10466 12806 13569 ...
 $ number_1_clicked_asin    : chr [1:90768] "B01FIS88SY" "B09PQSJYZM" "B07VVRZ7ZB" "B00SMRN2DU" ...
 $ number_1_product_title   : chr [1:90768] "Amazon eGift Card - Happy Birthday Balloons" "Bosch: Legacy Season 1" "Lenovo Legion 17 Armored Backpack II, Gaming Laptop Bag, Double-Layered Protection, Dedicated Storage Pockets, "| __truncated__ "Duco Mens Sports Polarized Sunglasses UV Protection Sunglasses for Men 8177s(Black Frame Gray Lens)" ...
 $ number_1_click_share     : num [1:90768] 28.6 28.6 36.4 13 15.4 ...
 $ number_1_conversion_share: num [1:90768] 100 100 100 100 100 100 100 100 100 100 ...
 $ number_2_clicked_asin    : chr [1:90768] "B01EAG3VZA" "B09PQDFV3L" "B07GGPH3CZ" "B081L8NYW1" ...
 $ number_2_product_title   : chr [1:90768] "Frito-Lay Ultimate Snack Care Package, Variety Assortment of Chips, Cookies, Crackers & More, 40 Count" "Bosch: Legacy Season 1" "ProCase Tactical Backpack Bag 40L Large 3 Day Military Army Outdoor Assault Pack Rucksacks Carry Bag Backpacks -Black" "Duco Men's Sports Polarized Driving Carbon Fiber Sunglasses for Men UV400 Protection DC8277 (Gunmetal Frame Blue Lens)" ...
 $ number_2_click_share     : num [1:90768] 8.88 25.25 10.54 11.06 13.14 ...
 $ number_2_conversion_share: num [1:90768] 0 0 0 0 0 0 0 0 0 0 ...
 $ number_3_clicked_asin    : chr [1:90768] "B09Q6DYDTD" "B09SGLBS2L" "B075SZQN9Q" "B07DNXGYC2" ...
 $ number_3_product_title   : chr [1:90768] "Clarifion - DSTx Portable Air Purifier - Plug In Air Ionizer HEPA Air Filter, Mini Personal Air Purifiers For, "| __truncated__ "The Wrong Side of Goodbye" "Tzowla Travel Laptop Backpack Water Resistant Anti-Theft Bag with USB Charging Port and Lock 15.6 Inch Computer"| __truncated__ "Duco Men's Luxury Carbon Fiber Temple Polarized Sunglasses for Men Sports UV400 DC8206" ...
 $ number_3_click_share     : num [1:90768] 6.52 16.76 4.93 10.27 11.44 ...
 $ number_3_conversion_share: num [1:90768] 0 0 0 0 0 0 0 0 0 0 ...
 $ total_click_share        : num [1:90768] 44 70.6 51.9 34.3 40 ...
 $ total_conversion_share   : num [1:90768] 100 100 100 100 100 100 100 100 100 100 ...

There are over 90000 such cases or about 9% of our data where a single product garners 100% of the sales for a search term! While this is believable in cases where customers search for an exact product, a review of the data above suggests some inaccuracy in the provided numbers. E.g., independently searching “mens sunglasses duco” (where “DUCO” is a brand) provides several well-selling mens sunglasses made by DUCO, yet the data suggests only 1 of those products has 100% of the conversion for that search term. The search is also ranked at 12806, which means it is a relatively highly searched term, which means it is more competitive and a case where this would not occur. This may reflect errant data. I will make a scatterplot to see where these types of outliers exist, i.e. where the most clicked product for a keyword controls 100% of the conversions for that keyword. Alpha is set to 0.005 because of large # of points:

ggplot(data=outlier_no_1_conversion_share) + geom_point(mapping=aes(x=search_frequency_rank, y=total_conversion_share),alpha=0.005) +
  labs(title="Conversion Share % of Top Clicked Product vs. Keyword Search Rank", 
       caption="A visual representation showing the outlier cases of one product with 100% conversion for a keyword", x="Search Frequency Rank for Keyword (Bigger # Means Lower Search Volume)",
       y="% of Click Share by Top Clicked Product") + coord_cartesian(xlim =c(0, 1000000), ylim = c(0, 100))

We see that these cases appear more for lesser searched keywords (i.e. keywords with a lower search frequency rank), but that they are also well represented in the higher ranked keywords as well. We will keep this data for now as it is hard to determine genuine cases here from non-genuine.

Let’s explore cases where the top 3 clicked products result in 0% of conversion share for a keyword:

outlier_zero_conversion_share <- search_data %>% filter(search_data$total_conversion_share == 0)
head(outlier_zero_conversion_share, 80)

str(outlier_zero_conversion_share)

tibble [175,645 × 17] (S3: tbl_df/tbl/data.frame)
 $ department               : chr [1:175645] "Amazon.com" "Amazon.com" "Amazon.com" "Amazon.com" ...
 $ search_term              : chr [1:175645] "documentaries" "kgf chapter 2" "bosch" "pime tv" ...
 $ search_frequency_rank    : num [1:175645] 140 226 237 552 605 ...
 $ number_1_clicked_asin    : chr [1:175645] "B09VFRTZLY" "B08KWQLPFF" "B089Y776DM" "B09M7RCXBB" ...
 $ number_1_product_title   : chr [1:175645] "The Catch" "K.G.F: Chapter 1 (Telugu)" "Chapter One: 'Tis the Season" "The Marvelous Mrs. Maisel - Season 4" ...
 $ number_1_click_share     : num [1:175645] 5.77 16.49 15.94 9.58 7.34 ...
 $ number_1_conversion_share: num [1:175645] 0 0 0 0 0 0 0 0 0 0 ...
 $ number_2_clicked_asin    : chr [1:175645] "B00CSCY54A" "B08KWQ5BBK" "B089XVT9D6" "B09PVRLSLB" ...
 $ number_2_product_title   : chr [1:175645] "Girl 27" "K.G.F: Chapter 1 (Hindi)" "Chapter One: 'Tis the Season" "Outer Range - Season 1" ...
 $ number_2_click_share     : num [1:175645] 3.12 11.52 9.25 6.28 5.94 ...
 $ number_2_conversion_share: num [1:175645] 0 0 0 0 0 0 0 0 0 0 ...
 $ number_3_clicked_asin    : chr [1:175645] "B09KJ9B4HL" "B08KWQ4QMH" "B09SGLBS2L" "B08X7JSCHQ" ...
 $ number_3_product_title   : chr [1:175645] "Eating Our Way To Extinction" "K.G.F: Chapter 1 (Telugu)" "The Wrong Side of Goodbye" "Insignia 32-inch Class F20 Series Smart HD 720p Fire TV (NS-32F201NA22, 2021 Model)" ...
 $ number_3_click_share     : num [1:175645] 2.39 5.74 8.79 6.11 5.24 ...
 $ number_3_conversion_share: num [1:175645] 0 0 0 0 0 0 0 0 0 0 ...
 $ total_click_share        : num [1:175645] 11.3 33.8 34 22 18.5 ...
 $ total_conversion_share   : num [1:175645] 0 0 0 0 0 0 0 0 0 0 ...

There are over 175,000 such cases. A review of some examples shows that they include very open ended search terms including “stuff” “hindi movies”, “documentaries” “moves & tv,” along with nonmeaningful search words like “them”. Many of these are instances where 0 conversion is believable, as the customer is merely doing an open search to see what is available.

Let us determine if click share and conversion share for the top 3 products varies according to search frequency rank:

Because of the extremely large number of points in our scatterplot (near 1 million), we opt for a very low alpha on each point to detect density of points.

The scatter plot below is a visual representation showing how control of click share by the top three clicked products for a keyword becomes easier to obtain for lower ranked words that are less competitive. Note that the x axis is the Search Frequency Rank for Keyword, where a larger # means lower search volume.

ggplot(data=search_data) + geom_point(mapping=aes(x=as.numeric(search_frequency_rank), y=as.numeric(total_click_share)),alpha=0.005)

What is our correlation coefficient for search frequency rank explaining the total click share of the top 3 clicked products for a search term:

cor(search_data$search_frequency_rank, search_data$total_click_share)

[1] 0.6009246

There appears to be a notable correlation, as visible in the scatterplot above as well.

Now let’s explore how the conversion share of the top 3 clicked products for a search term varies with the search frequency rank of a search keyword. The scatter plot below is a visual representation showing how control of conversion share by the top three clicked products for a keyword similarly becomes easier as the search rank of a search term goes down (i.e. it’s less competitive). Note that the x axis is the Search Frequency Rank for Keyword, where a larger # means lower search volume.

ggplot(data=search_data) + geom_point(mapping=aes(x=as.numeric(search_frequency_rank), y=as.numeric(total_conversion_share)),alpha=0.005)

Warning: Removed 2709 rows containing missing values (geom_point).

What is our correlation coefficient for search frequency rank explaining the total conversion share of the top 3 clicked products for a search term?

Note that we have some na values that need to be cleaned out first.

no_na_total_conversion_share <- search_data %>% drop_na(total_conversion_share)
cor(no_na_total_conversion_share$search_frequency_rank, no_na_total_conversion_share$total_conversion_share)

[1] 0.1430613

While the correlation coefficient is not very meaningful here, we do see a trajectory in our plotted data indicating that as a search term becomes less competitive (it has a lower search frequency rank), the top 3 products are more able to garner a larger percentage of the conversion share, though this is not as strong as we saw for click share.

Note that in the scatter plot we see the existence of ‘lines’ of points at specific % conversion intervals (0%, 33%, 50%, 66%, 75%, 100%), which makes sense given our data. The data is for 1, 2, or 3 products, and if 1,2, or 3 garner a certain % of the conversion share, we can see these lines appearing at these given intervals.