Hi,
I ahve a sales app with outlets that place orders periodically. I also have data from another source that shows logins by customers when they visit the an outlet. Not all visitors to an outlet login but generally, the more logins - the more people visited the outlet.
I have created a 7 day averge for number of logins per week for the last 6 weeks for each outlet. I then divide the most recent weekโs last 7 day average to the average 6 weekly login. I get a percent deviation from this.
I have data showing when some outlets order products from us. I am assuming (rightly or wrongly) that when an outlet has a high postive % indicating that they have been unsusually busy week that they are more likely to order from us the week after the increase in their sales.
How can I plot this data in a googlesheet and table to use machine learning and train appheet to create a model of which outlets are most likely to order from us in the coming week?
I can simply alloctae a % to each outlet based on how unusually busy they have been and order the outlets in a deck/table view based on that but I would like to use the machine learning capabilty of appsheet to look at historical data and dates of orders compared to the number of logins. It may pick up on something other than my assumption that unusually high number of logins = more likely to order.
For example
Below is the data with averages and % deviation
First 2 outlets have negative PD (percentage deviation) meaning they have been less busy then average.
The Four below have been busier. I have the % deviation shown for each of the last 6 weeks to show a history trail but PD6 is the important one as that was last week.
I should call the Old Drum fist as they have had the biggest uptick in logins/visitors/sales and need more stock. After that Cuisine Catering etc.
The 6 week periods that are being averaged start with the followinng dates
06/06/2020 13/06/2020 20/06/2020 27/06/2020 04/07/2020 11/07/2020
week 1 etc is the average number of logins.
PD1 etc is the % deviation from the 6 week average logins which is found in Avergae 1 etc.
Unique Id | Week 1 | Week 2 | Week 3 | Week 4 | Week 5 | This Weeks Number | PD1 | PD2 | PD3 | PD4 | PD5 | PD6 | Average 1 | Average 2 | Average 3 | Average 4 | Average 5 | Average 6 | Name And Town | |||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Todayโs Date | 06/06/2020 | 13/06/2020 | 20/06/2020 | 27/06/2020 | 04/07/2020 | 11/07/2020 | 0 | |||||||||||||||||||||||||||||||||||
e3733cf8 | 89 | 29 | 6 | 20 | 32 | 3 | 160.49% | -21.97% | -84.14% | -47.60% | 2.67% | -89.94% | 34.17 | 37.17 | 37.83 | 38.17 | 31.17 | 29.83 | Bench Bar @ Surrey Sports Park | |||||||||||||||||||||||
e75a63ef | 26 | 31 | 1 | 24 | 29 | 20 | -31.88% | -21.52% | -97.09% | -31.43% | 0.58% | -8.40% | 38.17 | 39.50 | 34.33 | 35.00 | 28.83 | 21.83 | Avenue Lawn Tennis Squash & Fitness Club | |||||||||||||||||||||||
1833e855 | 0 | 24 | 1 | 20 | 24 | 24 | -100.00% | 14.29% | -95.24% | 46.34% | 35.85% | 54.84% | 22.33 | 21.00 | 21.00 | 13.67 | 17.67 | 15.50 | Cuisine Catering | |||||||||||||||||||||||
461b8fb3 | 4 | 25 | 0 | 13 | 24 | 34 | -89.74% | -41.41% | -100.00% | -59.79% | -4.00% | 104.00% | 39.00 | 42.67 | 42.50 | 32.33 | 25.00 | 16.67 | Old Drum | |||||||||||||||||||||||
7e4a4f04 | 27 | 50 | 50 | 100 | 100 | 100 | -55.12% | -18.26% | -19.57% | 60.86% | 46.70% | 40.52% | 60.17 | 61.17 | 62.17 | 62.17 | 68.17 | 71.17 | Storrington Sports & Social Club | |||||||||||||||||||||||
c70a2256 | 56 | 50 | 50 | 100 | 100 | 100 | 32.28% | 8.30% | -2.28% | 88.09% | 47.78% | 31.58% | 42.33 | 46.17 | 51.17 | 53.17 | 67.67 | 76.00 | Wine Rack |
Below are example orders places in our Orders table.
Order Id | Contact Status | Order Status | Order Status Calc | Run 2 | Running Sheet Calc | Price List | Name & Town | Outlet no | Order Date | Delivery Date | Complete Order Total Inc Vat | Order Taken By | Date Taken | Time Taken | Standing Delivery/Order Details | Payment Type | Cash Status | Invoice Number | Route | User Stop | Actual Payment Type (driver) | Intended Payment Type | Signature | Print Name | Driver User Id | Due Date | Photo | Outlet Discounted | Invoice Discounted | Invoice Discounting Open/Closed |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
ef7d1a10 | FALSE | Archived | Closed | Run | Custom | Bench Bar @ Surrey Sports Park | e3733cf8 | 13/06/2020 | 08/07/2020 | 0 | phil@listersbrewery.com | 07/07/2020 | 13:09:25 | Always ring Kerry on 07730 132486 30 mins before arrival | FALSE | #N/A | C | 100 | phil@listersbrewery.com | 08/08/2020 | ||||||||||
ca459c09 | FALSE | Archived | Closed | Run | Band 3 | Avenue Lawn Tennis Squash & Fitness Club | e75a63ef | 11/07/2020 | 08/07/2020 | 0 | hilary@listersbrewery.com | 07/07/2020 | 15:07:13 | DELIVER AFTER 10.30AM IF POSS | FALSE | #N/A | B | 20 | phil@listersbrewery.com | 08/08/2020 | ||||||||||
47eb1069 | FALSE | Archived | Closed | Run | Band 3 | Cuisine Catering | 1833e855 | 07/07/2020 | 08/06/2020 | 0 | hilary@listersbrewery.com | 07/07/2020 | 15:09:18 | 0 | FALSE | #N/A | C | 30 | phil@listersbrewery.com | 08/08/2020 | ||||||||||
511df072 | FALSE | Archived | Closed | Run | Band 3 | Old Drum | 461b8fb3 | 13/07/2020 | 15/06/2020 | 0 | hilary@listersbrewery.com | 07/07/2020 | 15:13:50 | Deliver after 10.30am | FALSE | #N/A | B | 70 | phil@listersbrewery.com | 08/08/2020 | ||||||||||
b5cb8c23 | FALSE | Archived | Closed | Run | Band 3 | Storrington Sports & Social Club | 7e4a4f04 | 29/06/2020 | 08/07/2020 | 201.6 | hilary@listersbrewery.com | 07/07/2020 | 15:23:38 | 0 | FALSE | 3369 | C | 0 | phil@listersbrewery.com | 08/08/2020 | TRUE | Yes | ||||||||
da5642fd | FALSE | Archived | Closed | Run | Band 3 | Wine Rack | c70a2256 | 29/06/2020 | 27/06/2020 | 177.6 | hilary@listersbrewery.com | 07/07/2020 | 15:40:16 | Deliver after 10am or leave & put invoice through letterbox | FALSE | 3370 | B | 0 | Bacs | phil@listersbrewery.com | 08/08/2020 | TRUE | Yes |
These orders fall within weeks that have data for customer logins. How can I use machine learning in appsheet to compare order dates with login data.
Many thanks
Phil
User | Count |
---|---|
40 | |
36 | |
32 | |
23 | |
16 |