Weld logo
tiktok-ads logo
Tiktok ads

Account Report

Generates an account report for the TikTok Ads integration. It retrieves data from the `ad_daily_report`, `advertiser`, and `ad` tables, and joins them based on specific conditions. The resulting report includes aggregated metrics such as impressions, clicks, spend, reach, conversion, likes, comments, shares, profile visits, follows, video watched durations, and video view percentages.
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  , advertiser as (
12        select
13            *
14        from
15            {{raw.tiktok_ads.advertiser}}
16    )
17  , ads as (
18        select
19            *
20        from
21            {{raw.tiktok_ads.ad}}
22    )
23  , joined as (
24        select
25            date_trunc(
26                cast(cast(stat_time_day as timestamp) as date)
27              , day
28            ) date_day
29          , advertiser.id account_id
30          , advertiser.name account_name
31          , advertiser.currency
32          , sum(daily.impressions) as impressions
33          , sum(daily.clicks) as clicks
34          , sum(daily.spend) as spend
35          , sum(daily.reach) as reach
36          , sum(daily.conversion) as conversion
37          , sum(daily.likes) as likes
38          , sum(daily.comments) as comments
39          , sum(daily.shares) as shares
40          , sum(daily.profile_visits) as profile_visits
41          , sum(daily.follows) as follows
42          , sum(daily.video_watched_2s) as video_watched_2_s
43          , sum(daily.video_watched_6s) as video_watched_6_s
44          , sum(daily.video_views_p25) as video_views_p_25
45          , sum(daily.video_views_p50) as video_views_p_50
46          , sum(daily.video_views_p75) as video_views_p_75
47          , sum(safe_divide(spend, nullif(daily.clicks, 0))) as cpc
48          , sum(safe_divide(clicks, nullif(daily.impressions, 0))) * 100 as ctr
49          , sum(safe_divide(spend, nullif(daily.impressions, 0))) * 1000 as cpm
50            -- Additional pass-through columns should be manually specified if needed
51        from
52            daily
53            left join ads on daily.ad_id = ads.id
54            left join advertiser on ads.advertiser_id = advertiser.id
55        group by
56            1
57          , 2
58          , 3
59          , 4 -- Adjust these numbers according to the selected columns
60    )
61select
62    *
63from
64    joined
Example of output from model:

Generate a comprehensive report on advertising accounts from the TikTok Ads integration. This SQL model combines data from multiple tables, including the `ad_daily_report`, `advertiser`, and `ad` tables. The resulting report provides insights into various performance metrics for each advertising account. It includes data such as impressions, clicks, spend, reach, conversions, likes, comments, shares, profile visits, follows, video views, and more. Additionally, it calculates derived metrics like CPC (Cost Per Click), CTR (Click-Through Rate), and CPM (Cost Per Thousand Impressions). By executing this SQL template, you can gain valuable insights into the performance of TikTok Ads campaigns across different advertising accounts. It allows you to analyze key metrics and identify trends, optimize advertising strategies, and make data-driven decisions to maximize campaign effectiveness.

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
© 2024 Weld. All rights reserved.