title: How to determine trial booking conversion - google sheets
body: <p><strong>Introduction</strong></p>
  
  <p>Many sites use WooCommerce as a plugin for their WordPress site and so do we :). We've linked all purchases to google sheets, so I can do some analyses.</p>
  
  <p>Our goal is get a many children physically active as we can and we have gym classes for very young children with they parents. To teach them the basics of the fun of physical activity</p>
  
  <p><strong>What I would like to do</strong></p>
  
  <p>I would like to know, how many free trial classes actually convert to paying customers and what the average timespan is between booking a trial class and becoming a member</p>
  
  <p><strong>The data that I have</strong></p>
  
  <p>I have the following columns which are necessary for this, I believe:</p>
  
  <ul>
  <li>Datestamp</li>
  <li>paymentID (is empty when booking a free trial class)</li>
  <li>Price (is 0,00 whem booking a free trial class)</li>
  <li>childsName (Unique in combination with parentsEmailadress, but recurs every month in the list once a membership is active)</li>
  <li>parentsEmailadress (may have several children)</li>
  <li>OrderName (has the string "trial" or "Membership")</li>
  </ul>
  
  <p>I've made some dummy data in the following sheet: 
  <a href="https://docs.google.com/spreadsheets/d/1lWzQbXMU4qRLp_2qiQ_qsq57nPMy2RG8AHDMGKW626E/edit?usp=sharing" rel="nofollow noreferrer">https://docs.google.com/spreadsheets/d/1lWzQbXMU4qRLp_2qiQ_qsq57nPMy2RG8AHDMGKW626E/edit?usp=sharing</a></p>
  
  <p><strong>Possible solution</strong></p>
  
  <p>My guess is that I should:</p>
  
  <ol>
  <li>make a column in which I combine the childs name and the emailadress</li>
  <li>Make a TRUE of FALSE column to check if order is trial class or not</li>
  <li>Make a column to find the first Unique child-emailadres combination in previous orders (<em>How do I do that?!</em> - <em>Vlookup?</em>) </li>
  </ol>
  
  <p>and than</p>
  
  <ul>
  <li>if this is found than check again if this is a trial class order.</li>
  <li>If it is a trial class order than it should determine the amount of days between the trial class order and the non-trial class order and display the amount of days</li>
  <li>if this is another normal order than leave empty(it's just a membership order)</li>
  <li>if the emailadres is not found than display "direct" (it's a directly bought membership)</li>
  </ul>
  
  <p>I did 1 and 2 and tried 3 with: 
  =ALS(H2=0;VERT.ZOEKEN(G2;A:G;1;ONWAAR);) (in Dutch)
  =IF(H2=0;V.LOOKUP(G2;A:G;1;FALSE);) (possible translation)</p>
  
  <p>But this doesn't work.</p>
  
  <p>Really hope some can point me in the right direction!</p>
  
  <p>Thank you very much in advance!</p>