Time and time again I’m amazed by the power that a subrelated eVar report can provide.  Believe it or not, if you’re clever (as you will be after reading this), you can actually use Adobe Report Builder to find interesting data anomalies for you!  First of all, if you’re not familiar with what an eVar subrelation is, let me explain:

To create a subrelations report, simply click on the “break down” icon next to an eVar’s name.  I’ve pulled a report from my personal blog, Voodoolytics.com, to illustrate a simple example.  In this case, it’s a “Content:Title” report:

Then, after selecting the variable you’d like to subrelate against (in this case browser language), you’ll get a report that looks like this:

A breakdown metric count represents the number of times that the event or metric was set while both values were persisting.  For example, you can see that one of my attribution modeling articles had 46 unique visitors for this time period, 5 of which spoke Japanese, 33 spoke American English, 4 spoke UK English, and 1 spoke German.  Simple enough, right?

This information can be valuable in and of itself, however, when there are more than three or four eVar values, it’s very difficult to comb through so many breakdowns to find meaningful relationships.  That’s where Report Builder and a little Excel wizardry come in.

I’ve created an example using some spoofed data to illustrate.  First, create a report builder request that looks similar to this:

Notice that I have two eVars selected.  This will breakdown product category by country using orders as the metric.  Also notice that I have “Pivot Layout” selected and have the top 100 values set as a filter to ensure I get a sizable sample from the data.

The output columns will look something like this:

Once you’ve pulled that data request, create 2 new data requests that have each eVar individually (without any kind of breakdown).  Once you’ve pulled that data, create a column that shows the percent of the total.  Those two reports will look similar to this:

Now that we have these three data sets we can do some pretty useful analysis.  First, append a column to your break down data called “Predicted Value”.  To calculate what the predicted value for each breakdown is, we’ll multiply the total orders that a Product Category had by the percent the corresponding country represented in the dataset.  For example, if “Weekend Escapes” had 11608 orders, and Italy represented 16.75% of the total orders, we’d expect Italy to have 11608 x 16.75% = 1944 orders of “Weekend Escapes”.  Hint: Use Vlookups to make this easier!

Now that we have the predicted value for each row of the breakdown dataset, create another column to calculate the percentage difference of our predicted value compared to what actually happened.  This can be calculated using (Actual Orders – Predicted Orders) / Predicted Orders.

Here’s the basic formula in Excel:

Finally, with these two additional calculated columns, you can sort the data according to the percentage difference.  I also like to filter out anything less than 100 orders to ensure statistical significance.  Once you’ve done that, you should see something like this:

Now this is something very useful!  I’ve filtered the data to only show the differences that were very high or very low because those are the most interesting.  First, you can notice that visitors from France were very drawn to “Musicals & Plays”, but were also more averse to “Walking Tours”; Italians seem to purchase “Walking Tours” more than other people might and do not purchase “Weekend Cruises” as much as we would predict.

This information would be very valuable to a marketer looking to create a marketing strategy for these different geographic segments.  It also brings forward some very interesting targeting strategies that could be used with Adobe Test&Target.

Remember that this technique could be used for any two eVars, and by using Report Builder, this process could be easily automated for any report suite as well!  Finally, Adobe Consulting can provide even more extensive custom datasets (using multi-level breakdowns for example) if you want to dive even deeper into your analysis.  Hopefully, you’ll never look at an eVar breakdown in the same way again!