Monitor Adobe Analytics usage for free with Power BI and Python

Adobe Analytics is, without a doubt, the most complete and feature-rich product for Web Analytics and Reporting on the market. I won’t even try to list all its features, since I would definitely forget some or would have to update the list in a few months as new functionality is released. And while I, as an analyst and power user, love to have all those great tools available, they create a challenge for me in my other role as an analytics admin.

All of those features bring complexity to the every-day work of our business users. For example, when Analysis Workspace was released in 2016, it meant that users had to learn a new interface to get the most value out of Adobe Analytics. But as an admin who knows their users, I have a strong feeling that some people still use the old Reports & Analytics interface in 2021. So if we would switch that ancient interface off, we would first need to identify the users of those legacy interfaces and inform them about the change.

There are even more use cases where I would really like to know what my users are doing in the tool. Here are some examples:

• As mentioned above, I want to enable my company to get rid of Reports & Analytics by removing access to the interface. To do that, I need to know who still uses R&A, what those users are doing there, and what we would need to build in Workspace first before we make the change.
• Whenever we give people access to Analytics and train them to use the tool, it would be helpful to know if and how they are using it.
• If you manage some shared-with-everyone projects in Analysis Workspace, it is crucial to know if and who uses those to identify opportunities for new preset reports.
• In general, if a company buys into a tool like Adobe Analytics, it’s important to monitor overall user adoption across the company to ensure the maximum ROI through active users.

The topic for this post is around those use cases. Similar to what I did in a previous post, where I visualized the Adobe Analytics Report Suite structure, we are going to pull the Adobe Analytics usage logs into the free Power BI Desktop app to show what our users are up to. In the end, we are going to have a nice overview like this, showing usage numbers and features used:

Let’s start with the first step!

Step 0: Preparations

To get us ready to pull our Adobe Analytics usage data into Power BI, we need to go through the same process as we did in step 0 and 1 in the last post. I won’t go through the full process here again, but the requirements in summary are:

• Create an Adobe.io integration to use the Analytics API
• Download and install the aanalytics2 Python package from the amazing Julien Piccini
• Download and install the free Power BI Desktop client
• Create a new Power BI Report and add a Python data source as described before

With all that done, we can now look at the Python code we need to collect the usage logs.

Step 1: Query Adobe Analytics usage logs with Python in Power BI

Now we need to think of the actual Python code we need to run to get the usage data. And I have some great news for you: Thanks to Julien’s great work with the aanalytics2 package, it really only needs one line of code to get the data once the connection is initialized.

import aanalytics2 as api2
import pandas as pd
api2.importConfigFile('C:\your directory\config_analytics_template.json')
login = api2.Login()
cids = login.getCompanyId()
cid = cids[0]['globalCompanyId']
mycompany = api2.Analytics(cid)

df = mycompany.getUsageLogs()

Lines 1 to 7 are exactly the same as before and take care of the connection to the Adobe Analytics API. The magic happens in line 9, where we query the API for, by default, the last three months of usage data. The result ends up in a data frame that can then be used by Power BI to further process the data. Super simple, right?

There is a limitation on the Adobe Analytics API to only report for up to three months of data in one request. If you need more than that, you could create a simple for-loop that iterates over the last n years in three-month-sets.

While in the last post, we needed some additional processing to make best use of the data, we don’t need to do much this time to use the data in Power BI. In fact, this is how my data looks like in Power BI right now:

Instead of processing this further, we will focus on adding a bit more context to our data through a lookup for the event type and a custom calendar.

Step 2: Adding a event type lookup and custom calendar

As you can see in the screenshot above, the Adobe Analytics usage logs API does not return a very understandable description of the type of an event. But thanks to this great post on the Experience League, we can just create a new table in Power BI as a lookup for the event type. To add this table, just click “Enter data” (3) in the Data view (1):

Once you paste the whole table from the post, it will give you the nice table (4) you can see in the screenshot. Before we link that lookup to our data, let’s create one more additional table for a custom calendar (more on why we do this later). In the same section (1) click on “New table” (2) and add the below query (3):

Dates = 
  GENERATE ( 
    CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2021, 12, 31 ) ), 
    VAR currentDay = [Date]
    VAR day = DAY( currentDay )
    VAR month =  MONTH ( currentDay ) 
    VAR year =  YEAR ( currentDay )
  RETURN   ROW ( 
    "day", day, 
    "month", month, 
    "year", year,
    "dates",year&"-"&month&"-"&day)
  )

This will give you a nice table with a row for every date from 2017 to the end of 2021 (4). We need this to make sure that days without events don’t show as missing in the report. Go ahead and add a similar formatted column like the “dates” column above to the logs table.

Next, we need to link our three tables in the model view.

Step 3: Link tables in Power BI’s model view

To allow Power BI to use our newly created tables as lookups, we need to link them to the original table with our usage logs. To achieve that, go to the Model view (1) and draw a connection from the calendar table’s “dates” column to the “dateCreated” column of the logs table (2) and one more connection from the “eventType” column in the logs table to the “Event Type (API)” column in the lookup table (3) like this:

With this data all connected, we can now finally create some visualizations in the report!

Step 4: Add a trended view to the report

Let’s start this off simple. In the report view, add a Line chart with the date (from the calendar table, not the logs table!) as X-axis. I’ve added the login column twice, once on the primary and secondary axis each like that:

The secret sauce here is the difference between the values and secondary values. While the first is just a simple count of values (showing how many events were logged on a date), the second is a Count Distinct on the login field. This effectively gives us a number of Unique Users who have been using Adobe Analytics on a certain date and can be set like this:

This is already super helpful to see the overall trend in our Adobe Analytics usage. We can see trends in usage, overall adoption, and engagement per user just by looking at those two lines and their convergence or divergence!

But now, let’s go one step further.

Step 5: Adding a feature usage breakdown

To see what our users are doing in Adobe Analytics, let’s now add a table that gives us that exact information. In the Report view, add a Matrix visualization and drag the Event Type (UI), eventDescription, and login columns into the rows and the event and Unique User count (as before) to the values. The result should look like this:

In my example, I already drilled in to the “Report viewed” item, as that will show me precisely which reports in the old Reports & Analytics interface have been viewed by the 29 users who still use it. Drilling down further would show me which users I would need to approach if I wanted them to stop using the interface. And since we have created a custom calendar, I could also just click the Report viewed item to filter the trend chart to analyze trends in R&A usage too. Pretty cool!

Conclusion

I find it quite amazing how 30 minutes of work can give you an awesome result like this! Big props to Julien for creating the library that makes this whole adventure so super simple. As always, let me know what you think of this approach and the cool things that you will build based on this post!

Frederik Werner

German Analyst and Data Scientist working in and writing about (Web) Analytics and Online Marketing Tech.

2021&2022 Adobe Analytics Champion

EMEA Adobe Analytics User Group Lead

www.frederikwerner.de/