Weld logo
pinterest-ads logo
Pinterest ads

Ad Report

Generates an ad report for the Pinterest Ads integration. It retrieves data from multiple tables, including ad reports, ad accounts, campaigns, and ads. The report includes information such as date, account name and ID, campaign name and ID, ad name, status, type, created time, spend, impressions, clicks, CPC, CTR, CPM, and total conversions. The data is grouped by various columns for analysis.
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
Example of output from model:
+------------+--------------+------------+----------------+--------------+--------------+---------+-----------+----------+---------------------+-------+-------------+------------------+------------------+------------------+
| 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.

Ready to start modeling your own pinterest-ads data?

Get started building your data warehouse with pinterest-ads and connect all your apps and databases.

pinterest-ads logo
Weld logo
Sign up for free
© 2024 Weld. All rights reserved.