Ad Report
1with
2 report as (
3 select
4 *
5 from
6 {{raw.pinterest_ads.ad_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 as (
24 select
25 *
26 from
27 {{raw.pinterest_ads.ad}}
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.id campaign_id
35 , ad.name ad_name
36 , report.ad_id
37 , ad.status as ad_status
38 , ad.type as ad_type
39 , ad.created_time
40 , sum(report.spend_in_micro_dollar / 1000000) spend
41 , sum(
42 case
43 when report.impression_1_gross is null then report.impression_2
44 else report.impression_1_gross
45 end
46 ) as impressions
47 , sum(total_engagement) as clicks
48 , sum(report.cpc_in_micro_dollar / 1000000) cpc
49 , sum(
50 safe_divide(total_engagement, report.impression_1_gross)
51 ) as ctr
52 , sum(
53 safe_divide(
54 (report.spend_in_micro_dollar / 1000000)
55 , report.impression_1_gross
56 )
57 ) * 1000 as cpm
58 , sum(total_conversions) as total_conversions
59 -- Additional columns should be manually specified if needed
60from
61 report
62 left join ad on report.ad_group_id = ad.id
63 left join campaigns on ad.campaign_id = campaigns.id
64 left join advertisers on campaigns.ad_account_id = advertisers.id
65group by
66 1
67 , 2
68 , 3
69 , 4
70 , 5
71 , 6
72 , 7
73 , 8
74 , 9
75 , 10
76 -- Adjust these numbers according to the selected columns+------------+--------------+------------+----------------+--------------+--------------+---------+-----------+----------+---------------------+-------+-------------+------------------+------------------+------------------+
| date_day | account_name | account_id | campaign_name | campaign_id | ad_name | ad_id | ad_status | ad_type | created_time | spend | impressions | clicks | cpc | ctr |
+------------+--------------+------------+----------------+--------------+--------------+---------+-----------+----------+---------------------+-------+-------------+------------------+------------------+------------------+
| 2022-01-01 | Pinterest | 1234567890 | Summer Campaign| 9876543210 | Ad 1 | 1111111 | Active | Image | 2022-01-01 08:00:00 | 50.00 | 1000 | 50 | 1.00 | 0.05 |
| 2022-01-01 | Pinterest | 1234567890 | Summer Campaign| 9876543210 | Ad 2 | 2222222 | Active | Video | 2022-01-01 09:00:00 | 75.00 | 1500 | 75 | 1.00 | 0.05 |
| 2022-01-02 | Pinterest | 1234567890 | Winter Campaign| 9876543211 | Ad 3 | 3333333 | Paused | Carousel | 2022-01-02 10:00:00 | 100.00| 2000 | 100 | 1.00 | 0.05 |
+------------+--------------+------------+----------------+--------------+--------------+---------+-----------+----------+---------------------+-------+-------------+------------------+------------------+------------------+Generate a comprehensive report on advertising performance using data from the Pinterest Ads integration. This SQL model combines data from multiple tables, including ad reports, ad accounts, campaigns, and ads, to provide insights into various metrics related to ad performance. The SQL code begins by creating a temporary table called "report" that includes all the columns from the "pinterest_ads.ad_report" table. This table can be expanded by joining additional tables if needed. Next, the code creates three more temporary tables: "advertisers," "campaigns," and "ad," which contain data from the respective tables in the Pinterest Ads integration. Finally, the code selects specific columns from the "report" table and joins it with the other temporary tables to retrieve relevant information such as the date, account name and ID, campaign name and ID, ad name, ad status and type, created time, spend, impressions, clicks, CPC, CTR, CPM, and total conversions. Additional columns can be manually specified if required. The SQL model is useful for analyzing and monitoring the performance of Pinterest ad campaigns. It provides valuable insights into key metrics such as spend, impressions, clicks, conversions, and engagement rates. By aggregating data from different tables, it allows advertisers to evaluate the effectiveness of their campaigns, identify trends, and make data-driven decisions to optimize their advertising strategies.
