Day on Day Change Issue

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 Answer

  • dthierjung
    dthierjung 🟠
    Accepted 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

  • 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.

  • Hey, sorry if not made clear.

     

    At the moment, depending on when the user views the cards, some of the records that make up the overall DoD value are from the previous day and some from the current day. So for DoD, I want to take the records up to '23:59:59' for each day, creating an internal day view. 

  • Thanks dthierjung for the write-up. I'll have a look tomorrow to see if using the previous day calculation to get the internal day value works for me, and report any futher updates that I may notice. The dataset 'campaign_schedule_date_local' just has date.