With another updated application there and i also provide pdf downloads that allow you to track the code of these applications and of course you are also welcome to download the entire training the video training and of course we have lots of resources such as the icons and pictures and backgrounds that go along with these training so i would appreciate your support there i'll include the link down below that is excel for freelancers patreon platform all right let's get started on this training this week i've got a really cool training because we're going to be able to automatically download ratings on a click of a button we're going to be able to add any.
Type of business that we want so we've got some business ids we're going to be able to we're going to start with yelp but eventually you can add additional platforms on here to download reviews so i've got places for that we've got a database so it's a relatively simple particular application with not a whole lot of code so this is not going to be a very long training because it's relatively straightforward we do have some very very cool filtering techniques that we're going to be used and i'm going to show you how to do that so we've got a very compact filter we'll be able to show replies whether the replies are replied both or just not replied right both of that we're going to be have we also have the ability to reply after if we want to reply after a certain date so we can.
Put in a certain date and only those reply it's only those reviews that are after that date also we can also show ratings above if we have uh two four stars or we only want to show ratings that are above four star or five star we can do that here as well so we can show you that so we've got a lot of features on that and of course we can change the business out of it so there's a lot of opportunity here that business of course has only five stars this one only has four so if we want to show all the stars or ratings below maybe we want to show only ratings below a certain star we.
Can do that too so we've got a dynamic filter that we're going to be able to show here so it's going to be a really great training on that we're going to be able to select different businesses we'll be able to track all of that so all we need to do in fact we're going to start out with yelp all we need to do get the business information is just to do that so it's very very easy to add a business so we're going to show you that right now so what we're going to do is i'm going to go into yelp yelp is one of the best particular designs for websites for this type of reviews right now lots of others have it we've got facebook and you've got a few trustpilot and a few others that also do it but we'll just try this so let's take this new york pizza right this looks like a pretty good.
So if i go ahead and click down here it's going to show those reviews of course we want to show any star so let's just go ahead and click show replies and then i'll just do both so that way it's gonna have so now we see we've got one five star one two star and one four star so automatically those reviews got downloaded we've got the links if we want to go to the review i can go directly to that review and click on that review and it's going to show up here so we can see the review here and it's going to scroll down down to the reviews section there and we can see those reviews okay so it automatically will open up that particular review so it's a great way and.
If we want to reply we can just reply very much at least we can track the replies here eventually we'll be able to upload it but this will at least allow us to track the replies once we reply here we would also do that on the website this is simply a way to track the reply that we've already replied so we would reply directly on the website and then just copy and paste that here that way we know we've replied it and of course if we filter that again we see that there's a check mark and that means that has been replied so that's a great great great way to track that we can get reviews from additional but i've just started with yelp but we could eventually do business google business facebook trust i'll kind of give you an idea of what that would be like however there's a little bit different okay so let's get started right away on this what i.
Want to do is i want to show you that first thing how do we get the reviews from yelp right so from this business name it's relatively straightforward we're going to use something called make actually it's formerly called integra matte and take a map but it's also called make we're just going to use the the one called integra mat now this is integra maps very very simple here it's a dashboard now remember let's uh let's save those changes in case i made any changes now what i want to do is we're going to go into this called integra map now it's also called make right so you can see upgrade for make for free a lot of people are using the new version called make it's almost the same but i'm.
Just using the old version because that's where my account lies for now and i'm lazy to upgrade so basically what we're going to do is we're going to create a brand new scenario so you go into scenario and you create a brand new scenario so it wants me to upgrade to make it's saying keep earning a make so what i've done is i've just created one already so if i create a new one what we're going to be doing is we want to send information we want to send information to this and we want that information to be processed so we created a basically a scenario like this and i'll show you how we would do that but basically we're going to create a scenario called a web hook and that's going to let us send information basically all i want to do is i'm going to send.
This business information and we'll send it to yelp and i want to tell it how many reviews to get and then what i want to do is i wanted to download those reviews and then i want to bring those reviews and put them in this database right here so we see this new york pizza right we've got those these three reviews each review comes with a review id a link the text the actual text for that link a rating the date and time the review and then we're going to add in the platform the business and if there is a reply and then a row associated from that and i'll show you explain this one a little bit later and this one okay so that's what we're going to do that's the information we want so what we want to do is we want to send that to integromat so we're going to.
Posts Related:
Create something called a web hook and that if you want we're to create a new scenario and we've been this video before but if you want to create a new scenario we'll just click create new scenario here
Once you get signed up you get about a thousand free operations so integromat or make either one 1000 free operations we're not going to use that many today and what we'll do is we'll just click on add here or you can click add here we'll click here and then we're going to search for web hook we want a web hook and we're going to be sending so once we scroll down here all the way down we'll see that web hook this is the one we want and what we want to do is we can custom web book custom web that's what we want so basically we want to send information what that's going to do is going to give us we can just click add and we can create anything we want so it's we'll just call it test.We can call it reviews okay and then there's no ip restrictions and then we're going to save it once that happens it's going to assign us this web hook we're going to copy that at rest and we're going to add that to our code in actuality i've left a place for that so we can do that here because we're going to eventually have different here so we can do is we can actually place that code that web hook right in here i'm not going to change the one i have but so we're going to place that right here because this yelp is going to use this web hook now if it was google business or facebook or another type we would add a different type of a web hook for that because that web book is going to be dynamic i don't have any businesses yelp allows us to look up any business which is.
Really nice google business allows you to only look up your own business i don't have a business on on google business or facebook or trust pilot but we can add the code for that at a later time or we can have that you can have that customized or you can do it and i'll go over the steps of course a little bit on that so basically after we create this web book we're going to add that directly in here our code of course is going to take that our code will pick up this web book so we'll place that web web directly in there okay so once we have that we can then i'll show you in the code in just a moment so then what we want to do is we want to send that information where do we want to send it well i want to send it to yelp so i'm going to click add another module and i'm just.
Going to click yelp okay and that way it's going to just going to find that yelp and then what we can do is just going to click here yelp data and what we want is we want the business details or we can list the business reviews right so really we're just focused on the business reviews now if this looks familiar we did create a business directory in an old training where we did extract some of the reviews for that this is a little bit different training so what this is asking for is a business id and a locale look how it's just simply the country that we want so of course you will do united states because that's the one we're having so also once we select a locale on that we also wanted the business id now we need to know that business id.
Now that business id is this whatever business we're looking up that business id can be found directly in here so we see that the business id is right here just like this we click on the business it's everything like that that's all we need and that's the same information that we place directly in here so when we select a business all i need is get that business id so that's pretty much all we need we also want to set the number of reviews that we want to download so if we take a look inside here right the next one is the limit of 10. so we've set that three then what i want to.
Do is i want to parse that information it comes it comes in like an array right so i want to parse that basically it comes in like in just a big colluded grip let me show you what that will look like when it comes in it looks something like this let's bring that down here it looks something like this so it's a big big mess of data and that's really not going to help us right but we can use integromat to help us parse that data and to make it workable right so basically what's going to return is all this and so what we're going to do is we want to bring it in we want to separate it in because we don't want this bunch of data the bunch of data is fine however we do.
Want some separations in there so what we're going to be doing to that is adding something additional let's go into the one i've created so that you can see we can go step by step this is the one that i've created already and so all i've done is did the same thing i've added that web book i've added the yelp if we take a look on here we see that we have the business id now we're going to send over this information from the web hook and i'll show you how to get this label this business id label and i set the limit to three in the united states and then what we're going to do is we're going to use a table aggregator and what that's going to do is going to help us separate all the information that comes to through yelp it's going to be in an array but i want to aggregate that in a form that excel can understand so we can take it apart with the vba code and we can separate those.
Columns and we can separate those rows into nice data so that's exactly what we're going to do so once we get the information once you send the information and i'll show you how to send that right what information do we want to get from yelp well we want the review id we want the url we want the text of the review right certainly the rig i want to know the rating is it one two 3 4 5 star and i want to know the time that was created i don't necessarily need to know the user now what we want to do is i want to separate that right i want to separate the columns right as we add.
It if we take a look inside our data right we're downloading a lot of data here i want to separate the review id the link i need to separate i need to know where this data starts and where this and where this is so we need to separate those columns and then i also need to separate the rows here so this can help us do just that so let's take a look inside here so i'm going to use this as our column separator backslash c backslash and then i need a r separator for our row now it's we can use a new row a tab but i'm going to use a very special character very specific then i want a row.
Separator right because i need to know if there's how many different rows of data there are we don't know how much it's going to return so i'm going to separate the rows by this backslash r backslash
Okay so that's all we have to do that's it nothing else there and just click okay now if you want to know where to get this table aggregate all you do is click on this purple and take a look down here and it's this table aggregator so all you need to do is just drag it up into here and there's your table aggregator right we don't need two of them but that would be how you do it just drag it up there right from here then the last thing is i need to get the information that this aggregates all that information i need to get it back into excel and we can do that with the web hook response now the web hook response all we need to do is click on one click web hooks and then just.Click webhook response that's all we have to do relatively simple that webhook response is going to come directly from this aggregator here so we click on that we look at the options right we want we don't want the business id we don't want the json text we want this text directly from here that's coming from this table aggregator we want to drag that all we just need to do is drag it in there and then it's there for that or we can just double click it okay so what that's going to do is going to get us inside that response right so that we have all that information once we get all that.
Information in a response in vba we can then take it apart using vba and put it in the appropriate columns and the appropriate rows and if you want to check it you click run once it'll run or you just turn it on and it'll run automatically if you want to know what has already run just go here and let's we can discard changes that are making change go into the history and you see the list of all the runs now if we take a look at the ones that i just went through click on the details here and let's take a look at this so we see exactly what happened inside the web hook here right i sent some information what did i send i sent the business id new york roma so that's all i sent the.
Business id here so let's take a look inside here what got received the input the business id here's what's got received we want a limit of three and we have the locale so that's all what got received now what was the output the output is all of this so this is what we have to make some sense of so we got bundle one we got the review id we got the url we got the text we got the rating and we got the time created here okay and then we also have the user which we didn't get the users.
Another one so we didn't need this because this is in another array so i just left the user blank and the total number of bundles this is the three and the order position okay so we got another so bundle means review bundle so we got three different reviews this is everything we got so this is the output right so then we went over to the tools right and we got this input so the input is exactly the same as what the output is here's the here's all the input the same as what it is before and what is the output the output is one bundle of text but that bundle of text.
Is separated so we've got the id here and i'll show let me show you this in a little bit larger form and it is this right here so this is the output that got output right i just have it in a text file just so we can see it so we have that first id here that first id then it's separated by a c so if i hit enter and we see enter here it's a little bit easier to separate it right then okay so then we have the link if we take a look at the end of the link we've got another backslash c right here and what a let's bring that down here and let's hit enter so if i hit enter.
There then we have the review text here and here's the review text so here's the review text and then again once the review text is done then we have another column separator then we have the review itself here's the five then we also have the date stamp let's take a look at the date stamp now the day stamp is going to have to work with that so it says monday july 18 2022 11 50 and we got all this right so that's not going to be helpful for excel so we'll have to work with that a little bit and i'll show you how to do that so that's the date right and actually universal time this is we need.
To go back here so this is here let's go back so universal time so this is everything that we have here and then once we get to the r that's a brand new row so we take a look at this here here so this r means a brand new row starting so then again we have another business id and it goes on for three different times so this is what i want to do i want to take all this the business id the link the text the rating and the time and put that into the first row but first i want to check does it exist yet i want to check this id and i want to look directly.
Inside our table does it exist yet if it doesn't exist then add it if it does then ignore it okay so that's exactly what we're going to do right here so once we have all that parsed information vba is going to take on the rest and let us know exactly how we're able to fill that in so that's all we need to do so this is going to help for me so that response is going to be in one big text file all we need to do is just separate that text file into all these different parts and then just put it in the table and i'm going to show you how to do that inside an array and we're going to use a split okay so let's get into the vba and all of that happens when we click this.
Button right we know it's going to be yelp and we know it's going to be this business so that's what we're going to get reviews if i try to get reviews again we've already got all three of the reviews so nothing has been we've got the three last reviews already so nothing's going to be added which is correct right i don't want to keep adding and duplicating them again and again all right so let's take a look inside the code and see exactly how we created this now if we take a look at the macro that assigned clicking assign macro we see it's a macro called download reviews and when i edit it it's going to open up our vba editor it's going to go into this module called get review macros it's not a very long macro we've compiled it and it's very very compressed and optimized.
So it's called download reviews okay the first thing we're going to do is dimension the object http as an object we need several strings the url we need to build out that url i need a json as a string i need that response that response is going to go that big all that text and go into response i need the hook string that hook string is very important because that's going to be the string that starts out right here that string we're going to pull that depending upon the type of platform that we are using to extract it we are also going to need the business id i need to look for that to see if it's been found so we're going to put that in there and i also need to know the review id the view id it's going to be important so that business id and the review id the business id is what we're going to be putting in the string okay i also need to create several arrays the review.
Array is a string it's going to be a review and i need to know the review data once that data gets in we're going to split it into multiple strings we're going to use the same thing in the review data array as a string i need to know what column we're putting that review and i also need to know what row we're putting that in and i also want to keep track of how many reviews we're tracking have we added one two three or four how many reviews have you added so i need to keep track of that as a review number and what is the total number of reviews that got downloaded i also need to know if it's been found that review has been found i need to know what what row it is and i want to keep track of the count okay we have multiple ones so we're going to focus on the platform review as.
Long so that platform review as long right so i want to know the platform row is long okay so which platform right it's very important right we may be using yelp i want to keep track of the row there if it's yelp i know to use this web book if it's a google business use this webpage all right so let's continue on with the code okay so we need to know the row all right so the first thing what i want to do is we're going to focus on the reviews right if the reviews b2 is empty why is b2 important let's go ahead and take a look inside some hidden columns and see what we have we don't have too much but let's just a few items so the first thing what i want to do is i want to know the row that's associated with the platform yeah right i want to know that yelp is on.
Row 4 here because it's important because i have to extract the web up very specifically used for this yelp platform to do that all we do is create a named range let's go ahead and view these and we can also view the formula bar and we'll view the headings to help us out okay so first thing what i want to do is i want to create some named ranges so we're going to go into the formulas and we're going to name ranges we're going to see a few formulas that got created the first one is called platforms if we tab order over we see that we have inside our admin we're using offset and we've got a named range for all the platforms that we have here and it's just basically using the.
Offset so that's going to create a dynamic ring name range we also have another one for businesses if we take a look here we see we have the business id here and i also have the business nickname here the nickname used for the business the business nickname going to come very very helpful when we use our dashboard right because i don't want these long ids i want the business nicknames to be used inside our dashboard great all right so let's take a look inside here we have one for review database and and review id so that's the last one i really wanted to show you we've got an idea dynamic named range for all the review ids inside the review database so that's it so what i want to do is once we change this right if we change it to google business or whatever.
I want to make sure that i know that i've got the row that is associated with that and it's just simple we wrap it around if error we're going to match whatever the user has put an m to we want to get it based on the platforms and we're going to add three the reason we add three is because the first one starts on row four and i'm looking for that row number if i know the row number then i know that an e and the row number is where our web hook is going to be placed for that particular platform so i want to make sure certainly that b2 contains a value if it doesn't how am i going to be able to extract that web hook right it's a dynamic web book based on the platform used okay so we want to make sure if not we're going to select the platform to get the reviews from.
And i'm going to put that into a variable called platform row that's going to be based on b2 and i also want to extract that hook string into a string variable and of course it's going to come from the admin e and the platform row just as i had mentioned it's coming directly from e and the platform row so i'm going to put that entire string right here this is remember this is the exact same string that we pulled directly from this web hook here let's go back to the diagram here and we're going to put that is the right one here inside this diagram and we just want to copy.
The address notice that that platform it ends in ek5 right so it is the same one that we're using inside here so ending in ek5 that's the same one okay so continuing on so we want to make sure that we have that if the hook string is empty for any reason please let the user know to add a web book in the admin to get the reviews right we need that web hook to extract that i also want to get the business id the business id is very critical that business id is located directly right here inside i2 if that is empty we should certainly let the user know that please select a business to get.
The reviews that can't be empty we need to have a business if we're going to extract the reviews for that once we have all that information we are ready to build our link and we are ready to send that information so we're going to set that object http to create the object msxml2 server and basically what that's going to do is just send allows to send information over the website using this hook so we're going to build that url i want to send the hook string that string and i also want to send the business id and i want to give a very specific name called business id that is the same name business id is the same exact name when it comes across crossover.
You're going to see it directly in here let's see if we take a look inside a separator here let's take a look inside here it's the same one that you saw right here business id so that's what i want to send information over right whatever we sent over gonna send okay so the business id and this is the actual value and this is the label that we're gonna use the labels go from and and then equals and then the business id after that the actual value that's all we need to send simply just send the hook string which is the web hook and the business id so we're going to open get get the url we're going to send that url then we're going to send the request our header types are basically just very basic with content type and then application json.
Then we're going to actually send that information and then what we would do is we're going to get that response remember that response is going to come directly inside here that response is going to come right here this is what we're looking for and we're getting that text that text again is going to look exactly like it does here so that text all that text is going to get in one single variable and that variable is called a response so the first thing what i want to do is i want to make sure that we have a correct response if for some reason inside that response it includes the word failed then we know it has not ever happened or maybe the word accepted if it's accepted maybe that means that your it is not turned on or not running or something or the response equals empty.
Then we know that there's an issue so for any of those types of responses i want to let the user know to please make sure to add a correct web book and of course check all the settings we're going to exit the sub out of it right we don't have a correct response but if we do have a correct response then we can continue on first i want to do is i want to get that first available review row inside our review database here that first available row using a and xlr plus one it's going to give us that first available row i'm going to put that into a variable and that variable is going to be called review row or rev row right here that's the first available row then what i want to do is i want to determine how many rows there are right i need to know how many rows so to get the what we're going to do is we're going to put that in an array and i'm going to split that.
Entire text by the number of r's right if we take a look at this that backslash r backslash that is the separator that we use that's the separator that we're using right here so this separates the rows as you can see there's there's two in this right so that means there's three different ones so if we know that there's two in our response here if there's two different separators of rows we know that there's three different rows of information right two r's here and you would see in this one we would see the second one would be right up here right after the time so here's the second one here so we know that in this case there's three different reviews that we're going.
To have to parse simply by counting the r's so we're going to take a look inside that and then we're going to determine that so once we have that we're going to count that i want to split that response by the r's and i wanted to put the quantity is the quantity is the reverse quantity u bound the upper bound review array so this is going to get us the quantity actually the quantity in this case would be two but if we're starting at zero arrays always start at zero zero one and two is actually three remember so this will be two but we always start at zero we're starting at.
Zero in our loop going to two so that's actually 3 right 0 to 2 is the same as 1 2 3. 3 still three different so once we have that quantity we're going to extract in this case it would be 2 because the upper bound the upper bound u bound of that array is the limit what is the highest number of that array right the highest number is two the lowest one is zero the middle one is one so this is going to be two right this is the upper bound number of the array bound number of the array all right so once we have that we're going to focus directly on that review database we're going to set the review count as zero this sets the initial account of the number of reviews to zero and then what we're going to do is we're going to loop through all of the reviews so we're starting at zero which the reason we're starting at zero now is because.
The arrays start at zero so we want it to be equal to the arrays an array always starts their count at zero and then goes to one and then goes two or however many you have so it's going to be count from zero to two and we're gonna extract that data the first thing what i want to do is i want to get the review data all of the data inside that first review is going to be review array and then the first one is going to be zero so what does that mean that's all of the data inside that array that would be everything here so all of this data is going to be inside a single string all this data inside a single string maybe not the r so once i have it all in a single string i need to separate it again by the columns by the columns so that's the next step so we have all the data for a single.
Review in one string then i want to separate it into another array called review data array how am i going to separate that well i'm going to use the split function and that split function is going to be separated by the column so i need to split it once again this time by the individual columns otherwise known as the backslash c backslash that we're going to split that review data so we can separate it into individual columns and but before i actually put it in the database i want to check to see if it exists how do i know that well the review id is very unique that review id is right here so what i'm going to do is i'm going to look for the first item and extract that.
The first item is right here and this would be this first item right here this is the review id so this particular one is going to be the first item inside our array right here so to get that i need to put that inside a variable and check to see if it exists so that review id is going to go into a string variable it's called the review data and it is the first one remember arrays start in zero so we want to get the first item in our array meaning zero and that's going to get the review id so what i want to do is i want to check to see if it exists in the table if it doesn't exist it could create an error so therefore we're going to wrap it in on air resume next and on air go to 0..
I'm going to set the row i want to determine if there's a row found so to do that we've already inside the review database dot range we have a named range that is dynamic name branch for our reviews id so i'm going to look inside this named range and i'm going to look for the review id i'm going to look inside the values i'm going to look whole and i want to extract the row number if that row number is found it would not be zero and that means it exists already if it has not been found it will be zero so if the found row does not equal zero then we know we can add it right so that's all we have to do if it does not equal zero that means it's been found already we.