Here we will show you how to create a regression model with “daily cost” as the independent variable and “daily conversions” as the dependent variable. We’ll do it in 5 easy steps.
Note: This will only work with a Google Ads account that has conversion data.
Prepare the report and upload
Once in the report (screenshot below), select the ‘Columns’ button (red box), then remove all columns except ‘Cost’ and ‘Conversions’. Then select a date one year from today (blue box). Finally, download the report as an “excel .csv” file (green box).
Open the Excel file and select the columns containing Sri-lanka Phone Number only the “cost” and “conversions” data. In the example below, cells C3:D17. Then, from the menu bar, select “Insert Scatter Chart”. “
We now have a beautiful scatterplot representing “cost” and “conversions”. Generate a regression line by right-clicking on one of the data points and selecting “Add Trendline”.
Step 5 – Choose the best regression line using r-squared:
In the menu on the right, you can now select different regression options (red box). Check the “Show R-squared value on the chart” box (pink box). In a general sense, the higher the r-squared, the better the fit of the line. As you walk through different regression lines, you can see which has the highest r-squared value. You can also visually decide what suits you best. Next, add the regression formula for the fit you chose (green box). We will use this formula to make predictions.
Make extended predictions using the regression equation
The regression line we just created is extremely useful. Even from a visual perspective, you are now able to visualize what your expected daily conversions will be at any time of the daily cost.
Although this can be done visually, using the regression formula is more accurate and you can also extend the predictions off the graph. In the example below that I have plotted (with a larger count), the regression equation is given as y = 28.782 * ln(x) – 190.36.
In the equation, y represents conversions and x represents “cost”. To predict y for any given x , we replace x with a real number. Assume a cost of $5,000. We say y = 28.782 * ln( 5000 ) – 190.36. Using a calculator, we get 54 conversions per day.
Now, the real power comes here when we extend this calculation beyond the graph to where the expenses haven’t been before. The data points on the chart show that the highest spend ever per day was under $7,000. If we replace x with 10,000, (an expected spend of $10,000 per day), I can get an estimate using the formula, of 74.7 conversions per day.
Bonus: Finding Optimal Points or Diminishing Returns with CPA
Graphing “cost” and “conversions” together is extremely powerful in being able to predict conversions at different spends. But in reality, we are often more interested in lowering CPA or predicting conversions at a specific CPA. We can also plot CPA against conversions to better understand this.
From the CPA chart on the right, we identify a minimum point where the CPA is lowest on the cost dimension, this is the bottom of the “U” shape. This point also corresponds on the left graph (cost vs conversions) with the green line.
Using this methodology, we can now identify the lowest CPA potential, at what cost this occurs, and then also predict how many conversions we would receive at that point. The same can be done for any point on the CPA line.
It is essential to mention that the regression only uses historical data. All costs and conversion data are based on what has happened in the past. Therefore, if you expect your performance to improve and conversions to increase in the future, this will not be reflected in these models. To fix this, taking only more recent data, like six months back or three months back, might be a better option. Similarly, you can remove or include “days” during sales periods which may or may not be relevant, so as not to skew the data.