1
00:00:00,000 --> 00:00:23,400
Okay, so we'll just we'll just get into it I

2
00:00:23,400 --> 00:00:31,560
don't have the the audio or the the recording enabled through Google meets

3
00:00:31,560 --> 00:00:39,240
but but I'll just go ahead and present my screen and well we'll give David one

4
00:00:39,240 --> 00:00:45,080
second to get back and then then we'll start okay so I'll just go ahead and

5
00:00:45,080 --> 00:01:14,080
just share with you would I have or actually okay all right so

6
00:01:14,080 --> 00:01:20,520
all right so we'll just jump into it so what you can get over here is you can

7
00:01:20,520 --> 00:01:30,800
actually get pretty rich data from the from the LCB so you know if you just do

8
00:01:30,800 --> 00:01:47,240
a Google search for you know washing you know Washington cannabis data actually

9
00:01:47,240 --> 00:02:05,200
you can get the the frequently requested lists then you can make a public records

10
00:02:05,200 --> 00:02:14,320
request and then they'll give you essentially a data dump like this and

11
00:02:14,320 --> 00:02:20,200
so you know this is basically your starting point if you're working with

12
00:02:20,200 --> 00:02:30,280
Washington traceability data right so this is for the month of December and

13
00:02:30,280 --> 00:02:46,440
they dumped out almost 30 gigabytes of data so that's a lot to chew on so you

14
00:02:46,440 --> 00:02:53,160
know how do you go about doing that so a tool I use I'm not sure if you use

15
00:02:53,160 --> 00:03:00,880
Python but if you're looking to get set up with Python you can look at the

16
00:03:00,880 --> 00:03:08,520
anaconda distribution it looks like they are migrating to paid so I'm not a huge

17
00:03:08,520 --> 00:03:14,640
fan of that so if anyone has any alternatives for Python distributions you

18
00:03:14,640 --> 00:03:31,880
know definitely suggest them but exactly so if you're working on windows over

19
00:03:31,880 --> 00:03:39,080
here okay so you open up your bash terminal and you can bypass anaconda all

20
00:03:39,080 --> 00:03:45,680
you would use anaconda for is to set up the libraries for you just makes a more

21
00:03:45,680 --> 00:03:49,560
convenient way but if you use if you go to the bash terminal and just type

22
00:03:49,560 --> 00:03:55,400
jupyter notebook two words it'll open up the jupyter notebook for you so here's just the

23
00:03:55,400 --> 00:04:00,480
anaconda bash and so you say you can just type in jupyter notebook

24
00:04:00,480 --> 00:04:12,400
how do you spell it jupyter

25
00:04:30,480 --> 00:04:40,440
you're using visual studio to okay exactly yeah and so redirect you if it

26
00:04:40,440 --> 00:04:44,480
doesn't just click on that bottom link there and it'll it should open it up in

27
00:04:44,480 --> 00:04:47,480
your browser

28
00:04:47,480 --> 00:05:00,280
that way you bypass anaconda so if they do have to or if they do charge that's

29
00:05:00,280 --> 00:05:07,480
easier way to get into everything that you've got awesome so that everybody is

30
00:05:07,480 --> 00:05:11,760
on a Mac it's just you open up the terminal type in jupyter notebook and it'll

31
00:05:11,760 --> 00:05:20,120
automatically open so just for the sake of this I'm probably just going to be

32
00:05:20,120 --> 00:05:27,000
running a lot of this in spider and it's just sort of tried and true that I use

33
00:05:27,000 --> 00:05:39,400
but I but I just just just to make sure everything runs smoothly will just run

34
00:05:39,400 --> 00:05:45,920
it in spider over here but basically I'll you know provide you with this

35
00:05:45,920 --> 00:05:52,680
script afterwards but I was just going to run through just an example of you

36
00:05:52,680 --> 00:05:59,560
know how to you know read in this data here and then see what we we can do with

37
00:05:59,560 --> 00:06:11,560
it so so what I've done is I you know my company's can litig so I'm primarily

38
00:06:11,560 --> 00:06:20,800
interested in lab results here so I've downloaded this lab results file which

39
00:06:20,800 --> 00:06:32,440
has a lab result CSV in it and so we're just going to read that in you know

40
00:06:32,440 --> 00:06:37,720
right off the bat and so you know what do we need to do that you know we need

41
00:06:37,720 --> 00:06:47,440
pandas and so if you've worked with Python this is a nifty tool so you're

42
00:06:47,440 --> 00:06:59,040
going to import pandas and so I just have the CSV saved locally and then you

43
00:06:59,040 --> 00:07:03,800
know what you can notice is if it's it's going to be a little tricky to work with

44
00:07:03,800 --> 00:07:10,840
the way the date the state formats the data but essentially they have it

45
00:07:10,840 --> 00:07:21,520
formatted as UTF-16 with tab delimited values so you can go ahead and you know

46
00:07:21,520 --> 00:07:31,440
run in your data here and it's a large file here so I'm reading in this file

47
00:07:31,440 --> 00:07:41,880
right here and so this is almost two gigs but we'll just look at lab results

48
00:07:41,880 --> 00:07:49,720
for the past month okay so while this is reading in I'll go ahead and just get

49
00:07:49,720 --> 00:08:11,880
the next block of block of code here all right looks like there's a little okay

50
00:08:11,880 --> 00:08:23,800
so we've read in the data we have almost 2 million observations so this is a

51
00:08:23,800 --> 00:08:29,440
fairly large data set so that's another reason why I wanted to look at the the

52
00:08:29,440 --> 00:08:35,680
lab results here so if any of you are feeling a bit more ambitious there's

53
00:08:35,680 --> 00:08:42,600
probably a lot of really interesting data to be found here and like the sales

54
00:08:42,600 --> 00:08:53,800
data the tax data strain data and then I was going to actually look at inventory

55
00:08:53,800 --> 00:09:01,200
transfers next there may be some interesting things with transfers so

56
00:09:01,200 --> 00:09:04,880
there's a lot of rich data here but enough of that so we'll just go ahead

57
00:09:04,880 --> 00:09:10,920
and jump back in here we've got these lab results and okay so what do they

58
00:09:10,920 --> 00:09:16,720
what do they look like so basically I'm just going to just grab the you know the

59
00:09:16,720 --> 00:09:25,560
first observation here just to okay so what does this look like you've

60
00:09:25,560 --> 00:09:34,240
basically just got a giant dictionary here the key fields are actually going

61
00:09:34,240 --> 00:09:44,920
to be you know your global ID you know then when it was created at and then

62
00:09:44,920 --> 00:09:49,680
there's some other things here like so this is where it was tested at so this

63
00:09:49,680 --> 00:09:56,160
one was tested back in 2016 so you may not want to look at that and then they

64
00:09:56,160 --> 00:10:03,120
dump out all of these values what you'll notice is a lot of them are in it or NAM

65
00:10:03,120 --> 00:10:10,560
so for example at the moment the state doesn't mandate pesticide testing

66
00:10:10,560 --> 00:10:16,400
however or require pesticide testing however I think that is going to change

67
00:10:16,400 --> 00:10:22,920
here in the you know the coming year or so so that could be interesting data to

68
00:10:22,920 --> 00:10:28,480
look out for if if pesticide data starts coming through so it'd be just

69
00:10:28,480 --> 00:10:34,840
interesting to look at pesticide levels okay so you know and then if you want to

70
00:10:34,840 --> 00:10:48,040
see the actual data points here you know you can just list you know you can just

71
00:10:48,040 --> 00:10:56,600
list the so you know it's it's dumping out a lot of data so you know we're

72
00:10:56,600 --> 00:11:03,840
going to need to sort of narrow this down a little bit okay so how can we

73
00:11:03,840 --> 00:11:23,440
narrow narrow this down well you can you know you can figure out okay we know

74
00:11:23,440 --> 00:11:29,400
what type of samples do we you know do we do we have here you say you can look

75
00:11:29,400 --> 00:11:34,200
at all the intermediate types and you can find out you can list all of the

76
00:11:34,200 --> 00:11:38,520
intermediate types that are unique

77
00:11:44,280 --> 00:11:49,840
okay so now you so now we're starting to you know find out a bit more about the

78
00:11:49,840 --> 00:11:55,960
data here we're okay so these are all the product types that you'd have in

79
00:11:55,960 --> 00:12:11,840
Washington okay and so we also didn't want to look at you know every data

80
00:12:11,840 --> 00:12:24,160
point here so I was going to restrict them to to December so let's see

81
00:12:24,160 --> 00:12:44,200
well let's actually just walk through this so basically what I had done is I'd

82
00:12:44,200 --> 00:12:48,800
realized there's just going to be you know a little too many here to work with

83
00:12:48,800 --> 00:12:53,160
a million so I thought okay let's just work with you know ten thousand at a

84
00:12:53,160 --> 00:13:02,760
time just just so it it's it's a bit more manageable so you know just created

85
00:13:02,760 --> 00:13:14,160
a sample here so you know there's the sample and then the full data set is the

86
00:13:14,160 --> 00:13:26,800
two million so this is just for speed of sake but we'll go we'll but we'll we'll

87
00:13:26,800 --> 00:13:34,840
remove the the limit at the end so then just to create a few helper variables

88
00:13:34,840 --> 00:13:43,920
here so right so you have the the tested at time and so you can look at the first

89
00:13:43,920 --> 00:13:51,480
five observations like that you know you could look at the first ten

90
00:13:51,480 --> 00:14:00,420
observations okay and so we've got an ISO formatted time here and so we can

91
00:14:00,420 --> 00:14:09,280
create a nice time variable that's you know time aware so simple enough we can

92
00:14:09,280 --> 00:14:29,800
create a time variable and then given the sample global ID you know you can

93
00:14:29,800 --> 00:14:38,400
also figure out essentially what lab this is coming from so the so that would

94
00:14:38,400 --> 00:14:46,760
be their license number but they realize this isn't actually going to quite do

95
00:14:46,760 --> 00:14:59,440
the trick but anyways you know you can create some helper variables there okay

96
00:14:59,440 --> 00:15:06,920
so now now let's say you know you just want to you know look at one group so

97
00:15:06,920 --> 00:15:14,480
say you just wanted to look at flower so you could just you can say okay let's

98
00:15:14,480 --> 00:15:24,880
just find all of the samples where the intermediate type is a flower lot and so

99
00:15:24,880 --> 00:15:36,880
you can find the flower so about you know roughly 70% of the samples are

100
00:15:36,880 --> 00:15:51,840
flower samples all right and so something I did here was essentially what was

101
00:15:51,840 --> 00:16:05,160
interested to see if there is any correlation between CBD and in THC so

102
00:16:05,160 --> 00:16:22,280
we're going to need a few more packages here so up at the top you can add in the

103
00:16:22,280 --> 00:16:30,320
stats models and this is there's this is a statistics package that I like to use

104
00:16:30,320 --> 00:16:42,320
for simple regressions and things like that so you import your stats models

105
00:16:42,320 --> 00:17:04,280
right and so for a regression you're going to need an in X your dependent

106
00:17:04,280 --> 00:17:08,240
variables or your independent variables so in this case are independent

107
00:17:08,240 --> 00:17:17,920
variables I was wanting to see if CBD a has any effect on Delta 9 THC a so you

108
00:17:17,920 --> 00:17:26,440
can get your X and then your dependent variable Y and then you'll need a

109
00:17:26,440 --> 00:17:38,200
constant because you're you know why you know beta naught plus beta 1 times X right

110
00:17:38,200 --> 00:17:45,560
so we're going to add the constant and we're going to fit the regression here

111
00:17:45,560 --> 00:17:58,720
right and so we've run a regression the first thing I do is look at the R squared

112
00:17:58,720 --> 00:18:03,800
and so this is the total amount of variation that we just explained in this

113
00:18:03,800 --> 00:18:12,120
regression so we just explained you know about 6% of all the variation so this is

114
00:18:12,120 --> 00:18:19,680
not a very explanatory model here so there's not you couldn't put a lot of

115
00:18:19,680 --> 00:18:26,520
stake in this model you know it looks at rough estimate that there may be a

116
00:18:26,520 --> 00:18:34,320
negative relationship between CBD and THC but I will you know you can't make any

117
00:18:34,320 --> 00:18:44,200
conclusions off of this model but it's you know and then what an interesting

118
00:18:44,200 --> 00:18:49,760
rule is always look at the data so let's you know see what you know we just did

119
00:18:49,760 --> 00:18:56,160
here so we basically just we just basically fit this line so we said okay

120
00:18:56,160 --> 00:19:08,600
is there a relationship between CBD and THC and it looks like there may be a

121
00:19:08,600 --> 00:19:16,240
slight you know negative relationship but it's you know there's there's a large

122
00:19:16,240 --> 00:19:23,320
group here so there's it's not not the best trend line in the world but you

123
00:19:23,320 --> 00:19:31,760
know it looks like if you just looked at the the high CBD strains then there may

124
00:19:31,760 --> 00:19:42,280
be you know a better linear relationship there so so that's that's comparing two

125
00:19:42,280 --> 00:19:53,040
analytes and so another interesting thing you can do is let's say we wanted

126
00:19:53,040 --> 00:20:02,520
to look at one of these analytes over time so we just looked at flower so now

127
00:20:02,520 --> 00:20:12,480
let's say okay what happens if we want to look at butane over time right because

128
00:20:12,480 --> 00:20:21,760
some of these concentrates are extracted with butane so you know you may want to

129
00:20:21,760 --> 00:20:33,520
know okay is there you know is there any level of butane in the final product so

130
00:20:33,880 --> 00:20:49,520
remember above we listed all the intermediate types and so what I did was

131
00:20:49,520 --> 00:20:57,920
I basically just went and I grabbed any one that ended in concentrate and put it

132
00:20:57,920 --> 00:21:01,760
into this list so you could do that programmatically I just did that

133
00:21:01,760 --> 00:21:07,400
manually but I just manually created this list you know some of these you may

134
00:21:07,400 --> 00:21:12,800
not expect to have butane in there so you may not expect to have butane in the

135
00:21:12,800 --> 00:21:18,200
non solvent based concentrates however we can exclude those in the future for

136
00:21:18,200 --> 00:21:30,160
now I just wanted to grab everything that was a concentrate okay and so you

137
00:21:30,160 --> 00:21:34,820
know in the laboratory setting the meaningful way that you would look at

138
00:21:34,820 --> 00:21:41,320
these is you would look at them over time so you would say okay over time is

139
00:21:41,320 --> 00:21:48,160
there any trend in this analyte because that's what you would you would be

140
00:21:48,160 --> 00:21:53,760
curious about is this analyte is there a positive trend is there a negative trend

141
00:21:53,760 --> 00:22:05,760
so how do we do that so I am going to group the data by day so you've got

142
00:22:05,760 --> 00:22:12,800
these long lines here I'm going to share the code afterwards and I've also

143
00:22:12,800 --> 00:22:22,480
included these references here for how you can go about you know how you can go

144
00:22:22,480 --> 00:22:29,040
about aggregating the number of occurrences per day so this is a useful

145
00:22:29,040 --> 00:22:45,480
thing to do in pandas so let's go ahead and create these series here okay so

146
00:22:45,480 --> 00:22:53,400
about 20 so about 19% of the samples is or concentrates so then you can

147
00:22:53,400 --> 00:23:03,080
aggregate them by day and so what does the daily concentrates look like well

148
00:23:03,080 --> 00:23:13,320
this is a series over time and once again you can look at all of the

149
00:23:13,320 --> 00:23:26,640
variables so it's I'm creating the mean of each variable by day so that's

150
00:23:26,640 --> 00:23:33,560
numbers so let's look at it so that it's more meaningful so like what I always

151
00:23:33,560 --> 00:23:44,760
say is look at the data so we're going to create a figure for figures we're

152
00:23:44,760 --> 00:23:57,080
going to need matplotlib so the classic way to import matplotlib is import

153
00:23:57,080 --> 00:24:13,640
matplotlib.pyplot as PLT and we're also using the datetime package so we can add

154
00:24:13,640 --> 00:24:23,120
that to the top of the script as well so we're going to create a figure we're

155
00:24:23,120 --> 00:24:32,960
going to plot daily concentrates we're going to plot the butane on the y-axis

156
00:24:32,960 --> 00:24:41,520
we're going to fit a trend line by running the same regression that we ran

157
00:24:41,520 --> 00:24:49,400
well the same regression model that we used earlier except now our X value is

158
00:24:49,400 --> 00:25:03,720
going to be just a time series so this is just going to be the index so you can

159
00:25:03,720 --> 00:25:11,520
fit a trend line and then we'll plot the trend line so putting it all together

160
00:25:11,520 --> 00:25:27,480
you can get the average butane whoops I misspoke so putting it all together you

161
00:25:27,480 --> 00:25:34,320
can get the average butane level in Washington concentrates over time so if

162
00:25:34,320 --> 00:25:45,800
you I put the the limits here so if you look at the limits you'll see that

163
00:25:45,800 --> 00:25:54,800
butane has an action limit of 5,000 so anything less than 5,000 ppm will pass

164
00:25:54,800 --> 00:26:00,960
so as you can see you know these concentrates you know these are passing

165
00:26:00,960 --> 00:26:07,680
samples but you know as you'll see there's a you know a non-zero average

166
00:26:07,680 --> 00:26:15,640
level of butane you know that's 100% up to the consumer and you know it's under

167
00:26:15,640 --> 00:26:23,080
the action limit so so it is what it is I think it's interesting to note that in

168
00:26:23,080 --> 00:26:34,160
this small sample you do see a negative trend and then let's just for fun let's

169
00:26:34,160 --> 00:26:46,720
just go ahead and essentially remove this limit here and just and just see

170
00:26:46,720 --> 00:27:00,880
what see what may happen I haven't actually run this but okay and so now

171
00:27:00,880 --> 00:27:13,600
we can see the data in the full sample and it's similar here where you've got

172
00:27:13,600 --> 00:27:25,480
the negative trend so I think I'll go ahead unless I've been sort of running

173
00:27:25,480 --> 00:27:30,680
through this fairly quickly I should have asked has there been any questions

174
00:27:30,680 --> 00:27:35,200
so far about some of the things that that we've done

175
00:27:35,200 --> 00:27:47,520
awesome can you go over real quick can you go over again where you went and found that data

176
00:27:47,520 --> 00:27:54,840
absolutely so you let me put it in the

177
00:27:54,840 --> 00:28:17,080
here I'll share it with you so there is the data dump and then I believe these

178
00:28:17,080 --> 00:28:22,560
put they put them out periodically but you know if you need historic data then

179
00:28:22,560 --> 00:28:28,200
you know there is just the link to make the request and the request is fairly

180
00:28:28,200 --> 00:28:35,920
simple you just you know they have just a bullet point of you know they just

181
00:28:35,920 --> 00:28:44,200
have these bullet points of things that you get you went to a site to first to

182
00:28:44,200 --> 00:28:53,720
make the request where was that this site or is this the other side you're

183
00:28:53,720 --> 00:28:56,320
referring to

184
00:28:56,320 --> 00:29:11,200
your screen yeah where you came with see LCB records make public requests you

185
00:29:11,200 --> 00:29:18,080
copy that link to the chat yeah sure thing

186
00:29:18,080 --> 00:29:27,360
so you come down in the public make public request

187
00:29:32,760 --> 00:29:42,280
then it gives you a document you got to fill out and email it is I believe you

188
00:29:42,280 --> 00:29:50,440
can email it in so you know so like I said it depends on what you it depends

189
00:29:50,440 --> 00:29:54,800
on what you're looking at like I said a lot of my interest has just been in lab

190
00:29:54,800 --> 00:30:00,960
results and so I know everybody has their own interest and in fact you know

191
00:30:00,960 --> 00:30:08,760
I'm sure most of the interest is probably in sales right because I mean

192
00:30:08,760 --> 00:30:12,440
that's I've heard that's where the actions at rate that's where the consumers

193
00:30:12,440 --> 00:30:22,240
are but I don't know what how about can I just ask the crowd like what sort of

194
00:30:22,240 --> 00:30:27,320
data points what sort of data points are you interested in or what statistics are

195
00:30:27,320 --> 00:30:32,600
you interested in looking at by chance well we would be we'd be actually

196
00:30:32,600 --> 00:30:39,200
extracting for trying to find all of the people that are in the business to be able to

197
00:30:39,200 --> 00:30:49,480
you know we're we're trying to design an add-in for acrobatica's ERP that will

198
00:30:49,480 --> 00:31:02,680
interact with the state or API as it does the growing and does the harvesting

199
00:31:02,680 --> 00:31:09,640
as sort of manufacturing processes okay well why don't just now here on the fly

200
00:31:09,640 --> 00:31:17,240
why don't we see what we can do here so they've got these licensees so we'll

201
00:31:17,240 --> 00:31:23,160
just walk through the you know this is how the sausage gets made so you so you

202
00:31:23,160 --> 00:31:34,280
download this we're gonna do it to the cannabis data science group there it is

203
00:31:34,280 --> 00:31:39,960
and the next question I have for both of you do you know of anybody that is

204
00:31:39,960 --> 00:31:49,240
familiar in dotnet with using the API to call for ID values right I mean that's

205
00:31:49,240 --> 00:31:54,960
the biggest thing of accounting systems trying to work with the state board is

206
00:31:54,960 --> 00:32:02,600
every ID having to be assigned by the API for vendor IDs and you know

207
00:32:02,600 --> 00:32:09,560
you're actually speaking to somebody so that's I I'm actually quite quite

208
00:32:09,560 --> 00:32:15,080
familiar with working with the state API so I've my background is in a testing

209
00:32:15,080 --> 00:32:20,640
laboratory so I so I've integrated with the state and then my company's in the

210
00:32:20,640 --> 00:32:26,720
process of getting our integrator status at the moment so if if if you need any

211
00:32:26,720 --> 00:32:35,240
any help I can we're gonna start we're gonna start small with like a hello

212
00:32:35,240 --> 00:32:41,040
world type of thing just trying to get like a vendor ID assigned by the state

213
00:32:41,040 --> 00:32:47,760
rather than being assigned through the ERP software so we can take that off if

214
00:32:47,760 --> 00:32:52,640
you can you know email me your contact information we can set up another time

215
00:32:52,640 --> 00:32:58,320
to chat and it's not gonna be right away we're finishing up another product first

216
00:32:58,320 --> 00:33:03,640
but I've got a full-time dotnet developer and one of the cool things

217
00:33:03,640 --> 00:33:08,520
about this accounting software is you can customize right within the cloud app

218
00:33:08,520 --> 00:33:16,320
in dotnet so so anything you can do in dotnet you can create screens or

219
00:33:16,320 --> 00:33:23,280
processing screens or change the way that the code works in the system so

220
00:33:23,280 --> 00:33:32,660
everything from purchasing sales you know manufacturing distribution any of

221
00:33:32,660 --> 00:33:37,280
those things where it has to interact with the API we're gonna have to write

222
00:33:37,280 --> 00:33:44,360
code to do that and I wanted you know once we once we kind of learn the API

223
00:33:44,360 --> 00:33:49,320
you know just I think one time if you do one time interacting with it then after

224
00:33:49,320 --> 00:33:54,000
that you know my developer probably be pretty good at it definitely but I'd be

225
00:33:54,000 --> 00:34:00,240
happy to just give you a crash course tell you the nuances show you how to hit

226
00:34:00,240 --> 00:34:11,120
the endpoints but yeah I yeah definitely so I can help you with that and you know

227
00:34:11,120 --> 00:34:17,000
like I said that's what my background's in and like I said you know a lot of

228
00:34:17,000 --> 00:34:24,880
this can be automated but for now let's just grab this licensees file so just

229
00:34:24,880 --> 00:34:27,600
to show you this real quick and then we can talk more about the state

230
00:34:27,600 --> 00:34:34,160
traceability system but I'm just going to do a similar thing here as I did with

231
00:34:34,160 --> 00:34:50,480
the lab results so just substituting the file name and then it's going to be in

232
00:34:50,480 --> 00:35:02,600
this folder so I'm going to add this folder directory here so just open a new

233
00:35:02,600 --> 00:35:15,720
console and let's let it rip and see what we get okay so 28 so you've got

234
00:35:15,720 --> 00:35:22,120
almost 3,000 licensees and same thing so this is how the sausage gets made the

235
00:35:22,120 --> 00:35:30,920
first thing I do is I look at the first observation and maybe let's look at the

236
00:35:30,920 --> 00:35:41,000
last observation so I so yeah so so this first one looks like almost like a like

237
00:35:41,000 --> 00:35:47,880
a test that that looks like actually Washington State but this last

238
00:35:47,880 --> 00:35:55,120
observation here it actually looks like it's it there they may be suspended but

239
00:35:55,120 --> 00:36:03,400
oh yeah but it looks like I thought yeah so it's a dispensary in I don't know I

240
00:36:03,400 --> 00:36:09,600
would like I said I would maybe double check on this I mean it may be worth

241
00:36:09,600 --> 00:36:15,840
giving them a call just to make sure that's accurately coded

242
00:36:23,840 --> 00:36:29,280
yeah so and so actually what you could do here is you could almost say okay

243
00:36:29,280 --> 00:36:40,680
let's just do data suspended okay so okay so now we can do a little on-the-fly

244
00:36:40,680 --> 00:36:46,200
data analytics so let's see how many of these are suspended right so you could

245
00:36:46,200 --> 00:37:01,520
do okay so you could do okay so data dot loc data dot suspended equals true and

246
00:37:01,520 --> 00:37:08,880
then you can just divide that by length of data so it looks like yes so it

247
00:37:08,880 --> 00:37:15,080
looks like like 35% of these licenses are suspended so it could so it could be

248
00:37:15,080 --> 00:37:27,920
that people aren't renewing their licenses or or something like that so

249
00:37:27,920 --> 00:37:34,280
that's where the the data crunching becomes quite interesting um but so you

250
00:37:34,280 --> 00:37:41,440
know so let's say you're looking for you know active licenses well you know

251
00:37:41,440 --> 00:37:52,160
you're gonna be looking for you know people where the suspended's false for

252
00:37:52,160 --> 00:38:09,120
starters so so there's 1871 non suspended but I believe I wonder if the

253
00:38:09,120 --> 00:38:23,480
let's find out all the the fields again oh and then so there's also an is live

254
00:38:23,480 --> 00:38:38,440
and an expired at so let's take a look at is live and then let's see what

255
00:38:38,440 --> 00:38:41,840
expired at looks like

256
00:38:45,080 --> 00:38:53,440
interesting so for your active licenses you know you may want to change this to

257
00:38:53,440 --> 00:38:57,800
a conditional so you want me want to say okay we want everyone that's suspended

258
00:38:57,800 --> 00:39:11,680
false and you know data thought is live is true

259
00:39:11,680 --> 00:39:26,760
some reason it's not crazy about this

260
00:39:26,760 --> 00:39:45,520
there we go so it's a look you know and then you could do a further sanity check

261
00:39:45,520 --> 00:39:51,480
in you know cut off all the ones that aren't expired but you know this is it

262
00:39:51,480 --> 00:40:08,920
you know probably a pretty good list for you here and then you know you could you

263
00:40:08,920 --> 00:40:14,320
know and so what you could do is you know you could actually you know let's

264
00:40:14,320 --> 00:40:20,240
say you want to save this to excel so you can now say okay active licenses

265
00:40:20,240 --> 00:40:39,560
new to excel and you know there's probably some more settings you can

266
00:40:39,560 --> 00:40:52,920
toggle to make it look a little nicer but there you go so you didn't do much

267
00:41:09,560 --> 00:41:16,680
exactly incident so then you know then you could even pass this to your your

268
00:41:16,680 --> 00:41:27,360
marketing your marketing agent and then say okay yeah okay here's a you know you

269
00:41:27,360 --> 00:41:31,720
could clean this up through them a little bit but you can basically say okay

270
00:41:31,720 --> 00:41:40,520
you know here's their phone yeah here here's your yeah here I do think these

271
00:41:40,520 --> 00:41:46,520
phone numbers just for your warning I think maybe not all of them but I think

272
00:41:46,520 --> 00:41:50,640
you know some of these you know because their number is listed publicly they may

273
00:41:50,640 --> 00:41:56,360
get a lot of contacts so a lot of them may have different numbers for their

274
00:41:56,360 --> 00:42:04,160
primary lines but we don't actually market via phone yeah it's not a real

275
00:42:04,160 --> 00:42:10,400
effective method we've got kind of a we've developed a process where we can

276
00:42:10,400 --> 00:42:21,120
identify the people and then they're either using an AI site like really high

277
00:42:21,120 --> 00:42:27,120
percentage probability of getting the good right email address we we do some

278
00:42:27,120 --> 00:42:31,080
email marketing that way and then the other thing we do is we invite them to

279
00:42:31,080 --> 00:42:36,080
connect on LinkedIn and then they kind of see our marketing social media posts

280
00:42:36,080 --> 00:42:42,760
on LinkedIn or business marketing LinkedIn is seems to be the most helpful

281
00:42:42,760 --> 00:42:47,600
yeah well you kind of hit the nail on the head and you may be interested in

282
00:42:47,600 --> 00:42:53,360
joining there I attend this startup group on Tuesdays and one of the topics

283
00:42:53,360 --> 00:42:59,800
they talk about is customer development automation and yes you hit the nail on

284
00:42:59,800 --> 00:43:04,440
the head it's they their strategies it seems to be connect with people on

285
00:43:04,440 --> 00:43:09,880
LinkedIn and I and they may enjoy that better themselves because like I said I

286
00:43:09,880 --> 00:43:16,720
don't think they love getting phone calls all day and in fact like I said I'm

287
00:43:16,720 --> 00:43:22,680
sure some of these numbers are active but the the large cultivators they are

288
00:43:22,680 --> 00:43:28,520
they were you know we're getting so many phone calls that they just they just got

289
00:43:28,520 --> 00:43:36,200
a new number almost but it's still you know it's still just good to know okay

290
00:43:36,200 --> 00:43:41,880
what does the industry look like and you know you can you know you've got the

291
00:43:41,880 --> 00:43:47,280
power of Python here or whatever your programming tool is and so you

292
00:43:47,280 --> 00:43:54,840
Python guy I grew up on the Microsoft side so I've been a sequel guy okay

293
00:43:54,840 --> 00:44:04,280
we because the software we sell is dotnet based we really focus hard to have

294
00:44:04,280 --> 00:44:10,560
stuff on that so but but you know it's the same idea right getting access to

295
00:44:10,560 --> 00:44:15,680
data like this and then in building queries and store procedures to slice

296
00:44:15,680 --> 00:44:22,160
and dice the data or use something like a report builder for an SSRS report or

297
00:44:22,160 --> 00:44:28,800
something of that nature even power BI right gives you a lot of similar type of

298
00:44:28,800 --> 00:44:34,160
capability to slice and dice the data exactly it's just exactly it's just sort

299
00:44:34,160 --> 00:44:39,660
of tools of the trade and then like you said it's just how you want to to rig up

300
00:44:39,660 --> 00:44:44,280
your system and so I really enjoyed this because I've never seen I've never

301
00:44:44,280 --> 00:44:48,920
really seen anybody do what they were doing well well it was kind of it was

302
00:44:48,920 --> 00:44:52,920
cool to kind of see okay that's kind of similar to sequel and I was kind of

303
00:44:52,920 --> 00:44:56,640
seeing what you're doing like oh yeah that's just a different language for

304
00:44:56,640 --> 00:45:03,560
doing essentially the same thing and and this is sort of bare-bones like you can

305
00:45:03,560 --> 00:45:10,240
you know you can use VS code and get better better highlighting and you know

306
00:45:10,240 --> 00:45:17,360
you can run this over here in the terminal but I just sort of learned on

307
00:45:17,360 --> 00:45:25,000
spider and I find it just quite simple because I can write scripts and run

308
00:45:25,000 --> 00:45:31,800
right here in the console but I'm just I'm all about practicality just getting

309
00:45:31,800 --> 00:45:42,240
things done so this was just a quick fast way to so but you know to get what

310
00:45:42,240 --> 00:45:48,600
you wanted so basically we because you know you you doubt you download this

311
00:45:48,600 --> 00:45:57,840
ugly file and you're gonna have to do something with it so you know you have

312
00:45:57,840 --> 00:46:04,320
to parse this one way or another right get the data out of it and then dump it

313
00:46:04,320 --> 00:46:11,960
to to an Excel and then yeah and then this can then go in your database and

314
00:46:11,960 --> 00:46:30,920
you can have at it but but that's sort of round but that I guess that sort of

315
00:46:30,920 --> 00:46:41,320
I you know rounds it out so yeah it was fascinating hearing what you what you do

316
00:46:41,320 --> 00:46:46,760
and I was just wondering you know for you know for next week is there anything

317
00:46:46,760 --> 00:46:58,000
like anything else you'd like to see or I know this is more about analyzing the

318
00:46:58,000 --> 00:47:04,240
data so I wouldn't want to take it really off course too much but but yeah

319
00:47:04,240 --> 00:47:11,240
I'm just as far as the cannabis industry I've been trying to find a way to break

320
00:47:11,240 --> 00:47:16,960
in to that industry with the software that I sell and be able to create and

321
00:47:16,960 --> 00:47:23,720
add in for that software to make it work with the state API and there are some

322
00:47:23,720 --> 00:47:30,320
other competitors out there but you know I think competition is good I don't

323
00:47:30,320 --> 00:47:37,520
think that they have particularly good products yet so we're looking to I've got

324
00:47:37,520 --> 00:47:44,160
experience in growing but it's growing shellfish I wrote for Taylor shellfish

325
00:47:44,160 --> 00:47:52,280
over at Shelton back in 97 when active server pages were the rage we wrote and

326
00:47:52,280 --> 00:47:58,480
a complete growing app for growing from seed to the final pulling out of the

327
00:47:58,480 --> 00:48:05,160
beach clams and oysters and other types of shellfish and it allowed them to to

328
00:48:05,160 --> 00:48:12,720
grow pretty significantly over time over the last 20 years but they they used it

329
00:48:12,720 --> 00:48:19,400
for many many years so that their staff knew what they had in the in the beaches

330
00:48:19,400 --> 00:48:26,600
they they had measurements done on their on their phones with the web apps to

331
00:48:26,600 --> 00:48:33,440
update the size of the shellfish as it was growing you know to take the sales

332
00:48:33,440 --> 00:48:37,320
orders and the guys would go out on the boats with their phones they'll look and

333
00:48:37,320 --> 00:48:41,760
see what the sales orders from the prior day were that need to be fulfilled that

334
00:48:41,760 --> 00:48:47,520
day so there's lots you can do with technology I became kind of a geek after

335
00:48:47,520 --> 00:48:54,000
being a CPA accounting nerd I became a geek with technology and active server

336
00:48:54,000 --> 00:48:58,260
pages really kind of opened my eyes to what you can do with databases and web

337
00:48:58,260 --> 00:49:06,160
pages and it's it's come a long way from there and I've grown a company that does

338
00:49:06,160 --> 00:49:12,240
you know ERP consulting and sales with Acumatica which is if you haven't heard

339
00:49:12,240 --> 00:49:18,800
of it there they're like ten years beyond net suite in terms of platform

340
00:49:18,800 --> 00:49:24,440
and technology they you know that suite was started in 1999 that came out about

341
00:49:24,440 --> 00:49:35,440
2009 but it's all dotnet from the ground up knowledge tml5 from the beginning all

342
00:49:35,440 --> 00:49:44,480
open API so web services both rest or so boarded it is it is amazing software

343
00:49:44,480 --> 00:49:49,440
because it was originally started as a rapid development platform for dotnet

344
00:49:49,440 --> 00:49:55,680
websites and then well we've got a background from our ERP days they were

345
00:49:55,680 --> 00:50:02,600
owners or managers of Solomon this project so they may said oh let's throw

346
00:50:02,600 --> 00:50:07,720
on an ERP platform on top of that and then let people program whatever else

347
00:50:07,720 --> 00:50:14,400
they need in dotnet and they let you view the source code so whenever you're

348
00:50:14,400 --> 00:50:17,520
doing development if you can see the source code if you're developing

349
00:50:17,520 --> 00:50:24,080
enhancements it just makes everything easier right yeah and because of their

350
00:50:24,080 --> 00:50:27,920
development environment you can actually be coding C sharp right within their

351
00:50:27,920 --> 00:50:33,480
application and they have some tools where you can essentially create a table

352
00:50:33,480 --> 00:50:38,560
in the database and then you go in the program you say okay create the class

353
00:50:38,560 --> 00:50:43,480
object that represents that database table and it'll write the code

354
00:50:43,480 --> 00:50:49,120
essentially for you to create that class object and then you can say create me a

355
00:50:49,120 --> 00:50:54,400
screen for that class object and it'll write the code to create the screen for

356
00:50:54,400 --> 00:50:58,840
that right and then you can tweak it after that so it's one of those rapid

357
00:50:58,840 --> 00:51:05,480
development environments for business software applications that makes makes

358
00:51:05,480 --> 00:51:11,120
creating a full solution in a single database with everything from

359
00:51:11,120 --> 00:51:15,440
manufacturing to financials to distribution warehouse management

360
00:51:15,440 --> 00:51:20,160
project accounting recurring billing equipment maintenance everything a

361
00:51:20,160 --> 00:51:26,280
business does or could do in a single application for multiple companies

362
00:51:26,280 --> 00:51:32,800
levels of consolidation of you know three four levels deep parent to child

363
00:51:32,800 --> 00:51:39,280
to child to branch so as you can tell I'm kind of an evangelist for the software

364
00:51:39,280 --> 00:51:45,720
but it was the I found it in 2012 and it's really the most advanced business

365
00:51:45,720 --> 00:51:50,120
software application out there I mean they're really on cutting edge I mean

366
00:51:50,120 --> 00:51:53,560
they've got a they're like one of the first with a business skill for Alexa

367
00:51:53,560 --> 00:52:01,160
right so I literally I can put on my Alexa glasses right and I can I can talk

368
00:52:01,160 --> 00:52:06,960
to the accounting system and say what the inventory stock status is of item X

369
00:52:06,960 --> 00:52:14,680
right you know it's kind of like Star Trek computer type functionality right

370
00:52:14,680 --> 00:52:20,120
so for me imagine a world where we are now interacting with our business

371
00:52:20,120 --> 00:52:29,480
software applications verbally through Alexa and giving commands to the system

372
00:52:29,480 --> 00:52:35,800
imagine being on a warehouse floor and being able to check stock status or to

373
00:52:35,800 --> 00:52:40,440
and I think where they're going I'm actually been making suggestions on

374
00:52:40,440 --> 00:52:44,760
improvements but not only being able to ask for information but what if you

375
00:52:44,760 --> 00:52:53,440
could say create a purchase order for vendor X for item Y right or do a step

376
00:52:53,440 --> 00:52:58,720
in a manufacturing process saying you know complete step a of this particular

377
00:52:58,720 --> 00:53:04,800
manufacturing job all these things are going to be possible for you to

378
00:53:04,800 --> 00:53:08,480
interact in the future with your accounting system your business software

379
00:53:08,480 --> 00:53:16,040
systems verbally hands-free you know they're doing development now with OCR

380
00:53:16,040 --> 00:53:21,760
so like AP documents that get emailed into the system they can monitor the

381
00:53:21,760 --> 00:53:27,880
email queues and convert an AP invoice PDF form into a bill in the accounting

382
00:53:27,880 --> 00:53:33,880
system without anyone having to enter it right expense reports right so if you're

383
00:53:33,880 --> 00:53:39,640
out traveling to a job or say you're an appointment you know service appointment

384
00:53:39,640 --> 00:53:44,280
and you take a picture of the work that you did and attach it to the appointment

385
00:53:44,280 --> 00:53:49,960
or if you take a expense receipt and you take a picture of it and it creates the

386
00:53:49,960 --> 00:53:56,240
expense record in your accounting system for reimbursement all these things are

387
00:53:56,240 --> 00:54:05,360
things they've already developed and I mean so it's as a geek I look at technology

388
00:54:05,360 --> 00:54:12,480
that they're coming up with. I'd like how you think David and so I was thinking just from what

389
00:54:12,480 --> 00:54:18,680
you've mentioned do you think for next week a good topic may be API's? Yeah that's a

390
00:54:18,680 --> 00:54:22,240
great idea. Because that was something I was wanting to hit on and that's

391
00:54:22,240 --> 00:54:29,120
something that is programming language agnostic so you can have any programming

392
00:54:29,120 --> 00:54:33,160
language you want like and we can just we can just go through some endpoints

393
00:54:33,160 --> 00:54:40,080
and just walk through them hit some endpoints get some data and that way

394
00:54:40,080 --> 00:54:50,960
we're all talking the same language. I'll have my developer join us too. He's down in California now. It's amazing with this COVID right?

395
00:54:50,960 --> 00:55:04,200
We operate remotely. I mean we use Acumatica internally as well so he's just working from down in California. He says hey I'm moving to California. I say okay cool. Enjoy yourself. Awesome.

396
00:55:04,200 --> 00:55:14,200
I'm going to go get a license or what I have to get an employer ID from the state of California to get some tax raise but other than that you know gusto. More than marriage.

397
00:55:14,200 --> 00:55:26,200
But definitely so same same time next week and and we can we can talk about API's and we can hit some endpoints.

398
00:55:26,200 --> 00:55:36,200
Send me the information or link information and does the meetup site send out an email or do we have to find one?

399
00:55:36,200 --> 00:55:43,200
I'm going to go ahead and create a new event and I'll get you sent out a new invite so I'm still sort of learning so sorry about the

400
00:55:43,200 --> 00:55:55,200
slow start today. If I can answer any questions for you on anything. My depth is maybe not as deep. My knowledge is not as deep as everyone else.

401
00:55:55,200 --> 00:56:08,200
But I have a lot of breadth of knowledge. I wouldn't say. I can tell you that I have an engineering background nuclear engineering background for my Navy days as a submarine officer.

402
00:56:08,200 --> 00:56:22,200
I'm a family of accountants so when I got out I ended up becoming a CPA. I'm doing payroll and accounting and I'm one of the people that walked away from Amazon before I invested in my stock options back in the 90s.

403
00:56:22,200 --> 00:56:34,200
So if you ever want to feel bad. I can tell you how much money I walked away from pretty much every day. I know how many hundred million.

404
00:56:34,200 --> 00:56:44,200
Don't be hard on yourself, but I've learned a lot from you and this is what this group is about. So I'm here to share what I know and then we can share. Just keep sharing.

405
00:56:44,200 --> 00:56:54,200
Perfect. I love it. Awesome. Well, alright guys. I gotta run. I'm late for my next meeting but this was great and I look forward to next week.

406
00:56:54,200 --> 00:57:09,200
Alright until next time. Bye Charles.