潛力產品與店家銷售分析｜市場趨勢與數據洞察報告

Contents 目錄

介紹 About the data

朋友給的資料集，假設原始資料如下，一份門店數據，可以從中得知什麼? 如果有更多數據，還能評估什麼? Assuming the original data is as follows, a piece of store data, what can be learned from it? If there is more data, what can be evaluated?

一、瞭解【資料內容】、轉換【資料型態】Checking【data content】and converting 【data type】

資料內容 : 品項、店家代號、日期(2022/1~2023/9)
資料型態 : 文字 string、時間日期 datetime、數字 int

— 先把資料轉換型態 first convert the data type
品項新增一欄，填滿品項，轉置回去，再轉文字

=IF(A2<>"", A2, B1)

–店家轉文字

–轉日期，再複製回去

=SUBSTITUTE(SUBSTITUTE(D2,"年","/"), "月", "")

二、資料清理 Data cleaning

沒銷售資料的放置 0 Placement without sales “0”

三、資料剖析，整理出會用到的新欄位 Analyze data and sort out new fields that will be used

增加欄位【銷售數量總和】、【2022銷售數量總和】、【2023銷售數量總和】、【每季】分別的銷售數量總和 Add the fields

四、樞紐分析 Pivot Table、Cross Analysis

潛力產品分析

【產品銷售佔比】: 每項產品占總銷售數量比例，找出主要產品

2022
品項 ABC，佔比80.57%，推測為主要發展產品，如果有利潤數據，我用PARETO找出前80%利潤品項
ABC items, accounting for 80.57%, are speculated to be the main development products. If there is profit data, I would use PARETO to find the top 80% profit items.

品項 ABCD，佔比94.90%，D也可以考慮進去
ABCD items, accounting for 94.90%, D also could be considered.

2023
品項 ABC，佔比74.88%，推測為主要發展產品，如果有利潤數據，我用PARETO找出前80%利潤品項
ABC items, accounting for 74.88%, are speculated to be the main development products. If there is profit data, I would use PARETO to find the top 80% profit items.

品項 ABCD，佔比87.85%，D也可以考慮進去
ABCD items, accounting for 87.85%, D also could be considered.

【2023 年每月總銷售數量】、【2022 年每月總銷售數量】: 查看不同月份的銷售表現

2022
品項ABCD 應為季節性產品，四月~七月銷售較好
ABCD items should be seasonal products, with better sales from April to July.

2023
品項 ABCD 2023年只有四月銷售佳，需要探討比起2022年，五~七月比例為何降低如此多?
ABCD items only will have good sales in April, 2023. It needs to be discussed why the ratio from May to July has dropped so much compared to 2022?

品項 FGH 2022/10 開始才有資料，應為新品項，但佔比不高，暫時不納入考慮
FGH items only has data starting from Oct, 2022. It should be a new item, but the proportion is not high, so it will not be considered for the time being.

【2022、2023 每季銷售數量比較】: 推測可能原因

第一季銷售數量比較 Sales volume comparison in the first quarter

B產品可以多加發展
Item B could be developed more.

第二季銷售數量比較 Sales volume comparison in the second quarter

ABCD 品項，2023 四月銷售佳，五六月差距很大，但2022、2023第二季總量卻只有些微下降，推測四月有做活動，導致消費者幾乎都集中在四月購買

For items ABCD, sales were very good in April, 2023, with a big gap between May and June. However, the total volume in the second quarter of 2022 and 2023 only dropped slightly.

It is speculated that there will be activities in April, resulting in consumers almost all buying in April.

第三季銷售數量比較 Sales volume comparison in the third quarter

ABCD 品項，2023 銷售成長率為負，推測為外力因素或公關危機
Items ABCD, the sales growth rate in 2023 is negative, presumably due to external factors or public relations crisis.

G品項有機會發展，但與前面圖比較，有些月份為0、負，需要探討原因
G items have the opportunity to develop, but compared with the previous figure, some months are 0 or negative. The reasons need to be explored.

店家銷售分析

【ABCD 品項 TOP20 店家 ABCD items TOP20 stores】:

2022

如果有店家區域(北中南東)、人口數、人均收入、類型(商場、大賣場、百貨公司)…，可以分群，更好分析每種類型店家適合發展的產品，或是針對特定區域制定特定價格
If there are store areas (north, middle, south and east), population, per capita income, types (malls, hypermarkets, department stores)…, they can be divided into groups to better analyze the products suitable for each type of store, or to target specific areas, set a specific price.

2023

進貨量過低店家，可以當作未來增減店家參考
Purchase volume is too low Store. It could be used as a reference for adding or removing stores in the future.

【店家貢獻度分群 Store contribution grouping】

設置中貢獻為(前10%~25%)、高貢獻為(前10%)，寫回原始資料表格
Set the medium contribution to (top 10%~25%) and the high contribution to (top 10%), and put them back to the original data table.

新的樞紐分析表，選擇有貢獻度的資料
New pivot analysis table, select contributing data.

中貢獻、高貢獻度中，設置【計算加總至百分比】前80%銷售總數量店家，組成A級，後20%銷售總數量店家，組成B級
For medium contribution and high contribution, set 【Calculate and add up to percentage】 to store the top 80% of the total sales volume to form A level, and the bottom 20% of the total sales volume stores to form B level.

A級 ABCD 品項 TOP10 店家 (總共有171家，如果有更詳細店家資料，可以另外做銷售分析)
A-level ABCD items TOP10 stores (171 stores in total, if we have more detailed store information, can do additional sales analysis.)

PS 直接拉樞紐分析圖也可以，但如果有銷售數量和銷售利潤，分級會比較清楚觀察
We can also directly pull the pivot analysis chart, but if there are sales quantity and sales profit, the classification will be more clear.

另外發現有些店面只進單一產品，標示出來 -> 建立新table -> 用樞紐分析
In addition, I found that some stores only carry a single product, marked it -> Create a new table -> Use pivot analysis

五、結論 Conclusion

潛力產品分析儀錶板 Potential Product Analysis Dashboard

因為沒有利潤，只能以銷售數量推測，找出主要品項(ABCD)後，判斷是否為季節性產品
Since there is no profit data, we can only estimate based on the sales volume. After finding out the main items (ABCD), we can determine whether it is a seasonal product.

每季與去年同期比較，找出增減原因
Compare each quarter with the same period last year to find out the reasons for increases and decreases.

ABCD品項中，大部分收入來自高貢獻(前10%)的店家
Among ABCD items, most of the revenue comes from stores with high contributions (top 10%).

店家銷售分析儀錶板 Store sales analysis dashboard

ABCD品項，以數量(或利潤)做貢獻度分群，從中標示出高貢獻(前10%)、中貢獻(前10%~25%) ABCD items are divided into groups based on quantity (or profit) contribution, and high contribution (top 10%) and medium contribution (top 10%~25%) are marked.

再從中高貢獻度中店家中，篩選出A級店家(前80%)、B級店家(後20%)
Then, from the stores with medium and high contribution, A-level stores (top 80%) and B-level stores (bottom 20%) are selected.

列出A級TOP10店家，但A級店家總共有171家，如果有更詳細店家資料，可以另外做銷售分析
List the top 10 A-level stores, but there are 171 A-level stores in total. If we have more detailed store information, we can do additional sales analysis.

發現一些只進單一品項的店家，可以查看銷售數量
I found some stores that only carry a single item, we can check the sales quantity.

只賣ABCD產品中，存在【A級店家】的只有B產品，數量明顯也比ACD高，從這推測B產品可能相對比較好賣，或是利潤較高
Among the products that only sell ABCD, only product B in 【A-level store】, and the quantity is obviously higher than that of ACD. From this, it is inferred that product B may be relatively easy to sell, or may have higher profits.

更多 More
如果有單價、即期促銷、活動等，或是廣告投放，也能列入分析
If there are unit prices, immediate promotions, activities, etc., or advertising, they could also be included in the analysis.