Weld
Start free
Facebook Ads model

Ad Report

Integrates with Facebook Ads and retrieves data from various tables such as ad_insight, account, campaign, ad_set, and ad. It selects specific columns from these tables and performs calculations to generate aggregated metrics such as spend, reach, impressions, clicks, CPC, CTR, and CPM. The template then joins these tables based on specific conditions and groups the results by certain columns.

See the SQLAll Facebook Ads models
Grain
See the notes
Built from
  1. 5
    Raw tables
  2. 1
    Staging
Reads from Facebook Ads
adad_insightad_roas_insight_action_valuesad_roas_insight_conversion_insightscreative
Ships as
Weld and dbt SQL, open source on GitHub
staging.facebook_ads.ad_report
1-- staging.facebook_ads.ad_report
2-- Ad Report. Integrates with Facebook Ads and retrieves data from various tables
3-- such as ad_insight, account, campaign, ad_set, and ad. It selects specific
4-- columns from these tables and performs calculations to generate aggregated
5-- metrics such as spend, reach, impressions, clicks, CPC, CTR, and CPM. The
6-- template then joins these tables based on specific conditions and groups the
7-- results by certain columns.
8--
9-- From the weld.app template library, corrected. Not reconciled against
10-- Facebook Ads; check the output against the source before relying on it.
11
12with
13    stats as (
14        select
15            *
16        from
17            {{raw.facebook_ads.ad_insight}}
18    )
19  , ad as (
20        select
21            *
22        from
23            {{raw.facebook_ads.ad}}
24    )
25  , creative as (
26        select
27            *
28        from
29            {{raw.facebook_ads.creative}}
30    )
31  , action_data as (
32        select
33            *
34        except
35        (
36            account_id
37          , account_name
38          , account_currency
39          , campaign_id
40          , adset_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.

Provide insights and analytics on Facebook Ads data. By integrating with the Facebook Ads platform, this SQL template retrieves data from various tables such as ad_insight, account, campaign, ad_set, and ad. The SQL code starts by creating temporary tables for each of these entities, allowing for easier querying and analysis. It then selects specific columns from these tables, including the date, account ID and name, campaign ID and name, ad set ID and name, ad ID and name, as well as various metrics such as spend, reach, impressions, clicks, CPC (cost per click), CTR (click-through rate), and CPM (cost per thousand impressions). The SQL template performs left joins between the temporary tables to link the data together based on the corresponding IDs. This enables the aggregation of metrics at different levels, such as by date, account, campaign, ad set, and ad. The resulting output provides a comprehensive overview of the performance of Facebook Ads campaigns, allowing marketers and advertisers to analyze key metrics and derive insights. For example, they can track the spend and reach of their ads, measure engagement through impressions and clicks, and calculate important performance indicators like CPC and CTR. By customizing the SQL template and selecting additional columns, users can further tailor the analysis to their specific needs and gain deeper insights into their Facebook Ads campaigns.

Raw tables it reads

  • raw.facebook_ads.ad
  • raw.facebook_ads.ad_insight
  • raw.facebook_ads.ad_roas_insight_action_values
  • raw.facebook_ads.ad_roas_insight_conversion_insights
  • raw.facebook_ads.creative

More Facebook Ads models

Put Facebook Ads in front
of your agents.

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

Start for freeTalk to us