0

I'm trying to phase out a monthly KPI target into the days of the month so I can accurately track how I'm pacing to target.

I want to use my historical data from past months as my benchmark. Ideally, I'd like to have the average percentage of revenue per day a month to then multiple by the monthly KPI and see what my target should be per day.

The problem I'm facing is that my business only collects payments on business days (Monday - Friday, not bank holidays). This means that when trying to compare month on month, the 4th e.g. could be on a weekday and have a value or be on a weekend and not. This means that when aggregating all days between 1 - 31, the sum of the average values is greater than 100%.

What I think might solve the problem is if I could index the business days of the month to run sequentially and miss out weekends, this way the 4th day of the month will always be the 4th business day and comparing like for like will be made much easier.

select
    DATE_FORMAT(charged_date,'%Y-%m') month,
    DATE_FORMAT(charged_date,'%d') day,
    round(sum(total_charged),0) Week_Charged,
    round(m.month_spend,0) month_spend,
    round(sum(total_charged) / m.month_spend,2) as of_Month
from
    payment_data
left join
    (select
        DATE_FORMAT(charged_date,'%Y-%m') months,
        sum(total_charged) month_spend
    from
        payment_data
    where
        DATE_FORMAT(charged_date,'%Y-%m') BETWEEN  DATE_FORMAT(date_add(now(), INTERVAL - 9 MONTH),'%Y-%m') and DATE_FORMAT(date_add(now(), INTERVAL - 1 MONTH),'%Y-%m')
    and
        user_type = 'business'
    group by 1) as m on m.months = DATE_FORMAT(charged_date,'%Y-%m')
where
     DATE_FORMAT(charged_date,'%Y-%m') BETWEEN  DATE_FORMAT(date_add(now(), INTERVAL - 9 MONTH),'%Y-%m') and DATE_FORMAT(date_add(now(), INTERVAL - 1 MONTH),'%Y-%m')
and
    user_type = 'business'
group by 1,2

This doesn't necessarily solve the >100% problem as there are still different number of business days each month but I think by removing the weekend variability, this should make it much better.

That said, I'm open to feedback/suggestions on better ways of doing this as well.

Liam Cox
  • 27
  • 5
  • Here's an interesting suggestion: [MySQL function to find the number of working days between two dates](https://stackoverflow.com/questions/1828948/): `5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)` – paulsm4 Nov 04 '19 at 06:18
  • This seems like it would only work on a specific timeframe and not solve the revolving month-on-month or weekend issue I'm trying to fix. Super interesting solution though. – Liam Cox Nov 04 '19 at 07:09

0 Answers0