<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/r-plus-magento-2-rest-api-revisited-part-3-more-complex-samples-of-use/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://levashov.biz/r-plus-magento-2-rest-api-revisited-part-3-more-complex-samples-of-use/"> R – Alex Levashov – eCommerce Consultant (Melbourne, Australia)</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>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 <a rel="nofollow" href="https://levashov.biz/r-plus-magento-2-rest-api-revisited-part-2-filtered-search/" target="_blank">R plus Magento 2 REST API revisited: part 2 – filtered search</a> and <a rel="nofollow" href="https://levashov.biz/r-plus-magento-2-rest-api-revisited-authentication-and-universal-search-part-one/" target="_blank">R plus Magento 2 REST API revisited: part 1- authentication and universal search</a></p>
<p>In this post we’ll work with two more complex tasks related with pulling data from Magento 2 and processing it in R.</p>
<h3>Sample tasks 1 – get names and emails of all customers who have placed no orders in given period of time</h3>
<p>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.</p>
<p>Assume that we want to get list of customers who placed no orders in May 2013.</p>
<p><code># set parameters<br>
start_date &lt;- &quot;2013-05-01 00:00:00&quot;<br>
end_date &lt;- &quot;2013-05-31 23:59:59&quot;</code></p>
<h4>Step 1. Get all orders over this period of time.</h4>
<p>We assume that you are already got your Magento 2 REST API authorization token, if not check <a rel="nofollow" href="https://levashov.biz/r-plus-magento-2-rest-api-revisited-authentication-and-universal-search-part-one/" target="_blank">the first series post</a>.</p>
<p><code>endpoint &lt;- &quot;rest/V1/orders&quot;<br>
myquery &lt;- list(&quot;searchCriteria[filter_groups][0][filters][0][field]&quot;=&quot;created_at&quot;,<br>
&quot;searchCriteria[filter_groups][0][filters][0][value]&quot;=start_date,<br>
&quot;searchCriteria[filter_groups][0][filters][0][condition_type]&quot;=&quot;gt&quot;,<br>
&quot;searchCriteria[filter_groups][1][filters][0][field]&quot;=&quot;created_at&quot;,<br>
&quot;searchCriteria[filter_groups][1][filters][0][value]&quot;=end_date,<br>
&quot;searchCriteria[filter_groups][1][filters][0][condition_type]&quot;=&quot;lt&quot;)<br>
orders &lt;- getm2objects(urlbase = urlbase, endpoint = endpoint, query = myquery, auth=auth)</code></p>
<h4>Step 2. Get email of customers who made these orders.</h4>
<p><code># initialize empty factor and add there ids of customers who placed orders<br>
customers_ordered &lt;- character()<br>
# run loop<br>
for (i in 1:length(orders[[&quot;items&quot;]])){<br>
customers_ordered[i] &lt;- orders[[&quot;items&quot;]][[i]][[&quot;customer_email&quot;]]<br>
}<br>
# get rid of duplicates and combine in one string<br>
customers_ordered &lt;- unique(customers_ordered)<br>
</code></p>
<p>We need customer emails in that format for future search.</p>
<h4>Step 3. Get customers who are not in this list</h4>
<p>We use there Not In search query parameter, condition time ‘nin’ in Magento 2 REST API</p>
<p><code>#form character/string for query<br>
customers_ordered_req &lt;- paste(customers_ordered, collapse = ',')<br>
# get all who not in the list of customers ordered<br>
# using customer search endpoint<br>
endpoint &lt;- &quot;rest/V1/customers/search/&quot;<br>
myquery &lt;- list(&quot;searchCriteria[filter_groups][0][filters][0][field]&quot;=&quot;email&quot;,<br>
&quot;searchCriteria[filter_groups][0][filters][0][value]&quot;=customers_ordered_req,<br>
&quot;searchCriteria[filter_groups][0][filters][0][condition_type]&quot;=&quot;nin&quot;)<br>
cust_no &lt;- getm2objects(urlbase = urlbase, endpoint = endpoint, query = myquery, auth=auth)</code></p>
<p>No we have all customers who placed no orders in given period of time in cust_no object.</p>
<p>Step 4. Save results for further use</p>
<p>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.</p>
<p><code>emails_list &lt;- data.frame()[1:length(cust_no[[&quot;items&quot;]]),]<br>
for (i in 1:length(cust_no[[&quot;items&quot;]])) {<br>
emails_list$email[i] &lt;- cust_no[[&quot;items&quot;]][[i]][[&quot;email&quot;]]<br>
emails_list$fname[i] &lt;-  cust_no[[&quot;items&quot;]][[i]][[&quot;firstname&quot;]]<br>
emails_list$lname[i] &lt;- cust_no[[&quot;items&quot;]][[i]][[&quot;lastname&quot;]]<br>
}<br>
# write as CSV file to use in your marketing software<br>
row.names(emails_list) &lt;-NULL<br>
write.csv(emails_list, file=&quot;email-list.csv&quot;, row.names = FALSE)</code></p>
<h3>Sample task 2. Compare sales for products in 2 categories for the period of time</h3>
<h4>Step 1. Define parameters: start and end date, product categories</h4>
<p><code>#define dates<br>
start_date &lt;- &quot;2013-01-01 00:00:00&quot;<br>
end_date &lt;- &quot;2013-12-31 23:59:59&quot;<br>
# define categories, using categories ids<br>
cat1 &lt;- &quot;11&quot;<br>
cat2 &lt;- &quot;6&quot;</code></p>
<h4>Step 2. Search through API</h4>
<p><code># set API endpoint - orders<br>
#searching invoices after specific date<br>
endpoint &lt;- &quot;rest/V1/orders&quot;</code><br>
Now let’s form search query. Using search with filtered data pulled.<br>
It is important to have request started from items in filtering part of query. See the last parameter in query<br>
We only count orders with “complete” status.<br>
<code>myquery &lt;- list(&quot;searchCriteria[filter_groups][0][filters][0][field]&quot;=&quot;created_at&quot;,<br>
&quot;searchCriteria[filter_groups][0][filters][0][value]&quot;=start_date,<br>
&quot;searchCriteria[filter_groups][0][filters][0][condition_type]&quot;=&quot;gt&quot;,<br>
&quot;searchCriteria[filter_groups][1][filters][0][field]&quot;=&quot;created_at&quot;,<br>
&quot;searchCriteria[filter_groups][1][filters][0][value]&quot;=end_date,<br>
&quot;searchCriteria[filter_groups][1][filters][0][condition_type]&quot;=&quot;lt&quot;,<br>
&quot;searchCriteria[filter_groups][2][filters][0][field]&quot;=&quot;status&quot;,<br>
&quot;searchCriteria[filter_groups][2][filters][0][value]&quot;=&quot;complete&quot;,<br>
&quot;searchCriteria[filter_groups][2][filters][0][condition_type]&quot;=&quot;eq&quot;,<br>
&quot;fields&quot;=&quot;items[increment_id,status,items[sku,name,row_total]]&quot;)<br>
orders &lt;- getm2objects(urlbase = urlbase, endpoint = endpoint, query = myquery, auth=auth)</code></p>
<h4>Step 3. Data wrangling orders data</h4>
<p>No we need to do a bit of data wrangling, will use <em><strong>dplyr </strong></em>library.<br>
<code>library(dplyr)<br>
n &lt;- length(orders[[&quot;items&quot;]])<br>
# unpack list to dataframe<br>
sales &lt;- data.frame()<br>
for (i in 1:n){<br>
m &lt;- length(orders[[&quot;items&quot;]][[i]][[&quot;items&quot;]])<br>
for (j in 1:m){<br>
stemp &lt;- data.frame()[1,]<br>
stemp$sku[1] &lt;- orders[[&quot;items&quot;]][[i]][[&quot;items&quot;]][[j]][[&quot;sku&quot;]]<br>
stemp$name[1] &lt;- orders[[&quot;items&quot;]][[i]][[&quot;items&quot;]][[j]][[&quot;name&quot;]]<br>
stemp$amount[1] &lt;- orders[[&quot;items&quot;]][[i]][[&quot;items&quot;]][[j]][[&quot;row_total&quot;]]<br>
stemp$order[1] &lt;- orders[[&quot;items&quot;]][[i]][[&quot;increment_id&quot;]]<br>
sales &lt;- rbind(sales, stemp)<br>
}<br>
}<br>
# change sales to numbers<br>
# filter out zero values (happens due to configurable products references)<br>
# and count total for sku<br>
sales$amount &lt;- as.numeric(sales$amount)<br>
sales &lt;- sales %>% filter(amount!=0) %>% group_by(sku) %>% summarise(total=amount, n=n())</code><br>
We have a data frame of all products sold over period of time.</p>
<h4>Step 4. Matching products with categories</h4>
<p>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.<br>
We use filtered search with <em><strong>getm2productdata</strong> </em>function defined earlier (in part 2 of this series of articles).<br>
<code>fields &lt;- &quot;sku,extension_attributes[category_links[category_id]]&quot;<br>
n &lt;- nrow(sales)<br>
### this loop takes time to run<br>
for (i in 1:n){<br>
t &lt;- getm2singleproductinfo(url=urlbase, sku=sales$sku[i], fields=fields, auth)<br>
cats &lt;- unlist(t[[&quot;extension_attributes&quot;]][[&quot;category_links&quot;]])<br>
## check if sku sale falls in any of our 2 target category and add new column with logical flag<br>
sales$cat1[i] &lt;- cat1 %in% cats<br>
sales$cat2[i] &lt;- cat2 %in% cats<br>
}</code></p>
<p>Now we filter out the results to include the categories we are interested in and calculate total for each.</p>
<p><code># calculate total<br>
sales_cat1 &lt;- sales %>% filter (cat1)<br>
cat1totalsales &lt;- sum (sales_cat1$total)<br>
sales_cat2 &lt;- sales %>% filter (cat2)<br>
cat2totalsales &lt;- sum (sales_cat2$total)</code></p>
<p>The numbers we need will be in <em>cat1totalsales</em> and <em>cat2totalsales</em> variables.</p>
<p>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.</p>
<p><a rel="nofollow" href="https://www.addtoany.com/add_to/facebook?linkurl=https%3A%2F%2Flevashov.biz%2Fr-plus-magento-2-rest-api-revisited-part-3-more-complex-samples-of-use%2F&linkname=R%20plus%20Magento%202%20REST%20API%20revisited%3A%20part%203%20%E2%80%93%20more%20complex%20samples%20of%20use" title="Facebook" target="_blank"></a><a rel="nofollow" href="https://www.addtoany.com/add_to/twitter?linkurl=https%3A%2F%2Flevashov.biz%2Fr-plus-magento-2-rest-api-revisited-part-3-more-complex-samples-of-use%2F&linkname=R%20plus%20Magento%202%20REST%20API%20revisited%3A%20part%203%20%E2%80%93%20more%20complex%20samples%20of%20use" title="Twitter" target="_blank"></a><a rel="nofollow" href="https://www.addtoany.com/add_to/google_plus?linkurl=https%3A%2F%2Flevashov.biz%2Fr-plus-magento-2-rest-api-revisited-part-3-more-complex-samples-of-use%2F&linkname=R%20plus%20Magento%202%20REST%20API%20revisited%3A%20part%203%20%E2%80%93%20more%20complex%20samples%20of%20use" title="Google+" target="_blank"></a><a rel="nofollow" href="https://www.addtoany.com/add_to/linkedin?linkurl=https%3A%2F%2Flevashov.biz%2Fr-plus-magento-2-rest-api-revisited-part-3-more-complex-samples-of-use%2F&linkname=R%20plus%20Magento%202%20REST%20API%20revisited%3A%20part%203%20%E2%80%93%20more%20complex%20samples%20of%20use" title="LinkedIn" target="_blank"></a><a rel="nofollow" href="https://www.addtoany.com/add_to/reddit?linkurl=https%3A%2F%2Flevashov.biz%2Fr-plus-magento-2-rest-api-revisited-part-3-more-complex-samples-of-use%2F&linkname=R%20plus%20Magento%202%20REST%20API%20revisited%3A%20part%203%20%E2%80%93%20more%20complex%20samples%20of%20use" title="Reddit" target="_blank"></a><a data-a2a-title="R plus Magento 2 REST API revisited: part 3 – more complex samples of use" rel="nofollow" href="https://www.addtoany.com/share#url=https%3A%2F%2Flevashov.biz%2Fr-plus-magento-2-rest-api-revisited-part-3-more-complex-samples-of-use%2F&amp;%23038;title=R%20plus%20Magento%202%20REST%20API%20revisited%3A%20part%203%20%E2%80%93%20more%20complex%20samples%20of%20use" data-a2a-url="https://levashov.biz/r-plus-magento-2-rest-api-revisited-part-3-more-complex-samples-of-use/" target="_blank"></a></p>
<p>The post <a rel="nofollow" href="https://levashov.biz/r-plus-magento-2-rest-api-revisited-part-3-more-complex-samples-of-use/" target="_blank">R plus Magento 2 REST API revisited: part 3 – more complex samples of use</a> appeared first on <a rel="nofollow" href="https://levashov.biz/" target="_blank">Alex Levashov – eCommerce Consultant (Melbourne, Australia)</a>.</p>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://levashov.biz/r-plus-magento-2-rest-api-revisited-part-3-more-complex-samples-of-use/"> R – Alex Levashov – eCommerce Consultant (Melbourne, Australia)</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img width="1" alt="" src="http://feeds.feedburner.com/~r/RBloggers/~4/aLsbE4K__Zs" height="1"></a></div>