Databases
StackOverflow2013
Postsdbo
POAttempting to calculate customer churn w/ PHP and MySQL db
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAttempting to calculate customer churn w/ PHP and MySQL db
    primarykey
    data
    text
    <p>So I have a db with a transactions table which stores each transaction with unique id#, customer id#, date and a couple other transaction specific variables. I'm attempting to calculate customer churn thus:</p> <ul> <li>On given day x there are # of active customers.</li> <li>30 days from given day x the # of active customers (from original day x) we have lost. (so I am trying to track exact customers who have no new transactions)</li> <li>I want to be able to run this query for every day in the year.</li> </ul> <p>I don't know if that's clear enough. If not, let me try to rephrase: on any given day in the past I want a list of customer ID's that have an active subscription (our subscriptions are 30 days long) I then want to count how many of those customer ID's renews their subscription within the next 30 days.</p> <p>I've run a few queries, but everything I am doing seems really inefficient. for example:</p> <pre><code> //begin pseudo code SELECT CustomerID FROM Transaction WHERE TransactionDate &lt; @SomeDate - INTERVAL 1 DAY AND TransactionDate &gt; @SomeDate - INTERVAL 30 DAY </code></pre> <p>So now I have the customer ID's of those w/ active subscriptions on SomeDate (because any transaction w/in 30 days of SomeDate will have an active subscription) I can then join a new subselect with SomeDate + 30 days and join on CustomerID to get a count of renewed accounts.</p> <p>The issue(s) I'm concerned with are this: 1. efficiency; I'll have to run this query for each day I want to assess, and I'll only return (if I join) the renewed accounts #--so I'd have to run the query above, then a query w/ join, for each day I want to get active#-renewed#.</p> <p>What I'd prefer to do (but am apparently incapable of figuring out how) is to run a single query that output a table like this:</p> <pre><code>|SomeDate | Active Cust# | Renewed w/in 30 # | ---------------------------------------------- | 10/1 | 123 | 115 | ---------------------------------------------- | 10/2 | 127 | 112 | ---------------------------------------------- | 10/3 | 115 | 108 | ---------------------------------------------- </code></pre> <p>The key is that the number of renewed accounts is based on comparing to the customer ID's of the Active customer # unique ids. I.e. If Bill (ID#2) and Jim (ID# 4) are the two active customers I want the second "renewed w/in 30 #" list to reflect whether Jim or Bill renews, not whether Bobby Joe bought a subscription during the second period. Thoughts?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USSuperNoob
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. CO. . I don't usually do this, but if you want to calculate customer churn using SQL, then I highly recommend my book "Data Analysis Using SQL and Excel". Two chapters devoted to survival analysis explain exactly what you want to do and how to do it.
      singulars
      1. POAttempting to calculate customer churn w/ PHP and MySQL db
      1. USGordon Linoff
    2. CO@GordonLinoff Hey, anything helps! I can get the query to work by hacking together a series of queries and lengthy php logic to make this work. My goal is to find a streamlined way that isn't expensive on resources.
      singulars
      1. POAttempting to calculate customer churn w/ PHP and MySQL db
      1. USSuperNoob
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload