Believe it's made right or back then it was integra and so we did so for the product which i will show you in a bit uh we basically had to only code the core features like you know the that thing which is generating the value for the users but all the stuff like uh license management um fail notifications when the product doesn't work uh purchasing invoicing you know reporting all that we build that on may so we basically.
Minimize all the costs right so it's hard to say right now that running that product monthly cost me like 15 and i'm not really kidding like it it's as low as that so let's take a sneak peek at the product what it is i'm not gonna go into details because it's it really doesn't matter like you are here for the scenarios and for the workflow right but i still kind of need to show you what it does so uh let's say you are working in google sheets and you want to pass this.
Sample data to bigquery which is like online database or online warehouse from from google where you can where you can run sql and you can run droids and updates etc and you know we saw a gap on the market and there was not really an efficient way to do it from google sheets so we were like hey let's just build it ourselves because we are using this process over and over and we just made a product for that and made it public and so when you are as a user uh in google sheets and you want to push that.
Data to bigquery you will likely go to extensions and you will look for add-ons and then you will get to you will get to the app store and then you will likely start looking for bigquery and here is our product right actually we have two products you know maybe i should say that too so we have uploader which basically allows you to upload the data from to bigquery and then we have another product which is newer which was released this year which is downloading data from bigquery to google sheets so the circle is complete.
The software or the add-on has uh two modes right you are either valid user or not quality user because you are you have to pay us or you are in trial to be valid or you're expired and you cannot use the product and we need to somehow manage this licensing problem right like who is valid who can use the product and who cannot so the moment you start uh interacting with the product for the first time we send an event with your id and with your.
Email to our license server and you know we check whether you are existing user if you are a new user etc and you know i'm saying license server but it's it's super simple like it's a it's a google sheet and i'm gonna show you how it looks so whenever whenever we see a new user we basically take the user and add it to this spreadsheet obviously it's anonymized.
For for the purposes of the webinar but you know normally you will see all emails here or ids etc and this is where we store um the id email and then 13 day trial expiration date you know license started etc and this is basically our core uh for deciding whether you are okay user or not and how we communicated the product and i will show you a scenario which is dealing with this so for example if i start as a new user you know i will i will have okay trial.
Status here and then you know we pass this trial this status back to back to the product so the product allows you to be actually used and if you are on the other hand expired trial or license you know the product will show you a fail message and it will tell you that you should you should get uh a paid license and so all the scenarios i'll be talking about are not that fancy you know they're pretty straightforward but when you combine them all together.
Uh you are basically minimizing all the manual work so when we look at that licensing scenario there are basically two things which can happen so let's let's load it up and see what's happening there i can actually open up a sample execution here and all i'm showing is like production stuff like as it runs this is it like there are no edits this is this is real stuff and basically here uh we have this big scenario but this upper route is.
Posts Related:
Basically enacted for some time so we don't have to worry about it but when you start interacting with the product or when you try to upload the - Make Automate
Data we sent a webhook payload to make url right so it's custom revoked module where we receive the data and there's some action you know this one was for example status check so it's an existing user and then you know with make we figure out whether steve actually has a valid license or not uh and as i speak you know we do this we.This one scenario is covering it for two products so i know because i know this stuff i know that uh there is no product name here but if it was about product downloader i would have the product name here since it's missing here i know it's uploader and we edit the downloader product later as i already mentioned so this one i know it's for downloader uh so i go in my spreadsheet you know and i check that email and i try to list this in in the list.
Or i search for that email in the list of users here to retrieve the calculated status in this calling right and then the user is either expired or he's okay if it's an existing user so this is what's happening in this second route right it's it's very simple you know you look up the status and then we get the status and reply back to the product and say you know okay user or not okay user by sending basically the chart expiration the date which we compared in the product.
Against the current date right so this user doesn't have a license and he was expired last year so he's not valid and the product will not allow him to be used and that's that's for existing users right but if there is a new user we just installed a product you know this module this search will return zero rows because you know the email is not on the list so we will go and create create a user in that spreadsheet so basically one user means one row.
And we will you know add his first team date historic expiration date his license start date if he's paid but most likely not they always start his trial and then they eventually convert if they decide to buy the product and then based on these dates you know we calculate these statuses but at the end of the day the calculation is simple it's just array formula running you know in the first row and it's inherited into all the rows in the spreadsheet and it's returning the status is based on calculating the dates.
In all these columns so you know this is how we handle the licensing and decision process behind uh behind user management and there's one more thing when the user is new we also want to send them welcome instructions because those products are kind of specific and you know there is stuff you should know before you actually start playing with it otherwise it is going to fail or you are going to fail during your first upload or download so we sent a.
Welcome email and again it's done with make so when i go to the diagram you know we have one vocab email which is specific to the to the uploader product and one which is specific to the downloader so when i open up the email you know it's basically a static string with some html text but at the end of the day the email is very simple and i can show you a sample here how it looks.
A few seconds and you know the moment you install the product you are getting this this email this subject and you know if you get read this before you will probably read it so the open rate is probably pretty good and we explain you what you should do here uh what you should read you know before you are to start actually using the product and again you know it's all done through make we don't really need any fancy emailing solutions we decided to go with with zoho as you.
Could sell here because we simply did not want to pay for any microsoft paid accounts which would allow us to run those emails from custom domain or for g suite account so we just wanted zoho which allows you to do it for free with some limitations but it's livable so this is how we you know handle new users and how we handle existing users and how we you know are passing the statuses back to the product so the product knows.
Whether the user should be allowed to use it or not so
We basically saw the first point here then when the users are actually using the product you know they can run into failures like uh you are uploading um you are uploading this simple table and you you think that this table still exists in bigquery but there is a colleague who deleted a table and the upload will result in failure and you should know that right because you want.To have your data in order and for that you know we introduced a feature which allows you to subscribe to failure emails kind of like you know when you run scenario in make and it fails you get an email which is fancy looking in our case it's kind of ugly but it does the job and so in our case when you are setting up upload schedule you know you can opt in for failure emails and you know one would think that again it's some kind of like fancy solution but at the end of the day it's super simple.
There is a scenario listening for failure web hooks so basically the product knows when it fails right and then when this happens uh the failure is pushed to make again oops i did not mean to open this one i meant this one so the trailer is pushed to a webhook url from may and then we take that information along with some metadata and we push it to the user and we send it to the user via email so when we look at sample payload.
You know here we have a user [Music] which failed this was the fail message and this was the sheet id and we basically know whether it was uploaded or downloader if it's a folder you know as in this case you would go and send out this email which is fairly simple it's uh it's again strings with few html tags and the user is notified that pharaoh happened and he should do something about it if you want to look at the failure email.
Example how it looks in your gmail this is it so you know we just say hey oh no your upload too big when it failed you know if it was download the other product you would say oh no your download has failed but then at the end of the day this message will be pretty much the same you just swap you know upload for download and that's it so you know that's how we keep users in the know and again it's all just based on map books and nothing too complicated but.
You know it has great value for the users because they know when something fails and they know that they should go and look at it okay so let's now talk about expiration mode so as this every software product you know there is likely some travel period license period and you you want to know your you you want to let your new users know when they are trial expiring when their license is expiring when the trial is actually expired and.
When the license is expired and again it's all kind of managed through our fake license server which is this uh this google sheet so not only we store the dates for trials and licenses we also store various timestamps for uh when we send which emails right so when we for example send welcome instruction to the new user which i showed you previously we send a timestamp here uh you know when we start expiring.
We we save it here expired here license expiring here and license expire here how do we know when to send the emails well it's simple so here we have uh a column which is calculating again a very simple formula how many days to trial expiration the given user has and if the number is five that's a threshold for us and every night you know we will go and check this volume for number five and all the users which have number five here will get an email saying hey your trial is about to expire maybe you should consider buying a.
License and the same we do for license expiration right so if he has paid us in the past but his license is nearing uh expiration then five days before the expiration we will send in an email and that scenario which is responsible for managing this is again kind of simple so we do again run this for the bose products right so we we run this for uploader and then we will run this for.
Downloader and the downloader sheet has the exactly same column set so we can just iterate through the sheets like uh like in this scenario so i have a simple configuration variables here like spreadsheet id and then sheets array with sheet names and then i simply go and iterate through sheet names i also decide whether the product is downloader or uploader i set official product names and official like.
Actions whether it's you know the word the verb the action verb is like download or upload and then i go and compile emails dynamically so i send an email as i said for trials which are about to expire soon for trials which just expired and same for licenses so when we open up this this email we can see that you know i'm dynamically replacing product name here here here the action word so whether it's a download or upload but at the end of the.
Day you know the email is exactly the same for the two products and we just replace a few variables uh based on the iterations which which based on the iteration which is currently running so when we check out a real email and how it looks you know it looks again pretty okay no fancy images just text and it does the job.
So hey you know your child will expire in five days and this is dynamically inserted based on the product the user is using same here you know and this verb is also dynamically uh replaced again based on the product if this was about downloader it would be you know bigquery downloader and this would say download okay so now we are kind of halfway through so you.
Know we solve on boarding licensing some favorite emails when problems arise and exploration basically the the main part of communication of the product is now done and as i said you know it it's costing us pretty much zero all is based on networks free emailing provider you know and google sheet so maybe we can now uh let you ask your questions if you have any.
Because i don't want to be talking like for 60 minutes straight and you you could uh forget your questions if you have any if you don't then we just move forward i check it here on the chat seems everything fine so guys this is the moment if you have any question um there is one how to how do you handle oath okay so the product itself needs to go through certification with google.
So this is not done in make and you know when you when you publish an i don't like this you kind of do it through cloud console and then you ask google to review your product you know you have to record a video and then finally after many many attempts and many rejections from google you will succeed and they will grant you the old screen etc and public will be able to use the product but this is really happening you know in the google environment and there is.
Nothing nothing mate can do there oh for i mean you don't have to like there is i mean that that the book is unique so we are just bringing you straight from the product and there is no uh no sort of like authentication going on because it's only us who knows the exact url and you know even if the url leaked we.
Can just replace it like in five minutes and everything would be fine so there is nothing too complicated behind that part and we've been there like we had to replace those webhooks so you know you go to mac and replace the webhook in five scenarios and if you go okay how often the license check happened so of course this was our first question right how many operations are we gonna burn per month by doing these things like these.
Never-ending things and in our case it's it's uh it's burning about 20 000 operations per month because we don't have that many active users yes we on board users but the trial expires and then they don't pay right so there is no more peaks when you go to the web store you will for example see that the uploader product is like 100 000 users but basically that's a lie because whenever you add the product into your organization google will count all users in your organization but.
Nobody's actually using it except one person so the numbers you see in that store are invalid basically and in our case you know we were worried and you know we are averaging about 20k per month if we had more users it would be fine because we would likely be making more money and we would you know we would just afford to pay more for may so that would be like this basically perfectly proportional relationship to the usage to sales and.
To to the cost generated in may so er it's not really an issue uh if it was an issue we could still probably edit the product so it only checks for the license validity every day and not during every upload for example so there is some things you can tweak on the product side all right any more questions if not then i guess we can move on so now i think that that's gonna be the.
Coolest scenario and uh it's about license purchase right because people want to get paid that's the d-day for us when somebody when finally somebody pays us and uh when people pay you they kind of expect to get an invoice right because they have to give it to their finance so everyone is happy so in the product you know the way how.
It works is that currently i have a license so i'm not expired so i have like no messaging here but i can still buy another license if i wanted to so if i go to license status menu there is a buy button if it loads yeah and when i click it i'll be taking to our site and there is some explanation like if you want to buy multiple then contact us directly and we will give you some.
Discount but it doesn't matter most of the time users will either buy one user one year or one user two years right and when they click when they make a decision they click they are taken to stripe landing page so let's go there so this is no longer hosted on our site it's basically stripe url you can generate it in strive for free it's called payment links so you just set up a product price and it will generate a url for you and then.
You take that url and you place it to your website and of course you know people come here they fill it out and they pay and since we are in make we all i just know that we are supporting stripe integration so we can listen for stripe events you know and take actions based on those events so when i open my scenario for purchases this is what's happening.
So at first you know i get a notification about about the payment i basically sent a simple email to myself saying hey there is something happening it's right because the payment can fail and maybe you want to reach out to your customers if the payment is failing and help them out or offer a different payment method like paypal or something then i generate the invoice and then you know i need to update my licensing sheet i need to find that user and assign him is a license which means uh i need to you know update these.
These license related volumes here and also i want to keep track of all my sales so i will just add a line item to my to my uh google sheet so let's let's look at it in more details so whenever payment comes from stripe you can basically subscribe to various uh stripe events and one of them is obviously payments uh then as i said in my send you know like simple email to myself and i.
Basically said hey this i say this customer this email is trying to pay and make sure the payment goes through so you know then when the payment goes through i get billing details of the customer for the invoice i get line items of the payment link by making custom api calls and then i'm starting setting important variables like invoice number which is basically now function formatted to this format so again nothing too fancy.
Then i get customer email from stripe customer name from stride license star date right so that's today's date because the customer paid today the period is always year duration it depends on the payment link so either it's one years or two years but it comes from stripe because that product you know i basically sold two products one is one year one user and the other is one user two years then obviously price uh you need to divide your price by 100 because the price from the api is multiplied by 100.
