2 Replies Latest reply on Feb 28, 2016 12:25 PM by Nick Foley

    Padding my Data

    Nick Foley

      I have transaction data for 20,000 customers by month and product line.

       

      If a customer didnt order a product that month, there is no row of data.  If they have never ordered that product line, there is no row of data corresponding to that customer and product line in the entire data set.

       

      For example, the data is structured

       

      Date, Customer, Product Line, Product, Amount

       

      This is causing a few issues:

      1   This is throwing off my averages for that customer.    A 0 would seriosuly drag it down which is what i need.

       

      2.  im trying to rank my customers by recency of there order using the following:

      RANK_PERCENTILE(MIN(DATEDIFF('month',[Accounting Month],TODAY())), 'asc')

       

      Everyone is in the top percentile due to the lack of data rows.

       

      3.  I want to segment my customers by region and get penetration rates.  Its throwing off my penetration rates as the customers who havent purchased that product EVER, arent being included.

       

      any help?