Importing Squidoo Stats into Excel

WHY YOU MIGHT WANT TO SAVE THIS DATA: Having a detailed record of your Squidoo income is important, just as a record. Also, by identifying which of your lenses are successful or less successful, hopefully, you can learn what works and what doesn't.

How to Export Your Squidoo Payments Data to Excel

I mentioned this is my Getting the Most out of Squidoo Stats tutorial: you can export your Squidoo payout stats in a format that Microsoft Excel can read.

Step One: Click the main stats tab at the TOP of your dashboard:



Step Two: Scroll waaaaay down and choose a time period (last month, 3 months, life to date) from the right-hand pulldown menu, and "My Earnings" or "My Payments" from the left-hand pulldown menu. Click Go.



Step Three: Click the "Download report as TSV" link that's at right, partway down the pane. TSV is a format Excel can read.



(Note: the triangle by "[Month such-and-such] earnings" is a toggle; click it to make the earnings breakdown appear right here.)

Step Four: Open the chart in Excel! You'll probably want to rename the document; Squidoo always calls the file "Previous Payout Squidoo Earnings" or "Previous Payout Squidoo Payments."

"My Earnings" shows a breakdown of different income sources for each lens, like Amazon, Infolinks, eBay. (Unfortunately Squidoo doesn't put them in separate columns, and they're not always listed in the same order so it's hard to sort). Here's what the Squidoo earnings TSV looks like in Excel:

"My Earnings" TSV Report from Squidoo:


Studying this chart can help you see which of your lenses are most successful (at least monetarily), and then you can compare them to less-successful lenses and see if you can detect any patterns.

"My Payments" shows a lens-by-lens breakdown of where the money went -- to you, or to the charities you selected.

"My Payments" TSV Report from Squidoo:


It's a good idea to check this in case a charity's gotten off the Squidoo Partners list, because when that happens, it goes instead to the Squidoo Charity Fund. More importantly, (annoyingly enough), some Squidoo co-brands like SquidLit ignore your global payment settings and start out as charity lenses. You have to change payment settings in the Lens Workshop to "I want cash!" You can double-check all lenses by clicking the "payment settings for all lenses" link under your dashboard's "Stats" tab, but if you forget, it will show up here, and then you can at least correct it for next time.

WHY YOU MIGHT WANT TO SAVE THIS DATA: Sometimes it's nice to be able to review not just your list of lenses, but your changing interests, your Squidoo progress, and what you've learned over time.

SquidUtils.com's Advanced Squidoo Dashboard, put together by Fluffanutta, lets you view your lens list by various publicly accessible criteria.

Squidoo lenses sorted by "Creation" [date]:


Thankfully, the data from SquidUtils' advanced dashboard is easy to port into Excel.

1. Copy the table of lenses plus creation dates.

2. Paste it into "Paste Your Data" area of Free Text Editor I showed earlier. Or paste it into any text editor which allows you to change the Format to plain text (e.g. TextEdit). This step strips away the graphics, which you probably don't want in the Excel spreadsheet.

3. Select and copy the result. (From your text editor, or from the "Paste Your Data" box above.)

4. Paste it into an Excel spreadsheet.

5. Click your cursor above Column D (Edit | Stats | Info | Ping ) where you see the down arrow, then pick "Delete" from the Edit menu to remove the leftover commands from the dashboard. Voila:

Squidutils pasted into Excel:


You might wish to delete column A as well.

WHY YOU MIGHT WANT TO SAVE THIS DATA: Vanity? Or, maybe, tracking seasonal trends or evidence of search engine craziness.

I don't do this, but just in case you might want to, there's an easy way to grab the day-by-day traffic and lensrank data for a particular lens.

1. Get the Workshop Add-on from SquidUtils.

2. Click "Traffic" under a lens name on your dashboard.



3. Now you're on the summary page for lensrank and traffic for that lens. At right, there's a "By Month" or "By day" toggle so you can get a historical summary of the lens going all the way back, or daily traffic for the last six months.

4. SquidUtils creates a simple table of this data at the bottom of the page:



5. Copy and paste this chart directly into Excel.

WHY YOU MIGHT WANT TO SAVE THIS DATA: Your basic dashboard stats are a snapshot of your progress on Squidoo. If you grab it over time (say, once a month), you can keep a record of your Squidoo progress. See my Greekgeek's Squidoo Stats lens for an example. You can also discover seasonal trends, or discover when Google algorithms or other changes cause spikes or drops in traffic.

Unfortunately, if you copy and paste the data from the dashboard straight into Excel, it looks really weird. We need to convert Squidoo's dashboard data to a tidy, plain-text, tab-separated list. For Mac, there's a bit of freeware that will make this easy and automatic. (PC users, see below.)

SETUP (Mac Users ONLY):

1. Download TextWrangler, a free text editor for Mac that lets us set up complicated search-and-replace macros.
2. Open Textwrangler.
3. Now we're going to set up two macros. We only have to do step 3 once; afterwards you can skip all this:

3a. First, hit command-F (Find) to get the search/replace pane. Click the "Grep" and "Wraparound" checkboxes.
3b. Put the following into the "Find" box, and NOTHING into the "Replace" box:
(\rEdit \| Label \| Stats \| Delete|")
This macro strips out double quotes, which tend to screw up Excel, and remove the Edit | Label | etc strip.



3c. Click the little "G" icon to the right, scroll all the way down, and save this as Dashboard Tidy (or whatever you like).
3d. Put the following into the "Find" Box:
\r([0-9,]+)[ ]+\t([0-9,]+|)\t([0-9,]+) week\r([0-9,]+) today\t([0-9$.,]+)\t ([0-9\/]+)
3e. Put the following into the "Replace" Box:
\t\1\t\2\t\3\t\4\t\5\t\6
This macro strips out linebreaks, weird spacing, and words like "today" and "week" which can make it impossible to sort data in Excel, turning the whole complicated mess into a nice orderly tab-delimited list that Excel can figure out.
3f. Click the "g" icon and save this macro as Dashboard Tidy 2

Once you've saved those macros, they're around forever. In fact I forget how to delete macros. So from now on, whenever you want to grab your dashboard stats and port them to Excel:

CONVERT RAW DASHBOARD STATS TO EXCEL:

1. Copy your dashboard stats into Textwrangler, skipping any headers or footers.

2. Command-F to get the search/replace pane. Choose "Dashboard Tidy" from the little "g" menu and click "Replace All."

3. Choose "Dashboard Tidy 2" from the "g" menu and "Replace All."

4. Select All, Copy, and Paste into my Squidoo Dashboard Stats Template. Kaboom!

Squidoo Dashboard Converted into Excel Format (with extras!):


This might work for PC users as well, if they can find a free, downloadable "grep" tool. The problem is, different tools use different characters to represent line breaks and tabs, so what works in one tool may not work in another.

Unfortunately, I can't find a free PC tool for setting up GREP macros (fancy search-and-replace filters) like Mac's TextWrangler does. Fortunately, there's an online website that will do the same thing.

How to Convert the Squidoo Dashboard to Excel Format on a PC

1. Download my handy-dandy Squidoo Dashboard Excel Template, which includes a nice little sidebar tallying up all your lenses, your lens count for each tier, and traffic ranges.

2. Copy all the lens stats from your dashboard, but not headers and footers. JUST copy the lines that start with the name of a lens and end with the date of the last update.

3. Copy and paste this raw dashboard data to this free search-and-replace pattern editor.

4. Put THIS gobbledygook into the regular expression box:
(Edit \| Label \| Stats \| Delete\s\n|")
Click the tiny checkbox beside the "Replace With" box, but don't enter anything in the pink area.
Click Get Results.



This strips out any spare quotes and that strip of commands we don't want messing up the spreadsheet. Now, we want to get the data for each lens onto one line, stripping out all the weird spacing:

5. Put THIS gobbledygook in the regular expression box:
\s\n([0-9,]+)\s\s\s([0-9]+)(\s)([0-9]+) week\s\n([0-9]+) today
Put THIS gobbledygook in the "Replace With" box:
\3\1\3\2\3\4\3\5
Click Get Results:



6. Try copying and pasting everything from the "Results" box straight into Excel. However, on my computer, it sometiems loses the tabs and tries to cram everything into the first column. To fix this, copy and paste the "Results" into a plain old text editor like TextEdit or Notepad, copy it there, and paste it into Excel.

Ta-da! Click to see larger size:

Squidoo Dashboard Converted into Excel Format (with extras!):


"Extras" include a few cells that automatically tally up the number of lenses in each lensrank tier (approximate, since the cutoffs vary slightly month to month) and the number of lenses within different ranges of traffic.