No-code Community Leaderboard for #Tweet100

[00:00:00] Aron: Hey, Jay, welcome to automate all the things I'm excited to have you here.  

[00:00:05] Jay: Hey, I'm stoked to be here. This feels amazing.  

[00:00:08] Aron: Yeah. Well, how do, how do we know each other? I'm you, you are a friend from the Twitter verse. Yes. How did this come to you and  

[00:00:17] Jay: Twitter gotta be Twitter. That's how I know, like everybody, these days, I'm increasingly two dimensional I'm online and that's pretty much it online.

And in this space,  

[00:00:30] Aron: that's, that's, you know, that's 20, 20 and 2021 for ya. Um, I know you and I know what you do, but maybe we'll do an intro, uh, for everyone, uh, kind of joining us today. We've got Travis Coleen, Steven, uh, got some familiar faces, so say hi in the chat, but I think Jay, a good place to start is tell folks a little bit about yourself.

[00:00:51] Jay: Yeah, I'm a creator. Mostly I'm hosting podcasts. These days, I've hosted a show called creative elements where I talk to high-profile creators about how they made a living from their art and creativity. People like Seth Godin and James clear, and Vanessa van Edwards and Tim urban, uh, instead of talking about their work, I talk about how did you actually get to make a living from the things that you make?

It's very fun. Uh, I also lead community at SPI smart passive income companies started by PatFlynn back in 2008. Uh, but yeah, I spent a lot of my time just making stuff, writing podcasting workshops courses. Not unlike you, Erin.  

[00:01:28] Aron: Yeah, no, it's funny. I, I don't, I, I don't re I don't know if I consider myself a creator, uh, maybe a creator of workflows.

I do enjoy the streaming part. Uh, but I guess, I guess I am, I'm bundled in this, in this creator world.  

[00:01:45] Jay: Yeah, creator. Um, I feel like creator is a broad term for sure. You know, I think it encapsulates a lot of things. Like I would say you are 100% of creative things that you make her incredible you're streaming right now.

This is like advanced creator stuff.  

[00:02:00] Aron: Well, I appreciate that. So what, uh, uh, as two creators, you know, we'll call ourselves that for the rest. So let us know in the chat, if you consider yourself a creator, we're definitely going to do some, some creative workflows here. So, um, you came to me with, uh, a workflow, like, like, like I would say the essence of the things where you asked me to like, automate something quite crucial and quite fun.

Uh, so tell, tell the folks today, like, what are, what are we going to automate today?  

[00:02:31] Jay: Yeah, I had this idea last week that. Whenever I tweet more, I get more followers and I meet new people and good things happen. And so I asked myself, why don't I just tweet more? But it's really easy to get distracted by frankly, probably more important work than tweeting.

And so I thought to myself, I just want to have a mechanism to force myself to make one good tweet a day. That's all I'm going to ask myself one good tweet a day that I think is worthwhile and helpful for other people. And then I realized, oh, I bet some of my friends would be in on this too. So I asked them and everyone had told us too.

They're like, yes, I would love some sort of forcing function to just tweet more. So I'm creating a rolling 100 day challenge called tweet 100, uh, tweet one hundred.com is active. And as a part of making that work better, I wanted to create a bit of a leaderboard so people could see how many days in a row have I tweeted.

Um, and it also might be able to link to those tweets so that people can support each other. I thought that would be a really fun public thing. And a spreadsheet could handle that if I trusted people to only edit their own data and not mess around with other people's data, which I just don't, I just don't, I don't trust people to not do that.

So I was looking for a way to create a leaderboard that people could control their data. They could see it, um, but it would be safe from other people kind of playing bad actor. Awesome.  

[00:04:00] Aron: Okay. So I'm hearing a few things. So the goal is you sign up to this hundred days of Twitter and you tweet multiple times a day, but in one of those, you want it to be like your R hunt, your a hundred days of Twitter.

[00:04:16] Jay: Right. Yes. Yes, I would. I would like that person to be able to just hashtag hashtag tweet 100. So they know that that counts as the tweet that I made today, that I want to be counted in this a hundred day challenge. And the leaderboard should show how many days in a row have you done that? All the way up to 100.

[00:04:32] Aron: Okay. Okay. Got it. Um, then it's, it's rolling. So essentially someone signs we're going to get into the workflow here, but you know, your goal is just to get people to tweet more. And, uh, actually I think it's pretty cool. I, I, I was, uh, I think I'm on like eight years of Twitter and I have been on Twitter for eight years.

I've only been tweeting for two. Uh, so had this happen to me maybe 10 years ago, I'd have met, you know, increasingly more interesting people like yourself. So, um, yeah, I, and I'm really excited about this workflow. Let us know in the chat, if you've done any type of, uh, Twitter automation, um, I know that there's a, like, people love scraping Twitter because it's got a very open API.

But what I particularly like about this one is that it's one of those workflows that you don't feel like you can automate, but we're actually going to fully automate it. Right. Hopefully, hopefully, if all goes well, if all goes well. Um, okay, so let's, let's dive in. Let's go into, uh, kind of, uh, sharing my base here.

Boom. So folks should see my base now and then let me, let me get you as well. Now you J should see my screen. Everyone's good to go. Okay. So let me bring up the agenda for today. Oh no, there we go. Okay. So our first step is the base structure, right? Right. So how do we get new members, new active people into 100 days of Twitter.

And then how do we track their tweets in an automated fashion? Right. At least we're going to give the structure, like, what do we want to track for each tweet? Um, then we're going to create a form for people to sign up to a hundred days of Twitter, and then we're going to do like the actual magic. The magic is how do we automate when someone signs up to be able to track their tweets, you know, after that, right?

And then once we're updating, once we're tracking the tweets, how do we actually have a leaderboard that automatically updates that says, Hey, you actually tweeted, uh, in that day. And finally, I'm going to kind of open it up to Q and a. So folks who are new here, uh, um, Q and A's throughout, I have a dedicated section at the end, but like you you're doing the work of coming live.

So feel free to ask questions throughout Jay. You're more than welcome to ask questions throughout. How, how familiar are you with air table, fairly  

[00:07:02] Jay: familiar. Um, but you know, I've never wrote any scripts. Um, I'm getting a little bit better at, uh, lookups and all the relational things. So it's not my first rodeo, but, uh, I definitely feel like an amateur sitting next to you.

[00:07:18] Aron: Rodale, Rodale, Rodale. Interesting. I'd never heard that before. Wrote, wrote, wrote my first rodeo wrote him. I mean, same word. Listen, I've never been to a rodeo or a rodeo and probably you have, so I shouldn't, I shouldn't, I'm just, I just love it. I just love it. Okay. Uh, okay. Based structure, more important things based structure.

Okay. So tell me like, so we want a member, like a list of people who have signed up. I've called that member. Let me know if that makes sense for you. What are some of the elements you want to track for each of your members who sign up?  

[00:08:00] Jay: I would love to see their username,  

[00:08:03] Aron: a Twitter user. Twitter username.

Okay. So I'm going to, I'm going to put that as a primary field, right. And we'll use like you as an example. Right. So, and myself, and let's maybe give us a third, like a guy called Jeff. He got a really good username, but doesn't tweet very often. Right? So the name is going to be our username. Actually. Let's call this username.

If that makes sense. He's your name? Great. Okay. What else do you think you want to know about each person who has signed up? Uh, I think  

[00:08:31] Jay: it'd be good to get their first and last name. If they wanted to provide it. I don't think I'll make it, um, required, but, uh, I like that personal touch.  

[00:08:40] Aron: Got it. So I'm adding single line text, right.

And let's fill it out for ourselves. What else while I'm doing this, what else do you kind of comes to mind? Doesn't really have to be much more does it? No.  

[00:08:52] Jay: I mean, probably email address, but when people sign up for the challenge, they will sign up with their email first. Right? So I'll already have that, but it would be nice to sync this a little bit.

[00:09:08] Aron: Absolutely. Thanks Coleen for the reminder to hide the agenda. And I, I am, I rely on, on you all to, to remind me of that and appreciate that. Uh that's awesome. Okay. So, so it doesn't feel like we need much more than this, right? As a right. We can kind of add as we go along. Now, one thing I will add is when did they sign up?

Right. And that's going to be the timer for the hundred days, right? So in air table, you can use this creative time. So signed up at and created time tells you when the record was created. Right? So this tells you that this one was created earlier today. And then we just created two records a moment ago.

Uh, it's automated. So whenever you create a record, it'll automatically give you that create a time. And that'll be useful when we get to the form. Okay, so this feels over, right. Um, okay. Let's talk about tweets. So let's get a tweets table and then primary key here. I actually think that easiest for now would be the username again.

And why is that? Eventually we're going to have to match records from the members to the tweet. So it kind of makes sense. Um, so I'm going to call this, let's keep it to name just so we can differentiate the two. Um, we probably want like what the tweet is, right? The tweet itself.  

[00:10:35] Jay: Yeah. Yeah. Um, or like just a URL.

It'd be good to get the whole body. We can get the whole body that's weak, but it'd be good to have the URL.  

[00:10:45] Aron: Got it. And not, not that much else. Right? Like you're just kind of, you want the existence of the tweet and only have the tweets that. Uh, the, the, the right hashtag. So I think, yeah, the body of the tweet, the tweet URL, the name, and I'm going to add a Korean time as well.

Yeah. I think that's a good idea. So, uh, you know, tweeted at right. And let's give this another, uh, create a time. Okay. So this so far is making sense. It's kind of weird, cause we don't really have tweets just yet, but Zapier is going to be the one let's maybe just give ourselves like a tweet for me, let's fake a tweet.

This is a tweet. And then just go twitter.com. Just so we have something here. Okay. Um, I will now link my tweet to the members. Right. So create a relationship, uh, between members and tweets so that we know when someone tweets we're able to associate those two things, the way I'm going to structure, this is not necessary, but it is useful, right?

Let's go ahead and link to our members and I'm going to link this to myself and let's say, you've got a tweet here  

[00:12:02] Jay: and you had that set. So it could link to multiple records, which makes sense here, because someone will have up to a hundred records. Right? Well,  

[00:12:09] Aron: no, in this you're actually right. That's a mistake I made.

So we should not allow them to multiple records because every tweet should only be associated to one member. Right, right, right. Yep. Right. That's uh, so you're, you're on the other side, it is a, a multi, uh, um, you know, I can have multiple tweets from one member, so here that's. Okay. So here we can actually allow link to multiple records, but on this side it should be a one to many relationship.

Uh, sorry. It should be a w each to each should have one member. One member can have multiple tweets. Um, okay. So actually our base does not need to be more complicated than this, right. And the reason is, is that we can have a view. That is the leaderboard, right. Which is filtered for those who have signed up less than a hundred days ago.

Right. So that's our current. So let's create a field that is active and we're going to create a formula that says is the signed update less than a hundred days away. So we're just going to do a daytime diff and we're going to go to magic knowing signed  

[00:13:15] Jay: up formulas.  

[00:13:16] Aron: Incredible. Are you, are you a pro or is this, uh, would you have thought of this or no?

[00:13:23] Jay: No, I would not have. Okay,  

[00:13:25] Aron: awesome. So we're saying from, uh, the signup date to today, right? What is the difference in days? Let's just create that field. It should be zero for all of these, right? So let's just create a condition that says is that less than a hundred? That should turn into one. Cause it's true for all of these, right?

Let me zoom in here for folks. Uh, and then we create a filter that says if active equals one, then you are active. So past the a hundred days, you're out. So, and this is what you would share. So let's give username and we forgot one field that's super important, which is consent like days tweeted. Yeah.  

[00:14:18] Jay: I was just gonna throw this out at you.

Days. Tweeted is obviously important, but if somebody doesn't tweet, I don't want to take them down to zero. I'd love to see it. Can we show a progress or like a proportion? Like,  

[00:14:31] Aron: yeah. Yeah.  

[00:14:32] Jay: I mean, number of days out of 100 or number of days since they've joined.  

[00:14:37] Aron: Yeah. So this is how I'm thinking of this field.

So this field will be from the day you signed up, how many of the days did you tweet? And then you could make it a percentage. Right. And this will always be less than a hundred because you can't tweet. You can't have, we won't count more than one tweet. Um, and so, uh, so we're going to go ahead and keep this as day tweeted and it doesn't have to be consecutive.

Right. Got it. Okay. Um, okay. So days tweeted, and this is what you, this is your leaderboard, right? So you share this right. Boom. You can, you know, make it much nicer. You can put it into web flow. You can, but at the base, everyone who's going to sign up is going to be here. And then we're going to have the number of days that they've tweeted and you're trying to hit 100.

Um, does that make sense?  

[00:15:25] Jay: It does make sense. Um, yeah, I think it would be good also to add, and I can do this, uh, afterwards, cause I I'm going to get like row happy here, but it'd be good to add. Um, like if I tweeted 90 times in 95 days, I would want to know. Or if I tweeted 90 times in 90 days, you know, like there's like consecutive days tweeted and then there's the total, you know?

[00:15:52] Aron: Yes. So that's actually easy. You would just create a daytime diff from today to the moment they signed up, that gives you a whole number of days, and then we'll compare days tweeted over that number to see progress. Uh, we can all, you can create formulas for, for all of those. So that's really not a problem.

Um, let me know in the chat, if I'm going too quickly. Cause I know that we have some like awesome stuff coming. So I want to, I want to kind of make sure that everyone's on good foundations, that they've got good baseline. I'm trying to put more puns into the stream. Okay. Um, so, so we've got our leaderboard.

Um, so that was, that was kind of step one. Right? So now we have our base structure very simply where we can go from here. Let's just create a form for signup, right. So that's going to be step two here. Uh, and I will quickly just create a quick form. Boom. Just creating a view, which is a form. So sign up to 100 days of Twitter.

Let's remove all the fields. What I want is your Twitter username, your first, your last name and your email. That's it. Boom. So I'll let you style this a little nicer post stream. I don't know if I should have made that a step it's quite quite quick. Right? So we just created our form. I can drop that. Uh, um, I'll drop it.

I'll send it to you later. Actually. You're going to copy this. So, um, I want to drop it in the chat, but I'm worried that also some people are just going to start willingly filling it out, which you don't necessarily want. Um, okay. So that's step two. So far, we haven't done much automating, right? We're just kind of, uh, uh, um, create a base structure, creative.

Now let's talk about the Twitter part. So when someone signs up, what do you just like, what is the outcome that you want? Right. So how, how did you think about tracking folks on Twitter? Um,  

[00:17:59] Jay: it would be good to see them in a list because then, um, I can interact with them and support them on that. Um, I want to use the hashtag tweet 100 so I can find them, uh, and see that happening.

Um, and people can understand what's happening, but it might also be helpful with the automation. I'm not sure. Um, but yeah, mostly I want to get them into this so they can take advantage of the leaderboard and use that as motivation.  

[00:18:27] Aron: Awesome. I also just updated the hashtag on the stream to tweet 100.

Right. So it's a good to go. There we go. Love it. Um, okay. So the workflow that you would want in it. He is someone signs up as a member gets added to a list. And then we track that list for every tweet that has, uh, um, that hashtag right? Yes. Yes. Okay. So I'm gonna, we're going to go over to Zapier, right?

Where we can kind of translate that automation into zaps. So the first thing you said is person signs up. So new record is created in air table. Add that user to a list. Second automation would be when a new tweet in that list has the hashtag tweet 100 track that tweet attitude or table. Okay.  

[00:19:26] Jay: Yes. And associated with the right member  

[00:19:28] Aron: associated with the right member.

Absolutely. Um, Yes. And then there's going to be, the challenge comes after that for folks who are kind of trying to foreshadow where we're going. So I'm in zap here. Um, so for folks who are new here, Zapier is a tool that is a kind of, if this, then that you're very familiar with a J um, let's, you have triggers an action similar to air table automations.

So our first one we said is in air. When a record is created an air table,

new record in my air table account, this thing is called 100 days of Twitter. I'll change that later base is 100 days of Twitter and the table that you need to have a record created in ease the members, table members, table. Boom. Let's test that we should see either Jeff, Aaron or yourself. Jeff. Jeff came up.

Okay, good. Old Jeff. Um, let me see if I can get another one. Okay. I have all three records. Actually. Let's get one that has like a PR a real, uh, username. Right? So your username actually, let's go with yours, your username. That's fine. Continue. And then in Twitter, we want to go ahead and add user to list. So this is the trigger.

This is what we want the automation to do. I mean, I I'm, I have to say, gee, I'm really worried that people are gonna watch this and add me to a bunch of lists, automated fashion. After this,  

[00:21:17] Jay: it doesn't hurt you. It, it fills up your notifications a little bit, but it's not a bad thing.  

[00:21:22] Aron: Uh, I, I am already on too many no-code lists like that is, if you look I'm list, I can show you.

List some on  

[00:21:30] Jay: you need to grade a leaderboard that counts how many no-code lists you're on.  

[00:21:33] Aron: Like could, could we just put everyone on one list? Like just everyone should be on the one no-code list.  

[00:21:42] Jay: Uh, dedupe, dedupe function here at wireless  

[00:21:46] Aron: Twitter should build on for sure. So I created a T a test list right here, and right now it's only got you, but let me remove you.

Let me see how I can remove someone from a list. Oh,  

[00:22:01] Jay: everybody's seeing how I was in the wrong zoom room to start. I'm so embarrassed.  

[00:22:07] Aron: Uh, let me remove you. There you go.

Boom. Okay, so now there are no one in my list. Um, so a good question from colleagues. Sending a tweet when a person completes a form that can also be automated. That's a great, we'll put that at the end calling. And that's a great question. Having a tweet from you welcoming that person is a cool idea. We can automate that as well.

Um, so we want to add you to the test list and the username. Your username is from the record, right? So username was J Klaus. Cluse Klaus,  

[00:22:47] Jay: Klaus, Klaus. Now does the username need to have an at symbol?  

[00:22:51] Aron: We'll see, I don't know if not we'll I will. Oh, no. So it does not need to add an add symbol. That's there's two things you can do for that.

One is in the form. Mention it. So say like, do not include your at, but then cause people won't read it because that's the nature of forms and people, you can have a formula that uses like a reg X or rejects to remove any add symbol. Or, you know, just so if someone puts it at the end or whatever, uh, you can remove it in irritable and use that field for your automation.

Okay. So we should have, now you, in my list,

there you go. You're back on my list. You're one member. Uh, so you are on my Teslas now. And so this will be automated now, right? Great. Uh, turn on the zap. Okay. I do not want to share it. Um, actually I will share it if folks want to use it. So I'm going to share that there's always a formula for everything Steven, for, you know, uh, if there's not a feature that script  

[00:24:10] Jay: share that formula with me, Steve, I'm going to need it.

[00:24:14] Aron: Um, Okay. So that's actually the first app. Like that was, it was as easy as that. Now, anyone who fills out the form is a part of our list. And now we see that we want to go ahead and track the tweets coming from, uh, members on this list, right. And something that's particular. We only want to track members that have specific, uh, um, elements in their list.

So namely the hashtag. So, uh, Jay, how about go ahead on Twitter and give us a tweet, 100 tweet that we will have it, uh, in the list. And as you're doing that, I'm going to kind of configure the zap to start tracking every tweet. So this is a new member add to list pro-tip I've started like using the trigger and the actions in the descriptions of my.

Uh, and air table automations, just so like, it's easier to understand instead of like automation, one and automation to, uh, trying to live a more organized life. Um,  

[00:25:25] Jay: here's a question for you as I'm typing that tweet that I just sent, uh, will capitalization matter.  

[00:25:30] Aron: Mm. Good question. We life it's up to you.

Do you want it to matter?  

[00:25:37] Jay: I want it to not matter.  

[00:25:38] Aron: Okay. Okay. So yes, we can. We can make it not matter. We can do whatever we want, Jay. This is, uh, this is all the things, right? Exactly. It's a space where the laws don't apply. Um, so our next step here. So remember we did our first app, which was new member created, added to a list.

Our second member or second step is like track every tweet that's emanating, I guess, or being tweeted by members of the list and then filter for those that have the hat. Uh, um, and then send those tweets to air table so we can track, uh, whether or not folks have in fact tweeted. So our next zap is to go to Twitter as our first application or trigger.

And then I have tweet enlist. Now, a lot of you will say, what, why don't you just search the mention? Because people outside of our list may be also tweeting about it. And I don't want to capture those. I want people to go through the funnel  

[00:26:56] Jay: and I have seen that people have used that hashtag before seemingly very randomly.

So I'm glad we're making this builder. Yeah.  

[00:27:03] Aron: So we're going to go ahead and go tweet in list and let's go to my, there we go. Boom, boom, boom. Test, boom. Let's test. Let's see. Did you give us a tweet? Awesome. Ed Tweed, 100. Boom. Okay. So let's see what we get about this. We get the full text. Uh, we get the creative time, which we're tracking in air table.

We've got everyone who gets mentioned in an array. We've got the URL, someone here, it  

[00:27:37] Jay: breaks down hashtags underneath entities. And we'll give you the text of the hashtag.  

[00:27:43] Aron: Oh yeah, we can totally do that. There you go. I will say that I'm worried about this because let's say you have three hashtags before.

I won't know which, which position it's in. Right? So if it's the third position, it's harder for me to say in, in Zapier is not great at saying if any of the hashtags is tweet. Filter on that. So I wouldn't actually use this as like my filter. Okay. Um, I would actually just go with the whole text and have Zapier check, whether it includes tweet 100.

Um,

there we go. Kids got like, even got follower counts. You can track follower counts over time if you're interested. Right. Like, so you in the tweets.  

[00:28:39] Jay: Interesting. Oh wow. I could measure people's growth through the  

[00:28:44] Aron: challenge. Yeah, absolutely. Yeah. Yeah. You can have that. Cool. Right? Yeah. We hadn't thought of that.

Um, you can get where people are tweeting from, so you can create a map in air table of where everyone's tweeting from. Uh, so interesting  

[00:28:59] Jay: and quitters med metadata, they call following  

[00:29:02] Aron: friends. Oh yeah. That's probably like a remnant from like 2000, 2007 or whatever. Or whenever Twitter, um, you can also get URL images.

So in your members, if you want to add the image, uh, you can get their profile images, things like that, whatever, there's a bunch of stuff here. We only care about whether they've tweeted or not. So that's just food for thought. That's a future thing. So here we want to filter, right? So we want text

contains tweet 100. Now you said like, oh, but what if they, you know, made a, uh, uh, T capital T whatever, what I would do, and you can do this, we'll come back to it. Post stream, you have a format or by Zapier, where you can take text and make it all lowercase. Right? So just we'll do this at the end. If we have time, you create a new field or a new variable that is like lowercase hash.

And where it takes the whole tweet and makes it all lowercase. And that way you can then filter on it. Does that make sense? Is that  

[00:30:16] Jay: just, does that get created in air table or in some like Zapier purgatory  

[00:30:21] Aron: in Zapier purgatory. Okay. But you get the full text tweet in air table anyway. Right. So it's not a problem.

Okay. Let's see if that would have worked. You said your zap would have continued. That's awesome. Boom, boom, boom. Thanks Steven. For the formula and the chat. So if folks are wondering how do I remove the at symbol? Uh, um, Steven Len looking at the formula looks right. Good job, Steven. I appreciate that. Um, amazing.

Okay. So next step here. We're filtering for those that have the hashtag. Now we want to send to air table easy peasy, air, table. Boom. We're going to create a record. In our tweets table,

let's go to air table.

Let's go to 100 days of Twitter. Let's go into our tweets table. And then we're just kind of mapping the different fields to the elements from the tweet. Right? So if we see this, I want J J Klaus, this right here, the little at, to be the name, the body of the tweet goes here, the URL of the tweet goes here.

And then I associated to the right member, which is just inputting their username so we can link it back. Um, okay. So let's go ahead and input that making sense so far, you're, you're a Zapier user,  

[00:31:59] Jay: right? Yeah. This is awesome. This makes a lot of sense. Uh, I'm also just in awe of how fast your computer.

[00:32:06] Aron: Okay. In what sense really? You find, I find my computer to be very slow.  

[00:32:11] Jay: Uh, I don't know. Things seem to be loading. Like now it's all happening quickly.  

[00:32:16] Aron: So there might be an it request for a better computer. So if your work, if you work at air table, it, I would appreciate you to skip what JJ said. And just note that my computer is not running as fast as it seems on the stream.

Uh, but I do like, uh, it is very luckily you can't hear it, but like, my computer is like screaming at me. Like, why are you running so many things at once? Sounds like an  

[00:32:39] Jay: airplane  

[00:32:40] Aron: taking off. Exactly. So we're filling out the name, then we're going to go ahead and get the body of the tweet. So go here full text.

Let's see. They have like full text. It seems like there's a difference. I would rather to have full text. I'm not sure where the difference between texts and full-time. Tweet URL. Let's search for URL URL. The tweet tweeted at is created automatically. And then member is a linked record. But what you need to know about our linked record is if you input the primary field like J Klaus air T was automatically, you're going to try to find the username and associated.

If it can't find it, it's going to go ahead and create that record. We don't have, we know it's going to find something, right, because we know that it's an existing user, an air table. Doesn't I don't think it cares about a capitalization, but that's something we can check. Okay. Let's test that out. Boom. A record was just created and we see J clusters make this a little larger.

Boom. We've got you. We've got your tweet. We've got the URL that we can click into. So you can go ahead and retweet this. We have a creative time and we're associated to the right member. Right. So you have two tweets now, does that make sense? Yep. Okay. I think, I think that's a success folks. I think we can you a little bit of a, we're going to do some confetti here.

I feel like that's it. Uh, so Jay, we've got some confetti trimming down. This is already a pretty cool workflow. If you asked me this is already a  

[00:34:25] Jay: pretty cool workflow. You're absolutely right.  

[00:34:27] Aron: Um, so now is the hard part and the part I've been a little worried about, I will, I will be honest though, is, well, what if Jay tweeted like 50 times, which Jay does, right.

And I do, or they're trying to game the system. Right. We don't want it to count every tweet. Right. So we only want it to count. One tweet every 24 hours. So, you know, a reflex that I had was like, oh, it was create a number of tweets, which is account field of tweets, but then J tweeted twice in 24 hours. And that's, that's no bueno jail.

And he gets one point every 24 hours. So this is where the hard stuff comes in, not the hard stuff, but the, the, the use your brain stuff. Are you, I saw you had a question there, Jay, are you thinking about this? Yeah.  

[00:35:31] Jay: Well, I'm thinking ahead here. Uh, from like a, just a layman's situation, like let's say I get my tweet in today at 6:00 PM and then tomorrow I do it at noon.

24 hours will not have passed, but in my mind I would count that as two. Right. So I feel like you're going to do some math here and I'm worried. I'm wondering if that's going to box us in.  

[00:35:53] Aron: No. So I'm going to assume. That you have a calendar 24 hours, right? It doesn't have to be, see a penny. Um, you don't have to be as long as in a calendar day.

You tweet once. It doesn't matter if it's a 3:00 AM it doesn't, you can tweet once at midnight, five minutes before midnight, and again, at 10 minutes past midnight. And you're good to go. Yes. Okay, perfect. So the first thing we need to kind of do is we have active members, right? So we have active members, which is just checking whether they've signed up in the last 24 hours.

So let's do the same thing here, which is, was this tweet today because we're going to have tweets from not tomorrow, that'd be weird, but like from two weeks ago or things like that. So we need to say, is it today? Right. And what we're going to do is the same thing we did before. Usually, if this was a different setting, Jay, I would ask you to write this formula, but I'm not going to do that.

Uh, so we're going to say daytime diff between tweeted at actually there's a better way of doing this, not daytime F which will take less resources, which is, is no, no, let's do it like this, but the better way to do it is actually probably saying is the day

of tweeted at equal to you the day of today. That's your return one for all of them. Yeah. Okay, great. So this is actually much less intensive. This just says is the day of tweeted at right here. Boom today is just a function that returns, you know, today. Uh, and you're taking the day of that. And you're just saying, is it equal to this?

So every 24 calendar hours, it's going to update, and this will be zero. So if we want today's tweets,

we can go ahead and filter where today is equal to one. Now all of our tweets were today and I can't change that cause I can't change the creative time. But, um, I promise you tomorrow, this will be zero. So they will not be in this view. Right? So the next thing, let me know if that makes sense, folks in the chat.

I'm sorry, I'm going a little quickly, cause I know we have to write a script and we've got about 15 minutes. So the next thing to do is for the number of days tweeted, I need to go into these linked records right into Jay's two records, right? And count one. And even if you've tweeted 10 times, let's only count one.

So we're going to use a script that literally for every member that is active, goes through today's tweets and does plus one, two days tweeted. If we find that person has tweeted, otherwise it's not going to add it makes sense.  

[00:39:09] Jay: Makes sense. It sounds like crazy magic, but I'm excited to see this scripted action.

Uh  

[00:39:14] Aron: it's it's it's weirdly difficult, not difficult. It's like weirdly un-intuitive right. But, um, we did a learn air table scripting series. This is actually the same thing as finding duplicates, right? So you're saying, huh? Take every record. Try to find a record with the same username. The only difference is you're not looping in the same table.

You're looping in a different table. So duplex. And finding whether someone has tweeted is exactly the same thing. Um, awesome. So Coleen's on board. Love it, love it. Let's see how let's see how my, my scripting skills are. Um, so I'm happy. You're warming up to Jay. That's good.

Um, so every day, so what we want to do is run this script every day and I I'm running it 15 minutes before midnight Eastern time, you can make that like more precise. You can run it at midnight, but I was worried that if you run it at midnight tweeted at might update. So I want it to give us the buffer of like 15 minutes so that if, if you're running at a midnight and it runs at midnight, 0, 0 1, you're actually looking at no tweets.

So 15 minutes before. Uh, you can let folks know that they have until 1145 to tweet, and then let's write a great sum coat. Okay. So I have the script, it's about 30 lines of code, but I'm going to attempt to rewrite it all, you know, live and I might regret those words. So the first thing we want to do is get records from both views, right?

So from the member active members view, and then from the last days tweets view, right? And then we want to loop through and identify members that have tweeted in the last

24 hours. Does that make sense so far? Makes sense. And then we're going to increment their days tweeted. Right. So we're going to update their records and say that they have, you know, one extra day. So let me know in the chat, if the text is large enough, or if this has been, I think this is the best, probably  

[00:41:44] Jay: this looks good.

Okay. On  

[00:41:45] Aron: my screen. Okay. Let me know in the chat, if this looks good to you. Okay. So this is the easy part. And you were about to get a crash course in air table. So all tweets, we're going to go get the table. This is going to initialize and give us like, okay. All tweets represents the tweets table. And then actually let's start with members.

I think members is more important. So let, what did I call that down here? I called it all members make sense. All members equal base get table members, right? It's like a, it's almost like plug and play. I appreciate that calling and happiness has is good. Um, and then we're going to initialize the view, let active members equal go to all members, get the view of active members so far I blank stare J it's.

Okay. This is all new.  

[00:42:58] Jay: I've never seen any of this before. This  

[00:43:00] Aron: is crazy. Oh, you're going to open up a whole new world of air table. Um, and we're going to call, let met. We're going to call this a man I'm really bad. So all active members, does that make sense? Maybe not equal a weight. And then we want to go to active members.select records, a sync.

I think, and that should give us all the records. We're just going to console that log that just to like, see what it outputs. So this is just plug and play. Right? So this is just like you do this every time

[00:43:44] Jay: we testing this right now. Is this a big moment?  

[00:43:46] Aron: Yeah. No, it's a small moment. Hopefully it works. Oh, I, uh, I shouldn't have done this. Okay. Yeah. Okay. So we have an array which has the record IDs and then the records. So, uh, all active members that records gives us the primary field, the record ID, sorry, and their name.

We can then get all other information about the record, right? So this gives us every single record, all the records from that table. We're going to do the exact same thing with the tweets. So let all tweets let, uh, we do, we have a view we did want, so we. Yesterday tweets equal all tweets dot get view. And then we want today's tweets.

We should call it today. Tweets. That makes more sense today, tweets, right? And let's console dot log today. Tweets were like 60% of the way there and it's working so well. All right. We forgot to get, uh, uh, the actual let all today tweets equal await all tweets. Uh, today we want today tweets.select records, a sync, and now let's get the record.

So this, this is going to return the records.

Okay. So we have our two views, right? So we have one view with you. Uh, this is the all active members, you, myself, and a dude named Jeff and Jeff do named the dude. Not tweeting though. He does. He hasn't tweeted. So his it's good to have some words. And then in this case we have three tweets, two from you and then one for me.

Right? So the output, what we want to end with is that I have one, you have two and Jeff stays at zero, right? Let's see if we can do that. So this is where things get a little tricky. So we want to do this is where they get tricky. Has this been tricky? This is all just sure. Yeah. But this is like, this is all things you have to do every time.

So it's not even, I want to worry about it. Um, I, I feel like a lot of people are going to be like, when did automate all the things become a place where we just write scripts, but you get to a point of complexity where it's necessary. Um, so yeah, I'm excited about this. Okay. So we want to loop through every single member and identify those.

So let me kind of say this for every member. So for Jay, myself, Jeff, go into tweets and then check every tweet, whether the name in this view, sorry, in the today view. So go to today's tweets and check. Okay. Is Erin here? So we're going to start with Jay. Sorry. Oh, Jay is here. So that's plus one, I don't care if you've tweeted 20 times or 30 times, so I need to go through each one of these and check whether there's a match on the name in today's tweets.

How you write that in JavaScript or an irritable, I guess, equivalency of JavaScript is that we're going to start, let, uh, members tweeted equal. Okay. So we're going to start with, let me just, um, uh, boom, boom, boom. Trying to make sure I'm doing the right thing. Yeah. Awesome. Okay. So we want to start with our members table, which is all active members.

We're going go to the records. So when you click a.in JavaScript, that means like go to, so when you see this, like this little. We want to go to the records, right? So then, then we're in this array, which has three objects in it. And we want to go ahead and filter what this is going to do is that it's going to go through each member and find all the matches that you want.

So here, I'm going to say, take every member and then do this thing. The little arrow means do this thing, right? And now I need to go ahead and filter returns, all successes. That's what you need to know. Uh, you're never going to have to write this script again. So, uh, don't worry about it if you're, if you're losing here, but if you're ever interested in learning scripts, uh, this one's actually a really good one to start.

Um, and then we're going to say, okay, let, how would I, how did I call this before? I'm really bad at naming. Uh, okay, well we just need to check, right? So we just need to return. In all today, tweets right. Go ahead and go into records and then find, so the difference between filter and find is that fine.

We'll only find one, whereas filter will find all. And what find will do is that you can give it a condition to say, okay, return all, tweet it, give it a tweet where we've got to give it another return here where the tweet.name is equal to three equals that's the match to member.name.

And does that make sense? Boom, boom, boom. I think that makes sense. Oh, I'm missing a bracket.

There we go, okay. Let me read that back to you. If, if you ever want to edit this, this is going, please go to all active members, one by one and return all that have a condition, right? And we're going to return in that one, all the ones where the tweet is equal to the member names. So it's, we're going to return return all members where a tweet has at least one match, which is filter.

That's my best way of explaining it. Let me know in the chat. If that makes sense, I'm happy to answer some questions. Um, and yeah, let's go ahead and console dot log members tweeted. So I just want to read this one more time. We're going to all active. We're finding all tweets where the name is equal to the member name we're looping over and find is only going to return once.

So it's going to give us one. Array and then filter will return. All true. So all the ones that match. So I think we should be good. Okay. Yeah, we are. So we have two boom. So we have two objects. We have two objects in our array. We have you, and we have me because we are the two that tweeted, right? So now it's actually quite easy.

All we need to do is restructure this such that where we have the record ID, right? So this is your record ID. This is mine. We just need to say increment days tweeted by one. So currently days tweeted is zero for everyone we're saying TIG days tweeted and do it plus one. And how you update records in air table, you know, via scripting.

Let me go back here, edit the code. As you construct this array, you just update this to have a new day's tweeted, which has plus one. And then you're going to go ahead and update those records. So how we're going to do this, we're going to call this, let updated members here. Let's just go search and it's update records, a sync.

So you need an array where the first or one of the fields is the ID, this one right here. So we're going to input that at, in fields. And those are the fields you want to update in our case, that's going to be days tweeted. So let members update equal to, oh, we're going to do a map. So for every man, I feel like I'm teaching.

Like, I'm going way over your head here, but, uh, I'm here for it. Okay, good. Okay. So we're going to go through members too. We're going to use a map map is like a way of looping and creating a new array from an existing array. So we're going to go pass and member for every one of those member. We want the following structure.

We want the ID to be equal to member.id. Comma. I think we might have to do this. I'm not sure. I think so. Yeah. That makes more sense. And then, oh, it doesn't seem to like it. Oh no. Yeah. We're yeah, that makes sense. And then we want a fields to be another, oh no, maybe it is just ID

like that. That makes sense. Okay. And then fields like that. Does that make sense? It doesn't seem to like it. What did I have before? Oh, I need a return. Okay. That's why these tweeted. Yep. Boom. So I knew and she actually returned them.

I think it's going to be, so what would that a mess up here? A map member. Oh, I okay. I'm missing a bracket over here. And in men writing code is, it's so  

[00:54:28] Jay: easy to be missing a bracket. It's such a pain.  

[00:54:32] Aron: Yeah. Okay. And then I want to return that object. There we go. Return the object there. That seems much better.

And then here, I want this like this boom. Okay. So I'm saying for every member return an object that takes the ID from the one we're looking at and an inputs, the following field. So the field that we want to update is days tweeted. Let me make sure I have that written out. Well, day's tweeted.

Days tweeted. Go ahead. Here's a, here's the  

[00:55:15] Jay: question for you. If I were to change that column name at some point, would this days tweeted label update in the script or would I have to update the script as well? You  

[00:55:24] Aron: would have to update the script. You don't have to update automations. You do have to update the script.

Uh, and now we want member dot gets sell value. So getting the existing number of days that they've tweeted, right? And this is days tweeted. We should have it as an option. Uh, oh, it's member, there we go.

Boom days tweeted plus one. And that should create the exact same array we have here with the ID. But the difference is that you and I. Should have a, uh, plus one of days tweeted and we're not going to have the name cause we're not passing that in boom let's console dot log update members.

We are so close folks. We're so close.  

[00:56:23] Jay: This is amazing.  

[00:56:26] Aron: Here we go. Boom. Fields day. Sweden has one day's feel is one. So we're almost there. We haven't updated air table just yet. That's the last step. So the last step here is async um, I'm happy that this has extended that to other people. I'm just glad you're excited cutter.

Yeah, I'm also very, I, if I wasn't terrified of writing code on the stream, I would also be excited. So we want to go to, uh, our active members or no to our table. All members.

Dots update records, a sync. And all we have to do is pass in update members. It's going to go ahead and find that ID and update the field that we wanted to. Boom. Now, this is where we know that it worked. So if we test this boom, oh no, it's not. Async it's a wait, there we go. You gotta have a, you gotta have some gotta build in some, uh,  

[00:57:31] Jay: suspense, big money, big money, big  

[00:57:33] Aron: money.

Okay. Now we're going to check successfully. Boom, boom.

wow. This is going to automatically,  

[00:57:48] Jay: yeah. Uh, every evening. Right? So people would expect to see this change once a day.  

[00:57:55] Aron: Yes you could. So you could run it. Right. So if you change the formulas to be every hour, right? You could do hours every hour. And I think it should work if you update today two hour, but then you're going to have to do an additional automation.

What happens if someone tweets multiple hours, but you can fix that. I think with a script pretty easily. So, uh, once a day is fine. Yeah, I think so too. So now this is all automated. So what like looping doing the whole thing here. If someone signs up to this form, they're automatically added to this test list.

Whenever they're they tweet it gets added to this active members, uh, And those are w w they get act when the tweet, every tweet shows up here and then a script checks, whether you've run once a day and then updates this number. Now, some questions here, which I think are very pertinent. Okay. So Colleen asked if you have folks in different time, zones might be better to do once per day.

Totally agree. You can adjust for time zone, so you can ask as input their time zone, and then in this formula, which is today you factor in their time zone. So you can say, is there tweeted time, right? In the last 24 hours based on GMT, for instance, right? So you could do that as well. Um, you can also, you, you really, you can do whatever you want.

J so, uh, there were some questions around. Uh, how do you do percent active and things like that? You can do that with some formulas. Um, yeah, that was amazing. I had a lot of fun.  

[01:00:01] Jay: Amazing. Uh, just stupendous. Um, one, one last question. Something I can learn afterwards, what happens? Um, let's say it goes really well.

I get hundreds of people who are doing this. My base is going to get massive. Yeah. How do I manage for that?  

[01:00:19] Aron: Yeah, a few ways. So, um, one, so there are two, I think the easiest way to manage for it is to think about there's a 50,000 limit. So you're not going to get 50,000 members, but you might get a lot of tweets.

So one thing you could do is if you're, once you've run this a few times in this step, you can actually go to a weight, uh, all. Tweets dot delete records, they sync, right? So you can delete all records that are because there's no, like, you don't need to keep the records after the day. So you can delete those records in the script to say, okay, I don't need them anymore.

I've updated your number. Um, you can minimize the amount of fields that you actually track. So, you know, base performance yes. As a function of records, but it's also a function of, of fields. And one that's very consuming is today. So you may want to rethink that in a different way. Maybe Zapier could flag whether it's today or not.

Right. So you can do things like that and use Zapier to your advantage as well. Um, when say once a member is no longer active, you can remove them from the list as well. That way they won't. Yeah, because even if you're inactive air table is still tracking tweets that you're sending. So there's a lot of ways that you can make this much more efficient.

Uh, and I hope that you will have to, because that'll mean you're super successful. Uh, but we'll, we'll, you know, it's definitely manageable.  

[01:01:58] Jay: This has been incredible.  

[01:02:01] Aron: Well, appreciate that. Hopefully folks followed along. I know that was a, there was a little bit of a, a little bit of scripting, which I do a little less, but, uh, Jay, I'm really excited.

I think this is a cool workflow. Uh, I'm happy that I could contribute in some way to yours.  

[01:02:19] Jay: I'm excited. This is, this got me all the way. The goal line. I'll be able to launch this entire projects within a week now. Amazing and have this be live and happening and, uh, people playing around with it, trying it out.

[01:02:33] Aron: Awesome. Well, listen, if you want to, if any folks in the chat want to participate, uh, tweet one hundred.com. Um, And you will, you will see the workflow in action. Oh, some other things, sorry. I got carried away. Um, what you could do is automate things from the leaderboard as well. So when they sign up, you can give, you know, you can send them an email with a link to the list and the leaderboard, uh, you can tweet from Zapier, right?

So you can imagine like what, you know, if you want to at the beginning kind of, uh, encourage, well, cause you can hear when someone signs up, uh, to you right here, which is the create, uh, here, you can add a step that you tweet from your account to welcome them, right. Or maybe you want to do that in air table where you're like, oh, like tweet little checkbox.

You can have an automation that runs that tweet post to Twitter from air table. So like welcome this person. Once you've done a little bit of checking. So the world is your oyster J uh, and I'm happy that I can give you a little bit of help along the way,  

[01:03:53] Jay: dude, a lot of help, a lot of, bit of  

[01:03:55] Aron: help. Amazing.

Okay. Well, that's it for this week. I'm going to be off the next two weeks. Uh, going on a little vacation here. Appreciate all of you. Colleen Connor. Uh, James, I saw before Jay Stephen, uh, really appreciate you all joining. I hope to see you back in September. I've got big plans for September and yeah, really excited to be back on the stream already.

So thanks. You all have a great day, Jay. Huge. Thanks to you again for coming on. And I can't wait to see this work now.  

[01:04:24] Jay: Same. Thank you. Thanks everybody for joining in.

‍