Pinterest ads
Daily Ad Group Performance Report
This SQL template fetches a daily breakdown of Pinterest advertising performance, organized by ad groups. It combines data from the ad group report, ad account, ad group, and campaign tables to present metrics such as clickthroughs, impressions, clicks, conversions, and spend for each ad group.
1with
2 ad_report as (
3 select
4 ar.ad_group_id
5 , ar.ad_account_id as advertiser_id
6 , ac.name campaign_name
7 , ar.total_clickthrough as clickthrough
8 , case
9 when ar.impression_1_gross is null then ar.impression_2
10 else ar.impression_1_gross
11 end impressions
12 , total_engagement as clicks
13 , ar.total_conversions conversions
14 , ar.cpc_in_micro_dollar / 1000000 cpc
15 , cast(date_trunc(ar.date, day) as date) day
16 , ar.spend_in_micro_dollar / 1000000 spend
17 , ah.currency
18 from
19 {{raw.pinterest_ads_weld.ad_group_report}} ar
20 left join {{raw.pinterest_ads_weld.ad_account}} ah on ah.id = ar.ad_account_id
21 left join (
22 select distinct
23 id
24 , campaign_id
25 from
26 {{raw.pinterest_ads_weld.ad_group}}
27 ) ab on ar.ad_group_id = ab.id
28 left join (
29 select distinct
30 id
31 , name
32 from
33 {{raw.pinterest_ads_weld.campaign}}
34 ) ac on ab.campaign_id = ac.id
35 )
36select
37 *
38from
39 ad_report
40order by
41 day descExample of output from model:
+------------+----------------+---------------+--------------+-------------+----------+----------+-------------+--------+---------+---------+
| day | campaign_name | ad_group_id | advertiser_id| clickthrough|impressions|clicks|conversions| cpc | spend |currency|
+------------+----------------+---------------+--------------+-------------+----------+----------+-------------+--------+---------+---------+
| 2023-05-07 | Spring Sales | group_203 | adv_1001 | 0.1 | 11000 | 950 | 460 | 0.16 | 145.0 | USD |
| 2023-05-06 | Spring Sales | group_203 | adv_1001 | 0.095 | 10500 | 940 | 455 | 0.155 | 140.0 | USD |
| 2023-05-05 | Spring Sales | group_203 | adv_1001 | 0.09 | 10300 | 930 | 452 | 0.152 | 138.0 | USD |
| 2023-05-04 | Spring Sales | group_203 | adv_1001 | 0.085 | 10050 | 910 | 450 | 0.15 | 135.5 | USD |
| 2023-05-03 | Spring Sales | group_203 | adv_1001 | 0.08 | 9800 | 890 | 445 | 0.148 | 132.0 | USD |
| 2023-05-02 | Spring Sales | group_203 | adv_1001 | 0.075 | 9600 | 875 | 440 | 0.145 | 130.0 | USD |
| 2023-05-01 | Spring Sales | group_203 | adv_1001 | 0.07 | 9400 | 860 | 435 | 0.142 | 127.5 | USD |
+------------+----------------+---------------+--------------+-------------+----------+----------+-------------+--------+---------+---------+With this SQL model, users gain a day-to-day insight into their advertising operations on Pinterest at the ad group level. By merging data sources like the ad group report, ad account specifics, and campaign details, advertisers receive a detailed view of key performance indicators like clickthrough rates, total impressions, engagement in the form of clicks, total conversions, cost per click, and overall expenditure. Such granular analysis is pivotal for advertisers aiming to adjust and refine their advertising strategies on Pinterest.
