Counting unique orders each month

KieranVinomofo
KieranVinomofo Member
in Magic ETL

Hello

 

I'm trying to build a beastmode calculation that will only count users ordering for the first time in any given month.

My end game is to be able to have a vertical chart showing by day users ordering for the first time that month, however using a count(distinct) beast mode command will only tell me unique orders for the time period specified in the card filter.

 

My beastmode calc currently looks like this:

COUNT(DISTINCT case when `submitted_at` >= STR_TO_DATE(CONCAT(YEAR(`submitted_at`),'/',MONTH(`submitted_at`),'/1'),'%Y,%m,%d') and `submitted_at`<= CURDATE() then `user_id` else '0' end), however this is still returning the same values on a day by day basis as a simple count(distinct) command.

 

Can anyone offer any insight as to where I'm falling down in my command?

 

Thanks!

Best Answer

  • Valiant
    Valiant Coach
    Answer ✓

    So since you're wanting to show 'by day', a beastmode won't be the way to go on this one. A beastmode will only look at the rows falling into whatever grouping you define, in this case daily.

     

    What you'll need is a new data transform that will return these users for you. Based on some of the fields you're using, a MySQL transform for this would look something like this:

    SELECT DISTINCT `userid`, MIN(`submitted_at`) as 'First_Order_Date'
    FROM datasetname
    GROUP BY `userid` ,CONCAT(MONTH(`submitted_at`),YEAR(`submitted_at`))

    The above transform will return the first order date of the month for each user and the userid.

     

    Let me know if you need any more detail or any additional help.

     

    Sincerely,

    ValiantSpur

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

Answers

  • Valiant
    Valiant Coach
    Answer ✓

    So since you're wanting to show 'by day', a beastmode won't be the way to go on this one. A beastmode will only look at the rows falling into whatever grouping you define, in this case daily.

     

    What you'll need is a new data transform that will return these users for you. Based on some of the fields you're using, a MySQL transform for this would look something like this:

    SELECT DISTINCT `userid`, MIN(`submitted_at`) as 'First_Order_Date'
    FROM datasetname
    GROUP BY `userid` ,CONCAT(MONTH(`submitted_at`),YEAR(`submitted_at`))

    The above transform will return the first order date of the month for each user and the userid.

     

    Let me know if you need any more detail or any additional help.

     

    Sincerely,

    ValiantSpur

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

Categories