Ad Group Report
1with
2 daily as (
3 select
4 *
5 from
6 {{raw.tiktok_ads.ad_group_daily_report}} -- to join another tiktok ads account
7 -- union all
8 -- select * from {{}}
9 )
10 , ad_groups as (
11 select
12 *
13 from
14 {{raw.tiktok_ads.ad_group}}
15 )
16 , advertiser as (
17 select
18 *
19 from
20 {{raw.tiktok_ads.advertiser}}
21 )
22 , campaigns as (
23 select
24 *
25 from
26 {{raw.tiktok_ads.campaign}}
27 )
28select
29 date_trunc(
30 cast(cast(stat_time_day as timestamp) as date)
31 , day
32 ) date_day
33 , ad_groups.advertiser_id as account_id
34 , advertiser.name as account_name
35 , cast(campaigns.id as string) as campaign_id
36 , campaigns.campaign_name
37 , daily.ad_group_id
38 , ad_groups.adgroup_name as ad_group_name
39 , advertiser.currency
40 , ad_groups.gender
41 , ad_groups.audience_type
42 , ad_groups.budget
43 , sum(daily.impressions) as impressions
44 , sum(daily.clicks) as clicks
45 , sum(daily.spend) as spend
46 , sum(daily.reach) as reach
47 , sum(daily.conversion) as conversion
48 , sum(daily.likes) as likes
49 , sum(daily.comments) as comments
50 , sum(daily.shares) as shares
51 , sum(daily.profile_visits) as profile_visits
52 , sum(daily.follows) as follows
53 , sum(daily.video_watched_2s) as video_watched_2_s
54 , sum(daily.video_watched_6s) as video_watched_6_s
55 , sum(daily.video_views_p25) as video_views_p_25
56 , sum(daily.video_views_p50) as video_views_p_50
57 , sum(daily.video_views_p75) as video_views_p_75
58 , sum(safe_divide(daily.spend, nullif(daily.clicks, 0))) as cpc
59 , sum(daily.clicks) / nullif(sum(daily.impressions), 0) * 100 as ctr
60 , sum(daily.spend) / nullif(sum(daily.impressions), 0) * 1000 as cpm
61from
62 daily
63 left join ad_groups on daily.ad_group_id = ad_groups.id
64 left join advertiser on ad_groups.advertiser_id = advertiser.id
65 left join campaigns on ad_groups.campaign_id = campaigns.id
66group by
67 1
68 , 2
69 , 3
70 , 4
71 , 5
72 , 6
73 , 7
74 , 8
75 , 9
76 , 10
77 , 11+------------+-----------+--------------+-------------+-----------------+--------------+----------------+----------+--------+---------------+---------+-------------+---------+-------+---------+------------+-----------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+
| date_day | account_id | account_name | campaign_id | campaign_name | ad_group_id | ad_group_name | currency | gender | audience_type | budget | impressions | clicks | spend | reach | conversion | likes | comments | shares | profile_visits | follows | video_watched_2_s | video_watched_6_s | video_views_p_25 | video_views_p_50 | video_views_p_75 | cpc | ctr | cpm |
+------------+-----------+--------------+-------------+-----------------+--------------+----------------+----------+--------+---------------+---------+-------------+---------+-------+---------+------------+-----------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+
| 2022-01-01 | 12345 | Example Corp | 67890 | Example Campaign| 987654321 | Example Ad Group| USD | Male | Targeted | 1000.00 | 1000000 | 5000 | 500.00| 800000 | 100 | 500 | 200 | 100 | 1000 | 200 | 1000 | 800 | 500 | 200 | 0.10 | 0.50 | 0.50 |
+------------+-----------+--------------+-------------+-----------------+--------------+----------------+----------+--------+---------------+---------+-------------+---------+-------+---------+------------+-----------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+Retrieves data from various tables within the TikTok Ads database to generate a comprehensive report on ad group performance. The SQL code begins by creating a temporary table named "daily" that selects all columns from the "ad_group_daily_report" table in the TikTok Ads database. This table can be joined with another TikTok Ads account to combine data. Next, the code creates additional temporary tables for "ad_groups," "advertiser," and "campaigns," selecting all columns from their respective tables in the TikTok Ads database. The final SELECT statement combines data from these temporary tables to generate the desired report. It retrieves information such as the date, account ID, account name, campaign ID, campaign name, ad group ID, ad group name, currency, gender, audience type, budget, impressions, clicks, spend, reach, conversion, likes, comments, shares, profile visits, follows, video watched times, and video view percentages. The GROUP BY clause groups the data by various columns to provide aggregated insights for each unique combination of these columns. Overall, this SQL template is useful for analyzing and monitoring the performance of ad groups on the TikTok Ads platform. It provides valuable insights into key metrics such as impressions, clicks, spend, reach, conversions, engagement, and video views. These insights can help advertisers optimize their ad campaigns, allocate budgets effectively, and make data-driven decisions to maximize their advertising ROI on TikTok.
