Weld logo
snapchat-ads logo
Snapchat ads

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.
1with
2    report as (
3        select
4            *
5        from
6            {{raw.snapchat_ads.ad_daily_report}}
7    )
8  , creatives as (
9        select
10            *
11        from
12            {{raw.snapchat_ads.campaign}}
13    )
14  , account as (
15        select
16            *
17        from
18            {{raw.snapchat_ads.ad_account}}
19    )
20  , ads as (
21        select
22            *
23        from
24            {{raw.snapchat_ads.ad}}
25    )
26select
27    cast(report.date as date) as date_day
28  , account.ad_account_id as account_id
29  , account.name as account_name
30  , campaign.id as campaign_id
31  , campaign.name as campaign_name
32  , report.ad_id
33  , ads.name ad_name
34  , account.currency
35  , round(sum(spend / 1000000), 2) spend
36  , sum(report.impressions) as impressions
37  , sum(report.swipes) as clicks
38  , sum(safe_divide((spend / 1000000), swipes)) as cpc
39  , sum(safe_divide(swipes, impressions)) as ctr
40  , sum(safe_divide((spend / 1000000), impressions)) * 1000 as cpm
41  , sum(conversion_purchases) as conversions
42from
43    report
44    left join ads on report.ad_id = ads.id
45    left join campaign on ads.campain_id = creatives.id
46    left join account on creatives.ad_account_id = account.id
47where
48    cast(report.date as date) <= current_date()
49group by
50    1
51  , 2
52  , 3
53  , 4
54  , 5
55  , 6
Example of output from model:
+------------+------------+--------------+-------------+----------------+--------+----------------+----------+-------+-------------+-------+---------+---------+--------------+
| date_day   | account_id | account_name | campaign_id | campaign_name  | ad_id  | ad_name        | currency | spend | impressions | clicks| cpc     | ctr     | cpm          |
+------------+------------+--------------+-------------+----------------+--------+----------------+----------+-------+-------------+-------+---------+---------+--------------+
| 2022-01-01 | 123456     | Example Ads   | 987654      | Campaign XYZ   | 789012 | Ad 1           | USD      | 10.50 | 1000        | 50    | 0.21    | 0.05    | 10.50        |
| 2022-01-01 | 123456     | Example Ads   | 987654      | Campaign XYZ   | 789012 | Ad 2           | USD      | 15.75 | 1500        | 75    | 0.21    | 0.05    | 10.50        |
| 2022-01-01 | 123456     | Example Ads   | 987654      | Campaign XYZ   | 789012 | Ad 3           | USD      | 20.00 | 2000        | 100   | 0.20    | 0.05    | 10.00        |
| 2022-01-02 | 123456     | Example Ads   | 987654      | Campaign XYZ   | 789012 | Ad 1           | USD      | 12.00 | 1200        | 60    | 0.20    | 0.05    | 10.00        |
| 2022-01-02 | 123456     | Example Ads   | 987654      | Campaign XYZ   | 789012 | Ad 2           | USD      | 18.00 | 1800        | 90    | 0.20    | 0.05    | 10.00        |
| 2022-01-02 | 123456     | Example Ads   | 987654      | Campaign XYZ   | 789012 | Ad 3           | USD      | 24.00 | 2400        | 120   | 0.20    | 0.05    | 10.00        |
+------------+------------+--------------+-------------+----------------+--------+----------------+----------+-------+-------------+-------+---------+---------+--------------+

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.

Ready to start modeling your own snapchat-ads data?

Get started building your data warehouse with snapchat-ads and connect all your apps and databases.

snapchat-ads logo
Weld logo
Sign up for free
© 2024 Weld. All rights reserved.