Tiktok ads
Ad stats by ad_group monthly
Generates monthly ad statistics by ad group for TikTok Ads integration, including clicks, conversions, conversion rate, impressions, CTR, CPM, likes, follows, reach, profile visits, shares, cost, cost per conversion, and CPC. It uses a left join to combine data from the ad group daily report and ad group tables.
1select
2 date_trunc(cast(stat_time_day as timestamp), month) month
3 , ad_group
4 , sum(clicks) as clicks
5 , sum(conversion) as conversions
6 , avg(conversion_rate) as avg_conversion_rate
7 , sum(impressions) as impressions
8 , sum(safe_divide(clicks, impressions)) as ctr
9 , sum(safe_divide(cost, impressions)) * 1000 as cpm
10 , sum(likes) as likes
11 , sum(follows) as follows
12 , sum(reach) as reach
13 , sum(profile_visits) as profile_visits
14 , sum(shares) as shares
15 , sum(spend) as cost
16 , sum(safe_divide(spend, conversion)) as cost_per_conversion
17 , sum(safe_divide(spend, clicks)) as cpc
18from
19 {{raw.tiktok_ads.ad_group_daily_report}} s
20 left join {{raw.tiktok_ad.ad_group}} a on s.ad_group_id = a.id
21group by
22 1
23 , 2Example of output from model:
+---------------------+----------------------+--------+-------------+----------------------+-------------+-----------+---------+-------+--------+----------------+--------+--------+----------+----------------------+---------+
| month | ad_group | clicks | conversions | avg_conversion_rate | impressions | ctr | cpm | likes | follows| reach | profile_visits| shares | cost | cost_per_conversion | cpc |
+---------------------+----------------------+--------+-------------+----------------------+-------------+-----------+---------+-------+--------+----------------+--------+--------+----------+----------------------+---------+
| 2021-01-01 00:00:00 | Fitness Videos | 1500 | 50 | 0.03 | 100000 | 1.5% | 10.00 | 100 | 20 | 50000 | 50 | 10 | 100.00 | 2.00 | 0.07 |
| 2021-01-01 00:00:00 | Beauty Videos | 2000 | 80 | 0.04 | 150000 | 1.33% | 12.00 | 150 | 30 | 75000 | 80 | 20 | 200.00 | 2.50 | 0.10 |
| 2021-01-01 00:00:00 | Gaming Videos | 1000 | 20 | 0.02 | 200000 | 0.50% | 15.00 | 50 | 10 | 25000 | 20 | 5 | 50.00 | 2.50 | 0.05 |
+---------------------+----------------------+--------+-------------+----------------------+-------------+-----------+---------+-------+--------+----------------+--------+--------+----------+----------------------+---------+This SQL model allows you to gather insights on your ad campaigns by grouping data by month and ad group. With this template, you can easily track important metrics such as clicks, conversions, impressions, and more. Additionally, you can calculate important ratios such as click-through rate (CTR), cost per thousand impressions (CPM), cost per conversion, and cost per click (CPC). By using this SQL template, you can gain a deeper understanding of your ad performance and make data-driven decisions to optimize your campaigns.
