ITD CAMPAIGNS LIKELY: Include NO INTAKE DESK campaigns. Some slipped below the table

jesusitd commented 3 years ago

Suite:

-- QUERY 1
SELECT
    DISTINCT concat("'", subid_c, "',")
FROM
    leads l
JOIN leads_cstm lc ON
    l.id = lc.id_c
--  AND pid_c = '3000'
    AND length(subid_c) > 0
WHERE
    l.date_entered >= '2021-01-01'

;

-- QUERY 3
SELECT
    l.id AS 'IntakeDesk ID',
    ap_leadid_c,
    l.date_entered AS 'Posting Date (UTC Time)',
    aos_products.name AS 'Lead  Type',
    pid_c AS PID,
    subid_c AS 'Ad ID',
    l.first_name AS 'First Name',
    l.last_name AS 'Last Name',
    l.status,
    l.phone_home AS 'Phone',
    LOWER(ea.email_address_caps) AS 'Email Address',
    primary_address_postalcode
FROM
    leads l
    JOIN leads_cstm lc ON l.id = lc.id_c AND subid_c IN ('23846551191580189',
'23846551268720189',
'23846551280890189',
'23846551280900189',
'23846724380020680',
'23846973526660680',
'23846973601380680')
    LEFT JOIN aos_products ON l.lead_form_product_id = aos_products.id
    LEFT JOIN 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 email_addresses ea ON eb.email_address_id = ea.id AND ea.deleted = 0
WHERE
    l.deleted = 0
    AND l.date_entered >= '2021-01-01'
ORDER BY
    l.date_entered DESC;

SELECT
    subid_c AS 'Ad ID',
    count(*)
FROM
    leads l
    JOIN leads_cstm lc ON l.id = lc.id_c AND subid_c IN ('23846551191580189',
'23846551268720189',
'23846551280890189',
'23846551280900189',
'23846724380020680',
'23846973526660680',
'23846973601380680')
WHERE
    l.deleted = 0
    AND l.date_entered >= '2021-01-01'
GROUP BY subid_c 
ORDER BY
    2 DESC;

FB:

-- QUERY 2
SELECT
    concat("'", a.ID, "',")
    , cac.source_campaign_id
    , c.*
FROM
    Ads a
LEFT JOIN Campaigns c ON
    a.CampaignId = c.ID
LEFT JOIN facebook_sync_signatures.crm_ad_campaigns cac ON
    cac.source_campaign_id = c.ID
WHERE
    TRUE
    -- AND c.ID = '23846724379990680'
    AND cac.source_campaign_id IS NULL
    AND a.ID IN ( '23846267821660073', '23846471574980680', '23846542293770073', '23846542319270073', '23846354145370680', '23845777095520680', '23846235139500461', '23846551280900189', '23846152680340680', '23846038820220073', '23846449132600073', '23845320836250148', '23846503673760614', '23846430018860073', '23846449177680073', '23846430060060073', '23845131654000073', '23846446103950461', '23846562018200680', '23846590213880614', '23846318562290073', '23846569023220680', '23846152841300680', '23845956086260189', '23846587621400614', '6218558701991', '23846233836600073', '6222034402991', '23846075180690148', '23846434681180680', '23846374912740073', '23846430135600073', '23846446113630461', '23846430043580073', '23846547994080189', '6217581116391', '}', '23845334664760073', '23846547994100189', '23846399623400148', '6215259156191', '23845704474090073', '23846430049440073', '23846318551230073', '6213547450391', '23846469139200461', '23846503673780614', '23846678938890461', '23846267815730073', '23846234497100148', '23846593485640148', '23846562018210680', '23846038777730073', '23846173787030220', '6222034575191', '23846569023240680', '23846474703760189', '23846562018160680', '23846152680330680', '23846629940640614', '23846454834520614', '23846545636730614', '6211901451991', '23846629741670614', '23845861978780680', '23846593485660148', '23846562018180680', '23846629940780614', '23846629741690614', '23846252942160189', '23845858957260189', '23846495022170680', '23846629940690614', '6222110362391', '6215259094591', '23846434261330680', '6222110361391', 'Organic', '23846298505890268', '23846434342890680', '23846469139180461', '23845920610580073', '6222034403191', '23846629940630614', '23846446113620461', '23846551268720189', '23846629741680614', '23846551280890189', '23846551191580189', '23846495022250680', '23846678938930461', '23845832987190461', '23846495022180680', '23846678938920461', '23846562018170680', '23846338645730268', '23846576950090680', '23846576950050680', '23846545654240614', '23846576950100680', '23846361625830268', '23846361625860268', '23846545654260614', '23846481813260073', '23846629940650614', '23846629691600614', '23846571047550189', '23846571203410189', '23846571203350189', '23846571203450189', '23846571203400189', '23846571203360189', '23846571203390189', '23846571203480189', '6216543425391', '23846587621390614', '23846571047520189', '23846624527990680', '23846629940730614', '23846465464920680', '23846678938900461', '23846609592690680', '23846455655540680', '23846609592680680', '23846212795920189', '23846678938880461', '23846388282780189', '23846609728570680', '23846571203330189', '23846609592660680', '23846609592700680', '23846499843610073', '23846399623430148', '23846081171050073', '23846609592670680', '23846499866280073', '23846609592710680', '23846542274390073', '23846370602440680', '23846633088130680', '23846629940670614', '6215411382191', '23846624527980680', '23846569023200680', '23846633097730680', '23846633088120680', '6223223781391', '23846252942170189', '23846634115490680', '23846639660520680', '23846639687630680', '23846639687640680', '23846724794220614', '23846639687620680', '6223584759591', '6223584758991', '23846805321230073', '6223584759191', '2384699843610073', '23846445469670268', '23846542330190073', '6218295340391', '23846656638330680', '23846666733640680', '23846656613660680', '23846233839180073', '23846695170390073', '23845754240370680', '23846656684520680', '23846666768280680', '23846445469700268', '23846667364070680', '23846667308590680', '23846445469750268', '23846669836000680', '23846666984390680', '23846562963500073', '23846682750780680', '23846682651960680', '23846445469710268', '23846691813080680', '23846694903860680', '23846691950780680', '23846692029760680', '23846694896180680', '23846698789820680', '23846698773200680', '23846698773210680', '23846698789810680', '23846698824350680', '23845850290670461', '23846694903830680', '23846433798120680', '23846698757270680', '23845850290700461', '23846433798110680', '23846868985280461', '23846433798100680', '23846433798060680', '23846434462600680', '23846176709240461', '2384699866280073', '23846433798090680', '23846938097140680', '23846724380020680', '[aid]', '23846617154050073', '23846617185950073', '23846617125050073', '23846521732060268', '238464300490073', '23846734983730680', '23846250361670073', '2384654231970073', '23846531183920268', '23846894962860461', '23846894962900461', '23846753681020680', '23846454834490614', '23846817620010614', '23846894962840461', '23846894962830461', '23846941479840614', '23846695185370073', '23846577075940268', '23846543392770073', '23843830823280073', '23846804657140680', '23846817620000614', '23846894962680461', '23845158169830073', '23846629664350614', '23846817602900614', '23845990475820073', '23846445514150268', '23846695140670073', '23846695159610073', '23846445469830268', '23846629664360614', '23847051528530461', '23846804657130680', '23847062136530461', '23847062192160461', '23847062187370461', '23847062190020461', '23847062212450461', '23846848906080680', '23847062206480461', '23847040956820461', '23847062136540461', '23843628909910073', '23846894962980461', '23846743727410073', '23846743762150073', '23846743698350073', '23846894962690461', '23846941524050614', '23846894962760461', '23846768165780073', '23846902252480680', '23846902226600680', '23846894962720461', '23846902245890680', '23846902272630680', '23846902261860680', '23846894962890461', '23847041014760461', '23846902252470680', '23846805317140073', '23846938102430680', '23846818303280073', '23846818290640073', 'invalid', '23846894962970461', '23846956379350680', '6229051423191', '6229051423391', '23846968916720680', '23846968716270680', '23846968945040680', '23846973526660680', '23846973601380680', '23847190447920461' )

jesusitd commented 3 years ago

Latest version of procedure:

@CesarITD Use CData Sync to move Leads from SuiteCRM to Facebook_Sync:

SELECT lead_id, date_entered, pid_c, subid_c
WHERE date_entered >= DATE_ADD(NOW(), INTERVAL -90 DAY) -- last 90 days
Replicate on a Daily Basis, 06:00 UTC, in TRUNCATE Cdata sync mode
Name this table suitecrm_leads_90d

@CesarITD Create a View in facebook_sync.v_campaigns_itd_likely_by_leads:

Must give same columns we have in current email ITD Campaigns Likely
JOIN with the new table suitecrm_leads_90d
GROUP BY to get the COUNT of leads HAVING count(*) > 0
TRICK: Because the views Always lose their formatting in MySQL, it is better to create a Stored Procedure that in turn, Creates the View. It will not lose formatting. Use CREATE OR REPLACE VIEW facebook_sync.v_campaigns_itd_likely_by_leads

Jesus:

Create a Power Automate flow "ITD Campaigns Likely By Leads"
Must grab this view
Must email on a Daily Basis

jesusitd commented 3 years ago

Complemented with Power Automate: ITD Campaigns Likely V2 - 3000 Only

intakedesk / PowerBI-General

ITD CAMPAIGNS LIKELY: Include NO INTAKE DESK campaigns. Some slipped below the table #665

Latest version of procedure: