from datetime import date from utils.snowflake.metrics.schema import MetricsFilters METRICS_LIST_FIELDS = { "clicks_value": "COALESCE(report.CLICKS, ad_report.CLICKS)", "spend_value": "COALESCE(report.SPEND, ad_report.cost)", "ctr_value": "COALESCE(report.CTR, ad_report.CTR)", "reach_value": "COALESCE(report.REACH, c_report.campaign_reach)", "cpm_value": "COALESCE(report.CPM, ad_report.AVERAGE_CPM)", "engagements_value": "COALESCE(report.INLINE_POST_ENGAGEMENT, ad_report.ENGAGEMENTS)", "impressions_value": "COALESCE(report.IMPRESSIONS, ad_report.IMPRESSIONS)", "conversions_value": "COALESCE(conversions_report.value, ad_report.conversions)", "start_date_value": """ COALESCE( COALESCE(adset_history.f_start_date, c_report.start_date), COALESCE(report.fallback_fb_start_date, ad_report.fallback_google_start_date) )""", "end_date_value": """ COALESCE( COALESCE(adset_history.f_end_date, c_report.end_date), COALESCE(report.fallback_fb_end_date, ad_report.fallback_google_end_date) )""", "frequency_value": "COALESCE(report.FREQUENCY, c_report.frequency)", "video_25_watched_value": "COALESCE(video_watched_report.video_25_val / video_views_report.value, ad_report.VIDEO_QUARTILE_25_RATE)", "video_50_watched_value": "COALESCE(video_watched_report.video_50_val / video_views_report.value, ad_report.VIDEO_QUARTILE_50_RATE)", "video_75_watched_value": "COALESCE(video_watched_report.video_75_val / video_views_report.value, ad_report.VIDEO_QUARTILE_75_RATE)", "video_95_watched_value": "COALESCE(video_watched_report.video_100_val / video_views_report.value, ad_report.VIDEO_QUARTILE_100_RATE)", "video_views_value": "COALESCE(video_views_report.value, ad_report.video_views)", "video_view_rate_value": "ad_report.video_view_rate", "thruplays_value": "thruplays_report.value", "u_outbound_clicks_value": "u_outbound_clicks_report.value", "u_outbound_clicks_ctr_value": "u_outbound_clicks_ctr_report.value", "custom_conversions_value": "actions_report.custom_conversions", "landing_page_views_value": "actions_report.landing_page_views", "leads_value": "actions_report.leads", "reactions_value": "actions_report.reactions", "u_custom_conversions_value": "unique_actions_report.u_custom_conversions", "u_conversions_value": "unique_actions_report.u_conversions", "fallback_start_date_value": """ COALESCE( c_report.start_date, COALESCE(report.fallback_fb_start_date, ad_report.fallback_google_start_date)) """, "fallback_end_date_value": """ COALESCE( c_report.end_date, COALESCE(report.fallback_fb_end_date, ad_report.fallback_google_end_date) ) """, } class UtilsQueries: @staticmethod def filters_by(filters: MetricsFilters, table_alias: str = "report"): start_date = date.strftime(filters.dates.start, "%Y-%m-%d") end_date = date.strftime(filters.dates.end, "%Y-%m-%d") result = f"{table_alias}.DATE BETWEEN '{start_date}' AND '{end_date}'" return result @staticmethod def order_by(filters: MetricsFilters): return filters.sort @staticmethod def fields_mapping(key): return METRICS_LIST_FIELDS.get(key) @staticmethod def default_breakdown_field_by_entity(): return """ SUM(ad_report.cost) as spend, SUM(ad_report.video_views) as video_views, SUM(ad_report.IMPRESSIONS) as impressions, SUM(ad_report.conversions) as conversions, AVG(c_report.frequency) as frequency, SUM(ad_report.CLICKS) as clicks, CASE SUM(ad_report.conversions) WHEN 0 THEN 0 ELSE (spend / SUM(ad_report.conversions)) END as cost_per_conversion, SUM(c_report.campaign_reach) as reach, AVG(ad_report.CTR) as ctr, SUM(ad_report.ENGAGEMENTS) as engagements, CASE SUM(ad_report.ENGAGEMENTS) WHEN 0 THEN 0 ELSE spend / SUM(ad_report.ENGAGEMENTS) END as cost_per_engagement, CASE SUM(ad_report.CLICKS) WHEN 0 THEN 0 ELSE spend / SUM(ad_report.CLICKS) END as cost_per_click, AVG(ad_report.VIDEO_QUARTILE_25_RATE) / 100 as video_25_watched, AVG(ad_report.VIDEO_QUARTILE_50_RATE) / 100 as video_50_watched, AVG(ad_report.VIDEO_QUARTILE_75_RATE) / 100 as video_75_watched, AVG(ad_report.VIDEO_QUARTILE_100_RATE) / 100 as video_100_watched, AVG(ad_report.AVERAGE_CPM) as cpm, CASE reach WHEN 0 THEN 0 ELSE (spend / reach / 1000) END as cpm_reach, AVG(ad_report.video_view_rate) as video_view_rate, CASE SUM(ad_report.video_views) WHEN 0 THEN 0 ELSE spend / SUM(ad_report.video_views) END as cost_per_video_view, MIN(c_report.start_date) as start_date, MAX(c_report.end_date) as end_date """ @staticmethod def calculate_cost_per(spend: str, divisor: str): return f""" CASE {divisor} WHEN 0 THEN 0 ELSE ({spend} / {divisor}) END """ @staticmethod def default_combined_result_fields(use_fb_history: bool = False): start_date_field = "start_date_value" end_date_field = "end_date_value" if use_fb_history: start_date_field = "fallback_start_date_value" end_date_field = "fallback_end_date_value" return f""" -- GOOGLE & FACEBOOK METRICS SUM({UtilsQueries.fields_mapping("spend_value")}) as spend_value, SUM({UtilsQueries.fields_mapping("video_views_value")}) as video_views_value, SUM({UtilsQueries.fields_mapping("impressions_value")}) as impressions_value, SUM({UtilsQueries.fields_mapping("conversions_value")}) as conversions_value, AVG({UtilsQueries.fields_mapping("frequency_value")}) as frequency_value, SUM({UtilsQueries.fields_mapping("clicks_value")}) as clicks_value, {UtilsQueries.calculate_cost_per("spend_value", "conversions_value")} as cost_per_conversion_value, SUM({UtilsQueries.fields_mapping("reach_value")}) as reach_value, AVG({UtilsQueries.fields_mapping("ctr_value")}) as ctr_value, SUM({UtilsQueries.fields_mapping("engagements_value")}) as engagements_value, {UtilsQueries.calculate_cost_per("spend_value", "engagements_value")} as cost_per_engagement_value, {UtilsQueries.calculate_cost_per("spend_value", "clicks_value")} as cost_per_click_value, AVG({UtilsQueries.fields_mapping("video_25_watched_value")}) as video_25_watched_value, AVG({UtilsQueries.fields_mapping("video_50_watched_value")}) as video_50_watched_value, AVG({UtilsQueries.fields_mapping("video_75_watched_value")}) as video_75_watched_value, AVG({UtilsQueries.fields_mapping("video_95_watched_value")}) as video_95_watched_value, CASE spend_value WHEN 0 THEN 0 ELSE DIV0(spend_value, DIV0(impressions_value, 1000)) END as cpm_value, CASE spend_value WHEN 0 THEN 0 ELSE DIV0(spend_value, DIV0(reach_value, 1000)) END as cpm_reach_value, AVG({UtilsQueries.fields_mapping("video_view_rate_value")}) as video_view_rate_value, {UtilsQueries.calculate_cost_per("spend_value", "video_views_value")} as cost_per_video_view_value, MIN({UtilsQueries.fields_mapping(start_date_field)}) as start_date_value, MAX({UtilsQueries.fields_mapping(end_date_field)}) as end_date_value, -- FACEBOOK METRICS ONLY SUM({UtilsQueries.fields_mapping("thruplays_value")}) as thruplays_value, SUM({UtilsQueries.fields_mapping("u_outbound_clicks_value")}) as u_outbound_clicks_value, AVG({UtilsQueries.fields_mapping("u_outbound_clicks_ctr_value")}) as u_outbound_clicks_ctr_value, SUM({UtilsQueries.fields_mapping("custom_conversions_value")}) as custom_conversions_value, SUM({UtilsQueries.fields_mapping("u_custom_conversions_value")}) as u_custom_conversions_value, SUM({UtilsQueries.fields_mapping("landing_page_views_value")}) as landing_page_views_value, SUM({UtilsQueries.fields_mapping("leads_value")}) as leads_value, SUM({UtilsQueries.fields_mapping("reactions_value")}) as reactions_value, SUM({UtilsQueries.fields_mapping("u_conversions_value")}) as u_conversions_value, {UtilsQueries.calculate_cost_per("spend_value", "thruplays_value")} as cost_per_thurplays_value, {UtilsQueries.calculate_cost_per( "spend_value", "u_outbound_clicks_value" )} as cost_per_u_outbound_clicks_value, {UtilsQueries.calculate_cost_per( "spend_value", "custom_conversions_value" )} as cost_per_custom_conversions_value, {UtilsQueries.calculate_cost_per( "spend_value", "u_custom_conversions_value" )} as cost_per_u_custom_conversions_value, {UtilsQueries.calculate_cost_per( "spend_value", "landing_page_views_value" )} as cost_per_landing_page_views_value """ @staticmethod def default_reporting_fields(): return f""" -- SPEND SUM(report.SPEND) as facebook_spend_value, SUM(ad_report.cost) as google_spend_value, SUM({UtilsQueries.fields_mapping("spend_value")}) as spend_value, -- VIDEO VIEWS SUM({UtilsQueries.fields_mapping("thruplays_value")}) as facebook_thruplays_value, SUM(video_views_report.value) as facebook_video_views, SUM(ad_report.video_views) as google_video_views, SUM({UtilsQueries.fields_mapping("video_views_value")}) as video_views_value, -- IMPRESSIONS SUM(report.IMPRESSIONS) as facebook_impressions_value, SUM(ad_report.IMPRESSIONS) as google_impressions_value, SUM({UtilsQueries.fields_mapping("impressions_value")}) as impressions_value, -- CONVERSIONS SUM({UtilsQueries.fields_mapping("conversions_value")}) as conversions_value, -- CLICKS SUM(report.CLICKS) as facebook_clicks_value, SUM(ad_report.CLICKS) as google_clicks_value, SUM({UtilsQueries.fields_mapping("clicks_value")}) as clicks_value, -- REACH SUM({UtilsQueries.fields_mapping("reach_value")}) as reach_value, -- ENGAGEMENT SUM({UtilsQueries.fields_mapping("reactions_value")}) as facebook_reactions_value, SUM(ad_report.ENGAGEMENTS) as google_engagements_value, SUM(actions_report.comments) as facebook_comments_value, SUM({UtilsQueries.fields_mapping("engagements_value")}) as engagements_value, -- CPM CASE spend_value WHEN 0 THEN 0 ELSE DIV0( facebook_spend_value, DIV0(facebook_impressions_value, 1000) ) END as facebook_cpm_value, CASE spend_value WHEN 0 THEN 0 ELSE DIV0( google_spend_value, DIV0(google_impressions_value, 1000) ) END as google_cpm_value, CASE spend_value WHEN 0 THEN 0 ELSE DIV0(spend_value, DIV0(impressions_value, 1000)) END as cpm_value """ @staticmethod def metrics_with_totals(entity_id_field: str): return f""" {entity_id_field} as entity_id, {UtilsQueries.default_combined_result_fields()}, COUNT({entity_id_field}) OVER () as entities_count, -- GOOGLE & FACEBOOK METRICS SUM(spend_value) OVER () as total_spend_value, SUM(video_views_value) OVER () as total_video_views_value, SUM(impressions_value) OVER () as total_impressions_value, SUM(conversions_value) OVER () as total_conversions_value, AVG(frequency_value) OVER () as total_frequency_value, SUM(clicks_value) OVER () as total_clicks_value, AVG(cost_per_conversion_value) OVER () as total_cost_per_conversion_value, SUM(reach_value) OVER () as total_reach_value, AVG(ctr_value) OVER () as total_ctr_value, SUM(engagements_value) OVER () as total_engagements_value, AVG(cost_per_engagement_value) OVER () as total_cost_per_engagement_value, AVG(cost_per_click_value) OVER () as total_cost_per_click_value, AVG(video_25_watched_value) OVER () as total_video_25_watched_value, AVG(video_50_watched_value) OVER () as total_video_50_watched_value, AVG(video_75_watched_value) OVER () as total_video_75_watched_value, AVG(video_95_watched_value) OVER () as total_video_95_watched_value, AVG(cpm_value) OVER () as total_cpm_value, AVG(cpm_reach_value) OVER () total_cpm_reach_value, AVG(video_view_rate_value) OVER () as total_video_view_rate_value, AVG(cost_per_video_view_value) OVER () as total_cost_per_video_view_value, MIN(start_date_value) OVER () as total_start_date_value, MAX(end_date_value) OVER () as total_end_date_value, -- FACEBOOK METRICS ONLY SUM(thruplays_value) OVER () as total_thruplays_value, SUM(u_outbound_clicks_value) OVER () as total_u_outbound_clicks_value, AVG(u_outbound_clicks_ctr_value) OVER () as total_u_outbound_clicks_ctr_value, SUM(custom_conversions_value) OVER () as total_custom_conversions_value, SUM(u_custom_conversions_value) OVER () as total_u_custom_conversions_value, SUM(landing_page_views_value) OVER () as total_landing_page_views_value, SUM(leads_value) OVER () as total_leads_value, SUM(reactions_value) OVER () as total_reactions_value, SUM(u_conversions_value) OVER () as total_u_conversions_value, AVG(cost_per_thurplays_value) OVER () as total_cost_per_thurplays_value, AVG(cost_per_u_outbound_clicks_value) OVER () as total_cost_per_u_outbound_clicks_value, AVG(cost_per_custom_conversions_value) OVER () as total_cost_per_custom_conversions_value, AVG(cost_per_u_custom_conversions_value) OVER () as total_cost_per_u_custom_conversions_value, AVG(cost_per_landing_page_views_value) OVER () as total_cost_per_landing_page_views_value """ @staticmethod def dsp_age_gender_joins(filter_by: str): return f""" LEFT JOIN ({FacebookUtilsQueries.age_and_gender_report(filter_by)}) as report ON report.CAMPAIGN_ID = project_campaigns.campaign_id LEFT JOIN ({FacebookUtilsQueries.adset_history_report()}) as adset_history ON adset_history.CAMPAIGN_ID = project_campaigns.CAMPAIGN_ID LEFT JOIN ({GoogleUtilsQueries.ad_performance_report(filter_by)}) as ad_report ON ad_report.CAMPAIGN_ID = project_campaigns.campaign_id LEFT JOIN ({FacebookUtilsQueries.age_gender_actions_report(filter_by)}) as actions_report ON actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID LEFT JOIN ({FacebookUtilsQueries.age_gender_unique_actions_report(filter_by)}) as unique_actions_report ON unique_actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID LEFT JOIN ({FacebookUtilsQueries.age_gender_thruplays_report(filter_by)}) as thruplays_report ON thruplays_report.CAMPAIGN_ID = report.CAMPAIGN_ID LEFT JOIN ({FacebookUtilsQueries.age_gender_u_outbound_clicks_report(filter_by)}) as u_outbound_clicks_report ON u_outbound_clicks_report.CAMPAIGN_ID = report.CAMPAIGN_ID LEFT JOIN ({FacebookUtilsQueries.age_gender_u_outbound_clicks_ctr_report(filter_by)}) as u_outbound_clicks_ctr_report ON u_outbound_clicks_ctr_report.CAMPAIGN_ID = report.CAMPAIGN_ID LEFT JOIN ({FacebookUtilsQueries.age_gender_conversions_report(filter_by)}) as conversions_report ON conversions_report.CAMPAIGN_ID = report.CAMPAIGN_ID LEFT JOIN ({FacebookUtilsQueries.age_gender_video_views_report(filter_by)}) as video_views_report ON video_views_report.CAMPAIGN_ID = report.CAMPAIGN_ID LEFT JOIN ({FacebookUtilsQueries.age_gender_video_watched_report(filter_by)}) as video_watched_report ON video_watched_report.CAMPAIGN_ID = report.CAMPAIGN_ID LEFT JOIN ({GoogleUtilsQueries.campaign_report_query(filter_by)}) as c_report ON c_report.CAMPAIGN_ID = ad_report.CAMPAIGN_ID """ @staticmethod def entity_campaigns_query(entity_campaigns: list): projects = [ "({}, '{}', {})".format( getattr(item, "entity_id", "NULL"), getattr(item, "entity_name", "NULL").replace("'", "\\'"), getattr(item, "campaign_id", "NULL"), ) for item in entity_campaigns ] return f"""SELECT * FROM ( VALUES {",".join(projects)} ) AS q (entity_id, entity_name, campaign_id) """ @staticmethod def projects_artists_query(project_artists: list): artists = [ "({}, {}, '{}')".format( getattr(item, "project_id", "NULL"), getattr(item, "artist_id", "NULL"), getattr(item, "artist_name", "NULL").replace("'", "\\'"), ) for item in project_artists ] return f""" SELECT * FROM ( VALUES {",".join(artists)} ) AS q (project_id, artist_id, artist_name) """ class GoogleUtilsQueries: @staticmethod def campaign_report_query(filters_by: str): return f""" SELECT report.CAMPAIGN_ID, array_agg(DISTINCT report.BIDDING_STRATEGY_TYPE) as objectives, AVG(report.AVERAGE_FREQUENCY) as frequency, SUM(report.IMPRESSION_REACH) as campaign_reach, MIN(report.start_date) as start_date, MAX(report.end_date) as end_date FROM "V_GOOGLE_CAMPAIGN_GROUP_PERFORMANCE_REPORT" as report JOIN PROJECT_CAMPAIGN ON PROJECT_CAMPAIGN.campaign_id = report.CAMPAIGN_ID WHERE {filters_by} GROUP BY report.CAMPAIGN_ID """ @staticmethod def ad_performance_report(filters_by: str): return f""" SELECT report.CAMPAIGN_ID, report.CAMPAIGN_NAME, SUM(report.CLICKS) as CLICKS, SUM(report.cost) as cost, AVG(report.CTR) as CTR, AVG(report.AVERAGE_CPM) as AVERAGE_CPM, SUM(report.ENGAGEMENTS) as ENGAGEMENTS, SUM(report.IMPRESSIONS) as IMPRESSIONS, SUM(report.conversions) as conversions, AVG(report.VIDEO_QUARTILE_25_RATE) / 100 as VIDEO_QUARTILE_25_RATE, AVG(report.VIDEO_QUARTILE_50_RATE) / 100 as VIDEO_QUARTILE_50_RATE, AVG(report.VIDEO_QUARTILE_75_RATE) / 100 as VIDEO_QUARTILE_75_RATE, AVG(report.VIDEO_QUARTILE_100_RATE) / 100 as VIDEO_QUARTILE_100_RATE, SUM(report.video_views) as video_views, AVG(report.video_view_rate) as video_view_rate, MIN(report.DATE) as fallback_google_start_date, MAX(report.DATE) as fallback_google_end_date FROM V_GOOGLE_AD_PERFORMANCE_REPORT as report JOIN PROJECT_CAMPAIGN ON PROJECT_CAMPAIGN.campaign_id = report.CAMPAIGN_ID WHERE {filters_by} GROUP BY report.CAMPAIGN_ID, report.CAMPAIGN_NAME """ class FacebookUtilsQueries: @staticmethod def age_gender_campaign_adsets(filters_by: str): return f""" SELECT DISTINCT report.CAMPAIGN_ID, report.ADSET_ID, report.AGE, report.GENDER, report.DATE FROM V_FACEBOOK_AGE_GENDER_DAILY_REPORT as report JOIN PROJECT_CAMPAIGN ON PROJECT_CAMPAIGN.campaign_id = report.CAMPAIGN_ID WHERE {filters_by} GROUP BY report.CAMPAIGN_ID, report.ADSET_ID, report.AGE, report.GENDER, report.DATE """ @staticmethod def age_gender_video_watched_report(filters_by: str): return f""" SELECT report.CAMPAIGN_ID, SUM(report_25.VALUE) as video_25_val, SUM(report_50.VALUE) as video_50_val, SUM(report_75.VALUE) as video_75_val, SUM(report_100.VALUE) as video_100_val FROM campaign_adsets as report LEFT JOIN V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_P_25_WATCHED_ACTIONS report_25 ON ( report.ADSET_ID = report_25.ADSET_ID AND report.AGE = report_25.AGE AND report.GENDER = report_25.GENDER AND report.DATE = report_25.DATE ) LEFT JOIN V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_P_50_WATCHED_ACTIONS report_50 ON ( report.ADSET_ID = report_50.ADSET_ID AND report.AGE = report_50.AGE AND report.GENDER = report_50.GENDER AND report.DATE = report_50.DATE ) LEFT JOIN V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_P_75_WATCHED_ACTIONS report_75 ON ( report.ADSET_ID = report_75.ADSET_ID AND report.AGE = report_75.AGE AND report.GENDER = report_75.GENDER AND report.DATE = report_75.DATE ) LEFT JOIN V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_P_100_WATCHED_ACTIONS report_100 ON ( report.ADSET_ID = report_100.ADSET_ID AND report.AGE = report_100.AGE AND report.GENDER = report_100.GENDER AND report.DATE = report_100.DATE ) WHERE {filters_by} GROUP BY report.CAMPAIGN_ID """ @staticmethod def age_gender_thruplays_report(filters_by: str): return f""" SELECT campaign_adsets.CAMPAIGN_ID, SUM(report.VALUE) as value FROM V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_THRUPLAY_WATCHED_ACTIONS report JOIN campaign_adsets on ( campaign_adsets.ADSET_ID = report.ADSET_ID AND campaign_adsets.AGE = report.AGE AND campaign_adsets.GENDER = report.GENDER AND campaign_adsets.DATE = report.DATE ) WHERE {filters_by} GROUP BY campaign_adsets.CAMPAIGN_ID """ @staticmethod def age_gender_u_outbound_clicks_report(filters_by: str): return f""" SELECT campaign_adsets.CAMPAIGN_ID, SUM(report.VALUE) as value FROM V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_OUTBOUND_CLICKS report JOIN campaign_adsets on ( campaign_adsets.ADSET_ID = report.ADSET_ID AND campaign_adsets.AGE = report.AGE AND campaign_adsets.GENDER = report.GENDER AND campaign_adsets.DATE = report.DATE ) WHERE {filters_by} GROUP BY campaign_adsets.CAMPAIGN_ID """ @staticmethod def age_gender_u_outbound_clicks_ctr_report(filters_by: str): return f""" SELECT campaign_adsets.CAMPAIGN_ID, AVG(report.VALUE) as value FROM V_FACEBOOK_AGE_GENDER_DAILY_REPORT_OUTBOUND_CLICKS_CTR report JOIN campaign_adsets on ( campaign_adsets.ADSET_ID = report.ADSET_ID AND campaign_adsets.AGE = report.AGE AND campaign_adsets.GENDER = report.GENDER AND campaign_adsets.DATE = report.DATE ) WHERE {filters_by} GROUP BY campaign_adsets.CAMPAIGN_ID """ @staticmethod def age_gender_video_views_report(filters_by: str): return f""" SELECT campaign_adsets.CAMPAIGN_ID, SUM(report.VALUE) as value FROM V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_PLAY_ACTIONS report JOIN campaign_adsets on ( campaign_adsets.ADSET_ID = report.ADSET_ID AND campaign_adsets.AGE = report.AGE AND campaign_adsets.GENDER = report.GENDER AND campaign_adsets.DATE = report.DATE ) WHERE {filters_by} GROUP BY campaign_adsets.CAMPAIGN_ID """ @staticmethod def age_gender_conversions_report(filters_by: str): return f""" SELECT campaign_adsets.CAMPAIGN_ID, SUM(report.VALUE) as value FROM V_FACEBOOK_AGE_GENDER_DAILY_REPORT_CONVERSIONS report JOIN campaign_adsets on ( campaign_adsets.ADSET_ID = report.ADSET_ID AND campaign_adsets.AGE = report.AGE AND campaign_adsets.GENDER = report.GENDER AND campaign_adsets.DATE = report.DATE ) WHERE {filters_by} GROUP BY campaign_adsets.CAMPAIGN_ID """ @staticmethod def age_gender_actions_report(filters_by: str): return f""" SELECT campaign_adsets.CAMPAIGN_ID, SUM(CASE when report.ACTION_TYPE = 'offsite_conversion.fb_pixel_custom' then report.value ELSE 0 END) as custom_conversions, SUM( CASE when report.ACTION_TYPE = 'landing_page_view' then report.value ELSE 0 END) as landing_page_views, SUM(CASE when report.ACTION_TYPE = 'lead' then report.value ELSE 0 END) as leads, SUM(CASE when report.ACTION_TYPE = 'post_reaction' then report.value ELSE 0 END) as reactions FROM V_FACEBOOK_AGE_GENDER_DAILY_REPORT_ACTIONS report JOIN campaign_adsets on ( campaign_adsets.ADSET_ID = report.ADSET_ID AND campaign_adsets.AGE = report.AGE AND campaign_adsets.GENDER = report.GENDER AND campaign_adsets.DATE = report.DATE ) WHERE report.ACTION_TYPE IN ( 'offsite_conversion.fb_pixel_custom', 'lead', 'post_reaction', 'landing_page_view' ) AND {filters_by} GROUP BY campaign_adsets.campaign_id """ @staticmethod def age_gender_unique_actions_report(filters_by: str): return f""" SELECT campaign_adsets.CAMPAIGN_ID, SUM(CASE when report.ACTION_TYPE = 'offsite_conversion.fb_pixel_custom' then report.value ELSE 0 END) as u_custom_conversions, SUM( CASE when CONTAINS(report.ACTION_TYPE, 'offsite_conversion') OR CONTAINS(report.ACTION_TYPE, 'onsite_conversion') then report.value ELSE 0 END) as u_conversions FROM V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_ACTIONS report JOIN campaign_adsets on ( campaign_adsets.ADSET_ID = report.ADSET_ID AND campaign_adsets.AGE = report.AGE AND campaign_adsets.GENDER = report.GENDER AND campaign_adsets.DATE = report.DATE ) WHERE CONTAINS(report.ACTION_TYPE, 'offsite_conversion') OR CONTAINS(report.ACTION_TYPE, 'onsite_conversion') AND {filters_by} GROUP BY campaign_adsets.campaign_id """ @staticmethod def age_and_gender_report(filters_by: str): return f""" SELECT report.CAMPAIGN_ID, report.CAMPAIGN_NAME, SUM(report.SPEND) as SPEND, SUM(report.IMPRESSIONS) as IMPRESSIONS, AVG(report.FREQUENCY) as FREQUENCY, SUM(report.CLICKS) as CLICKS, SUM(report.REACH) as REACH, AVG(report.CTR) as CTR, SUM(report.INLINE_POST_ENGAGEMENT) as INLINE_POST_ENGAGEMENT, AVG(report.CPM) as CPM, MIN(report.DATE) as fallback_fb_start_date, MAX(report.DATE) as fallback_fb_end_date FROM V_FACEBOOK_AGE_GENDER_DAILY_REPORT as report JOIN campaign_adsets on ( campaign_adsets.ADSET_ID = report.ADSET_ID AND campaign_adsets.AGE = report.AGE AND campaign_adsets.GENDER = report.GENDER AND campaign_adsets.DATE = report.DATE ) WHERE {filters_by} GROUP BY report.CAMPAIGN_ID, report.CAMPAIGN_NAME """ @staticmethod def adset_history_report(): return """ SELECT report.CAMPAIGN_ID, TO_DATE(MIN(report.START_TIME)) as f_start_date, TO_DATE(MAX(report.END_TIME)) as f_end_date FROM V_FACEBOOK_AD_SET_HISTORY as report JOIN campaign_adsets on ( campaign_adsets.ADSET_ID = report.ADSET_SOURCE_ID AND campaign_adsets.CAMPAIGN_ID = report.CAMPAIGN_ID ) GROUP BY report.CAMPAIGN_ID """