def compile_data_view(run_social: bool = False,
run_search: bool = True,
company_name: str = None,
start_date: str = None,
end_date: str = None,
demo: bool = False,
ltv: float = None,
get_opps: bool = True):
orm = GoogleORM(company_name)
if not demo:
async def dataframes(run_social, run_search):
if run_search:
search_df = loop.run_in_executor(None, orm.search_index,
start_date, end_date)
search = await search_df
if get_opps:
_opportunities = loop.run_in_executor(None, orm.keywords)
opps = await _opportunities
else:
opps = None
else:
search_df = None
search = None
opps = None
if run_social:
social_df = loop.run_in_executor(None, orm.social_index,
start_date, end_date)
social = await social_df
else:
social_df = None
social = None
return search, social, opps
asyncio.set_event_loop(asyncio.new_event_loop())
loop = asyncio.get_event_loop()
search_df, social_df, opps = loop.run_until_complete(
dataframes(run_social, run_search))
else:
ltv = 400
new_start = start_date.replace(" UTC", ".000")
new_end = end_date.replace(" UTC", ".000")
search_query = f"SELECT * FROM demo_data_search where date_start between '{new_start}' and '{new_end}'"
social_query = f"SELECT * FROM demo_data_social where date_start between '{new_start}' and '{new_end}'"
search_df = db.sql_to_df(search_query)
social_df = db.sql_to_df(social_query)
opps = orm.keywords()
return {'search_df': search_df, 'social_df': social_df, 'topic_opps': opps}
def past_inputs(page, user, view_id=None):
intake_pages = ['begin', 'competitors', 'company', 'competitors', 'audience', 'product', 'product_2', 'salescycle', 'goals', 'history', 'platforms', 'past', 'creative']
if page in intake_pages:
if page == 'begin':
query = "SELECT first_name, last_name, company_name, revenue, employees, zip, stage, website FROM dbo.customer_basic WHERE ID = %s" % (user,)
result = db.sql_to_df(query)
elif page == 'salescycle':
awareness = db.sql_to_df("select distinct tactic from dbo.awareness WHERE customer_id=%d" % (user,))
awareness.insert(loc=0, column='stage', value='awareness')
evaluation = db.sql_to_df("select distinct tactic from dbo.evaluation WHERE customer_id=%d" % (user,))
evaluation.insert(loc=0, column='stage', value='evaluation')
conversion = db.sql_to_df("select distinct tactic from dbo.conversion WHERE customer_id=%d" % (user,))
conversion.insert(loc=0, column='stage', value='conversion')
retention = db.sql_to_df("select distinct tactic from dbo.retention WHERE customer_id=%d" % (user,))
retention.insert(loc=0, column='stage', value='retention')
referral = db.sql_to_df("select distinct tactic from dbo.referral WHERE customer_id=%d" % (user,))
referral.insert(loc=0, column='stage', value='referral')
stages = [awareness, evaluation, conversion, retention, referral]
result = pd.concat(stages)
elif page == 'audience':
query = "SELECT * FROM dbo.audience WHERE customer_id = %s and audience_id = %s" % (user, view_id)
result = db.sql_to_df(query)
elif page == 'product_2':
query = "SELECT * FROM dbo.product_list WHERE customer_id = %d and p_id = %s" % (user,view_id)
result = db.sql_to_df(query)
elif page == 'past':
query = "SELECT freeform FROM dbo.past WHERE customer_id = %d" % (user,)
result = db.sql_to_df(query)
elif page == 'creative':
result = 'nah'
elif page == 'splash':
result = 'nah'
else:
query = "SELECT * FROM dbo.%s WHERE customer_id = %d" % (page, user)
result = db.sql_to_df(query)
return result
else:
return False
def customer_login(email, password):
try:
tup = (email, )
query = """
select * from all_co_users(?)
order by id desc
"""
data, cursor = db.execute(query, True, tup)
data = cursor.fetchall()
cursor.close()
pw = data[0][1]
uid = data[0][2]
email_confirmed = data[0][3]
first_name = data[0][4]
last_name = data[0][5]
if sha256_crypt.verify(password, pw):
# if email_confirmed == 1:
session['prev_log_in'] = data[0][6]
session['logged_in'] = True
session['customer'] = True
session['email'] = data[0][0]
session['user'] = int(uid)
session['stripe_id'] = data[0][7]
session['company_name'] = data[0][8]
session['user_name'] = "%s %s" % (first_name, last_name)
session.permanent = True
session.remember = True
st = UserService.now()
last_logged_in = """UPDATE customer_basic SET last_logged_in = ? WHERE id = ?"""
db.execute(last_logged_in, False, (st, int(uid)), commit=True)
first_query = db.sql_to_df(
"SELECT first_name FROM dbo.customer_basic WHERE ID = '" +
str(session['user']) + "'")
if first_query['first_name'][0] == None:
session['onboarding_complete'] = False
return True, 'begin'
else:
step = load_last_page(session['user'])
return True, step
# else:
# tup = ("begin",)
# query = "SELECT heading, paragraph FROM dbo.splash WHERE after_page = ?"
# data, cursor = db.execute(query, True, tup)
# heading, paragraph = cursor.fetchone()
# heading = heading.replace("`", "'")
# paragraph = paragraph.replace("`", "'")
# return True, 'verify_email'
else:
error = "Invalid credentials. Try again."
return False, error
except Exception as e:
error = "Invalid credentials. Try again!"
return False, error
def load_admin():
results = db.sql_to_df(
'SELECT customer_basic.id, customer_basic.company_name, admins.first_name FROM customer_basic, admins WHERE admins.ID = '
+ str(session['admin']))
results = results.to_json(orient='records')
return results
def clean(self):
df = db.sql_to_df(f'exec prep_marketr_score @customer_id = {self.customer_id}')
df['html_name'] = df['html_name'].apply(self.pop_suffix)
df.drop_duplicates(subset="html_name", keep = 'first', inplace=True)
self.df = df
self.total_possible = df['score_weight_factor'].sum()
self.sum_completed = df[df['answer'] != 'null'].score_weight_factor.sum()
def get_reference(self):
df = db.sql_to_df(
f"select * from campaign_reference where customer_id = {self.customer_id} order by claimed desc"
)
return df
def admin_availability():
result = db.sql_to_df('select email from dbo.admins')
result = result.to_json(orient='records')
return result
def home(self) -> None:
query = db.sql_to_df("SELECT customer_basic.id, customer_basic.company_name, customer_basic.account_created, customer_basic.perc_complete, customer_basic.last_modified, admins.first_name FROM customer_basic, admins WHERE admins.ID = '" + self.admin + "' ORDER BY company_name ASC")
self.data = query