Account Report
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+------------+--------------+------------+----------------+---------+-------+-------------+-------+-------+-------+-------+------------------+
| 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.
