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
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
-
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 -
Why would on-site search queries show up as referral traffic?
The site analytics have been set up for over a year and suddenly last month there was a huge spike in referral traffic (1100+ sessions). Upon further investigation, the majority of it was coming directly from internally, either as mysite.com or search.mysite.com and the landing pages from the referrals are all /search.html?query=* This was never an issue before so I'm trying to understand what could have changed. I'm following up with the client to find out if their dev team may have changed anything related to their search engine but I'm wondering if there may be another explanation. A few notes: previously mysite.com / search.mysite.com were not in the Referral Exclusion list. I've added them now but this was never an issue before. Thanks in advance!
Reporting & Analytics | | SEMnMs0 -
Google Search Console (new GWT) - Does a language specific sub folder need its own GSC profile
HI I've got a clients site set which targets 3 language/countries: English via the main site on the domain.com Turkish via a Turkish language site on a subfolder domain.com/tr/ And German via domain.de The devs have set up .com and .de in GSC and is reporting data in both However there's no data in the domain/com/tr GSC profile ! Is that because its on a subfolder so data pertaining to it is being reported in the main domain.com GSC account ? Or does something more need to be done to set up the Turkish subfolder in GSC ? If so what ? All Best Dan
Reporting & Analytics | | Dan-Lawrence0 -
How to interpret search "clicks" in GA?
I am trying to make some sense of the data in the "Landing Pages" report under "Search Engine Optimization" in GA versus the data under the "Landing Pages" report in "Behavior | Site Content". For example, the SEO report says my page http://www.asiantraveltips.com/blog/bangkok-skytrain-bts-mrt-lines/ received 22,000 search impressions in the past 30 days and 900 "clicks" (12.42%). What are these "clicks" when the Content "Landing Pages" report says the same page only got 382 "sessions" in the same period??? What are these "clicks" if not clicks on the search results link that should be reflected in landing page sessions on the corresponding page?
Reporting & Analytics | | Gavin.Atkinson0 -
Is there a way to find out who the admin of a Google Analytics account is?
We have a client who has been searching high and low to gain access to the GA account that is tied to their website, but previous agencies keep stating they don't have access. Is there a way to find out who the admins are on a GA account with the tracking number so we can reach out to that person and ask them to admin the client?
Reporting & Analytics | | marketingmediamanagement0 -
What is the best way of understanding bad backlinks?
I want to building trust link profile and i have to know which backlinks bad for my website. And i have to disavow bad links.
Reporting & Analytics | | umutege0 -
Can you get local search numbers/traffic out of Google Analytics?
With Google's new local search I am more curious as to market penetration on keywords that are now localized to my different US cities. I understand that you can separate out Google traffic based on regional Google domains, but I am curious if there is an effective way to separate out searches and keywords based on a my local US Metros? If google cannot do this, any recommendations on products that can? Thanks.
Reporting & Analytics | | Thos0031 -
What's the best way to track moble traffic / sales from a subdomain in Google Analytics?
Hello, I've been trying to figure out the best way to track traffic and sales for a subdomain mobile site and would appreciate any feedback or guidance. I've set up an advanced segment but have found that I'm not able to source traffic since the segments get disabled when trying to view top content pages. I've also setup an additional sub-profile under the main domain profile in google analytics and have set up an advanced filter using the following guildelines http://code.google.com/apis/analytics/docs/tracking/gaTrackingSite.html In the Profile Settings page, click the Add Filter link. Choose Add New Filter and provide the filter a name. Choose Custom Filter and select Advanced on the Filter type settings. Under Advanced settings: FieldA should be set to Hostname FieldB should be set to Request URI Set the values for both Field A and Field B to (.*), which is an expression that captures all characters. Set the Output To --> Constructor option to Request URI and provide $A1$B1 as the value for that choice. I'm unsure if I've set up the profile / filter correctly, the traffic stats are different than the segment. The mobile profile is also tracking all ecommerce transactions instead of just mobile. Here's the code I have in place. we're using the Traditional code Thanks, Jamie
Reporting & Analytics | | marketing_zoovy.com0