print("Client list loaded!\n")
# transform data
multiple_categories = ["JD Williams"]
root = fr"C:\Users\JLee35\dentsu\iProspect Hub - Documents\Channels\Owned & Earned\Automation\STAT\Clients"
for i in client_list.index:
folder_name = client_list["Folder Name"][i]
client_name = client_list["Client Name"][i]
stat_id = client_list["STAT ID"][i]
database_name = getstat.dbize(folder_name)
save_name = getstat.scrub(client_name)
con = sqlite3.connect(os.path.join(root, folder_name, database_name))
cur = con.cursor()
print(f"Starting {client_name}...")
cur.execute(f"""CREATE TABLE IF NOT EXISTS Criteria(
Id INTEGER PRIMARY KEY,
Criterion TEXT,
UNIQUE (Criterion)
);""")
cur.execute("INSERT OR IGNORE INTO Criteria (Criterion) VALUES (?)",
("", ))
cur.execute(f"""CREATE TABLE IF NOT EXISTS VisibilityDaily_{save_name}(
print("Done!")
#%%
folder_name = "Toolstation"
client_list = client_list[client_list["Folder Name"] == folder_name]
#%% request exports for all clients
root = fr"C:\Users\JLee35\dentsu\iProspect Hub - Documents\Channels\Owned & Earned\Automation\STAT\Clients"
for i in client_list.index:
folder_name = client_list["Folder Name"][i]
client_name = client_list["Client Name"][i]
stat_id = client_list["STAT ID"][i]
csv_name = getstat.scrub(client_name) + "_keywords_list.csv"
setup = "Setup"
# create folders for client
if not os.path.exists(os.path.join(root, folder_name)):
os.mkdir(os.path.join(root, folder_name))
if not os.path.exists(os.path.join(root, folder_name, setup)):
os.mkdir(os.path.join(root, folder_name, setup))
print("\n" + f"Requesting data for {client_name}")
keywords_list = getstat.keywords_list(stat_id)
# process keywords for export
keywords_list = pd.json_normalize(keywords_list)
categories = pd.DataFrame(columns=[
"Category1", "Category2", "Category3", "Category4", "Category5"
])