If a device is purchased in the first order what is the average amount spent per order on the first,

[Anonymous]

Hi all,

I have been assigned a task to understand the purchasing behaviour of customers for products.

I need to calculate average amount spent by the customer in the first,second...10th transaction if the first transaction contained a device.

I have a column with a device flag (Which has values 0 and 1)

Data_Type	Order #	Transaction_no	Product ID	Name	Product Model	Bundles	a part of bundle	Single Order	VuseXU_Engraved Device	VuseXu_device_colour	All_Product_colour	Flavours bundle	Flavor	Bundle+Flavor	Flavor_SKU_Names	Single Pods	Double Pods	Total Pods	Device	Accessories	Product Price	QTY	Product Total Price	Product Tax	Product Total Price_wFB	Order_Value_per_SKU	Product Reward	Product Option	Order Vouchers	Total with Vat	Order	Total	Order Status	Customer ID	Customer_Age	Currency	Date	Date Modified	Date_REV	Time	Month	Year
FY21	12042	1	206	Crisp Mint - 6mg	Mint-6	0	1	0	0	0	0	0	1	Crisp Mint - 6mg	Vuse 2 Pod Pack Crisp Mint 6mg	0	1	2	0	0	AED 43.0	2	AED 86.0	AED 2.1	86	86	0			Sub-Total - AED 214.0; Coupon (Gena170d5) - AED -100.0; VAT - AED 3.2; Free Shipping - AED 0.0; Total - AED 118.0	AED	$117.50	Complete	1945782	36.0	AED	2021-12-30 14:41:06	2021-12-30 17:30:44	12/30/2021	5:30:44 PM	12	2021
FY21	12042	1	58	Forest Blueberry	Blueberry	0	1	0	0	0	0	0	1	Forest Blueberry	Vuse 2 Pod Pack Forest Blueberry 18mg	0	1	2	0	0	AED 43.0	1	AED 43.0	AED 2.1	43	43	0			Sub-Total - AED 214.0; Coupon (Gena170d5) - AED -100.0; VAT - AED 3.2; Free Shipping - AED 0.0; Total - AED 118.0	AED	$117.50	Complete	1945782	36.0	AED	2021-12-30 14:41:06	2021-12-30 17:30:44	12/30/2021	5:30:44 PM	12	2021
FY21	12042	1	207	Fresh Apple	Fresh Apple	0	1	0	0	0	0	0	1	Fresh Apple	Vuse 2 Pod Pack Fresh Apple 18mg	0	1	2	0	0	AED 43.0	1	AED 43.0	AED 2.1	43	43	0			Sub-Total - AED 214.0; Coupon (Gena170d5) - AED -100.0; VAT - AED 3.2; Free Shipping - AED 0.0; Total - AED 118.0	AED	$117.50	Complete	1945782	36.0	AED	2021-12-30 14:41:06	2021-12-30 17:30:44	12/30/2021	5:30:44 PM	12	2021
FY21	12042	1	205	Iced Mango	Iced Mango	0	1	0	0	0	0	0	1	Iced Mango	Vuse 2 Pod Pack Iced Mango 18mg	0	1	2	0	0	AED 43.0	1	AED 43.0	AED 2.1	43	43	0			Sub-Total - AED 214.0; Coupon (Gena170d5) - AED -100.0; VAT - AED 3.2; Free Shipping - AED 0.0; Total - AED 118.0	AED	$117.50	Complete	1945782	36.0	AED	2021-12-30 14:41:06	2021-12-30 17:30:44	12/30/2021	5:30:44 PM	12	2021
FY21	12041	6	65	Flavours bundle	Flavour Bundle	1	0	0	0	0	0	1	0	Flavours bundle	0	0	0	0	0	0	AED 171.4	2	AED 342.9	AED 8.6	342.9	0	0			Sub-Total - AED 343.0; VAT - AED 17.1; Free Shipping - AED 0.0; Total - AED 360.0	AED	$360.00	Complete	1945093	38.0	AED	2021-12-30 14:16:10	2021-12-30 16:15:44	12/30/2021	4:15:44 PM	12	2021
FY21	12041	6	52	Blushed Mango	Mango	0	1	0	0	0	0	0	1	Blushed Mango	Vuse 2 Pod Pack Blushed Mango 18mg	0	1	2	0	0	AED 43.0	2	AED 0.0	AED 2.1	0	86	0			Sub-Total - AED 343.0; VAT - AED 17.1; Free Shipping - AED 0.0; Total - AED 360.0	AED	$360.00	Complete	1945093	38.0	AED	2021-12-30 14:16:10	2021-12-30 16:15:44	12/30/2021	4:15:44 PM	12	2021
FY21	12041	6	52	Blushed Mango	Mango	0	1	0	0	0	0	0	1	Blushed Mango	Vuse 2 Pod Pack Blushed Mango 18mg	0	1	2	0	0	AED 43.0	2	AED 0.0	AED 2.1	0	86	0			Sub-Total - AED 343.0; VAT - AED 17.1; Free Shipping - AED 0.0; Total - AED 360.0	AED	$360.00	Complete	1945093	38.0	AED	2021-12-30 14:16:10	2021-12-30 16:15:44	12/30/2021	4:15:44 PM	12	2021
FY21	12041	6	52	Blushed Mango	Mango	0	1	0	0	0	0	0	1	Blushed Mango	Vuse 2 Pod Pack Blushed Mango 18mg	0	1	2	0	0	AED 43.0	2	AED 0.0	AED 2.1	0	86	0			Sub-Total - AED 343.0; VAT - AED 17.1; Free Shipping - AED 0.0; Total - AED 360.0	AED	$360.00	Complete	1945093	38.0	AED	2021-12-30 14:16:10	2021-12-30 16:15:44	12/30/2021	4:15:44 PM	12	2021
FY21	12041	6	52	Blushed Mango	Mango	0	1	0	0	0	0	0	1	Blushed Mango	Vuse 2 Pod Pack Blushed Mango 18mg	0	1	2	0	0	AED 43.0	2	AED 0.0	AED 2.1	0	86	0			Sub-Total - AED 343.0; VAT - AED 17.1; Free Shipping - AED 0.0; Total - AED 360.0	AED	$360.00	Complete	1945093	38.0	AED	2021-12-30 14:16:10	2021-12-30 16:15:44	12/30/2021	4:15:44 PM	12	2021
FY21	12041	6	52	Blushed Mango	Mango	0	1	0	0	0	0	0	1	Blushed Mango	Vuse 2 Pod Pack Blushed Mango 18mg	0	1	2	0	0	AED 43.0	2	AED 0.0	AED 2.1	0	86	0			Sub-Total - AED 343.0; VAT - AED 17.1; Free Shipping - AED 0.0; Total - AED 360.0	AED	$360.00	Complete	1945093	38.0	AED	2021-12-30 14:16:10	2021-12-30 16:15:44	12/30/2021	4:15:44 PM	12	2021

[v-easonf-msft]

Hi， @Anonymous 

I didn't find the ‘device flag’ you mentioned in your datasheet, can you provide simplified data for easy reference?

Best Regards,
Community Support Team _ Eason