class InsertCampaignTags():
def __init__(self):
self.db_session = AnalysisSession()
def insert(self, campaign_tags):
for item in campaign_tags:
self.db_session.execute(CampaignCategoryMappingTable.__table__.insert(), [
{'campaign': item['campaign'],
'team_new': item['team'],
'category_new': item['category']}
])
self.db_session.commit()
def test_connection(self):
self.db_session = AnalysisSession()
q = self.db_session.query(MonthlySubsHistoryTable)
# q = q.filter(MonthlySubsHistoryTable.subscriberid == '4e4a8c15-cb26-4b08-aa0a-4cf6c20a76b5')
results = q.count()
pass
class UnmappedCampaignTags():
def __init__(self):
self.db_session = AnalysisSession()
def get(self):
q1 = (self.db_session.query(MonthlySubsHistoryTable.campaign).
outerjoin(CampaignCategoryMappingTable, MonthlySubsHistoryTable.campaign == CampaignCategoryMappingTable.campaign).
# filter(MonthlySubsHistoryTable.date_effective >= '2015-01-01').
filter(CampaignCategoryMappingTable.campaign == None).
distinct(MonthlySubsHistoryTable.campaign).
group_by(MonthlySubsHistoryTable.campaign))
return q1.all()
def __init__(self):
self.db_session = AnalysisSession()
self.all_based_subs = self.get_all()
self.ad_based_subs = self.get_ad()
self.all_subs_list = self.get_all_list()
self.all_subs_brief = self.get_all_brief()
class GroupedOutput():
def __init__(self):
self.db_session = AnalysisSession()
self.all_based_subs = self.get_all()
self.ad_based_subs = self.get_ad()
self.all_subs_list = self.get_all_list()
self.all_subs_brief = self.get_all_brief()
def get_all(self):
q1 = (self.db_session.query(func.count(MonthlySubsHistoryTable.subscriberid).label('subs'),
CampaignCategoryMappingTable.team_new,
CampaignCategoryMappingTable.category_new,
extract('month', MonthlySubsHistoryTable.date_effective).label('month'),
extract('year', MonthlySubsHistoryTable.date_effective).label('year')).
outerjoin(CampaignCategoryMappingTable, MonthlySubsHistoryTable.campaign == CampaignCategoryMappingTable.campaign).
filter(MonthlySubsHistoryTable.briefid == ActiveBriefsTable.briefid).
filter(extract('month', MonthlySubsHistoryTable.date_effective) == extract('month', ActiveBriefsTable.date_effective)).
filter(MonthlySubsHistoryTable.date_effective >= '2015-01-01').group_by(CampaignCategoryMappingTable.team_new,
CampaignCategoryMappingTable.category_new,
extract('month', MonthlySubsHistoryTable.date_effective).label('year'),
extract('year', MonthlySubsHistoryTable.date_effective).label('year')).all())
list_of_dict_all = [item.__dict__ for item in q1]
all_subs = pd.DataFrame(list_of_dict_all)
return all_subs
def get_ad(self):
q2 = (self.db_session.query(func.count(MonthlySubsHistoryTable.subscriberid).label('subs_ad_based'),
CampaignCategoryMappingTable.team_new,
CampaignCategoryMappingTable.category_new,
extract('month', MonthlySubsHistoryTable.date_effective).label('month'),
extract('year', MonthlySubsHistoryTable.date_effective).label('year')).
outerjoin(CampaignCategoryMappingTable, MonthlySubsHistoryTable.campaign == CampaignCategoryMappingTable.campaign).
filter(MonthlySubsHistoryTable.briefid == ActiveBriefsTable.briefid).
filter(extract('month', MonthlySubsHistoryTable.date_effective) == extract('month', ActiveBriefsTable.date_effective)).
filter(MonthlySubsHistoryTable.date_effective >= '2015-01-01').
filter(or_(ActiveBriefsTable.brief_tags.like('%Voodoo%'),
ActiveBriefsTable.brief_tags.like('%Ad Based%'))).group_by(CampaignCategoryMappingTable.team_new,
CampaignCategoryMappingTable.category_new,
extract('month', MonthlySubsHistoryTable.date_effective),
extract('year', MonthlySubsHistoryTable.date_effective).label('year')).all())
list_of_dict_ad = [item.__dict__ for item in q2]
ad_subs = pd.DataFrame(list_of_dict_ad)
return ad_subs
def get_all_brief(self):
q2 = (self.db_session.query(func.count(MonthlySubsHistoryTable.subscriberid).label('subs_ad_based'),
ActiveBriefsTable.brief_name,
extract('month', MonthlySubsHistoryTable.date_effective).label('month'),
extract('year', MonthlySubsHistoryTable.date_effective).label('year')).
outerjoin(CampaignCategoryMappingTable, MonthlySubsHistoryTable.campaign == CampaignCategoryMappingTable.campaign).
filter(MonthlySubsHistoryTable.briefid == ActiveBriefsTable.briefid).
filter(extract('month', MonthlySubsHistoryTable.date_effective) == extract('month', ActiveBriefsTable.date_effective)).
filter(MonthlySubsHistoryTable.date_effective >= '2015-01-01').group_by(ActiveBriefsTable.brief_name,
extract('month', MonthlySubsHistoryTable.date_effective),
extract('year', MonthlySubsHistoryTable.date_effective).label('year')).all())
list_of_dict_ad = [item.__dict__ for item in q2]
ad_subs_brief = pd.DataFrame(list_of_dict_ad)
return ad_subs_brief
def get_all_list(self):
q1 = (self.db_session.query(func.count(MonthlySubsHistoryTable.subscriberid).label('subs'),
MonthlySubsHistoryTable.campaign,
CampaignCategoryMappingTable.team_new,
CampaignCategoryMappingTable.category_new,
extract('month', MonthlySubsHistoryTable.date_effective).label('month'),
extract('year', MonthlySubsHistoryTable.date_effective).label('year')).
outerjoin(CampaignCategoryMappingTable, MonthlySubsHistoryTable.campaign == CampaignCategoryMappingTable.campaign).
filter(MonthlySubsHistoryTable.briefid == ActiveBriefsTable.briefid).
filter(extract('month', MonthlySubsHistoryTable.date_effective) == extract('month', ActiveBriefsTable.date_effective)).
filter(MonthlySubsHistoryTable.date_effective >= '2015-01-01').group_by(MonthlySubsHistoryTable.campaign,
CampaignCategoryMappingTable.team_new,
CampaignCategoryMappingTable.category_new,
extract('month', MonthlySubsHistoryTable.date_effective),
extract('year', MonthlySubsHistoryTable.date_effective)).all())
list_of_dict_all = [item.__dict__ for item in q1]
all_subs = pd.DataFrame(list_of_dict_all)
return all_subs
#todo make sure year parsing works
def process_output(self):
data = pd.merge(self.ad_based_subs, self.all_based_subs, how='outer')
sub_count = data.pivot_table(index=['team_new', 'category_new'], columns=['year', 'month'], values=['subs', 'subs_ad_based'], aggfunc='sum')
sub_count = pd.DataFrame(sub_count)
return sub_count
def list_output(self):
data = self.all_subs_list
# last_months_data = data[max(data['month'])] #todo last months data
# .sort(columns=['year', 'month'], ascending=False) # todo get sort to work and subset for current month
return data
def brief_output(self):
data = self.all_subs_brief
sub_count = data.pivot_table(index=['brief_name'], columns=['year', 'month'], values=['subs'], aggfunc='sum')
return sub_count
def __init__(self):
self.db_session = AnalysisSession()