Campaign Report
1with
2 stats as (
3 select
4 *
5 from
6 {{raw.google_ads.campaign_stats}} -- to join another google ads account
7 -- union all
8 -- select * from {{}}
9 )
10 , campaigns as (
11 select
12 *
13 from
14 {{raw.google_ads.campaign}}
15 )
16 , account as (
17 select
18 *
19 from
20 {{raw.google_ads.account}}
21 )
22select
23 date_trunc(cast(date as date), day) date_day
24 , cast(account.id as string) account_id
25 , account.descriptive_name as account_name
26 , campaigns.name campaign_name
27 , cast(campaigns.id as string) campaign_id
28 , campaigns.advertising_channel_type
29 , campaigns.advertising_channel_sub_type
30 , campaigns.status
31 , sum(cost_micros * 0.000001) as spend
32 , sum(impressions) as impressions
33 , sum(clicks) as clicks
34 , sum(safe_divide((cost_micros * 0.000001), clicks)) as cpc
35 , sum(safe_divide(clicks, impressions)) as ctr
36 , sum(
37 safe_divide((cost_micros * 0.000001), impressions)
38 ) * 1000 as cpm
39 , sum(conversions) as conversions -- Additional columns should be manually specified if needed
40from
41 stats
42 left join campaigns on stats.campaign_id = campaigns.id
43 left join account on campaigns.account_id = account.id
44group by
45 1
46 , 2
47 , 3
48 , 4
49 , 5
50 , 6
51 , 7
52 , 8 -- Adjust these numbers according to the selected columns+------------+------------+----------------+----------------+--------------+---------------------------+-----------------------------+--------+---------+-------------+-------+-------+-------+-------------+
| date_day | account_id | account_name | campaign_name | campaign_id | advertising_channel_type | advertising_channel_sub_type | status | spend | impressions | clicks| cpc | ctr | cpm |
+------------+------------+----------------+----------------+--------------+---------------------------+-----------------------------+--------+---------+-------------+-------+-------+-------+-------------+
| 2022-01-01 | 123456789 | My Campaign | Campaign 1 | 987654321 | Search | Search Standard | ENABLED| 100.00 | 1000 | 50 | 2.00 | 0.05 | 100.00 |
| 2022-01-01 | 123456789 | My Campaign | Campaign 2 | 123456789 | Display | Image | ENABLED| 50.00 | 500 | 25 | 2.00 | 0.05 | 100.00 |
| 2022-01-02 | 123456789 | My Campaign | Campaign 1 | 987654321 | Search | Search Standard | ENABLED| 150.00 | 1500 | 75 | 2.00 | 0.05 | 100.00 |
| 2022-01-02 | 123456789 | My Campaign | Campaign 2 | 123456789 | Display | Image | ENABLED| 75.00 | 750 | 37 | 2.00 | 0.05 | 100.00 |
+------------+------------+----------------+----------------+--------------+---------------------------+-----------------------------+--------+---------+-------------+-------+-------+-------+-------------+Combines data from three tables: "campaign_stats," "campaign," and "account." The output of this SQL query provides insights into various metrics related to campaign performance. It includes information such as the date of the campaign, the account ID and name, the campaign name and ID, the advertising channel type and sub-type, the campaign status, the total spend, impressions, clicks, cost per click (CPC), click-through rate (CTR), cost per thousand impressions (CPM), and conversions. By analyzing this data, marketers can gain valuable insights into the effectiveness of their campaigns. They can identify which campaigns are performing well in terms of impressions, clicks, and conversions. Additionally, they can assess the cost-effectiveness of their campaigns by analyzing metrics such as CPC and CPM. This SQL template provides a comprehensive overview of campaign performance and enables data-driven decision-making for optimizing advertising strategies.
