Weld logo
tiktok-ads logo
Tiktok ads

Ad Report

Generates an ad report by joining multiple tables from the TikTok Ads integration. It retrieves data such as impressions, clicks, spend, reach, conversions, likes, comments, shares, and various video metrics for each ad group. The report also includes information about the advertiser, campaign, account, currency, gender, audience type, and budget.
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
Example of output from model:
+------------+-----------+--------------+------------+-----------------+--------------+-----------------+-------+-----------------+----------+---------+----------------+-------------+-------------+-------------+---------------+-------------+--------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+
|  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.

Ready to start modeling your own tiktok-ads data?

Get started building your data warehouse with tiktok-ads and 100+ more apps and databases available.

tiktok-ads logo
Weld logo
Sign up for free
Weld logo

Tired of scattered data? Sync and analyze your data with AI in minutes. Connect to 150+ apps, files and databases.

Backed by leading investors
Frontline logoCherry logoInnnovation Fund logo
Twitter LogoLinkedIn Logo
GDPR logoSOC2
© 2024 Weld. All rights reserved.