Clever Geek Handbook

Extraiga aún más datos: configure la recopilación de estadísticas de publicidad de TikTok por día

Hola, mi nombre es Masha, trabajo como analista de marketing en Ozon. Nuestro equipo "pythonite" y "escuelite" en todas las manos y pies en beneficio de todo el marketing de la empresa. Una de mis responsabilidades es respaldar el análisis del equipo de publicidad gráfica de Ozon.





Los anuncios gráficos de Ozon se presentan en diferentes plataformas: Facebook, Google, MyTarget, TikTok y otras. Para que cualquier campaña publicitaria funcione de manera eficaz, necesita análisis en tiempo real. Este artículo se centrará en mi experiencia en la recopilación de datos publicitarios de la plataforma TikTok sin intermediarios ni problemas innecesarios.





La tarea de recopilar estadísticas: introductoria

El equipo de anuncios gráficos de Ozon tiene una cuenta comercial de TikTok en la que administran todos los anuncios en ese sitio. Aguantaron durante mucho tiempo, ellos mismos recopilaron datos de las oficinas de publicidad, pero aún ha llegado el momento en que ya no era posible aguantar. Así que tengo la tarea de automatizar la recopilación de estadísticas de TikTok.





Ya teníamos datos sobre pedidos de campañas de TikTok en nuestras bases de datos; no había suficientes datos de costos para realizar análisis efectivos.





, " TikTok" " TikTok" :





  1. ,





  2. ,





  3. - ,





  4. , , .





.





-. 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 .





  1. Callback Address



    https://www.ozon.ru.





  2. Authorized URL



    , , -.





  3. , "Confirm".





  4. Ozon, url. https://www.ozon.ru/?auth_code=XXXXXXXXXXX



    .





  5. 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 . , .





  • TikTok Marketing API: ;





  • TikTok;





  • request: ;





  • six: ;





  • pandas: ;





  • sqlalchemy: .







More articles:


All Articles