Campaign Report
1with
2 report as (
3 select
4 *
5 from
6 {{raw.pinterest_ads.campaign_report}}
7 -- to join another pinterest ads account
8 -- union all
9 -- select * from {{}}
10 )
11 , campaigns as (
12 select
13 *
14 from
15 {{raw.pinterest_ads.campaign}}
16 )
17 , advertisers as (
18 select
19 *
20 from
21 {{raw.pinterest_ads.ad_account}}
22 )
23select
24 cast(report.date as date) date_day
25 , advertisers.name account_name
26 , advertisers.id account_id
27 , campaigns.name campaign_name
28 , report.campaign_id
29 , campaigns.status campaign_status
30 , sum(report.spend_in_micro_dollar / 1000000) spend
31 , sum(
32 case
33 when report.impression_1_gross is null then report.impression_2
34 else report.impression_1_gross
35 end
36 ) as impressions
37 , sum(total_engagement) as clicks
38 , sum(report.cpc_in_micro_dollar / 1000000) cpc
39 , sum(
40 safe_divide(total_engagement, report.impression_1_gross)
41 ) as ctr
42 , sum(
43 safe_divide(
44 (report.spend_in_micro_dollar / 1000000)
45 , report.impression_1_gross
46 )
47 ) * 1000 as cpm
48 , sum(total_conversions) as total_conversions
49 -- Additional columns should be manually specified if needed
50from
51 report
52 left join campaigns on report.campaign_id = campaigns.id
53 left join advertisers on campaigns.ad_account_id = advertisers.id
54group by
55 1
56 , 2
57 , 3
58 , 4
59 , 5
60 , 6
61 -- Adjust these numbers according to the selected columns+------------+----------------+------------+-----------------+--------------+-----------------+-------+-------------+--------+-----+-----+-----+-----------------+
| date_day | account_name | account_id | campaign_name | campaign_id | campaign_status | spend | impressions | clicks | cpc | ctr | cpm | total_conversions |
+------------+----------------+------------+-----------------+--------------+-----------------+-------+-------------+--------+-----+-----+-----+-----------------+
| 2022-01-01 | Acme Company | 123456 | Winter Campaign | 987654 | Active | 100 | 1000 | 50 | 2 | 0.05| 100 | 10 |
| 2022-01-02 | Acme Company | 123456 | Winter Campaign | 987654 | Active | 150 | 2000 | 75 | 2 | 0.0375| 75 | 15 |
| 2022-01-03 | Acme Company | 123456 | Winter Campaign | 987654 | Active | 200 | 3000 | 100 | 2 | 0.0333| 66.67| 20 |
+------------+----------------+------------+-----------------+--------------+-----------------+-------+-------------+--------+-----+-----+-----+-----------------+Generate a report based on data from the Pinterest Ads integration. This SQL model combines data from three tables: "pinterest_ads.campaign_report", "pinterest_ads.campaign", and "pinterest_ads.ad_account". The resulting report provides insights into various metrics related to advertising campaigns on Pinterest. It includes information such as the date of the campaign, the name and ID of the advertising account, the name and status of the campaign, the spend, impressions, clicks, CPC (Cost Per Click), CTR (Click-Through Rate), CPM (Cost Per Thousand Impressions), and total conversions. By analyzing this report, advertisers can gain valuable insights into the performance of their Pinterest advertising campaigns. They can track the effectiveness of their campaigns, monitor spending, evaluate engagement metrics, and measure the overall success of their advertising efforts on Pinterest.
