Forum Moderators: martinibuster
"Come'on, of course", I hear you say.
But let me explain: when you look at weekly data and just evaluate the relative changes (in %) of the current week to the previous week for both clicks and revenue, and you plot these on a scatter diagram in Excel (change clicks on X, change revenue on Y), I get a corridor of data points. The higher the change in clicks, the higher the change in revenue, and vice versa. While this is not a straight line, you can calculate that straight line by inserting a linear trendline into the picture.
And then, suddenly, it becomes all much clearer.
Conveniently, Excel displays the formula for that trendline, which in my case is
y=0.85x
In other words: "when my clicks increase by 100%, then my revenue increases by 85%", but also: "when my clicks decline by 10%, then my revenue declines by 8.5%" (Obviously, this does not work for a 100% decline in clicks.)
Which is quite surprising to me, because one would think that -in theory- a click increase by 100% would be met by a revenue increase of 100%.
It sure is interesting to analyze.
When you create these reports, how much data are you using, 1 year or all years? What's the best way to do this from signing in to Excel?
To get to this, I suggest to have two columns with the weekly absolutes from Google (clicks, revenue), and two columns with a formula calculating the relative changes in relation to the previous week (i.e. CURRENT/LAST-1) and format both values as %. All this for all weeks available.
Your table should look like this now:
(CLICKS WEEK1) (REVENUE WEEK1) (empty) (empty)
(CLICKS WEEK2) (REVENUE WEEK2) (CLICKS CHANGE %) (REVENUE CHANGE %)
(CLICKS WEEK3) (REVENUE WEEK3) (CLICKS CHANGE %) (REVENUE CHANGE %)
(CLICKS WEEK4) (REVENUE WEEK4) (CLICKS CHANGE %) (REVENUE CHANGE %)
...and so on and so on...
Now mark all cells containing percentages and click on the "graph" icon (diagram assistant). Select "Point (XY)", and make sure that the clicks are indeed on the X axis. Click OK. The diagram will appear.
As a final step, select all the points (in the diagram) by clicking on a data point, then right click and select "add trendline". Select the "linear" type. To see the formula, click the "Options" tab, and select "display formula in diagram". Then you are done.
I'd be very interested in your findings.
With data since 1.1.2006 I get:
0.94x + 0.06
My site seems to be less vulnerable to click changes.
Also intersting is +0.06 which ensures constant earnings during constant click periods.
Do you get 0.85x + 0.15?
My site seems to be less vulnerable to click changes
Yep. That seems to be the case. Lucky you! I'll do the calculation from 1/1/2006 later today and see what happens.
Do you get 0.85x + 0.15?
In order to not reveal critical information, I rounded off the factor (it is not exactly 0.85x, and I too have a tiny adder that can be neglected). But it is fair to say it's y=0.85x
Also intersting is +0.06 which ensures constant earnings during constant click periods.
I'm not really sure how to interpret the offset (+0.06). I think that it's interesting that it almost directly goes through the zero point, which means that there indeed seems to be a strong relationship between clicks and revenue. "No change in clicks" = "no change in revenue".
The actual offset could be a statistical error that can be neglected as long as it is close to the center. I'd be sceptical if the offset would be too big, because this would mean "No change in clicks" = "More revenue". This may be so in individual weeks, but it is very unlikely to be true when looking at longer periods.
The "angle" is the interesting part IMO. It seems to be pointing to the "volatility" of the niche and/or site. 100% more clicks = 85% more revenue, this could mean that the higher paying ads are gone and are replaced by lower-paying (on average 15% lower paying) ads.
In your case, the volatility is not as big, as "the next ads" seem to be on average just 6% lower paying.
The 100% more clicks = 100% more revenue assumption can only be true in a market with unlimited supply of identically priced ads. But this is not realistic. (From an individual publishers pov, it certainly could be true, given the small share of ads showing on a single site [compared to the overall pool at Google].)
So is this number (~0.85) a global 'click deviation corrector' or an individual factor for each individual site and some kind of 'quality indicator'?
BTW I've also omitted some digits because
a) it's just a trendline on statistical data and more digits would suggest an accuracy not given and
b) it looks nicer.
I got around 0.96x +0.09 (probably helped by the site I used not really changing at all during the reporting period).
Your explanation of the angle makes sense, maybe it's the offset that is some kind of 'site quality indicator' because that number indicates your long term performance. As said above, an offset < (1-angle) would imply that the EPC is constantly falling (and of course constantly rising if greater than (1-angle)).
since 1.1.2006 I get: 0.94x + 0.06
Wow. Amazing.
When looking at the data since 1/1/2006 I get: 0.93x + 0.01
Anyone else following our little experiment?
For major deviations from a linear relationship,you'd probably detact it via eyeballing the chart, but I'd do a test to determine linearity before putting any confidence in the result.
If it turns out the relationships is, in fact curvilinear, it would say a huge amount about how google handles payouts, so this isn't just an abstract discussion. Thanks for starting the thread.
