Account Report
1with
2 stats as (
3 select
4 *
5 from
6 {{raw.facebook_ads.ad_insight}}
7 )
8 , accounts as (
9 select
10 *
11 from
12 {{raw.facebook_ads.account}}
13 )
14select
15 cast(stats.date as date) date_day
16 , accounts.id account_id
17 , accounts.name account_name
18 , accounts.account_status
19 , accounts.business_country_code
20 , accounts.created_time
21 , accounts.currency
22 , sum(spend) spend
23 , sum(impressions) impressions
24 , sum(clicks) clicks
25 , sum(safe_divide(clicks, spend)) as cpc
26 , sum(safe_divide(clicks, impressions)) as ctr
27 , sum(safe_divide(spend, impressions)) * 1000 as cpm -- Additionalcolumns should be manually specified if needed
28from
29 stats
30 left join accounts on stats.account_id = accounts.id
31group by
32 1
33 , 2
34 , 3
35 , 4
36 , 5
37 , 6
38 , 7
39 -- Adjust these numbers according to the selected columnsdate_day | account_id | account_name | account_status | business_country_code | created_time | currency | spend | impressions | clicks | cpc | ctr | cpm
------------+------------+--------------+----------------+-----------------------+--------------+----------+-------+-------------+--------+------+------+
2022-01-01 | 1234567890 | Example Ads | Active | US | 2021-01-01 | USD | 100 | 1000 | 50 | 0.5 | 0.05 | 100
2022-01-02 | 1234567890 | Example Ads | Active | US | 2021-01-01 | USD | 150 | 2000 | 75 | 0.5 | 0.0375 | 75
2022-01-03 | 1234567890 | Example Ads | Active | US | 2021-01-01 | USD | 200 | 3000 | 100 | 0.5 | 0.0333 | 66.67Generate a comprehensive report on Facebook Ads performance. This template integrates with the Facebook Ads data source and retrieves data from two main tables: "ad_insight" and "account". The SQL code combines these tables using a left join to create a unified dataset for analysis. The resulting report provides valuable insights into various aspects of Facebook Ads accounts. It includes information such as the date of the data, account ID, account name, account status, business country code, account creation time, and currency. Additionally, the report calculates aggregated metrics like total spend, impressions, clicks, cost per click (CPC), click-through rate (CTR), and cost per thousand impressions (CPM). By using this SQL template, marketers and advertisers can gain a deeper understanding of their Facebook Ads performance. They can analyze trends over time, compare account performance, track advertising costs, and evaluate the effectiveness of their campaigns. This report serves as a powerful tool for optimizing ad strategies, identifying areas for improvement, and making data-driven decisions to maximize ROI.
