Weld
Start free
Snapchat Ads model

Ad Report

Generates an ad report by integrating with Snapchat Ads. It retrieves data from various tables such as ad_daily_report, campaign, ad_account, and ad. The report includes information such as date, account ID and name, campaign ID and name, ad ID and name, currency, spend, impressions, clicks, CPC, CTR, CPM, and conversions. The data is filtered based on the date and grouped by different columns.

See the SQLAll Snapchat Ads models
Grain
See the notes
Built from
  1. 4
    Raw tables
  2. 1
    Staging
Reads from Snapchat Ads
adad_accountad_daily_reportcreative
Ships as
Weld and dbt SQL, open source on GitHub
staging.snapchat_ads.ad_report
1-- staging.snapchat_ads.ad_report
2-- Ad Report. Generates an ad report by integrating with Snapchat Ads. It retrieves
3-- data from various tables such as ad_daily_report, campaign, ad_account, and ad.
4-- The report includes information such as date, account ID and name, campaign ID
5-- and name, ad ID and name, currency, spend, impressions, clicks, CPC, CTR, CPM,
6-- and conversions. The data is filtered based on the date and grouped by different
7-- columns.
8--
9-- From the weld.app template library. Not reconciled against Snapchat Ads;
10-- check the output against the source before relying on it.
11
12with
13    report as (
14        select
15            *
16        from
17            {{raw.snapchat_ads.ad_daily_report}}
18    
19    )
20  , account as (
21        select
22            *
23        from
24            {{raw.snapchat_ads.ad_account}}
25    )
26  , ads as (
27        select
28            *
29        from
30            {{raw.snapchat_ads.ad}}
31    )
32    , creative as (  
33        select
34            *
35        from
36            {{raw.snapchat_ads.creative}})
37
38    
39select
40    cast(report.date as date) as date_day

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

Notes

How it counts. And where it stops.

Combines data from multiple tables, including ad_daily_report, campaign, ad_account, and ad, to generate a comprehensive report. The template begins by creating temporary tables using the "with" clause. These tables include "report," which contains data from the ad_daily_report table, "creatives," which contains data from the campaign table, "account," which contains data from the ad_account table, and "ads," which contains data from the ad table. The main query then selects various fields from these temporary tables, such as the date, account ID and name, campaign ID and name, ad ID, ad name, currency, spend, impressions, clicks, CPC (cost per click), CTR (click-through rate), CPM (cost per thousand impressions), and conversions. To generate the report, the main query performs left joins between the temporary tables based on their respective relationships. The "where" clause filters the data to include only records with a date less than or equal to the current date. Finally, the results are grouped by the selected fields, providing a summarized view of the advertising performance. This SQL template can be useful for analyzing ad campaign effectiveness, monitoring spending, tracking conversions, and optimizing advertising strategies on Snapchat Ads.

Raw tables it reads

  • raw.snapchat_ads.ad
  • raw.snapchat_ads.ad_account
  • raw.snapchat_ads.ad_daily_report
  • raw.snapchat_ads.creative

Put Snapchat Ads in front
of your agents.

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

Start for freeTalk to us