Power Automate: 3000 and 8000 Blank Ad ID Email

[jesusitd]

Checklist:

• [x] JG: Create SQL for View
• [x] EG: Create VIEW in Production
• [x] JG: Set up Flow in Power Automate for 3000
• [ ] JG: Set up Flow in Power Automate for 8000

CC @fedegarza

[jesusitd]

PID 3000 (10/5/2020), requested to @fedegarza

• [x] EG Task: Create view in Production

SELECT
    l.id AS intakedesk_internal_id
    , l.date_entered AS date_entered_date_utctime
    , l.first_name AS lead_first_name
    , l.last_name AS lead_last_name
    , l.phone_home AS phone
    , LOWER(ea.email_address_caps) AS email_address
    , l.lead_form_landing_page
    , p.short_name AS lead_tort
    , lc.subid_c AS current_ad_id_number_code
    , '' AS correct_ad_id_number
FROM
    suitecrm.leads l
JOIN suitecrm.leads_cstm lc ON
    l.id = lc.id_c
    AND lc.pid_c = '3000'
    AND NULLIF(
        lc.subid_c
        , ''
    ) IS NULL
LEFT JOIN suitecrm.campaigns cmp ON
    l.campaign_id = cmp.id
    AND cmp.deleted = 0
JOIN suitecrm.aos_products p ON
    l.lead_form_product_id = p.id
    AND p.deleted = 0
LEFT JOIN suitecrm.email_addr_bean_rel eb ON
    l.id = eb.bean_id
    AND eb.bean_module = 'Leads'
    AND eb.primary_address = 1
    AND eb.deleted = 0
LEFT JOIN suitecrm.email_addresses ea ON
    eb.email_address_id = ea.id
    AND ea.deleted = 0
WHERE
    TRUE
    AND l.test = 0
    AND l.date_entered >= DATE_ADD(
        CURDATE(), INTERVAL -7 DAY
    )
    AND l.deleted = 0
ORDER BY
    l.date_entered DESC ;

[jesusitd]

@fedegarza hi Let me know when the View is in production thanks!

[jesusitd]

We are rolling this out soon. Preparing for 8000 too (change for 3000 if needed)

-- 8000
SELECT
    l.id AS intakedesk_internal_id
    , l.date_entered AS date_entered_date_utctime
    , l.first_name AS lead_first_name
    , l.last_name AS lead_last_name
    , l.phone_home AS phone
    , lower(ea.email_address_caps) AS email_address
    , l.lead_form_landing_page
    , p.short_name AS lead_tort
    , '' AS correct_ad_id_number
FROM
    suitecrm.leads l
JOIN suitecrm.leads_cstm lc ON
    l.id = lc.id_c
    AND lc.pid_c = '8000'
    AND NULLIF(
        lc.subid_c, ''
    ) IS NULL
LEFT JOIN suitecrm.campaigns cmp ON
    l.campaign_id = cmp.id
    AND cmp.deleted = 0
LEFT JOIN suitecrm.aos_products p ON
    l.lead_form_product_id = p.id
    AND p.deleted = 0
LEFT JOIN suitecrm.email_addr_bean_rel eb ON
    l.id = eb.bean_id
    AND eb.bean_module = 'Leads'
    AND eb.primary_address = 1
    AND eb.deleted = 0
LEFT JOIN suitecrm.email_addresses ea ON
    eb.email_address_id = ea.id
    AND ea.deleted = 0
WHERE
    TRUE
    AND l.test = 0
    AND l.date_entered >= DATE_ADD(
        CURDATE(), INTERVAL -7 DAY
    )
    AND l.deleted = 0
ORDER BY
    l.date_entered

@fedegarza please see this SQL above for Blank Ad ID. Not sure if we can pass the PID as a parameter, we'd just need not to display the PID number in the email we send.

[jesusitd]

As we sought for PID 8000's assistance on this, they claim they cannot find the Ad IDs of the leads.

"

1. I did some more digging in the ads manager to see if I can find why leads are coming in with no Ad ID. My initial thought was that an ad creative must not have had the UTM code associated with it, but I looked through all ads and they all have UTM codes. Also, the only leads that came in today were from 1 ad so it's weird that you'd have some with the ad ID and some without when it all came from the same ad. If the ad ID's are dependent on the UTMs coming through on submission, I'm wondering if some people's browsers are stripping out the UTMs for some reason. However, if you haven't experienced this with any of the other agencies running ads for you, I wouldn't think that was the case. "

With this, it is technically unfeasible to set up this automated process for PID 8000.

[jesusitd]

For PID 3000, I was looking into the view suitecrm.leads_invalid_ad_id_3000 for leads with No Ad ID.

As I go through the leads, I check in LeadConduit and they don't have any values for cid, asid nor aid either. They look a lot like Organics.

[jesusitd]

UPDATE: 3000 was set to receive "Blank Ad ID" emails starting yesterday, twice a day. We are still expecting to receive their response to start processing the data automatically.

[jesusitd]

No setting 8000 or 6000 up for now.