Weld
Start free
Pinterest Ads model

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.

See the SQLAll Pinterest Ads models
Grain
See the notes
Built from
  1. 3
    Raw tables
  2. 1
    Staging
Reads from Pinterest Ads
adad_accountad_report
Ships as
Weld and dbt SQL, open source on GitHub
staging.pinterest_ads.ad_report
1-- staging.pinterest_ads.ad_report
2-- Ad Report. Generates an ad report for the Pinterest Ads integration. It retrieves
3-- data from multiple tables, including ad reports, ad accounts, campaigns, and ads.
4-- The report includes information such as date, account name and ID, campaign name
5-- and ID, ad name, status, type, created time, spend, impressions, clicks, CPC,
6-- CTR, CPM, and total conversions. The data is grouped by various columns for
7-- analysis.
8--
9-- From the weld.app template library, corrected. Not reconciled against
10-- Pinterest Ads; check the output against the source before relying on it.
11
12with
13    report as (
14        select
15            *
16        from
17            {{raw.pinterest_ads.ad_report}} --   to join another pinterest ads account
18            --   union all
19            --   select * from <second_account_table>
20    )
21  , advertisers as (
22        select
23            *
24        from
25            {{raw.pinterest_ads.ad_account}}
26    )
27  , ad as (
28        select
29            *
30        from
31            {{raw.pinterest_ads.ad}}
32    )
33select
34    cast(report.date as date) date_day
35  , advertisers.name account_name
36  , report.ad_account_id as account_id
37  , report.campaign_name
38  , report.campaign_id
39  , ad.name ad_name
40  , report.ad_id

Refs like {{raw.…}} resolve to your synced tables in Weld; the dbt view uses source() instead.

Notes

How it counts. And where it stops.

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.

Raw tables it reads

  • raw.pinterest_ads.ad
  • raw.pinterest_ads.ad_account
  • raw.pinterest_ads.ad_report

Put Pinterest Ads in front
of your agents.

Connect Pinterest Ads, deploy the models, and ask questions in Claude, Cursor or ChatGPT. Free for 14 days, no credit card.

Start for freeTalk to us