Hello DOMO Community,
Questions:
1. Can you tell me why person_created_date is not filtering >= 01-01-2019 results?
-As you can see in the screenshot, the person_created_date is returning results prior to 2019.
2. Can you tell me why my SQL is not grouping the data by lead_marketing_channel in Domo Analyzer?
-As you can see in the screenshot, the data is simply returning too many rows and not grouping appropriately.
SQL:
SELECT
`person_marketing_channel` as lead_marketing_channel,
SUM(CASE WHEN dc.date is not null then 1 else 0 end) as lead_count,
SUM(CASE WHEN ds.date is not null then 1 else 0 end) as mql_count,
DATE_FORMAT(ds.`date`,'%m/%y') as lead_create_month,
SUM(CASE WHEN dc.date is not null then 1 else 0 end) / SUM(CASE WHEN ds.date is not null then 1 else 0 end) as lead_mql_conversion_rate
FROM
`transform_data_2` pcd
LEFT JOIN
`dim_date` ds on ds.`date` = cast(pcd.`person_created_date` as date)
LEFT JOIN
`dim_date` dc on dc.`date` = cast(pcd.`person_mql_date`as date)
WHERE
convert_tz(pcd.`person_created_date`, 'utc', 'us/pacific') >= '01-01-2019'
AND
pcd.`person_source_type` = "Inbound"
GROUP BY
1,4
Any solutions would be great! Thanks!
All the best,
Andrew Slutzky