1 Reply Latest reply on Jul 10, 2018 11:30 AM by Patrick A Van Der Hyde

    Repeat customers Not sure about the figs


      Hi All,


      I am not sure if this is correct I am trying to count repeat customers, using this logic below which I has help (from ZZ, thanks man). The data starts in 20o5 and it shows 3556 customers in this year for which are repeat. I think its counting the first puchase as 1 and the repeat purchase as 1. I am sure there was no double puchases in 2005 so 3556 seems high.


      With this in mind, I am ultamatly trying to get these figures correct and then seperatly look at a rolling forward figure where if they brought in 2005 then in 13 years till 2018 what is the number of custmers who after buying in 2005 boright in 2006,2007,2008....2018. This is for each year respectively. The problem I am encountering is that as my numbers are not just based on  basecustomerid but on multiple conditions (which are below),  I am getting a bit lost on how to construct this.


      Any ideas guys


      I have data from 2004-2018, I would like to find repeat customers based on a few conditions


      The main one is base customer id,( but sometimes this will not be same so)




      Phone Number (Mobile OR Landline)




      Email  ignoring any email with the following words ( 'noemail%' , 'nomail%' )




      (First name + Last name + Postcode Match)


      How do I do a yearly repeat customer visual representation or table .

        • 1. Re: Repeat customers Not sure about the figs
          Patrick A Van Der Hyde

          Hello Junaid,


          I am not sure of your correct numbers but aren't you just missing the year value for the LOD calcs:


          { FIXED [BaseCustomerID],year([SaleDate]):SUM({ FIXED [BaseCustomerID],YEAR([InvoiceDate]):COUNTD([BaseCustomerID])})} >1


          { FIXED [Mobile Phone],year([SaleDate]):SUM({ FIXED [Mobile Phone],YEAR([InvoiceDate]):COUNTD([Mobile Phone])})} >1


          { FIXED [Landline Phone],year([SaleDate]):SUM({ FIXED [Landline Phone],YEAR([InvoiceDate]):COUNTD([Landline Phone])})} >1


          { FIXED ([FirstName]+[LastName]+[Postcode1]),year([SaleDate]):SUM({ FIXED ([FirstName]+[LastName]+[Postcode1]),YEAR([InvoiceDate]):COUNTD(([FirstName]+[LastName]+[Postcode1]))})} >1


          { FIXED [Email Condition],year([SaleDate]):SUM({ FIXED [Email Condition],YEAR([InvoiceDate]):COUNTD(IF [Email Condition] <> '' THEN [Email Condition] END)})} >1