R plus Magento 2 REST API revisited: part 3 – more complex samples of use

This is 3rd part of series about working with Magento 2 REST API in R. If you haven’t read previous posts in this series, I would recommend to do it. This article sample use the functions defined in previous posts. You may find them at R plus Magento 2 REST API revisited: part 2 – filtered search and R plus Magento 2 REST API revisited: part 1- authentication and universal search

In this post we’ll work with two more complex tasks related with pulling data from Magento 2 and processing it in R.

Sample tasks 1 – get names and emails of all customers who have placed no orders in given period of time

It is quite common task – find customers who haven’t placed order in given time to target them in marketing campaign. Let’s see how this task can be done in R.

Assume that we want to get list of customers who placed no orders in May 2013.

# set parameters start_date <- "2013-05-01 00:00:00" end_date <- "2013-05-31 23:59:59"

Step 1. Get all orders over this period of time.

We assume that you are already got your Magento 2 REST API authorization token, if not check the first series post.

endpoint <- "rest/V1/orders" myquery <- list("searchCriteria[filter_groups][0][filters][0][field]"="created_at", "searchCriteria[filter_groups][0][filters][0][value]"=start_date, "searchCriteria[filter_groups][0][filters][0][condition_type]"="gt", "searchCriteria[filter_groups][1][filters][0][field]"="created_at", "searchCriteria[filter_groups][1][filters][0][value]"=end_date, "searchCriteria[filter_groups][1][filters][0][condition_type]"="lt") orders <- getm2objects(urlbase = urlbase, endpoint = endpoint, query = myquery, auth=auth)

Step 2. Get email of customers who made these orders.

# initialize empty factor and add there ids of customers who placed orders customers_ordered <- character() # run loop for (i in 1:length(orders[["items"]])){ customers_ordered[i] <- orders[["items"]][[i]][["customer_email"]] } # get rid of duplicates and combine in one string customers_ordered <- unique(customers_ordered)

We need customer emails in that format for future search.

Step 3. Get customers who are not in this list

We use there Not In search query parameter, condition time ‘nin’ in Magento 2 REST API

#form character/string for query customers_ordered_req <- paste(customers_ordered, collapse = ',') # get all who not in the list of customers ordered # using customer search endpoint endpoint <- "rest/V1/customers/search/" myquery <- list("searchCriteria[filter_groups][0][filters][0][field]"="email", "searchCriteria[filter_groups][0][filters][0][value]"=customers_ordered_req, "searchCriteria[filter_groups][0][filters][0][condition_type]"="nin") cust_no <- getm2objects(urlbase = urlbase, endpoint = endpoint, query = myquery, auth=auth)

No we have all customers who placed no orders in given period of time in cust_no object.

Step 4. Save results for further use

Finally, lets extract emails, first and last names to data frame and save it to csv file that we can load to our marketing campaign software for targeting.

emails_list <- data.frame()[1:length(cust_no[["items"]]),] for (i in 1:length(cust_no[["items"]])) { emails_list$email[i] <- cust_no[["items"]][[i]][["email"]] emails_list$fname[i] <- cust_no[["items"]][[i]][["firstname"]] emails_list$lname[i] <- cust_no[["items"]][[i]][["lastname"]] } # write as CSV file to use in your marketing software row.names(emails_list) <-NULL write.csv(emails_list, file="email-list.csv", row.names = FALSE)

Sample task 2. Compare sales for products in 2 categories for the period of time

Step 1. Define parameters: start and end date, product categories

#define dates start_date <- "2013-01-01 00:00:00" end_date <- "2013-12-31 23:59:59" # define categories, using categories ids cat1 <- "11" cat2 <- "6"

Step 2. Search through API

# set API endpoint - orders #searching invoices after specific date endpoint <- "rest/V1/orders"
Now let’s form search query. Using search with filtered data pulled.
It is important to have request started from items in filtering part of query. See the last parameter in query
We only count orders with “complete” status.
myquery <- list("searchCriteria[filter_groups][0][filters][0][field]"="created_at", "searchCriteria[filter_groups][0][filters][0][value]"=start_date, "searchCriteria[filter_groups][0][filters][0][condition_type]"="gt", "searchCriteria[filter_groups][1][filters][0][field]"="created_at", "searchCriteria[filter_groups][1][filters][0][value]"=end_date, "searchCriteria[filter_groups][1][filters][0][condition_type]"="lt", "searchCriteria[filter_groups][2][filters][0][field]"="status", "searchCriteria[filter_groups][2][filters][0][value]"="complete", "searchCriteria[filter_groups][2][filters][0][condition_type]"="eq", "fields"="items[increment_id,status,items[sku,name,row_total]]") orders <- getm2objects(urlbase = urlbase, endpoint = endpoint, query = myquery, auth=auth)

Step 3. Data wrangling orders data

No we need to do a bit of data wrangling, will use dplyr library.
library(dplyr) n <- length(orders[["items"]]) # unpack list to dataframe sales <- data.frame() for (i in 1:n){ m <- length(orders[["items"]][[i]][["items"]]) for (j in 1:m){ stemp <- data.frame()[1,] stemp$sku[1] <- orders[["items"]][[i]][["items"]][[j]][["sku"]] stemp$name[1] <- orders[["items"]][[i]][["items"]][[j]][["name"]] stemp$amount[1] <- orders[["items"]][[i]][["items"]][[j]][["row_total"]] stemp$order[1] <- orders[["items"]][[i]][["increment_id"]] sales <- rbind(sales, stemp) } } # change sales to numbers # filter out zero values (happens due to configurable products references) # and count total for sku sales$amount <- as.numeric(sales$amount) sales <- sales %>% filter(amount!=0) %>% group_by(sku) %>% summarise(total=amount, n=n())
We have a data frame of all products sold over period of time.

Step 4. Matching products with categories

We need to find product categories of the products sold over given period of time. Note that in Magento 2 each product can be in more than one category.
We use filtered search with getm2productdata function defined earlier (in part 2 of this series of articles).
fields <- "sku,extension_attributes[category_links[category_id]]" n <- nrow(sales) ### this loop takes time to run for (i in 1:n){ t <- getm2singleproductinfo(url=urlbase, sku=sales$sku[i], fields=fields, auth) cats <- unlist(t[["extension_attributes"]][["category_links"]]) ## check if sku sale falls in any of our 2 target category and add new column with logical flag sales$cat1[i] <- cat1 %in% cats sales$cat2[i] <- cat2 %in% cats }

Now we filter out the results to include the categories we are interested in and calculate total for each.

# calculate total sales_cat1 <- sales %>% filter (cat1) cat1totalsales <- sum (sales_cat1$total) sales_cat2 <- sales %>% filter (cat2) cat2totalsales <- sum (sales_cat2$total)

The numbers we need will be in cat1totalsales and cat2totalsales variables.

That is it for this blog post. As you see it is possible to use Magento 2 API combined with R provides quite a handy tool set for eCommerce data analysis.

The post R plus Magento 2 REST API revisited: part 3 – more complex samples of use appeared first on Alex Levashov – eCommerce Consultant (Melbourne, Australia).