Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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" ) )
Proud to be a Super User!
@JackEnviro,
Please share sample data of your tables and post expected result based on the sample data here.
Regards,
Lydia
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?
Proud to be a Super User!
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?
Proud to be a Super User!
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" ) )
Proud to be a Super User!
Thats great - thanks very much for your help!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |