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