title: Calculating LTV Accurately
body: <p>I am trying to calculate the "Average Lifetime Value" of customers. Meaning what is the average total spend of all customers during their time with us &amp; also for a previous 30 day span.</p>
  
  <p>Data in table (customer):</p>
  
  <pre><code>|-----------------------|
  |   ID   |   FirstName  |
  |-----------------------|
      35       John
      84       Mike
      90       Cassie
  </code></pre>
  
  <p>Data in table (tblservice):</p>
  
  <pre><code>|---------------------------------------------------|
  |   serviceID   |   Timing  |  Total  |  customerID |
  |---------------------------------------------------|
          1         3/10/2012   5.00          35
          2         3/19/2012   5.00          35
          3         3/20/2012   10.00         35
          4         3/25/2012   10.00         84
          5         4/15/2012   10.00         84
  </code></pre>
  
  <p>note: Tables "customer" &amp; "tblservice" are in a one to many relationship.</p>
  
  <p>This is what I got so far which calculates the average spend each time a customer purchases. BUT I DO NOT need it to calculate the average purchase cost which it does now </p>
  
  <p>I'm trying to calculate the average of <strong>total</strong> spend per customer. So it must total all the purchases made by each customer first, then divide by the total number of customers.</p>
  
  <p><strong>With the table layout above it would calculate average lifetime value for customer 35 as ((5+5+10)/3) = 6.66 &amp; customer 84 as ((10+10)/2) = 10.00.</strong></p>
  
  <p><strong>THEN in order to find average lifetime value for the customer base as a whole, it should average out the results from each customer as: ((6.66+10)/2) = 8.33</strong></p>
  
  <p>Please help, here are the two queries so far (one for all time and another for the past 30days):</p>
  
  <pre><code>SELECT AVG(tblservice.Total) AS LifetimeValue FROM tblservice;
  
  SELECT AVG(tblservice.Total) AS LifetimeValue FROM tblservice
  WHERE ((tblservice.Timing)&gt;=DateAdd("d",-30,Date()));
  </code></pre>