Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 501
  • Last Modified:

Calculating Average In MySql

Hi Experts
I have a query written in MySQL. Not sure if I am doing this correctly.
I want to achieve the following:

I need to find out the the average amount of months that a group of users use a product for.
See 'AS MEMBERSHIP_LTV' in query.

I have attempted to use datediff for this but am not sure if this is correct. Im not sure if I am going at this in the totally incorrect way and need some help please.

How does one calculate what the average subscription time in months per product across multiple users?
How long does the product user group stay subscribed for on average?

The columns that manage the state date and end date are:

 - dap_users_products_jn.access_start_date
 - dap_users_products_jn.access_end_date

How doe we use these multiple dates to work out a group average because the indiviuals subscribe at different dates

I am wanting to know grouped by
dap_products.id,

I want to look at these numbers  by year to establish if the changes we make to our products we offer are improving the time the customers stay subscribed for looking back over the years.

Any suggestions or advice will be appreciated

Thanks

Matt
Member-LTV-by-product.sql
0
matthewdacruz
Asked:
matthewdacruz
  • 4
  • 3
1 Solution
 
Ray PaseurCommented:
While you can probably do this in a MySQL query, if you're using PHP it might be easier to collect the data into arrays of objects and run the analysis over the arrays.  Each object would have the data elements you want to aggregate and analyze.  You can use PHP usort() to order and reorder the arrays.

If you want to post the CREATE TABLE statement for dap_products and dap_users_products_jn we might be able to help with some ideas.
0
 
Julian HansenCommented:
So you want to know for a given group of users U what the average time a user uses your product where the time used is the difference between

dap_users_products_jn.access_start_date
AND
dap_users_products_jn.access_end_date

How do you want partial months to be treated - round up / down or sum as is?

something probably like this
SELECT 
  AVG(DATEDIFF(dap_users_products_jn.access_end_date, dap_users_products_jn.access_start_date))/29.5, 
  dap_products.id 
FROM dap_products
WHERE
  dap_products.is_recurring ='y' AND 
  dap_users_products_jn.access_end_date < '2020-01-01'
GROUP BY 
  YEAR(dap_users_products_jn.access_end_date), 
  dap_products.id

Open in new window

Without seeing your table and data difficult to test but it should be something like that
0
 
matthewdacruzAuthor Commented:
Hi Ray
I would love to see what you do with Php.
I have attached the tables as requested.
d-tables.sql
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Julian HansenCommented:
This should be solvable in MySQL without having to resort to PHP code - did you try the query I posted

Here is an updated version tested against your tables

SELECT 
  AVG(DATEDIFF(dup.access_end_date, dup.access_start_date))/29.5, 
  dp.id 
FROM dap_users_products_jn dup LEFT JOIN dap_products dp ON dup.product_id = dp.id
WHERE
  dp.is_recurring ='y' AND 
  dup.access_end_date < '2020-01-01'
GROUP BY 
  YEAR(dup.access_end_date), 
  dp.id;

Open in new window

0
 
matthewdacruzAuthor Commented:
Hi Julian

Thanks for the response, I have run the query.
i have a question. Does your query give the average between all the dates per product?
Does it look through all the dates associated with one product and then average it out?

This information is crucial so I don't want to get it wrong.
I attached the db tables in the previous post for the PHP request.

Thanks

Matt
0
 
Julian HansenCommented:
Yes the query is producing an average of days groupd by product ID. The average is divided by 29.5 to give months.

So the query will group all users with like product id's together.

Subtract the start date from the end date for all users

Average these by product ID

If I read your question correctly - this is what you are looking for?
0
 
matthewdacruzAuthor Commented:
Thanks, That is what I was looking for.
0
 
Julian HansenCommented:
You are welcome - thanks for the points
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now