Notebook

In [1]:

import cloudinary
import cloudinary.uploader
import cloudinary.api
import json
import pandas as pd
from google.oauth2 import service_account
import pandas_gbq

config = cloudinary.config(
        cloud_name = "###",
        api_key = "###",
        api_secret = "###"
    )

In [2]:

credentials = service_account.Credentials.from_service_account_file('data/credentials.json')

In [3]:

# Update the in-memory credentials cache (added in pandas-gbq 0.7.0).
pandas_gbq.context.credentials = credentials

sql = """
SELECT Date, Ad_Creative_Image_URL, Ad_Creative_Video_Picture_URL, Ad_Name, Data_Source_Name
FROM `idr-insights.all_cloudinary.all_cloudinary_view`
WHERE DATE(Date) BETWEEN CURRENT_DATE()-14 and CURRENT_DATE()
AND (Ad_Creative_Image_URL IS NOT NULL OR Ad_Creative_Video_Picture_URL IS NOT NULL);
"""
df = pandas_gbq.read_gbq(sql, project_id="idr-insights", progress_bar_type=None)

In [4]:

img_df = df.drop(['Ad_Creative_Video_Picture_URL'], axis=1)
vid_df = df.drop(['Ad_Creative_Image_URL'], axis=1)

In [5]:

img_df = img_df.dropna()
vid_df = vid_df.dropna()

In [6]:

img_df['Media_Type'] = "Image"
vid_df['Media_Type'] = "Video"

In [7]:

df = pd.concat([img_df, vid_df.rename(columns={'Ad_Creative_Video_Picture_URL':'Ad_Creative_Image_URL'})], ignore_index=True)

In [8]:

df = df.sort_values(['Date','Media_Type'], ascending=([False, True])).reset_index(drop=True)
df = df.drop_duplicates(subset='Ad_Name', keep='first').reset_index(drop=True)

In [9]:

df.loc[df['Data_Source_Name'].str.contains('ASO'), 'Client'] = 'aso'
df.loc[df['Data_Source_Name'].str.contains('Adaptive Sports'), 'Client'] = 'asc'
df.loc[df['Data_Source_Name'].str.contains('BOG'), 'Client'] = 'bog'
df.loc[df['Data_Source_Name'].str.contains('B&C'), 'Client'] = 'bcc'
df.loc[df['Data_Source_Name'].str.contains('CHRISTUS'), 'Client'] = 'cts'
df.loc[df['Data_Source_Name'].str.contains('Fidelity'), 'Client'] = 'fid'
df.loc[df['Data_Source_Name'].str.contains('Folds'), 'Client'] = 'fhf'
df.loc[df['Data_Source_Name'].str.contains('GOSAFE'), 'Client'] = 'gos'
df.loc[df['Data_Source_Name'].str.contains('Idea Ranch'), 'Client'] = 'idr'
df.loc[df['Data_Source_Name'].str.contains('MEAT'), 'Client'] = 'meat'
df.loc[df['Data_Source_Name'].str.contains('Meprolight'), 'Client'] = 'mep'
df.loc[df['Data_Source_Name'].str.contains('Moultrie'), 'Client'] = 'pob'
df.loc[df['Data_Source_Name'].str.contains('REDARC'), 'Client'] = 'red'
df.loc[df['Data_Source_Name'].str.contains('Splatrball'), 'Client'] = 'gam'
df.loc[df['Data_Source_Name'].str.contains('UA - Fish'), 'Client'] = 'ua'
df.loc[df['Data_Source_Name'].str.contains('UA - Freedom'), 'Client'] = 'ua'
df.loc[df['Data_Source_Name'].str.contains('UA - Hunt'), 'Client'] = 'ua'
df.loc[df['Data_Source_Name'].str.contains('Utica'), 'Client'] = 'usp'
df.loc[df['Data_Source_Name'].str.contains('Bushnell - Standard'), 'Client'] = 'bsh'
df.loc[df['Data_Source_Name'].str.contains('Bushnell Golf'), 'Client'] = 'bsh'
df.loc[df['Data_Source_Name'].str.contains('Blackhawk'), 'Client'] = 'bsh'
df.loc[df['Data_Source_Name'].str.contains('Hoppe'), 'Client'] = 'bsh'
df.loc[df['Data_Source_Name'].str.contains('RCBS'), 'Client'] = 'bsh'
df.loc[df['Data_Source_Name'].str.contains('Primos'), 'Client'] = 'bsh'
df.loc[df['Data_Source_Name'].str.contains('Wild Sheep'), 'Client'] = 'wsf'

In [10]:

df["Ad_Name"] = df["Ad_Name"].str.replace(" ", '-', regex=True)
df["Ad_Name"] = df["Ad_Name"].str.replace(".", '-', regex=True)
df["Ad_Name"] = df["Ad_Name"].str.replace("_", '-', regex=True)
df["Ad_Name"] = df["Ad_Name"].str.replace("[^a-zA-Z0-9 -]", '', regex=True)
df["Ad_Name"] = df["Ad_Name"].str.replace("---", '-', regex=True)
df["Ad_Name"] = df["Ad_Name"].str.replace("--", '-', regex=True)
df["Ad_Name"] = df["Ad_Name"].str.replace("-$", "", regex=True)

In [11]:

image_url = df['Ad_Creative_Image_URL'].to_list()
ad_name = df['Ad_Name'].to_list()
client = df['Client'].to_list()

In [12]:

for img, client, path in zip(image_url, client, ad_name):
    try:
        cloudinary.uploader.upload("%s" % img, public_id="%s/" % client + "%s" % path, unique_filename = False, overwrite = False)
    except:
        pass