Sales = # Sales calendar heatmap Here's a calendar heatmap of the invoices coming in:   Here's with revenue of 2019-2021 (no values, couldn't fit)  The amount of invoices doesn't directly correlate for higher revenue. We can observe how in the weekends, there generally aren't any invoices coming in. Secondly, the trend of **slowing business in 2021**. One can also argue that **summer time** brings more sales, and we'll show this better in the following plot. ### Purchase pattern *(Data gathered in 8.7.2021)* Here's a graph of all monthly invoices that've come in: Dark orange shows amount of purchases, light orange shows value of these purchases.  Peaks at April-June, dips in Dec-January. The abrupt dip at the end of 2021 is because those days haven't happened yet and there are only pre-orders. Peaks follow peaks, dips follow dips. However sometimes there are more individually expensive purchases. The actual revenue has stayed the same and seems to bounce back up in 2021. However, there's a hint of a down-trend with the revenue. Here's a heatmap of where and how many relative sales came from per year:   The most sales come from near Hollola, the main manufacturing factory. The area of Helsinki, Vantaa, Oulu and Tampere are also the ones buying the most. We also se already in this plot, how **Covid-19 affected sales in 2020 and on**; 2021 is missing many recurring customers. But the revenue itself hasn't fallen as substancially. **This type of trend is seen in many aspects:** ### Working hours The Job Numbers datatable has also a time added with the date, which none of the other datatable have. So we can figure out the relative hours which these invoices come in too through that: Here's in what hours invoices come in:  Many of the datapoints didn't have the time, that's why the 0:00 is so high. But it seems a lot of invoices come in 4PM. Here's when these invoices are recorded:  Seems to follow normal working hours. --- ## Product sales Here are top 50 most sold products:  Same, but showing the revenue from the items:  Here's top 50 products sorted by most revenue:  I also tried to see what products were frequently bought together. Example orders: ``` 2017-01-10 Fazer Leipomot Oy Ostoreskon 'Reissumies 4 kpl 2000043, Reissumies 4 kpl 2000043, Reissumies Tumma 4 kpl 2006687, Reissumies Tumma 4 kpl 2006687, Reissumies 2000045 12 kpl, Reissumies Kaura 4 kpl 2006824, Reissumies Kaura 4 kpl 2006824, Reissumies Kaura 4 kpl 2006824' 2017-01-16 Pousin Puutarha Oy 'SOK Ruukkujääsalaatti, SOK Lehtisalaatti, Aamukaste Makea Frisee 320 mm , SOK Lehtisalaatti, SOK Ruukkujääsalaatti, Sok jäärata 320 mm antif., Aamukaste Makea Frisee 320 mm , SOK Lehtisalaatti, SOK Ruukkujääsalaatti, SOK Lehtisalaatti, Aamukaste jääsalaatti 320 mm, Menu jääsalaatti pussi' ``` Top 10 least bought products: ``` [(('Basilika Ecoplast', 'Basilika yrttipussi', 'Basilika yrttipussi'), 1), (('Basilika Ecoplast', 'Korianteri yrttipussi', 'Korianteri yrttipussi'), 1), (('Porkkana 500 g pitkä pussi', 'Porkkana 1 kg', 'Porkkana 1 kg'), 1), (('Porkkana 1 kg', 'Porkkana 1 kg', 'Porkkana 500 g pitkä pussi'), 1), (('Jääsalaattipussi korkea', 'Jääsalaattipussi korkea', 'Salaattipussi painamaton'), 1), (('Jääsalaattirata opp 320 mm', 'Persilja 1 pnt', 'Persilja 1 pnt'), 1), (('Persilja 1 pnt', 'Persilja 1 pnt', 'Jääsalaattirata opp 320 mm'), 1), (('Peruna tk 400/400 0', '035', 'Peruna tk 400/400 0'), 1), (('Peruna tk 400/400 0', '035', '035'), 1)] ``` --- ### Top 5 products Sorted by most amount sold: ``` 2016807 Reissumies 4 kpl pyör Frequency: 78 Freq Percentile: 0.54% Price: 1292363.84 € Price percentile: 0.17% Amount: 32364180 Amount percentile: 5.38% 2017013 Reissumies Tumma 4 kp Frequency: 68 Freq Percentile: 0.47% Price: 5446911.04 € Price percentile: 0.70% Amount: 16627616 Amount percentile: 2.76% Reissumies 4 kpl 2015084 Promo Frequency: 27 Freq Percentile: 0.19% Price: 46907086.00 € Price percentile: 6.02% Amount: 11176250 Amount percentile: 1.86% 2017014 Reissumies Kaura 4 kpl Frequency: 42 Freq Percentile: 0.29% Price: 3641962.50 € Price percentile: 0.47% Amount: 7458350 Amount percentile: 1.24% 2016007 Reissumies 4 kpl Frequency: 36 Freq Percentile: 0.25% Price: 3413107.50 € Price percentile: 0.44% Amount: 7391140 Amount percentile: 1.23% ``` Sorted by most revenue: ``` 2016009 Reissumies 12 kpl Frequency: 48 Freq Percentile: 0.33% Price: 77563247.20 € Price percentile: 9.95% Amount: 6845780 Amount percentile: 1.14% Reissumies 4 kpl 2015084 Promo Frequency: 27 Freq Percentile: 0.19% Price: 46907086.00 € Price percentile: 6.02% Amount: 11176250 Amount percentile: 1.86% Reissumies Tumma 4 kpl 2006687 Frequency: 18 Freq Percentile: 0.12% Price: 42409081.10 € Price percentile: 5.44% Amount: 3649750 Amount percentile: 0.61% Paahtoleipä monivilja pp Priim Frequency: 11 Freq Percentile: 0.08% Price: 21259035.60 € Price percentile: 2.73% Amount: 3827350 Amount percentile: 0.64% Reissumies 4 kpl 2000043 Frequency: 35 Freq Percentile: 0.24% Price: 17812600.00 € Price percentile: 2.29% Amount: 6389000 Amount percentile: 1.06% ``` By times appeared in an invoice: ``` Porkkana 1 kg Frequency: 92 Freq Percentile: 0.63% Price: 8975426.20 € Price percentile: 1.15% Amount: 5501900 Amount percentile: 0.91% Lihapiirakka 10kpl pss Frequency: 81 Freq Percentile: 0.56% Price: 2786682.70 € Price percentile: 0.36% Amount: 4809719 Amount percentile: 0.80% Jääsalaatti Frequency: 80 Freq Percentile: 0.55% Price: 6406272.68 € Price percentile: 0.82% Amount: 3742019 Amount percentile: 0.62% 2016807 Reissumies 4 kpl pyör Frequency: 78 Freq Percentile: 0.54% Price: 1292363.84 € Price percentile: 0.17% Amount: 32364180 Amount percentile: 5.38% Sok Persilja Frequency: 76 Freq Percentile: 0.52% Price: 5376206.80 € Price percentile: 0.69% Amount: 3796780 Amount percentile: 0.63% ``` --- ### Top products by year Sorted by revenue ``` 2016 Lihapiirakka 4kpl flow Frequency: 5 Freq Percentile: 25.00% Price: 1257162.30 € Price percentile: 75.17% Amount: 341302 Amount percentile: 41.56% Pirkka lihapiirakka 4kpl flow Frequency: 2 Freq Percentile: 10.00% Price: 415198.80 € Price percentile: 24.83% Amount: 39770 Amount percentile: 4.84% Burger Frequency: 2 Freq Percentile: 10.00% Price: 0.00 € Price percentile: 0.00% Amount: 0 Amount percentile: 0.00% Jääsalaatti Fresh Frequency: 1 Freq Percentile: 5.00% Price: 0.00 € Price percentile: 0.00% Amount: 474 Amount percentile: 0.06% Ldpe pussi teippi Frequency: 1 Freq Percentile: 5.00% Price: 0.00 € Price percentile: 0.00% Amount: 0 Amount percentile: 0.00% ``` ``` 2017 Reissumies 4 kpl 2015084 Promo Frequency: 27 Freq Percentile: 1.72% Price: 46907086.00 € Price percentile: 12.17% Amount: 11176250 Amount percentile: 13.56% Paahtoleipä monivilja pp Priim Frequency: 3 Freq Percentile: 0.19% Price: 21246324.00 € Price percentile: 5.51% Amount: 3041400 Amount percentile: 3.69% Reissumies Tumma 4 kpl 2015085 Frequency: 15 Freq Percentile: 0.95% Price: 12293528.50 € Price percentile: 3.19% Amount: 4392450 Amount percentile: 5.33% Jääsalaatti Kaarlo Kani Frequency: 2 Freq Percentile: 0.13% Price: 10850112.00 € Price percentile: 2.81% Amount: 724500 Amount percentile: 0.88% SOK Korianteri 310 Frequency: 2 Freq Percentile: 0.13% Price: 9058802.03 € Price percentile: 2.35% Amount: 1006249 Amount percentile: 1.22% ``` ``` 2018 2016009 Reissumies 12 kpl Frequency: 14 Freq Percentile: 0.41% Price: 77563247.20 € Price percentile: 25.69% Amount: 1323050 Amount percentile: 0.97% Reissumies Tumma 4 kpl 2006687 Frequency: 12 Freq Percentile: 0.35% Price: 42409081.10 € Price percentile: 14.05% Amount: 2986250 Amount percentile: 2.20% Reissumies 4 kpl 2000043 Frequency: 26 Freq Percentile: 0.76% Price: 12940900.00 € Price percentile: 4.29% Amount: 3998000 Amount percentile: 2.94% Patrik Jälkiuuniruispala 6 kpl Frequency: 10 Freq Percentile: 0.29% Price: 7252812.36 € Price percentile: 2.40% Amount: 640500 Amount percentile: 0.47% Riisipiirakka 9kpl flow Frequency: 8 Freq Percentile: 0.23% Price: 5361112.10 € Price percentile: 1.78% Amount: 504028 Amount percentile: 0.37% ``` ``` 2019 2017013 Reissumies Tumma 4 kp Frequency: 10 Freq Percentile: 0.28% Price: 5446911.04 € Price percentile: 7.04% Amount: 6145546 Amount percentile: 3.97% Lehtsalat uusi y-pussi Frequency: 4 Freq Percentile: 0.11% Price: 5348148.00 € Price percentile: 6.92% Amount: 1011150 Amount percentile: 0.65% Suippopaprika Frequency: 2 Freq Percentile: 0.06% Price: 3711548.00 € Price percentile: 4.80% Amount: 373000 Amount percentile: 0.24% 2017014 Reissumies Kaura 4 kpl Frequency: 10 Freq Percentile: 0.28% Price: 3641962.50 € Price percentile: 4.71% Amount: 3089450 Amount percentile: 2.00% Lapu salati uusi y-pussi Frequency: 6 Freq Percentile: 0.17% Price: 3181158.16 € Price percentile: 4.11% Amount: 1387750 Amount percentile: 0.90% ``` ``` 2020 Pirkka Luomu Lehtisalaatti 007 Frequency: 5 Freq Percentile: 0.13% Price: 3801735.00 € Price percentile: 29.34% Amount: 671950 Amount percentile: 0.40% Lihapiirakka 4kpl flow Frequency: 8 Freq Percentile: 0.21% Price: 3061729.00 € Price percentile: 23.63% Amount: 1120659 Amount percentile: 0.66% Lapu salati y-pussi Frequency: 4 Freq Percentile: 0.11% Price: 2535097.50 € Price percentile: 19.56% Amount: 1228350 Amount percentile: 0.72% Persilja 1P Frequency: 8 Freq Percentile: 0.21% Price: 2189700.00 € Price percentile: 16.90% Amount: 639650 Amount percentile: 0.38% Vihreäkeiju Sitruunamelissa Frequency: 4 Freq Percentile: 0.11% Price: 349272.00 € Price percentile: 2.70% Amount: 61400 Amount percentile: 0.04% ``` ``` 2021 100% Kaurarieskanen pyöreäpohj Frequency: 4 Freq Percentile: 0.19% Price: 0.00 € Price percentile: nan% Amount: 247250 Amount percentile: 0.43% 2000035 Pitkojen Pitko 600g Frequency: 2 Freq Percentile: 0.09% Price: 0.00 € Price percentile: nan% Amount: 76100 Amount percentile: 0.13% 2017013 Reissumies 25µ Tumma Frequency: 1 Freq Percentile: 0.05% Price: 0.00 € Price percentile: nan% Amount: 41250 Amount percentile: 0.07% 2017013 Reissumies 25 µ Tumma Frequency: 1 Freq Percentile: 0.05% Price: 0.00 € Price percentile: nan% Amount: 8600 Amount percentile: 0.01% 10005373 Huhtamäki Paper Cup 1 Frequency: 1 Freq Percentile: 0.05% Price: 0.00 € Price percentile: nan% Amount: 533 Amount percentile: 0.00% ``` --- ### Top 5 least performing products By total amount sold: ``` Saariston Porkkana 1 kg -225 532.9149 Pe-pussi 440x900 -71 450.0000 Pe-pussi UR adap. PRO 230 x 23 -23 478.2609 Sekaleipä pitkä 0.0000 Setsuurisämpylä 0.0000 ``` By revenue: ``` Saariston Porkkana 1 kg -1 849 370 € Pe-pussi UR adap. PRO 230 x 23 -208 487 € 0 0.00 € Persilja antifog 0.00 € Persilja opp/pp 0.00 € ``` By times appeared in an invoice (Actually 1045 items, here's a few:) ``` Menu 5991 245 33x33 50 kp 445 1 10 kg vihreä 1 Jyväsydänsämpylä 6kpl 360g 1 PP-pussi 200*280 1 Karhujää 10 kg murska 1 ``` ### 3 most common products sold together ``` ((' 2017013 Reissumies Tumma 4 kp', ' 2017013 Reissumies Tumma 4 kp', ' 2017013 Reissumies Tumma 4 kp'), 930), ((' Lidl lihapiirakka 8 kpl', ' Lihapiirakka 10kpl pss', ' Lihapiirakka 10kpl pss'), 890), ((' Lihapiirakka 10kpl pss', ' Lihapiirakka 10kpl pss', ' Lihapiirakka 10kpl pss'), 785), ((' Sok Tilli', ' Sok Tilli', ' Sok Tilli'), 702), ((' Iso Pehtoorin pussi painamaton', ' Painettu Yleispussi 235*450 mm', ' Painettu Yleispussi 235*450 mm'), 690), ``` ### Item group sales Most sold ``` Vikettipussit painettu 139 867 799 Muotosauma painettu 54 078 994 Radat painettu (kq) 45 435 489 Vikettipussit painamaton 14 560 528 Radat painamaton (kq) 12 137 473 ``` Most sold by value ``` Vikettipussit painettu 32 847 991 € Erikoispussit painettu 10 332 000 € Muotosauma painettu 10 035 348 € feipit 3 870 000 € Vikettipussit painamaton 2 677 500 € ``` Most frequent ``` Vikettipussit painettu 3049 Muotosauma painettu 1371 Radat painettu (kq) 991 Vikettipussit painamaton 362 Radat painamaton (kq) 292 ``` Least sold ``` älä käytä 0 OPP valkoinen 5 700 PP 25 638 rahti 29 600 Barrier 49 500 ``` I can't be sure of the "least sold" as many items don't have a "sell value". Least frequent ``` OPP valkoinen 1 (kq) Alihankintatyöt tai osa-alihankinta mm. Kokemäki (kq) 1 älä käytä 1 rahti 2 Laminaattiradat (kq) 3 ``` ### Conclusions of product basics - Most sold - 2016807 Reissumies 4 kpl pyör 32364180 - Overall Fazer products - Group: Vikettipussit painettu - Least sold - Saariston Porkkana 1 kg -225532.9149 - (I don't actually know what negative amount means here...) - Group: "OPP valkoinen" & "kq) Alihankintatyöt tai osa-alihankinta mm. Kokemäki (kq)" - Most lucrative - 2016009 Reissumies 12 kpl 77 563 247.20 € - Overall Fazer products - Group: Vikettipussit painettu 32 847 991 € - Least lucrative - Saariston Porkkana 1 kg -1 849 370 € - Group: Again, not sure of this one - Most frequent - Porkkana 1 kg 92 - Group: Vikettipussit painettu 3049 - Least frequent - 1045 individual items only bought once - Group: OPP valkoinen Again, we can see how the top products are Fazer products. The product groups make sense, packaging is the main product of Aspelin afterall. --- #### [SOLVED] Mystery of the "group" ID I went back to the Oscar system and used their built-in datafetching features and found a table describing what these group IDs mean. Most products have a "group" they belong to. Here's what group these top 50 products belong to:  0 is basically just "nan", so the product ID has no assigned group. A grouping or categorization of the items would be usefull for **purchase suggestions**, as any potential machine learning or neural network method for this **would benefit from such a high impact feature**. Let's look what group 5050 has:  Group 5110:  What about the ones with no group:  --- ## Most sold items by category 0 means item has no group:  Here's a how the top 50 most sold items are categorized:  Here's most sold materials used to create items:  Who are buying these items:  And if you remember how Fazer is the main customer, let's see what they buy:  With added category:  ### Conclusion on groups The most sold products are in the *"Vikettipussit painettu" and "Muotosauma painettu"* **categories**. Makes sense as Fazer The most sold item is a bag for 1kg carrots, but the most sold materials to create other packaging is this *"opp320 \*0.025v8"* type, that Boströn E. Oy Ab seems to buy a lot. --- ## Sales growth Growth is calculated basically like this: ```(This_month / Last_month) - 1```  The ["Growth"] column is then plotted. ---- **Month-over-Month growth**  ---- **MoM growth in revenue**  ---- Here's 2016 and end of 2021 take out, to make this pattern more clear:  Beginning of summer growth is strong, end of summer it nosedives. ---- **Quarter-over-Quarter growth**  Q1 is the strongest and Q4 is the weakest. This is very common in almost any industry. ---- **QoQ growth in revenue**  In revenue, this trend is even more obvious. ---- **Year-over-Year growth**  The pattern show here again how in spring the growth is positive and in autumn and winter, it's negative. ---- **YoY growth in revenue**  ---- Let's drop 2016 and end of 2021 again:  Revenue growth for 2021 seems to be all around worse than in previous years. --- ## Sales from jobs Jobs are basically describes what items invoice holds. None of the items in it had monetary values, so those had to be taken from **Item Registery**. But seemingly, **only about half of the items in Jobs have these values, rest are null.** This made it difficult to find out the true value of sales. ---- Here's top 5 customers by sale value.  ---- **Here's when the 3 different price columns are sorted:** Sorted by "Keskihinta": ![Uploading file..._c816ze3r3]() Närpes Grönsaker has the highest "average price" ---- Sorted by "Myyntihinta":  Fazer has the highest "sell price", Oksasen Puutrha Oy is second. ---- Sorted by "Ostohinta":  ---- Fazer takes the cake in "buy price" too. Kyröntarhat Laskut is takes the second place. **But overall:** Fazer is disproporsionally the "best value" customer/supplier. ---- ## Conclusion Covid-19 affected sales negatively. Less customers and less suppliers due to lockdown and quarantines. But before the pandemic, business was doing well and was stable. It wasn't really failing nor growing. Fazer Leipomot Oy Ostoreskon is the #1 where the most value goes through. It has the most invoices too.