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

user030892
user030892 Member
in Magic ETL

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

Tagged:

Comments

  • GrantSmith
    GrantSmith Coach

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    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.  

     

     

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories