Time and time again I’m amazed by the power that a sub­re­lated eVar report can pro­vide.  Believe it or not, if you’re clever (as you will be after read­ing this), you can actu­ally use Adobe Report Builder to find inter­est­ing data anom­alies for you!  First of all, if you’re not famil­iar with what an eVar sub­re­la­tion is, let me explain:

To cre­ate a sub­re­la­tions report, sim­ply click on the “break down” icon next to an eVar’s name.  I’ve pulled a report from my per­sonal blog, Voodoolyt​ics​.com, to illus­trate a sim­ple exam­ple.  In this case, it’s a “Content:Title” report:

Then, after select­ing the vari­able you’d like to sub­re­late against (in this case browser lan­guage), you’ll get a report that looks like this:

A break­down met­ric count rep­re­sents the num­ber of times that the event or met­ric was set while both val­ues were per­sist­ing.  For exam­ple, you can see that one of my attri­bu­tion mod­el­ing arti­cles had 46 unique vis­i­tors for this time period, 5 of which spoke Japan­ese, 33 spoke Amer­i­can Eng­lish, 4 spoke UK Eng­lish, and 1 spoke Ger­man.  Sim­ple enough, right?

This infor­ma­tion can be valu­able in and of itself, how­ever, when there are more than three or four eVar val­ues, it’s very dif­fi­cult to comb through so many break­downs to find mean­ing­ful rela­tion­ships.  That’s where Report Builder and a lit­tle Excel wiz­ardry come in.

I’ve cre­ated an exam­ple using some spoofed data to illus­trate.  First, cre­ate a report builder request that looks sim­i­lar to this:

Notice that I have two eVars selected.  This will break­down prod­uct cat­e­gory by coun­try using orders as the met­ric.  Also notice that I have “Pivot Lay­out” selected and have the top 100 val­ues set as a fil­ter to ensure I get a siz­able sam­ple from the data.

The out­put columns will look some­thing like this:

Once you’ve pulled that data request, cre­ate 2 new data requests that have each eVar indi­vid­u­ally (with­out any kind of break­down).  Once you’ve pulled that data, cre­ate a col­umn that shows the per­cent of the total.  Those two reports will look sim­i­lar to this:

Now that we have these three data sets we can do some pretty use­ful analy­sis.  First, append a col­umn to your break down data called “Pre­dicted Value”.  To cal­cu­late what the pre­dicted value for each break­down is, we’ll mul­ti­ply the total orders that a Prod­uct Cat­e­gory had by the per­cent the cor­re­spond­ing coun­try rep­re­sented in the dataset.  For exam­ple, if “Week­end Escapes” had 11608 orders, and Italy rep­re­sented 16.75% of the total orders, we’d expect Italy to have 11608 x 16.75% = 1944 orders of “Week­end Escapes”.  Hint: Use Vlookups to make this easier!

Now that we have the pre­dicted value for each row of the break­down dataset, cre­ate another col­umn to cal­cu­late the per­cent­age dif­fer­ence of our pre­dicted value com­pared to what actu­ally hap­pened.  This can be cal­cu­lated using (Actual Orders – Pre­dicted Orders) / Pre­dicted Orders.

Here’s the basic for­mula in Excel:

Finally, with these two addi­tional cal­cu­lated columns, you can sort the data accord­ing to the per­cent­age dif­fer­ence.  I also like to fil­ter out any­thing less than 100 orders to ensure sta­tis­ti­cal sig­nif­i­cance.  Once you’ve done that, you should see some­thing like this:

Now this is some­thing very use­ful!  I’ve fil­tered the data to only show the dif­fer­ences that were very high or very low because those are the most inter­est­ing.  First, you can notice that vis­i­tors from France were very drawn to “Musi­cals & Plays”, but were also more averse to “Walk­ing Tours”; Ital­ians seem to pur­chase “Walk­ing Tours” more than other peo­ple might and do not pur­chase “Week­end Cruises” as much as we would predict.

This infor­ma­tion would be very valu­able to a mar­keter look­ing to cre­ate a mar­ket­ing strat­egy for these dif­fer­ent geo­graphic seg­ments.  It also brings for­ward some very inter­est­ing tar­get­ing strate­gies that could be used with Adobe Test&Target.

Remem­ber that this tech­nique could be used for any two eVars, and by using Report Builder, this process could be eas­ily auto­mated for any report suite as well!  Finally, Adobe Con­sult­ing can pro­vide even more exten­sive cus­tom datasets (using multi-level break­downs for exam­ple) if you want to dive even deeper into your analy­sis.  Hope­fully, you’ll never look at an eVar break­down in the same way again!

0 comments