Ad Report
1with
2 daily as (
3 select
4 *
5 from
6 {{raw.tiktok_ads.ad_daily_report}} -- to join another tiktok ads account
7 -- union all
8 -- select * from {{}}
9 )
10 , ads as (
11 select
12 *
13 from
14 {{raw.tiktok_ads.ad}}
15 )
16 , ad_groups as (
17 select
18 *
19 from
20 {{raw.tiktok_ads.ad_group}}
21 )
22 , advertiser as (
23 select
24 *
25 from
26 {{raw.tiktok_ads.advertiser}}
27 )
28 , campaigns as (
29 select
30 *
31 from
32 {{raw.tiktok_ads.campaign}}
33 )
34select
35 date_trunc(
36 cast(cast(stat_time_day as timestamp) as date)
37 , day
38 ) date_day
39 , ad_groups.advertiser_id account_id
40 , advertiser.name account_name
41 , campaigns.id campaign_id
42 , campaigns.campaign_name
43 , ad_groups.id ad_group_id
44 , ad_groups.adgroup_name ad_group_name
45 , daily.ad_id
46 , ads.ad_name
47 , advertiser.currency
48 , ad_groups.gender
49 , ad_groups.audience_type
50 , ad_groups.budget
51 , daily.impressions as impressions
52 , daily.clicks as clicks
53 , daily.spend as spend
54 , daily.reach as reach
55 , daily.conversion as conversion
56 , daily.total_purchase_value as conversion_value
57 , daily.likes as likes
58 , daily.comments as comments
59 , daily.shares as shares
60 , daily.profile_visits as profile_visits
61 , daily.follows as follows
62 , daily.video_watched_2s as video_watched_2_s
63 , daily.video_watched_6s as video_watched_6_s
64 , daily.video_views_p25 as video_views_p_25
65 , daily.video_views_p50 as video_views_p_50
66 , daily.video_views_p75 as video_views_p_75
67from
68 daily
69 left join ads on daily.ad_id = ads.id
70 left join ad_groups on ads.ad_group_id = ad_groups.id
71 left join advertiser on ads.advertiser_id = advertiser.id
72 left join campaigns on ads.campaign_id = campaigns.id+------------+-----------+--------------+------------+-----------------+--------------+-----------------+-------+-----------------+----------+---------+----------------+-------------+-------------+-------------+---------------+-------------+--------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+
| 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.
