Keyword Tracking Daily

Kendy511 · ‎Mar 22, 2021

Hi guys, I’m current tranform sheet to airtable. I wanna to use it as database tracking daily. But seem a little bit misunderstanding in building 1 base in my case
In my case, Each product has ~ 1000 keywords research with some fields: {Avg search volume}, {Ranking}, {Cost} … each values changing every single day.
I dont wanna dedupe 1000 keywords each day from importing Csv file. i just wanna tracking those 1000 keywords within those 1000 records

Rose_K · ‎Mar 29, 2021

Hello @Kendy511!

To make sure I understand your base setup: are you looking to track each product (including that product’s 1000 keywords) separately or are you looking to track the changing keywords for each themselves for each product?

For context we have a Deduping App you could possibly use to automatically help with deduping but I wanna make sure I understand your scenario a bit better before making any further recommendations.

Kendy511 · ‎Mar 30, 2021

I have 2 tables:
1 table: Product
link to 1 table: Keyword. In keyword table, we have several field: {Status single select: Bidding, Canceled, Consider…}, {Bid price/ day}
Each Product have more than 1000 keywords and be imported in the first day of my marketing campaign.
Daily, Each keyword get a new bid price, and new status from new excel file to this table. I wanna track those changing keywords (price changed, status changed), but storage 1000 keywords/ day seem can let me out of limited records fast. Can you suggest any solution for this?

Rose_K · ‎Mar 30, 2021

Hey @Kendy511!

I could see how that would be tricky. If the keywords are fairly consistent each day and their attributes just change (e.x. the keyword “dog” is the same for each day of the week but the bid price changes from day to day) you could potentially update the existing records instead of creating a new one each time.

This should be possible using Airtable Automations. A possible workflow could be:

A new row is added to a Google Sheet (using the Google Sheet Trigger)
If a record matching that new row exists in your selected table (using the Find Row action)
You can then update the matching record in your Airtable base(using the Update Record action)

You could also look into using an integration service such as Zapier. :thumbs_up:

Let me know if I can help provide any more information, and if this helped answer your question please mark it as the solution :white_check_mark: .

Kendy511 · ‎Mar 31, 2021

About No.2: So you mean I can setup automation in airtable then import and storage data in google sheets. Then automation will auto create record when a new row in google sheet created. So what if dedupe keywords in airtable, do we have any way to setup auto change bid price when dedupe existed record (keyword)? I seem stuck a little bit in setting Find row action in this case

Rose_K · ‎Mar 31, 2021

So the workflow I described would mean putting the new bid price as a column in Google Sheets (here is a simple sheet I made for this example)

Then here is my corresponding table in Airtable:

Then in order to find the record in Airtable that matches the Google Sheet row you would need to do something like match the name of the row in the Google Sheet to a record, like I do below:

This would then allow you to then setup your “Update record” action with the information from the row in the Google Sheet, and the Record ID of the record you found in the previous step:

One thing I’d like to point out is this automation would only cover if a record existed in Airtable with the same name (or in your case keyword) as the new row in your Google Sheet.

If you added a row where the name didn’t match:

Then the “Find Records” action wouldn’t find anything, which would eventually cause your automation to fail:

You could create second separate automation to create a new record when a new row was submitted to a Google Sheet(this would be the action after setting up the Google Sheet trigger):

Which would ensure that if a keyword is new a new record is created, or if it already exists, then the existing keyword is updated:

Let me know if that clears things up or if I can help answer any more questions about automations!

Kendy511 · ‎Apr 02, 2021

Love the way you’re supporting me.!
After following your guide, new problem’s appear:
1/ dedupe records
Do we have any function in Find record that lookup dedupe records, choose the newest updated date to keep and delete the other one?
2/ and some several time, it automation create new record even before I fill down bid price in google sheets, so that the automation doesn’t update record correctly

Rose_K · ‎Apr 02, 2021

Of course! I’m so glad I can help with building your workflow. I always love seeing how people use Airtable in their lives.

I actually just learned we also have a CSV import App which could potentially help make your original workflow with CSVs work the way you intended.

I’ll definitely help with this Automations workflow though, especially if it’s working for you!

For deduping- if the name you put in the Google sheet doesn’t match the record value in the Keyword Name field exactly then it will think that is a separate name and create a new record. This includes spaces in the beginning or the end of the word, it’s a little finicky that way.

We do have a couple of tools that could help you dedupe after the fact though, we have the Dedupe App that can help merge all occurrences of record names that are slightly different into one.

The way this automation is triggered is when a new Google Sheets row is created. The delay on the trigger isn’t very long- so I recommend building your keywords in another sheet and then copy and pasting them, once complete, into the sheet linked to the automation.

I hope that helps, and please don’t hesitate to let me know if you have anymore questions!

Kendy511 · ‎Apr 13, 2021

Appreciate a lot, thank you!