Account Report

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