{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Matching Ads data with Orchard Dimension Data\n", "\n", "The following code takes an output file from Datorama of all ad campaign data and utilizes ows-search to identify matches with orchard artists and releases.\n", "\n", "Input \"campaign_all_by_day.csv\" \n", "Output \"campaign_data_with_orchard_dimension_data.csv\" \n", "\n", "Files are located in the S3 bucket: data-science-events-streams (orchard dev account) \n", "You will need to ensure you have properly configured your machine with aws an config.\n", "\n", "Since we are searching ows-search, this notebook needs to be executed while in the orchard's network." ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [], "source": [ "from sqlalchemy import create_engine, Table, MetaData, Column, String\n", "from sqlalchemy.sql import select, text\n", "from snowflake.sqlalchemy import URL\n", "from pprint import pprint as print\n", "import requests\n", "import urllib\n", "\n", "\n", "metadata = MetaData()\n", "\n", "engine = create_engine(URL(\n", " user='seggensperger',\n", " password='',\n", " account='orchard',\n", " database='dev_engineering',\n", " schema='events_streams',\n", " role='dev_engineering',\n", " warehouse='dev_performance_warehouse'\n", "))" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [], "source": [ "import boto3\n", "import pandas as pd\n", "import numpy as np\n", "import matplotlib\n", "import matplotlib.pyplot as plt\n", "%matplotlib inline\n", "matplotlib.style.use('ggplot')\n", "\n", "s3 = boto3.client('s3')\n", "\n", "events_streams_bucket = 'data-science-events-streams'\n", "ads_csv = 'ad_campaign_all_by_day.csv'" ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [], "source": [ "# # Upload local file (downloaded from datorama) to S3\n", "\n", "# # dated file\n", "# s3.upload_file('./es_campaign_all.csv', events_streams_bucket, 'es_campaign_all_by_day_20181212.csv')\n", "# # working file\n", "# s3.upload_file('./es_campaign_all.csv', events_streams_bucket, 'ad_campaign_all_by_day.csv')" ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "/Users/seggensperger/anaconda3/lib/python3.6/site-packages/IPython/core/interactiveshell.py:2728: DtypeWarning: Columns (5) have mixed types. Specify dtype option on import or set low_memory=False.\n", " interactivity=interactivity, compiler=compiler, result=result)\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Campaign NameCampaign AdvertiserCampaign Advertiser IDCampaign Billing CodeCampaign BudgetCampaign CategoryCampaign End DateCampaign KeyCampaign Start DateCampaign Status...Unique EventsTotal EventsTime On Site TotalTime On Site Avg.Page ViewsPage ExitsAdded FansFans (Daily)Fans (Lifetime)Shares
0NaNNaNNaNNaNNaNNaNNaTNaNNaTNaN...NaNNaNNaNNaNNaNNaNNaNNaN10142.0NaN
1Diboh || Stadig Mig - FB IG Out Now Website Cl...NaNNaNNaNNaNNaNNaT6112443716093NaTACTIVE...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
2Sinead Harnett Lessons YT Video ViewsOrchard Legacy Account359624072555226866810.03596240725NaT1652637062NaTNaN...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
3Hannah Kerr - Christmas Even In Bethlehem YT V...NaNNaNNaNNaNNaNNaT1619369734NaTNaN...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
4L.A Gray || Better Days - FB IG Website ClicksNaNNaNNaNNaNNaNNaT6112356789493NaTPAUSED...NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
\n", "

5 rows × 62 columns

\n", "
" ], "text/plain": [ " Campaign Name Campaign Advertiser \\\n", "0 NaN NaN \n", "1 Diboh || Stadig Mig - FB IG Out Now Website Cl... NaN \n", "2 Sinead Harnett Lessons YT Video Views Orchard Legacy Account \n", "3 Hannah Kerr - Christmas Even In Bethlehem YT V... NaN \n", "4 L.A Gray || Better Days - FB IG Website Clicks NaN \n", "\n", " Campaign Advertiser ID Campaign Billing Code Campaign Budget \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 3596240725 5522686681 0.0 \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " Campaign Category Campaign End Date Campaign Key Campaign Start Date \\\n", "0 NaN NaT NaN NaT \n", "1 NaN NaT 6112443716093 NaT \n", "2 3596240725 NaT 1652637062 NaT \n", "3 NaN NaT 1619369734 NaT \n", "4 NaN NaT 6112356789493 NaT \n", "\n", " Campaign Status ... Unique Events Total Events Time On Site Total \\\n", "0 NaN ... NaN NaN NaN \n", "1 ACTIVE ... NaN NaN NaN \n", "2 NaN ... NaN NaN NaN \n", "3 NaN ... NaN NaN NaN \n", "4 PAUSED ... NaN NaN NaN \n", "\n", " Time On Site Avg. Page Views Page Exits Added Fans Fans (Daily) \\\n", "0 NaN NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN NaN \n", "\n", " Fans (Lifetime) Shares \n", "0 10142.0 NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN \n", "\n", "[5 rows x 62 columns]" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Retrieve file from s3\n", "obj = s3.get_object(Bucket=events_streams_bucket, Key=ads_csv)\n", "\n", "import_ads_df = pd.read_csv(\n", " obj['Body'], \n", " dtype={'Campaign Advertiser ID': str}, \n", " parse_dates=['Campaign Start Date', 'Campaign End Date', 'Day'])\n", "\n", "\n", "import_ads_df.head()" ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Campaign KeyCampaign NameDayImpact Score 2.0
0NaNNaN2018-12-120.000000
16112443716093Diboh || Stadig Mig - FB IG Out Now Website Cl...2018-12-1127.615833
21652637062Sinead Harnett Lessons YT Video Views2018-12-110.000000
31619369734Hannah Kerr - Christmas Even In Bethlehem YT V...2018-12-11100.700439
46112356789493L.A Gray || Better Days - FB IG Website Clicks2018-12-11179.642355
\n", "
" ], "text/plain": [ " Campaign Key Campaign Name \\\n", "0 NaN NaN \n", "1 6112443716093 Diboh || Stadig Mig - FB IG Out Now Website Cl... \n", "2 1652637062 Sinead Harnett Lessons YT Video Views \n", "3 1619369734 Hannah Kerr - Christmas Even In Bethlehem YT V... \n", "4 6112356789493 L.A Gray || Better Days - FB IG Website Clicks \n", "\n", " Day Impact Score 2.0 \n", "0 2018-12-12 0.000000 \n", "1 2018-12-11 27.615833 \n", "2 2018-12-11 0.000000 \n", "3 2018-12-11 100.700439 \n", "4 2018-12-11 179.642355 " ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Specify Column Names to keep from original DF\n", "new_cols = [\n", " 'Campaign Key',\n", " 'Campaign Name',\n", " 'Day',\n", " 'Impact Score 2.0'\n", "]\n", "\n", "# Drop all but chosen columns\n", "ads_df = import_ads_df[new_cols]\n", "ads_df.head()" ] }, { "cell_type": "code", "execution_count": 22, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
campaign_keycampaign_namedaycampaign_impact
0NaNNaN2018-12-120.000000
16112443716093Diboh || Stadig Mig - FB IG Out Now Website Cl...2018-12-1127.615833
21652637062Sinead Harnett Lessons YT Video Views2018-12-110.000000
31619369734Hannah Kerr - Christmas Even In Bethlehem YT V...2018-12-11100.700439
46112356789493L.A Gray || Better Days - FB IG Website Clicks2018-12-11179.642355
\n", "
" ], "text/plain": [ " campaign_key campaign_name \\\n", "0 NaN NaN \n", "1 6112443716093 Diboh || Stadig Mig - FB IG Out Now Website Cl... \n", "2 1652637062 Sinead Harnett Lessons YT Video Views \n", "3 1619369734 Hannah Kerr - Christmas Even In Bethlehem YT V... \n", "4 6112356789493 L.A Gray || Better Days - FB IG Website Clicks \n", "\n", " day campaign_impact \n", "0 2018-12-12 0.000000 \n", "1 2018-12-11 27.615833 \n", "2 2018-12-11 0.000000 \n", "3 2018-12-11 100.700439 \n", "4 2018-12-11 179.642355 " ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ads_df = ads_df.rename(\n", " index=str, \n", " columns={\n", " 'Campaign Key': 'campaign_key', \n", " 'Campaign Name': 'campaign_name', \n", " 'Day': 'day',\n", " 'Impact Score 2.0': 'campaign_impact'\n", " })\n", "ads_df.head()" ] }, { "cell_type": "code", "execution_count": 23, "metadata": {}, "outputs": [], "source": [ "# at this point save the raw data to a sql table to be joined to campaign meta data once parsed\n", "ads_df.to_sql(name='ad_campaigns_by_day', con=engine, if_exists='replace', index=False, chunksize=1000)" ] }, { "cell_type": "code", "execution_count": 24, "metadata": {}, "outputs": [], "source": [ "# Aggregate ads data Campaign creating a summary object with unique campaign key\n", "def campaign_agg(x):\n", " \"\"\"Custom aggregation function for campaign Data\"\"\"\n", " names = {\n", " 'campaign_key': x['campaign_key'].iloc[0],\n", " 'campaign_name': x['campaign_name'].iloc[0],\n", " 'campaign_start': x['day'].min(),\n", " 'campaign_end': x['day'].max()\n", " }\n", "\n", " return pd.Series(names, index=['campaign_key', 'campaign_name', 'campaign_start', 'campaign_end'])\n", "\n", "\n", "ads_df_agg = ads_df.groupby('campaign_key').apply(campaign_agg)" ] }, { "cell_type": "code", "execution_count": 25, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
campaign_keycampaign_namecampaign_startcampaign_end
campaign_key
\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1\"Deck - Cacife Clandestino (Sub-genre: Brazili...\"Deck - Cacife Clandestino (Sub-genre: Brazili...2018-10-032018-10-03
\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop...\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop...2018-11-172018-11-17
(not set)(not set)(not set)2016-03-072017-05-29
01f155b7-b8ae-42e7-bf74-fab06b6af79f01f155b7-b8ae-42e7-bf74-fab06b6af79fAventura NY - Romeo Tour Campaign – Campaign2018-02-152018-02-18
02wvj9802wvj98Fred The Godson - Special Love Banners (1301-0...2018-08-142018-08-21
\n", "
" ], "text/plain": [ " campaign_key \\\n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilia... \"Deck - Cacife Clandestino (Sub-genre: Brazili... \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop,... \"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop... \n", "(not set) (not set) \n", "01f155b7-b8ae-42e7-bf74-fab06b6af79f 01f155b7-b8ae-42e7-bf74-fab06b6af79f \n", "02wvj98 02wvj98 \n", "\n", " campaign_name \\\n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilia... \"Deck - Cacife Clandestino (Sub-genre: Brazili... \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop,... \"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop... \n", "(not set) (not set) \n", "01f155b7-b8ae-42e7-bf74-fab06b6af79f Aventura NY - Romeo Tour Campaign – Campaign \n", "02wvj98 Fred The Godson - Special Love Banners (1301-0... \n", "\n", " campaign_start campaign_end \n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilia... 2018-10-03 2018-10-03 \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop,... 2018-11-17 2018-11-17 \n", "(not set) 2016-03-07 2017-05-29 \n", "01f155b7-b8ae-42e7-bf74-fab06b6af79f 2018-02-15 2018-02-18 \n", "02wvj98 2018-08-14 2018-08-21 " ] }, "execution_count": 25, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# drop first row as it's nothing\n", "ads_df_agg = ads_df_agg.drop(ads_df_agg.index[[0]])\n", "ads_df_agg.head()" ] }, { "cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
campaign_keycampaign_namecampaign_startcampaign_endcampaign_duration
campaign_key
\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1\"Deck - Cacife Clandestino (Sub-genre: Brazili...\"Deck - Cacife Clandestino (Sub-genre: Brazili...2018-10-032018-10-030 days
\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop...\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop...2018-11-172018-11-170 days
(not set)(not set)(not set)2016-03-072017-05-29448 days
01f155b7-b8ae-42e7-bf74-fab06b6af79f01f155b7-b8ae-42e7-bf74-fab06b6af79fAventura NY - Romeo Tour Campaign – Campaign2018-02-152018-02-183 days
02wvj9802wvj98Fred The Godson - Special Love Banners (1301-0...2018-08-142018-08-217 days
\n", "
" ], "text/plain": [ " campaign_key \\\n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilia... \"Deck - Cacife Clandestino (Sub-genre: Brazili... \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop,... \"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop... \n", "(not set) (not set) \n", "01f155b7-b8ae-42e7-bf74-fab06b6af79f 01f155b7-b8ae-42e7-bf74-fab06b6af79f \n", "02wvj98 02wvj98 \n", "\n", " campaign_name \\\n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilia... \"Deck - Cacife Clandestino (Sub-genre: Brazili... \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop,... \"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop... \n", "(not set) (not set) \n", "01f155b7-b8ae-42e7-bf74-fab06b6af79f Aventura NY - Romeo Tour Campaign – Campaign \n", "02wvj98 Fred The Godson - Special Love Banners (1301-0... \n", "\n", " campaign_start \\\n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilia... 2018-10-03 \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop,... 2018-11-17 \n", "(not set) 2016-03-07 \n", "01f155b7-b8ae-42e7-bf74-fab06b6af79f 2018-02-15 \n", "02wvj98 2018-08-14 \n", "\n", " campaign_end \\\n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilia... 2018-10-03 \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop,... 2018-11-17 \n", "(not set) 2017-05-29 \n", "01f155b7-b8ae-42e7-bf74-fab06b6af79f 2018-02-18 \n", "02wvj98 2018-08-21 \n", "\n", " campaign_duration \n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilia... 0 days \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop,... 0 days \n", "(not set) 448 days \n", "01f155b7-b8ae-42e7-bf74-fab06b6af79f 3 days \n", "02wvj98 7 days " ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ads_df_agg['campaign_duration'] = ads_df_agg['campaign_end'] - ads_df_agg['campaign_start']\n", "ads_df_agg.head()" ] }, { "cell_type": "code", "execution_count": 30, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "count 7828\n", "mean 8 days 11:52:49.545222\n", "std 29 days 22:44:27.000885\n", "min 0 days 00:00:00\n", "25% 0 days 00:00:00\n", "50% 3 days 00:00:00\n", "75% 7 days 00:00:00\n", "max 951 days 00:00:00\n", "Name: campaign_duration, dtype: object" ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "campaign_duration = ads_df_agg['campaign_duration']\n", "campaign_duration.describe()" ] }, { "cell_type": "code", "execution_count": 31, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "count 7440\n", "mean 4 days 07:22:03.870967\n", "std 5 days 11:25:01.687540\n", "min 0 days 00:00:00\n", "25% 0 days 00:00:00\n", "50% 3 days 00:00:00\n", "75% 7 days 00:00:00\n", "max 30 days 00:00:00\n", "Name: campaign_duration, dtype: object" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "campaign_duration = campaign_duration[campaign_duration < pd.Timedelta(days=31)]\n", "campaign_duration.describe()" ] }, { "cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" }, { "data": { "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYAAAAD8CAYAAAB+UHOxAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMS4yLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvNQv5yAAAFBNJREFUeJzt3X9oVff9x/HXuUnVZne5vT8yJanCovWPDEOUG9pKa1K87UZXJLNBEFzpulXK7SYYNmZGaQtWuVCzOEHpqCWs/csiNSt8/xBuxYQtjF5JYofSGqUd3TSJuec28abKTHK+f7hd6hKbm5vk3tzzeT7+ag7n3vN5eyDPnHN/1HIcxxEAwDieQi8AAFAYBAAADEUAAMBQBAAADEUAAMBQBAAADEUAAMBQBAAADEUAAMBQBAAADFVa6AXM5urVqzk/NhQKaWRkZAFXU1hum0dy30xum0dy30xum0eaPlNlZWVWj+MKAAAMRQAAwFAEAAAMRQAAwFAEAAAMRQAAwFAEAAAMRQAAwFAEAAAMteQ/CTwfQz/ZnNV+JW9/uMgrAYClhysAADAUAQAAQxEAADAUAQAAQxEAADAUAQAAQxEAADAUAQAAQxEAADAUAQAAQxEAADAUAQAAQxEAADAUAQAAQxEAADAUAQAAQxEAADAUAQAAQxEAADAUAQAAQxEAADAUAQAAQ5XOtsPIyIiOHj2qr776SpZlKRKJ6Omnn1Y6nVZ7e7uuX7+uiooK7d27V16vV47jqKOjQ319fVq+fLmi0aiqq6slSWfPntUHH3wgSdq+fbsaGxsXdTgAwL3NGoCSkhL99Kc/VXV1tW7evKl9+/aptrZWZ8+e1YYNG9TU1KTOzk51dnZq165d6uvr0+DgoI4cOaKBgQEdP35cBw8eVDqd1smTJxWLxSRJ+/btUzgcltfrXfQhAQDTzXoLyO/3Z/6Cv//++1VVVSXbtpVIJNTQ0CBJamhoUCKRkCSdO3dOW7ZskWVZWr9+vcbHx5VKpdTf36/a2lp5vV55vV7V1taqv79/EUcDAHybWa8Avml4eFiff/651q1bp9HRUfn9fkl3IjE2NiZJsm1boVAo85hgMCjbtmXbtoLBYGZ7IBCQbdvTjhGPxxWPxyVJsVjsrueaq6Es95vPMfKptLS0aNaaLbfN5LZ5JPfN5LZ5pNxnyjoAt27dUltbm55//nmVlZXdcz/HcaZtsyxrxn1n2h6JRBSJRDI/j4yMZLvEnOXjGAshFAoVzVqz5baZ3DaP5L6Z3DaPNH2mysrKrB6X1buAJiYm1NbWpscff1wPP/ywJMnn8ymVSkmSUqmUysvLJd35i/+bC0kmk/L7/QoEAkomk5nttm1nriAAAPk3awAcx9Fbb72lqqoqPfPMM5nt4XBYXV1dkqSuri7V19dntnd3d8txHF26dEllZWXy+/2qq6vT+fPnlU6nlU6ndf78edXV1S3SWACA2cx6C+izzz5Td3e31qxZo9/85jeSpJ07d6qpqUnt7e06c+aMQqGQWlpaJEkbN25Ub2+v9uzZo2XLlikajUqSvF6vnn32WbW2tkqSmpubeQcQABSQ5cx0034JuXr1as6PnXxxW1b7lbz9Yc7HyCcT7l0WO7fNI7lvJrfNIy3yawAAAPchAABgKAIAAIYiAABgKAIAAIYiAABgKAIAAIYiAABgKAIAAIYiAABgKAIAAIYiAABgKAIAAIYiAABgKAIAAIYiAABgKAIAAIYiAABgKAIAAIYiAABgKAIAAIYiAABgKAIAAIYiAABgKAIAAIYiAABgKAIAAIYiAABgKAIAAIYiAABgKAIAAIYiAABgKAIAAIYiAABgKAIAAIYiAABgqNLZdjh27Jh6e3vl8/nU1tYmSXr//ff10Ucfqby8XJK0c+dObdq0SZJ06tQpnTlzRh6PRz/72c9UV1cnServ71dHR4empqa0detWNTU1LdZMAIAszBqAxsZG/ehHP9LRo0fv2v7jH/9Y27Ztu2vbP//5T/X09Oj3v/+9UqmU9u/frz/84Q+SpHfeeUevvPKKgsGgWltbFQ6H9eCDDy7gKACAuZg1ADU1NRoeHs7qyRKJhDZv3qz77rtP3/ve97Rq1SpdvnxZkrRq1SqtXLlSkrR582YlEgkCAAAFNGsA7uX06dPq7u5WdXW1nnvuOXm9Xtm2rYceeiizTyAQkG3bkqRgMJjZHgwGNTAwMOPzxuNxxeNxSVIsFlMoFMp1iRrKcr/5HCOfSktLi2at2XLbTG6bR3LfTG6bR8p9ppwC8NRTT6m5uVmSdOLECb377ruKRqNyHGfG/WfablnWjPtGIhFFIpHMzyMjI7kscU7ycYyFEAqFimat2XLbTG6bR3LfTG6bR5o+U2VlZVaPy+ldQA888IA8Ho88Ho+2bt2qK1euSLrzl30ymczsZ9u2AoHAtO3JZFJ+vz+XQwMAFkhOAUilUpn//vjjj7V69WpJUjgcVk9Pj27fvq3h4WFdu3ZN69at09q1a3Xt2jUNDw9rYmJCPT09CofDCzMBACAns94COnz4sC5evKgbN27opZde0o4dO3ThwgV98cUXsixLFRUV2r17tyRp9erVevTRR9XS0iKPx6Of//zn8njuNOaFF17QgQMHNDU1pSeeeCITDQBAYVjOvW7cLxFXr17N+bGTL26bfSdJJW9/mPMx8smEe5fFzm3zSO6byW3zSHl+DQAAUPwIAAAYigAAgKEIAAAYigAAgKEIAAAYigAAgKEIAAAYigAAgKEIAAAYigAAgKEIAAAYigAAgKEIAAAYigAAgKEIAAAYigAAgKEIAAAYigAAgKEIAAAYigAAgKEIAAAYigAAgKEIAAAYigAAgKEIAAAYigAAgKEIAAAYigAAgKEIAAAYigAAgKEIAAAYigAAgKEIAAAYigAAgKEIAAAYqnS2HY4dO6be3l75fD61tbVJktLptNrb23X9+nVVVFRo79698nq9chxHHR0d6uvr0/LlyxWNRlVdXS1JOnv2rD744ANJ0vbt29XY2Lh4UwEAZjXrFUBjY6N+97vf3bWts7NTGzZs0JEjR7RhwwZ1dnZKkvr6+jQ4OKgjR45o9+7dOn78uKQ7wTh58qQOHjyogwcP6uTJk0qn04swDgAgW7MGoKamRl6v965tiURCDQ0NkqSGhgYlEglJ0rlz57RlyxZZlqX169drfHxcqVRK/f39qq2tldfrldfrVW1trfr7+xdhHABAtma9BTST0dFR+f1+SZLf79fY2JgkybZthUKhzH7BYFC2bcu2bQWDwcz2QCAg27ZnfO54PK54PC5JisVidz3fXA1lud98jpFPpaWlRbPWbLltJrfNI7lvJrfNI+U+U04BuBfHcaZtsyxrxn3vtT0SiSgSiWR+HhkZWZjFfYt8HGMhhEKhollrttw2k9vmkdw3k9vmkabPVFlZmdXjcnoXkM/nUyqVkiSlUimVl5dLuvMX/zcXkUwm5ff7FQgElEwmM9tt285cQQAACiOnAITDYXV1dUmSurq6VF9fn9ne3d0tx3F06dIllZWVye/3q66uTufPn1c6nVY6ndb58+dVV1e3cFMAAOZs1ltAhw8f1sWLF3Xjxg299NJL2rFjh5qamtTe3q4zZ84oFAqppaVFkrRx40b19vZqz549WrZsmaLRqCTJ6/Xq2WefVWtrqySpubl52gvLAID8spyZbtwvIVevXs35sZMvbstqv5K3P8z5GPlkwr3LYue2eST3zeS2eaQ8vwYAACh+BAAADEUAAMBQC/o5ABSfbF8nkYrntRIA2eEKAAAMRQAAwFAEAAAMRQAAwFAEAAAMRQAAwFAEAAAMRQAAwFAEAAAMRQAAwFAEAAAMRQAAwFB8Gdwi4UvWACx1XAEAgKEIAAAYigAAgKEIAAAYigAAgKEIAAAYigAAgKEIAAAYigAAgKEIAAAYigAAgKEIAAAYigAAgKH4NtA5msu3fALAUsYVAAAYigAAgKEIAAAYigAAgKEIAAAYal7vAnr55Ze1YsUKeTwelZSUKBaLKZ1Oq729XdevX1dFRYX27t0rr9crx3HU0dGhvr4+LV++XNFoVNXV1Qs1R1HL+p1Fp3oWdyEAjDLvt4G+9tprKi8vz/zc2dmpDRs2qKmpSZ2dners7NSuXbvU19enwcFBHTlyRAMDAzp+/LgOHjw438MDAHK04J8DSCQSev311yVJDQ0Nev3117Vr1y6dO3dOW7ZskWVZWr9+vcbHx5VKpeT3+xd6CXPGe/sBmGjeAThw4IAk6cknn1QkEtHo6Gjml7rf79fY2JgkybZthUKhzOOCwaBs214SAQAAE80rAPv371cgENDo6KjeeOMNVVZW3nNfx3GmbbMsa9q2eDyueDwuSYrFYndFY66Gcn7k0lRaWjqvf4+ZzOXfaKGPLS3OTIXktnkk983ktnmk3GeaVwACgYAkyefzqb6+XpcvX5bP58vc2kmlUpnXB4LBoEZGRjKPTSaTM/71H4lEFIlEMj9/8zGmm5iYKOi/x2IcOxQKueocu20eyX0zuW0eafpM3/bH+Dfl/DbQW7du6ebNm5n//uSTT7RmzRqFw2F1dXVJkrq6ulRfXy9JCofD6u7uluM4unTpksrKyrj9AwAFlPMVwOjoqA4dOiRJmpyc1GOPPaa6ujqtXbtW7e3tOnPmjEKhkFpaWiRJGzduVG9vr/bs2aNly5YpGo0uzAQAgJzkHICVK1fqzTffnLb9u9/9rl599dVp2y3L0i9+8YtcDwcAWGB8EhgADMX/D8Cl+GwDgNlwBQAAhuIKoIgM/WRzoZcAwEW4AgAAQxEAADAUAQAAQxEAADAUAQAAQxEAADAUAQAAQxEAADAUAQAAQxEAADAUAQAAQxEAADAUAQAAQxEAADAUAQAAQxEAADAUAQAAQxEAADAUAQAAQxEAADAUAQAAQxEAADAUAQAAQxEAADAUAQAAQ5UWegEoHpMvbstqv5K3P1zklQBYCAQACy7bUEiSTvUs3kIAfCsCAGQh66gRNBQRAoCCGvrJ5qz2W4zbSnO6UgFciBeBAcBQBAAADMUtIBSFudyu4V1IZuJ1mrnLewD6+/vV0dGhqakpbd26VU1NTfleAlzO5Hv7vFV36VtK5yivAZiamtI777yjV155RcFgUK2trQqHw3rwwQfzuQxg0WT7ojaKw1L6Zb0Y8hqAy5cva9WqVVq5cqUkafPmzUokEgQAyLO5XCUNZbkf79QqPnkNgG3bCgaDmZ+DwaAGBgbyuQQAi6RYflkvxlVascz+v/IaAMdxpm2zLOuun+PxuOLxuCQpFoupsrIy9wP+37ncHwsARSSX35V5fRtoMBhUMpnM/JxMJuX3++/aJxKJKBaLKRaLzft4+/btm/dzLCVum0dy30xum0dy30xum0fKfaa8BmDt2rW6du2ahoeHNTExoZ6eHoXD4XwuAQDwH3m9BVRSUqIXXnhBBw4c0NTUlJ544gmtXr06n0sAAPxH3j8HsGnTJm3atCkvx4pEInk5Tr64bR7JfTO5bR7JfTO5bR4p95ksZ6ZXZgEArsd3AQGAoVz5XUBu/LqJl19+WStWrJDH41FJScmCvEsqn44dO6be3l75fD61tbVJktLptNrb23X9+nVVVFRo79698nq9BV5p9maa6f3339dHH32k8vJySdLOnTvzdstzvkZGRnT06FF99dVXsixLkUhETz/9dNGep3vNU8zn6N///rdee+01TUxMaHJyUo888oh27Nih4eFhHT58WOl0Wt///vf1q1/9SqWlWfx6d1xmcnLS+eUvf+kMDg46t2/fdn796187X375ZaGXNW/RaNQZHR0t9DJyduHCBefKlStOS0tLZtt7773nnDp1ynEcxzl16pTz3nvvFWp5OZlpphMnTjh//vOfC7iq3Nm27Vy5csVxHMf5+uuvnT179jhffvll0Z6ne81TzOdoamrKuXnzpuM4jnP79m2ntbXV+eyzz5y2tjbnL3/5i+M4jvPHP/7ROX36dFbP57pbQN/8uonS0tLM102gsGpqaqb91ZhIJNTQ0CBJamhoKLrzNNNMxczv96u6ulqSdP/996uqqkq2bRftebrXPMXMsiytWLFCkjQ5OanJyUlZlqULFy7okUcekSQ1NjZmfY5cdwvIzV83ceDAAUnSk08+6Yp3MoyOjmY+COj3+zU2NlbgFS2M06dPq7u7W9XV1XruueeKMhLDw8P6/PPPtW7dOlecp2/O8+mnnxb1OZqamtJvf/tbDQ4O6oc//KFWrlypsrIylZSUSJICgUDWoXNdAJwsvm6iGO3fv1+BQECjo6N64403VFlZqZqamkIvC//jqaeeUnNzsyTpxIkTevfddxWNRgu8qrm5deuW2tra9Pzzz6usrKzQy5m3/52n2M+Rx+PRm2++qfHxcR06dEj/+te/cn+uBVzXkpDN100Uo0AgIEny+Xyqr6/X5cuXC7yi+fP5fEqlUpKkVCqVeVGumD3wwAPyeDzyeDzaunWrrly5UuglzcnExITa2tr0+OOP6+GHH5ZU3OdppnmK/Rz913e+8x3V1NRoYGBAX3/9tSYnJyXduQvy398Xs3FdANz4dRO3bt3SzZs3M//9ySefaM2aNQVe1fyFw2F1dXVJkrq6ulRfX1/gFc3ff39RStLHH39cVJ90dxxHb731lqqqqvTMM89kthfrebrXPMV8jsbGxjQ+Pi7pzjuC/v73v6uqqko/+MEP9Le//U2SdPbs2ax/57nyg2C9vb3605/+lPm6ie3btxd6SfMyNDSkQ4cOSbrzws9jjz1WdDMdPnxYFy9e1I0bN+Tz+bRjxw7V19ervb1dIyMjCoVCamlpKap7sTPNdOHCBX3xxReyLEsVFRXavXt30VyBfvrpp3r11Ve1Zs2azG3TnTt36qGHHirK83Svef76178W7Tn6xz/+oaNHj2pqakqO4+jRRx9Vc3OzhoaGpr0N9L777pv1+VwZAADA7Fx3CwgAkB0CAACGIgAAYCgCAACGIgAAYCgCAACGIgAAYCgCAACG+n9MUPhrK0fZVwAAAABJRU5ErkJggg==\n", "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# ads_df_agg.hist(column='Campaign Duration')\n", "(campaign_duration / pd.Timedelta(days=1)).hist(bins=range(0, 30, 1))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "How many of these are actually zero? \n", "What is the typical case for when a duration is zero? Are these real ads?\n" ] }, { "cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
campaign_keycampaign_namecampaign_startcampaign_endcampaign_duration
campaign_key
\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1\"Deck - Cacife Clandestino (Sub-genre: Brazili...\"Deck - Cacife Clandestino (Sub-genre: Brazili...2018-10-032018-10-030 days
\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop...\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop...2018-11-172018-11-170 days
10200405561020040556Black Honey DIG Out Now (4023-00-000-0000)2017-12-232017-12-230 days
10202200011020220001Employed To Serve Kerrang AOTY (4023-00-000-0000)2017-12-232017-12-230 days
10465407921046540792Sean Christopher Carry On Phase 2 YT Out Now (...2018-01-262018-01-260 days
13576988681357698868Brandon Severs 'Chameleon' YT Discovery Video ...2018-04-272018-04-270 days
13825304891382530489ASN Inga Glass YT Out Now2018-05-092018-05-090 days
14570332501457033250Same Plate - 91 Million YT Video Views (1301-0...2018-06-292018-06-290 days
15237042721523704272Night Flight - Roulette Video Promo (4023-00-0...2018-08-242018-08-240 days
15237049211523704921Pancho Uresti 'Marihuano Enamorado' - YT Video...2018-08-312018-08-310 days
15277197011527719701The Daw Wall - Google Search Ads2018-08-242018-08-240 days
15994307041599430704Be Svendsen Out Now (1415-00-000-12051)2018-10-212018-10-210 days
16345360731634536073Mondo Marcio - Dio Del Rap - views2018-11-202018-11-200 days
16389078261638907826Jorja Smith - Ina's Nacht Performance (Pre sho...2018-11-242018-11-240 days
16531590901653159090Maverick Sabre - Her Grace (1415-00-000-00000)2018-12-112018-12-110 days
186672703186672703triple j -- Hottest 100 Volume 23 OUT NOW Vide...2016-03-012016-03-010 days
190597783190597783Drowners - On Desire OUT NOW Search (6202-00-0...2016-07-012016-07-010 days
2384266247729058423842662477290584Big & Rich - Did It For The Party FB IG Pre-Or...2017-09-122017-09-120 days
2384301492898027423843014928980274Michael Jackson Cine 20182018-10-242018-10-240 days
3pi8rzju3pi8rzjuNightwish - Endless Forms Most Beautiful Out N...2016-06-072016-06-070 days
4hvz94hvz9Radial Branding: Narco Corridos Website Clicks...2016-04-012016-04-010 days
4hwfy4hwfyRadial Branding: New Nordic Sounds Website Cli...2016-04-012016-04-010 days
4xj6b4xj6bCharles Bradley - Conan Appearance - Video Web...2016-05-102016-05-100 days
51x2751x27Quick promote · Make it a…2016-05-242016-05-240 days
535lq535lqQuick promote · RT We're coming…2016-05-282016-05-280 days
53h7e53h7eQuick promote · The secret's out…2016-05-302016-05-300 days
57fz557fz5Quick promote · See the Music…2016-06-112016-06-110 days
5evmm5evmmQuick promote · I'm super excited…2016-07-082016-07-080 days
5ftym5ftymQuick promote · I'm super excited…2016-07-112016-07-110 days
5grln5grlnQuick promote · See the Music…2016-07-132016-07-130 days
..................
die ärzte_ThisIsPlaylist_Desktop Overlay_Genre:Rock_CH_Label_Overlaydie ärzte_ThisIsPlaylist_Desktop Overlay_Genre...die ärzte_ThisIsPlaylist_Desktop Overlay_Genre...2018-11-152018-11-150 days
die ärzte_ThisIsPlaylist_Desktop Overlay_Genre:Rock_DE_Label_Overlaydie ärzte_ThisIsPlaylist_Desktop Overlay_Genre...die ärzte_ThisIsPlaylist_Desktop Overlay_Genre...2018-11-152018-11-150 days
die ärzte_ThisIsPlaylist_Leaderboard_AT_Label_728x90die ärzte_ThisIsPlaylist_Leaderboard_AT_Label_...die ärzte_ThisIsPlaylist_Leaderboard_AT_Label_...2018-11-152018-11-150 days
die ärzte_ThisIsPlaylist_Leaderboard_DE_Label_728x90die ärzte_ThisIsPlaylist_Leaderboard_DE_Label_...die ärzte_ThisIsPlaylist_Leaderboard_DE_Label_...2018-11-302018-11-300 days
die ärzte_ThisIsPlaylist_Leaderboard_Genre:Rock_AT_Label_728x90die ärzte_ThisIsPlaylist_Leaderboard_Genre:Roc...die ärzte_ThisIsPlaylist_Leaderboard_Genre:Roc...2018-11-302018-11-300 days
die ärzte_ThisIsPlaylist_Leaderboard_Genre:Rock_CH_Label_728x90die ärzte_ThisIsPlaylist_Leaderboard_Genre:Roc...die ärzte_ThisIsPlaylist_Leaderboard_Genre:Roc...2018-11-152018-11-150 days
die ärzte_ThisIsPlaylist_Leaderboard_Genre:Rock_DE_Label_728x90die ärzte_ThisIsPlaylist_Leaderboard_Genre:Roc...die ärzte_ThisIsPlaylist_Leaderboard_Genre:Roc...2018-11-152018-11-150 days
die ärzte_ThisIsPlaylist_Mobile Overlay_AT_Label_Mobile_Overlaydie ärzte_ThisIsPlaylist_Mobile Overlay_AT_Lab...die ärzte_ThisIsPlaylist_Mobile Overlay_AT_Lab...2018-11-152018-11-150 days
die ärzte_ThisIsPlaylist_Mobile Overlay_Genre:Rock_CH_Label_Mobile_Overlaydie ärzte_ThisIsPlaylist_Mobile Overlay_Genre:...die ärzte_ThisIsPlaylist_Mobile Overlay_Genre:...2018-11-152018-11-150 days
die ärzte_ThisIsPlaylist_Mobile Overlay_Genre:Rock_DE_Label_Mobile_Overlaydie ärzte_ThisIsPlaylist_Mobile Overlay_Genre:...die ärzte_ThisIsPlaylist_Mobile Overlay_Genre:...2018-11-152018-11-150 days
dodie _If I'm Being Honest_Genre_Pop_GB_Label_728x90dodie _If I'm Being Honest_Genre_Pop_GB_Label_...dodie _If I'm Being Honest_Genre_Pop_GB_Label_...2018-11-222018-11-220 days
dodie _If I'm Being Honest_Genre_Pop_IE_Label_728x90dodie _If I'm Being Honest_Genre_Pop_IE_Label_...dodie _If I'm Being Honest_Genre_Pop_IE_Label_...2018-11-212018-11-210 days
dodie _If I'm Being Honest_RTPL_Chill_GB_Label_Overlay_1dodie _If I'm Being Honest_RTPL_Chill_GB_Label...dodie _If I'm Being Honest_RTPL_Chill_GB_Label...2018-11-222018-11-220 days
dodie _If I'm Being Honest_RTPL_Chill_IE_Label_Overlaydodie _If I'm Being Honest_RTPL_Chill_IE_Label...dodie _If I'm Being Honest_RTPL_Chill_IE_Label...2018-11-212018-11-210 days
hgifqrkchgifqrkcAbove and Beyond Out Now Banner Ads (1415-00-0...2016-06-102016-06-100 days
lfprgurnlfprgurnExodus - Blood In Blood Out Out Now Banner Ads...2016-04-252016-04-250 days
lowlow - Il Bambino Soldato _IT_Label_Audio_Everywhere_3lowlow - Il Bambino Soldato _IT_Label_Audio_Ev...lowlow - Il Bambino Soldato _IT_Label_Audio_Ev...2018-06-072018-06-070 days
lowlow - Il Bambino Soldato _IT_Label_Audio_Playstation_3lowlow - Il Bambino Soldato _IT_Label_Audio_Pl...lowlow - Il Bambino Soldato _IT_Label_Audio_Pl...2018-06-072018-06-070 days
lowlow - Sfoghi di una vita complicata 4_IT_Label_728x90lowlow - Sfoghi di una vita complicata 4_IT_La...lowlow - Sfoghi di una vita complicata 4_IT_La...2018-05-222018-05-220 days
lowlow - Sfoghi di una vita complicata 4_IT_Label_Audio_Everywherelowlow - Sfoghi di una vita complicata 4_IT_La...lowlow - Sfoghi di una vita complicata 4_IT_La...2018-05-222018-05-220 days
lowlow - Sfoghi di una vita complicata 4_IT_Label_Audio_Playstationlowlow - Sfoghi di una vita complicata 4_IT_La...lowlow - Sfoghi di una vita complicata 4_IT_La...2018-05-222018-05-220 days
n2me9uaqn2me9uaqSFS & Michael Tilson Thomas - Tchaikovsky: Sym...2016-06-102016-06-100 days
n2mwofgn2mwofgDior and I - General Premiere HTML5 Banner Ads...2016-06-102016-06-100 days
o379qa1ko379qa1kCody Simpson - Free Pre-Order Banners (4023-00...2016-06-102016-06-100 days
pnm95r15pnm95r15SFS & Michael Tilson Thomas - Tchaikovsky: Sym...2016-06-092016-06-090 days
svz53jisvz53jiNBA Champions 2017 Golden State Warriros - Out...2017-08-162017-08-160 days
w25trcoyw25trcoyToonstone - Out Now Banners (1415-00-000-23528)2016-06-092016-06-090 days
wof0wof0FOLLOW US AND WATCH \"TLSID\" http://youtu.be/Qj...2018-06-222018-06-220 days
x8fs08fx8fs08fIn Motion Out Now Premium Banner Inventory Ads...2016-02-092016-02-090 days
zdsx2afzdsx2afKelsea Ballerini - Unapologetically OUT NOW CM...2018-08-232018-08-230 days
\n", "

2293 rows × 5 columns

\n", "
" ], "text/plain": [ " campaign_key \\\n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilia... \"Deck - Cacife Clandestino (Sub-genre: Brazili... \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop,... \"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop... \n", "1020040556 1020040556 \n", "1020220001 1020220001 \n", "1046540792 1046540792 \n", "1357698868 1357698868 \n", "1382530489 1382530489 \n", "1457033250 1457033250 \n", "1523704272 1523704272 \n", "1523704921 1523704921 \n", "1527719701 1527719701 \n", "1599430704 1599430704 \n", "1634536073 1634536073 \n", "1638907826 1638907826 \n", "1653159090 1653159090 \n", "186672703 186672703 \n", "190597783 190597783 \n", "23842662477290584 23842662477290584 \n", "23843014928980274 23843014928980274 \n", "3pi8rzju 3pi8rzju \n", "4hvz9 4hvz9 \n", "4hwfy 4hwfy \n", "4xj6b 4xj6b \n", "51x27 51x27 \n", "535lq 535lq \n", "53h7e 53h7e \n", "57fz5 57fz5 \n", "5evmm 5evmm \n", "5ftym 5ftym \n", "5grln 5grln \n", "... ... \n", "die ärzte_ThisIsPlaylist_Desktop Overlay_Genre:... die ärzte_ThisIsPlaylist_Desktop Overlay_Genre... \n", "die ärzte_ThisIsPlaylist_Desktop Overlay_Genre:... die ärzte_ThisIsPlaylist_Desktop Overlay_Genre... \n", "die ärzte_ThisIsPlaylist_Leaderboard_AT_Label_7... die ärzte_ThisIsPlaylist_Leaderboard_AT_Label_... \n", "die ärzte_ThisIsPlaylist_Leaderboard_DE_Label_7... die ärzte_ThisIsPlaylist_Leaderboard_DE_Label_... \n", "die ärzte_ThisIsPlaylist_Leaderboard_Genre:Rock... die ärzte_ThisIsPlaylist_Leaderboard_Genre:Roc... \n", "die ärzte_ThisIsPlaylist_Leaderboard_Genre:Rock... die ärzte_ThisIsPlaylist_Leaderboard_Genre:Roc... \n", "die ärzte_ThisIsPlaylist_Leaderboard_Genre:Rock... die ärzte_ThisIsPlaylist_Leaderboard_Genre:Roc... \n", "die ärzte_ThisIsPlaylist_Mobile Overlay_AT_Labe... die ärzte_ThisIsPlaylist_Mobile Overlay_AT_Lab... \n", "die ärzte_ThisIsPlaylist_Mobile Overlay_Genre:R... die ärzte_ThisIsPlaylist_Mobile Overlay_Genre:... \n", "die ärzte_ThisIsPlaylist_Mobile Overlay_Genre:R... die ärzte_ThisIsPlaylist_Mobile Overlay_Genre:... \n", "dodie _If I'm Being Honest_Genre_Pop_GB_Label_7... dodie _If I'm Being Honest_Genre_Pop_GB_Label_... \n", "dodie _If I'm Being Honest_Genre_Pop_IE_Label_7... dodie _If I'm Being Honest_Genre_Pop_IE_Label_... \n", "dodie _If I'm Being Honest_RTPL_Chill_GB_Label_... dodie _If I'm Being Honest_RTPL_Chill_GB_Label... \n", "dodie _If I'm Being Honest_RTPL_Chill_IE_Label_... dodie _If I'm Being Honest_RTPL_Chill_IE_Label... \n", "hgifqrkc hgifqrkc \n", "lfprgurn lfprgurn \n", "lowlow - Il Bambino Soldato _IT_Label_Audio_Eve... lowlow - Il Bambino Soldato _IT_Label_Audio_Ev... \n", "lowlow - Il Bambino Soldato _IT_Label_Audio_Pla... lowlow - Il Bambino Soldato _IT_Label_Audio_Pl... \n", "lowlow - Sfoghi di una vita complicata 4_IT_Lab... lowlow - Sfoghi di una vita complicata 4_IT_La... \n", "lowlow - Sfoghi di una vita complicata 4_IT_Lab... lowlow - Sfoghi di una vita complicata 4_IT_La... \n", "lowlow - Sfoghi di una vita complicata 4_IT_Lab... lowlow - Sfoghi di una vita complicata 4_IT_La... \n", "n2me9uaq n2me9uaq \n", "n2mwofg n2mwofg \n", "o379qa1k o379qa1k \n", "pnm95r15 pnm95r15 \n", "svz53ji svz53ji \n", "w25trcoy w25trcoy \n", "wof0 wof0 \n", "x8fs08f x8fs08f \n", "zdsx2af zdsx2af \n", "\n", " campaign_name \\\n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilia... \"Deck - Cacife Clandestino (Sub-genre: Brazili... \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop,... \"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop... \n", "1020040556 Black Honey DIG Out Now (4023-00-000-0000) \n", "1020220001 Employed To Serve Kerrang AOTY (4023-00-000-0000) \n", "1046540792 Sean Christopher Carry On Phase 2 YT Out Now (... \n", "1357698868 Brandon Severs 'Chameleon' YT Discovery Video ... \n", "1382530489 ASN Inga Glass YT Out Now \n", "1457033250 Same Plate - 91 Million YT Video Views (1301-0... \n", "1523704272 Night Flight - Roulette Video Promo (4023-00-0... \n", "1523704921 Pancho Uresti 'Marihuano Enamorado' - YT Video... \n", "1527719701 The Daw Wall - Google Search Ads \n", "1599430704 Be Svendsen Out Now (1415-00-000-12051) \n", "1634536073 Mondo Marcio - Dio Del Rap - views \n", "1638907826 Jorja Smith - Ina's Nacht Performance (Pre sho... \n", "1653159090 Maverick Sabre - Her Grace (1415-00-000-00000) \n", "186672703 triple j -- Hottest 100 Volume 23 OUT NOW Vide... \n", "190597783 Drowners - On Desire OUT NOW Search (6202-00-0... \n", "23842662477290584 Big & Rich - Did It For The Party FB IG Pre-Or... \n", "23843014928980274 Michael Jackson Cine 2018 \n", "3pi8rzju Nightwish - Endless Forms Most Beautiful Out N... \n", "4hvz9 Radial Branding: Narco Corridos Website Clicks... \n", "4hwfy Radial Branding: New Nordic Sounds Website Cli... \n", "4xj6b Charles Bradley - Conan Appearance - Video Web... \n", "51x27 Quick promote · Make it a… \n", "535lq Quick promote · RT We're coming… \n", "53h7e Quick promote · The secret's out… \n", "57fz5 Quick promote · See the Music… \n", "5evmm Quick promote · I'm super excited… \n", "5ftym Quick promote · I'm super excited… \n", "5grln Quick promote · See the Music… \n", "... ... \n", "die ärzte_ThisIsPlaylist_Desktop Overlay_Genre:... die ärzte_ThisIsPlaylist_Desktop Overlay_Genre... \n", "die ärzte_ThisIsPlaylist_Desktop Overlay_Genre:... die ärzte_ThisIsPlaylist_Desktop Overlay_Genre... \n", "die ärzte_ThisIsPlaylist_Leaderboard_AT_Label_7... die ärzte_ThisIsPlaylist_Leaderboard_AT_Label_... \n", "die ärzte_ThisIsPlaylist_Leaderboard_DE_Label_7... die ärzte_ThisIsPlaylist_Leaderboard_DE_Label_... \n", "die ärzte_ThisIsPlaylist_Leaderboard_Genre:Rock... die ärzte_ThisIsPlaylist_Leaderboard_Genre:Roc... \n", "die ärzte_ThisIsPlaylist_Leaderboard_Genre:Rock... die ärzte_ThisIsPlaylist_Leaderboard_Genre:Roc... \n", "die ärzte_ThisIsPlaylist_Leaderboard_Genre:Rock... die ärzte_ThisIsPlaylist_Leaderboard_Genre:Roc... \n", "die ärzte_ThisIsPlaylist_Mobile Overlay_AT_Labe... die ärzte_ThisIsPlaylist_Mobile Overlay_AT_Lab... \n", "die ärzte_ThisIsPlaylist_Mobile Overlay_Genre:R... die ärzte_ThisIsPlaylist_Mobile Overlay_Genre:... \n", "die ärzte_ThisIsPlaylist_Mobile Overlay_Genre:R... die ärzte_ThisIsPlaylist_Mobile Overlay_Genre:... \n", "dodie _If I'm Being Honest_Genre_Pop_GB_Label_7... dodie _If I'm Being Honest_Genre_Pop_GB_Label_... \n", "dodie _If I'm Being Honest_Genre_Pop_IE_Label_7... dodie _If I'm Being Honest_Genre_Pop_IE_Label_... \n", "dodie _If I'm Being Honest_RTPL_Chill_GB_Label_... dodie _If I'm Being Honest_RTPL_Chill_GB_Label... \n", "dodie _If I'm Being Honest_RTPL_Chill_IE_Label_... dodie _If I'm Being Honest_RTPL_Chill_IE_Label... \n", "hgifqrkc Above and Beyond Out Now Banner Ads (1415-00-0... \n", "lfprgurn Exodus - Blood In Blood Out Out Now Banner Ads... \n", "lowlow - Il Bambino Soldato _IT_Label_Audio_Eve... lowlow - Il Bambino Soldato _IT_Label_Audio_Ev... \n", "lowlow - Il Bambino Soldato _IT_Label_Audio_Pla... lowlow - Il Bambino Soldato _IT_Label_Audio_Pl... \n", "lowlow - Sfoghi di una vita complicata 4_IT_Lab... lowlow - Sfoghi di una vita complicata 4_IT_La... \n", "lowlow - Sfoghi di una vita complicata 4_IT_Lab... lowlow - Sfoghi di una vita complicata 4_IT_La... \n", "lowlow - Sfoghi di una vita complicata 4_IT_Lab... lowlow - Sfoghi di una vita complicata 4_IT_La... \n", "n2me9uaq SFS & Michael Tilson Thomas - Tchaikovsky: Sym... \n", "n2mwofg Dior and I - General Premiere HTML5 Banner Ads... \n", "o379qa1k Cody Simpson - Free Pre-Order Banners (4023-00... \n", "pnm95r15 SFS & Michael Tilson Thomas - Tchaikovsky: Sym... \n", "svz53ji NBA Champions 2017 Golden State Warriros - Out... \n", "w25trcoy Toonstone - Out Now Banners (1415-00-000-23528) \n", "wof0 FOLLOW US AND WATCH \"TLSID\" http://youtu.be/Qj... \n", "x8fs08f In Motion Out Now Premium Banner Inventory Ads... \n", "zdsx2af Kelsea Ballerini - Unapologetically OUT NOW CM... \n", "\n", " campaign_start \\\n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilia... 2018-10-03 \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop,... 2018-11-17 \n", "1020040556 2017-12-23 \n", "1020220001 2017-12-23 \n", "1046540792 2018-01-26 \n", "1357698868 2018-04-27 \n", "1382530489 2018-05-09 \n", "1457033250 2018-06-29 \n", "1523704272 2018-08-24 \n", "1523704921 2018-08-31 \n", "1527719701 2018-08-24 \n", "1599430704 2018-10-21 \n", "1634536073 2018-11-20 \n", "1638907826 2018-11-24 \n", "1653159090 2018-12-11 \n", "186672703 2016-03-01 \n", "190597783 2016-07-01 \n", "23842662477290584 2017-09-12 \n", "23843014928980274 2018-10-24 \n", "3pi8rzju 2016-06-07 \n", "4hvz9 2016-04-01 \n", "4hwfy 2016-04-01 \n", "4xj6b 2016-05-10 \n", "51x27 2016-05-24 \n", "535lq 2016-05-28 \n", "53h7e 2016-05-30 \n", "57fz5 2016-06-11 \n", "5evmm 2016-07-08 \n", "5ftym 2016-07-11 \n", "5grln 2016-07-13 \n", "... ... \n", "die ärzte_ThisIsPlaylist_Desktop Overlay_Genre:... 2018-11-15 \n", "die ärzte_ThisIsPlaylist_Desktop Overlay_Genre:... 2018-11-15 \n", "die ärzte_ThisIsPlaylist_Leaderboard_AT_Label_7... 2018-11-15 \n", "die ärzte_ThisIsPlaylist_Leaderboard_DE_Label_7... 2018-11-30 \n", "die ärzte_ThisIsPlaylist_Leaderboard_Genre:Rock... 2018-11-30 \n", "die ärzte_ThisIsPlaylist_Leaderboard_Genre:Rock... 2018-11-15 \n", "die ärzte_ThisIsPlaylist_Leaderboard_Genre:Rock... 2018-11-15 \n", "die ärzte_ThisIsPlaylist_Mobile Overlay_AT_Labe... 2018-11-15 \n", "die ärzte_ThisIsPlaylist_Mobile Overlay_Genre:R... 2018-11-15 \n", "die ärzte_ThisIsPlaylist_Mobile Overlay_Genre:R... 2018-11-15 \n", "dodie _If I'm Being Honest_Genre_Pop_GB_Label_7... 2018-11-22 \n", "dodie _If I'm Being Honest_Genre_Pop_IE_Label_7... 2018-11-21 \n", "dodie _If I'm Being Honest_RTPL_Chill_GB_Label_... 2018-11-22 \n", "dodie _If I'm Being Honest_RTPL_Chill_IE_Label_... 2018-11-21 \n", "hgifqrkc 2016-06-10 \n", "lfprgurn 2016-04-25 \n", "lowlow - Il Bambino Soldato _IT_Label_Audio_Eve... 2018-06-07 \n", "lowlow - Il Bambino Soldato _IT_Label_Audio_Pla... 2018-06-07 \n", "lowlow - Sfoghi di una vita complicata 4_IT_Lab... 2018-05-22 \n", "lowlow - Sfoghi di una vita complicata 4_IT_Lab... 2018-05-22 \n", "lowlow - Sfoghi di una vita complicata 4_IT_Lab... 2018-05-22 \n", "n2me9uaq 2016-06-10 \n", "n2mwofg 2016-06-10 \n", "o379qa1k 2016-06-10 \n", "pnm95r15 2016-06-09 \n", "svz53ji 2017-08-16 \n", "w25trcoy 2016-06-09 \n", "wof0 2018-06-22 \n", "x8fs08f 2016-02-09 \n", "zdsx2af 2018-08-23 \n", "\n", " campaign_end \\\n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilia... 2018-10-03 \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop,... 2018-11-17 \n", "1020040556 2017-12-23 \n", "1020220001 2017-12-23 \n", "1046540792 2018-01-26 \n", "1357698868 2018-04-27 \n", "1382530489 2018-05-09 \n", "1457033250 2018-06-29 \n", "1523704272 2018-08-24 \n", "1523704921 2018-08-31 \n", "1527719701 2018-08-24 \n", "1599430704 2018-10-21 \n", "1634536073 2018-11-20 \n", "1638907826 2018-11-24 \n", "1653159090 2018-12-11 \n", "186672703 2016-03-01 \n", "190597783 2016-07-01 \n", "23842662477290584 2017-09-12 \n", "23843014928980274 2018-10-24 \n", "3pi8rzju 2016-06-07 \n", "4hvz9 2016-04-01 \n", "4hwfy 2016-04-01 \n", "4xj6b 2016-05-10 \n", "51x27 2016-05-24 \n", "535lq 2016-05-28 \n", "53h7e 2016-05-30 \n", "57fz5 2016-06-11 \n", "5evmm 2016-07-08 \n", "5ftym 2016-07-11 \n", "5grln 2016-07-13 \n", "... ... \n", "die ärzte_ThisIsPlaylist_Desktop Overlay_Genre:... 2018-11-15 \n", "die ärzte_ThisIsPlaylist_Desktop Overlay_Genre:... 2018-11-15 \n", "die ärzte_ThisIsPlaylist_Leaderboard_AT_Label_7... 2018-11-15 \n", "die ärzte_ThisIsPlaylist_Leaderboard_DE_Label_7... 2018-11-30 \n", "die ärzte_ThisIsPlaylist_Leaderboard_Genre:Rock... 2018-11-30 \n", "die ärzte_ThisIsPlaylist_Leaderboard_Genre:Rock... 2018-11-15 \n", "die ärzte_ThisIsPlaylist_Leaderboard_Genre:Rock... 2018-11-15 \n", "die ärzte_ThisIsPlaylist_Mobile Overlay_AT_Labe... 2018-11-15 \n", "die ärzte_ThisIsPlaylist_Mobile Overlay_Genre:R... 2018-11-15 \n", "die ärzte_ThisIsPlaylist_Mobile Overlay_Genre:R... 2018-11-15 \n", "dodie _If I'm Being Honest_Genre_Pop_GB_Label_7... 2018-11-22 \n", "dodie _If I'm Being Honest_Genre_Pop_IE_Label_7... 2018-11-21 \n", "dodie _If I'm Being Honest_RTPL_Chill_GB_Label_... 2018-11-22 \n", "dodie _If I'm Being Honest_RTPL_Chill_IE_Label_... 2018-11-21 \n", "hgifqrkc 2016-06-10 \n", "lfprgurn 2016-04-25 \n", "lowlow - Il Bambino Soldato _IT_Label_Audio_Eve... 2018-06-07 \n", "lowlow - Il Bambino Soldato _IT_Label_Audio_Pla... 2018-06-07 \n", "lowlow - Sfoghi di una vita complicata 4_IT_Lab... 2018-05-22 \n", "lowlow - Sfoghi di una vita complicata 4_IT_Lab... 2018-05-22 \n", "lowlow - Sfoghi di una vita complicata 4_IT_Lab... 2018-05-22 \n", "n2me9uaq 2016-06-10 \n", "n2mwofg 2016-06-10 \n", "o379qa1k 2016-06-10 \n", "pnm95r15 2016-06-09 \n", "svz53ji 2017-08-16 \n", "w25trcoy 2016-06-09 \n", "wof0 2018-06-22 \n", "x8fs08f 2016-02-09 \n", "zdsx2af 2018-08-23 \n", "\n", " campaign_duration \n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilia... 0 days \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop,... 0 days \n", "1020040556 0 days \n", "1020220001 0 days \n", "1046540792 0 days \n", "1357698868 0 days \n", "1382530489 0 days \n", "1457033250 0 days \n", "1523704272 0 days \n", "1523704921 0 days \n", "1527719701 0 days \n", "1599430704 0 days \n", "1634536073 0 days \n", "1638907826 0 days \n", "1653159090 0 days \n", "186672703 0 days \n", "190597783 0 days \n", "23842662477290584 0 days \n", "23843014928980274 0 days \n", "3pi8rzju 0 days \n", "4hvz9 0 days \n", "4hwfy 0 days \n", "4xj6b 0 days \n", "51x27 0 days \n", "535lq 0 days \n", "53h7e 0 days \n", "57fz5 0 days \n", "5evmm 0 days \n", "5ftym 0 days \n", "5grln 0 days \n", "... ... \n", "die ärzte_ThisIsPlaylist_Desktop Overlay_Genre:... 0 days \n", "die ärzte_ThisIsPlaylist_Desktop Overlay_Genre:... 0 days \n", "die ärzte_ThisIsPlaylist_Leaderboard_AT_Label_7... 0 days \n", "die ärzte_ThisIsPlaylist_Leaderboard_DE_Label_7... 0 days \n", "die ärzte_ThisIsPlaylist_Leaderboard_Genre:Rock... 0 days \n", "die ärzte_ThisIsPlaylist_Leaderboard_Genre:Rock... 0 days \n", "die ärzte_ThisIsPlaylist_Leaderboard_Genre:Rock... 0 days \n", "die ärzte_ThisIsPlaylist_Mobile Overlay_AT_Labe... 0 days \n", "die ärzte_ThisIsPlaylist_Mobile Overlay_Genre:R... 0 days \n", "die ärzte_ThisIsPlaylist_Mobile Overlay_Genre:R... 0 days \n", "dodie _If I'm Being Honest_Genre_Pop_GB_Label_7... 0 days \n", "dodie _If I'm Being Honest_Genre_Pop_IE_Label_7... 0 days \n", "dodie _If I'm Being Honest_RTPL_Chill_GB_Label_... 0 days \n", "dodie _If I'm Being Honest_RTPL_Chill_IE_Label_... 0 days \n", "hgifqrkc 0 days \n", "lfprgurn 0 days \n", "lowlow - Il Bambino Soldato _IT_Label_Audio_Eve... 0 days \n", "lowlow - Il Bambino Soldato _IT_Label_Audio_Pla... 0 days \n", "lowlow - Sfoghi di una vita complicata 4_IT_Lab... 0 days \n", "lowlow - Sfoghi di una vita complicata 4_IT_Lab... 0 days \n", "lowlow - Sfoghi di una vita complicata 4_IT_Lab... 0 days \n", "n2me9uaq 0 days \n", "n2mwofg 0 days \n", "o379qa1k 0 days \n", "pnm95r15 0 days \n", "svz53ji 0 days \n", "w25trcoy 0 days \n", "wof0 0 days \n", "x8fs08f 0 days \n", "zdsx2af 0 days \n", "\n", "[2293 rows x 5 columns]" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ads_df_agg[ads_df_agg['campaign_duration'] == pd.Timedelta(days=0)]" ] }, { "cell_type": "code", "execution_count": 33, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
campaign_keycampaign_namecampaign_startcampaign_endcampaign_duration
campaign_key
\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_12018-10-032018-10-030 days
\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_12018-11-172018-11-170 days
(not set)(not set)(not set)2016-03-072017-05-29448 days
01f155b7-b8ae-42e7-bf74-fab06b6af79f01f155b7-b8ae-42e7-bf74-fab06b6af79fAventura NY - Romeo Tour Campaign – Campaign2018-02-152018-02-183 days
02wvj9802wvj98Fred The Godson - Special Love Banners (1301-00-000-28713) Campaign2018-08-142018-08-217 days
06tps7d06tps7dtriple j - Hottest 100, Vol. 23 (4023-00-000-18000)2016-02-162016-02-215 days
0c3c3c24-b85b-4b18-aee6-ad8f0f93a3790c3c3c24-b85b-4b18-aee6-ad8f0f93a379Aventura Vday ad – Campaign2018-02-122018-02-153 days
0fntoia0fntoiaAdam Sanders EP Out Now Banners2018-05-102018-07-2475 days
0pfrnx90pfrnx9AWOLNATION 'Passion' - OUT NOW Soundcloud Audio (4023-00-000-21975)2017-10-132017-11-3048 days
0zn9weo0zn9weoNothing - Dance on the Blacktop US Banners Campaign2018-08-302018-09-1718 days
1000 Years of Classical Music OUT NOW Video (4023-00-000-18000)1000 Years of Classical Music OUT NOW Video (4023-00-000-18000)1000 Years of Classical Music OUT NOW Video (4023-00-000-18000)2016-01-012016-03-3089 days
10139162161013916216Menke YT 'Moln Video' (4023-00-000-20325) #22018-01-022018-01-075 days
10154266891015426689Marinah 'Afrolailo' - Video Views (4023-00-000-9545)2017-12-192018-01-1224 days
10200405561020040556Black Honey DIG Out Now (4023-00-000-0000)2017-12-232017-12-230 days
10202200011020220001Employed To Serve Kerrang AOTY (4023-00-000-0000)2017-12-232017-12-230 days
10203779911020377991Kathie Lee - The Little Giant Google Adwords Search (1415)2017-12-182017-12-224 days
10204451011020445101Jen Cloher Out Now YT Video Views (6202)2017-12-212017-12-254 days
10204523551020452355Jen Cloher Out Now Google Adwords Search (6202)2017-12-182018-02-0145 days
10219994031021999403Casey - WIGWIAS - Pre Order Awareness (4023-00-000-00000)2017-12-212017-12-254 days
10224222341022422234Roo Panes \"A Message To Myself\" YT Phase 1 Video Views (1415)2018-01-022018-01-097 days
10280106231028010623Frontiers Mar 2018 'Bulletboys' Promoted YT Video Views (1415-00-000-07963)2018-01-042018-01-117 days
10283622721028362272Night Flight - Wanderlust EP PROMOTION (4023-00-000-25771) #22018-01-022018-01-042 days
10287040581028704058Super Dark Times Landing Page2018-01-032018-01-107 days
10287191551028719155Frontiers Dec 2017 Lione Conti - YT Video Views (1415-00-000-07963)2018-01-032018-01-096 days
10311883141031188314Frontiers Mar 2018 'W.E.T' Watch The Fire - YT Discovery (1415-00-000-07963)2018-01-052018-01-127 days
10312588351031258835\"BalconyTV \"JOHN PAGANO BAND\" - Promoted YT Video Views (6202-00-000-00000)\"2018-01-052018-01-061 days
10312597291031259729BalconyTV 'BEHOLD THE BRAVE' - Promoted YT Video Views (6202-00-000-00000)2018-01-052018-01-061 days
10312600171031260017BalconyTV 'TRASH PANDA' - Promoted YT Video Views (6202-00-000-00000)2018-01-052018-01-061 days
10312619191031261919BalconyTV 'BRYNN ELLIOTT' - Promoted YT Video Views (6202-00-000-00000)2018-01-052018-01-061 days
10315161401031516140Frontiers Mar 2018 'Bulletboys' D-Evil - YT Discovery (1415-00-000-07963)2018-01-052018-01-116 days
..................
uzxsznruzxsznrInstant Love - \"Aint No Sunshine\" - OUT NOW Native Video Banners (1415-00-000-22501)2017-07-072017-07-3124 days
vbnw3gyvbnw3gyJohn Prine - The Tree of Forgiveness Grammy Nomination Video Banners2018-10-302018-11-2021 days
vhin0p0vhin0p0The Great Outdoors - Banners2018-05-022018-05-097 days
vlmxlfvvlmxlfvRelapse Records March Releases Boris Merzbow PRE-ORDER (1415-00-000-10440)2016-03-112016-03-187 days
vml3h6kvml3h6kJohn Mayer - New Light Out Now Banners2018-05-162018-07-2469 days
vntki8evntki8eDennis Miller || Fake News, Real Jokes - Banners Campaign2018-11-092018-11-178 days
vosvqv9vosvqv9Relapse Records February Releases OUT NOW (1415-00-000-10440)2016-02-172016-05-0275 days
w25trcoyw25trcoyToonstone - Out Now Banners (1415-00-000-23528)2016-06-092016-06-090 days
w8y477lw8y477lSlothrust - Everyone Else UK OUT NOW Native Banners (1415-00-000-24570)2017-04-282017-07-1073 days
wdc07jqwdc07jqLouie Anderson Banners Out Now2018-04-052018-07-25111 days
we01si3we01si3Relapse Records WRONG Out Now (1415-00-000-10440)2016-04-292016-05-1516 days
wof0wof0FOLLOW US AND WATCH \"TLSID\" http://youtu.be/QjryK43q_cc2018-06-222018-06-220 days
wonk93uwonk93uThe Album Leaf - Between Waves PRE-ORDER Banners (1415-00-000-10440)2016-08-162016-08-259 days
wumyqdvwumyqdvBorn Ruffians - Uncle, Duke, & The Chief UK Banners2018-02-152018-03-0518 days
x20iji8x20iji8Lauren Ruth Ward 'Well Hell' - Banners Out Now Website Clicks (1301-00-000-25947)2018-02-282018-04-1142 days
x8fs08fx8fs08fIn Motion Out Now Premium Banner Inventory Ads (1415-00-000-25152)2016-02-092016-02-090 days
xan35b5xan35b5Radial - Punk February Spotify Audio (6202-00-002-00000)2017-03-042017-04-0330 days
xaque1zxaque1zObituary - Obituary Out Now Banners (4023-00-000-10440)2017-03-172017-04-1226 days
xlmt3hnxlmt3hnFruition -Out Now Banner Ads (1415-00-000-25971)2017-01-162017-01-215 days
xmtv4bvxmtv4bvSuperwalkers - Seagulls Out Now Spotify Audio (4023-00-000-20325)2016-12-162017-01-1530 days
xn5gudxxn5gudxSharon Jones - Miss Sharon Jones! OUT NOW Banners 1415-00-000-11057)2016-08-192016-12-19122 days
xtsezo9xtsezo9Red Baraat - Bhangra Pirates OUT NOW Banners (4023-00-000-24895)2017-03-242017-07-28126 days
y2qdrwwy2qdrwwRelapse Records March Releases Wolvsperpent & Inverloch PRE-ORDER (1415-00-000-10440)2016-02-292016-04-1546 days
y78ezt9y78ezt9A Stand Up Guy OUT NOW on iTunes and On Demand (1415-00-000-25042)2016-02-102016-02-2414 days
yenerm8yenerm8The Lonely Biscuits|| The Universe In You Out Now Banners2018-03-292018-04-079 days
yk8scxryk8scxrNervous Laughter- Out Now Banners (1415-00-000-24700)2017-04-182017-05-0719 days
zdsx2afzdsx2afKelsea Ballerini - Unapologetically OUT NOW CMA Banners (6202-00-002-0000)2018-08-232018-08-230 days
zfwbm6zzfwbm6zPronoun - There's no one new around you OUT NOW Spotify Audio Banners (1415-00-000-24895)2016-11-212016-11-287 days
zu8m6rizu8m6riHip Hop R&B Christmas Gold - Spotify Audio (4023-00-000-08869)2016-12-142016-12-2612 days
zw0rltnzw0rltnDirty Old Wedge - OUT NOW Banners (1415-00-000-23455)2016-10-132016-10-207 days
\n", "

7828 rows × 5 columns

\n", "
" ], "text/plain": [ " campaign_key \\\n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1 \"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1 \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1 \"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1 \n", "(not set) (not set) \n", "01f155b7-b8ae-42e7-bf74-fab06b6af79f 01f155b7-b8ae-42e7-bf74-fab06b6af79f \n", "02wvj98 02wvj98 \n", "06tps7d 06tps7d \n", "0c3c3c24-b85b-4b18-aee6-ad8f0f93a379 0c3c3c24-b85b-4b18-aee6-ad8f0f93a379 \n", "0fntoia 0fntoia \n", "0pfrnx9 0pfrnx9 \n", "0zn9weo 0zn9weo \n", "1000 Years of Classical Music OUT NOW Video (4023-00-000-18000) 1000 Years of Classical Music OUT NOW Video (4023-00-000-18000) \n", "1013916216 1013916216 \n", "1015426689 1015426689 \n", "1020040556 1020040556 \n", "1020220001 1020220001 \n", "1020377991 1020377991 \n", "1020445101 1020445101 \n", "1020452355 1020452355 \n", "1021999403 1021999403 \n", "1022422234 1022422234 \n", "1028010623 1028010623 \n", "1028362272 1028362272 \n", "1028704058 1028704058 \n", "1028719155 1028719155 \n", "1031188314 1031188314 \n", "1031258835 1031258835 \n", "1031259729 1031259729 \n", "1031260017 1031260017 \n", "1031261919 1031261919 \n", "1031516140 1031516140 \n", "... ... \n", "uzxsznr uzxsznr \n", "vbnw3gy vbnw3gy \n", "vhin0p0 vhin0p0 \n", "vlmxlfv vlmxlfv \n", "vml3h6k vml3h6k \n", "vntki8e vntki8e \n", "vosvqv9 vosvqv9 \n", "w25trcoy w25trcoy \n", "w8y477l w8y477l \n", "wdc07jq wdc07jq \n", "we01si3 we01si3 \n", "wof0 wof0 \n", "wonk93u wonk93u \n", "wumyqdv wumyqdv \n", "x20iji8 x20iji8 \n", "x8fs08f x8fs08f \n", "xan35b5 xan35b5 \n", "xaque1z xaque1z \n", "xlmt3hn xlmt3hn \n", "xmtv4bv xmtv4bv \n", "xn5gudx xn5gudx \n", "xtsezo9 xtsezo9 \n", "y2qdrww y2qdrww \n", "y78ezt9 y78ezt9 \n", "yenerm8 yenerm8 \n", "yk8scxr yk8scxr \n", "zdsx2af zdsx2af \n", "zfwbm6z zfwbm6z \n", "zu8m6ri zu8m6ri \n", "zw0rltn zw0rltn \n", "\n", " campaign_name \\\n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1 \"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1 \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1 \"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1 \n", "(not set) (not set) \n", "01f155b7-b8ae-42e7-bf74-fab06b6af79f Aventura NY - Romeo Tour Campaign – Campaign \n", "02wvj98 Fred The Godson - Special Love Banners (1301-00-000-28713) Campaign \n", "06tps7d triple j - Hottest 100, Vol. 23 (4023-00-000-18000) \n", "0c3c3c24-b85b-4b18-aee6-ad8f0f93a379 Aventura Vday ad – Campaign \n", "0fntoia Adam Sanders EP Out Now Banners \n", "0pfrnx9 AWOLNATION 'Passion' - OUT NOW Soundcloud Audio (4023-00-000-21975) \n", "0zn9weo Nothing - Dance on the Blacktop US Banners Campaign \n", "1000 Years of Classical Music OUT NOW Video (4023-00-000-18000) 1000 Years of Classical Music OUT NOW Video (4023-00-000-18000) \n", "1013916216 Menke YT 'Moln Video' (4023-00-000-20325) #2 \n", "1015426689 Marinah 'Afrolailo' - Video Views (4023-00-000-9545) \n", "1020040556 Black Honey DIG Out Now (4023-00-000-0000) \n", "1020220001 Employed To Serve Kerrang AOTY (4023-00-000-0000) \n", "1020377991 Kathie Lee - The Little Giant Google Adwords Search (1415) \n", "1020445101 Jen Cloher Out Now YT Video Views (6202) \n", "1020452355 Jen Cloher Out Now Google Adwords Search (6202) \n", "1021999403 Casey - WIGWIAS - Pre Order Awareness (4023-00-000-00000) \n", "1022422234 Roo Panes \"A Message To Myself\" YT Phase 1 Video Views (1415) \n", "1028010623 Frontiers Mar 2018 'Bulletboys' Promoted YT Video Views (1415-00-000-07963) \n", "1028362272 Night Flight - Wanderlust EP PROMOTION (4023-00-000-25771) #2 \n", "1028704058 Super Dark Times Landing Page \n", "1028719155 Frontiers Dec 2017 Lione Conti - YT Video Views (1415-00-000-07963) \n", "1031188314 Frontiers Mar 2018 'W.E.T' Watch The Fire - YT Discovery (1415-00-000-07963) \n", "1031258835 \"BalconyTV \"JOHN PAGANO BAND\" - Promoted YT Video Views (6202-00-000-00000)\" \n", "1031259729 BalconyTV 'BEHOLD THE BRAVE' - Promoted YT Video Views (6202-00-000-00000) \n", "1031260017 BalconyTV 'TRASH PANDA' - Promoted YT Video Views (6202-00-000-00000) \n", "1031261919 BalconyTV 'BRYNN ELLIOTT' - Promoted YT Video Views (6202-00-000-00000) \n", "1031516140 Frontiers Mar 2018 'Bulletboys' D-Evil - YT Discovery (1415-00-000-07963) \n", "... ... \n", "uzxsznr Instant Love - \"Aint No Sunshine\" - OUT NOW Native Video Banners (1415-00-000-22501) \n", "vbnw3gy John Prine - The Tree of Forgiveness Grammy Nomination Video Banners \n", "vhin0p0 The Great Outdoors - Banners \n", "vlmxlfv Relapse Records March Releases Boris Merzbow PRE-ORDER (1415-00-000-10440) \n", "vml3h6k John Mayer - New Light Out Now Banners \n", "vntki8e Dennis Miller || Fake News, Real Jokes - Banners Campaign \n", "vosvqv9 Relapse Records February Releases OUT NOW (1415-00-000-10440) \n", "w25trcoy Toonstone - Out Now Banners (1415-00-000-23528) \n", "w8y477l Slothrust - Everyone Else UK OUT NOW Native Banners (1415-00-000-24570) \n", "wdc07jq Louie Anderson Banners Out Now \n", "we01si3 Relapse Records WRONG Out Now (1415-00-000-10440) \n", "wof0 FOLLOW US AND WATCH \"TLSID\" http://youtu.be/QjryK43q_cc \n", "wonk93u The Album Leaf - Between Waves PRE-ORDER Banners (1415-00-000-10440) \n", "wumyqdv Born Ruffians - Uncle, Duke, & The Chief UK Banners \n", "x20iji8 Lauren Ruth Ward 'Well Hell' - Banners Out Now Website Clicks (1301-00-000-25947) \n", "x8fs08f In Motion Out Now Premium Banner Inventory Ads (1415-00-000-25152) \n", "xan35b5 Radial - Punk February Spotify Audio (6202-00-002-00000) \n", "xaque1z Obituary - Obituary Out Now Banners (4023-00-000-10440) \n", "xlmt3hn Fruition -Out Now Banner Ads (1415-00-000-25971) \n", "xmtv4bv Superwalkers - Seagulls Out Now Spotify Audio (4023-00-000-20325) \n", "xn5gudx Sharon Jones - Miss Sharon Jones! OUT NOW Banners 1415-00-000-11057) \n", "xtsezo9 Red Baraat - Bhangra Pirates OUT NOW Banners (4023-00-000-24895) \n", "y2qdrww Relapse Records March Releases Wolvsperpent & Inverloch PRE-ORDER (1415-00-000-10440) \n", "y78ezt9 A Stand Up Guy OUT NOW on iTunes and On Demand (1415-00-000-25042) \n", "yenerm8 The Lonely Biscuits|| The Universe In You Out Now Banners \n", "yk8scxr Nervous Laughter- Out Now Banners (1415-00-000-24700) \n", "zdsx2af Kelsea Ballerini - Unapologetically OUT NOW CMA Banners (6202-00-002-0000) \n", "zfwbm6z Pronoun - There's no one new around you OUT NOW Spotify Audio Banners (1415-00-000-24895) \n", "zu8m6ri Hip Hop R&B Christmas Gold - Spotify Audio (4023-00-000-08869) \n", "zw0rltn Dirty Old Wedge - OUT NOW Banners (1415-00-000-23455) \n", "\n", " campaign_start \\\n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1 2018-10-03 \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1 2018-11-17 \n", "(not set) 2016-03-07 \n", "01f155b7-b8ae-42e7-bf74-fab06b6af79f 2018-02-15 \n", "02wvj98 2018-08-14 \n", "06tps7d 2016-02-16 \n", "0c3c3c24-b85b-4b18-aee6-ad8f0f93a379 2018-02-12 \n", "0fntoia 2018-05-10 \n", "0pfrnx9 2017-10-13 \n", "0zn9weo 2018-08-30 \n", "1000 Years of Classical Music OUT NOW Video (4023-00-000-18000) 2016-01-01 \n", "1013916216 2018-01-02 \n", "1015426689 2017-12-19 \n", "1020040556 2017-12-23 \n", "1020220001 2017-12-23 \n", "1020377991 2017-12-18 \n", "1020445101 2017-12-21 \n", "1020452355 2017-12-18 \n", "1021999403 2017-12-21 \n", "1022422234 2018-01-02 \n", "1028010623 2018-01-04 \n", "1028362272 2018-01-02 \n", "1028704058 2018-01-03 \n", "1028719155 2018-01-03 \n", "1031188314 2018-01-05 \n", "1031258835 2018-01-05 \n", "1031259729 2018-01-05 \n", "1031260017 2018-01-05 \n", "1031261919 2018-01-05 \n", "1031516140 2018-01-05 \n", "... ... \n", "uzxsznr 2017-07-07 \n", "vbnw3gy 2018-10-30 \n", "vhin0p0 2018-05-02 \n", "vlmxlfv 2016-03-11 \n", "vml3h6k 2018-05-16 \n", "vntki8e 2018-11-09 \n", "vosvqv9 2016-02-17 \n", "w25trcoy 2016-06-09 \n", "w8y477l 2017-04-28 \n", "wdc07jq 2018-04-05 \n", "we01si3 2016-04-29 \n", "wof0 2018-06-22 \n", "wonk93u 2016-08-16 \n", "wumyqdv 2018-02-15 \n", "x20iji8 2018-02-28 \n", "x8fs08f 2016-02-09 \n", "xan35b5 2017-03-04 \n", "xaque1z 2017-03-17 \n", "xlmt3hn 2017-01-16 \n", "xmtv4bv 2016-12-16 \n", "xn5gudx 2016-08-19 \n", "xtsezo9 2017-03-24 \n", "y2qdrww 2016-02-29 \n", "y78ezt9 2016-02-10 \n", "yenerm8 2018-03-29 \n", "yk8scxr 2017-04-18 \n", "zdsx2af 2018-08-23 \n", "zfwbm6z 2016-11-21 \n", "zu8m6ri 2016-12-14 \n", "zw0rltn 2016-10-13 \n", "\n", " campaign_end \\\n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1 2018-10-03 \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1 2018-11-17 \n", "(not set) 2017-05-29 \n", "01f155b7-b8ae-42e7-bf74-fab06b6af79f 2018-02-18 \n", "02wvj98 2018-08-21 \n", "06tps7d 2016-02-21 \n", "0c3c3c24-b85b-4b18-aee6-ad8f0f93a379 2018-02-15 \n", "0fntoia 2018-07-24 \n", "0pfrnx9 2017-11-30 \n", "0zn9weo 2018-09-17 \n", "1000 Years of Classical Music OUT NOW Video (4023-00-000-18000) 2016-03-30 \n", "1013916216 2018-01-07 \n", "1015426689 2018-01-12 \n", "1020040556 2017-12-23 \n", "1020220001 2017-12-23 \n", "1020377991 2017-12-22 \n", "1020445101 2017-12-25 \n", "1020452355 2018-02-01 \n", "1021999403 2017-12-25 \n", "1022422234 2018-01-09 \n", "1028010623 2018-01-11 \n", "1028362272 2018-01-04 \n", "1028704058 2018-01-10 \n", "1028719155 2018-01-09 \n", "1031188314 2018-01-12 \n", "1031258835 2018-01-06 \n", "1031259729 2018-01-06 \n", "1031260017 2018-01-06 \n", "1031261919 2018-01-06 \n", "1031516140 2018-01-11 \n", "... ... \n", "uzxsznr 2017-07-31 \n", "vbnw3gy 2018-11-20 \n", "vhin0p0 2018-05-09 \n", "vlmxlfv 2016-03-18 \n", "vml3h6k 2018-07-24 \n", "vntki8e 2018-11-17 \n", "vosvqv9 2016-05-02 \n", "w25trcoy 2016-06-09 \n", "w8y477l 2017-07-10 \n", "wdc07jq 2018-07-25 \n", "we01si3 2016-05-15 \n", "wof0 2018-06-22 \n", "wonk93u 2016-08-25 \n", "wumyqdv 2018-03-05 \n", "x20iji8 2018-04-11 \n", "x8fs08f 2016-02-09 \n", "xan35b5 2017-04-03 \n", "xaque1z 2017-04-12 \n", "xlmt3hn 2017-01-21 \n", "xmtv4bv 2017-01-15 \n", "xn5gudx 2016-12-19 \n", "xtsezo9 2017-07-28 \n", "y2qdrww 2016-04-15 \n", "y78ezt9 2016-02-24 \n", "yenerm8 2018-04-07 \n", "yk8scxr 2017-05-07 \n", "zdsx2af 2018-08-23 \n", "zfwbm6z 2016-11-28 \n", "zu8m6ri 2016-12-26 \n", "zw0rltn 2016-10-20 \n", "\n", " campaign_duration \n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1 0 days \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1 0 days \n", "(not set) 448 days \n", "01f155b7-b8ae-42e7-bf74-fab06b6af79f 3 days \n", "02wvj98 7 days \n", "06tps7d 5 days \n", "0c3c3c24-b85b-4b18-aee6-ad8f0f93a379 3 days \n", "0fntoia 75 days \n", "0pfrnx9 48 days \n", "0zn9weo 18 days \n", "1000 Years of Classical Music OUT NOW Video (4023-00-000-18000) 89 days \n", "1013916216 5 days \n", "1015426689 24 days \n", "1020040556 0 days \n", "1020220001 0 days \n", "1020377991 4 days \n", "1020445101 4 days \n", "1020452355 45 days \n", "1021999403 4 days \n", "1022422234 7 days \n", "1028010623 7 days \n", "1028362272 2 days \n", "1028704058 7 days \n", "1028719155 6 days \n", "1031188314 7 days \n", "1031258835 1 days \n", "1031259729 1 days \n", "1031260017 1 days \n", "1031261919 1 days \n", "1031516140 6 days \n", "... ... \n", "uzxsznr 24 days \n", "vbnw3gy 21 days \n", "vhin0p0 7 days \n", "vlmxlfv 7 days \n", "vml3h6k 69 days \n", "vntki8e 8 days \n", "vosvqv9 75 days \n", "w25trcoy 0 days \n", "w8y477l 73 days \n", "wdc07jq 111 days \n", "we01si3 16 days \n", "wof0 0 days \n", "wonk93u 9 days \n", "wumyqdv 18 days \n", "x20iji8 42 days \n", "x8fs08f 0 days \n", "xan35b5 30 days \n", "xaque1z 26 days \n", "xlmt3hn 5 days \n", "xmtv4bv 30 days \n", "xn5gudx 122 days \n", "xtsezo9 126 days \n", "y2qdrww 46 days \n", "y78ezt9 14 days \n", "yenerm8 9 days \n", "yk8scxr 19 days \n", "zdsx2af 0 days \n", "zfwbm6z 7 days \n", "zu8m6ri 12 days \n", "zw0rltn 7 days \n", "\n", "[7828 rows x 5 columns]" ] }, "execution_count": 33, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pd.set_option('display.max_colwidth', -1)\n", "ads_df_agg" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\n", "\n", "With `ads_df` as the ads summary data frame, the next step is to match these items using the `\"Campaign Name\"` field with orchard dimension data. The objective will be to find the most likely product or artist that the ad campaign is intended to promote. \n", "\n", "Campaign Names use a variety of structures, so it is necessary to try a variety of tactics to parse and search the text string. " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The ads team has implemented a standard naming taxonomy found here: https://quip.com/nq5CAwfPmjMn\n", "\n", "\n", "Campaign: [Artist] - [Album] [PHASE] [Platform Name] [Objective] [Accounting Code] \n", "Ad Set: [Artist/Film Targeting] \n", "Ad Creative: [Creative Description] - [Artist/Film Targeting] \n", "\n", "\n", "\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "many do not respond to this format, one of the obvious areas is film ads. I believe we can filter these out by creating a label type column corresponding to `LABEL_IDENTIFIER`\n", "\n", "start by creating a df which can be used as a label definition, then merge with the campaign data, then this new column can be used to filter out non-audio campaigns " ] }, { "cell_type": "code", "execution_count": 35, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
label_identifier
vendor_id
30875None
31589None
31340None
31738None
31151None
\n", "
" ], "text/plain": [ " label_identifier\n", "vendor_id \n", "30875 None \n", "31589 None \n", "31340 None \n", "31738 None \n", "31151 None " ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# connect to orchard_app_reporting.art_relations and create vendor df\n", "ar_engine = create_engine(URL(\n", " user='seggensperger',\n", " password='',\n", " account='orchard',\n", " database='orchard_app_reporting',\n", " schema='art_relations',\n", " role='dev_engineering',\n", " warehouse='dev_performance_warehouse'\n", "))\n", "\n", "vendor_table = Table('vendor', MetaData(), autoload=True, autoload_with=ar_engine)\n", "vendor_df = pd.read_sql(\n", " sql=select([vendor_table.c.vendor_id, vendor_table.c.label_identifier]), con=ar_engine, index_col='vendor_id')\n", "vendor_df.head()" ] }, { "cell_type": "code", "execution_count": 25, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "array([None, 'Frontline', 'Film', 'D3', 'Client Services', 'Catalog', '',\n", " 'Test', 'TV'], dtype=object)" ] }, "execution_count": 25, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# types of label_identifiers\n", "vendor_df.label_identifier.unique()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "from this we will exclude `Film`, `TV`, `Test` \n", "acceptable types: `None`, `Frontline`, `D3`, `Catalog`, `''`, and `Client Services` \n", "\n", "the first step though is to merge the two frames (`vendor_df` and `ads_df_agg`)\n", "\n", "for that the vendor_id column needs to be added by seeking out the account code (xxxx-xx-xxx-xxxxx), the last 5 digits of which are vendor_id" ] }, { "cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [], "source": [ "import re\n", "\n", "def generate_vendor_id_from_campaign_name(row):\n", " \"\"\"Function to get vendor_id from campaign name\"\"\"\n", " name = row['campaign_name']\n", " result = re.search('[0-9]{4,5}\\)', name)\n", " if result:\n", " return int(name[result.start():result.end()-1])\n", " return 0\n", "\n", "\n", "\n", "# testing out the above functino with several different variations of campaign names\n", "sample_name_1 = 'Cool Band of Dudes - Our Hip Album OUT NOW Facebook Brand Awareness (1415-00-000-12345)'\n", "sample_name_2 = 'Fred The Godson - Special Love Banners (1301-00-000-28713) Campaign'\n", "sample_name_3 = 'Fred The Godson - Special Love Banners (1301-00-000-8713) Campaign'\n", "sample_name_4 = 'Fred The Godson - Special Love Banners 1301-00-000-1234) Campaign'\n", "\n", "sample_names = {\n", " 12345: sample_name_1, \n", " 28713: sample_name_2, \n", " 8713: sample_name_3, \n", " 1234: sample_name_4}\n", "\n", "def create_sample_row(sample_name):\n", " return {'campaign_name': sample_name}\n", "\n", "# will throw error if extracted labelid not as expected\n", "for key, val in sample_names.items():\n", " assert key == generate_vendor_id_from_campaign_name(create_sample_row(val))\n" ] }, { "cell_type": "code", "execution_count": 29, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
campaign_keycampaign_namecampaign_startcampaign_endcampaign_durationvendor_id
campaign_key
\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_12018-10-032018-10-030 days0
\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_12018-11-172018-11-170 days0
(not set)(not set)(not set)2016-03-072017-05-29448 days0
01f155b7-b8ae-42e7-bf74-fab06b6af79f01f155b7-b8ae-42e7-bf74-fab06b6af79fAventura NY - Romeo Tour Campaign – Campaign2018-02-152018-02-183 days0
02wvj9802wvj98Fred The Godson - Special Love Banners (1301-00-000-28713) Campaign2018-08-142018-08-217 days28713
06tps7d06tps7dtriple j - Hottest 100, Vol. 23 (4023-00-000-18000)2016-02-162016-02-215 days18000
0c3c3c24-b85b-4b18-aee6-ad8f0f93a3790c3c3c24-b85b-4b18-aee6-ad8f0f93a379Aventura Vday ad – Campaign2018-02-122018-02-153 days0
0fntoia0fntoiaAdam Sanders EP Out Now Banners2018-05-102018-07-2475 days0
0pfrnx90pfrnx9AWOLNATION 'Passion' - OUT NOW Soundcloud Audio (4023-00-000-21975)2017-10-132017-11-3048 days21975
0zn9weo0zn9weoNothing - Dance on the Blacktop US Banners Campaign2018-08-302018-09-1718 days0
1000 Years of Classical Music OUT NOW Video (4023-00-000-18000)1000 Years of Classical Music OUT NOW Video (4023-00-000-18000)1000 Years of Classical Music OUT NOW Video (4023-00-000-18000)2016-01-012016-03-3089 days18000
10139162161013916216Menke YT 'Moln Video' (4023-00-000-20325) #22018-01-022018-01-075 days20325
10154266891015426689Marinah 'Afrolailo' - Video Views (4023-00-000-9545)2017-12-192018-01-1224 days9545
10200405561020040556Black Honey DIG Out Now (4023-00-000-0000)2017-12-232017-12-230 days0
10202200011020220001Employed To Serve Kerrang AOTY (4023-00-000-0000)2017-12-232017-12-230 days0
10203779911020377991Kathie Lee - The Little Giant Google Adwords Search (1415)2017-12-182017-12-224 days1415
10204451011020445101Jen Cloher Out Now YT Video Views (6202)2017-12-212017-12-254 days6202
10204523551020452355Jen Cloher Out Now Google Adwords Search (6202)2017-12-182018-02-0145 days6202
10219994031021999403Casey - WIGWIAS - Pre Order Awareness (4023-00-000-00000)2017-12-212017-12-254 days0
10224222341022422234Roo Panes \"A Message To Myself\" YT Phase 1 Video Views (1415)2018-01-022018-01-097 days1415
10280106231028010623Frontiers Mar 2018 'Bulletboys' Promoted YT Video Views (1415-00-000-07963)2018-01-042018-01-117 days7963
10283622721028362272Night Flight - Wanderlust EP PROMOTION (4023-00-000-25771) #22018-01-022018-01-042 days25771
10287040581028704058Super Dark Times Landing Page2018-01-032018-01-107 days0
10287191551028719155Frontiers Dec 2017 Lione Conti - YT Video Views (1415-00-000-07963)2018-01-032018-01-096 days7963
10311883141031188314Frontiers Mar 2018 'W.E.T' Watch The Fire - YT Discovery (1415-00-000-07963)2018-01-052018-01-127 days7963
10312588351031258835\"BalconyTV \"JOHN PAGANO BAND\" - Promoted YT Video Views (6202-00-000-00000)\"2018-01-052018-01-061 days0
10312597291031259729BalconyTV 'BEHOLD THE BRAVE' - Promoted YT Video Views (6202-00-000-00000)2018-01-052018-01-061 days0
10312600171031260017BalconyTV 'TRASH PANDA' - Promoted YT Video Views (6202-00-000-00000)2018-01-052018-01-061 days0
10312619191031261919BalconyTV 'BRYNN ELLIOTT' - Promoted YT Video Views (6202-00-000-00000)2018-01-052018-01-061 days0
10315161401031516140Frontiers Mar 2018 'Bulletboys' D-Evil - YT Discovery (1415-00-000-07963)2018-01-052018-01-116 days7963
.....................
uzxsznruzxsznrInstant Love - \"Aint No Sunshine\" - OUT NOW Native Video Banners (1415-00-000-22501)2017-07-072017-07-3124 days22501
vbnw3gyvbnw3gyJohn Prine - The Tree of Forgiveness Grammy Nomination Video Banners2018-10-302018-11-2021 days0
vhin0p0vhin0p0The Great Outdoors - Banners2018-05-022018-05-097 days0
vlmxlfvvlmxlfvRelapse Records March Releases Boris Merzbow PRE-ORDER (1415-00-000-10440)2016-03-112016-03-187 days10440
vml3h6kvml3h6kJohn Mayer - New Light Out Now Banners2018-05-162018-07-2469 days0
vntki8evntki8eDennis Miller || Fake News, Real Jokes - Banners Campaign2018-11-092018-11-178 days0
vosvqv9vosvqv9Relapse Records February Releases OUT NOW (1415-00-000-10440)2016-02-172016-05-0275 days10440
w25trcoyw25trcoyToonstone - Out Now Banners (1415-00-000-23528)2016-06-092016-06-090 days23528
w8y477lw8y477lSlothrust - Everyone Else UK OUT NOW Native Banners (1415-00-000-24570)2017-04-282017-07-1073 days24570
wdc07jqwdc07jqLouie Anderson Banners Out Now2018-04-052018-07-25111 days0
we01si3we01si3Relapse Records WRONG Out Now (1415-00-000-10440)2016-04-292016-05-1516 days10440
wof0wof0FOLLOW US AND WATCH \"TLSID\" http://youtu.be/QjryK43q_cc2018-06-222018-06-220 days0
wonk93uwonk93uThe Album Leaf - Between Waves PRE-ORDER Banners (1415-00-000-10440)2016-08-162016-08-259 days10440
wumyqdvwumyqdvBorn Ruffians - Uncle, Duke, & The Chief UK Banners2018-02-152018-03-0518 days0
x20iji8x20iji8Lauren Ruth Ward 'Well Hell' - Banners Out Now Website Clicks (1301-00-000-25947)2018-02-282018-04-1142 days25947
x8fs08fx8fs08fIn Motion Out Now Premium Banner Inventory Ads (1415-00-000-25152)2016-02-092016-02-090 days25152
xan35b5xan35b5Radial - Punk February Spotify Audio (6202-00-002-00000)2017-03-042017-04-0330 days0
xaque1zxaque1zObituary - Obituary Out Now Banners (4023-00-000-10440)2017-03-172017-04-1226 days10440
xlmt3hnxlmt3hnFruition -Out Now Banner Ads (1415-00-000-25971)2017-01-162017-01-215 days25971
xmtv4bvxmtv4bvSuperwalkers - Seagulls Out Now Spotify Audio (4023-00-000-20325)2016-12-162017-01-1530 days20325
xn5gudxxn5gudxSharon Jones - Miss Sharon Jones! OUT NOW Banners 1415-00-000-11057)2016-08-192016-12-19122 days11057
xtsezo9xtsezo9Red Baraat - Bhangra Pirates OUT NOW Banners (4023-00-000-24895)2017-03-242017-07-28126 days24895
y2qdrwwy2qdrwwRelapse Records March Releases Wolvsperpent & Inverloch PRE-ORDER (1415-00-000-10440)2016-02-292016-04-1546 days10440
y78ezt9y78ezt9A Stand Up Guy OUT NOW on iTunes and On Demand (1415-00-000-25042)2016-02-102016-02-2414 days25042
yenerm8yenerm8The Lonely Biscuits|| The Universe In You Out Now Banners2018-03-292018-04-079 days0
yk8scxryk8scxrNervous Laughter- Out Now Banners (1415-00-000-24700)2017-04-182017-05-0719 days24700
zdsx2afzdsx2afKelsea Ballerini - Unapologetically OUT NOW CMA Banners (6202-00-002-0000)2018-08-232018-08-230 days0
zfwbm6zzfwbm6zPronoun - There's no one new around you OUT NOW Spotify Audio Banners (1415-00-000-24895)2016-11-212016-11-287 days24895
zu8m6rizu8m6riHip Hop R&B Christmas Gold - Spotify Audio (4023-00-000-08869)2016-12-142016-12-2612 days8869
zw0rltnzw0rltnDirty Old Wedge - OUT NOW Banners (1415-00-000-23455)2016-10-132016-10-207 days23455
\n", "

7828 rows × 6 columns

\n", "
" ], "text/plain": [ " campaign_key \\\n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1 \"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1 \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1 \"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1 \n", "(not set) (not set) \n", "01f155b7-b8ae-42e7-bf74-fab06b6af79f 01f155b7-b8ae-42e7-bf74-fab06b6af79f \n", "02wvj98 02wvj98 \n", "06tps7d 06tps7d \n", "0c3c3c24-b85b-4b18-aee6-ad8f0f93a379 0c3c3c24-b85b-4b18-aee6-ad8f0f93a379 \n", "0fntoia 0fntoia \n", "0pfrnx9 0pfrnx9 \n", "0zn9weo 0zn9weo \n", "1000 Years of Classical Music OUT NOW Video (4023-00-000-18000) 1000 Years of Classical Music OUT NOW Video (4023-00-000-18000) \n", "1013916216 1013916216 \n", "1015426689 1015426689 \n", "1020040556 1020040556 \n", "1020220001 1020220001 \n", "1020377991 1020377991 \n", "1020445101 1020445101 \n", "1020452355 1020452355 \n", "1021999403 1021999403 \n", "1022422234 1022422234 \n", "1028010623 1028010623 \n", "1028362272 1028362272 \n", "1028704058 1028704058 \n", "1028719155 1028719155 \n", "1031188314 1031188314 \n", "1031258835 1031258835 \n", "1031259729 1031259729 \n", "1031260017 1031260017 \n", "1031261919 1031261919 \n", "1031516140 1031516140 \n", "... ... \n", "uzxsznr uzxsznr \n", "vbnw3gy vbnw3gy \n", "vhin0p0 vhin0p0 \n", "vlmxlfv vlmxlfv \n", "vml3h6k vml3h6k \n", "vntki8e vntki8e \n", "vosvqv9 vosvqv9 \n", "w25trcoy w25trcoy \n", "w8y477l w8y477l \n", "wdc07jq wdc07jq \n", "we01si3 we01si3 \n", "wof0 wof0 \n", "wonk93u wonk93u \n", "wumyqdv wumyqdv \n", "x20iji8 x20iji8 \n", "x8fs08f x8fs08f \n", "xan35b5 xan35b5 \n", "xaque1z xaque1z \n", "xlmt3hn xlmt3hn \n", "xmtv4bv xmtv4bv \n", "xn5gudx xn5gudx \n", "xtsezo9 xtsezo9 \n", "y2qdrww y2qdrww \n", "y78ezt9 y78ezt9 \n", "yenerm8 yenerm8 \n", "yk8scxr yk8scxr \n", "zdsx2af zdsx2af \n", "zfwbm6z zfwbm6z \n", "zu8m6ri zu8m6ri \n", "zw0rltn zw0rltn \n", "\n", " campaign_name \\\n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1 \"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1 \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1 \"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1 \n", "(not set) (not set) \n", "01f155b7-b8ae-42e7-bf74-fab06b6af79f Aventura NY - Romeo Tour Campaign – Campaign \n", "02wvj98 Fred The Godson - Special Love Banners (1301-00-000-28713) Campaign \n", "06tps7d triple j - Hottest 100, Vol. 23 (4023-00-000-18000) \n", "0c3c3c24-b85b-4b18-aee6-ad8f0f93a379 Aventura Vday ad – Campaign \n", "0fntoia Adam Sanders EP Out Now Banners \n", "0pfrnx9 AWOLNATION 'Passion' - OUT NOW Soundcloud Audio (4023-00-000-21975) \n", "0zn9weo Nothing - Dance on the Blacktop US Banners Campaign \n", "1000 Years of Classical Music OUT NOW Video (4023-00-000-18000) 1000 Years of Classical Music OUT NOW Video (4023-00-000-18000) \n", "1013916216 Menke YT 'Moln Video' (4023-00-000-20325) #2 \n", "1015426689 Marinah 'Afrolailo' - Video Views (4023-00-000-9545) \n", "1020040556 Black Honey DIG Out Now (4023-00-000-0000) \n", "1020220001 Employed To Serve Kerrang AOTY (4023-00-000-0000) \n", "1020377991 Kathie Lee - The Little Giant Google Adwords Search (1415) \n", "1020445101 Jen Cloher Out Now YT Video Views (6202) \n", "1020452355 Jen Cloher Out Now Google Adwords Search (6202) \n", "1021999403 Casey - WIGWIAS - Pre Order Awareness (4023-00-000-00000) \n", "1022422234 Roo Panes \"A Message To Myself\" YT Phase 1 Video Views (1415) \n", "1028010623 Frontiers Mar 2018 'Bulletboys' Promoted YT Video Views (1415-00-000-07963) \n", "1028362272 Night Flight - Wanderlust EP PROMOTION (4023-00-000-25771) #2 \n", "1028704058 Super Dark Times Landing Page \n", "1028719155 Frontiers Dec 2017 Lione Conti - YT Video Views (1415-00-000-07963) \n", "1031188314 Frontiers Mar 2018 'W.E.T' Watch The Fire - YT Discovery (1415-00-000-07963) \n", "1031258835 \"BalconyTV \"JOHN PAGANO BAND\" - Promoted YT Video Views (6202-00-000-00000)\" \n", "1031259729 BalconyTV 'BEHOLD THE BRAVE' - Promoted YT Video Views (6202-00-000-00000) \n", "1031260017 BalconyTV 'TRASH PANDA' - Promoted YT Video Views (6202-00-000-00000) \n", "1031261919 BalconyTV 'BRYNN ELLIOTT' - Promoted YT Video Views (6202-00-000-00000) \n", "1031516140 Frontiers Mar 2018 'Bulletboys' D-Evil - YT Discovery (1415-00-000-07963) \n", "... ... \n", "uzxsznr Instant Love - \"Aint No Sunshine\" - OUT NOW Native Video Banners (1415-00-000-22501) \n", "vbnw3gy John Prine - The Tree of Forgiveness Grammy Nomination Video Banners \n", "vhin0p0 The Great Outdoors - Banners \n", "vlmxlfv Relapse Records March Releases Boris Merzbow PRE-ORDER (1415-00-000-10440) \n", "vml3h6k John Mayer - New Light Out Now Banners \n", "vntki8e Dennis Miller || Fake News, Real Jokes - Banners Campaign \n", "vosvqv9 Relapse Records February Releases OUT NOW (1415-00-000-10440) \n", "w25trcoy Toonstone - Out Now Banners (1415-00-000-23528) \n", "w8y477l Slothrust - Everyone Else UK OUT NOW Native Banners (1415-00-000-24570) \n", "wdc07jq Louie Anderson Banners Out Now \n", "we01si3 Relapse Records WRONG Out Now (1415-00-000-10440) \n", "wof0 FOLLOW US AND WATCH \"TLSID\" http://youtu.be/QjryK43q_cc \n", "wonk93u The Album Leaf - Between Waves PRE-ORDER Banners (1415-00-000-10440) \n", "wumyqdv Born Ruffians - Uncle, Duke, & The Chief UK Banners \n", "x20iji8 Lauren Ruth Ward 'Well Hell' - Banners Out Now Website Clicks (1301-00-000-25947) \n", "x8fs08f In Motion Out Now Premium Banner Inventory Ads (1415-00-000-25152) \n", "xan35b5 Radial - Punk February Spotify Audio (6202-00-002-00000) \n", "xaque1z Obituary - Obituary Out Now Banners (4023-00-000-10440) \n", "xlmt3hn Fruition -Out Now Banner Ads (1415-00-000-25971) \n", "xmtv4bv Superwalkers - Seagulls Out Now Spotify Audio (4023-00-000-20325) \n", "xn5gudx Sharon Jones - Miss Sharon Jones! OUT NOW Banners 1415-00-000-11057) \n", "xtsezo9 Red Baraat - Bhangra Pirates OUT NOW Banners (4023-00-000-24895) \n", "y2qdrww Relapse Records March Releases Wolvsperpent & Inverloch PRE-ORDER (1415-00-000-10440) \n", "y78ezt9 A Stand Up Guy OUT NOW on iTunes and On Demand (1415-00-000-25042) \n", "yenerm8 The Lonely Biscuits|| The Universe In You Out Now Banners \n", "yk8scxr Nervous Laughter- Out Now Banners (1415-00-000-24700) \n", "zdsx2af Kelsea Ballerini - Unapologetically OUT NOW CMA Banners (6202-00-002-0000) \n", "zfwbm6z Pronoun - There's no one new around you OUT NOW Spotify Audio Banners (1415-00-000-24895) \n", "zu8m6ri Hip Hop R&B Christmas Gold - Spotify Audio (4023-00-000-08869) \n", "zw0rltn Dirty Old Wedge - OUT NOW Banners (1415-00-000-23455) \n", "\n", " campaign_start \\\n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1 2018-10-03 \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1 2018-11-17 \n", "(not set) 2016-03-07 \n", "01f155b7-b8ae-42e7-bf74-fab06b6af79f 2018-02-15 \n", "02wvj98 2018-08-14 \n", "06tps7d 2016-02-16 \n", "0c3c3c24-b85b-4b18-aee6-ad8f0f93a379 2018-02-12 \n", "0fntoia 2018-05-10 \n", "0pfrnx9 2017-10-13 \n", "0zn9weo 2018-08-30 \n", "1000 Years of Classical Music OUT NOW Video (4023-00-000-18000) 2016-01-01 \n", "1013916216 2018-01-02 \n", "1015426689 2017-12-19 \n", "1020040556 2017-12-23 \n", "1020220001 2017-12-23 \n", "1020377991 2017-12-18 \n", "1020445101 2017-12-21 \n", "1020452355 2017-12-18 \n", "1021999403 2017-12-21 \n", "1022422234 2018-01-02 \n", "1028010623 2018-01-04 \n", "1028362272 2018-01-02 \n", "1028704058 2018-01-03 \n", "1028719155 2018-01-03 \n", "1031188314 2018-01-05 \n", "1031258835 2018-01-05 \n", "1031259729 2018-01-05 \n", "1031260017 2018-01-05 \n", "1031261919 2018-01-05 \n", "1031516140 2018-01-05 \n", "... ... \n", "uzxsznr 2017-07-07 \n", "vbnw3gy 2018-10-30 \n", "vhin0p0 2018-05-02 \n", "vlmxlfv 2016-03-11 \n", "vml3h6k 2018-05-16 \n", "vntki8e 2018-11-09 \n", "vosvqv9 2016-02-17 \n", "w25trcoy 2016-06-09 \n", "w8y477l 2017-04-28 \n", "wdc07jq 2018-04-05 \n", "we01si3 2016-04-29 \n", "wof0 2018-06-22 \n", "wonk93u 2016-08-16 \n", "wumyqdv 2018-02-15 \n", "x20iji8 2018-02-28 \n", "x8fs08f 2016-02-09 \n", "xan35b5 2017-03-04 \n", "xaque1z 2017-03-17 \n", "xlmt3hn 2017-01-16 \n", "xmtv4bv 2016-12-16 \n", "xn5gudx 2016-08-19 \n", "xtsezo9 2017-03-24 \n", "y2qdrww 2016-02-29 \n", "y78ezt9 2016-02-10 \n", "yenerm8 2018-03-29 \n", "yk8scxr 2017-04-18 \n", "zdsx2af 2018-08-23 \n", "zfwbm6z 2016-11-21 \n", "zu8m6ri 2016-12-14 \n", "zw0rltn 2016-10-13 \n", "\n", " campaign_end \\\n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1 2018-10-03 \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1 2018-11-17 \n", "(not set) 2017-05-29 \n", "01f155b7-b8ae-42e7-bf74-fab06b6af79f 2018-02-18 \n", "02wvj98 2018-08-21 \n", "06tps7d 2016-02-21 \n", "0c3c3c24-b85b-4b18-aee6-ad8f0f93a379 2018-02-15 \n", "0fntoia 2018-07-24 \n", "0pfrnx9 2017-11-30 \n", "0zn9weo 2018-09-17 \n", "1000 Years of Classical Music OUT NOW Video (4023-00-000-18000) 2016-03-30 \n", "1013916216 2018-01-07 \n", "1015426689 2018-01-12 \n", "1020040556 2017-12-23 \n", "1020220001 2017-12-23 \n", "1020377991 2017-12-22 \n", "1020445101 2017-12-25 \n", "1020452355 2018-02-01 \n", "1021999403 2017-12-25 \n", "1022422234 2018-01-09 \n", "1028010623 2018-01-11 \n", "1028362272 2018-01-04 \n", "1028704058 2018-01-10 \n", "1028719155 2018-01-09 \n", "1031188314 2018-01-12 \n", "1031258835 2018-01-06 \n", "1031259729 2018-01-06 \n", "1031260017 2018-01-06 \n", "1031261919 2018-01-06 \n", "1031516140 2018-01-11 \n", "... ... \n", "uzxsznr 2017-07-31 \n", "vbnw3gy 2018-11-20 \n", "vhin0p0 2018-05-09 \n", "vlmxlfv 2016-03-18 \n", "vml3h6k 2018-07-24 \n", "vntki8e 2018-11-17 \n", "vosvqv9 2016-05-02 \n", "w25trcoy 2016-06-09 \n", "w8y477l 2017-07-10 \n", "wdc07jq 2018-07-25 \n", "we01si3 2016-05-15 \n", "wof0 2018-06-22 \n", "wonk93u 2016-08-25 \n", "wumyqdv 2018-03-05 \n", "x20iji8 2018-04-11 \n", "x8fs08f 2016-02-09 \n", "xan35b5 2017-04-03 \n", "xaque1z 2017-04-12 \n", "xlmt3hn 2017-01-21 \n", "xmtv4bv 2017-01-15 \n", "xn5gudx 2016-12-19 \n", "xtsezo9 2017-07-28 \n", "y2qdrww 2016-04-15 \n", "y78ezt9 2016-02-24 \n", "yenerm8 2018-04-07 \n", "yk8scxr 2017-05-07 \n", "zdsx2af 2018-08-23 \n", "zfwbm6z 2016-11-28 \n", "zu8m6ri 2016-12-26 \n", "zw0rltn 2016-10-20 \n", "\n", " campaign_duration \\\n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1 0 days \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1 0 days \n", "(not set) 448 days \n", "01f155b7-b8ae-42e7-bf74-fab06b6af79f 3 days \n", "02wvj98 7 days \n", "06tps7d 5 days \n", "0c3c3c24-b85b-4b18-aee6-ad8f0f93a379 3 days \n", "0fntoia 75 days \n", "0pfrnx9 48 days \n", "0zn9weo 18 days \n", "1000 Years of Classical Music OUT NOW Video (4023-00-000-18000) 89 days \n", "1013916216 5 days \n", "1015426689 24 days \n", "1020040556 0 days \n", "1020220001 0 days \n", "1020377991 4 days \n", "1020445101 4 days \n", "1020452355 45 days \n", "1021999403 4 days \n", "1022422234 7 days \n", "1028010623 7 days \n", "1028362272 2 days \n", "1028704058 7 days \n", "1028719155 6 days \n", "1031188314 7 days \n", "1031258835 1 days \n", "1031259729 1 days \n", "1031260017 1 days \n", "1031261919 1 days \n", "1031516140 6 days \n", "... ... \n", "uzxsznr 24 days \n", "vbnw3gy 21 days \n", "vhin0p0 7 days \n", "vlmxlfv 7 days \n", "vml3h6k 69 days \n", "vntki8e 8 days \n", "vosvqv9 75 days \n", "w25trcoy 0 days \n", "w8y477l 73 days \n", "wdc07jq 111 days \n", "we01si3 16 days \n", "wof0 0 days \n", "wonk93u 9 days \n", "wumyqdv 18 days \n", "x20iji8 42 days \n", "x8fs08f 0 days \n", "xan35b5 30 days \n", "xaque1z 26 days \n", "xlmt3hn 5 days \n", "xmtv4bv 30 days \n", "xn5gudx 122 days \n", "xtsezo9 126 days \n", "y2qdrww 46 days \n", "y78ezt9 14 days \n", "yenerm8 9 days \n", "yk8scxr 19 days \n", "zdsx2af 0 days \n", "zfwbm6z 7 days \n", "zu8m6ri 12 days \n", "zw0rltn 7 days \n", "\n", " vendor_id \n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1 0 \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1 0 \n", "(not set) 0 \n", "01f155b7-b8ae-42e7-bf74-fab06b6af79f 0 \n", "02wvj98 28713 \n", "06tps7d 18000 \n", "0c3c3c24-b85b-4b18-aee6-ad8f0f93a379 0 \n", "0fntoia 0 \n", "0pfrnx9 21975 \n", "0zn9weo 0 \n", "1000 Years of Classical Music OUT NOW Video (4023-00-000-18000) 18000 \n", "1013916216 20325 \n", "1015426689 9545 \n", "1020040556 0 \n", "1020220001 0 \n", "1020377991 1415 \n", "1020445101 6202 \n", "1020452355 6202 \n", "1021999403 0 \n", "1022422234 1415 \n", "1028010623 7963 \n", "1028362272 25771 \n", "1028704058 0 \n", "1028719155 7963 \n", "1031188314 7963 \n", "1031258835 0 \n", "1031259729 0 \n", "1031260017 0 \n", "1031261919 0 \n", "1031516140 7963 \n", "... .. \n", "uzxsznr 22501 \n", "vbnw3gy 0 \n", "vhin0p0 0 \n", "vlmxlfv 10440 \n", "vml3h6k 0 \n", "vntki8e 0 \n", "vosvqv9 10440 \n", "w25trcoy 23528 \n", "w8y477l 24570 \n", "wdc07jq 0 \n", "we01si3 10440 \n", "wof0 0 \n", "wonk93u 10440 \n", "wumyqdv 0 \n", "x20iji8 25947 \n", "x8fs08f 25152 \n", "xan35b5 0 \n", "xaque1z 10440 \n", "xlmt3hn 25971 \n", "xmtv4bv 20325 \n", "xn5gudx 11057 \n", "xtsezo9 24895 \n", "y2qdrww 10440 \n", "y78ezt9 25042 \n", "yenerm8 0 \n", "yk8scxr 24700 \n", "zdsx2af 0 \n", "zfwbm6z 24895 \n", "zu8m6ri 8869 \n", "zw0rltn 23455 \n", "\n", "[7828 rows x 6 columns]" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ads_df_agg['vendor_id'] = ads_df_agg.apply(generate_vendor_id_from_campaign_name, axis=1).astype(int)\n", "ads_df_agg" ] }, { "cell_type": "code", "execution_count": 30, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "2483" ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "vendor_id = ads_df_agg['vendor_id']\n", "vendor_id = vendor_id[vendor_id > 0]\n", "len(vendor_id)" ] }, { "cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
campaign_keycampaign_namecampaign_startcampaign_endcampaign_durationvendor_idlabel_identifier
campaign_key
\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_12018-10-032018-10-030 days0NaN
\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_12018-11-172018-11-170 days0NaN
(not set)(not set)(not set)2016-03-072017-05-29448 days0NaN
01f155b7-b8ae-42e7-bf74-fab06b6af79f01f155b7-b8ae-42e7-bf74-fab06b6af79fAventura NY - Romeo Tour Campaign – Campaign2018-02-152018-02-183 days0NaN
0c3c3c24-b85b-4b18-aee6-ad8f0f93a3790c3c3c24-b85b-4b18-aee6-ad8f0f93a379Aventura Vday ad – Campaign2018-02-122018-02-153 days0NaN
\n", "
" ], "text/plain": [ " campaign_key \\\n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1 \"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1 \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1 \"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1 \n", "(not set) (not set) \n", "01f155b7-b8ae-42e7-bf74-fab06b6af79f 01f155b7-b8ae-42e7-bf74-fab06b6af79f \n", "0c3c3c24-b85b-4b18-aee6-ad8f0f93a379 0c3c3c24-b85b-4b18-aee6-ad8f0f93a379 \n", "\n", " campaign_name \\\n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1 \"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1 \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1 \"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1 \n", "(not set) (not set) \n", "01f155b7-b8ae-42e7-bf74-fab06b6af79f Aventura NY - Romeo Tour Campaign – Campaign \n", "0c3c3c24-b85b-4b18-aee6-ad8f0f93a379 Aventura Vday ad – Campaign \n", "\n", " campaign_start \\\n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1 2018-10-03 \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1 2018-11-17 \n", "(not set) 2016-03-07 \n", "01f155b7-b8ae-42e7-bf74-fab06b6af79f 2018-02-15 \n", "0c3c3c24-b85b-4b18-aee6-ad8f0f93a379 2018-02-12 \n", "\n", " campaign_end \\\n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1 2018-10-03 \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1 2018-11-17 \n", "(not set) 2017-05-29 \n", "01f155b7-b8ae-42e7-bf74-fab06b6af79f 2018-02-18 \n", "0c3c3c24-b85b-4b18-aee6-ad8f0f93a379 2018-02-15 \n", "\n", " campaign_duration \\\n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1 0 days \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1 0 days \n", "(not set) 448 days \n", "01f155b7-b8ae-42e7-bf74-fab06b6af79f 3 days \n", "0c3c3c24-b85b-4b18-aee6-ad8f0f93a379 3 days \n", "\n", " vendor_id \\\n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1 0 \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1 0 \n", "(not set) 0 \n", "01f155b7-b8ae-42e7-bf74-fab06b6af79f 0 \n", "0c3c3c24-b85b-4b18-aee6-ad8f0f93a379 0 \n", "\n", " label_identifier \n", "campaign_key \n", "\"Deck - Cacife Clandestino (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Mobile_Overlay_1 NaN \n", "\"Deck - MODÉ$TIA (Sub-genre: Brazilian Hip Hop, Pop Rap, Trap\"_BR_Label_Audio_Everywhere_1 NaN \n", "(not set) NaN \n", "01f155b7-b8ae-42e7-bf74-fab06b6af79f NaN \n", "0c3c3c24-b85b-4b18-aee6-ad8f0f93a379 NaN " ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ads_df_w_vendor = ads_df_agg.merge(vendor_df, how='left', left_on='vendor_id', right_index=True)\n", "\n", "# inspecting if why some vendors are unmatched, mostly missing the numerical account code\n", "ads_df_w_vendor[ads_df_w_vendor['vendor_id'] == 0].head()" ] }, { "cell_type": "code", "execution_count": 33, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "'total campaigns: 7828'\n", "'total campaigns with matching label: 2243'\n" ] }, { "data": { "text/plain": [ "" ] }, "execution_count": 33, "metadata": {}, "output_type": "execute_result" }, { "data": { "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYQAAAD8CAYAAAB3u9PLAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMS4yLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvNQv5yAAAF6dJREFUeJzt3X+cXXV95/HXkKFYijXAhcgk2QY1a1fjgoL4gz4UjaVAKYEWPlBdDZiSh6ug6FpF8bFh1VpcXDG7tdpRkPAoFT7S5kHsYjELdq2LoJAFEdE+ImbJMDHpmBBLUXHC3T/Od+hlcmeSe89k7oR5PR+PeeSe7/l+7znnm7nnfc73nHOnr9lsIknSAb1eAUnSzGAgSJIAA0GSVBgIkiTAQJAkFQaCJAkwECRJhYEgSQIMBElS0d/rFdgDH6OWpO70ddpgpgcCw8PDXbdtNBqMjIxM4do8s9lfnbG/OmN/daZOfw0MDHTVziEjSRJgIEiSCgNBkgQYCJKkwkCQJAEGgiSpMBAkSYCBIEkqDARJErAfPKksSb2068IzerPgtXdM+yI9Q5AkAQaCJKnY45BRRFwDnA5sy8wl4+a9F7gSOCIzRyKiD1gNnAY8DpyfmRtK3eXAh0rTj2bmmqnbDElSXXtzhnAtcMr4wohYCPw28HBL8anA4vKzEvhMqXsYsAp4BXACsCoiDq2z4pKkqbXHQMjMrwPb28y6CngfT/+bBcuA6zKzmZl3AnMj4ijgd4D1mbk9M3cA62kTMpKk3unqGkJEnAE8kpn3jZs1H9jcMj1UyiYqlyTNEB3fdhoRBwOXASe3md3uL/Q0Jylv9/4rqYabyEwajUanq/iU/v7+Wu1nG/urM/ZXZ/bX/trao+X2or+6eQ7h+cDRwH0RAbAA2BARJ1Ad+S9sqbsAGC7lJ40r//t2b56Zg8BgmWzW+QtL/oWmzthfnbG/OmN/dWZ0dHTa/2Jax4GQmfcDR45NR8Qm4Phyl9E64KKIuIHqAvLOzNwSEbcCH2u5kHwy8IGu1liStE/s8RpCRHwR+CbwwogYiogVk1S/BXgI2Ah8Dng7QGZuBz4CfLv8fLiUSZJmiL5ms+1Q/kzRHB4e7rqxp6idsb86Y391Zn/tr159dcW8tXfUHTJqd+12Uj6pLEkCDARJUmEgSJIAA0GSVBgIkiTAQJAkFQaCJAkwECRJhYEgSQIMBElSYSBIkgADQZJUGAiSJMBAkCQVBoIkCTAQJEmFgSBJAgwESVJhIEiSAANBklT076lCRFwDnA5sy8wlpexK4PeAJ4AfAhdk5qNl3geAFcAu4J2ZeWspPwVYDcwBPp+ZV0z95kiSurU3ZwjXAqeMK1sPLMnMfw/8I/ABgIh4EXAe8OLS5s8jYk5EzAE+DZwKvAj4w1JXkjRD7DEQMvPrwPZxZV/NzNEyeSewoLxeBtyQmb/IzB8BG4ETys/GzHwoM58Abih1JUkzxFRcQ3gr8JXyej6wuWXeUCmbqFySNEPs8RrCZCLiMmAUuL4U9bWp1qR98DQneM+VwEqAzKTRaHS9fv39/bXazzb2V2fsr87sr/21tUfL7UV/dR0IEbGc6mLz0swc27kPAQtbqi0AhsvricqfJjMHgcEy2RwZGel2FWk0GtRpP9vYX52xvzpjf3VmdHS06/4aGBjoql1XgVDuGHo/8NrMfLxl1jrgryLik8AAsBj4FtWZw+KIOBp4hOrC8xu7WmNJ0j6xN7edfhE4CWhExBCwiuquooOA9REBcGdmvi0zH4iIBL5HNZT0jszcVd7nIuBWqttOr8nMB/bB9kiSutTXbLYdyp8pmsPDbUeW9oqnqJ2xvzpjf3Vmf+2vXRee0ZPlzlt7R90ho3bXdCflk8qSJMBAkCQVBoIkCTAQJEmFgSBJAgwESVJhIEiSAANBklQYCJIkwECQJBUGgiQJMBAkSYWBIEkCDARJUmEgSJIAA0GSVBgIkiTAQJAkFQaCJAmA/j1ViIhrgNOBbZm5pJQdBtwILAI2AZGZOyKiD1gNnAY8DpyfmRtKm+XAh8rbfjQz10ztpkiS6tibM4RrgVPGlV0K3JaZi4HbyjTAqcDi8rMS+Aw8FSCrgFcAJwCrIuLQuisvSZo6ewyEzPw6sH1c8TJg7Ah/DXBmS/l1mdnMzDuBuRFxFPA7wPrM3J6ZO4D17B4ykqQe6vYawrzM3AJQ/j2ylM8HNrfUGyplE5VLkmaIPV5D6FBfm7LmJOW7iYiVVMNNZCaNRqPrlenv76/Vfraxvzpjf3Vmf+2vrT1abi/6q9tA2BoRR2XmljIktK2UDwELW+otAIZL+Unjyv++3Rtn5iAwWCabIyMjXa4iNBoN6rSfbeyvzthfnbG/OjM6Otp1fw0MDHTVrtsho3XA8vJ6OXBzS/lbIqIvIl4J7CxDSrcCJ0fEoeVi8smlTJI0Q+zNbadfpDq6b0TEENXdQlcAGRErgIeBc0r1W6huOd1IddvpBQCZuT0iPgJ8u9T7cGaOv1AtSeqhvmaz7VD+TNEcHh7uurGnqJ2xvzpjf3Vmf+2vXRee0ZPlzlt7R90ho3bXbiflk8qSJMBAkCQVBoIkCTAQJEmFgSBJAgwESVJhIEiSAANBklQYCJIkwECQJBUGgiQJMBAkSYWBIEkCDARJUmEgSJIAA0GSVBgIkiTAQJAkFQaCJAkwECRJRX+dxhHxbuCPgCZwP3ABcBRwA3AYsAF4c2Y+EREHAdcBxwE/Ac7NzE11li9JmjpdnyFExHzgncDxmbkEmAOcB3wcuCozFwM7gBWlyQpgR2a+ALiq1JMkzRB1h4z6gV+NiH7gYGAL8HrgpjJ/DXBmeb2sTFPmL42IvprLlyRNka4DITMfAT4BPEwVBDuBe4BHM3O0VBsC5pfX84HNpe1oqX94t8uXJE2trq8hRMShVEf9RwOPAl8CTm1TtVn+bXc20BxfEBErgZUAmUmj0eh2Fenv76/Vfraxvzpjf3Vmf+2vrT1abi/6q85F5TcAP8rMfwKIiL8BXg3MjYj+chawABgu9YeAhcBQGWJ6DrB9/Jtm5iAwWCabIyMjXa9go9GgTvvZxv7qjP3VGfurM6Ojo13318DAQFft6gTCw8ArI+Jg4GfAUuBu4GvA2VR3Gi0Hbi7115Xpb5b5t2fmbmcIkqTeqHMN4S6qi8MbqG45PYDqyP79wHsiYiPVNYKrS5OrgcNL+XuAS2ustyRpitV6DiEzVwGrxhU/BJzQpu7PgXPqLE+StO/4pLIkCTAQJEmFgSBJAgwESVJhIEiSAANBklQYCJIkwECQJBUGgiQJMBAkSYWBIEkCDARJUmEgSJIAA0GSVBgIkiTAQJAkFQaCJAkwECRJhYEgSQIMBElS0V+ncUTMBT4PLAGawFuBHwA3AouATUBk5o6I6ANWA6cBjwPnZ+aGOsuXJE2dumcIq4G/y8zfBI4BHgQuBW7LzMXAbWUa4FRgcflZCXym5rIlSVOo60CIiF8HXgNcDZCZT2Tmo8AyYE2ptgY4s7xeBlyXmc3MvBOYGxFHdb3mkqQpVWfI6HnAPwFfiIhjgHuAdwHzMnMLQGZuiYgjS/35wOaW9kOlbEvrm0bESqozCDKTRqPR9Qr29/fXaj/b2F+dsb86s7/219YeLbcX/VUnEPqBlwEXZ+ZdEbGafx0eaqevTVlzfEFmDgKDY/NHRka6XsFGo0Gd9rON/dUZ+6sz9ldnRkdHu+6vgYGBrtrVuYYwBAxl5l1l+iaqgNg6NhRU/t3WUn9hS/sFwHCN5UuSplDXgZCZPwY2R8QLS9FS4HvAOmB5KVsO3FxerwPeEhF9EfFKYOfY0JIkqfdq3XYKXAxcHxG/AjwEXEAVMhkRK4CHgXNK3VuobjndSHXb6QU1ly1JmkK1AiEz7wWObzNraZu6TeAddZYnSdp3fFJZkgQYCJKkwkCQJAEGgiSpMBAkSYCBIEkqDARJEmAgSJIKA0GSBBgIkqTCQJAkAQaCJKkwECRJgIEgSSoMBEkSYCBIkgoDQZIEGAiSpMJAkCQBNf+mMkBEzAHuBh7JzNMj4mjgBuAwYAPw5sx8IiIOAq4DjgN+ApybmZvqLl+SNDWm4gzhXcCDLdMfB67KzMXADmBFKV8B7MjMFwBXlXqSpBmiViBExALgd4HPl+k+4PXATaXKGuDM8npZmabMX1rqS5JmgLpnCJ8C3gc8WaYPBx7NzNEyPQTML6/nA5sByvydpb4kaQbo+hpCRJwObMvMeyLipFLc7oi/uRfzWt93JbASIDNpNBrdriL9/f212s829ldn7K/O7K/9tbVHy+1Ff9W5qHwicEZEnAY8C/h1qjOGuRHRX84CFgDDpf4QsBAYioh+4DnA9vFvmpmDwGCZbI6MjHS9go1GgzrtZxv7qzP2V2fsr86Mjo523V8DAwNdtet6yCgzP5CZCzJzEXAecHtmvgn4GnB2qbYcuLm8XlemKfNvz8zdzhAkSb2xL55DeD/wnojYSHWN4OpSfjVweCl/D3DpPli2JKlLfc3mjD5Ibw4PD++51gQ8Re2M/dUZ+6sz+2t/7brwjJ4sd97aO+oOGXV8F6dPKkuSAANBklQYCJIkwECQJBUGgiQJMBAkSUXtr7+WNHvUuQWzzldAzPncuhqttbc8Q5AkAQaCJKkwECRJgIEgSSoMBEkSYCBIkgoDQZIEGAiSpMJAkCQBBoIkqTAQJEmAgSBJKgwESRJQ49tOI2IhcB3wXOBJYDAzV0fEYcCNwCJgExCZuSMi+oDVwGnA48D5mbmh3uprPL+NUlK36nz99SjwnzJzQ0Q8G7gnItYD5wO3ZeYVEXEpcCnwfuBUYHH5eQXwmfKvtF/aetare7Jcw1f7StdDRpm5ZewIPzP/GXgQmA8sA9aUamuAM8vrZcB1mdnMzDuBuRFxVNdrLkmaUlPyB3IiYhHwUuAuYF5mboEqNCLiyFJtPrC5pdlQKdsy7r1WAitLexqNRtfr1d/fX6v9/qjOsE8ds62fYXb2tds8fXqx/6odCBFxCPDXwCWZ+dOImKhqX5uy5viCzBwEBsfmj4yMdL1ujUaDOu219+zn6TMb+3o2bvPo6GjX2z0wMNBVu1p3GUXEgVRhcH1m/k0p3jo2FFT+3VbKh4CFLc0XAMN1li9Jmjp17jLqA64GHszMT7bMWgcsB64o/97cUn5RRNxAdTF559jQkiSp9+oMGZ0IvBm4PyLuLWUfpAqCjIgVwMPAOWXeLVS3nG6kuu30ghrLliRNsa4DITO/QfvrAgBL29RvAu/odnmSpH3LJ5UlSYCBIEkqDARJEmAgSJIKA0GSBBgIkqTCQJAkAQaCJKkwECRJgIEgSSoMBEkSYCBIkgoDQZIEGAiSpMJAkCQBBoIkqTAQJEmAgSBJKur8TeUZb+tZr+7Jcud8bl1PlitJdUx7IETEKcBqYA7w+cy8YrrXQZK0u2kdMoqIOcCngVOBFwF/GBEvms51kCS1N93XEE4ANmbmQ5n5BHADsGya10GS1MZ0B8J8YHPL9FApkyT12HRfQ+hrU9ZsnYiIlcBKgMxkYGCg+6X9z7u7b7u/mo3b3Cuzsa/d5mlVa//Xhek+QxgCFrZMLwCGWytk5mBmHp+Zx1MFSNc/EXFP3feYTT/2l/1lf82cnynor45N9xnCt4HFEXE08AhwHvDGaV4HSVIb03qGkJmjwEXArcCDVVE+MJ3rIElqb9qfQ8jMW4Bbpmlxg9O0nGcK+6sz9ldn7K/OTHt/9TWbzT3XkiQ94/ldRpIkYIZ+l1FEPBf4FPBy4BfAJuCSzPzHNnXnAm/MzD/fi/d9LDMPmeLV7dpE2wk8AfxtZi6JiOOBt2TmO7tcxgcz82MTzHsr8G6qW38PAC7LzJu7Wc64930b8HhmXlf3vWaCiNgF3A8cCIwCa4BPZeaTEXEC/3pq3wdcnplre7OmvdHSP2POBBqU39uIOB84PjMv6sX6TYd2fZCZm6bgfS8BBjPz8TK9iaovRyLijsyc0i9sm3GBEBF9wFpgTWaeV8qOBeYBuwUCMBd4O7DHQJhJ9rCdTz28l5l3A3VuhP4gsFsgRMQC4DLgZZm5MyIOAY7oYP37y00Cu8nMz3a7sjPUzzLzWICIOBL4K+A5wCrgu1Qf0NGIOAq4LyK+PFHfPEM91T8tNlHv93Z/064PnjLZ52UPLgH+Enh8/IypDgOYgYEAvA74ZetOJTPvjYhDIuI24FCqI7UPlaPZK4DnR8S9wHrgvwA3t6n3lLIz/q9U36nUBD6amTdGxAHAnwGvBX5EddR8TWbeNF3bWdZvUcu6ngS8NzNPj4hfA/4H8BKq/7vLM/PmcgR2BnAw8HxgbWa+LyKuAH619M0DmfmmluUfCfwz8FhZ9mNjryPi+VTfOXUE1S/ihZn5/Yi4FtgOvBS4NyLOAo7NzEdLu43AicB/BB7LzE9ExAuAz5b32gWck5k/jIg/BgI4qKzvqrJ9SfV8yhzgI5l5Y51OnmqZua08PPntiLh87MiteBbjHrScrVp/b8eVXwv8DPhN4DeAC4DlwKuAuzLz/Gld0X2ofC5/l+r34tciYint9zsnAZcDI8AS4B7gPwAXAwPA1yJiJDNfN+79H8vMQyZqn5nNiDgO+CRwSJl/fmZumWidZ+I1hLENGu/nwFmZ+TKqnel/Kzv2S4EfZuaxmfnHk9Rr9fvAscAxwBuAK8vR3e8Di6h2uH9E9Uu6r0y0nZO5DLg9M19OtW1Xlp0oVNtzLtW6nxsRCzPzUsqRy7gwALgP2Ar8KCK+EBG/1zJvELg4M48D3svTz77+LfCGzHw3VfCeBRARrwA2ZebWccu5Hvh0Zh4DvBrYEhEnA4upvtvqWOC4iHgNcAownJnHZOYS4O867J9pkZkPUX12joRq2yPiAaohg7fNsrMDKAcd5WdvhssOBV5PNVz5ZeAq4MXAS8pZ8v5ooj54FbA8M1/PxPsdqA6yLqH60s/nASdm5n+nenD3dePDoI3d2kfEgVQHkGeXz/I1wJ9M9iYz8QxhIn3Ax8qO40mq70Ca10G9H7fU+S3gi5m5C9gaEf+bahz/t4AvZeaTwI8j4mv7bGu6czJwRkS8t0w/C/g35fVtmbkTICK+R3X0tXn3t6hk5q7yVeQvB5YCV5WjiU9Q7bi/FBFj1Q9qafql0m8ANwL/GfgC1UOGTzuaj4hnA/PHxtQz8+el/OSyLf+3VD2EKiD+AfhERHyc6hrKP+xNp/TIUwcZmXkX8OKI+HfAmoj4yti2zhKTDpe08eVy9Ho/sDUz7wcooboIuHcfrOO+NlEfrM/M7eX1RPudnwLfyswhgHJGvwj4RgfLb9f+UaoDz/XlszwHmPDsAGZmIDwAnN2m/E1Uww7HZeYvy8WVZ3VZb6LHurt63LtLE23nZPqAP8jMH7QWlqPzX7QU7WIv/m8zswl8C/hWRKyn2rF/Enh0kg/4v7S8/ibwgog4gupC4kfbrO9E2/GnmfkX42eUUDoN+NOI+GpmfnhP2zHdIuJ5VH28rbU8Mx+MiH+h+hDOpvHzTo39rj7J039vn2Rm7pPqaP28TLZ/6fjzuxft+6iGivd6pGMmDhndDhwUEReOFUTEy6mOeLeVnfzryjRU4+DPbmn/nAnqtfo61bDKnLIzew3VjvEbwB9ExAERMQ84aYq3rVXb7YyI107S5lbg4rEhsIh46V4s55fl1PFpImIgIl7WUnQs8P8y86dUw0jnlHp9EXFMuzcugbKWKkQezMyfjJv/U2AoIs4s73VQRBxctuOt5UI2ETE/Io6MiAGqu5P+kupMpXX9ZoTy+/JZ4M/KUe7REdFf5v0G8EKqC6rSeBPtdyYzfv/WiR8AR0TEqwAi4sCIePFkDWZcIJSdzFnAb0fED8tp5OVUTzcfHxF3U50FfL/U/wnwfyLiuxFxJdWY9W71xlkLfIdqHP124H2Z+WPgr6m+gO+7wF8AdwE7p3k7hydp9hGqC+XfiYjvluk9GSz1rx9XfiDV8Mz3yynmucC7yrw3ASsi4j6qM5nJ/mbFjVQXwCa6+Ptm4J0R8R3gDuC5mflVqjt1vlmGDW6i+qV/CdXZyr1U10vGn3H0ytj48APA/wK+SnXzAlTDAPeVdV4LvD0zR3q0nprZJtrvTGYQ+Eo3w9dZ/c2Zs4GPl8/yvVTDwRPySeVxIuKQzHwsIg6nSu8T9+I/TZL2e8+08bqp8LdRPez2K1S3PRoGkmYFzxAkScAMvIYgSeoNA0GSBBgIkqTCQJAkAQaCJKkwECRJAPx/pJ/PTOx3N2gAAAAASUVORK5CYII=\n", "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "label_identifier = ads_df_w_vendor.label_identifier\n", "print('total campaigns: ' + str(len(label_identifier)))\n", "label_identifier = label_identifier.dropna()\n", "print('total campaigns with matching label: ' + str(len(label_identifier)))\n", "label_identifier.hist()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "next step is to run through parsing function based on standard taxonomy provided by ads team" ] }, { "cell_type": "code", "execution_count": 129, "metadata": { "scrolled": true }, "outputs": [], "source": [ "\"\"\"Individual functions to parse attributes for campaign names \"\"\"\n", "import re\n", "\n", "def extract_artist_or_album(row, position):\n", " campaign_name = row['campaign_name_no_quotes']\n", " location = 0\n", " phase_found = False\n", " \n", " phases = [\n", " 'DIG', 'PRE-ORDER', 'OUT NOW', 'DIGITAL ', 'THEATRICAL', 'EP ',\n", " 'THEATRICAL AWARENESS', 'Promoted', 'Landing Page', 'Apple Promotion', 'Album Release', 'NY ',\n", " 'Search Ads', 'Banners', 'Google', ' YT ', 'Promoted', 'PreOrder', 'video promotion', \n", " 'Album Promotion', 'promotion', 'Album Awareness', 'Promo', 'Tour Phase', 'Vday campaign', \n", " 'Video Views', 'Video Ad', 'Video Discovery', 'Remarketing', 'Sponsored Snap', \n", " 'TW Website Clicks', 'Video', 'Out Now Banners', '\\('\n", " ]\n", " \n", " for phase in phases:\n", " result = re.search(phase, campaign_name, re.IGNORECASE)\n", " if result is not None:\n", " phase_found = True\n", " location = result.start()\n", " break\n", " \n", " names_list = campaign_name[0:location].split(' - ')\n", "\n", " if position > len(names_list) - 1:\n", " return ''\n", " return names_list[position].strip()\n", "\n", "def extract_artist(row):\n", " return extract_artist_or_album(row, 0)\n", "\n", "def extract_album(row):\n", " return extract_artist_or_album(row, 1)" ] }, { "cell_type": "code", "execution_count": 130, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "'artist: Ephwurd'\n", "'album: Everywhere I Go'\n", "'-------------------------'\n" ] } ], "source": [ "# Testing for alubm and artist extract\n", "# samples for testing: \n", "sample_0 = 'Cool Band of Dudes - Our Hip Album OUT NOW Facebook Brand Awareness (1415-00-000-12345)'\n", "sample_1 = 'Fred The Godson - Special Love Banners (1301-00-000-28713) Campaign'\n", "sample_2 = 'triple j - Hottest 100, Vol. 23 (4023-00-000-18000)'\n", "sample_3 = '1000 Years of Classical Music OUT NOW Video (4023-00-000-18000)'\n", "sample_4 = 'Kathie Lee - The Little Giant Google Adwords Search (1415)'\n", "sample_5 = 'Jen Cloher Out Now YT Video Views '\n", "sample_6 = 'Rick Springfield Pre-Order Google Adwords Search (1415-00-000-07963)'\n", "sample_7 = 'Adam Sanders EP Out Now Banners'\n", "sample_8 = 'Sufjan Stevens - - Mystery of Love CMBYN Search Ads'\n", "sample_9 = 'Ephwurd - Everywhere I Go Out Now Banners (1301-00-000-26716) Campaign'\n", "\n", "samples = [sample_0, sample_1, sample_2, sample_3, sample_4, sample_5, sample_6, sample_7, sample_8, sample_9]\n", "samples = [sample_9]\n", "\n", "\n", "def create_row_from_sample(string_sample):\n", " return {'campaign_name_no_quotes': string_sample}\n", "\n", "for sample in samples:\n", " print('artist: ' + extract_artist(create_row_from_sample(sample)))\n", " print('album: ' + extract_album(create_row_from_sample(sample)))\n", " print('-------------------------')" ] }, { "cell_type": "code", "execution_count": 49, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "'Menke YT - Moln Video (4023-00-000-20325) #2'\n", "'AWOLNATION - Passion - OUT NOW Soundcloud Audio (4023-00-000-21975)'\n", "'Marinah - Afrolailo - Video Views (4023-00-000-9545)'\n", "'Roo Panes - A Message To Myself YT Phase 1 Video Views (1415)'\n", "'Nuclear Blast USA Black Friday 2016'\n", "'W.E.T - Watch The Fire - YT Discovery (1415-00-000-07963)'\n" ] } ], "source": [ "def replace_quoted_substring_with_dash(row):\n", " string = row['campaign_name']\n", " if (string[0] == '\"' and string[-1] == '\"') or (string[0] == \"'\" and string[-1] == \"'\"):\n", " string = string[1:-1]\n", " result = re.search('(?:\\'|\").*(?:\\'|\")', string)\n", "\n", " if result:\n", " start = result.start()\n", " if start > 0:\n", " string = string[:start] + '- ' + string[start:]\n", " string = re.sub('(?:\\'|\")', '', string)\n", " else:\n", " end = result.end()\n", " string = string[:end] + ' -' + string[end:]\n", " string = re.sub('(?:\\'|\")', '', string)\n", "\n", " return string\n", "\n", "\n", "# album in quotes\n", "sample_m = 'Menke YT \\'Moln Video\\' (4023-00-000-20325) #2'\n", "sample_n = 'AWOLNATION \\'Passion\\' - OUT NOW Soundcloud Audio (4023-00-000-21975)'\n", "sample_o = 'Marinah \\'Afrolailo\\' - Video Views (4023-00-000-9545)'\n", "sample_p = '\"Roo Panes \"A Message To Myself\" YT Phase 1 Video Views (1415)\"'\n", "sample_q = 'Nuclear Blast USA Black Friday 2016'\n", "sample_r = \"'W.E.T' Watch The Fire - YT Discovery (1415-00-000-07963)\"\n", "\n", "samples = [sample_m, sample_n, sample_o, sample_p, sample_q, sample_r]\n", "\n", "\n", "def create_row_from_sample(string_sample):\n", " return {'campaign_name': string_sample}\n", "\n", "\n", "for sample in samples:\n", " print(replace_quoted_substring_with_dash(create_row_from_sample(sample)))\n", " " ] }, { "cell_type": "code", "execution_count": 50, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "'Bulletboys D-Evil - YT Discovery (1415-00-000-07963)'\n", "'Animal Drive Tower of Lies - YT Discovery (1415-00-000-0796'\n", "'Shiraz Lane People Like Us - YT Discovery (1415-00-000-07963)'\n", "'Fred The Godson - Special Love Banners (1301-00-000-28713) Campaign'\n", "'Nuclear Blast USA Black Friday 2016'\n" ] } ], "source": [ "def remove_label_year_from_front_of_campaign_name(row):\n", " string = row['campaign_name']\n", " \n", " result = re.search('[a-z].[0-9]{4}.', string)\n", " if result:\n", " string = string[result.end():].strip()\n", " \n", " result = re.match('-', string)\n", "\n", " if result:\n", " string = string[result.end():]\n", " \n", " return string.strip()\n", "\n", "\n", "# Pattern: