辅导 MKT4636 Customer Analytics 2023/24 Semester B辅导 Java编程

2023/24 Semester B

MKT4636 Customer Analytics

Data Report - Case of Google Adwords

PART 1 - Keyword Choices Based on Return-on-investment

Q1. Estimate the monthly advertising budget based on previous keywords.

There are 100 keywords, and the total volume of their monthly click is 856.01, with an average of 8.56 clicks and a standard deviation of 26.20 per keyword. Meanwhile, the total daily cost is $2134.17, with an average cost of $21.34 and a standard deviation of $74.62 per keyword (Appendix 1 and 2).

Jim can consider the average cost and balance it against the expected returns to determine whether the cost aligns with the budget. Given that the daily budget (cost) is $2,134.17, the monthly budget is $2,134.17* 30 = $64,025.10.

Q2. Choose keywords based on their return on investment (ROI).

On average, for every 1,000 impressions, the conversion rate is = 1/5 = 0.2.

The probability of sale when they visit the dealership = 1/20 = 0.05

So, the estimated return of 1,000 impressions is 0.2 * 0.05 *2500 = $25.

As a result, we can gain 25 dollars is the dollar profit per 1000 impressions

After adjusting the ROI of keywords with $0 cost to $1000, when the remaining keywords are calculated as (return of 1000 impressions / cost of 1000 impressions).

Among all keywords, 87.43% of the keywords bring in ROI within $4.30 to $1503.58, and the standard deviation of keywords’ ROI is 1503.63, which may be caused by the existence of the 24 outliers of keywords bring in ROI within $1503.58 to $9000.

After choosing the keywords that align with the daily advertising budget of $2,134.17, 137 keywords are selected by the accumulative daily cost, which costs $2116.13 in total daily cost and $2116.13*30 = $63483.9 in total monthly cost (Appendix 4).

In the 137 chosen keywords, there are 46% differences in Jim’s and Mark’s keywords choice.

After calculation, Mark’s monthly return is $3913.96, and Jim’s monthly return is $4843.14.

Change in monthly return after choosing new keywords set

= $(4843.14 - 3913.96) / $3913.96 = 23.74%

PART 2 - Improving Returns through Bid Adjustment

Q3. To estimate how change in bid affects the ad position in each keyword.

3a) The scatterplot & linear regression of log"average positions" & log"suggested bid"

elasticity = percentage change in average position / percentage change in suggested bid

Based on the analysis result, the elasticity = -0.303248066, indicates an inverse relationship between the suggested bid and the average position. Specifically, it suggests that a 1% increase in the suggested bid would result in a 0.3032% decrease in the average position, and vice versa.

Scatterplot:

Based on the scatterplot (Appendix 5), we can observe a negative correlation between the log of "average positions" and the log of "suggested bid". This indicates that as the suggested bid increases, the average positions tend to decrease. In other words, a higher suggested bid is associated with a more front average position.

Regression Table:

Based on the regression table (Appendix 6), The multiple correlation coefficient (Multiple R) is 0.676, indicating a moderately strong positive relationship between the log of "average positions" and the log of "suggested bid".

The coefficient of determination (R Square) is 0.458, which means that approximately 45.8% of the variation in the log of "average positions" can be explained by the log of "suggested bid".

ANOVA table:

This table provides information about the overall significance of the regression model. The regression model has one degree of freedom (df) and explains a significant amount of variation, as indicated by the F-statistic of 159.48 and a very small p-value (6.60E-27). This suggests that the regression model is statistically significant, it implies that the effect of the log of suggested bids on the log of average positions is unlikely to be due to random error. Instead, the suggested bids may indeed have an impact on ad positions, and we can use the regression model relatively reliably to predict and explain changes in ad positions.

Coefficients table:

The intercept coefficient is 1.336, indicating the estimated value of the log of "average positions" when the log of "suggested bid" is zero.

The coefficient for the log of "suggested bid" is -0.303, suggesting that a one-unit increase in the log of "suggested bid" is associated with a decrease of approximately 0.303 units in the log of "average positions".

Marketing Insights:

1. There is a negative correlation between the suggested bid and the average ad position. According to the regression analysis, as the suggested bid increases, the average ad position tends to decrease. This implies that by increasing the suggested bid, the marketing team can achieve higher ad positions, leading to increased exposure and visibility for the ad.

2. Although the suggested bid has an impact on the average ad position, its explanatory power is limited. Based on the R-squared value, only about 45.7% of the variation in the average ad position can be explained by the suggested bid. This suggests that there may be other factors influencing the ad position, such as ad quality, competitor bids, and so on. Therefore, the marketing team needs to consider other factors and adopt a comprehensive marketing strategy to improve the effectiveness of the ad.

3. Optimizing the suggested bid can improve ad performance. Based on the regression coefficient, for every unit increase in the suggested bid, the average ad position is expected to decrease by approximately 0.303 units.

3b) The new estimated elasticity of position to bid

In the second regression analysis where the log of "suggested bid" and other covariates ("average monthly searches," "competition," "length," and "promo") are used as independent variables, the estimated elasticity of position to bid is -0.2869, suggesting that a 1% increase in the suggested bid would result in a -0.2869% decrease in the average position

Compared to the results of the first regression analysis, the estimated elasticity value in the second regression decreased. This suggests that the sensitivity of position to bid weakened when considering the effects of other covariates.

For the elasticity of the other covariates (Appendix 7):

1. The coefficient estimate for "average monthly searches" is very close to zero, and the p-value is high (greater than 0.05). This indicates that average monthly searches do not have a significant impact on ad position in this sample.

2. The coefficient estimate for "competition" is close to zero, and the p-value is also high (greater than 0.05). This implies that competition does not have a significant effect on ad position in this sample.

3. The coefficient estimate for "length" is 0.0791, and the p-value is small (less than 0.05), indicating that length has a significant positive effect on ad position. In other words, longer ads may be associated with higher ad positions.

4. The coefficient estimate for "promo" is 0.0019, and the p-value is high (greater than 0.05). This suggests that promotions do not have a significant impact on ad position in this sample.

3c) The corresponding change for the average position

The estimated elasticity of the average position with respect to the bid is -0.2869.

elasticity = percentage change in average position / percentage change in suggested bid

a) Jim increases the bid by 50% of the level suggested by Google:

Percentage increase in the bid = 50%

Corresponding change in the average position = 50% * (-0.2869) = -14.345%

b) Jim increases the bid by 25% of the level suggested by Google:

Percentage increase in the bid = 25%

Corresponding change in the average position = 25% * (-0.2869) = -7.1725%

* negative sign indicates a decrease in the average position

Q4. Build a model to predict the change in impressions and CTR

4a) The elasticity of daily impressions to average position and the regression model

Elasticity = percentage change in daily impression/ percentage change in average position

According to the regression results (Appendix 8), the estimated elasticity (i.e. coefficient) is -0.169726073, indicating an inverse relationship between the average position and daily impression. Specifically, it suggests that a 1% increase in the average position would result in a 0.1697% decrease in the daily impression, and vice versa. Besides, the R square is 0.000828856, indicating that only a very small proportion of the variation in daily impressions can be explained by the average position alone. In other words, the average position has a very weak explanatory power in predicting changes in daily impressions. Therefore, other factors can be included in the regression model to have a larger influence on the daily impression. In addition, the P-value of 0.692581793(greater than 0.05) indicates that the overall regression model is not statistically significant.

4b) The scatterplot of log "CTR" and log "average position" and their relationship

According to the scatterplot of log "CTR" and log "average position"(Appendix 9), it can be observed a negative correlation between log "CTR" and log "average position". This indicates that as the average position increases, the CTR tends to decrease. In other words, a lower ranked ad in search results receives fewer clicks.

4c) The estimated elasticity of CTR to position

According to the regression results(Appendix 10), when the log of "average position" and other covariates ("average monthly searches," "competition," "length," and "promo") are used as independent variables, the estimated elasticity of CTR to position is -0.0024892, suggesting that a 1% increase in the average position would result in a 0.0025% decrease in the CTR. This result is consistent with 4(b) and the negative sign is consistent with our expected intuitiveness. Usually, a lower average position corresponds to a higher ranking, where the ad would gain more visibility and attract a higher CTR. However, the p-value is 0.87918545(greater than 0.05), indicating the estimated elasticity is not statistically significant. Therefore, we cannot determine the sign of this elasticity of CTR to position makes sense although the negative sign aligns with expectation.

4d) The Prediction of the corresponding change in impressions and CTR

A. Corresponding change in impressions

The elasticity of daily impressions to average position = -0.1697

a. Percentage increase in the bid = 50%

Corresponding change in the average position is -14.345%

→ Change in impressions = -14.345%*(-0.1697) = 2.4346%

Therefore, a 50% increase in the bid will result in a 2.4346% increase in impressions.

b. Percentage increase in the bid = 25%

Corresponding change in the average position is -7.1725%

→Change in impressions = -7.1725%*(-0.1697) = 1.2163%

Therefore, a 25% increase in the bid will result in a 1.2163% increase in impressions.

B. Corresponding change in CTR