Ad Group Report
1with
2 report as (
3 select
4 *
5 from
6 {{raw.pinterest_ads.ad_group_report}}
7 -- to join another pinterest ads account
8 -- union all
9 -- select * from {{}}
10 )
11 , advertisers as (
12 select
13 *
14 from
15 {{raw.pinterest_ads.ad_account}}
16 )
17 , campaigns as (
18 select
19 *
20 from
21 {{raw.pinterest_ads.campaign}}
22 )
23 , ad_groups as (
24 select
25 *
26 from
27 {{raw.pinterest_ads.ad_group}}
28 )
29select
30 cast(report.date as date) date_day
31 , advertisers.name account_name
32 , advertisers.id account_id
33 , campaigns.name campaign_name
34 , campaigns.status campaign_status
35 , campaigns.id campaign_id
36 , ad_groups.name ad_group_name
37 , report.ad_group_id
38 , ad_groups.created_time
39 , ad_groups.start_time
40 , ad_groups.end_time
41 , ad_groups.status ad_group_status
42 , sum(report.spend_in_micro_dollar / 1000000) spend
43 , sum(
44 case
45 when report.impression_1_gross is null then report.impression_2
46 else report.impression_1_gross
47 end
48 ) as impressions
49 , sum(total_engagement) as clicks
50 , sum(report.cpc_in_micro_dollar / 1000000) cpc
51 , sum(
52 safe_divide(total_engagement, report.impression_1_gross)
53 ) as ctr
54 , sum(
55 safe_divide(
56 (report.spend_in_micro_dollar / 1000000)
57 , report.impression_1_gross
58 )
59 ) * 1000 as cpm
60 , sum(total_conversions) as total_conversions
61 -- Additional columns should be manually specified if needed
62from
63 report
64 left join ad_groups on report.ad_group_id = ad_groups.id
65 left join campaigns on ad_groups.campaign_id = campaigns.id
66 left join advertisers on campaigns.ad_account_id = advertisers.id
67group by
68 1
69 , 2
70 , 3
71 , 4
72 , 5
73 , 6
74 , 7
75 , 8
76 , 9
77 , 10
78 , 11
79 , 12
80 -- Adjust these numbers according to the selected columns+------------+--------------+------------+----------------+-----------------+-------------+-----------------+--------------+---------------------+---------------------+---------------------+-----------------+---------+-------------+--------+-------------+------------------+
| date_day | account_name | account_id | campaign_name | campaign_status | campaign_id | ad_group_name | ad_group_id | created_time | start_time | end_time | ad_group_status | spend | impressions | clicks | cpc | ctr |
+------------+--------------+------------+----------------+-----------------+-------------+-----------------+--------------+---------------------+---------------------+---------------------+-----------------+---------+-------------+--------+-------------+------------------+
| 2022-01-01 | Pinterest | 1234567890 | Winter Campaign | Active | 987654321 | Ad Group 1 | 111111111 | 2022-01-01 08:00:00 | 2022-01-01 09:00:00 | 2022-01-01 10:00:00 | Active | 100.50 | 1000 | 50 | 2.01 | 0.05 |
| 2022-01-01 | Pinterest | 1234567890 | Winter Campaign | Active | 987654321 | Ad Group 2 | 222222222 | 2022-01-01 09:00:00 | 2022-01-01 10:00:00 | 2022-01-01 11:00:00 | Active | 75.25 | 800 | 40 | 1.88 | 0.05 |
| 2022-01-01 | Pinterest | 1234567890 | Winter Campaign | Active | 987654321 | Ad Group 3 | 333333333 | 2022-01-01 10:00:00 | 2022-01-01 11:00:00 | 2022-01-01 12:00:00 | Active | 120.75 | 1500 | 75 | 1.61 | 0.05 |
+------------+--------------+------------+----------------+-----------------+-------------+-----------------+--------------+---------------------+---------------------+---------------------+-----------------+---------+-------------+--------+-------------+------------------+Provide insights and analytics for Pinterest Ads campaigns. This SQL model combines data from multiple tables, including ad group reports, ad accounts, campaigns, and ad groups. By executing this SQL template, you can obtain a comprehensive report that includes various metrics such as date, account name and ID, campaign name and status, ad group name and status, spend, impressions, clicks, CPC (cost per click), CTR (click-through rate), CPM (cost per thousand impressions), and total conversions. The template utilizes common SQL operations such as joins and aggregations to combine the data from different tables and calculate the desired metrics. It allows you to analyze the performance of your ad groups, campaigns, and overall account on Pinterest Ads. This SQL template can be useful for advertisers and marketers who want to monitor and optimize their Pinterest Ads campaigns. It provides valuable insights into key performance indicators, allowing you to identify successful campaigns, track ad spend, measure engagement, and evaluate conversion rates. By customizing the template and selecting specific columns, you can tailor the report to your specific needs and include additional metrics or dimensions as required.
