Hi, my name is Masha, I work as a marketing analyst at Ozon. Our team "pythonite" and "escuelite" in all hands and feet for the benefit of the entire marketing of the company. One of my responsibilities is to support analytics for the Ozon display advertising team.
Ozon display ads are presented on different platforms: Facebook, Google, MyTarget, TikTok and others. For any advertising campaign to work effectively, you need real-time analytics. This article will focus on my experience of collecting advertising data from the TikTok platform without intermediaries and unnecessary troubles.
The task of collecting statistics: introductory
The Ozon display ad team has a TikTok business account in which they manage all ads on that site. They endured for a long time, they themselves collected data from advertising offices, but still the time has come when it was no longer possible to endure. So I got a task to automate the collection of statistics from TikTok.
We already had data on orders for campaigns from TikTok in our databases; there was not enough cost data for effective analytics.
, " TikTok" " TikTok" :
,
,
- ,
, , .
.
-. TikTok Marketing API, "My Apps", "Become a Developer", .
TikTok – Facebook, , , . , "What services do you provide?" "Reporting".
"Create App". .
, callback-address. , . , , "Reporting". ID . .
TikTok , . , .
, , . , – , : , .
-
, . web-, , - . Access Token, -.
, , , callback .
Callback Address
https://www.ozon.ru.
Authorized URL
, , -.
, "Confirm".
Ozon, url.
https://www.ozon.ru/?auth_code=XXXXXXXXXXX
.
auth_code
,secret
app_id
TikTok long-term Access Token.
curl -H "Content-Type:application/json" -X POST \
-d '{
"secret": "SECRET",
"app_id": "APP_ID",
"auth_code": "AUTH_CODE"
}' \
https://ads.tiktok.com/open_api/v1.2/oauth2/access_token
:
{
"message": "OK",
"code": 0,
"data": {
"access_token": "XXXXXXXXXXXXXXXXXXXX",
"scope": [4],
"advertiser_ids": [
1111111111111111111,
2222222222222222222]
},
"request_id": "XXXXXXXXXXXXXXX"
}
long-term Access Token , Ozon. auth_code
– 10 .
access_token
, . access_token
, , , -.
advertiser_ids
, – ID -.
, !
TikTok, , depricated, .
, , :
access_token
,
advertiser_ids
.
, .
media source -> campaign -> adset -> ad_name |
media source
, – TikTok. API TikTok.
, . TikTok . , , , ; – , 30 . , .
: AUCTION RESERVATION. Ozon AUCTION .
: , , . :
METRICS = [
"campaign_name", #
"adgroup_name", #
"ad_name", #
"spend", # ( )
"impressions", #
"clicks", #
"reach", # ,
"video_views_p25", # 25%
"video_views_p50", # 50%
"video_views_p75", # 75%
"video_views_p100", # 100%
"frequency" #
]
TikTok API Java, Python, PHP curl-. Python .
TikTok :
pip install requests pip install six
requests
get-. six
url- .
, , :
pip install pandas pip install sqlalchemy
SQL- , pandas
DataFrame sqlalchemy
DataFrame .
TikTok url .
# url args
def build_url(args: dict) -> str:
query_string = urlencode({k: v if isinstance(v, string_types) else json.dumps(v) for k, v in args.items()})
scheme = "https"
netloc = "ads.tiktok.com"
path = "/open_api/v1.1/reports/integrated/get/"
return urlunparse((scheme, netloc, path, "", query_string, ""))
# TikTok Marketing API,
# json
def get(args: dict, access_token: str) -> dict:
url = build_url(args)
headers = {
"Access-Token": access_token,
}
rsp = requests.get(url, headers=headers)
return rsp.json()
get
access token. :
args = {
"metrics": METRICS, # ,
"data_level": "AUCTION_AD", #
"start_date": 'YYYY-MM-DD', #
"end_date": 'YYYY-MM-DD', #
"page_size": 1000, # - ,
"page": 1, # ( , )
"advertiser_id": advertiser_id, # ID advertiser_ids, access token
"report_type": "BASIC", #
"dimensions": ["ad_id", "stat_time_day"] # ,
}
page_size
: . TikTok – 1000. , . .
get
.
{
#
"message": "OK",
"code": 0,
"data": {
#
"page_info": {
#
"total_number": 3000,
#
"page": 1,
#
"page_size": 1000,
#
"total_page": 3
},
#
"list": [
#
{
#
"metrics": {
"video_views_p25": "0",
"video_views_p100": "0",
"adgroup_name": "adgroup_name",
"reach": "0",
"spend": "0.0",
"frequency": "0.0",
"video_views_p75": "0",
"video_views_p50": "0",
"ad_name": "ad_name",
"campaign_name": "campaign_name",
"impressions": "0",
"clicks": "0"
},
# ( )
"dimensions": {
"stat_time_day": "YYYY-MM-DD HH: mm: ss",
"ad_id": 111111111111111
}
},
...
]
},
# id
"request_id": "11111111111111111111111"
}
, 1000 , . total_page
, , . , .
page = 1 #
result_dict = {} # ,
result = get(args, access_token) #
result_dict[advertiser_id] = result['data']['list'] #
# page
# result
while page < result['data']['page_info']['total_page']:
# 1
page += 1
#
args['page'] = page
# page
result = get(args, access_token)
#
result_dict[advertiser_id] += result['data']['list']
advertiser_ids
.
. pandas.DataFrame
.
# DataFrame,
data_df = pd.DataFrame()
#
for adv_id in advertiser_ids:
#
adv_input_list = result_dict[adv_id]
#
adv_result_list = []
#
for adv_input_row in adv_input_list:
#
metrics = adv_input_row['metrics']
#
metrics.update(adv_input_row['dimensions'])
#
adv_result_list.append(metrics)
# DataFrame
result_df = pd.DataFrame(adv_result_list)
# id
result_df['account'] = adv_id
# DataFrame
data_df = data_df.append(
result_df,
ignore_index=True
)
#
#
#
#
# DataFrame
data_df.to_sql(
schema=schema,
name=table,
con=connection,
if_exists = 'append',
index = False
)
TikTok , , , , . Facebook, ( ).
, TikTok .
.
#
import json
from datetime import datetime
from datetime import timedelta
import requests
from six import string_types
from six.moves.urllib.parse import urlencode
from six.moves.urllib.parse import urlunparse
import pandas as pd
import sqlalchemy
# url args
def build_url(args: dict) -> str:
query_string = urlencode({k: v if isinstance(v, string_types) else json.dumps(v) for k, v in args.items()})
scheme = "https"
netloc = "ads.tiktok.com"
path = "/open_api/v1.1/reports/integrated/get/"
return urlunparse((scheme, netloc, path, "", query_string, ""))
# TikTok Marketing API,
# json
def get(args: dict, access_token: str) -> dict:
url = build_url(args)
headers = {
"Access-Token": access_token,
}
rsp = requests.get(url, headers=headers)
return rsp.json()
#
# (, start_date end_date, [start_date, end_date])
def update_tiktik_data(
# API TikTok
tiktok_conn: dict,
#
db_conn: dict,
# id
advertiser_ids: list,
# :
start_date:datetime=None,
# :
end_date:datetime=None
):
access_token = tiktok_conn['password']
start_date = datetime.now() - timedelta(7) if start_date is None else start_date
end_date = datetime.now() - timedelta(1) if end_date is None else end_date
START_DATE = datetime.strftime(start_date, '%Y-%m-%d')
END_DATE = datetime.strftime(end_date, '%Y-%m-%d')
SCHEMA = "schema"
TABLE = "table"
PAGE_SIZE = 1000
METRICS = [
"campaign_name", #
"adgroup_name", #
"ad_name", #
"spend", # ( )
"impressions", #
"clicks", #
"reach", # ,
"video_views_p25", # 25%
"video_views_p50", # 50%
"video_views_p75", # 75%
"video_views_p100", # 100%
"frequency" #
]
result_dict = {} # ,
for advertiser_id in advertiser_ids:
page = 1 #
args = {
"metrics": METRICS, # ,
"data_level": "AUCTION_AD", #
"start_date": START_DATE, #
"end_date": END_DATE, #
"page_size": PAGE_SIZE, # - ,
"page": 1, # ( , )
"advertiser_id": advertiser_id, # ID advertiser_ids, access token
"report_type": "BASIC", #
"dimensions": ["ad_id", "stat_time_day"] # ,
}
result = get(args, access_token) #
result_dict[advertiser_id] = result['data']['list'] #
# page ,
# result
while page < result['data']['page_info']['total_page']:
# 1
page += 1
#
args['page'] = page
# page
result = get(args, access_token)
#
result_dict[advertiser_id] += result['data']['list']
# DataFrame,
data_df = pd.DataFrame()
#
for adv_id in advertiser_ids:
#
adv_input_list = result_dict[adv_id]
#
adv_result_list = []
#
for adv_input_row in adv_input_list:
#
metrics = adv_input_row['metrics']
#
metrics.update(adv_input_row['dimensions'])
#
adv_result_list.append(metrics)
# DataFrame
result_df = pd.DataFrame(adv_result_list)
# id
result_df['account'] = adv_id
# DataFrame
data_df = data_df.append(
result_df,
ignore_index=True
)
#
#
#
#
#
connection = sqlalchemy.create_engine(
'{db_type}://{user}:{pswd}@{host}:{port}/{path}'.format(
db_type=db_conn['db_type'],
user=db_conn['user'],
pswd=db_conn['password'],
host=db_conn['host'],
port=db_conn['port'],
path=db_conn['path']
)
)
#
with connection.connect() as conn:
conn.execute(f"""delete from {SCHEMA}.{TABLE}
where date >= '{START_DATE}' and date <= '{END_DATE}'""")
# DataFrame
data_df.to_sql(
schema=SCHEMA,
name=TABLE,
con=connection,
if_exists = 'append',
index = False
)
!
, ( , ). , , API TikTok , .
, Facebook , , , , .. ETL , Permission Denied , – " ".
, Facebook TikTok : , . , TikTok Marketing API . , .
-
-
request: ;
six: ;
pandas: ;
sqlalchemy: .