Develop Notification for Wrong Facebook URL Tags

[jesusitd]

• [ ] 2000
• [x] 3000
• [ ] 6000
• [ ] 8000

[jesusitd]

2000: WAIT UNTIL NOTICE FROM @fedegarza on making sure ads are configured in "Tracking" section of FB Ads Manager.

6000: Same

[jesusitd]

SQL Snippet to detect tags. Supports UrlTags and ObjectStorySpecLinkData:

CREATE VIEW facebook_sync.ads_no_tags_7d
AS
SELECT
    a.Target AS 'ad_account_id'
    , caa.vendor_pid AS 'pid'
    , a.CampaignId AS 'ad_campaign_id'
    , a.AdSetId AS 'ad_set_id'
    , a.ID AS 'ad_id'
    , a.AdCreativeId AS 'ad_creative_id'
    , a.AdStatus AS 'ad_status'
    , ac.UrlTags AS 'creative_urltags'
    , ac.ObjectStorySpecLinkData AS 'creative_objectstoryspeclinkdata'
    , IFNULL(ac.UrlTags REGEXP '{{ad.id}}', 0) AS 'url_tag_valid_ad'
    , IFNULL(ac.UrlTags REGEXP '{{adset.id}}', 0) AS 'url_tag_valid_adset'
    , IFNULL(ac.UrlTags REGEXP '{{campaign.id}}', 0) AS 'url_tag_valid_campaign'
    , IFNULL(ac.UrlTags REGEXP '{{placement}}', 0) AS 'url_tag_valid_placement'
    , IFNULL(ac.UrlTags REGEXP '{{site_source_name}}', 0) AS 'url_tag_valid_platform'
    , IFNULL(ac.ObjectStorySpecLinkData REGEXP '{{ad.id}}', 0) AS 'link_valid_ad'
    , IFNULL(ac.ObjectStorySpecLinkData REGEXP '{{adset.id}}', 0) AS 'link_valid_adset'
    , IFNULL(ac.ObjectStorySpecLinkData REGEXP '{{campaign.id}}', 0) AS 'link_valid_campaign'
    , IFNULL(ac.ObjectStorySpecLinkData REGEXP '{{placement}}', 0) AS 'link_valid_placement'
    , IFNULL(ac.ObjectStorySpecLinkData REGEXP '{{site_source_name}}', 0) AS 'link_valid_platform'
    , COALESCE((SELECT `url_tag_valid_ad`), 0) + COALESCE((SELECT `link_valid_ad`), 0) AS 'valid_ad'
    , COALESCE((SELECT `url_tag_valid_adset`), 0) + COALESCE((SELECT `link_valid_adset`), 0) AS 'valid_adset'
    , COALESCE((SELECT `url_tag_valid_campaign`), 0) + COALESCE((SELECT `link_valid_campaign`), 0) AS 'valid_campaign'
    , COALESCE((SELECT `url_tag_valid_placement`), 0) + COALESCE((SELECT `link_valid_placement`), 0) AS 'valid_placement'
    , COALESCE((SELECT `url_tag_valid_platform`), 0) + COALESCE((SELECT `link_valid_platform`), 0) AS 'valid_platform'
    , CASE WHEN (SELECT `valid_ad`) < 1 THEN 'Missing {{ad.id}} URL tag' END AS 'verdict_ad'
    , CASE WHEN (SELECT `valid_adset`) < 1 THEN 'Missing {{adset.id}} URL tag' END AS 'verdict_ad_set'
    , CASE WHEN (SELECT `valid_campaign`) < 1 THEN 'Missing {{campaign.id}} URL tag' END AS 'verdict_campaign'
    , CASE WHEN (SELECT `valid_placement`) < 1 THEN 'Missing {{placement}} URL tag' END AS 'verdict_placement'
    , CASE WHEN (SELECT `valid_platform`) < 1 THEN 'Missing {{site_source_name}} URL tag' END AS 'verdict_platform'
    , IF((SELECT `verdict_ad`) IS NOT NULL, 1, 0)
        + IF((SELECT `verdict_ad_set`) IS NOT NULL, 1, 0)
        + IF((SELECT `verdict_campaign`) IS NOT NULL, 1, 0)
        + IF((SELECT `verdict_placement`) IS NOT NULL, 1, 0)
        + IF((SELECT `verdict_platform`) IS NOT NULL, 1, 0)
        AS `verdicts_count`
FROM
    Ads a
JOIN AdCreatives ac ON
    a.AdCreativeId = ac.ID
JOIN crm_ad_accounts caa ON a.Target = caa.source_account_id
WHERE
    a.ID IN (
    SELECT
         aia.AdId
    FROM
        AdInsights_Ad aia 
    INNER JOIN Campaigns c2 ON
        aia.CampaignId = c2.ID
        AND (
            CASE
                WHEN c2.Target IN (
                    -- Multi-Client Section 3000
                    'act_493368164186325'   -- IVC
                    , 'act_441886306001178' -- Xarelto
                    , 'act_441339949389147' -- Talcum Powder
                    , 'act_669626523227154' -- Roundup
                    , 'act_478349139021561' -- Hernia Mesh
                    , 'act_474541719402303' -- Hip Replacement
                    , 'act_436495123206963' -- Taxotere
                    , 'act_1481600065305757' -- Hernia Mesh #3
                    , 'act_786840148408564' -- Hernia Mesh New
                    , 'act_297412920916167' -- 3M #3
                    , 'act_433008340653515' -- Zantac
                    , 'act_424706118470030' -- 3000 - Facebook - Talc DQ
                ) THEN LOCATE('intake', c2.`Name`) > 0
                ELSE TRUE
            END
        )
    WHERE aia.DateStart >= DATE_ADD(CURDATE(), INTERVAL -7 DAY)
);

(Added verdicts and PID with a new Data Sync Job "CRM Ad Accounts" to be able to tell the PIDs.

This is now stored in a VIEW called facebook_sync.ads_no_tags_7d.

[jesusitd]

@fedegarza hola Ed! I generated the view defined above that checks if a Creative has tags.

I am thinking of 2 things:

1. Have this view in a Power BI report where we can see what is complying and what is not
2. With this view, we can use Power Automate to query this view and dynamically generate a notification for every PID or even for us.

Let me know your thoughts!

[jesusitd]

3000's auto email rolled out today in Power Automate flow: https://us.flow.microsoft.com/manage/environments/Default-011d0e09-4e96-4356-a27f-398562aca580/flows/shared/91674601-71d5-4b9c-a038-3afa6d046f9e/details

[jesusitd]

This is working fine as of now.

Are we going to deploy for other providers? @fedegarza

This data is not being looked at for the moment so we might save it for later in the backlog.