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

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.

jaeW_at_Onyx

To @GrantSmith 's point '01-01-2019' won't necessarily automatically convert into a DATE when compared to the value on the left.

convert_tz(pcd.`person_created_date`, 'utc', 'us/pacific') >= '01-01-2019'

 

I believe '2019-01-01' WILL automatically get converted to a date.

 

Grant is correct, re. the CAST.

 

But to test it, you should run

SELECT

convert_tz(pcd.`person_created_date`, 'utc', 'us/pacific') as t1

,'01-01-2019' as t2

convert_tz(pcd.`person_created_date`, 'utc', 'us/pacific') >=  '01-01-2019' as t3 which must return the correct result to work in your query, but I suspect you're comparing a date to a string which returns the 'wrong result'.

 

Also, I would be wary of doing the timezone conversion in your ETL unless your data was not properly ingested in UTC timezone.