Ad Campaign ROAS
Google marketing managers are analyzing the performance of various advertising accounts over the last month. They need your help to gather the relevant data.
Write a query to calculate the return on ad spend (ROAS) for each advertiser across all ad campaigns. Round your answer to 2 decimal places, and order your output by the .
Hint: ROAS = Ad Revenue / Ad Spend
The example output shows that 1 returned 90% of their ad spend, 2 returned 400% of their ad spend, and so on.
We are asked to find the ROAS for each advertiser, so we only need to operate on 3 fields: , , and . To sum up the amounts from different campaigns, we'l use and respectively, then group and order by the advertising account.
As shown above, ROAS (return on ad spend) is calculated by dividing the revenue by the ad spend. By following this logic, you should arrive at a query that looks like this:
The above produces an output with an inconsistent number of decimal places. The final step should be to round all the results to a uniform 2 decimal places.
However, PostgreSQL requires the input to the function to be a numeric data type, so we'll convert the resulting ROAS to a decimal type before rounding. We can accomplish this using the double-colon conversion, as shown below. You can read more about different numeric types in the PostgreSQL documentation here.
Congratulations, you have completed the task!