Visualizing Adobe Analytics Report Suites for free with Python and Power BI
Adobe Analytics is super flexible in the way it can be set up to exactly match all requirements of business users, analysts, and developers. A crucial part of any implementation is the creation and configuration of the Report Suites, which can be seen as the backend database of Adobe Analytics, that will hold the events sent to Adobe Analytics. In theory (and practice in some setups), each and every Report Suite can have a completely individual set of variables and metrics.
However, having the option to create an individual configuration of dimensions and events for each Report Suite comes with a hefty long-term cost. For example, each and every setup needs to be implemented in Adobe Launch, where the on-page data layer needs to be matched to the dimensions and metrics of the Report Suite. If every Report Suite is configured differently, a lot of work needs to be put into the implementation to map fields individually. Moving data collection from one Report Suite to another becomes an unfeasible nightmare. Another example is Analysis Workspace, where projects can be used for each and every Report Suite as long as the metrics and dimensions work the same. If they don’t do the same, every Report Suite will need its own set of projects, so a lot of work is duplicated.
So having one and the same setup for all Report Suites definitely pays off in the long run. But once your company decides to move to a unified Report Suite configuration, you will soon notice how difficult it is to get a clear picture of how dimensions and metrics are configured across your different Report Suites. For example, this is how the Evar settings look like for me:
As you can see, I selected “only” six Report Suites for this screenshot (1). Starting with the Tracking Code dimensions, we can immediately see how the expiration is configured differently somewhere on those six Report Suites (2). How are they configured? We don’t know and have no way to see, except if we would go through all of them individually. That’s a nightmare! Same goes for the Evars pictured below, which are named differently (3), have different expiration (4) and enablement (5) statuses. While it is super easy to change the setting for all selected Report Suites, there is no way to get a clear picture of the differences in configuration.
Luckily, we can quickly build a tool that helps us understand our setup out of free or open source components. The aanalytics2 Python package by analytics-superhero Julien Piccini can pull a lot of information out of Adobe Analytics using its API. And with Microsofts Power BI, we have a great visualization tool on our hands that integrates nicely with Python and doesn’t cost anything for the desktop client. Let’s get our hands dirty and see what we can achieve!
Step 0: Preparations
Before we can actually start, we need to prepare our environment. If you haven’t already, you can download the desktop client of Power BI for free from Microsoft’s website. To connect to Adobe Analytics’ APIs, you also need to install the Python scripting language and Julien’s aanalytics2 package. The setup is quite simple, take a look at the documentation for the exact steps.
After you have installed everything and connected to the Adobe Analytics API successfully, there are a few things we need to keep in mind. The two most important details are that, since we will use Python in Power BI, we should make our lives easy and use absolute paths for the configuration files. In the Python script, we will use an absolute path to find the config file, like for example importConfigFile(‘C:\your directory\config_analytics_template.json’) instead of the relative reference. In that JSON file, we will also use an absolute path for the key file, like “pathToKey”: “C:\\your directory\\privatekey.key”. Now we are good to start in Power BI!
Step 1: Using Python in Power BI
Once the initial setup is done, we can start bringing the data to Power BI. To do that, we need to add a new data source first from the “More…” section:
In the dialog, we can find the “Python script” option in the “Other” category. Select it and click connect:
This will open a new dialog where you can put your Python code in. This is where the magic starts! We need to pull our data out of Adobe Analytics and hand it over to Power BI in the shape of a Pandas data frame. Sounds daunting, but it is actually super easy. Here is my full code:
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)
rsids = ["reportSuite1","reportSuite2"]
data = []
dimensions = mycompany.compareReportSuites(rsids,element="dimensions").to_dict()
for item in dimensions:
for item2 in dimensions[item]:
data.append((item[1],item[0],item2,dimensions[item][item2],dimensions[("different","")][item2]))
metrics = mycompany.compareReportSuites(rsids,element="metrics").to_dict()
for item in metrics:
for item2 in metrics[item]:
data.append((item[1],item[0],item2,metrics[item][item2],metrics[("different","")][item2]))
df = pd.DataFrame(data,columns=['RSID','Attribute','Component','Value','Different'])One thing you will immediately notice: I am not a developer and naming things is hard. Feel free to make this more elegant in your own script, but here is what mine does:
- In lines 1 and 2, we pull the needed libraries into our script
- Lines 4 to 9 handle the connection to the Adobe Analytics API. Make sure you change the path to the configuration file to match your environment.
- In line 10, we have the list of Report Suites that we want to analyze. You can add your own here, as many as you like.
- Lines 13 to 16 and 18 to 21 are identical, the first queries for dimensions and the second for metrics. Note how both are added to the result list in a format that allows us to have all elements in one, big list.
- Line 23 then defines the actual resulting data frame that we hand over to Power BI. This is also where I define the name of the columns that should show up in Power BI.
That is not a lot of code! Let’s see what Power BI makes of this.
Step 2: Further process data in Power BI
Once we execute the code detailed above, Power BI gives us the result as a nice table:
Next up, let’s make the Component column a bit more readable. To do that, we first duplicate it, by right-clicking the column header and selecting Duplicate:
Now we will right-click our new column and, from the Split Column section, split it by a delimiter. Specifically, we want to split it after the first slash, so we can configure the step like this:
This will split the component field to give us the type and name of the component in separate columns. Double-clicking the column headers lets us rename it, I named mine like this:
As a last step, we now only need to remove the fields with an empty Report Suite ID. We can do this super easy by clicking the filter icon of the first column and selecting “Remove Empty”:
Now we just need to hit “Close & Apply” to make our data become available in the front end. Let’s build our report!
Step 3: Visualizing Report Suite configuration in Power BI
There are a bunch of ways we can analyze the Report Suite configuration. To keep things simple for the start, we will pull an empty Matrix visualization on the canvas:
Now we can start dragging and dropping fields from our table into the visualization (I wonder where I did that before in my past…). By using more than one field we will create a breakdown drilldown option, so we can really dive deep into our data. In my Matrix, I’ve dragged those fields into the list of Rows:
Right below that, we can configure the metrics for our Matrix, again by dragging and dropping from the list of fields. I’ve dragged the Component Name, Value, and RSID columns in there:
By default, Power BI might display a text value instead of a number. To change that, click the little arrow on the metrics and change them to “Count (Distinct)”, since we want to have the number of items instead of string values:
To round things off, we will add a little bit of color-coding into the Matrix. To do that, click the icon on the Value field and go to the background formatting:
For my simple example, I just want a green background for 1 and a red background for everything above that, so I configure the formatting like this:
With all that done, my Matrix now looks like this:
Now let’s see what we find in our data!
Step 4: Analyze Report Suite setup
On the first level show above, we can already see how many components are configured consistently across all Report Suites (1141 in my case) and how many have differences (61 for me). By clicking the little plus icon on each row, we can now drill down into the differences in my two report suites:
With this little drilldown, we can immediately see that we have 10 metrics and 51 dimensions with non-uniform configurations in my two Report Suites. Let’s look at some of those dimensions in more detail by, again, clicking the little plus-icon:
This now gives us the different components by their backend name. You can see how, for the campaign dimension, there is more than one row. There is a simple reason for that: Classifications! A row like “campaign.1” signifies the first classification dimension of the campaign dimension. Let’s see what is different in that first campaign classification:
This is where the color-coding comes in super handy. All the green rows signify a consistent configuration across all Report Suites, whereas the red values immediately show where the differences are. In our case, we can see that the name and title of the dimension is configured differently. But how are they named? Let’s again drill into the name field:
There we have it! In one Report Suite, the first campaign classification is called “Campaign”, whereas it is named “Channel” in the second one. Drilling down one last time precisely shows in which Report Suites the names are different:
Now we know! And just as easy, we can find Report Suites that don’t have the respective dimension activated at all, like below:
That is a super neat result for not a lot of work. Time for the wrap up!
Conclusion
I personally think it is super amazing what a little bit of Python code in Power BI can do to help us streamline our usage of dimensions and metrics in Adobe Analytics. Of course all props go to Julien for making this so easy for us in the first place by creating the awesome aanalytics2 Python package. Last, but not least, it still amazes me how a great tool like Power BI is still free, at least on the desktop. With this report in our back pocket, we can make any unification project super easy and quick, at least to get to the point where we know what we need to do.
As always, I hope you enjoyed this post. Let me know if you would like to see some more examples of how Power BI and Python can help us do analytics even better. Have a great day!
German Analyst and Data Scientist working in and writing about (Web) Analytics and Online Marketing Tech.