How can I calculate ROI from Google AdWords Campaigns?

This query is useful if you want to find how many users installed your app following a particular campaign. This ROI calculation refers to the out-of-the-box Google AdWords CoolaData Integration. We first calculate each user’s LTV using a Materialized View (scheduled aggregation table) and then we cross reference this value with the cost of the campaign that acquired the user.

select day, install_campaign, campaign as campaign_name,  advertising_channel,  users, sum(clicks) as clicks, sum(cost_nis) as cost , sum(ltv) as campaign_income, sum(number_of_purchases) as total_campaign_purchases
from
(select day, install_campaign,install_source, ltv, number_of_purchases, clicks,cost_nis, campaign, users, advertising_channel from
(select install_date, install_source,install_campaign, sum(number_of_purchases) as number_of_purchases, count(distinct internal_user_id) as users, sum(ltv) as ltv
 From   USER_AGGR
group by install_date, install_source,install_campaign) users
join
(select string(integer(campaign_id)) as campaign_id, campaign, advertising_channel, company_name, impressions, integer(clicks) clicks,  cost/1000000 as cost_nis,
(cost/1000000)/clicks as avg_cpc,  date(day) as day
from adwords_campaigns_<integration id>) adwords
on users.install_campaign=adwords.campaign_id and users.install_date=adwords.day)
group by day, install_campaign, campaign_name,  advertising_channel, users
order by day
Segentation
kpi
Behavioral