Leads Queries

Querying Leads Raw & Leads Standardized

--==================================================================================
-- QUERYING LEADS_RAW
--==================================================================================
SELECT  lead_data_supplier, start_time, created_ts, *
FROM sales.leads_raw
WHERE property_id in (${PROP IDS SEP BY COMMAS})
-- where client_id=502
and lead_data_supplier='ELEAD'
--and lead_data_supplier = 'iHeartMedia'
  -- and source_document = '2024 01 - CardinaleWay Mazda - Mesa - DealerSocket.csv'
-- and created_ts >= '2024-01-24'
 --and created_ts < '2023-05-13'
AND (to_date(start_time :: TEXT, 'MM/DD/YYYY') >= '${MINDATE}'
      AND to_date(start_time :: TEXT, 'MM/DD/YYYY') < '${MAXDATE LESS THAN}');
--order by created_ts asc

select source_document, count(*) from sales.leads_raw
where property_id in (869, 49358, 50309)
and lead_data_supplier='VinSolutions'
group by source_document;

--==================================================================================
-- QUERYING LEADS_STANDARDIZED
--==================================================================================

-- Lead counts by Property and Month for specific Lead Data Supplier
SELECT property_id, to_date(start_time:: TEXT, 'YYYY-MM'), count(*)
FROM sales.leads_standardized
WHERE property_id in (${PROP IDS SEP BY COMMAS})
AND lead_data_supplier = '${LEAD DATA SUPPLIER}'
AND start_time >= '${MINDATE}'
AND start_time < '${MAXDATE LESS THAN}'
GROUP BY property_id, to_date(start_time:: TEXT, 'YYYY-MM')
ORDER BY property_id, to_date(start_time:: TEXT, 'YYYY-MM') ASC;

-- Lead counts by Property and Month for specific Lead Type
SELECT property_id, to_date(start_time:: TEXT, 'YYYY-MM'), count(*)
FROM sales.leads_standardized
WHERE property_id in (${PROP IDS SEP BY COMMAS})
AND lead_type = '${LEAD TYPE}'
AND start_time >= '${MINDATE}'
AND start_time < '${MAXDATE LESS THAN}'
GROUP BY property_id, to_date(start_time:: TEXT, 'YYYY-MM')
ORDER BY property_id, to_date(start_time:: TEXT, 'YYYY-MM') ASC;

-- Lead counts by Day
SELECT to_date(start_time:: TEXT, 'YYYY-MM-DD'), count(*)
FROM sales.leads_standardized
WHERE property_id in (${PROP IDS SEP BY COMMAS})
AND lead_data_supplier = '${LEAD DATA SUPPLIER}'
AND start_time >= '${MINDATE}'
AND start_time < '${MAXDATE LESS THAN}'
GROUP BY to_date(start_time:: TEXT, 'YYYY-MM-DD')
ORDER BY to_date(start_time:: TEXT, 'YYYY-MM-DD') ASC;


-- Lead counts for specific sources by month
SELECT to_date(leads_standardized.start_time:: TEXT, 'YYYY-MM'), count(*)
FROM sales.leads_standardized
JOIN sales.leads_raw on sales.leads_standardized.raw_lead_id = sales.leads_raw.raw_lead_id
WHERE leads_standardized.property_id in (${PROP IDS SEP BY COMMAS})
AND leads_standardized.lead_data_supplier = '${LEAD DATA SUPPLIER}'
--AND lower(leads_raw.raw_source) in (lower('${RAWSOURCES SEP BY COMMAS}'))
  AND lower(leads_raw.raw_source) like 'gubagoo%'
AND leads_standardized.start_time >= '${MINDATE}'
AND leads_standardized.start_time < '${MAXDATE LESS THAN}'
GROUP BY to_date(leads_standardized.start_time:: TEXT, 'YYYY-MM')
ORDER BY to_date(leads_standardized.start_time:: TEXT, 'YYYY-MM') ASC;


-- Find Earliest Lead Date for Properties
SELECT leads_standardized.property_id, property_name, lead_data_supplier, MIN(start_time), MAX(start_time)
FROM sales.leads_standardized
JOIN mongodb.prod_properties_ft ON leads_standardized.property_id=prod_properties_ft.property_id
WHERE leads_standardized.property_id in (${PROPID})
--AND lead_data_supplier in(${LEAD DATA SUPPLIERS WITH QUOTES AROUND EACH})
AND lead_type='${LEAD TYPE}'
GROUP BY leads_standardized.property_id, property_name, lead_data_supplier
ORDER BY leads_standardized.property_id, property_name, lead_data_supplier;
--HAVING MIN(start_time) > '{$MIN START_TIME}';

Querying Leads Filtered

-------------------------------------------------------------------------
-- QUERYING LEADS FILTERED PAST 2 HOURS --
select sales.leads_raw.source_document, * from sales.leads_filtered
         JOIN sales.leads_raw ON sales.leads_raw.raw_lead_id = sales.leads_filtered.raw_lead_id
         where sales.leads_filtered.filtered_date > (now() - interval '2 hour')
        and lower(sales.leads_raw.source_document) not like '%api%'
        and lower(sales.leads_raw.source_document) not like '%tmi%';

-- FILTERED LEADS COUNTS BY PROPERTY WITH REASON
SELECT sales.leads_filtered.reason, count(*) FROM sales.leads_raw
         JOIN sales.leads_filtered ON sales.leads_raw.raw_lead_id = sales.leads_filtered.raw_lead_id
WHERE  sales.leads_raw.property_id = ${propid}
AND   leads_raw.created_ts >= (to_date('${startdate YYYY-MM-DD}','YYYY-MM-DD') - interval '2 month')    -- created in the past 2 months (gives us the last 3 months of leads)
GROUP BY leads_filtered.reason;

-- FILTERED LEADS BY PROPERTY WITH REASON
SELECT sales.leads_filtered.reason, sales.leads_raw.source_document, sales.leads_raw.campaign_phone_number, sales.leads_raw.campaign_name, sales.leads_raw.* FROM sales.leads_raw
         JOIN sales.leads_filtered ON sales.leads_raw.raw_lead_id = sales.leads_filtered.raw_lead_id
WHERE  sales.leads_raw.property_id = ${propid}
AND   leads_raw.created_ts >= (to_date('${startdate YYYY-MM-DD}','YYYY-MM-DD') - interval '7 days');    -- created in the past 7 days

-- FILTERED LEADS BY PROPERTY AND SOURCEDOC WITH REASON
SELECT sales.leads_filtered.reason, sales.leads_raw.source_document, sales.leads_raw.campaign_phone_number, sales.leads_raw.campaign_name, sales.leads_raw.* FROM sales.leads_raw
         JOIN sales.leads_filtered ON sales.leads_raw.raw_lead_id = sales.leads_filtered.raw_lead_id
WHERE  sales.leads_raw.property_id = ${propid}
AND sales.leads_raw.source_document = '${SOURCEDOC}';

-- FILTERED LEADS BY SOURCEDOC WITH REASON
SELECT sales.leads_filtered.reason, sales.leads_filtered.filtered_date, sales.leads_filtered.sales.leads_raw.source_document, sales.leads_raw.campaign_phone_number, sales.leads_raw.campaign_name, sales.leads_raw.* FROM sales.leads_raw
         JOIN sales.leads_filtered ON sales.leads_raw.raw_lead_id = sales.leads_filtered.raw_lead_id
WHERE  sales.leads_raw.source_document = '${SOURCEDOC}';

-- Get Sales Filtered Counts with Reasons for the past [timeframe]
SELECT reason, count(*) FROM sales.leads_filtered
where filtered_date >= (now() - interval '1 MONTH')
GROUP BY reason
ORDER BY reason ASC;

-- Get All Leads Filtered Reasons
SELECT distinct reason FROM sales.leads_filtered
GROUP BY reason
ORDER BY reason ASC;

Leads Statements

Deleting A Leads File

-------------------------------------------------------------------------
-- DELETING A PROCESSED LEADS FILE --
-- This will also remove the data from leads.filtered and leads.standardized as well --
-- 1. Find the name of the file - either in Leads Preprocessing board Source Document column
--    OR use this query.  Note: Filename may not always be what you think it is (for Excel files, it often appends _Sheetname)
SELECT source_document, * FROM sales.leads_raw WHERE property_id = '${PROPID}'
AND lead_data_supplier = '${LEAD DATA SUPPLIER}'
AND start_time > '${STARTTIME}';
-- OR --
SELECT * from sales.leads_raw where property_id = '${PROPID}' AND source_document like '${SOURCEDOC}%';
-- OR --
SELECT * from sales.leads_raw where source_document like '${SOURCEDOC}%';

-- 2. Delete it out
-- Do NOT set reprocess_row=TRUE also!! The DAG will pick up the rows and delete them with only delete_row=TRUE.
UPDATE sales.leads_raw SET delete_row = TRUE WHERE property_id = '${PROPID}' AND source_document like '${SOURCEDOC}%';
-- 3. Run etl_leads_preprocessing_processing to delete the rows out!!

--Delete Leads by Source Document ONLY
UPDATE sales.leads_raw SET delete_row = TRUE WHERE source_document like '${SOURCEDOC}%';

Leads loaded to Wrong Property ID

-------------------------------------------------------------------------
-- FIXING PROPERTY ID --
-- If you loaded leads to the wrong property, you can simply correct the property_id and set reprocess_file=TRUE --
-- NOTE: If XLS or XLSX file, the source_document will contain _SHEETNAME --
-- REMEMBER to MOVE THE FILE in Dropbox/Data Archive to the correct property folder so there is no confusion later! --
-- IF the filename is incorrect, it is best to just delete it out and reload it though! --
-- IF you are moving leads from one CLIENT group to another, you must ALSO update client_id!! --
-- YOU WILL NEED TO WAIT APPROX. 2 DAYS FOR MATCHING TO HAPPEN AGAIN & if this was prior to the current 3 processing months, you will need CT Refreshes also! --

-- Fixing Property ID By Source Doc UNDER THE SAME CLIENT --
-- 1. Move the data
UPDATE sales.leads_raw SET property_id='${NEWPROPID}', reprocess_file=TRUE where source_document = '${SOURCEDOCNAME}' and property_id='${OLDPROPID}';
-- 2. If any Collects exist, make sure you rename all Dropbox IDD & Data Archive folders to the correct Property ID!
-- 3. If any Collects, Downloads, Automated Reports make sure you fix the property aliases in the property_aliases table.

-- Fixing Property ID By Source Doc UNDER A DIFFERENT CLIENT --
-- 1. Move the data - IF THE PROPERTY IS UNDER A DIFFERENT CLIENT, YOU NEED TO UPDATE CLIENT_ID ALSO!!! --
UPDATE sales.leads_raw SET property_id='${NEWPROPID}', client_id='${NEWCLIENTID}', reprocess_file=TRUE where source_document = '${SOURCEDOCNAME}' and property_id='${OLDPROPID}';
-- 2. If any Collects exist, make sure you rename all Dropbox IDD & Data Archive folders to the correct Property ID!
-- 3. If any Collects, Downloads, Automated Reports make sure you fix the property aliases in the property_aliases table.

-- Fixing Property ID for ALL LEADS for a property UNDER THE SAME CLIENT --
-- 1. Move the data
UPDATE sales.leads_raw SET property_id='${NEWPROPID}', reprocess_file=TRUE where property_id='${OLDPROPID}';
-- If any Collects exist, make sure you rename all Dropbox IDD & Data Archive folders to the correct Property ID!
-- If any Collects, Downloads, Automated Reports make sure you fix the property aliases in the property_aliases table.

-- Fixing Property ID for ALL LEADS for a property UNDER A DIFFERENT CLIENT --
-- 1. Move the data
UPDATE sales.leads_raw SET property_id='${NEWPROPID}', client_id='${NEWCLIENTID}', reprocess_file=TRUE where property_id='${OLDPROPID}';
-- If any Collects exist, make sure you rename all Dropbox IDD & Data Archive folders to the correct Property ID!
-- If any Collects, Downloads, Automated Reports make sure you fix the property aliases in the property_aliases table.

Fixing a Leads Source Document Name

-------------------------------------------------------------------------
-- FIXING SOURCE_DOCUMENT --
-- If you loaded a file and named it incorrectly (for example put the wrong year in the filename)
-- you can simply correct the source_document by updating leads_raw and leads_standardized
-- The file does not need to be reprocessed.
-- Remember to rename the file in Data Archive after you are done here or it could cause a conflict in the future!!

-- Updating by source_document and property_id (for single property files)
-- 1. Select both statements and run
UPDATE sales.leads_raw SET source_document='${NEWDOCNAME}' where source_document='${OLDDOCNAME}' and property_id = '${PROPID}';
UPDATE sales.leads_standardized SET source_document='${NEWDOCNAME}' where source_document='${OLDDOCNAME}' and property_id = '${PROPID}';
-- 2. Rename the file in Data Archive!

-- Updating by source_document only (for multi files)
-- 1. Select both statements and run
UPDATE sales.leads_raw SET source_document='${NEWDOCNAME}' where source_document='${OLDDOCNAME}';
UPDATE sales.leads_standardized SET source_document='${NEWDOCNAME}' where source_document='${OLDDOCNAME}';
-- 2. Rename the file in Data Archive!