Weld logo
facebook-ads logo
Facebook ads

Account Report

Generates an account report for the Facebook Ads integration. It retrieves data from the "ad_insight" and "account" tables and calculates various metrics such as spend, impressions, clicks, CPC, and CTR. The report is grouped by date, account ID, account name, account status, business country code, created time, and currency.
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 columns
Example of output from model:
date_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.67

Generate 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.

Ready to start modeling your own facebook-ads data?

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

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