Bonjour, je m'appelle Masha, je travaille comme analyste marketing chez Ozon. Notre équipe « pythonite » et « escuelite » à toutes les mains et tous les pieds au profit de l'ensemble de la commercialisation de l'entreprise. L'une de mes responsabilités est de prendre en charge les analyses pour l'équipe de publicité display d'Ozon.
Les publicités display Ozon sont présentées sur différentes plateformes : Facebook, Google, MyTarget, TikTok et autres. Pour qu'une campagne publicitaire fonctionne efficacement, vous avez besoin d'analyses en temps réel. Cet article se concentrera sur mon expérience de collecte de données publicitaires à partir de la plate-forme TikTok sans intermédiaires et sans problèmes inutiles.
La tâche de collecter des statistiques: introduction
L'équipe de publicité display d'Ozon dispose d'un compte professionnel TikTok dans lequel elle gère toutes les publicités sur ce site. Ils ont enduré longtemps, ils ont eux-mêmes collecté des données auprès des bureaux de publicité, mais le moment est encore venu où il n'était plus possible de supporter. J'ai donc eu pour tâche d'automatiser la collecte de statistiques à partir de TikTok.
Nous avions déjà des données sur les commandes de campagnes de TikTok dans nos bases de données ; il n'y avait pas assez de données sur les coûts pour une analyse efficace.
, " 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: .