Tiktok ads
Ad stats by ad monthly
Generates monthly ad statistics for TikTok ads, including clicks, conversions, impressions, conversion rate, CTR, CPM, likes, follows, reach, profile visits, shares, cost, cost per conversion, and CPC. It pulls data from the ad daily report and ad tables and groups the results by month and ad name.
1# Ad stats by ad monthly
2select
3 date_trunc(cast(stat_time_day as timestamp), month) month
4 , a.ad_name
5 , sum(clicks) as clicks
6 , sum(conversion) as conversions
7 , avg(conversion_rate) as avg_conversion_rate
8 , sum(impressions) as impressions
9 , sum(safe_divide(clicks, impressions)) as ctr
10 , sum(safe_divide(cost, impressions)) * 1000 as cpm
11 , sum(likes) as likes
12 , sum(follows) as follows
13 , sum(reach) as reach
14 , sum(profile_visits) as profile_visits
15 , sum(shares) as shares
16 , sum(spend) as cost
17 , sum(safe_divide(spend, conversion)) as cost_per_conversion
18 , sum(safe_divide(spend, clicks)) as cpc
19from
20 {{raw.tiktok_ad.ad_daily_report}} s
21 left join {{raw.tiktok_ads.ad}} a on s.ad_id = a.id
22group by
23 1
24 , 2Example of output from model:
+------------+------------------+--------+-------------+----------------------+-------------+--------+--------+-------+--------+---------+-----------------+--------+---------+----------------------+-----------------+
| month | ad_name | clicks | conversions | avg_conversion_rate | impressions | ctr | cpm | likes | follows | reach | profile_visits | shares | cost | cost_per_conversion | cpc |
+------------+------------------+--------+-------------+----------------------+-------------+--------+--------+-------+--------+---------+-----------------+--------+---------+----------------------+-----------------+
| 2021-01-01 | Awesome Ad 1 | 100 | 10 | 0.1 | 1000 | 0.1 | 10.0 | 50 | 20 | 500 | 100 | 5 | 100.0 | 10.0 | 1.0 |
| 2021-01-01 | Awesome Ad 2 | 200 | 20 | 0.1 | 2000 | 0.1 | 10.0 | 75 | 30 | 750 | 150 | 10 | 200.0 | 10.0 | 1.0 |
| 2021-01-01 | Awesome Ad 3 | 300 | 30 | 0.1 | 3000 | 0.1 | 10.0 | 100 | 40 | 1000 | 200 | 15 | 300.0 | 10.0 | 1.0 |
+------------+------------------+--------+-------------+----------------------+-------------+--------+--------+-------+--------+---------+-----------------+--------+---------+----------------------+-----------------+This SQL model allows you to gather insights on various metrics such as clicks, conversions, impressions, likes, follows, reach, profile visits, shares, and cost. By grouping the data by month and ad name, you can easily identify which ads are performing well and which ones need improvement. Additionally, the SQL code calculates the average conversion rate, click-through rate (CTR), cost per thousand impressions (CPM), cost per conversion, and cost per click (CPC). This information can be used to optimize your ad campaigns and maximize your return on investment (ROI). Overall, the Ad stats by ad monthly SQL template is an essential tool for any marketer looking to improve their TikTok ad performance.
