Weld logo
pinterest-ads logo
Pinterest ads

Account Report

Generates an account report for Pinterest Ads integration. It retrieves data from the `pinterest_ads.ad_account_report` table and joins it with the `pinterest_ads.ad_account` table. The report includes information such as date, account name, account ID, currency code, country, spend, impressions, clicks, CPC, CTR, CPM, and total conversions. The data is grouped by various columns specified in the query.
1with
2    report as (
3        select
4            *
5        from
6            {{raw.pinterest_ads.ad_account_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    )
17select
18    cast(report.date as date) date_day
19  , advertisers.name account_name
20  , report.AD_ACCOUNT_ID account_id
21  , advertisers.currency currency_code
22  , advertisers.country
23  , sum(report.spend_in_micro_dollar / 1000000) spend
24  , sum(
25        case
26            when report.impression_1_gross is null then report.impression_2
27            else report.impression_1_gross
28        end
29    ) as impressions
30  , sum(total_engagement) as clicks
31  , sum(report.cpc_in_micro_dollar / 1000000) cpc
32  , sum(
33        safe_divide(total_engagement, report.impression_1_gross)
34    ) as ctr
35  , sum(
36        safe_divide(
37            (report.spend_in_micro_dollar / 1000000)
38          , report.impression_1_gross
39        )
40    ) * 1000 as cpm
41  , sum(total_conversions) as total_conversions
42    -- Additional columns should be manually specified if needed
43from
44    report
45    left join advertisers on report.ad_account_id = advertisers.id
46group by
47    1
48  , 2
49  , 3
50  , 4
51  , 5
52    -- Adjust these numbers according to the selected columns
Example of output from model:
+------------+--------------+------------+----------------+---------+-------+-------------+-------+-------+-------+-------+------------------+
| date_day   | account_name | account_id | currency_code  | country | spend | impressions | clicks| cpc   | ctr   | cpm   | total_conversions|
+------------+--------------+------------+----------------+---------+-------+-------------+-------+-------+-------+-------+------------------+
| 2022-01-01 | Pinterest Ads| 1234567890 | USD            | US      | 100.5 | 5000        | 100   | 1.005 | 0.02  | 20.1  | 10               |
| 2022-01-02 | Pinterest Ads| 1234567890 | USD            | US      | 150.2 | 7500        | 150   | 1.001 | 0.02  | 20.03 | 12               |
| 2022-01-03 | Pinterest Ads| 1234567890 | USD            | US      | 200.7 | 10000       | 200   | 1.003 | 0.02  | 20.07 | 15               |
+------------+--------------+------------+----------------+---------+-------+-------------+-------+-------+-------+-------+------------------+

Generate a comprehensive report on Pinterest Ads account performance. This SQL model integrates with the Pinterest Ads platform and retrieves data from two main tables: "pinterest_ads.ad_account_report" and "pinterest_ads.ad_account". The SQL code begins by creating a temporary table called "report" which selects all columns from the "pinterest_ads.ad_account_report" table. This table contains various metrics such as date, account ID, spend, impressions, clicks, CPC, CTR, CPM, and total conversions. The spend metric is divided by 1,000,000 to convert it from micro-dollars to dollars. The next step involves creating another temporary table called "advertisers" which selects all columns from the "pinterest_ads.ad_account" table. This table provides additional information about the account, such as the account name, currency code, and country. Finally, the SQL query joins the "report" and "advertisers" tables on the ad account ID and performs aggregations based on the selected columns. The result is a summarized report that includes the date, account name, account ID, currency code, country, spend, impressions, clicks, CPC, CTR, CPM, and total conversions. This SQL template can be useful for analyzing the performance of Pinterest Ads accounts. It provides insights into key metrics such as spend, impressions, clicks, and conversions, allowing marketers to evaluate the effectiveness of their advertising campaigns. By aggregating the data at the account level, it enables easy comparison and identification of trends over time.

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.