Ad Report
1with
2 daily as (
3 select
4 *
5 from
6 {{raw.tiktok_ads.ad_daily_report}}
7 -- to join another tiktok ads account
8 -- union all
9 -- select * from {{}}
10 )
11 , ads as (
12 select
13 *
14 from
15 {{raw.tiktok_ads.ad}}
16 )
17 , ad_groups as (
18 select
19 *
20 from
21 {{raw.tiktok_ads.ad_group}}
22 )
23 , advertiser as (
24 select
25 *
26 from
27 {{raw.tiktok_ads.advertiser}}
28 )
29 , campaigns as (
30 select
31 *
32 from
33 {{raw.tiktok_ads.campaign}}
34 )
35select
36 date_trunc(
37 cast(cast(stat_time_day as timestamp) as date)
38 , day
39 ) date_day
40 , ad_groups.advertiser_id account_id
41 , advertiser.name account_name
42 , campaigns.id campaign_id
43 , campaigns.campaign_name
44 , ad_groups.id ad_group_id
45 , ad_groups.adgroup_name ad_group_name
46 , daily.ad_id
47 , ads.ad_name
48 , advertiser.currency
49 , ad_groups.gender
50 , ad_groups.audience_type
51 , ad_groups.budget
52 , sum(daily.impressions) as impressions
53 , sum(daily.clicks) as clicks
54 , sum(daily.spend) as spend
55 , sum(daily.reach) as reach
56 , sum(daily.conversion) as conversion
57 , sum(daily.likes) as likes
58 , sum(daily.comments) as comments
59 , sum(daily.shares) as shares
60 , sum(daily.profile_visits) as profile_visits
61 , sum(daily.follows) as follows
62 , sum(daily.video_watched_2s) as video_watched_2_s
63 , sum(daily.video_watched_6s) as video_watched_6_s
64 , sum(daily.video_views_p25) as video_views_p_25
65 , sum(daily.video_views_p50) as video_views_p_50
66 , sum(daily.video_views_p75) as video_views_p_75
67 , sum(safe_divide(spend, nullif(daily.clicks, 0))) as cpc
68 , sum(safe_divide(clicks, nullif(daily.impressions, 0))) * 100 as ctr
69 , sum(safe_divide(spend, nullif(daily.impressions, 0))) * 1000 as cpm -- Additional pass-through columns should be manually specified if needed
70from
71 daily
72 left join ads on daily.ad_id = ads.id
73 left join ad_groups on ads.ad_group_id = ad_groups.id
74 left join advertiser on ads.advertiser_id = advertiser.id
75 left join campaigns on ads.campaign_id = campaigns.id
76group by
77 1
78 , 2
79 , 3
80 , 4
81 , 5
82 , 6
83 , 7
84 , 8
85 , 9
86 , 10
87 , 11
88 , 12
89 , 13
90 -- Adjust these numbers according to the selected columns+------------+-----------+--------------+------------+-----------------+--------------+-----------------+-------+-----------------+----------+---------+----------------+-------------+-------------+-------------+---------------+-------------+--------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+
| date_day | account_id | account_name | campaign_id | campaign_name | ad_group_id | ad_group_name | ad_id | ad_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 | 123 | Account A | 456789 | Campaign XYZ | 7890123 | Ad Group ABC | 9876 | Ad Example | USD | Male | Target Group | 1000.00 | 100000 | 5000 | 250.00 | 80000 | 10 | 50 | 20 | 5 | 100 | 2 | 200 | 150 | 100 | 50 | 25 | 0.05 | 5.00 | 2.50 |
+------------+-----------+--------------+------------+-----------------+--------------+-----------------+-------+-----------------+----------+---------+----------------+-------------+-------------+-------------+---------------+-------------+--------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+Retrieves data from the TikTok Ads integration. It combines multiple tables, including ad_daily_report, ad, ad_group, advertiser, and campaign, to generate a comprehensive report on ad performance. This SQL template provides valuable insights into the performance of ads on TikTok. It retrieves data such as impressions, clicks, spend, reach, conversions, likes, comments, shares, profile visits, follows, video views, and engagement rates. By aggregating data at the daily level, it allows users to analyze the performance of different ad campaigns, ad groups, and individual ads. The template joins the relevant tables using left joins, ensuring that all available data is included in the report. It groups the data by various dimensions, including date, account, campaign, ad group, and ad, providing a granular view of performance metrics. Additionally, it calculates derived metrics such as cost per click (CPC), click-through rate (CTR), and cost per thousand impressions (CPM). By utilizing this SQL template, users can gain valuable insights into their TikTok ad campaigns. They can identify top-performing ads, optimize their targeting strategies, track campaign budgets, and measure the effectiveness of their ad spend. The generated report can be used to make data-driven decisions and improve overall advertising performance on TikTok.
