Cacher is the code snippet organizer for pro developers

Customers who have bought more than once
224 out of 1214
18%

Customers who have bought more than twice
82 out of 1214
6%

Unique Customers 6 Months ago (excluding last month):
938 

Unique Customers 1 Month ago:
264 

Total Unique Customers from last month, that shopped at BB 6 Months ago as well:
61

<mvt:comment> == Total Unique Customers, using bill_email == </mvt:comment>
<mvt:assign name="l.settings:totalUniqueCustomers:query" value="'SELECT COUNT(DISTINCT bill_email) FROM s01_Orders WHERE bill_email<>\'\''" />
<mvt:item name="sql" param="l.settings:totalUniqueCustomers" />
<mvt:assign name="uniqueCustomers" value="l.settings:totalUniqueCustomers:results[1]" />

<mvt:comment> == Bought Two + == </mvt:comment>
<mvt:assign name="l.settings:boughttwice:query" value="'SELECT COUNT(*) FROM s01_Orders GROUP BY bill_email HAVING COUNT(*) > 1 AND bill_email<>\'\''" />
<mvt:item name="sql" param="l.settings:boughttwice" />
<mvt:assign name="boughtTwiceCustomers" value="0" />
<mvt:foreach iterator="row" array="boughttwice:results">
	<mvt:assign name="boughtTwiceCustomers" value="g.boughtTwiceCustomers + 1" />
</mvt:foreach>

<mvt:comment> == Bought Three + == </mvt:comment>
<mvt:assign name="l.settings:boughtthrice:query" value="'SELECT COUNT(*) FROM s01_Orders GROUP BY bill_email HAVING COUNT(*) > 2 AND bill_email<>\'\''" />
<mvt:item name="sql" param="l.settings:boughtthrice" />
<mvt:assign name="boughtThriceCustomers" value="0" />
<mvt:foreach iterator="row" array="boughtthrice:results">
	<mvt:assign name="boughtThriceCustomers" value="g.boughtThriceCustomers + 1" />
</mvt:foreach>

<strong>Customers who have bought more than once</strong><br />
&mvt:global:boughtTwiceCustomers; out of &mvt:global:uniqueCustomers;<br />
<span id="percentage1"></span><br /><br />

<strong>Customers who have bought more than twice</strong><br />
&mvt:global:boughtThriceCustomers; out of &mvt:global:uniqueCustomers;<br />
<span id="percentage2"></span><br /><br />

<script>
	document.getElementById('percentage1').innerText = Math.floor((&mvt:global:boughtTwiceCustomers; / &mvt:global:uniqueCustomers;) * 100) + '%';
	document.getElementById('percentage2').innerText = Math.floor((&mvt:global:boughtThriceCustomers; / &mvt:global:uniqueCustomers;) * 100) + '%';
</script>

<mvt:comment> == 6 Months Ago == </mvt:comment>
<mvt:assign name="l.settings:sixmonthsago:query" value="'SELECT DISTINCT bill_email FROM s01_Orders WHERE orderdate >= UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 6 MONTH)) AND bill_email<>\'\' AND orderdate <= UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 1 MONTH))'" />
<mvt:item name="sql" param="l.settings:sixmonthsago" />

<mvt:comment> == 1 Month Ago == </mvt:comment>
<mvt:assign name="l.settings:onemonthago:query" value="'SELECT DISTINCT bill_email FROM s01_Orders WHERE orderdate >= UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 1 MONTH)) AND bill_email<>\'\''" />
<mvt:item name="sql" param="l.settings:onemonthago" />
<mvt:assign name="retention" value="0" />
<mvt:assign name="totalSixMonths" value="0" />
<mvt:assign name="totalOneMonth" value="0" />
<mvt:foreach iterator="sixmonths" array="sixmonthsago:results">
	<mvt:assign name="totalSixMonths" value="g.totalSixMonths + 1" />
</mvt:foreach>
<mvt:foreach iterator="onemonth" array="onemonthago:results">
	<mvt:foreach iterator="sixmonths" array="sixmonthsago:results">
		<mvt:if expr="l.settings:onemonth EQ l.settings:sixmonths">
			<mvt:assign name="retention" value="g.retention + 1" />
		</mvt:if>
	</mvt:foreach>
	<mvt:assign name="totalOneMonth" value="g.totalOneMonth + 1" />
</mvt:foreach>
<strong>Unique Customers 6 Months ago (excluding last month):</strong><br />
&mvt:global:totalSixMonths; <br /><br />

<strong>Unique Customers 1 Month ago:</strong><br />
&mvt:global:totalOneMonth; <br /><br />

<strong>Total Unique Customers from last month, that shopped at BB 6 Months ago as well:</strong><br />
&mvt:global:retention;<br />