Ad Report
1with
2 report as (
3 select
4 *
5 from
6 {{raw.pinterest_ads.ad_report}} -- to join another pinterest ads account
7 -- union all
8 -- select * from {{}}
9 )
10 , advertisers as (
11 select
12 *
13 from
14 {{raw.pinterest_ads.ad_account}}
15 )
16 , ad as (
17 select
18 *
19 from
20 {{raw.pinterest_ads.ad}}
21 )
22select
23 cast(report.date as date) date_day
24 , advertisers.name account_name
25 , advertisers.id account_id
26 , '' as campaign_name
27 , ad.campaign_id campaign_id
28 , ad.name ad_name
29 , report.ad_id
30 , ad.status as ad_status
31 , ad.type as ad_type
32 , ad.created_time
33 , report.spend_in_micro_dollar / 1000000 spend
34 , case
35 when report.impression_1_gross is null then report.impression_2
36 else report.impression_1_gross
37 end as impressions
38 , total_engagement as clicks
39 , total_conversions conversions
40from
41 report
42 left join ad on report.ad_group_id = ad.id
43 left join advertisers on ad.ad_account_id = advertisers.id+------------+--------------+------------+----------------+--------------+--------------+---------+-----------+----------+---------------------+-------+-------------+------------------+------------------+------------------+
| 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.
