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"
        )
    )
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!