Skip to content

Instantly share code, notes, and snippets.

@Tin-Nguyen

Tin-Nguyen/Communique log

Created October 11, 2016 04:07
Show Gist options
  • Save Tin-Nguyen/bd34dee8f380ade1c0c2160eea102a33 to your computer and use it in GitHub Desktop.
Save Tin-Nguyen/bd34dee8f380ade1c0c2160eea102a33 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Communique log
2016-10-11 04:04:57,010 [INFO] platform_communique: Req UUID: '2f9def60-fd83-44bc-b024-a73e92b132c2' HTTP GET URL: 'https://stage-0001-elb-communique.unifiedsocial.com/v1/instagram/paid/raw?granularity=daily&click_attr=actions_28d&cost_calculation=owner_media_cost&breakout=none&line_item_id=1f67b3e4-28b8-48e6-b7b0-4a59191f527d&include_dark_social=false&include_custom_metrics=true&email=khoa.le%2Bprovideradmin%40unified.com&async=true&view_attr=actions_1d&format=xlsx&filename=RawDataReport_10.11.2016&start_date=06%2F16%2F2016&end_date=08%2F05%2F2016&columns=&brand_id=c6883ad3-9ae2-46f6-9870-30de548b98c9', Params: '{'format': 'xlsx', 'view_attr': 'actions_1d', 'start_date': '06/16/2016', 'breakout': 'none', 'brand_id': 'c6883ad3-9ae2-46f6-9870-30de548b98c9', 'click_attr': 'actions_28d', 'cost_calculation': 'owner_media_cost', 'async': 'true', 'include_custom_metrics': 'true', 'include_dark_social': 'false', 'end_date': '08/05/2016', 'line_item_id': '1f67b3e4-28b8-48e6-b7b0-4a59191f527d', 'filename': 'RawDataReport_10.11.2016', 'email': '[email protected]', 'granularity': 'daily'}', User Agent: 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/53.0.2785.116 Safari/537.36', X-Unified-IdentityID: '[email protected]'
2016-10-11 04:04:57,011 [DEBUG] platform_communique: Tabular report request getting dispatched for main section
2016-10-11 04:04:57,011 [INFO] platform_communique: Req UUID: '2f9def60-fd83-44bc-b024-a73e92b132c2', Response Code: '200 OK', X-Unified-IdentityID: '[email protected]'
[pid: 13442|app: 0|req: 772/1036] 127.0.0.1 () {50 vars in 1945 bytes} [Tue Oct 11 04:04:57 2016] GET /v1/instagram/paid/raw?granularity=daily&click_attr=actions_28d&cost_calculation=owner_media_cost&breakout=none&line_item_id=1f67b3e4-28b8-48e6-b7b0-4a59191f527d&include_dark_social=false&include_custom_metrics=true&email=khoa.le%2Bprovideradmin%40unified.com&async=true&view_attr=actions_1d&format=xlsx&filename=RawDataReport_10.11.2016&start_date=06%2F16%2F2016&end_date=08%2F05%2F2016&columns=&brand_id=c6883ad3-9ae2-46f6-9870-30de548b98c9 => generated 0 bytes in 4 msecs (HTTP/1.0 200) 7 headers in 389 bytes (0 switches on core 0)
2016-10-11 04:04:57,012 [INFO] platform_communique: Making platform API call using X-Unified-IdentityID [email protected]
2016-10-11 04:04:57,014 [INFO] platform_communique: Making platform API call using X-Unified-IdentityID [email protected]
2016-10-11 04:04:57,014 [INFO] platform_communique: Unified API Method: 'GET', URL: 'http://stage-0001-elb-pmn.unifiedsocial.com/line_items/1f67b3e4-28b8-48e6-b7b0-4a59191f527d', Params: '{}'
2016-10-11 04:04:57,027 [INFO] platform_communique: Unified API Response: '{"archived":false,"brandID":"c6883ad3-9ae2-46f6-9870-30de548b98c9","companyID":"fe044f9e-3a38-4d71-a508-744a9638cf06","creatorID":"[email protected]","currency":"USD","dateCreated":"2016-06-16T04:07:07Z","dateModified":"2016-07-29T08:48:05Z","deliveryStatus":"Completed","disabled":false,"displayOffset":"","endDate":"2016-08-05T23:59:59Z","fee":0,"feePercentage":0,"feeType":"","foreignID":"","id":"1f67b3e4-28b8-48e6-b7b0-4a59191f527d","initiativeID":"fa07c55d-b805-44ff-969c-19bd18ecaf78","mediaSpend":100,"mediaType":"Paid","name":"[Team5][16Jun16] ema.io/instavideo","publisher":"instagram","startDate":"2016-06-16T00:00:00Z","totalBudget":0}
'
2016-10-11 04:04:57,027 [INFO] platform_communique: Unified API Method: 'GET', URL: 'http://stage-0001-elb-pmn.unifiedsocial.com/line_items/1f67b3e4-28b8-48e6-b7b0-4a59191f527d/company_access', Params: '{}'
2016-10-11 04:04:57,035 [INFO] platform_communique: Unified API Response: '{}
'
2016-10-11 04:04:57,035 [INFO] platform_communique: Unified API Method: 'GET', URL: 'http://stage-0001-elb-neo4j-read.ec2.unifiedsocial.com:7474/ecm/api/0.5/entity/search?propertyId=1f67b3e4-28b8-48e6-b7b0-4a59191f527d', Params: '{}'
2016-10-11 04:04:57,044 [INFO] platform_communique: Unified API Response: '[
{
"id": 63239,
"unique_hash": "4002dbea4b031a0e3a036fbaa739e0db",
"property_type": "LineItem",
"property_id": "1f67b3e4-28b8-48e6-b7b0-4a59191f527d",
"content_type": "Internal",
"version": "0.1",
"network": "pmn",
"payload": {
"lineItemID": "1f67b3e4-28b8-48e6-b7b0-4a59191f527d",
"network": "instagram"
},
"created_date": "2016-06-16T04:07:34.263Z[UTC]",
"updated_date": "2016-09-02T14:20:36.179Z[UTC]",
"enabled": true
}
]'
2016-10-11 04:04:57,044 [INFO] platform_communique: Unified API Method: 'GET', URL: 'http://stage-0001-elb-neo4j-read.ec2.unifiedsocial.com:7474/ecm/api/0.5/entity/63239/entity', Params: '{}'
2016-10-11 04:04:57,048 [INFO] platform_communique: Unified API Response: '[
{
"id": 63240,
"unique_hash": "8d593be2a7eead2933ee298bea1a2293",
"property_type": "Campaign",
"property_id": "6046428115738",
"content_type": "Paid",
"version": "0.1",
"network": "instagram",
"payload": {
"adAccountID": "104448579711490",
"brandID": "c6883ad3-9ae2-46f6-9870-30de548b98c9",
"brandURL": "pmn/brands/c6883ad3-9ae2-46f6-9870-30de548b98c9",
"campaignID": "6046428115738",
"companyID": "fe044f9e-3a38-4d71-a508-744a9638cf06",
"companyURL": "pmn/companies/fe044f9e-3a38-4d71-a508-744a9638cf06",
"initiativeID": "fa07c55d-b805-44ff-969c-19bd18ecaf78",
"initiativeURL": "pmn/initiatives/fa07c55d-b805-44ff-969c-19bd18ecaf78",
"lineItemID": "1f67b3e4-28b8-48e6-b7b0-4a59191f527d",
"lineItemURL": "pmn/line_items/1f67b3e4-28b8-48e6-b7b0-4a59191f527d"
},
"created_date": "2016-06-16T04:07:34.308Z[UTC]",
"updated_date": "2016-09-02T14:20:36.179Z[UTC]",
"enabled": true
}
]'
2016-10-11 04:04:57,054 [DEBUG] platform_communique: PID: 5535, SERVER: {'host': 'dw-test.csl1vvtytte4.us-east-1.redshift.amazonaws.com', 'password': 'bigBoyDev1', 'protocol': 'postgresql', 'port': 5439, 'username': 'intelligence', 'path': 'fatty'}, SQL query:
SELECT
_st.campaign_id,
_st.campaign,
_st.adset_id,
_st.adset,
_st.ad_id AS ad_id,
_st.ad AS ad,
ac.object_story_id post_id,
coalesce(p.message,
p.caption, '') post,
_st.date :: DATE,
'Facebook' AS publisher,
coalesce(impressions,
0) AS impressions,
coalesce(total_actions,
0) AS total_actions,
coalesce(unique_impressions,
0) AS unique_impressions,
coalesce(ad_clicks,
0) AS ad_clicks,
coalesce(unique_clicks,
0) AS unique_clicks,
coalesce(inline_link_clicks,
0) AS inline_link_clicks,
coalesce(media_spend,
0) AS media_spend,
coalesce(inline_post_engagement,
0) AS inline_post_engagement,
coalesce(post_engagements,
0) AS post_engagements,
coalesce(comments,
0) AS comments,
coalesce(page_likes,
0) AS page_likes,
coalesce(post_shares,
0) AS post_shares,
coalesce(post_likes,
0) AS post_likes,
coalesce(link_clicks,
0) AS link_clicks,
coalesce(photo_views,
0) AS photo_views,
coalesce(video_plays,
0) AS video_plays,
coalesce(video_views,
0) AS video_views,
coalesce(conversions,
0) AS conversions,
coalesce(app_install,
0) AS app_install,
ad.targeting_age_min :: VARCHAR + '-' + ad.targeting_age_max :: VARCHAR AS age_range,
CASE WHEN ad.targeting_genders = 2
THEN 'Female'
ELSE CASE WHEN ad.targeting_genders = 1
THEN 'Male'
ELSE 'Both'
END END AS genders,
coalesce(ad.targeting_geo_locations_countries, '') AS countries,
coalesce(ad.targeting_geo_locations_regions, '') AS regions,
coalesce(ad.targeting_geo_locations_cities, '') AS cities,
coalesce(ad.targeting_interests, '') AS interests,
coalesce(ad.targeting_user_adclusters, '') AS user_adclusters,
coalesce(ad.targeting_connections, '') AS fans,
coalesce(ad.targeting_excluded_connections, '') AS non_fans,
coalesce(ad.targeting_friends_of_connections, '') AS friends_of_fans,
coalesce(ad.targeting_education_majors, '') AS education_majors,
coalesce(ad.targeting_education_schools, '') AS education_schools,
coalesce(ad.targeting_education_statuses, '') AS education_statuses,
coalesce(ad.targeting_page_types, '') AS placement,
coalesce(ad.targeting_user_device, '') AS device,
coalesce(ad.targeting_user_os, '') AS os,
coalesce(ad.targeting_behaviors, '') AS behaviors,
coalesce(vote,
0) AS vote,
coalesce(gift_sales,
0) AS gift_sales,
coalesce(social_impressions,
0) AS social_impressions,
coalesce(social_clicks,
0) AS social_clicks,
coalesce(mentions,
0) AS mentions,
coalesce(p.link,
'') AS destination_url,
coalesce(p.post_type,
'') AS post_type,
coalesce(ac.title,
'') AS post_title,
coalesce(video_10_second_watch,
0) AS video_10_second_watch,
coalesce(video_15_second_watch,
0) AS video_15_second_watch,
coalesce(video_30_second_watch,
0) AS video_30_second_watch,
coalesce(avg_pct_video_watch,
0) AS avg_pct_video_watch,
coalesce(avg_sec_video_watch,
0) AS avg_sec_video_watch,
coalesce(video_complete,
0) AS video_complete,
coalesce(video_25_pct_watched,
0) AS video_25_pct_watched,
coalesce(video_50_pct_watched,
0) AS video_50_pct_watched,
coalesce(video_75_pct_watched,
0) AS video_75_pct_watched,
coalesce(video_95_pct_watched,
0) AS video_95_pct_watched,
coalesce(video_100_pct_watched,
0) AS video_100_pct_watched,
coalesce(video_session_summary_delta,
0) AS video_session_summary_delta,
coalesce(app_custom_event,
0) AS app_custom_event,
coalesce(fb_mobile_activate_app,
0) AS fb_mobile_activate_app,
coalesce(fb_mobile_add_payment_info,
0) AS fb_mobile_add_payment_info,
coalesce(fb_mobile_add_to_cart,
0) AS fb_mobile_add_to_cart,
coalesce(fb_mobile_complete_registration,
0) AS fb_mobile_complete_registration,
coalesce(fb_mobile_initiated_checkout,
0) AS fb_mobile_initiated_checkout,
coalesce(fb_mobile_level_achieved,
0) AS fb_mobile_level_achieved,
coalesce(fb_mobile_purchase,
0) AS fb_mobile_purchase,
coalesce(fb_mobile_spent_credits,
0) AS fb_mobile_spent_credits,
coalesce(app_custom_event_other,
0) AS app_custom_event_other,
coalesce(app_engagements,
0) AS app_engagements,
coalesce(app_story,
0) AS app_story,
coalesce(app_use,
0) AS app_use,
coalesce(mobile_app_installs,
0) AS mobile_app_installs,
coalesce(checkin,
0) AS checkin,
coalesce(dwells,
0) AS dwells,
coalesce(leadgen_other,
0) AS leadgen_other,
coalesce(page_engagements,
0) AS page_engagements,
coalesce(offer_claims,
0) AS offer_claims,
coalesce(rsvp,
0) AS rsvp,
COALESCE(unlikes,
0) AS unlikes,
COALESCE(conversion_add_to_cart,
0) AS conversion_add_to_cart,
COALESCE(checkout,
0) AS checkout,
COALESCE(custom_1_conversion,
0) AS custom_1_conversion,
COALESCE(custom_2_conversion,
0) AS custom_2_conversion,
COALESCE(custom_3_conversion,
0) AS custom_3_conversion,
COALESCE(custom_4_conversion,
0) AS custom_4_conversion,
COALESCE(custom_5_conversion,
0) AS custom_5_conversion,
COALESCE(custom_6_conversion,
0) AS custom_6_conversion,
COALESCE(custom_7_conversion,
0) AS custom_7_conversion,
COALESCE(custom_8_conversion,
0) AS custom_8_conversion,
COALESCE(custom_9_conversion,
0) AS custom_9_conversion,
COALESCE(custom_10_conversion,
0) AS custom_10_conversion,
COALESCE(custom_11_conversion,
0) AS custom_11_conversion,
COALESCE(fb_pixel_add_to_cart,
0) AS fb_pixel_add_to_cart,
COALESCE(fb_pixel_complete_registration,
0) AS fb_pixel_complete_registration,
COALESCE(fb_pixel_initiate_checkout,
0) AS fb_pixel_initiate_checkout,
COALESCE(fb_pixel_add_payment_info,
0) AS fb_pixel_add_payment_info,
COALESCE(fb_pixel_add_to_cart,
0) AS fb_pixel_add_to_cart,
COALESCE(fb_pixel_complete_registration,
0) AS fb_pixel_complete_registration,
COALESCE(fb_pixel_custom,
0) AS fb_pixel_custom,
COALESCE(fb_pixel_lead,
0) AS fb_pixel_lead,
COALESCE(fb_pixel_purchase,
0) AS fb_pixel_purchase,
COALESCE(fb_pixel_search,
0) AS fb_pixel_search,
COALESCE(fb_pixel_view_content,
0) AS fb_pixel_view_content,
COALESCE(key_page_view,
0) AS key_page_view,
COALESCE(lead,
0) AS lead,
COALESCE(conversion_other,
0) AS conversion_other,
COALESCE(conversion_registration,
0) AS conversion_registration,
COALESCE(view_conversions,
0) AS view_conversions,
COALESCE(click_conversions,
0) AS click_conversions,
_st.collection_timestamp AS collection_timestamp
FROM (SELECT
st.join_col,
st.campaign_id :: VARCHAR campaign_id,
st.campaign_name campaign,
-- fbc.title post_title,
st.adset_id :: VARCHAR,
st.adset_name adset,
st.ad_id :: VARCHAR,
st.ad_name ad,
-- fbc.object_story_id post_id,
-- COALESCE(fbp.message,
-- fbp.caption) post,
st.date_start :: DATE DATE,
st.impressions,
COALESCE(st.clicks,
st.inline_link_clicks + st.inline_post_engagement) ad_clicks,
st.inline_link_clicks,
st.inline_post_engagement,
st.unique_clicks,
-- fbp.link destination_url,
-- fbp.post_type post_type,
st.spend media_spend,
st.reach reach,
st.cost_per_unique_click ecpuc,
st.total_actions,
st.social_clicks social_clicks,
st.social_impressions social_impressions,
st.social_reach social_reach,
st.unique_ctr unique_ctr,
st.unique_impressions unique_impressions,
st.unique_social_clicks unique_social_clicks,
st.unique_social_impressions unique_social_impressions,
st.app_store_clicks unique_app_store_clicks,
st.call_to_action_clicks button_clicks,
st.card_views card_views,
st.deeplink_clicks deep_link_clicks,
st.estimated_ad_recall_rate estimated_ad_recall_rate,
st.estimated_ad_recall_rate_lower_bound estimated_ad_recall_rate_lower_bound,
st.estimated_ad_recall_rate_upper_bound estimated_ad_recall_rate_upper_bound,
st.inline_link_clicks inline_link_click,
st.inline_post_engagement inline_post_engagements,
st.website_clicks website_clicks,
st.relevance_score__score relevancy_score,
st.relevance_score__positive_feedback relevancy_score_positive_feedback,
st.relevance_score__negative_feedback relevancy_score_negative_feedback,
st.relevance_score__status relevancy_status,
st.collection_timestamp collection_timestamp
FROM
facebook_insight st
WHERE
st.object = 'facebook-ad-insight' AND st.campaign_id IN ('6046428115738') and date_start::date between '2016-06-16' and '2016-08-05 23:59:59.999999'
) _st
LEFT JOIN (
SELECT
act.join_col,
sum(CASE
WHEN act.action_type = 'post_engagement'
THEN act.value
ELSE 0
END) AS post_engagements,
sum(CASE
WHEN act.action_type = 'comment'
THEN act.value
ELSE 0
END) AS COMMENTS,
sum(CASE
WHEN act.action_type = 'like'
THEN act.value
ELSE 0
END) AS page_likes,
sum(CASE
WHEN act.action_type = 'post'
THEN act.value
ELSE 0
END) AS post_shares,
sum(CASE
WHEN act.action_type = 'post_like'
THEN act.value
ELSE 0
END) AS post_likes,
sum(CASE
WHEN act.action_type = 'link_click'
THEN act.value
ELSE 0
END) AS link_clicks,
sum(CASE
WHEN act.action_type = 'photo_view'
THEN act.value
ELSE 0
END) AS photo_views,
sum(CASE
WHEN act.action_type = 'video_play'
THEN act.value
ELSE 0
END) AS video_plays,
sum(CASE
WHEN act.action_type = 'video_view'
THEN act.value
ELSE 0
END) AS video_views,
sum(CASE
WHEN act.action_type = 'offsite_conversion'
THEN act.value
ELSE 0
END) AS conversions,
sum(CASE
WHEN act.action_type = 'app_install'
THEN act.value
ELSE 0
END) AS app_install,
sum(CASE
WHEN act.action_type = 'video_10_sec_watched_actions'
THEN act.value
ELSE 0
END) AS video_10_second_watch,
sum(CASE
WHEN act.action_type = 'video_15_sec_watched_actions'
THEN act.value
ELSE 0
END) AS video_15_second_watch,
sum(CASE
WHEN act.action_type = 'video_30_sec_watched_actions'
THEN act.value
ELSE 0
END) AS video_30_second_watch,
sum(CASE
WHEN act.action_type = 'video_avg_pct_watched_actions'
THEN act.value
ELSE 0
END) AS avg_pct_video_watch,
sum(CASE
WHEN act.action_type = 'video_avg_sec_watched_actions'
THEN act.value
ELSE 0
END) AS avg_sec_video_watch,
sum(CASE
WHEN act.action_type = 'video_complete_watched_actions'
THEN act.value
ELSE 0
END) AS video_complete,
sum(CASE
WHEN act.action_type = 'video_p25_watched_actions'
THEN act.value
ELSE 0
END) AS video_25_pct_watched,
sum(CASE
WHEN act.action_type = 'video_p50_watched_actions'
THEN act.value
ELSE 0
END) AS video_50_pct_watched,
sum(CASE
WHEN act.action_type = 'video_p75_watched_actions'
THEN act.value
ELSE 0
END) AS video_75_pct_watched,
sum(CASE
WHEN act.action_type = 'video_p95_watched_actions'
THEN act.value
ELSE 0
END) AS video_95_pct_watched,
sum(CASE
WHEN act.action_type = 'video_p100_watched_actions'
THEN act.value
ELSE 0
END) AS video_100_pct_watched,
sum(CASE
WHEN act.action_type = 'video_session_summary_delta'
THEN act.value
ELSE 0
END) AS video_session_summary_delta,
sum(CASE
WHEN act.action_type = 'app_custom_event'
THEN act.value
ELSE 0
END) AS app_custom_event,
sum(CASE
WHEN act.action_type = 'app_custom_event.fb_mobile_activate_app'
THEN act.value
ELSE 0
END) AS fb_mobile_activate_app,
sum(CASE
WHEN act.action_type = 'app_custom_event.fb_mobile_add_payment_info'
THEN act.value
ELSE 0
END) AS fb_mobile_add_payment_info,
sum(CASE
WHEN act.action_type = 'app_custom_event.fb_mobile_add_to_cart'
THEN act.value
ELSE 0
END) AS fb_mobile_add_to_cart,
sum(CASE
WHEN act.action_type = 'app_custom_event.fb_mobile_complete_registration'
THEN act.value
ELSE 0
END) AS fb_mobile_complete_registration,
sum(CASE
WHEN act.action_type = 'app_custom_event.fb_mobile_initiated_checkout'
THEN act.value
ELSE 0
END) AS fb_mobile_initiated_checkout,
sum(CASE
WHEN act.action_type = 'app_custom_event.fb_mobile_level_achieved'
THEN act.value
ELSE 0
END) AS fb_mobile_level_achieved,
sum(CASE
WHEN act.action_type = 'app_custom_event.fb_mobile_purchase'
THEN act.value
ELSE 0
END) AS fb_mobile_purchase,
sum(CASE
WHEN act.action_type = 'app_custom_event.fb_mobile_spent_credits'
THEN act.value
ELSE 0
END) AS fb_mobile_spent_credits,
sum(CASE
WHEN act.action_type = 'app_custom_event.other'
THEN act.value
ELSE 0
END) AS app_custom_event_other,
sum(CASE
WHEN act.action_type = 'app_engagement'
THEN act.value
ELSE 0
END) AS app_engagements,
sum(CASE
WHEN act.action_type = 'app_story'
THEN act.value
ELSE 0
END) AS app_story,
sum(CASE
WHEN act.action_type = 'app_use'
THEN act.value
ELSE 0
END) AS app_use,
sum(CASE
WHEN act.action_type = 'mobile_app_install'
THEN act.value
ELSE 0
END) AS mobile_app_installs,
sum(CASE
WHEN act.action_type = 'checkin'
THEN act.value
ELSE 0
END) AS checkin,
sum(CASE
WHEN act.action_type = 'dwell'
THEN act.value
ELSE 0
END) AS dwells,
sum(CASE
WHEN act.action_type = 'gift_sale'
THEN act.value
ELSE 0
END) AS gift_sales,
sum(CASE
WHEN act.action_type = 'leadgen.other'
THEN act.value
ELSE 0
END) AS leadgen_other,
sum(CASE
WHEN act.action_type = 'mention'
THEN act.value
ELSE 0
END) AS mentions,
sum(CASE
WHEN act.action_type = 'attention_event'
THEN VALUE
ELSE 0
END) AS attention_event,
sum(CASE
WHEN act.action_type = 'page_engagement'
THEN act.value
ELSE 0
END) AS page_engagements,
sum(CASE
WHEN act.action_type = 'receive_offer'
THEN act.value
ELSE 0
END) AS offer_claims,
sum(CASE
WHEN act.action_type = 'rsvp'
THEN act.value
ELSE 0
END) AS rsvp,
sum(CASE
WHEN act.action_type = 'unlike'
THEN act.value
ELSE 0
END) AS unlikes,
sum(CASE
WHEN act.action_type = 'vote'
THEN act.value
ELSE 0
END) AS vote,
sum(CASE
WHEN act.action_type = 'offsite_conversion.add_to_cart'
THEN act.value
ELSE 0
END) AS conversion_add_to_cart,
sum(CASE
WHEN act.action_type = 'offsite_conversion.checkout'
THEN act.value
ELSE 0
END) AS checkout,
sum(CASE
WHEN act.action_type = 'offsite_conversion.custom.160872620928017'
THEN act.value
ELSE 0
END) AS custom_1_conversion,
sum(CASE
WHEN act.action_type = 'offsite_conversion.custom.162565994091381'
THEN act.value
ELSE 0
END) AS custom_2_conversion,
sum(CASE
WHEN act.action_type = 'offsite_conversion.custom.488946167934159'
THEN act.value
ELSE 0
END) AS custom_3_conversion,
sum(CASE
WHEN act.action_type = 'offsite_conversion.custom.523909897765864'
THEN act.value
ELSE 0
END) AS custom_4_conversion,
sum(CASE
WHEN act.action_type = 'offsite_conversion.custom.787111131411278'
THEN act.value
ELSE 0
END) AS custom_5_conversion,
sum(CASE
WHEN act.action_type = 'offsite_conversion.custom.897895546956295'
THEN act.value
ELSE 0
END) AS custom_6_conversion,
sum(CASE
WHEN act.action_type = 'offsite_conversion.custom.949055951784421'
THEN act.value
ELSE 0
END) AS custom_7_conversion,
sum(CASE
WHEN act.action_type = 'offsite_conversion.custom.950805044996873'
THEN act.value
ELSE 0
END) AS custom_8_conversion,
sum(CASE
WHEN act.action_type = 'offsite_conversion.custom.1071141926231038'
THEN act.value
ELSE 0
END) AS custom_9_conversion,
sum(CASE
WHEN act.action_type = 'offsite_conversion.custom.1668077430100573'
THEN act.value
ELSE 0
END) AS custom_10_conversion,
sum(CASE
WHEN act.action_type = 'offsite_conversion.custom.1722071144692298'
THEN act.value
ELSE 0
END) AS custom_11_conversion,
sum(CASE
WHEN act.action_type = 'offsite_conversion.fb_pixel_add_to_cart'
THEN act.value
ELSE 0
END) AS fb_pixel_add_to_cart,
sum(CASE
WHEN act.action_type = 'offsite_conversion.fb_pixel_complete_registration'
THEN act.value
ELSE 0
END) AS fb_pixel_complete_registration,
sum(CASE
WHEN act.action_type = 'offsite_conversion.fb_pixel_custom'
THEN act.value
ELSE 0
END) AS fb_pixel_custom,
sum(CASE
WHEN act.action_type = 'offsite_conversion.fb_pixel_add_payment_info'
THEN act.value
ELSE 0
END) AS fb_pixel_add_payment_info,
sum(CASE
WHEN act.action_type = 'offsite_conversion.fb_pixel_initiate_checkout'
THEN act.value
ELSE 0
END) AS fb_pixel_initiate_checkout,
sum(CASE
WHEN act.action_type = 'offsite_conversion.fb_pixel_lead'
THEN act.value
ELSE 0
END) AS fb_pixel_lead,
sum(CASE
WHEN act.action_type = 'offsite_conversion.fb_pixel_purchase'
THEN act.value
ELSE 0
END) AS fb_pixel_purchase,
sum(CASE
WHEN act.action_type = 'offsite_conversion.fb_pixel_search'
THEN act.value
ELSE 0
END) AS fb_pixel_search,
sum(CASE
WHEN act.action_type = 'offsite_conversion.fb_pixel_view_content'
THEN act.value
ELSE 0
END) AS fb_pixel_view_content,
sum(CASE
WHEN act.action_type = 'offsite_conversion.key_page_view'
THEN act.value
ELSE 0
END) AS key_page_view,
sum(CASE
WHEN act.action_type = 'offsite_conversion.lead'
THEN act.value
ELSE 0
END) AS lead,
sum(CASE
WHEN act.action_type = 'offsite_conversion.other'
THEN act.value
ELSE 0
END) AS conversion_other,
sum(CASE
WHEN act.action_type = 'offsite_conversion.registration'
THEN act.value
ELSE 0
END) AS conversion_registration,
sum(CASE
WHEN act.action_type = 'offsite_conversion' AND attribution_window = 'actions_28d_click'
THEN value
ELSE 0 END) AS click_conversions,
sum(CASE
WHEN act.action_type = 'offsite_conversion' AND attribution_window = 'actions_1d_view'
THEN value
ELSE 0 END) AS view_conversions
FROM
facebook_action act
WHERE
act.campaign_id IN ('6046428115738') and date_start::date between '2016-06-16' and '2016-08-05 23:59:59.999999'
AND act.object_type = 'ad_id'
AND act.attribution_window IN ('actions_28d_click', 'actions_1d_view')
GROUP BY act.join_col) _act ON _act.join_col = _st.join_col
LEFT JOIN facebook_ad ad ON ad.ad_id = _st.ad_id
LEFT JOIN facebook_adcreative ac ON ad.creative_ids = ac.adcreative_id
LEFT JOIN facebook_post p ON p.post_id = ac.object_story_id
order by _st.date, _st.campaign_id, _st.adset_id, p.post_id, _st.ad_id, _st.collection_timestamp
2016-10-11 04:05:07,487 [DEBUG] platform_communique: Postgres connection pool DATA_WAREHOUSE: 1 of 10 used.
2016-10-11 04:05:07,488 [DEBUG] platform_communique: SQL query completed, returning 7 row(s)
2016-10-11 04:05:07,489 [INFO] platform_communique: Making platform API call using X-Unified-IdentityID [email protected]
2016-10-11 04:05:07,489 [INFO] platform_communique: Unified API Method: 'POST', URL: 'http://stage-0001-elb-scheduler.unifiedsocial.com:8080/scheduler/0.2/actions/email', Params: '{'type': 'Email', 'configuration': {'numRetries': 0}, 'parameters': {'plainBody': "We're sorry for the inconvenience. There was an error encountered while attempting to generate and email this report to you. Please try to export your report again or reply to this email to initiate a support ticket.", 'from': '[email protected]', 'subject': '[Error] There was an error generating the report (RawDataReport_10.11.2016.xlsx) that you requested on 10/11/16 12:04AM.', 'to': ['[email protected]']}}'
2016-10-11 04:05:07,674 [INFO] platform_communique: Unified API Response: '{ "messageId": "01000157b1ea573e-dd23c61c-56bb-4bdc-9199-739e7c8b8290-000000" }'
2016-10-11 04:05:07,674 [INFO] platform_communique: Successfully sent email response
2016-10-11 04:05:07,675 [ERROR] platform_communique: UUID: '2f9def60-fd83-44bc-b024-a73e92b132c2', Error: 'failed to generate email report'
Traceback (most recent call last):
File "/usr/lib/unified/platform-communique/app/communique/report.py", line 407, in _async_email_request
req_data['payload'] = self.get_payload(req_data)
File "/usr/lib/unified/platform-communique/app/communique/report.py", line 450, in get_payload
payload = self.generate_payload(req_data)
File "/usr/lib/unified/platform-communique/app/communique/tabular_report.py", line 325, in generate_payload
return self.generate_single_section_payload(req_data, section)
File "/usr/lib/unified/platform-communique/app/communique/tabular_report.py", line 334, in generate_single_section_payload
dataframe, cols = self.generate_section_payload(req_data, section)
File "/usr/lib/unified/platform-communique/app/communique/tabular_report.py", line 329, in generate_section_payload
result = self.dispatcher(self, req_data, section)
File "/usr/lib/unified/platform-communique/app/communique/tabular_report.py", line 185, in dispatcher
return method_callable(req_data)
File "/usr/lib/unified/platform-communique/app/resources/facebook/facebook_raw.py", line 837, in generate_main
results = self.query_facebook_data(campaign_data, req_data)
File "/usr/lib/unified/platform-communique/app/resources/facebook/facebook_raw.py", line 779, in query_facebook_data
field_var.append(j['name'])
TypeError: string indices must be integers
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment