def get_campaign_data(ad_account: adaccount.AdAccount) -> {}:
"""Retrieves the campaign data of the ad account as a dictionary
Args:
ad_account: An ad account for which to retrieve the campaign data
Returns:
A dictionary with {campaign_id: {'name': 1, 'attributes': {}}} format
"""
logging.info('get campaign data for account {}'.format(ad_account['account_id']))
campaigns = ad_account.get_campaigns(
fields=['id',
'name',
'adlabels'],
params={'limit': 1000,
'status': ['ACTIVE',
'PAUSED',
'ARCHIVED']})
result = {}
for campaign in campaigns:
result[campaign['id']] = {'name': campaign['name'],
'attributes': parse_labels(
campaign.get('adlabels', []))}
return result
def get_campaigns_info_by_account(self, account_id):
account = AdAccount('act_%s' % str(account_id))
campaigns = account.get_campaigns(fields=[
Campaign.Field.id, Campaign.Field.name, Campaign.Field.objective,
Campaign.Field.status, Campaign.Field.updated_time
])
if campaigns: return campaigns
return {}
def get_campaigns(account_id):
fields = ["id", "name"]
def decorate(x):
return {"value": x["id"], "name": x["name"]}
account = AdAccount(account_id)
return [decorate(x) for x in account.get_campaigns(fields)]
def facebook_data_extraction(config):
'''
Facebook Ads platform data extraction
'''
facebook_get_service(config)
me = User(fbid='me')
print('me', me)
print('me.remote_read(fields=[User.Field.permissions])', me.remote_read())
ad_account = AdAccount(config['ad_account_id'])
print('ad_account', ad_account.remote_read())
print('ad_account', ad_account.get_campaigns())
return ''
def get_campaign_list(account_id):
try:
ad_account = AdAccount(account_id)
campaigns = ad_account.get_campaigns(fields=[
Campaign.Field.name, Campaign.Field.id, Campaign.Field.status
])
active_campaign_hash = {}
for campaign in campaigns:
if 'ACTIVE' in campaign[Campaign.Field.status]:
active_campaign_hash[campaign[Campaign.Field.id]] = campaign[
Campaign.Field.name]
logger.info(active_campaign_hash)
return active_campaign_hash
except exceptions.FacebookError, e:
logger.error('Error %s' % e)
return {}
ad_account_id = 'act_22759908'
fields = {
Campaign.Field.name,
Campaign.Field.objective,
}
params = {
Campaign.Field.effective_status: [
'ACTIVE',
],
}
account = AdAccount(ad_account_id)
campaigns = account.get_campaigns(fields=fields, params=params)
print campaigns
fields = [
AdsInsights.Field.impressions,
AdsInsights.Field.inline_link_clicks,
AdsInsights.Field.spend,
]
params = {
'end_time': 1504275690,
}
if len(campaigns) > 0:
campaign = Campaign(campaigns[0]['id'])
def get_campaign_data(ad_account: adaccount.AdAccount
) -> {}:
"""Retrieves the campaign data of the ad account as a dictionary
Args:
ad_account: An ad account for which to retrieve the campaign data
Returns:
A dictionary with {campaign_id: {'name': 1, 'attributes': {}}} format
"""
logging.info('get campaign data for account {}'.format(ad_account['account_id']))
campaigns = ad_account.get_campaigns(
fields=['id',
'name',
'adlabels'],
params={'limit': 1000,
'status': ['ACTIVE',
'PAUSED',
'ARCHIVED']})
result = {}
for campaign in campaigns:
result[campaign['id']] = {'name': campaign['name'],
'attributes': parse_labels(
campaign.get('adlabels', []))}
return result
@rate_limiting
def get_account_ad_performance_for_single_day(ad_account: adaccount.AdAccount,
single_date
: datetime) -> adsinsights.AdsInsights:
"""Downloads the ad performance for an ad account for a given day
https://developers.facebook.com/docs/marketing-api/insights
Args:
ad_account: An ad account to download.
single_date: A single date as a datetime object
Returns:
A list containing dictionaries with the ad performance from the report
"""
logging.info('download Facebook ad performance of act_{ad_account_id} on {single_date}'.format(
ad_account_id=ad_account['account_id'],
single_date=single_date.strftime('%Y-%m-%d')))
ad_insights = ad_account.get_insights(
# https://developers.facebook.com/docs/marketing-api/insights/fields
fields=['date_start',
'ad_id',
'impressions',
'actions',
'spend',
'action_values'],
# https://developers.facebook.com/docs/marketing-api/insights/parameters
params={'action_attribution_windows': ['28d_click'],
# https://developers.facebook.com/docs/marketing-api/insights/action-breakdowns
'action_breakdowns': ['action_type'],
# https://developers.facebook.com/docs/marketing-api/insights/breakdowns
'breakdowns': ['impression_device'],
'level': 'ad',
'limit': 1000,
'time_range': {'since': single_date.strftime('%Y-%m-%d'),
'until': single_date.strftime('%Y-%m-%d')},
# By default only ACTIVE campaigns get considered.
'filtering': [{
'field': 'ad.effective_status',
'operator': 'IN',
'value': ['ACTIVE',
'PAUSED',
'PENDING_REVIEW',
'DISAPPROVED',
'PREAPPROVED',
'PENDING_BILLING_INFO',
'CAMPAIGN_PAUSED',
'ARCHIVED',
'ADSET_PAUSED']}]})
return ad_insights
def ensure_data_directory(relative_path: Path = None
) -> Path:
"""Checks if a directory in the data dir path exists. Creates it if necessary
Args:
relative_path: A Path object pointing to a file relative to the data directory
Returns:
The absolute path Path object
"""
if relative_path is None:
return Path(config.data_dir())
try:
path = Path(config.data_dir(), relative_path)
# if path points to a file, create parent directory instead
if path.suffix:
if not path.parent.exists():
path.parent.mkdir(exist_ok=True, parents=True)
else:
if not path.exists():
path.mkdir(exist_ok=True, parents=True)
return path
except OSError as exception:
if exception.errno != errno.EEXIST:
raise
def parse_labels(labels: [{}]
) -> {str: str}:
"""Extracts labels from a string.
Args:
labels: Labels in the form of
[{"id": "1", "name": "{key_1=value_1}"},
{"id": "2", "name": "{key_2=value_2}"}]"'
Returns:
A dictionary of labels with {key_1 : value_1, ...} format
"""
labels_dict = {}
for label in labels:
match = re.search("{([a-zA-Z|_]+)=([a-zA-Z|_]+)}", label['name'])
if match:
key = match.group(1).strip().lower().title()
value = match.group(2).strip()
labels_dict[key] = value
return labels_dict
@rate_limiting
def _get_ad_accounts() ->
[adaccount.AdAccount]:
"""Retrieves the ad accounts of the user whose access token was provided and
returns them as a list.
Returns:
A list of ad accounts
"""
system_user = user.User(fbid='me')
ad_accounts = system_user.get_ad_accounts(fields=['account_id',
'name',
'created_time',
'timezone_offset_hours_utc'])
return list(ad_accounts)
def _upsert_ad_performance(ad_insights: [adsinsights.AdsInsights], con
: sqlite3.Connection):
"""Creates the ad performance table if it does not exists and upserts the
ad insights data afterwards
Args:
ad_insights: A list of Insights objects
con: A sqlite database connection
"""
con.execute("""
CREATE TABLE IF NOT EXISTS ad_performance (
date DATE NOT NULL,
ad_id BIGINT NOT NULL,
device TEXT NOT NULL,
performance TEXT NOT NULL,
PRIMARY KEY (ad_id, device)
);""")
con.executemany("INSERT OR REPLACE INTO ad_performance VALUES (?,?,?,?)",
_to_insight_row_tuples(ad_insights))
def _to_insight_row_tuples(ad_insights: [adsinsights.AdsInsights]
) -> Generator[tuple, None, None]:
"""Transforms the Insights objects into tuples that can be directly inserted
into the ad_performance table
Args:
ad_insights: A list of Insights objects for an ad on a specific day
Returns:
A list of tuples of ad performance data
"""
for ad_insight in ad_insights:
actions = ad_insight.get('actions') or []
actions = [_floatify_values(action) for action in actions]
action_values = ad_insight.get('action_values') or []
action_values = [_floatify_values(action_value) for action_value in action_values]
performance = {'impressions': int(ad_insight['impressions']),
'spend': float(ad_insight['spend']),
'actions': actions,
'action_values': action_values}
ad_insight_tuple = (ad_insight['date_start'],
ad_insight['ad_id'],
ad_insight['impression_device'],
json.dumps(performance))
yield ad_insight_tuple
def _floatify(value: str
) -> Union[str, float]:
try:
return float(value)
except ValueError:
return value
def _floatify_values(inp: {}
) -> {}:
return {key: _floatify(value) for key, value in inp.items()}
def _first_download_date_of_ad_account(ad_account: adaccount.AdAccount
) -> datetime.date:
"""Finds the first date for which the ad account's performance should be
downloaded by comparing the first download date from the configuration and
the creation date of the account and returning the maximum of the two.
Args:
ad_account: An ad account to download
Returns:
The first date to download the performance data for
"""
config_first_date = datetime.datetime.strptime(config.first_date(),
'%Y-%m-%d').date()
if 'created_time' in ad_account:
account_created_date = datetime.datetime.strptime(ad_account['created_time'],
"%Y-%m-%dT%H:%M:%S%z").date()
return max(config_first_date, account_created_date)
else:
return config_first_date
def get_bb_reports(self):
ad_account = AdAccount(config['facebook_bb_ad_account_id'])
denmark_now = datetime.now(
pytz.timezone('Europe/Copenhagen')).replace(tzinfo=None)
for campaign in ad_account.get_campaigns():
insights = campaign.get_insights(params={
'level':
'ad',
'date_preset':
'today',
'breakdowns':
['hourly_stats_aggregated_by_advertiser_time_zone']
},
fields=[
'impressions',
'inline_link_clicks',
'inline_link_click_ctr',
'relevance_score',
'spend',
'campaign_name',
'adset_name',
'ad_name',
'actions',
'date_start',
'date_stop',
'clicks',
'cpc',
'cpm',
'ctr',
])
for insight in insights:
hour_range = insight[
'hourly_stats_aggregated_by_advertiser_time_zone']
time_from = hour_range.split(' - ')[0]
time_to = hour_range.split(' - ')[1]
mnl_time_from = insight.get('date_start') + ' ' + time_from
mnl_time_to = insight.get('date_stop') + ' ' + time_to
mnl_time_from = datetime.strptime(mnl_time_from,
'%Y-%m-%d %H:%M:%S')
mnl_time_to = datetime.strptime(mnl_time_to,
'%Y-%m-%d %H:%M:%S')
denmark_time_from = mnl_time_from - timedelta(hours=8)
denmark_time_to = mnl_time_to - timedelta(hours=8)
if denmark_time_to > denmark_now: # if period is not yet done, skip
continue
facebook_report = FacebookAdReports.select(
and_(
FacebookAdReports.c.campaign_name == insight.get(
'campaign_name'), FacebookAdReports.c.ad_set_name
== insight.get('adset_name'),
FacebookAdReports.c.ad_name == insight.get('ad_name'),
FacebookAdReports.c.since == denmark_time_from,
FacebookAdReports.c.until ==
denmark_time_to)).execute().first()
if facebook_report is not None: # if entry already exists, skip
continue
offsite_conversion = 0
complete_registrations = 0
relevance_score = insight.get('relevance_score').get(
'score') if insight.get('relevance_score') else 0
actions = insight.get('actions') if insight.get(
'actions') else []
for action in actions:
if action['action_type'] == 'offsite_conversion':
offsite_conversion = action['value']
elif action[
'action_type'] == 'offsite_conversion.fb_pixel_complete_registration':
complete_registrations = action['value']
db_conn.execute(
FacebookAdReports.insert(),
account='budgetbear.net',
campaign_name=insight.get('campaign_name'),
ad_set_name=insight.get('adset_name'),
ad_name=insight.get('ad_name'),
account_currency='USD',
clicks=insight.get('clicks'),
cpc=insight.get('cpc'),
cpm=insight.get('cpm'),
ctr=insight.get('ctr'),
impressions=insight.get('impressions'),
inline_link_clicks=insight.get('inline_link_clicks'),
inline_link_click_ctr=insight.get('inline_link_click_ctr'),
spend=insight.get('spend'),
relevance_score=relevance_score,
offsite_conversion=offsite_conversion,
complete_registrations=complete_registrations,
since=denmark_time_from,
until=denmark_time_to)
def get_previous_bb_reports(self):
from analytics import metadata
from sqlalchemy import Table
FacebookAdDateQuery = Table('facebook_ad_date_query',
metadata,
autoload=True)
ad_date_query = FacebookAdDateQuery.select().execute().first()
query_date = ad_date_query['query_date']
end_of_query = '2018-02-14'
if query_date == end_of_query:
return
ad_account = AdAccount(config['facebook_bb_ad_account_id'])
mnl_now = datetime.now(pytz.timezone('Asia/Hong_Kong'))
for campaign in ad_account.get_campaigns():
insights = campaign.get_insights(params={
'level':
'ad',
'time_range': {
'since': query_date,
'until': query_date
},
'breakdowns':
['hourly_stats_aggregated_by_advertiser_time_zone']
},
fields=[
'impressions',
'inline_link_clicks',
'inline_link_click_ctr',
'relevance_score',
'spend',
'campaign_name',
'adset_name',
'ad_name',
'actions',
'date_start',
'date_stop',
'clicks',
'cpc',
'cpm',
'ctr',
])
for insight in insights:
hour_range = insight[
'hourly_stats_aggregated_by_advertiser_time_zone']
time_from = hour_range.split(' - ')[0]
time_to = hour_range.split(' - ')[1]
mnl_time_from = insight.get('date_start') + ' ' + time_from
mnl_time_to = insight.get('date_stop') + ' ' + time_to
mnl_time_from = datetime.strptime(mnl_time_from,
'%Y-%m-%d %H:%M:%S')
mnl_time_to = datetime.strptime(mnl_time_to,
'%Y-%m-%d %H:%M:%S')
denmark_time_from = mnl_time_from - timedelta(hours=8)
denmark_time_to = mnl_time_to - timedelta(hours=8)
offsite_conversion = 0
complete_registrations = 0
relevance_score = insight.get('relevance_score').get(
'score') if insight.get('relevance_score') else 0
actions = insight.get('actions') if insight.get(
'actions') else []
for action in actions:
if action['action_type'] == 'offsite_conversion':
offsite_conversion = action['value']
elif action[
'action_type'] == 'offsite_conversion.fb_pixel_complete_registration':
complete_registrations = action['value']
db_conn.execute(
FacebookAdReports.insert(),
account='budgetbear.net',
campaign_name=insight.get('campaign_name'),
ad_set_name=insight.get('adset_name'),
ad_name=insight.get('ad_name'),
account_currency='USD',
clicks=insight.get('clicks'),
cpc=insight.get('cpc'),
cpm=insight.get('cpm'),
ctr=insight.get('ctr'),
impressions=insight.get('impressions'),
inline_link_clicks=insight.get('inline_link_clicks'),
inline_link_click_ctr=insight.get('inline_link_click_ctr'),
spend=insight.get('spend'),
relevance_score=relevance_score,
offsite_conversion=offsite_conversion,
complete_registrations=complete_registrations,
since=denmark_time_from,
until=denmark_time_to)
now = datetime.now(pytz.timezone('Europe/Copenhagen'))
next_query_date = (datetime.strptime(query_date, '%Y-%m-%d') +
timedelta(days=1)).strftime('%Y-%m-%d')
FacebookAdDateQuery.update().where(
FacebookAdDateQuery.c.query_date == query_date).values(
query_date=next_query_date, updated_at=now).execute()
