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}} --   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
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 connect all your apps and databases.

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

Tired of scattered data? Sync your data in minutes with our AI-powered ETL platform. Seamlessly connect all your apps, files, and databases.

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