Пример #1
0
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()
Пример #2
0
    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
Пример #3
0
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()
Пример #4
0
 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()
Пример #5
0
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
Пример #6
0
 def __init__(self):
     self.db_session = AnalysisSession()