Tiktok ads
Campaign Report
Generates a campaign report for the TikTok Ads integration. It retrieves data from the `campaign_daily_report`, `campaign`, and `advertiser` tables. The report includes metrics such as clicks, impressions, spend, reach, conversions, likes, comments, shares, profile visits, follows, video views, and various calculated metrics like CPC, CTR, and CPM. The data is grouped by date, account ID, account name, campaign ID, and campaign name.
1with
2 daily as (
3 select
4 *
5 from
6 {{raw.tiktok_ads.campaign_daily_report}}
7 -- to join another tiktok ads account
8 -- union all
9 -- select * from {{}}
10 )
11 , campaigns as (
12 select
13 *
14 from
15 {{raw.tiktok_ads.campaign}}
16 )
17 , advertiser as (
18 select
19 *
20 from
21 {{raw.tiktok_ads.advertiser}}
22 )
23select
24 date_trunc(
25 cast(cast(stat_time_day as timestamp) as date)
26 , day
27 ) date_day
28 , advertiser.id account_id
29 , advertiser.name account_name
30 , daily.campaign_id
31 , campaigns.campaign_name
32 , advertiser.currency
33 , sum(daily.clicks) as clicks
34 , sum(daily.impressions) as impressions
35 , sum(daily.spend) as spend
36 , sum(daily.reach) as reach
37 , sum(daily.conversion) as conversion
38 , sum(daily.likes) as likes
39 , sum(daily.comments) as comments
40 , sum(daily.shares) as shares
41 , sum(daily.profile_visits) as profile_visits
42 , sum(daily.follows) as follows
43 , sum(daily.video_watched_2s) as video_watched_2_s
44 , sum(daily.video_watched_6s) as video_watched_6_s
45 , sum(daily.video_views_p25) as video_views_p_25
46 , sum(daily.video_views_p50) as video_views_p_50
47 , sum(daily.video_views_p75) as video_views_p_75
48 , sum(safe_divide(spend, nullif(daily.clicks, 0))) as cpc
49 , sum(safe_divide(clicks, nullif(daily.impressions, 0))) * 100 as ctr
50 , sum(safe_divide(spend, nullif(daily.impressions, 0))) * 1000 as cpm
51 -- Additional columns should be manually specified if needed
52from
53 daily
54 left join campaigns on daily.campaign_id = campaigns.id
55 left join advertiser on campaigns.advertiser_id = advertiser.id
56group by
57 1
58 , 2
59 , 3
60 , 4
61 , 5
62 , 6
63 -- Adjust these numbers according to the selected columnsExample of output from model:
+------------+-----------+--------------+-------------+-----------------+----------+-------+-------------+--------+-------+------------+--------+---------+--------+-----------------+--------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+
| date_day | account_id| account_name | campaign_id | campaign_name | currency | clicks| impressions | 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 1 | 456 | Campaign 1 | USD | 100 | 1000 | 50.00 | 800 | 5 | 10 | 2 | 3 | 20 | 5 | 100 | 50 | 200 | 300 | 400 | 0.50 | 10.00 | 50.00 |
| 2022-01-02 | 123 | Account 1 | 456 | Campaign 1 | USD | 150 | 2000 | 75.00 | 1500 | 10 | 20 | 5 | 6 | 30 | 10 | 200 | 100 | 400 | 600 | 800 | 0.50 | 7.50 | 37.50 |
| 2022-01-03 | 789 | Account 2 | 101 | Campaign 2 | USD | 200 | 3000 | 100.00 | 2500 | 15 | 30 | 8 | 9 | 40 | 15 | 300 | 150 | 600 | 900 | 1200 | 0.50 | 6.67 | 33.33 |
+------------+-----------+--------------+-------------+-----------------+----------+-------+-------------+--------+-------+------------+--------+---------+--------+-----------------+--------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+Generates a report based on data from the TikTok Ads integration. This SQL model combines data from three tables: "campaign_daily_report", "campaign", and "advertiser". The template retrieves various metrics such as clicks, impressions, spend, reach, conversions, likes, comments, shares, profile visits, follows, video watched times, and video view percentages. It also calculates additional metrics like cost per click (CPC), click-through rate (CTR), and cost per thousand impressions (CPM). By running this SQL template, you can gain insights into the performance of TikTok ad campaigns. It provides a comprehensive overview of campaign metrics at a daily level, including performance by account, campaign, and advertiser. This information can be useful for analyzing campaign effectiveness, optimizing ad spend, and identifying trends or patterns in user engagement. Please note that additional columns can be manually specified if needed to further customize the report based on specific requirements.
