I manage a small business and use Google Sheets to store customers data. I want to filter and analyze that data at the end of the month for which I use pivot tables. Now, the problem is my product has different variables such as design, color and size and if problem arises when I have a customer with multiple orders say 3. I can't figure out the best way to do for which I'm here. One solution I have thought is to have as many rows for a customer as much as their order is but in that case sheet seems very redundant and inefficient. Can someone with the same scenario give me some ideas which doesn't involve much programming or advance knowledge of Google Sheets.
Google-sheets – A Google Sheet with variables for products
google sheetspivot table
Best Answer
Your question suggests that you are having two technical problems:
First - you are confusing the difference between:
Second - products variants (color, size, design):
Transaction Data
Each transaction should be recorded in a single row. However, this data can be displayed in a different format for analysis.
In the simplified example shown below, each transaction is recorded on a single line of the sheet.
Transaction data
Displaying Transaction Information
In the example below, a query is used to analyse the sample data (including the various "Transaction groups") to display one product per customer per row. This can be used in a pivot table.
=query({"Date","Order ID","Customer","Product ID","Qty","Unit price","Sales Value";Orders!A3:C8,Orders!D3:G8;Orders!A3:C8,Orders!H3:K8;Orders!A3:C8,Orders!L3:O8;Orders!A3:C8,Orders!P3:S8},"select Col1,Col2,Col3,Col4,Col5,Col6,Col7 where Col4 is not null order by Col3, Col1")Query Output
Product variants
Your products have several variants (color, size, design).
Typically every product should have a unique code combining the variants. And there are advantages to using a consistent approach so that you can easily decipher/un-encode the meaning of a product code. For example, let's assume product X is available in 3 colours (Red, White, Blue), 3 sizes (Small, Medium, Large, and 2 designs (sHort, Long). There are 18 possible variants (3x3x2). Now you maybe you don't offer every variable for sale but each variant that you sell should have a unique product code.
For example, you might have use a format of - Product Code-Colour-Size-Design:
Product Variants - An alternative
You don't mention how many products you sell, nor the importance of the variants in the success of your business. But a possible alternative might be to have a simple Product Code (product X) and then include the variants in the transaction record. Some like this:
And then query for output like this:
=query({"Date","Order ID","Customer","Product ID","Color","Size","Design","Qty","Unit price","Sales Value";Orders_Variant!A3:C8,Orders_Variant!D3:J8;Orders_Variant!A3:C8,Orders_Variant!K3:Q8;Orders_Variant!A3:C8,Orders_Variant!R3:X8},"select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10 where Col4 is not null order by Col3, Col1")