<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="http://www.r-bloggers.com/adobe-analytics-clickstream-data-feed-calculations-and-outlier-analysis/"></div></div><div><a data-url="http://www.r-bloggers.com/adobe-analytics-clickstream-data-feed-calculations-and-outlier-analysis/" data-counturl="http://www.r-bloggers.com/adobe-analytics-clickstream-data-feed-calculations-and-outlier-analysis/" data-count="horizontal" data-text="Adobe Analytics Clickstream Data Feed: Calculations and Outlier Analysis" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://randyzwitch.com/adobe-analytics-clickstream-data-feed-calculations/"> R – randyzwitch.com</a></strong>, and kindly contributed to <a rel="nofollow" href="http://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>In a previous post, I outlined how to load <a target="_blank" href="http://randyzwitch.com/adobe-analytics-clickstream-data-feed-relational-database/">daily Adobe Analytics Clickstream data feeds</a> into a PostgreSQL database. While this isn’t a long-term scalable solution for large e-commerce companies doing millions of page views per day, for exploratory analysis a relational database structure can work well until a more robust solution is put into place (such as Hadoop/Spark).</p>
<h2>Data Validation &lt;groan></h2>
<p>Before digging too deeply into the data, we should validate that data from the data feed in our database (<a target="_blank" href="https://gist.github.com/randyzwitch/7a9c48e7132e6ed9dfb0d02ec906961c">custom database view code</a>) matches what we observe from other sources (mainly, the Adobe Analytics interface and/or <a target="_blank" href="http://randyzwitch.com/tag/rsitecatalyst/">RSiteCatalyst</a>). Given the Adobe Analytics data feed represents an export of the underlying data, and Adobe provides the formulas in the <a target="_blank" href="https://marketing.adobe.com/resources/help/en_US/sc/clickstream/datafeeds_calculate.html">data feed documentation</a>, <em>in theory</em> you should be able to replicate the numbers exactly:<br> The code snippet above shows the validation, and sure enough, the “two different sources” show the same exact values (i.e. differences are 0), so everything has been loaded properly into the PostgreSQL database.</p>
<h2>Finding Anomalies For Creating Bot Rules</h2>
<p>With the data validated, we can now start digging deeper into the data. As an example, although I have <a target="_blank" href="https://marketing.adobe.com/resources/help/en_US/reference/bot_rules.html">bot filtering</a> enabled, this only handles bots on the <a target="_blank" href="http://www.iab.com/guidelines/iab-abc-international-spiders-bots-list/">IAB bot list</a> but not necessarily people trying to scrape my site (or worse).</p>
<p>To create a <a target="_blank" href="https://marketing.adobe.com/resources/help/en_US/reference/t_create_bot_rules.html">custom bot rule in Adobe Analytics</a>, you can use IP address(es) and/or User-Agent string. However, as part of data exploration we are not limited to just these features (assuming, of course, that you can map your feature set back to an IP/User-Agent combo). To identify outlier behavior, I’m going to use a technique called ‘<a target="_blank" href="http://www.dbs.ifi.lmu.de/Publikationen/Papers/LOF.pdf">local outlier factors</a>‘ using the <a target="_blank" href="https://cran.r-project.org/web/packages/Rlof/index.html">Rlof</a> package in R with the following data features:</p>
<ul>
<li>Distinct Days Visited</li>
<li>Total Pageviews</li>
<li>Total Visits</li>
<li>Distinct Pages Viewed</li>
<li>Pageviews Per Visit</li>
<li>Average Views Per Page</li>
</ul>
<p>These aren’t the only features I could’ve used, but it should be pretty easy to view bot/scraper traffic using these metrics. Here’s the code:</p>
<p>A local outlier factor greater than 1 is classified as a potential outlier. Here’s a visual of the lof scores for the top 500 <em>worst</em> scoring IP addresses (<a target="_blank" href="https://gist.github.com/randyzwitch/178d72e01e30943f6af82c48a47c4478">vegalite R graph code</a>):</p>
<div></div>
<p></p>
<p>We can see from the graph that there are at least 500 IP addresses that are potential outliers (since the line doesn’t go below a lof value of 1). These points are now a good starting place to go back to our overall table and inspect the entire datafeed records by IP address.</p>
<h2>But what about business value?</h2>
<p>The example above just scratches the surface on what’s possible when you have access to the raw data from Adobe Analytics. It’s possible to do these calculations on my laptop using R because I only have a few hundred-thousand records and IP addresses. But this kind of ops work is pretty low-value, since unless you are trying to detect system hacking, trying to find hidden scrapers/spiders in your data to filter out just modifies the denominator of your KPIs it doesn’t lead to real money per se.</p>
<p>In the last post of this series, I’ll cover how to work with the datafeed using Spark, and provide an example of using <a target="_blank" href="http://spark.apache.org/docs/latest/mllib-guide.html">Spark MLLib</a> to increase site engagement.</p>
<p><div><div><div><div data-width="55" data-show-faces="false" data-send="true" data-layout="box_count" data-height="62" data-href="http://www.r-bloggers.com/adobe-analytics-clickstream-data-feed-calculations-and-outlier-analysis/"></div></div><div><a data-url="http://www.r-bloggers.com/adobe-analytics-clickstream-data-feed-calculations-and-outlier-analysis/" data-counturl="http://www.r-bloggers.com/adobe-analytics-clickstream-data-feed-calculations-and-outlier-analysis/" data-count="vertical" data-text="Adobe Analytics Clickstream Data Feed: Calculations and Outlier Analysis" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="http://randyzwitch.com/adobe-analytics-clickstream-data-feed-calculations/"> R – randyzwitch.com</a></strong>.</div>
<hr>
<a rel="nofollow" href="http://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="http://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a title="The R Project for Statistical Computing" rel="nofollow" href="http://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="http://www.r-bloggers.com/?s=data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="http://www.r-bloggers.com/?s=Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="http://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="http://www.r-bloggers.com/?s=map">maps</a>, <a title="Animation in R" rel="nofollow" href="http://www.r-bloggers.com/?s=animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="http://www.r-bloggers.com/?s=RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="http://www.r-bloggers.com/?s=sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="http://www.r-bloggers.com/?s=LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="http://www.r-bloggers.com/?s=SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="http://www.r-bloggers.com/?s=eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="http://www.r-bloggers.com/?s=git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="http://www.r-bloggers.com/?s=hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="http://www.r-bloggers.com/?s=Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="http://www.r-bloggers.com/?s=PCA">PCA</a>, <a title="Time series" rel="nofollow" href="http://www.r-bloggers.com/?s=time+series">time series</a>, <a title="finance trading" rel="nofollow" href="http://www.r-bloggers.com/?s=trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/KAVpLyFEouI"></a></div>