Ways to analyze a 1M rows dataset of search queries
-
Hi,
I have this large dataset, about 1 million search queries with visits, bounce rate and a few other metrics. I'm trying to explore this data to find keyword "buckets" (such as include product name, location name, transactional objective, informational, etc.), as well as explore the density of certain keywords (keywords as in instances of a single word amongst all queries)
My idea was to use Excel and a macro to split all queries in separate words (also clearing punctuation and uppercase/lowercase), then storing this word in a new worksheet, adding to another column the visit counts from the row where the word was extracted (as to give a sense of weight). Before adding the word to the new worksheet, the script will look if the word already existed, if so it would just add the current value of visits to the existing visit counts etc.
In the end it will create sort of a "dictionary" of all the keywords in all search queries ranked by weight (= visits from search query including this keyword)
This would help me get started I believe, because I can't segment and analyze 1M raw search queries...
My issue is: this VBA has been running on my (fast) PC for the last 24hr and it doesn't seem to get to an end. Obviously excel+VBA is not the best way to do text mining and manipulation in such a large dataset (although it's just a 30mb file)
What would you do if you had this dataset and would like to mine the text/semantic as I am doing? Any idea of tools? process?
I'm considering dumping this data into a MySQL db and doing the processing through PHP (the only backend language I'm versed in), and getting the "summified" data stored into another table, which I'll then be able to export to a Excel for analysis. But I'm afraid that I'll be facing memory limit issues and such...
In the meantime, I'm definitely interested into knowing what you guys would do if you had this data and wanted to simply start exploring its constituencies
Thanks!
-
Yeah, Access can process any number of rows. It's Microsoft's database program. You can upload data, and then create queries. They have a design view where you can construct queries in a WYSIWYG fashion, or if you want, you can write your own SQL.
-
Thanks a lot John!
I'm going to try this out tonight!
So, I assume, Access won't have the same processing limitations with 1 million rows, will it?
Once I'll be done with the "discovery phase" I'm going through with this keyword list, I'll definitely use Advanced filters (in Excel) as you recommend to understand keyword groups in details
-
I had a similar problem going through my search query reports. If you're already familiar with VB you could do this with a Microsoft Access database rather than setting up a MySQL one w/PHP. I've been working on creating an Access database that I can import my data into, and have it spit out all sorts of useful info (for example negative keywords and placements), but it's only in its early stages right now.
If you just want to see it for a few terms and don't mind doing it one at a time, in the past I've filtered data like this in Excel without VB using advanced filters. I found that using advanced filters rather than VB sped up the process quite a bit; I'd imagine because it's an innate Excel function. Using 4 filters you can match whole words in the queries. For example, to find queries with "blah", you'd set a filter for "blah", "* blah", "blah " and " blah *". Then you can use the Subtotal command to do calculations over the visible rows and calculate the data.
More about advanced filters: http://office.microsoft.com/en-us/excel-help/filter-by-using-advanced-criteria-HP005200178.aspx
Got a burning SEO question?
Subscribe to Moz Pro to gain full access to Q&A, answer questions, and ask your own.
Browse Questions
Explore more categories
-
Moz Tools
Chat with the community about the Moz tools.
-
SEO Tactics
Discuss the SEO process with fellow marketers
-
Community
Discuss industry events, jobs, and news!
-
Digital Marketing
Chat about tactics outside of SEO
-
Research & Trends
Dive into research and trends in the search industry.
-
Support
Connect on product support and feature requests.
Related Questions
-
Search Console Linking
Hey everyone! Quick question; there have been problems in the past with some of our accounts' Search Console integration becoming unlinked from Google Analytics without us knowing about it. I did some digging into Google Analytics to see if there was a way to create an alert for search queries dropping off, but there doesn't seem to be any options for Search Console alerts like there are for other integrations like Google Ads. Has anyone discovered a way to have an alert or some type of notification set up for when a Search Console integration is unlinked in Google Analytics or if search queries drop off significantly? I'd appreciate the help!
Reporting & Analytics | | ReunionMarketing0 -
Query on Implementing Product Impression Via Tag Manager?
Hi All, I have to implement Product Impression tracking as per this - https://developers.google.com/tag-manager/enhanced-ecommerce#product-impressions but In our product listing page we show 33 products in total. If we need to track the product impression of only those products which are visible currently in the screen. If we have 3 products in one row, then there should be 3 product impressions and when we do 2nd scroll then again 6 product impression and so on. How can we implement this? In this guide - https://developers.google.com/tag-manager/enhanced-ecommerce#product-impressions nothing mention like such things. In my case we I scroll the page on 1st scroll only 33 impression fall down even though I am able to see only 3 products. Hope you understood what I mean?
Reporting & Analytics | | dhisman
Thanks!0 -
The best way to track internal links in Google Analytics
Hi there, we are a retail business and we have invested in quality editorial content which sits in our Blog at ourwebsite.co.uk/blog/ The Blog links to the main site (an online store) and I want to track the 'value' of the blog by how many clicks the blog content generates back to the main store. At the moment we're using this code on the end of every link in the Blog: ?utm_source=Blog&utm_medium=Widget&utm_campaign=FromBlog Does this affect SEO and is there a better way of doing it? Thanks.
Reporting & Analytics | | Bee1590 -
How to get multiple pages to appear under main url in search - photo attached
How do you get a site to have an organized site map under the main url when it is searched as in the example photo? SIte-map.png
Reporting & Analytics | | marketingmediamanagement0 -
Google Making all searches secure - "Not provided" data to increase in Analytics
A lot of you might already be aware of the recent Google change at encrypting all search activity except for clicks on ads. Rand did a whiteboard session on this recently. How is everyone planning to adjust their research data to accommodate for this change?
Reporting & Analytics | | SEO5Team0 -
Do misspelled brand queries count as branded keywords?
Are misspelled brand queries considered branded keywords? I'm trying to segment branded traffic and wasn't sure what to include.
Reporting & Analytics | | IMM0 -
Number of Visitor Entries to page via search engine
Hi, I wanted to figure out the most optimal way to track the number of visitors that comes to a specific page on my blog via search engine only. I know Google Analytics has a "top landing page" filter, but that includes all visitors that comes in directly or other channels. Is there a way to figure out how many visitors a certain page received that was generated through only search engine? Does SEOmoz have this capability?
Reporting & Analytics | | kevinyu10290 -
Whats the best way to separate Google Shopping from regular organic traffic in analytics?
whats the best way to separate Google Shopping from regular organic traffic in analytics?
Reporting & Analytics | | DavidKonigsberg0