import json from datetime import datetime, date, time from decimal import Decimal import pytz from google.cloud import bigquery from google.cloud import bigquery_storage from google.oauth2 import service_account from google.api_core.exceptions import Conflict from google.api_core.exceptions import NotFound from typing import Any, Dict, List, Optional import pandas as pd import logging import json import os import re logging.basicConfig(level=logging.INFO) class MediaBigQuery: def __init__(self, credential_env_name=None): self.project_id = os.environ.get('GCP_PROJECT') if credential_env_name: self.client = bigquery.Client(project=self.project_id, credentials=service_account.Credentials.from_service_account_info(json.loads(os.environ.get(credential_env_name)))) else: self.client = bigquery.Client(project=self.project_id) if credential_env_name: self.bigquery_storage = bigquery_storage.BigQueryReadClient(credentials=service_account.Credentials.from_service_account_info(json.loads(os.environ.get(credential_env_name)))) else: self.bigquery_storage = bigquery_storage.BigQueryReadClient() def facebook_single_view_query(accountId_List,start,end): query = f""" SELECT "facebook" AS media_channel, DATE(FORMAT_DATE('%Y-%m-%d', media_facebook.date_start)) as date, EXTRACT(WEEK FROM media_facebook.date_start) AS week, EXTRACT(MONTH FROM media_facebook.date_start) AS month, (EXTRACT(YEAR FROM media_facebook.date_start )) AS year, media_facebook.account_name AS account_name, media_facebook.account_id AS account_id, media_facebook.campaign_name AS campaign_name, media_facebook.campaign_id AS campaign_id, media_facebook.adset_name AS adset_name, media_facebook.adset_id AS adset_id, media_facebook.ad_name AS ad_name, media_facebook.ad_id AS ad_id, "" AS match_type, "" AS ad_label, media_facebook.objective AS objectives, ROUND(COALESCE(CAST(SUM(DISTINCT CAST(ROUND(COALESCE(media_facebook.spend, 0), 9) AS NUMERIC)) AS NUMERIC), 0), 6) AS cost, ROUND(COALESCE(CAST(SUM(DISTINCT CAST(ROUND(COALESCE(media_facebook.impressions, 0), 9) AS NUMERIC)) AS NUMERIC), 0), 6) AS impression, (ROUND(COALESCE(CAST( ( SUM(DISTINCT (CAST(ROUND(COALESCE( media_facebook.reach ,0)*(1/1000*1.0), 9) AS NUMERIC) + (cast(cast(concat('0x', substr(to_hex(md5(CAST( CONCAT(media_facebook.date_start,media_facebook.account_id,media_facebook.campaign_id,media_facebook.adset_id,media_facebook.ad_id) AS STRING))), 1, 15)) as int64) as numeric) * 4294967296 + cast(cast(concat('0x', substr(to_hex(md5(CAST( CONCAT(media_facebook.date_start,media_facebook.account_id,media_facebook.campaign_id,media_facebook.adset_id,media_facebook.ad_id) AS STRING))), 16, 8)) as int64) as numeric)) * 0.000000001 )) - SUM(DISTINCT (cast(cast(concat('0x', substr(to_hex(md5(CAST( CONCAT(media_facebook.date_start,media_facebook.account_id,media_facebook.campaign_id,media_facebook.adset_id,media_facebook.ad_id) AS STRING))), 1, 15)) as int64) as numeric) * 4294967296 + cast(cast(concat('0x', substr(to_hex(md5(CAST( CONCAT(media_facebook.date_start,media_facebook.account_id,media_facebook.campaign_id,media_facebook.adset_id,media_facebook.ad_id) AS STRING))), 16, 8)) as int64) as numeric)) * 0.000000001) ) / (1/1000*1.0) AS NUMERIC), 0), 6) / NULLIF(CAST(COUNT(DISTINCT CASE WHEN media_facebook.reach IS NOT NULL THEN CONCAT(media_facebook.date_start,media_facebook.account_id,media_facebook.campaign_id,media_facebook.adset_id,media_facebook.ad_id) ELSE NULL END) AS FLOAT64), 0.0)) AS reach, (ROUND(COALESCE(CAST( ( SUM(DISTINCT (CAST(ROUND(COALESCE( media_facebook.frequency ,0)*(1/1000*1.0), 9) AS NUMERIC) + (cast(cast(concat('0x', substr(to_hex(md5(CAST( CONCAT(media_facebook.date_start,media_facebook.account_id,media_facebook.campaign_id,media_facebook.adset_id,media_facebook.ad_id) AS STRING))), 1, 15)) as int64) as numeric) * 4294967296 + cast(cast(concat('0x', substr(to_hex(md5(CAST( CONCAT(media_facebook.date_start,media_facebook.account_id,media_facebook.campaign_id,media_facebook.adset_id,media_facebook.ad_id) AS STRING))), 16, 8)) as int64) as numeric)) * 0.000000001 )) - SUM(DISTINCT (cast(cast(concat('0x', substr(to_hex(md5(CAST( CONCAT(media_facebook.date_start,media_facebook.account_id,media_facebook.campaign_id,media_facebook.adset_id,media_facebook.ad_id) AS STRING))), 1, 15)) as int64) as numeric) * 4294967296 + cast(cast(concat('0x', substr(to_hex(md5(CAST( CONCAT(media_facebook.date_start,media_facebook.account_id,media_facebook.campaign_id,media_facebook.adset_id,media_facebook.ad_id) AS STRING))), 16, 8)) as int64) as numeric)) * 0.000000001) ) / (1/1000*1.0) AS NUMERIC), 0), 6) / NULLIF(CAST(COUNT(DISTINCT CASE WHEN media_facebook.frequency IS NOT NULL THEN CONCAT(media_facebook.date_start,media_facebook.account_id,media_facebook.campaign_id,media_facebook.adset_id,media_facebook.ad_id) ELSE NULL END) AS FLOAT64), 0.0)) AS frequency, ROUND(COALESCE(CAST(SUM(DISTINCT CAST(ROUND(COALESCE(media_facebook.clicks, 0), 9) AS NUMERIC)) AS NUMERIC), 0), 6) AS clicks, COALESCE(SUM(CASE WHEN (( media_facebook_action.action_type ) LIKE 'link_click') THEN media_facebook_action.value ELSE NULL END), 0) AS link_clicks, ROUND(COALESCE(CAST(SUM(DISTINCT CAST(ROUND(COALESCE(media_facebook.video_thruplay_watched_actions, 0), 9) AS NUMERIC)) AS NUMERIC), 0), 6) AS view, ROUND(COALESCE(CAST(SUM(DISTINCT CAST(ROUND(COALESCE(media_facebook.video_play_actions, 0), 9) AS NUMERIC)) AS NUMERIC), 0), 6) AS video_play_action, COALESCE(SUM(0), 0) AS video_view, ROUND(COALESCE(CAST(SUM(DISTINCT CAST(ROUND(COALESCE(media_facebook.video_p25_watched_actions, 0), 9) AS NUMERIC)) AS NUMERIC), 0), 6) AS video_view_25, ROUND(COALESCE(CAST(SUM(DISTINCT CAST(ROUND(COALESCE(media_facebook.video_p50_watched_actions, 0), 9) AS NUMERIC)) AS NUMERIC), 0), 6) AS video_view_50, ROUND(COALESCE(CAST(SUM(DISTINCT CAST(ROUND(COALESCE(media_facebook.video_p75_watched_actions, 0), 9) AS NUMERIC)) AS NUMERIC), 0), 6) AS video_view_75, ROUND(COALESCE(CAST(SUM(DISTINCT CAST(ROUND(COALESCE(media_facebook.video_p100_watched_actions, 0), 9) AS NUMERIC)) AS NUMERIC), 0), 6) AS video_view_100, COALESCE(SUM(0 ), 0) AS view_15sec_tiktok, COALESCE(SUM(CASE WHEN (( media_facebook_action.action_type ) LIKE 'post_engagement') THEN media_facebook_action.value ELSE NULL END), 0) AS post_engagement, COALESCE(SUM(CASE WHEN (( media_facebook_action.action_type ) LIKE 'post_reaction') THEN media_facebook_action.value ELSE NULL END), 0) AS post_reaction, COALESCE(SUM(CASE WHEN (( media_facebook_action.action_type ) = 'comment') THEN media_facebook_action.value ELSE NULL END), 0) AS post_comment, COALESCE(SUM(CASE WHEN (( media_facebook_action.action_type ) = 'post') THEN media_facebook_action.value ELSE NULL END), 0) AS post_share, COALESCE(SUM(CASE WHEN (( media_facebook_action.action_type ) = 'like') THEN media_facebook_action.value ELSE NULL END), 0) AS page_like, COALESCE(SUM(CASE WHEN (( media_facebook_action.action_type ) LIKE 'onsite_conversion_messaging_conversation_started_7d') THEN value ELSE NULL END), 0) AS messages, COALESCE(SUM(CASE WHEN (( media_facebook_action.action_type ) = 'onsite_web_lead') THEN media_facebook_action.value ELSE NULL END), 0) AS meta_leads, COALESCE(SUM(CASE WHEN (( media_facebook_action.action_type ) = 'onsite_web_purchase') THEN media_facebook_action.value ELSE NULL END), 0) AS meta_purchase, COALESCE(SUM(0), 0) AS conversions, COALESCE(SUM(0), 0) AS conversions_value FROM `ydmdashboard.rda_analytics.media_facebook` AS media_facebook LEFT JOIN UNNEST(media_facebook.actions) as media_facebook_action WHERE DATE(FORMAT_DATE('%Y-%m-%d', media_facebook.date_start)) BETWEEN '{start}' AND '{end}' AND media_facebook.account_id IN ({accountId_List}) GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16 """ return query