Tiktok ads
Ad stats by campaign monthly
Generates monthly ad statistics by campaign for TikTok Ads, including metrics such as clicks, conversions, impressions, and cost per conversion. It pulls data from the campaign daily report and campaign tables and groups the results by month and campaign name.
1select
2 date_trunc(cast(stat_time_day as timestamp), month) month
3 , a.campaign_name
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.campaign_daily_report}} s
20 left join {{raw.tiktok_ads.campaign}} a on s.campaign_id = a.id
21group by
22 1
23 , 2Example of output from model:
+---------------------+----------------------+--------+-------------+----------------------+-------------+-----------+---------+-------+---------+----------------+--------+--------+---------+---------------------+-----------+
| month | campaign_name | clicks | conversions | avg_conversion_rate | impressions | ctr | cpm | likes | follows | reach | profile_visits | shares | cost | cost_per_conversion | cpc |
+---------------------+----------------------+--------+-------------+----------------------+-------------+-----------+---------+-------+---------+----------------+--------+--------+---------+---------------------+-----------+
| 2022-01-01 00:00:00 | Advertise Now | 1000 | 200 | 0.20 | 100000 | 0.01 | 10.00 | 500 | 100 | 50000 | 1000 | 50 | 1000 | 5.00 | 1.00 |
| 2022-01-01 00:00:00 | Brand Boost | 1500 | 300 | 0.20 | 150000 | 0.01 | 15.00 | 750 | 150 | 75000 | 1500 | 75 | 1500 | 5.00 | 1.00 |
| 2022-01-01 00:00:00 | Market Mover | 2000 | 400 | 0.20 | 200000 | 0.01 | 20.00 | 1000 | 200 | 100000 | 2000 | 100 | 2000 | 5.00 | 1.00 |
| 2022-01-01 00:00:00 | Campaign Connect | 2500 | 500 | 0.20 | 250000 | 0.01 | 25.00 | 1250 | 250 | 125000 | 2500 | 125 | 2500 | 5.00 | 1.00 |
+---------------------+----------------------+--------+-------------+----------------------+-------------+-----------+---------+-------+---------+----------------+--------+--------+---------+---------------------+-----------+This SQL model integrates with TikTok-ads and allows users to select a range of metrics including clicks, conversions, impressions, likes, follows, reach, profile visits, shares, and cost. The output is grouped by month and campaign name, providing a clear picture of how each campaign is performing over time. This SQL template is particularly useful for marketers and advertisers who want to track the success of their TikTok ad campaigns and gain insights into which campaigns are performing well and which ones need improvement. With this information, users can make data-driven decisions to optimize their ad spend and improve their overall ROI.
