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_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_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 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
"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
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;
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
