Using R to create a trended report from Adobe Analytics

If you frequently work with Adobe Analytics, you probably created trended reports as well. It’s a great tool; but recently I bumped into a small issue. I got a question from a client, for which – I thought – I could easily give them an answer.
But soon I noticed the issue wasn’t so easy to solve. Without going into any further details, this was my problem:

I needed a trended report, for 7 variables. But Adobe puts a limit of five on this, so there were always 2 variables missing. (For your information, the same limitation to 5 variables is set by Google Analytics as well.)

If you have Adobe Premium; this issue could be easily fixed by using the Workbench. And if you have a Windows computer, the problem can also be fixed by using the plug-in for Excel.

But guess what, I work on a mac, and I don’t have Adobe Premium. If I pottered long enough, I could have fixed something with the use of a virtual machine such as VMWare Fusion (this runs alongside MacOS X), but this would be too much of a hassle.

Because of my background in data analytics, I decided to look into R, and there the answer was awaiting! R Studio, as so often, was my savior. I could find some information online on how to use R to create a trended report with more than 5 variables, but it took going back and forth between a few sites to finally get the solution. So, without further ado: here is the detailed guideline on how to create a trended report in R using the RSiteCatalyst package.

If you are already experienced in R, you may skip the first step. Just to provide a complete guide, I documented all my steps.

1. Set your working directory

First things first, make sure you are in the correct working directory. This is important if you want to save your data later. Setting up or changing this is very easy:

2. Install package

This step is easy, just install the package and activate it.

3. Authentication 

If you want to get data out of your Adobe analytics account, you need to authenticate yourself. Therefore, you need to know your key and your secret. Both can be found in Adobe Analytics through this way:
Admin > admin tools home > manage company settings > Web services.

Here you can get an overview of the people who have access, their name, username and shared secret. Under “key” you have to put your Web Service Username (this is your login followed by your company, and separate by a colon). Under “secret” you have to put your Shared Secret, this is a combination of letters and numbers. The code goes as follows:

4. Report suite

Select the correct report suite out of which you want to extract your data. To get an overview of the report suite ID and name just type GetReportSuites(). The rsid name is the one you will need in the next step to indicate out of which report suite you want to extract the data.

5. Creating the trended report

We can already create our trended report! It is super easy, here is the code

As you can see, I worked with a classification. You do not have to use this when you don’t need it. The reason I created it was because I had dates in Adobe Analytics, but needed names of the week. So, I created a classification summing up all the same days of the week.

Just be complete, a short description of the different options you can use in this trended report:

topIf you want to limit the number of rows to include. This is a list, e.g. c(10,5)
startThe number of the row from where you want to start (default = 1)
selected List of specific items (of the first element) to include in the report. The reason I used this, there is no limit to 5 elements like in Adobe Analytics itself
searchList of keywords for the first specified element. This overrides anything that you specified in “selected”
search.typeString specifying the search type: 'and', or, 'or' 'not' (defaults to 'or')
date.granularityTime granularity of the report. This can be year, month, week, day or hour
segment.idId(s) of segments, if you want this trended report for a specific segment
segment.inlineInline segment definition
classificationSAINT classification. This applies to “elements”. If you have more than one you need to indicate to which element this applies
anomaly.detectionSet to TRUE if you want to include forecast data (this is only valid for day granularity with small date ranges)
date.currentTRUE or FALSE - whether to include current data for reports that include today's date
expediteSet to TRUE to expedite the processing of this report
interval.secondsHow long to wait between attempts
max.attemptsNumber of API attempts before stopping
validateWhether or not to submit report definition for validation before requesting the data
enqueueOnlyOnly enqueue the report, don't get the data. This returns a report id, which you can later use to get the data

The trended report automatically shows the date in the first column. If you want to put this in a different format, such as days of the week, you can use this code:

You may also notice some empty columns, automatically added in the trended report, such as url, and If you do not want to see them (when you did not fill this in) you can just remove them easily by typing:

Now you have a great report showing more than 5 variables. Here is how I visualized it:

This gives you a similar graph as in Adobe Analytics, but containing more than five values.

The last thing I did, and which you may have noticed as well, in Adobe Analytics you can also select the day of the week that you want to see in the trended report. For example, only Saturdays, or only weekdays. This can be solved as well. I did is as followed, just by aggregating the data per day of the week. There is probably a shorter code for this, I am not an R wizard yet, but it works.

This gives you separate reports per day of the week. To give you a better idea of what I mean, here is an example of the above for Friday and Wednesday, visualized.

Hopefully this can help you, in one way or another…

If you have a comment, question, better code, encountered the same issue or any remarks concerning this article, please put it in the comment section below. The more we know, the better!

That’s it for now, see ya!


Leave a Reply