Weld logo
snapchat-ads logo
Snapchat ads

Account Report

Generates an account report for the Snapchat Ads integration. It retrieves data from various tables such as ad_daily, account, ads, ad_squads, and campaigns. The report includes metrics such as spend, impressions, clicks, CPC, CTR, CPM, and conversions, grouped by date, account ID, account name, and currency.
1with
2    ad_daily as (
3        select
4            *
5        from
6            {{raw.snapchat_ads.ad_daily_report}} --   to join another snapchat ads account
7            --   union all
8            --   select * from {{}}
9    )
10  , account as (
11        select
12            *
13        from
14            {{raw.snapchat_ads.ad_account}}
15    )
16  , ads as (
17        select
18            *
19        from
20            {{raw.snapchat_ads.ad}}
21    )
22  , ad_squads as (
23        select
24            *
25        from
26            {{raw.snapchat_ads.ad_squad}}
27    )
28  , campaigns as (
29        select
30            *
31        from
32            {{raw.snapchat_ads.campaign}}
33    )
34select
35    cast(ad_daily.date as date) as date_day
36  , account.ad_account_id as account_id
37  , account.name account_name
38  , account.currency
39  , round(sum(spend / 1000000), 2) spend
40  , sum(ad_daily.impressions) as impressions
41  , sum(ad_daily.swipes) as clicks
42  , sum(safe_divide((spend / 1000000), swipes)) as cpc
43  , sum(safe_divide(swipes, impressions)) as ctr
44  , sum(safe_divide((spend / 1000000), impressions)) * 1000 as cpm
45  , sum(conversion_purchases) as conversions
46from
47    ad_daily
48    left join ads on ad_daily.ad_id = ads.id
49    left join ad_squads on ads.ad_squad_id = ad_squads.id
50    left join campaigns on ad_squads.campaign_id = campaigns.id
51    left join account on campaigns.ad_account_id = account.id
52group by
53    1
54  , 2
55  , 3
56  , 4 -- Adjust these numbers according to the selected columns
Example of output from model:
+------------+------------+----------------+----------+-------+-------------+--------+-------+-------+-------+--------------+
| date_day   | account_id | account_name   | currency | spend | impressions | clicks | cpc   | ctr   | cpm   | conversions  |
+------------+------------+----------------+----------+-------+-------------+--------+-------+-------+-------+--------------+
| 2022-01-01 | 1234567890 | Example Account| USD      | 10.25 | 10000       | 500    | 0.02  | 0.05  | 1.03  | 25           |
| 2022-01-02 | 1234567890 | Example Account| USD      | 15.75 | 15000       | 750    | 0.03  | 0.05  | 1.05  | 30           |
| 2022-01-03 | 1234567890 | Example Account| USD      | 12.50 | 12000       | 600    | 0.02  | 0.05  | 1.04  | 28           |
| 2022-01-04 | 1234567890 | Example Account| USD      | 9.75  | 9000        | 450    | 0.02  | 0.05  | 1.08  | 22           |
| 2022-01-05 | 1234567890 | Example Account| USD      | 11.00 | 11000       | 550    | 0.02  | 0.05  | 1.00  | 24           |
+------------+------------+----------------+----------+-------+-------------+--------+-------+-------+-------+--------------+

Generate a report on Snapchat Ads data. It integrates with the Snapchat Ads platform and retrieves data from various tables such as ad_daily, ad_account, ad, ad_squad, and campaign. The SQL code joins these tables together to calculate various metrics related to ad performance. The generated report includes insights such as the date of the ad, the account ID and name, the currency used, the total spend (in millions), the number of impressions, clicks (swipes), the cost per click (CPC), the click-through rate (CTR), the cost per thousand impressions (CPM), and the number of conversions. This SQL template is useful for analyzing and monitoring the performance of Snapchat Ads campaigns. It provides valuable insights into key metrics such as spend, impressions, clicks, and conversions. By aggregating data at the account level, advertisers can gain a comprehensive understanding of their ad performance and make informed decisions to optimize their campaigns.

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.