What formula would you use to calculate Amazon DSP CPM using the fields available in dsp_impressions?
- (SUM(total_cost)/100000)/(SUM(impressions))*1000 AS avg_cpm
- ((SUM(spend)/100000000)/SUM(impressions))*1000 AS avg_cpm
- ((SUM(total_cost)/100000)/SUM(impressions))/1000 AS avg_cpm
Explanation:
The correct answer is **(SUM(total_cost)/100000)/(SUM(impressions))*1000 AS avg_cpm** because the formula for calculating Cost Per Thousand Impressions (CPM) is based on dividing the total cost by the number of impressions, then multiplying by 1,000 to get the cost per thousand impressions. The field `total_cost` represents the total spend on the campaign, and impressions represents the number of impressions served. The formula correctly divides the total cost by the total number of impressions and then multiplies by 1,000 to scale the metric to CPM. The division by 100,000 in the formula accounts for the cost being expressed in cents or another unit that needs conversion. The other formulas either incorrectly order operations or use a different unit conversion, making them inaccurate for calculating CPM.