SQL Data Troubleshooting: Neither Date Filter Nor Conversion Rate DataFlow Calculation Working

user030892

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

Same Channel Not Grouped.png

lead_create_month not filtering.png

GrantSmith

Hi @user030892 ,

 

1) You should try explicitly casting your date string '01-01-2019' as a date:

cast('01-01-2019' as date)

 

2) Your data isn't getting grouped because you're not specifying an aggregate function for your fields. You need to select SUM, Average, Min, Max, Count etc for the fields you want to summarize and leave the fields you want to group as No Aggregation. You can do this by clicking on the field names in the columns section.