VK - Статистика по старому кабинету.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import requests\n",
    "from urllib.parse import urlparse, parse_qs\n",
    "\n",
    "#  Данные для авторизации - токен, версия API и номер рекламного кабинета\n",
    "token = 'токен'\n",
    "version = 5.131\n",
    "id_rk = айди_кабинета\n",
    "\n",
    "# Делаем первый запрос для получения данных по рекламным объявлениям с помощью метода getAds\n",
    "r = requests.get('https://api.vk.com/method/ads.getAds', params={\n",
    "    'access_token': token,\n",
    "    'v': version,\n",
    "    'account_id': id_rk\n",
    "})\n",
    "\n",
    "data = r.json()['response']\n",
    "\n",
    "# Создаим словарь с рекламными кампаниями и объявлениями\n",
    "ad_campaign_dict = {}\n",
    "for i in range(len(data)):\n",
    "    ad_campaign_dict[data[i]['id']] = data[i]['campaign_id']\n",
    "\n",
    "# Получим строку с айдишниками объявлений - нужно будет дальше\n",
    "unique_ad_ids = set(ad_id for ad_id in ad_campaign_dict.keys())\n",
    "ad_ids_string = ', '.join(str(ad_id) for ad_id in unique_ad_ids)\n",
    "\n",
    "chunk_size = 1000  # Максимальное количество объявлений в одном запросе - VK не принимает больше 2000\n",
    "ad_ids_list = [int(ad_id) for ad_id in unique_ad_ids]  # Преобразуем id объявлений в список целых чисел\n",
    "\n",
    "# Разбиваем список на мелкие подсписки\n",
    "ad_ids_chunks = [ad_ids_list[i:i + chunk_size] for i in range(0, len(ad_ids_list), chunk_size)]\n",
    "\n",
    "# Создаем список для хранения результатов\n",
    "result_df_list = []\n",
    "\n",
    "# Для каждой подсписки id объявлений делаем запрос и добавляем результат в список\n",
    "for ad_ids_chunk in ad_ids_chunks:\n",
    "    ad_ids_string_chunk = ', '.join(str(ad_id) for ad_id in ad_ids_chunk)\n",
    "    r33 = requests.get('https://api.vk.com/method/ads.getStatistics', params={\n",
    "        'access_token': token,\n",
    "        'v': version,\n",
    "        'account_id': id_rk,\n",
    "        'ids_type': 'ad',\n",
    "        'ids': ad_ids_string_chunk,\n",
    "        'period': 'day',\n",
    "        'date_from': '2023-07-01',\n",
    "        'date_to': '2023-07-31'\n",
    "    })\n",
    "    r33 = r33.json()['response']\n",
    "    result_df_list.append(pd.DataFrame(r33))\n",
    "\n",
    "# Объединяем результаты запросов в один датафрейм\n",
    "df = pd.concat(result_df_list, ignore_index=True)\n",
    "\n",
    "# Создаем пустой список для хранения разобранной статистики\n",
    "parsed_stats_list = []\n",
    "\n",
    "# Разбираем статистику для каждой записи в 'stats' и добавляем ее в список\n",
    "for _, row in df.iterrows():\n",
    "    stats = row['stats']\n",
    "    if stats:\n",
    "        parsed_stats_list.extend(stats)\n",
    "\n",
    "# Создаем новый датафрейм из списка разобранной статистики\n",
    "parsed_stats_df = pd.DataFrame(parsed_stats_list)\n",
    "\n",
    "# Объединяем новый датафрейм с исходным для сохранения информации об объявлениях\n",
    "df = pd.concat([df.drop(columns=['stats']), parsed_stats_df], axis=1)\n",
    "\n",
    "# Добавляем новую колонку 'campaign_id' к final_df\n",
    "df['campaign_id'] = df['id'].map(ad_campaign_dict)\n",
    "\n",
    "df = df.dropna(subset=['day'])\n",
    "\n",
    "df = df[['campaign_id', 'id', 'spent', 'impressions', 'clicks', 'reach', 'ctr', 'uniq_views_count', 'effective_cost_per_click', 'effective_cost_per_mille', 'join_rate', 'effective_cpf', 'link_external_clicks', 'conversions_external', 'conversion_count', 'conversion_cr', 'message_sends_by_any_user', 'effective_cost_per_message']]\n",
    "\n",
    "# Делаем второй запрос - для получения UTM-меток из креативов\n",
    "\n",
    "r2 = requests.get('https://api.vk.com/method/ads.getAdsLayout', params={\n",
    "    'access_token': token,\n",
    "    'v': version,\n",
    "    'account_id': id_rk,\n",
    "    'include_deleted': 0\n",
    "})\n",
    "\n",
    "data_ads_layout = r2.json()['response']\n",
    "data_ads_layout = pd.DataFrame(data_ads_layout)\n",
    "data_ads_layout = data_ads_layout[['id', 'campaign_id', 'link_url']]\n",
    "\n",
    "# Небольшая функция, которая распарсит ссылки в объявлениях\n",
    "def extract_utm_params(link_url):\n",
    "    parsed_url = urlparse(link_url)\n",
    "    utm_params = parse_qs(parsed_url.query)\n",
    "    return utm_params\n",
    "\n",
    "# Применить функцию к столбцу 'link_url'\n",
    "data_ads_layout['utm_params'] = data_ads_layout['link_url'].apply(extract_utm_params)\n",
    "\n",
    "# Извлечь значения UTM параметров в отдельные столбцы\n",
    "data_ads_layout['utm_source'] = data_ads_layout['utm_params'].apply(lambda x: x.get('utm_source', [None])[0])\n",
    "data_ads_layout['utm_medium'] = data_ads_layout['utm_params'].apply(lambda x: x.get('utm_medium', [None])[0])\n",
    "data_ads_layout['utm_campaign'] = data_ads_layout['utm_params'].apply(lambda x: x.get('utm_campaign', [None])[0])\n",
    "data_ads_layout['utm_id'] = data_ads_layout['utm_params'].apply(lambda x: x.get('utm_id', [None])[0])\n",
    "data_ads_layout['utm_content'] = data_ads_layout['utm_params'].apply(lambda x: x.get('utm_content', [None])[0])\n",
    "\n",
    "# Удалить столбец 'utm_params', если он больше не нужен\n",
    "data_ads_layout.drop('utm_params', axis=1, inplace=True)\n",
    "\n",
    "df = df.merge(data_ads_layout, left_on=['campaign_id', 'id'], right_on=['campaign_id', 'id'], how='left')\n",
    "\n",
    "# Делаем третий, вспомогательный запрос - с помощью метода getCampaigns получим человеческие названия кампаний\n",
    "\n",
    "r3 = requests.get('https://api.vk.com/method/ads.getCampaigns', params={\n",
    "    'access_token': token,\n",
    "    'v': version,\n",
    "    'account_id': id_rk,\n",
    "    'include_deleted': 0\n",
    "})\n",
    "data_campaigns = r3.json()['response']\n",
    "\n",
    "# Создать датафрейм из списка data2\n",
    "campaign_df = pd.DataFrame(data_campaigns)\n",
    "\n",
    "# Выбрать только столбцы 'id' и 'name'\n",
    "campaign_df = campaign_df[['id', 'name']]\n",
    "\n",
    "# Соединяем их с нашим основным датафреймом и убираем то, что нам не нужно\n",
    "df = df.merge(campaign_df, how='left', left_on='campaign_id', right_on='id')\n",
    "\n",
    "df = df[['name', \n",
    "         'campaign_id', \n",
    "         'utm_source', \n",
    "         'utm_medium', \n",
    "         'utm_campaign', \n",
    "         'utm_content', \n",
    "         'spent', \n",
    "         'impressions', \n",
    "         'clicks', \n",
    "         'reach', \n",
    "         'ctr', \n",
    "         'uniq_views_count', \n",
    "         'effective_cost_per_click', \n",
    "         'effective_cost_per_mille', \n",
    "         'join_rate', \n",
    "         'effective_cpf', \n",
    "         'link_external_clicks', \n",
    "         'conversions_external', \n",
    "         'conversion_count', \n",
    "         'conversion_cr', \n",
    "         'message_sends_by_any_user', \n",
    "         'effective_cost_per_message']]\n",
    "\n",
    "# Конвертация campaign_id в тип данных object\n",
    "df['campaign_id'] = df['campaign_id'].astype('object')\n",
    "\n",
    "# Конвертация spent и других в тип данных float64\n",
    "df['spent'] = df['spent'].astype('float64')\n",
    "df['ctr'] = df['ctr'].astype('float64')\n",
    "df['effective_cost_per_click'] = df['effective_cost_per_click'].astype('float64')\n",
    "df['effective_cost_per_mille'] = df['effective_cost_per_mille'].astype('float64')\n",
    "df['effective_cpf'] = df['effective_cpf'].astype('float64')\n",
    "df['conversion_cr'] = df['conversion_cr'].astype('float64')\n",
    "df['effective_cost_per_message'] = df['effective_cost_per_message'].astype('float64')\n",
    "\n",
    "# Заканчиваем уборку\n",
    "\n",
    "df[['spent', \n",
    "    'impressions', \n",
    "    'clicks', \n",
    "    'reach', \n",
    "    'ctr', \n",
    "    'uniq_views_count', \n",
    "    'effective_cost_per_click', \n",
    "    'effective_cost_per_mille', \n",
    "    'join_rate', \n",
    "    'effective_cpf', \n",
    "    'link_external_clicks', \n",
    "    'conversions_external', \n",
    "    'conversion_count', \n",
    "    'conversion_cr', \n",
    "    'message_sends_by_any_user', \n",
    "    'effective_cost_per_message']] = df[['spent', \n",
    "                                         'impressions', \n",
    "                                         'clicks', \n",
    "                                         'reach', \n",
    "                                         'ctr', \n",
    "                                         'uniq_views_count', \n",
    "                                         'effective_cost_per_click', \n",
    "                                         'effective_cost_per_mille', \n",
    "                                         'join_rate', \n",
    "                                         'effective_cpf', \n",
    "                                         'link_external_clicks', \n",
    "                                         'conversions_external', \n",
    "                                         'conversion_count', \n",
    "                                         'conversion_cr', \n",
    "                                         'message_sends_by_any_user', \n",
    "                                         'effective_cost_per_message']].fillna(0)\n",
    "\n",
    "df.head()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.3"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}