ClickEquations Blog

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.

Last week in Occam’s Razor, Avinash Kaushik discussed our ‘What’s Changed’ reports, which make it easy to see which campaigns, ad groups, or keywords are doing better or worse than they were previously.

These reports showcase a core feature of ClickEquations, the ability to compare performance between any two periods and to very easily see the difference between performance in those two periods. It’s a feature that was actually inspired by an earlier post on Occam’s Razor, and was the direct result of a conversation we had with Avinash early last summer.

(Click To Zoom)

In his original post, Avinash compellingly makes the case that top 10 lists are only of limited use. Or more accurately, they’re extremely useful but only for a limited time. Once you understand the top 10 of anything, it doesn’t tend to change so looking at the top 10 keywords or top 10 ad groups day-after-day really isn’t going to help drive constant campaign improvement.

But if you look at the top 10 keywords based on rate of change in volume, or based on delta in cost-per-click, or based on increasing ROI, then you’ve got some interesting and in almost every case actionable date. (Of course, 10 isn’t a magic number, it could be the top 25 or top X. If Letterman ran a top 11 list every night, would we all say Top 11?)

Prior Period & The Delta
Taking this insight to heart, we made it simple to pull two new pieces of data for any metric available within ClickEquations: the prior period version of that metric and the size of the delta between the current period and the prior period. So if you’re running a report for ‘this month’ and ask for the number of conversions for a keyword, for example, you also get back the number of conversions for ‘last month’ and the ‘delta’ between those two values.

We use this in many default reports to conditionally format numbers and present the % change represented by the new value. So in the dashboard below we see that revenue is down 3% in Google. (Click Image to Zoom)

The full What’s Changed Reports that Avinash mentioned display results for many different metrics – Revenues, Profit (ROI), Average CPC are the defaults – sorted by the amount of change in the current period vs the prior period. Each report shows the top 10 for each metric by amount of change in terms of both increase and decrease.

Each report also includes a handy bar chart showing the growth in revenue, in this case by campaign.

The default reports provide all of the above for Google and Yahoo (on separate pages), and cover both Campaigns, Ad Groups, and Keywords. By default they’re month over month reports, but using our Quick Change Palette you run them for any time period with a single click.

Making more significant customizations is pretty easy. You can change the metrics to shift Profit (ROI) to ROAS, for example, or any metric to any other. You can even customize the dates of the ‘prior period’.

All ClickEquations Delta reports automatically calculate values for the mirror-image prior period of any specified date range. So if you request a report for yesterday, the numbers will compare yesterday to the day before yesterday. If you choose this week, the report will compare this week to last week. But you can elect to specify the prior period as any arbitrary period, so you could compare this month to last July, or Valentines Day weekend to Presidents Day weekend, or whatever you’d like.

What’s Changed Reports can easily be created for other aspects of your PPC campaign too. Want a report to show the top 25 products selling faster this month than last month? How about one showing the geographies where sales are dropping the fastest? Each of these and many others are rather quick customizations in ClickEquations Analyst – after which they can be refreshed with one button push anytime.

A Sneak Peak
We’ve come to think very highly of these What’s Changed Reports as action drivers for PPC campaigns. So much so that in the next release of ClickEquations, we’ve moved the core What’s Changed reports onto the main dashboard. In a new tabbed-reports interface, you’ll be able to see the campaigns or keywords which are ‘changing’ anytime, and quickly dive into more details or to take corrective action.

Watch for more news on our upcoming release in the next few weeks.

Recently our friend and advisor Avinash Kaushik wrote a blog post in which he used ClickEquations Analyst to showcase a number of advanced analyses for paid search. Each of these used a report or dashboard which is provided to all ClickEquations customers.

In this and the next few posts we’ll dig a little deeper into these reports, sharing some background about how they were created, how they can be customized and used, and how they can help you to improve your paid search campaigns using ClickEquations.

Keywords By Engine
The first report Avinash covered is a new one, set to be released in a mid-July update. It’s called the Keywords by Engine report.

This report uses a single CQ Analyst query to pull all keywords with at least 1 click over the past month, along with the number of conversions for that keyword.

This information is then pulled into a an Excel pivot table, which allows us to see how the keywords compare across engines based on either clicks or conversions.

To sort the data, you simply point to a cell in the Google, Yahoo, or MSN columns and right click. Then choose Sort > Sort Largest To Smallest. The keywords are then sorted based on the number clicks in that engine, and the other engine columns show how many clicks each keyword got in those respective engines.

As Avinash pointed out, this provides a view of pure opportunity.

Running this report for client after client has shown vast differences in how keywords perform between the search engines. While there are many differences between the engines, and some valid reasons why one keyword or another would perform differently in these different environments, it seems clear that all of your top 10 or 20 performing keywords in Google shouldn’t be non-performers in Yahoo and MSN.

Yet that is frequently exactly what is happening.

Very often it’s simple oversights that will be highlighted. Such as versions of brand terms, mis-spellings, or even domain name addresses that turn out to have been left out of one engine or another.

This report offers a great way of prioritizing the additions of new keywords to Yahoo and MSN accounts which you wish to expand. The first step would be to verify that the keywords producing great Google results (in terms of either clicks or conversions) aren’t already in those engines.

If they are there then you’ll want to try and understand why their performance is so (relatively) low. Differences in matching algorithms and search queries could explain it, so review the search query report for the ad group in the ClickEquations web application. Perhaps there are other keywords in the same ad group which are capturing those clicks or conversions in that engine.

Or it could simply be a matter of the keyword needing better ad copy, or perhaps it’s position is poor and a bid adjustment is needed.

If the keywords are missing, taking the time to ad them in each engine could be an effort with a great return.

Variations
The default query in this report gets keyword performance data for 30 days. Using the Quick Change palette you could pull data for a longer time period – perhaps 3 or even 6 months – to see if the result patterns change.

And while we’ve pre-built these keyword comparisons for both the clicks and conversion metrics, you could easily modify or extend the report to compare impressions, CTR, or even ROI. That’s the great thing about ClickEquations analyst – you have the full authoring tools to modify any of the predefined reports.

Last week our friend and advisor Avinash Kaushik put up another of his legendary blog posts, and we’re pleased and honored to say it features paid search analysis done in ClickEquations Analyst.

In the post Avinash looks at five different advanced PPC analysis ideas, and provides his spin on why they’re important and how you can benefit from them.

Go read his post. This week on blog posts here we’ll provide some more in-depth review of the reports and dashboards he mentioned and some additional information on how ClickEquations clients can use and extend them.

All the recent talk about Quality Score got me to finally figure out how to build a ClickEquations Analyst template that I’ve been wanting to for some time now.

It shows you all the keywords whose Quality Score has changed from the prior period. It could be yesterday vs the day before, this week vs last week, or last month vs the month before.

So you can try to figure out why. Or make a new bidding decision. Or just marvel at the mysteries of Google.

Like all ClickEquations Analyst reports, to use it you simply open Microsoft Excel, open the report, and open the ‘Quick-Change’ palette to select the date range you like to report on.

Click Apply and hold your breath, in this case, for about 90 seconds.

.
The image above shows a zoom-in on the core data the report provides. There is one tab in Excel for the Quality Score gainers and another for the losers.

Click the thumbnail image to the left to see an enlarged full page of the ‘Gainers’ report.

This new report will be released to ClickEquations clients as an Analyst template set update in the very near future.

If you can’t wait that long, email support and we’ll get you a copy.

You don’t use ClickEquations for PPC management, reporting, and bidding? Click Here.

While Google’s Adwords Quality Score gets a lot of attention, an important and somewhat related keyword metric – the First Page Bid Estimate – hasn’t had quite as much coverage.

I’m going to tackle this one in two parts, today talking about how to monitor your FPBE’s (First Page Bid Estimates) and then in a future post digging deeper into the philosophy, politics, and implications of this metric. (You can tell that’s going to be the fun one.)

In the Adwords interface, the First Page Bid Estimate can be found on the Keyword Analysis page, which requires you first to mouse-over the magnifying glass icon next to the keyword, and then choose the ‘Ad Showing: Details and Recommendations’ link.

First Page Bid Estimate in the Keyword Analysis Window

Of course, you probably don’t care about the FPBE unless your current MaxCPC is below it. These keywords are more clearly identified, with a simple message shown below the Status indicator for each keyword.

First Page Bid Warning in Adwords

You can also add ‘Est. First Page Bid’ to a Keyword Performance Report in the Adwords Report interface and the metric has also been added to the latest version of the Adwords Editor.

In the Adwords Editor, you can even select the entire account, sort by the FPBE, and see your current Quality Score and MaxCPC in adjacent columns. This is the most useful presentation as those are the three metrics you need to both understand and use to decide on corrective action.

First Page Bid Estimate in Adwords Editor

First Page Bid Estimate Report – In ClickEquations
In ClickEquations we wanted to make use of First Page Bid Estimates even easier and more convenient.

So like the Adwords Editor, we provide the FPBE, Quality Score, and your MaxCPC next to every keyword so you can always keep an eye on these important metrics. And you can easily sort by any column to spot issues or trends.

ClickEquations Keyword Report with First Page Bid Estimate

But using ClickEquations Analyst we’ve taken it one step farther, providing the First Page Bid Estimate Report.

This Excel Template makes it easy to see your current keywords, sorted by FPBE in descending order, along with the corresponding QS, MaxCPC, Ave Position, Campaign & AdGroup names – plus a red-alert warning when you need to raise your bid to hit the FPBE.

First Page Bid Estimate Report in ClickEquations Analyst

Just by glancing down this column you can see which keywords require attention, and how much that attention is going to cost you. The data in the report can be updated with a single-click.

Keywords in your account which are currently bid below their First Page Bid Estimate are either not showing (if they have a low Quality Score) or showing at a highly diminished rate.

In effect, you’re currently not actively advertising on those keywords, which is why we think this kind of quick summary report is important.

In Part II of this post we’ll dig deeper into the why’s and what-to-do’s of the First Page Bid Estimate.

Months ago we were sharing our development progress with our advisor Avinash Kaushik and asking him for input in terms of the kinds of search analytics capabilities we should include in ClickEquations.

Avinash, like another charismatic leader, shared his passion for change.

“It’s fine to see the top 50 keywords by clicks, or the top 10 ad groups by revenue” he said, but what is much more powerful is to see what’s changed – the keywords making money today that were not making money yesterday. Or the campaigns that performed well last month, and are not performing well today.”

There is a great post on the subject of this approach to reporting on his well respected blog, written about the time this conversation took place.

In our talk he went on to explain that “the top ten of anything rarely changes. With the Delta Reports you can truly see “what’s changed” and what changes is what’s actionable. If keywords were suddenly producing clicks or conversions, a marketer can and should go figure out why.”

This leads to another word you hear often when talking to Avinash – “insight”.

“When they go and figure out why a keyword or campaign is suddenly performing or failing to perform, they’ll likely learn something about their business or market. Something they can capitalize on.”

ClickEquations Analyst Delta Reports
The conversation inspired us to add a major new capability to our ClickEquations Analyst Excel Plug-in.

The Delta Report tables make it possible to request information about any PPC metric, and get back a sorted list based on the difference between two time periods.

Or as Avinash would say, we show you what’s changed:

• So you can request the top 100 keywords making more money this week than last.
• Or the 5 campaigns whose click-through-rates have dropped the most dramatically between January and last September.
• Or the worst 5 ad groups in terms of declining impressions.
• Or just about any period-to-period comparison pre-sorted by the amount of change.

See These Reports In Action
The Delta Report tables in ClickEquations Analyst allow you to request any data for any time-frame, and build whatever report or dashboard you need. They automatically compare the selected timeframe to the prior period (this week to last week, last month to the month before it) or you can specify any two arbitrary periods to compare.

This is very powerful.

But we include pre-built reports that are ready-to-use and take advantage of these features too.

One is our Growth/Decline Report which shows you twelve different views of your campaign and keywords based on the amount of growth and decline against a variety of metrics including clicks, revenue, and profit.

This report is the subject of the next video in our ‘ClickEquations in 90-Seconds’ series – which is now live on YouTube and below.

Video: ClickEquations In 90 Seconds – Growth/Decline Report

.

PS: Thanks to Avinash for the idea and inspiration.

The post a few days ago about Impression Share included a few screen shots from one of the reports included with ClickEquations, which provides a graphic view of Impression Share.

This new video from our ‘ClickEquations in 90-Seconds’ series provides a full tour of that report and the benefits it provides.

After yesterday’s Quality Score analysis template and post, I got to thinking about match type.

So in 15 minutes while sitting in a meeting I built this ClickEquations Analyst template which analyzes a full paid search campaign in terms of how much cost and revenue is occurring at each of the Google Match Types:

Click Image To Enlarge

It’s interesting and may need some more tweaks and consideration to make it truly useful, but I do think these taken together help get a 360-degree view of campaign performance and structure.

Template available to ClickEquations clients and trial users. Gee ClickEquations Analyst is cool.