matthewdacruz
asked on
How do you get an Average time in months and days in mysql
Hi
I am trying to figure out how long a person is subscribed to our different services by product name.
Could someone please tell me if I have written this in the correct way.
Is this the best way to work out the average time a person is subscribed by start date and end date?
Thanks
Matt
========================
The Query
++++++++++++++++++++++++
SELECT
dap_products.`name`,
ROUND(AVG((DATEDIFF(dap_us ers_produc ts_jn.acce ss_end_dat e, dap_users_products_jn.acce ss_start_d ate)/365.2 42199)),2) AS Average_Time_Subscribed
FROM
dap_users_products_jn
LEFT JOIN dap_products ON dap_products.id = dap_users_products_jn.prod uct_id
where
dap_products.is_recurring = 'Y'
Group By
dap_products.`name`
Order By
Average_Time_Subscribed DESC
I am trying to figure out how long a person is subscribed to our different services by product name.
Could someone please tell me if I have written this in the correct way.
Is this the best way to work out the average time a person is subscribed by start date and end date?
Thanks
Matt
========================
The Query
++++++++++++++++++++++++
SELECT
dap_products.`name`,
ROUND(AVG((DATEDIFF(dap_us
FROM
dap_users_products_jn
LEFT JOIN dap_products ON dap_products.id = dap_users_products_jn.prod
where
dap_products.is_recurring = 'Y'
Group By
dap_products.`name`
Order By
Average_Time_Subscribed DESC
Yes, this seems to be good way for subscriptions longer than let say 6 months. Shorter subscriptions is better to calculate in days because fractions do not look so perfect.
You should also think how to count running subscriptions where the end date is not defined yet.
You should also think how to count running subscriptions where the end date is not defined yet.
Average number of months and days can be calculated from average number of days in month:
SELECT
dap_products.`name`,
FLOOR(AVG(DATEDIFF(dap_users_products_jn.access_end_date, dap_users_products_jn.access_start_date)/30.4375)) AS Average_Months_Subscribed,
FLOOR((AVG(DATEDIFF(dap_users_products_jn.access_end_date, dap_users_products_jn.access_start_date)/30.4375) - FLOOR(AVG(DATEDIFF(dap_users_products_jn.access_end_date, dap_users_products_jn.access_start_date)/30.4375)) ) * 30.4375) AS Plus_Average_Days_Subscribed
FROM
dap_users_products_jn
LEFT JOIN dap_products ON dap_products.id = dap_users_products_jn.product_id
where
dap_products.is_recurring = 'Y'
Group By
dap_products.`name`
Order By
Average_Time_Subscribed DESC
If precision is needed (usually not the case in "average" computations) you might want to delve into the idea that the term "month" is ambiguous. Some guidance on date-related computations is available in this article.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
ASKER
Thanks Guys for the feedback
@ pcelba, You Said "You should also think how to count running subscriptions where the end date is not defined yet. "
The challenge we have is the subscriptions are only always set 1 month in advance. If the sub is not paid then the system locks account.
With this said would it make sense to only look at the dates that are one month in advance?
Would love to hear your thoughts
Regards
Matt
@ pcelba, You Said "You should also think how to count running subscriptions where the end date is not defined yet. "
The challenge we have is the subscriptions are only always set 1 month in advance. If the sub is not paid then the system locks account.
With this said would it make sense to only look at the dates that are one month in advance?
Would love to hear your thoughts
Regards
Matt
ASKER
@ pcelba,
Why do you minus and then multiply in the line 4:
FLOOR((AVG(DATEDIFF(dap_us ers_produc ts_jn.acce ss_end_dat e, dap_users_products_jn.acce ss_start_d ate)/30.43 75) - FLOOR(AVG(DATEDIFF(dap_use rs_product s_jn.acces s_end_date , dap_users_products_jn.acce ss_start_d ate)/30.43 75)) )
Why do you minus and then multiply in the line 4:
FLOOR((AVG(DATEDIFF(dap_us
The 3rd line calculates months without fractions
The 4th line calculates the number of days from the fractional part of the month, so it calculates average months incl. decimal places, subtracts the whole number of months and the result (fraction of the months) multiplies by average number of days in the month. And finally decimal places are stripped from the result.
E.g. 547 days means 547/30.4375 = 17.9713 average months
0.9713 means 0.9713*30.4375 = 29.56 days
So the result is 17 months + 29 days.
Above formulas can result in XX months + 30 days which does not look so good but it is acceptable here. You may also update the result if this happens.
My opinion to "one month in advance" is following:
If the database contains end dates (entered by users) which are far in the future but not guaranteed then you should not count them to the average. OTOH, you may calculate some plans from these values if you know their relevancy.
The 4th line calculates the number of days from the fractional part of the month, so it calculates average months incl. decimal places, subtracts the whole number of months and the result (fraction of the months) multiplies by average number of days in the month. And finally decimal places are stripped from the result.
E.g. 547 days means 547/30.4375 = 17.9713 average months
0.9713 means 0.9713*30.4375 = 29.56 days
So the result is 17 months + 29 days.
Above formulas can result in XX months + 30 days which does not look so good but it is acceptable here. You may also update the result if this happens.
My opinion to "one month in advance" is following:
If the database contains end dates (entered by users) which are far in the future but not guaranteed then you should not count them to the average. OTOH, you may calculate some plans from these values if you know their relevancy.
subscriptions are only always set 1 month in advancePlease clarify what that means in the context of this question? Here's my point of confusion.
Are you looking to find the clients who signed up a year ago (for example) and are still renewing? They will have an average "system life" of one year. If someone signed up last year, hung on for three months and then stopped paying the subscription, how would you want to think about that circumstance?
I understand it now... Each subscription end is set to the "paid period + one moth" and updated after each payment.
The calculation is OK if you need the average from all (past and current) subscriptions.
Other values of your interest could be: Subscriptions which were never paid/renewed, their ratio to all subscriptions etc.
The calculation is OK if you need the average from all (past and current) subscriptions.
Other values of your interest could be: Subscriptions which were never paid/renewed, their ratio to all subscriptions etc.
ASKER
@ Ray Paseur, pcelba
Hi Ray, pcelba
The way the membership software works is when a user pays and the transaction is successful, the transaction then updates the db and sets the [dap_users_products_jn.acc ess_end_da te] column + 1 month access. If the transaction fails or payment is not made the [dap_users_products_jn.acc ess_end_da te] is not updated and access denied if user tries to log in.
So ideally what I am looking for is
what is the average subscription time per product in months.
From this I can work out the LTV (life time value of the average client). These figures will also allow us as a company to improve on service delivery to increase this LTV number by product.
So from what I have read above essentially we need to split the query into two.
1) results from all that started and ended
2) results from all that are active
In your opinions what would you consider the best path to follow to achieve these results?
Hi Ray, pcelba
The way the membership software works is when a user pays and the transaction is successful, the transaction then updates the db and sets the [dap_users_products_jn.acc
So ideally what I am looking for is
what is the average subscription time per product in months.
From this I can work out the LTV (life time value of the average client). These figures will also allow us as a company to improve on service delivery to increase this LTV number by product.
So from what I have read above essentially we need to split the query into two.
1) results from all that started and ended
2) results from all that are active
In your opinions what would you consider the best path to follow to achieve these results?
ASKER
Another though on this is how does the actual users come into play when looking at LTV
example we can have
one user that sticks for 10 months
two users who stick for 3 months
two users who stick for 1 month
each user pay $100 pm.
what will the average LTV be based on the 5 users for month and what is the LTV $ value average?
This is ideally what I am try to assess and was hoping the query I wrote was in the right direction
example we can have
one user that sticks for 10 months
two users who stick for 3 months
two users who stick for 1 month
each user pay $100 pm.
what will the average LTV be based on the 5 users for month and what is the LTV $ value average?
This is ideally what I am try to assess and was hoping the query I wrote was in the right direction
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
YE it does massively.
Would it be a good idea calculate the $ value the same as the 1 x 10 + 2 x 3 + 2 x 1 = 18?
Would it be a good idea calculate the $ value the same as the 1 x 10 + 2 x 3 + 2 x 1 = 18?
ASKER
Great help, Thanks
I don't know why you used 365.242199 instead of 365.25, but I guess it's a choice :)
HTH,
Dan