Weld logo
pinterest-ads logo
Pinterest ads

Campaign Report

Generates a campaign report for Pinterest Ads integration. It retrieves data from the `pinterest_ads.campaign_report`, `pinterest_ads.campaign`, and `pinterest_ads.ad_account` tables. The report includes information such as date, account name and ID, campaign name and status, spend, impressions, clicks, CPC, CTR, CPM, and total conversions.
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
Example of output from model:
+------------+----------------+------------+-----------------+--------------+-----------------+-------+-------------+--------+-----+-----+-----+-----------------+
| 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.

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.