Weld logo
pinterest-ads logo
Pinterest ads

Ad Group Report

Generates an ad group report for the Pinterest Ads integration. It retrieves data from various tables such as ad groups, campaigns, and advertisers. The report includes information such as the date, account name and ID, campaign name and status, ad group 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.ad_group_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_groups as (
24        select
25            *
26        from
27            {{raw.pinterest_ads.ad_group}}
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.status campaign_status
35  , campaigns.id campaign_id
36  , ad_groups.name ad_group_name
37  , report.ad_group_id
38  , ad_groups.created_time
39  , ad_groups.start_time
40  , ad_groups.end_time
41  , ad_groups.status ad_group_status
42  , sum(report.spend_in_micro_dollar / 1000000) spend
43  , sum(
44        case
45            when report.impression_1_gross is null then report.impression_2
46            else report.impression_1_gross
47        end
48    ) as impressions
49  , sum(total_engagement) as clicks
50  , sum(report.cpc_in_micro_dollar / 1000000) cpc
51  , sum(
52        safe_divide(total_engagement, report.impression_1_gross)
53    ) as ctr
54  , sum(
55        safe_divide(
56            (report.spend_in_micro_dollar / 1000000)
57          , report.impression_1_gross
58        )
59    ) * 1000 as cpm
60  , sum(total_conversions) as total_conversions
61    -- Additional  columns should be manually specified if needed
62from
63    report
64    left join ad_groups on report.ad_group_id = ad_groups.id
65    left join campaigns on ad_groups.campaign_id = campaigns.id
66    left join advertisers on campaigns.ad_account_id = advertisers.id
67group by
68    1
69  , 2
70  , 3
71  , 4
72  , 5
73  , 6
74  , 7
75  , 8
76  , 9
77  , 10
78  , 11
79  , 12
80    -- Adjust these numbers according to the selected columns
Example of output from model:
+------------+--------------+------------+----------------+-----------------+-------------+-----------------+--------------+---------------------+---------------------+---------------------+-----------------+---------+-------------+--------+-------------+------------------+
| date_day   | account_name | account_id | campaign_name  | campaign_status | campaign_id | ad_group_name   | ad_group_id  | created_time        | start_time          | end_time            | ad_group_status | spend   | impressions | clicks | cpc         | ctr              |
+------------+--------------+------------+----------------+-----------------+-------------+-----------------+--------------+---------------------+---------------------+---------------------+-----------------+---------+-------------+--------+-------------+------------------+
| 2022-01-01 | Pinterest    | 1234567890 | Winter Campaign | Active          | 987654321   | Ad Group 1      | 111111111    | 2022-01-01 08:00:00 | 2022-01-01 09:00:00 | 2022-01-01 10:00:00 | Active          | 100.50  | 1000        | 50     | 2.01        | 0.05             |
| 2022-01-01 | Pinterest    | 1234567890 | Winter Campaign | Active          | 987654321   | Ad Group 2      | 222222222    | 2022-01-01 09:00:00 | 2022-01-01 10:00:00 | 2022-01-01 11:00:00 | Active          | 75.25   | 800         | 40     | 1.88        | 0.05             |
| 2022-01-01 | Pinterest    | 1234567890 | Winter Campaign | Active          | 987654321   | Ad Group 3      | 333333333    | 2022-01-01 10:00:00 | 2022-01-01 11:00:00 | 2022-01-01 12:00:00 | Active          | 120.75  | 1500        | 75     | 1.61        | 0.05             |
+------------+--------------+------------+----------------+-----------------+-------------+-----------------+--------------+---------------------+---------------------+---------------------+-----------------+---------+-------------+--------+-------------+------------------+

Provide insights and analytics for Pinterest Ads campaigns. This SQL model combines data from multiple tables, including ad group reports, ad accounts, campaigns, and ad groups. By executing this SQL template, you can obtain a comprehensive report that includes various metrics such as date, account name and ID, campaign name and status, ad group name and status, spend, impressions, clicks, CPC (cost per click), CTR (click-through rate), CPM (cost per thousand impressions), and total conversions. The template utilizes common SQL operations such as joins and aggregations to combine the data from different tables and calculate the desired metrics. It allows you to analyze the performance of your ad groups, campaigns, and overall account on Pinterest Ads. This SQL template can be useful for advertisers and marketers who want to monitor and optimize their Pinterest Ads campaigns. It provides valuable insights into key performance indicators, allowing you to identify successful campaigns, track ad spend, measure engagement, and evaluate conversion rates. By customizing the template and selecting specific columns, you can tailor the report to your specific needs and include additional metrics or dimensions as required.

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.