Predictive Model Machine learning

Phil_Waite · 07-11-2020 12:00 PM

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	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	Cash Status	Invoice Number	Route	User Stop	Intended Payment Type	Driver User Id	Due Date	Outlet Discounted	Invoice Discounted
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

Aleksi

@tony Any thoughts?

tony1

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

Phil_Waite

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

tony1

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