Forum Discussion

JackEnviro's avatar
JackEnviro
Icon for Helper I rankHelper I
8 years ago
Solved

New Customers with order over a certain value - shopify data

  Hello   I am using Shopify data and I would like to perform some analysis to identify new customer sales to understand the ROI on our keyword marketing spend.   The Shopify data shows repeated...
Need Help
Show and Tell
Tutorial Requests
  • danextian's avatar
    danextian
    8 years ago

    Hi JackEnviro,

     

    The formula below assumes that Created Date is column is when an order was created so any row that matches the earliest created date by customer and where Full Order  = Full is the New Order.

     

    New Order? = 
VAR FirstOrderDate =
    CALCULATE (
        MIN ( ShopifyValues[Created Date ] ),
        ALLEXCEPT ( ShopifyValues, ShopifyValues[Customer ID] ),
        ShopifyValues[Customer ID] = EARLIER ( ShopifyValues[Customer ID] )
    )
RETURN
    IF (
        ShopifyValues[Full Order] = "Part",
        "N/A",
        IF (
            FirstOrderDate = ShopifyValues[Created Date ]
                && ShopifyValues[Full Order] = "Full",
            "New",
            "Return"
        )
    )
JackEnviro's avatar
JackEnviro
Icon for Helper I rankHelper I
8 years ago

Then to exclude that - most customers are ordering a sample pack before making a full order so the first purchase transaction is not really a full order. The income from that can be analysed seperately.

 

I have already created a custom column to assign "Full" and "Part" orders which classifies the order as over £250.

 

What I am looking to see is the cost to aquire a customer vs the return from a new customer in the period or relative periods.

 

One of the bits I am finding difficult being new to Power Bi and DAX is that an order in the Shopify data is made up of multiple lines.

 

 

 

danextian's avatar
danextian
Icon for Super User rankSuper User
8 years ago

Hi JackEnviro,

 

The formula below assumes that Created Date is column is when an order was created so any row that matches the earliest created date by customer and where Full Order  = Full is the New Order.

 

New Order? = 
VAR FirstOrderDate =
    CALCULATE (
        MIN ( ShopifyValues[Created Date ] ),
        ALLEXCEPT ( ShopifyValues, ShopifyValues[Customer ID] ),
        ShopifyValues[Customer ID] = EARLIER ( ShopifyValues[Customer ID] )
    )
RETURN
    IF (
        ShopifyValues[Full Order] = "Part",
        "N/A",
        IF (
            FirstOrderDate = ShopifyValues[Created Date ]
                && ShopifyValues[Full Order] = "Full",
            "New",
            "Return"
        )
    )
  • JackEnviro's avatar
    JackEnviro
    Icon for Helper I rankHelper I
    8 years ago

    Thats great - thanks very much for your help!