加拿大外贸
如题，有的话，发到我邮箱，[email protected] .谢谢评论
baidu了一个给你，其实了解了原理，你自己可以利用excel的小技巧完成 ，下载后可以试用，灵活应用确实能给力不少
评论
转发一个老外的博文
Group Keywords Using a Keyword Density Macro
I converted this Excel VBA code I found into an Excel keyword density tool. This Excel tool can analyze the density of a keyword list and output the most common frequencies for 1 word, 2 word and 3 word phrases.
Let me give you an example of when to use this.
Let's say you checked out a search query report or exported a huge list of keywords from Google Analytics (append &limit=50000 in GA, export as CSV, not CSV for Excel). You want to find the best way to group these keywords into a common theme. You take all those keywords, put them into column A of this Excel keyword grouper, remove duplicates, then hit the "Find Density" button.
Magically, the keyword frequency and count appears! Thanks to this Excel macro, you can now group keywords into common themes much easier.
What you can do is take these keywords, import them into one adgroup in Adwords Editor and begin grouping the keywords by the highest frequencies. This is WAY easier than staring at your huge keyword list and trying to find common themes manually. It tells you what keyword frequencies are most common so you can focus on keyword grouping.
Download the Keyword Density Grouper for Excel.
If you see a "Security Warning" from Excel to enable content, just allow it. Nothing sneaky or tricky going on here. There are no weird viruses to be worried about, I assure you. Hit the Enable Content button.
Note: if you're having problems opening the Excel file, open Excel first, then click the download link above.
http://www.ppccampaigngenerator. ... ensity-grouper.xlsm
评论
Tips For Growing Keyword Seeds With Excel Formulas
涉及使用的模板文件 http://jlh-marketing.com/wp-cont ... word-worksheet.xlsx
Have your seeds germinated yet? Last time, we talked about keywords as “seeds” of ideas; a single keyword to represent an entire keyword cluster. Now it’s time to make our seeds grow.
Keyword Seeds Germinating
Germinating your Keyword Seeds
Using the example of Artisan Construction Services again (thanks!), we’ll walk through the process that I use to grow a single seed.
In this case, let’s use “deck”. To begin with, I want to think of the types of decks my client might build:
Composite
Wood
PVC
Hardwood
Cedar
Then I am going to use what we learned about the client already to develop a list of verb modifiers:
Build
Building
Builder
Replace
Replacement
Replacing
Construct (wait, I’m not going to use that one, see below)
Constructing (not this one either)
Construction
I was able to generate the list above based on conversation with the client and a working knowledge of the deck building industry. If you’re not familiar with your client’s industry, you may want to research this more – look at forums, the discussions part of Google Search, and Yahoo Answers, for example.
Categorize Keywords By Intent
After I build my list of modifiers, I go back and think about intent. I’m not going to use “construct” or “constructing” as modifiers because I think the intent behind them is DIY. Construction probably has some DIY in it as well, but it might glean several useful keywords. I’ll make a note to filter those after the fact.
By the way, don’t go above 10 total modifiers; it gets to be too much to work with.
Since the focus of this post is how-to research, I won’t go into all the modifier sets I’d use, but normally, after I did this set, I’d go back and do one for decking and maybe also for specific brands (Azek, Nichiha, etc.) When you do this for yourself, keep in mind that de-duplication will be important (see below).
Concatenation
(Yes, it is a word, and a useful one for search!)
Next, I use a tool like the one found here to concatenate (that means put together) the keyword seed with the modifiers. I prefer not to use the “concatenate” function in Excel, because sometimes it is limiting. I also find this formula to be infinitely easier to remember and manipulate:
Let’s say you have the following cells:
A B C
build composite deck
You want to get the phrase [build composite deck] out of this. Use the formula A1&” “&B1&” “&C1.
The &” “& just adds a space between the two cells’ values. If you want to lock down a value, as in the list below, so that you could get the phrase [building wooden deck]:
A B C
build composite deck
building wooden
Use the formula the same way as above, but lock out C1 with dollar signs: A2&” “&B2&” “&$C$1.
You can “lock out” either a Row or a Column value.
Ok. If you haven’t already clicked to open the spreadsheet I mentioned above, do it now. This will show you my initial list of keywords based on the modifiers I listed above.
Notice that because I didn’t use all 10 possible modifiers, my actual keyword list ends at line 40. Just don’t use the incomplete keywords at the bottom.
Getting Search Frequencies
Go to the Google Keyword Tool. Login if you have an Adwords account; it saves you from having to enter the captcha for each search.
Cut and paste the first section of keywords from your list into the keywords field. Make sure the box that says “only show ideas closely related to my search terms” is NOT checked. Click the box on the left for “exact match” (and turn off “broad”).
After you click “Search” and get a list of results, don’t even look at them. Just click the “download – all – CSV for Excel” link.
Repeat these steps for each modifier, but when you get the CSV’s for the other modifiers, paste them into the original Excel sheet so that you have one big list of keywords in one Excel sheet.
Once you have your full list, you need to de-duplicate.
Steps to De-duplicate
Make sure all of your data is in the same format. I like to find and replace (CTRL-h) all of the brackets with nothing. Find=[, Replace=nothing (leave it blank). Then repeat with "]“.
Sort by global monthly searches ascending. Delete any rows where global monthly searches are “0″ or “-”.
Now sort by Keyword A-Z.
Go to Data – Remove Duplicates. Specify (check) only the “Keyword” Column. If you’re using an older version of Excel, it’s a little harder to do this – see these instructions to de-duplicate.
Removing 0-Value Keywords
Now we’re going to look for keyword patterns. The first obvious one I see is “how to”. Anyone using the word “how” is not interested in paying a contractor (at least not at this time).
Use the following formula in Column E: =FIND(“how”,A2). This will “find” anywhere in A2 that has the text string “how” and return the character position it starts at. Trust me, this is useful.
Now copy/paste the formula all the way down your list.
Select Col E and copy it.
Paste special for values only in Col F.
Sort Col F. ascending.
This brings all of the keywords with “how” to the top of the list. Simply delete out these rows, and then look for another pattern.
Hint: to easily look for another pattern, just select all of Col E and find/replace the string you’re looking for (example “how”) with the next one you need (example “design”).
When you think you’ve removed as much as you can, you will probably be down to under 100 keywords. These are the ones you really care about. Take just a minute to scroll through the completed list to make sure you didn’t miss anything and delete it.
Rinse and repeat as necessary. It takes a lot less time to do it than to explain it. You may also enjoy these easy PC/Excel shortcuts:
Shift+space bar = select an entire row
CTRL+- (that’s the minus sign) = delete an entire row
CTRL+h = open the find and replace window
ALT+e, then s, then v = copy and paste special for values only
原文链接 http://searchengineland.com/tips ... cel-formulas-114846
评论
MARK
评论
不错挺好!!!
评论
good
study
评论
Thanks, shared it with email
评论
thanks for this idea and your kindly information
评论
ggggggg
评论
good
评论
mark
评论
很实用！
评论
马克
评论
省了不少的脑细胞感谢管理员的共享，真的很实用
评论
实用，谢谢分享。
评论
真的很不错，省去了很多时间，顶。
评论
需要标记一下，我这账号为啥手机的客户端能上，妈蛋的电脑上不了啊！！！！
评论
应该不会呀 有提示为什么上不了么
评论
学习了，主要是运用VBA的方法来翻译生成关键词，很适合初级用，更适合在不断开发外贸业务的朋友们
评论
高超技术呀
评论
推荐一款应用吧，不需要这样麻烦的，www.sesamespell.com,刚刚上线。里面有一个功能模块，可以一键把输入的中文信息变成其他12种语言，同时不改变格式～感兴趣的可以试试
评论
谢谢分享。
评论
哇呜 很有用啊。
评论
楼主收到了的话可以发一个给我吗？我是外贸新手。谢谢！499641701qq.com
评论
Thanks !
评论
楼主，推荐你到海商网（HiSupplier）精准贸易平台上注册一个免费的会员账号，然后发布产品信息的时候，就有一项自动生成产品关键词的功能，你可以尝试一下。
评论
mark下明天试试
评论
收藏
评论
MARK。。
评论
关键词生成器是干什么用的呢？
评论
希望我也能活用
评论
谢谢管理员分享，学习一下。
评论
永远都觉得自己知识不够用，我的能力还不够支撑现在的工作是说明我该安心学习了。
评论
Good!!!
评论
一直在找 先下下来研究一下
谢谢分享
评论
good！
评论
楼主，能发到我qq 邮箱吗？外贸新人，在此谢过！770474729@qq. com
评论
收藏-下了
评论
很好啊
评论
马克，谢谢～
评论
mark
评论
You are a great help.mark
评论
QUOTE:vip 发表于 2014-2-22 16:15
Tips For Growing Keyword Seeds With Excel Formulas涉及使用的模板文件 <a type="0" href="http://jlh-marketing.com/wp-content/uploads/2012/03/keyword-worksheet.x...
马克
评论
m一个
评论
好东西，谢谢楼主
评论
马克下先
评论
麻烦群主发我一个，[email protected]
评论
马克
评论
不错~~~
评论
我也要一个[email protected]，谢谢
评论
强大啊，谢谢
评论
http://www.keywordtooldominator.com/k/google-auto-suggest/ 这个是长尾关键字利器
评论
这怎么用啊，我怎么看不懂、
评论
我看不懂这是啥意思，你能教教我吗？
评论
马克
评论
不错啊，mark一下待用关键词工具汇总
http://bbs.shanghai.com/keywords.php
评论
感谢楼主的热心分享
加拿大电商做区块链为什么都注册新加坡基金会 目前做区块链项目的客户都会通过注册基金会来作为项目的主体，为什么都在新加坡注册基金会，首先性价比高，而且新加坡市场也比较成熟，项目 加拿大电商露天电影首发——葡中双语字幕《中央车站》 http://bbs.shanghai.com/thread-1714999-1-1.html screen.width*0.7) {this.resized=true; this.width=screen.width*0.7; this.alt='Click here to open new window\nCTRL+Mouse wheel to zoo
·中文新闻 昆士兰青少年在穆伦巴唐斯（Murrumba Downs）开着被盗汽车闯红灯
·中文新闻 澳洲航空在悉尼机场发动机“爆炸”迫使紧急迫降