Reply
Highlighted
Frequent Visitor
Posts: 9
Registered: ‎04-18-2018
Accepted Solution

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 rows for each line item of an order.

 

With the help from people here I have categorised each order based on its composition (i.e. predominant product category) and added this as a caclulated column "CPA Order". I have also created a calculated column to identify low value sample orders which customer may order and smaller value top-up items "Full Order".

 

I would now like to identify a new/first "Full Order" a customer makes so I can attribute that to the marketing spend and look at the ROI per customer vs cost to aquire.

 

I would ideally like to see per month per product category:

 

- total margin from New customers (with "Full"orders)

- no. of order from those customers

- average order value

 

I have been looking a a custom column but struggling with the right formula. However could see this could also possibly be a measure? I have also wondered weather it would be worth creating a Customer table from the Shopify Transaction table somehow?

 

Any advice or suggestions much appreciated!

 

Thanks

Jack

 

 

 

Schema.JPGSchemaAnon Shopify.JPGShopify anon

 

 

 


Accepted Solutions
New Contributor
Posts: 450
Registered: ‎10-18-2016

Re: New Customers with order over a certain value - shopify data

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"
        )
    )
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post


All Replies
Moderator
Posts: 7,014
Registered: ‎03-10-2016

Re: New Customers with order over a certain value - shopify data

@JackEnviro,

Please share sample data of your tables and post expected result based on the sample data here.

Regards,
Lydia

Frequent Visitor
Posts: 9
Registered: ‎04-18-2018

Re: New Customers with order over a certain value - shopify data

Hi Lydia

 

Thanks for responsing.

 

Hopefully this file with some anonymised sample data. I've incuded what I hope the output to look like and some of the current measures and calculated columns. I am looking for a way to categorise a customer as "New" or "Repeat" for an order over £250 so can look at the net margin on that basis, as many customers order samaple packs or small quantities I've looked to exclude small or low value orders.

 

https://www.dropbox.com/s/12015rte5offmx0/New%20vs%20Repeat%20margin%20by%20product.xlsx?dl=0

 

Thanks

Jack

New Contributor
Posts: 450
Registered: ‎10-18-2016

Re: New Customers with order over a certain value - shopify data

[ Edited ]

Hi @JackEnviro

 

A question: for the first order, a customer is considered new and in the succedding orders a repeat?

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
Frequent Visitor
Posts: 9
Registered: ‎04-18-2018

Re: New Customers with order over a certain value - shopify data

Hi Danextian

 

Yes for the first Order over £250 value they are considered new and for all successive order repeat.

 

Thanks

Jack

New Contributor
Posts: 450
Registered: ‎10-18-2016

Re: New Customers with order over a certain value - shopify data

What if the first order doesn't go over £250?

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
Frequent Visitor
Posts: 9
Registered: ‎04-18-2018

Re: New Customers with order over a certain value - shopify data

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.

 

 

 

New Contributor
Posts: 450
Registered: ‎10-18-2016

Re: New Customers with order over a certain value - shopify data

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"
        )
    )
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
Frequent Visitor
Posts: 9
Registered: ‎04-18-2018

Re: New Customers with order over a certain value - shopify data

Thats great - thanks very much for your help!