Avinash Revisited – Part III (ROI Distribution Report)

The third ClickEquations Analyst report featured in Avinash Kaushik’s recent blog post is our ROI Distribution report, which enables you to analyze the performance of your campaigns and ad groups against high and low boundary conditions that you set. This helps you to know what percentage of your campaigns are achieving targets or falling below minimum goals.

To use the report, you first set your targets. In the ‘Target ROI’ box you can enter the return-on-investment goals you want to check. First enter your actual target in cell X17 (labeled ‘Great’) and then enter your minimum acceptable ROI in cell X18 (labeled ‘Poor’).

Of course, these are subjective targets, but the point is to establish boundaries and find out how different aspects of your campaign are performing relative to these boundaries. By entering a value which we would consider ‘great’ and another one we would consider ‘poor’ we can find out how different components of our compaign are performing relative to these two markers.

With your targets set, simply click the ‘Refresh All’ button in the ClickEquations Analyst Pallete. Note that this is perhaps the most complicated of our reports in terms of collecting and analyzing a lot of data – so this one takes a while. Actual time will depend upon the size of your campaigns, but processing times of 10 minutes or more are not unusual.

When complete you’ll get data tables and graphs showing the analysis of your Google campaigns. The provided report doesn’t offer Yahoo or MSN versions yet, but you could modify the template yourself if you wish. Similarly, this report analyzes ROI – and ROAS version will be released in the future, or you could modify this one if you track ROAS rather than ROI.

In our example, we see that 23% of our campaigns have achieved or exceeded our top goal delivering a 1715% ROI average. Better yet, 1% of our spend is bringing in over 22% of our revenue. Those are productive campaigns.

Interestingly, only 2 of our campaigns are between our minimum and our target. Which leaves a whopping 29 campaigns, or 97% of our spend falling below our designated ‘poor’ ROI levels. The Ad Group analysis tells a similar story. We have some huge winners and a lot of sub-par performers.

Next Steps
The ROI distribution report provides perspective. It pulls you out of the trees of keywords and click-through-rates and gives you a sense of the forest where and how your money is being spent.

You may wind up running the report several times, adjusting the target values as you go, to find the goals which really represent what means ‘great’ and ‘poor’ to you. Armed with the final results, you can move back into ClickEquations itself and filter campaigns or ad groups by their ROI, displaying those falling into one grouping or another, and then diving in to analyze why they’re delivering their current performance levels and seeing if there is anything you can and should do to improve them.

In many cases, the immediate inclination when seeing how many campaigns or ad groups are under-performing is to consider shutting them down. If you need to immediately cut expenses or boost overall returns, this may be a good idea.

But more typically the metrics you get at the campaign and ad group level represent the average of too many bundled keywords, of too many different text ads, and too much diversity of match types and search queries.

You have to take the time to dive into the ad groups to see which keywords or text ads are really causing the poor performance. Over time, however, it is true that better ad group and campaign organization can help to make these numbers more actionable.

How It’s Built
One somewhat technical point about how the ROI distribution report is built using ClickEquations Analyst is worth noting. This report uses a very powerful feature of Analyst, which is the ability to ‘Aggregate’ data rather than just gather it.

This report is not built by pulling all the individual campaign or ad group performances into Excel and then doing a lot of calculations. Rather we simply tell Analyst that we want it to aggregate the data – counting how many campaigns hit a certain criteria and summing up their expenses and revenues.

The ability to have ClickEquations Analyst do data aggregation as pulls data into Excel make a wide range of analysis reports and dashboards very easy to define and create.