Cek Funnel Stage

Cek Convert Stage

Select (leads)

select_convert.sql

select 

"timestamp", 
source, medium,
phone, name, contact_owner, assigned_pic, leads_qualification, 
"is_inbound?",leads_date, "is_leads?",mql_date, "is_mql?","mqlPlus_date", "is_mqlPlus?","tourPlus_date", "is_tourPlus?","trialPlus_date", "is_trialPlus?",convert_date, "is_convert?",

tour_date,"is_tour_show?", trial_date, "is_trial_show?", last_appt_category

from public.leads
WHERE convert_date IS NOT NULL
  AND (
    leads_date IS NULL 
    OR mql_date IS NULL 
    OR "mqlPlus_date" IS NULL 
    OR "tourPlus_date" IS NULL 
    OR "trialPlus_date" IS NULL 
    OR "is_leads?" IS NULL 
    OR "is_leads?" = FALSE 
    OR "is_mql?" IS NULL 
    OR "is_mql?" = FALSE 
    OR "is_mqlPlus?" IS NULL 
    OR "is_mqlPlus?" = FALSE 
    OR "is_tourPlus?" IS NULL 
    OR "is_tourPlus?" = FALSE 
    OR "is_trialPlus?" IS NULL 
    OR "is_trialPlus?" = FALSE 
    OR "is_convert?" IS NULL 
    OR "is_convert?" = FALSE
  );

Update (leads)

update_if_convert_from_tour

UPDATE public.leads
SET
    "mqlPlus_date" = CASE 
        WHEN last_appt_category = 'Tour' THEN tour_date
        WHEN last_appt_category = 'Free Trial' THEN trial_date
        ELSE "mqlPlus_date" -- Keep existing value if no condition matches
    END,
    "is_mqlPlus?" = CASE
        WHEN last_appt_category IN ('Tour', 'Free Trial') THEN TRUE
        WHEN last_appt_category IS NULL THEN TRUE
        ELSE "is_mqlPlus?" -- Keep existing value if no condition matches
    END,
    "tourPlus_date" = CASE 
        WHEN last_appt_category = 'Tour' THEN convert_date
        WHEN last_appt_category = 'Free Trial' THEN trial_date
        ELSE "tourPlus_date" -- Keep existing value if no condition matches
    END,
    "is_tourPlus?" = CASE
        WHEN last_appt_category IN ('Tour', 'Free Trial') THEN TRUE
        WHEN last_appt_category IS NULL THEN TRUE
        ELSE "is_tourPlus?" -- Keep existing value if no condition matches
    END,
    "trialPlus_date" = CASE 
        WHEN last_appt_category = 'Tour' THEN convert_date
        WHEN last_appt_category = 'Free Trial' THEN trial_date
        ELSE "trialPlus_date" -- Keep existing value if no condition matches
    END,
    "is_trialPlus?" = CASE
        WHEN last_appt_category IN ('Tour', 'Free Trial') THEN TRUE
        WHEN last_appt_category IS NULL THEN TRUE
        ELSE "is_trialPlus?" -- Keep existing value if no condition matches
    END,
    "is_convert?" = CASE
        WHEN convert_date IS NOT NULL THEN TRUE
        ELSE "is_convert?" -- Keep existing value if no condition matches
    END
WHERE convert_date IS NOT NULL
  AND (
    leads_date IS NULL 
    OR mql_date IS NULL 
    OR "mqlPlus_date" IS NULL 
    OR "tourPlus_date" IS NULL 
    OR "trialPlus_date" IS NULL 
    OR "is_leads?" IS NULL 
    OR "is_leads?" = FALSE 
    OR "is_mql?" IS NULL 
    OR "is_mql?" = FALSE 
    OR "is_mqlPlus?" IS NULL 
    OR "is_mqlPlus?" = FALSE 
    OR "is_tourPlus?" IS NULL 
    OR "is_tourPlus?" = FALSE 
    OR "is_trialPlus?" IS NULL 
    OR "is_trialPlus?" = FALSE 
    OR "is_convert?" IS NULL 
    OR "is_convert?" = FALSE
  );

Select (family)

select_convert_family.sql

select 

created_date, 
source, medium,
family_phone, family_name, contact_owner, assigned_pic, 
"is_inbound?",leads_date, "is_leads?",mql_date, "is_mql?","mqlPlus_date", "is_mqlPlus?","tourPlus_date", "is_tourPlus?","trialPlus_date", "is_trialPlus?",convert_date, "is_convert?",

tour_date,"is_tour_show?", trial_date, "is_trial_show?", last_appt_category

from public.family
WHERE convert_date IS NOT NULL
  AND (
    leads_date IS NULL 
    OR mql_date IS NULL 
    OR "mqlPlus_date" IS NULL 
    OR "tourPlus_date" IS NULL 
    OR "trialPlus_date" IS NULL 
    OR "is_leads?" IS NULL 
    OR "is_leads?" = FALSE 
    OR "is_mql?" IS NULL 
    OR "is_mql?" = FALSE 
    OR "is_mqlPlus?" IS NULL 
    OR "is_mqlPlus?" = FALSE 
    OR "is_tourPlus?" IS NULL 
    OR "is_tourPlus?" = FALSE 
    OR "is_trialPlus?" IS NULL 
    OR "is_trialPlus?" = FALSE 
    OR "is_convert?" IS NULL 
    OR "is_convert?" = FALSE
  );

Update (family)

UPDATE public.family
SET
    "mqlPlus_date" = CASE 
        WHEN last_appt_category = 'Tour' THEN tour_date
        WHEN last_appt_category = 'Free Trial' THEN trial_date
        ELSE "mqlPlus_date" -- Keep existing value if no condition matches
    END,
    "is_mqlPlus?" = CASE
        WHEN last_appt_category IN ('Tour', 'Free Trial') THEN TRUE
        WHEN last_appt_category IS NULL THEN TRUE
        ELSE "is_mqlPlus?" -- Keep existing value if no condition matches
    END,
    "tourPlus_date" = CASE 
        WHEN last_appt_category = 'Tour' THEN convert_date
        WHEN last_appt_category = 'Free Trial' THEN trial_date
        ELSE "tourPlus_date" -- Keep existing value if no condition matches
    END,
    "is_tourPlus?" = CASE
        WHEN last_appt_category IN ('Tour', 'Free Trial') THEN TRUE
        WHEN last_appt_category IS NULL THEN TRUE
        ELSE "is_tourPlus?" -- Keep existing value if no condition matches
    END,
    "trialPlus_date" = CASE 
        WHEN last_appt_category = 'Tour' THEN convert_date
        WHEN last_appt_category = 'Free Trial' THEN trial_date
        ELSE "trialPlus_date" -- Keep existing value if no condition matches
    END,
    "is_trialPlus?" = CASE
        WHEN last_appt_category IN ('Tour', 'Free Trial') THEN TRUE
        WHEN last_appt_category IS NULL THEN TRUE
        ELSE "is_trialPlus?" -- Keep existing value if no condition matches
    END,
    "is_convert?" = CASE
        WHEN convert_date IS NOT NULL THEN TRUE
        ELSE "is_convert?" -- Keep existing value if no condition matches
    END
WHERE convert_date IS NOT NULL
  AND (
    leads_date IS NULL 
    OR mql_date IS NULL 
    OR "mqlPlus_date" IS NULL 
    OR "tourPlus_date" IS NULL 
    OR "trialPlus_date" IS NULL 
    OR "is_leads?" IS NULL 
    OR "is_leads?" = FALSE 
    OR "is_mql?" IS NULL 
    OR "is_mql?" = FALSE 
    OR "is_mqlPlus?" IS NULL 
    OR "is_mqlPlus?" = FALSE 
    OR "is_tourPlus?" IS NULL 
    OR "is_tourPlus?" = FALSE 
    OR "is_trialPlus?" IS NULL 
    OR "is_trialPlus?" = FALSE 
    OR "is_convert?" IS NULL 
    OR "is_convert?" = FALSE
  );

Select Combined

select_combined

select 

"timestamp", 
source, medium,
phone, name, contact_owner, assigned_pic, leads_qualification, 
"is_inbound?",leads_date, "is_leads?",mql_date, "is_mql?","mqlPlus_date", "is_mqlPlus?","tourPlus_date", "is_tourPlus?","trialPlus_date", "is_trialPlus?",convert_date, "is_convert?",

tour_date,"is_tour_show?", trial_date, "is_trial_show?", last_appt_category

from public.combined
WHERE convert_date IS NOT NULL
  AND (
    leads_date IS NULL 
    OR mql_date IS NULL 
    OR "mqlPlus_date" IS NULL 
    OR "tourPlus_date" IS NULL 
    OR "trialPlus_date" IS NULL 
    OR "is_leads?" IS NULL 
    OR "is_leads?" = FALSE 
    OR "is_mql?" IS NULL 
    OR "is_mql?" = FALSE 
    OR "is_mqlPlus?" IS NULL 
    OR "is_mqlPlus?" = FALSE 
    OR "is_tourPlus?" IS NULL 
    OR "is_tourPlus?" = FALSE 
    OR "is_trialPlus?" IS NULL 
    OR "is_trialPlus?" = FALSE 
    OR "is_convert?" IS NULL 
    OR "is_convert?" = FALSE
  );

Cek Trial+ Stage

Table Leads

cek_funnel_trialPlus.sql

select 

"timestamp", 
source, medium,
phone, name, contact_owner, assigned_pic, leads_qualification, 
"is_inbound?",leads_date, "is_leads?",mql_date, "is_mql?","mqlPlus_date", "is_mqlPlus?","tourPlus_date", "is_tourPlus?","trialPlus_date", "is_trialPlus?",convert_date, "is_convert?",

tour_date,"is_tour_show?", trial_date, "is_trial_show?", last_appt_category

from public.leads
WHERE "trialPlus_date" IS NOT NULL
  AND (
    leads_date IS NULL 
    OR mql_date IS NULL 
    OR "mqlPlus_date" IS NULL 
    OR "tourPlus_date" IS NULL 
    OR "trialPlus_date" IS NULL 
    OR "is_leads?" IS NULL 
    OR "is_leads?" = FALSE 
    OR "is_mql?" IS NULL 
    OR "is_mql?" = FALSE 
    OR "is_mqlPlus?" IS NULL 
    OR "is_mqlPlus?" = FALSE 
    OR "is_tourPlus?" IS NULL 
    OR "is_tourPlus?" = FALSE 
    OR "is_trialPlus?" IS NULL 
    OR "is_trialPlus?" = FALSE 
  )
  order by "trialPlus_date" asc;

Table Family

select 

created_date, 
source, medium,
family_phone, family_name, contact_owner, assigned_pic, 
"is_inbound?",leads_date, "is_leads?",mql_date, "is_mql?","mqlPlus_date", "is_mqlPlus?","tourPlus_date", "is_tourPlus?","trialPlus_date", "is_trialPlus?",convert_date, "is_convert?",

tour_date,"is_tour_show?", trial_date, "is_trial_show?", last_appt_category

from public.family
WHERE "trialPlus_date" IS NOT NULL
  AND (
    leads_date IS NULL 
    OR mql_date IS NULL 
    OR "mqlPlus_date" IS NULL 
    OR "tourPlus_date" IS NULL 
    OR "trialPlus_date" IS NULL 
    OR "is_leads?" IS NULL 
    OR "is_leads?" = FALSE 
    OR "is_mql?" IS NULL 
    OR "is_mql?" = FALSE 
    OR "is_mqlPlus?" IS NULL 
    OR "is_mqlPlus?" = FALSE 
    OR "is_tourPlus?" IS NULL 
    OR "is_tourPlus?" = FALSE 
    OR "is_trialPlus?" IS NULL 
    OR "is_trialPlus?" = FALSE 
  )
  order by "trialPlus_date" asc;

Cek Tour+ Stage

cek_funnel_tourPlus.sql

select 

"timestamp", 
source, medium,
phone, name, contact_owner, assigned_pic, leads_qualification, 
"is_inbound?",leads_date, "is_leads?",mql_date, "is_mql?","mqlPlus_date", "is_mqlPlus?","tourPlus_date", "is_tourPlus?","trialPlus_date", "is_trialPlus?",convert_date, "is_convert?",

tour_date,"is_tour_show?", trial_date, "is_trial_show?", last_appt_category

from public.leads
where "tourPlus_date" is not null and (leads_date is null OR mql_date is null OR "mqlPlus_date" is null OR
"is_leads?" = NULL OR "is_leads?" = FALSE OR
"is_mql?" = NULL OR "is_mql?" = FALSE OR
"is_mqlPlus?" = NULL OR "is_mqlPlus?" = FALSE OR
"is_tourPlus?" = NULL OR "is_tourPlus?" = FALSE
)
order by "tourPlus_date" asc

Cek MQL+ Stage

cek_funnel_mqlPlus.sql

select 

"timestamp", 
source, medium,
phone, name, contact_owner, assigned_pic, leads_qualification, 
"is_inbound?",leads_date, "is_leads?",mql_date, "is_mql?","mqlPlus_date", "is_mqlPlus?","tourPlus_date", "is_tourPlus?","trialPlus_date", "is_trialPlus?",convert_date, "is_convert?",

tour_date,"is_tour_show?", trial_date, "is_trial_show?", last_appt_category

from public.leads
where "mqlPlus_date" is not null and (leads_date is null OR mql_date is null OR
"is_leads?" = NULL OR "is_leads?" = FALSE OR
"is_mql?" = NULL OR "is_mql?" = FALSE OR
"is_mqlPlus?" = NULL OR "is_mqlPlus?" = FALSE
)
order by "mqlPlus_date" asc

Cek MQL Stage

cek_funnel_mql.sql

select 

"timestamp", 
source, medium,
phone, name, contact_owner, assigned_pic, leads_qualification, 
"is_inbound?",leads_date, "is_leads?",mql_date, "is_mql?","mqlPlus_date", "is_mqlPlus?","tourPlus_date", "is_tourPlus?","trialPlus_date", "is_trialPlus?",convert_date, "is_convert?",

tour_date,"is_tour_show?", trial_date, "is_trial_show?", last_appt_category

from public.leads
where mql_date is not null and (leads_date is null OR
"is_leads?" = NULL OR "is_leads?" = FALSE OR
"is_mql?" = NULL OR "is_mql?" = FALSE
)
order by mql_date asc

Cek Leads Stage

cek_funnel_leads.sql

select 

"timestamp", 
source, medium,
phone, name, contact_owner, assigned_pic, leads_qualification, 
"is_inbound?",leads_date, "is_leads?",mql_date, "is_mql?","mqlPlus_date", "is_mqlPlus?","tourPlus_date", "is_tourPlus?","trialPlus_date", "is_trialPlus?",convert_date, "is_convert?",

tour_date,"is_tour_show?", trial_date, "is_trial_show?", last_appt_category

from public.leads
where leads_date is not null and ("is_leads?" is null OR "is_leads?" = FALSE)
order by leads_date asc