Day on Day Change Issue

amehdad
amehdad Coach
in Magic ETL

Hi,

I am trying to calculate the day on day changes of campaign activities (i.e. Sends, Receipts). The problem experienced is restricting the unique counts to one day because the code I've been using includes some records of the previous day. Depending on the time of viewing the domo cards, it takes the record of 24hrs from the time of viewing (which is not necessarily 24 hrs of the entire day). I've been using date_format, specifically:

 

(COUNT(DISTINCT
CASE
WHEN DATE_FORMAT(`campaign_schedule_date_local`, '%Y%m%d') = DATE_FORMAT(DATE_SUB(CURDATE('%Y%m%d'), INTERVAL 0 Day),'%y%m%d') AND `media_booking_type` <> 'Sample' AND (`campaign_status` = 'Scheduled' OR `campaign_status` = 'Sent')
THEN `campaign_id`
END))

 

I want to see if using time_format is able to restrict the timeframe for representing daily changes to the exact day (i.e. Up until 23:59:59) but have not been able to see any values return from the code when changing from date_format to time_format. Could you please advise?

 

Any help is appreciated. Cheers

Best Answers

  • dthierjung
    dthierjung Contributor
    Answer ✓

    Okay that helps clear it up. What datatype is your column "campaign_schedule_date_local" stored as? DateTime?

     

    The way we tackle Period Over Period calculations is like this:

    • Set the date range to the previous two periods (days in our case).
    • You'll need 2 calculations: 1 for the current period (day), and one for the previous period (day).
    • Depending on if it's year, month, or day, you'll use the appropriate function to test the day values. I.e. YEAR(), MONTH(), or DAY(). These return numerical values, so 8 for August as an example.
    • Then you simply subtract the previous period's values from the current period's values.

    As an simplified example, the current period's calculation could look like this:

     

    CASE
    WHEN 
              DAY(campaign_schedule_date_local) = DAY(CURDATE())
    THEN 
              `campaign_id`
    END

    Which will return all `campaign_id`s that have a `campaign_schedule_date_local` equal to the current date's value.

     

    The previous day's value could then be:

    CASE
    WHEN 
              DAY(campaign_schedule_date_local) = DAY(CURDATE()) - 1
    THEN 
              `campaign_id`
    END

    As a huge caveat though, you'll have to consider UTC timezone offsets which can skew your results. This link fully details it and proposes some solutions: https://knowledge.domo.com/?cid=timezoneissues

     

    The above might not answer your question exactly, but it should be a good start.

Answers

  • dthierjung
    dthierjung Contributor

    So I'm a little confused after reading your question.

     

    You want to calculate day over day changes for campaign activities.

     

    You have records from previous days but these are skewing your numbers?

     

    When a user views the card, you want them to see the current day's (up until 23:59:59) campaign numbers compared to the entire previous day's numbers? Or do you want them to see the current day's numbers compared to the previous day at the same time of viewing?

     

    Let me know if I misread that.

  • dthierjung
    dthierjung Contributor
    Answer ✓

    Okay that helps clear it up. What datatype is your column "campaign_schedule_date_local" stored as? DateTime?

     

    The way we tackle Period Over Period calculations is like this:

    • Set the date range to the previous two periods (days in our case).
    • You'll need 2 calculations: 1 for the current period (day), and one for the previous period (day).
    • Depending on if it's year, month, or day, you'll use the appropriate function to test the day values. I.e. YEAR(), MONTH(), or DAY(). These return numerical values, so 8 for August as an example.
    • Then you simply subtract the previous period's values from the current period's values.

    As an simplified example, the current period's calculation could look like this:

     

    CASE
    WHEN 
              DAY(campaign_schedule_date_local) = DAY(CURDATE())
    THEN 
              `campaign_id`
    END

    Which will return all `campaign_id`s that have a `campaign_schedule_date_local` equal to the current date's value.

     

    The previous day's value could then be:

    CASE
    WHEN 
              DAY(campaign_schedule_date_local) = DAY(CURDATE()) - 1
    THEN 
              `campaign_id`
    END

    As a huge caveat though, you'll have to consider UTC timezone offsets which can skew your results. This link fully details it and proposes some solutions: https://knowledge.domo.com/?cid=timezoneissues

     

    The above might not answer your question exactly, but it should be a good start.

Categories