Single out a domain’s most recent ads

Having years of a domain’s history and ad evolution over time helps you create a strong, well rounded strategy. It’s part of SpyFu’s strength. Then a customer challenged us with a different take  — how can I single out the keywords they bought just this past month?

This one’s easy. Watch.

Let’s say that you want to see what advertised on this past month. A quick reminder: the Ad History tab is best for an in-browser look at a domain’s most trusted keywords and how their strongest messages have evolved over time. However, if you’re specifically set on having a list of keywords they ran last month, the standard PPC keyword list is the best way to go.

1.  Download a domain’s PPC keywords

Type into the search bar and choose the PPC keywords tab. There’s a drop down option for PPC Keywords. Click it.

Exporting keywords from the PPC keywords page in SpyFu

2. Export the list to a spreadsheet

The point here is that you want to be able to sort. This is what breaks apart your most recent keywords. There is an export option just above the chart on the right hand side. Choose “Export as CSV.”

We’ll call this next one Step 2.5. Open the spreadsheet…

3. Sort by the “date” column

See that data column labeled “Max Search Date ID?” It’s meant to power our ad timeline on the site — not much to look at in its spreadsheet form. However, if you sort by this column, (largest value to smallest) you’ll see a pattern emerge.


Here’s an example of a code from that column:


The first 4 digits (2015) are the year. The second group of 4 digits  (0901) represent the month only. In this case, it means that we saw this ad run sometime in September 2015. (It would have been released as new data to in early October.)

4. Select all of the keywords from the most recent month

Just scroll until you see a change to an earlier month. Here, I’m looking for August to mark my break, so once I get to 20150801 (bold was just for emphasis) I’ve hit the end of my keyword list for the most recent month.

Since it is a date-related category, you can sort and filter out any keywords that did not run during the most recent month.

If any of you spreadsheet wizards know a better shortcut to single out the month, please drop a note in the comments. I love learning new stuff.


Recommended Reading

Intro into internet marketing

Easy way to start a new PPC campaign

How to start an AdWords campaign from scratch

  • Larry Cobb

    If you format the data as a table, then you can easily create a pivot table using the max_search_date_id and the term. It is easy to sort it either way. You can also slide the term over to sum area and get a quick count of terms by year and month. When the data is formatted as a table you also get some powerful filtering options, and it is easy to filter by max_search_date_id.

    • AgentSidra

      Excellent idea about adding sums. One caveat I should mention: the date is the most recent month shown, so if they ran “leather dog leashes” in October, September, August, July and June, the keyword will be tied to the October date as the most recent one. Fortunately, Ad History (and its exports) solve that issue if you want to count by month. Overall, great tip + added benefits with sorting and filtering. Thanks, Larry!

  • JameSEO

    Nice, this will be a new thing I report to my clients; # of new keywords added to campaign based on competitor findings.
    Thx for the I-should-have-been-thought-of-that-right-in-your-face-tip! 😀

    • AgentSidra

      Count me in the I-should-have-been-thought-of-that-right-in-your-face camp. We only thought of trying this after a customer asked us how. I’m glad it’s a bonus for you, JameSEO!