Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JackEnviro
Helper I
Helper I

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

 

 

 

SchemaSchemaShopify anonShopify anon

 

 

 

1 ACCEPTED SOLUTION

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

View solution in original post

8 REPLIES 8
v-yuezhe-msft
Employee
Employee

@JackEnviro,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Hi @JackEnviro

 

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






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

Hi Danextian

 

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

 

Thanks

Jack

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






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

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.

 

 

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

Thats great - thanks very much for your help!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.