Predictive Model Machine learning

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

@tony Any thoughts?

Hi @Phil_Waite. Thanks for sharing your scenario, that’s pretty interesting. I’m going to share this with the AppSheet ML team.

At a high level, AppSheet’s ML features work on tables. You specify a column that you want to predict (the “output” column) and then specify the columns that you want to use to make that prediction (the “input” columns). The platform treats the existing rows of data as input/output examples, and then you can use the PREDICT(...) function to predict an output column for new, previously unseen input rows. Right now there’s no way to get the confidence/ranking of the prediction, but that would be a good feature request.

In order to use the ML features, you’ll need to create a table that has the input/output columns described above. It looks like you’ve got your data already pretty close to that. You’ll just need a single table that has information that looks like this:

Company Date Statistic from three weeks before date Statistic from two weeks before date Statistic from one week before date Purchase week of date?
Company1 July 10, 2020 30 10 150 YES
Company1 July 3, 2020 8 30 10 NO
Company2 July 10, 2020 38 10 15 NO
Company2 July 3, 2020 5 30 10 NO

(etc.)

Then you can use the statistics columns as inputs and the Purchase? column as the output. This is kind of like taking your historical data and having a sliding window over it. Each company in your dataset will have a row for each window. Also, you can have as many statistics columns as you want, it doesn’t just have to be one.

More documentation is available here: https://help.appsheet.com/en/articles/2704830-predictive-models

1 Like

Hi Tony,

That’s really helpful. I will have 280 historical orders from the end of september last year over 42 weeks. I will have a play with the data and see what happens. I used to design futures trading systems and there are some similariies with that.

Many thanks

Phil

2 Likes

Cool. Feel free to share your thoughts and ideas w/the community.