Weld logo
google-ads logo
Google ads

Campaign Report

Generates a campaign report by joining data from the Google Ads integration. It retrieves various metrics such as spend, impressions, clicks, CPC, CTR, CPM, and conversions for each campaign, along with account information. The template uses common aggregation functions and grouping to summarize the data at the desired level of granularity.
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
Example of output from model:
+------------+------------+----------------+----------------+--------------+---------------------------+-----------------------------+--------+---------+-------------+-------+-------+-------+-------------+
| 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.

Ready to start modeling your own google-ads data?

Get started building your data warehouse with google-ads and connect all your apps and databases.

google-ads logo
Weld logo
Sign up for free
© 2024 Weld. All rights reserved.