How To Compile Your Stats Like I Do :)

[Greenguy]

I completely forgot that I promised people that I'd type up a little tutorial on how I compile my stats, so here it is.

1st, there's an xls file attached here. It's the blank sheet that I use when I start a new stats run. You can customize this anyway you like, but this is the one I've made up from the years of me publishing my stats.

So, go to a sponsor, log in & run the stats for whatever time period you choose. I find it easiest to copy & paste the table of results into a blank html file & edit the table by hand (I use Namo's WYSIWYG editor, but you can probably use any html editor or a blank xls sheet) I clean up the table/cells/rows so that there's no background colors, make the fonts all default & unlink everything. Then I delete the rows/columns that I don't need so that I'm down to 4: site, clicks, sales, money

I then copy the sites from the table to the spread sheet under the "Sites" column. Then, the clicks, sales & money totals are all copied & posted into the Clicks, Total Signups & Money columns (these 3 are right next to each other on my sheet, so you can copy & paste them all at once) The sheet then automatically calculates the Overall Ratio, Overall $ Per Click & Money Per PPS totals.

Finally, I put OVERALL in the Sites column & use the AutoSum feature to total up the columns, which also automatically calculates the numbers on the right.

Repeat this process for as many programs you want.

There's also a column on the sheet for Category, which I do at the end of everything - you can do this as you go if it makes you feel better.

One thing that I do as I go is remove bad sites from my listings. I scan thru the results & based on a couple of variables, if a site is performing poorly, I remove it from my admin.

Once you're all done & have all the columns filled in, before you start to sort & whatnot, copy the entire page, paste it into a blank text file (I use Textpad) Then copy it again & paste it back into the spread sheet. This will change all the numbers from calculations/sums to actual numbers (once you start to sort the sheet, if you do not do this, a lot of things will get fucked up, especially the overall totals you used the AutoSum option to compile)

That's it. Sort them by category, by ratio, by money per click, by money per signup, alphabetically - whatever.

OH! This is also a good time to have your accounting books open, looking to see when the last time a sponsor sent you money & if you're owed any.

If you have any questions, feel free to ask

[bluemoney]

Guess I have a new project for the weekend.
Thanks for the tutorial Greenie, this is just the tool I was needing

[Fonz]

It only took me 4 days and a newsletter to find out about this thread.
I'm going to try it out as soon as I have an extra few days. Thanks!

[tickler]

Originally Posted by Greenie

So, go to a sponsor, log in & run the stats for whatever time period you choose. I find it easiest to copy & paste the table of results into a blank html file & edit the table by hand

Perhaps try selecting the stats fields, and then copy/paste to a TXT editor. Add a few commas, save the file, and then import into the spreadsheet.

[Greenguy]

Originally Posted by tickler

Perhaps try selecting the stats fields, and then copy/paste to a TXT editor. Add a few commas, save the file, and then import into the spreadsheet.

Whatever works for you I used to go right to the spread sheet, but that was a mess with the colors & fonts & whatnot. For me, it's easier to just edit the tables/cells in my html editor.

[Smoking Dawn]

That was a great tutorial! I am going to give it a shot. Thank you!

[hony]

Great advice. You might want to check out the Excel command:

Edit / Paste Special / Values

That allows you to do a couple of steps you've mentioned rather faster, staying in Excel rather than needing html or text editors.

When I have a sponsor stats page up I'll :

Mouse to highlight the table with the stats
Ctrl-C
Alt-Tab to excel where I already have a blank workbook open
Ctrl-V
Ctrl-C (so it's excel data on the clipboard instead of html)
Ctrl-PgDn (to get to a blank sheet)
[Alt-E] s v [Enter] (Edit, Paste Special, Values)

That gives me all the facts with none of the formatting. It looks funny written down but after a few times it becomes so automatic to the fingers I forget how convoluted it actually is!

And if you can't tell, yes, I spend far too much time in Excel. As I type this in another window I'm moving a few hundred thousand records over from MSAccess into an Excel pivot for some SERP analysis. :-)

Hony

[Greenguy]

Like I said, whatever works for you

[Pusher]

I'm really impressed with all the work you've done, and your willingness to share with the rest of us peons...BUT...I can't seem to download your blank spreadsheet.. I would appreciate any help you could give

Thanks!

[Greenguy]

Pusher - try it now:
http://www.greenguysboard.com/blank.xls

I think the server changed the permissions on it - I'm gonna attach it to the 1st post as well in case it happens again.

[ffblueocean]

Excel file is now working for me! Thanks for the info!!!

[Useless]

Originally Posted by ffblueocean

Excel file is now working for me! Thanks for the info!!!

Can they still afford to pay you without the revenue from Zango?

[Pusher]

Ok - got it...thanks a million!

[TGPBro]

Thanks Greenie, very usefull tool!

[Erato]

Thanks for that, Greenie. Hope all you other guys got that!

[pussyluver]

A lot of work it seems. StatsRemote is useful, but really isn't the same. Tracking receivables to make sure you're getting you $$ is important. You can keep just so many in your head....

Thing to watch out for is sponsors are watching IRS documents closer now. If your EIN numbers don't match-up, no check. I had a sponsor check my SS# number against my dba. Don't think they emailed me, just held the checks. Ahhmmmmm, they took care of it when I questioned the lack of checks.

So tracking your receivables is work, but smart.

[Greenguy]

Originally Posted by pussyluver

...Thing to watch out for is sponsors are watching IRS documents closer now. If your EIN numbers don't match-up, no check. I had a sponsor check my SS# number against my dba....

Companies have been doing that for years - both adult & mainstream, online & offline.

[pussyluver]

Originally Posted by Greenie

Companies have been doing that for years - both adult & mainstream, online & offline.

Yeah, I kinda looked at it as another form of leakage.
Thought they would email or something.

Then they are sponsors that go out of their way to help out an affiliate. I think it was TCG that pointed out I had an error in my linking code on a gallery and might be missing conversions.

I have had sponsors bug me about getting W9 in when I missed it. Appreciate those that care and help. Try to return the favor.

[emc2]

Originally Posted by hony

Great advice. You might want to check out the Excel command:

Edit / Paste Special / Values

That allows you to do a couple of steps you've mentioned rather faster, staying in Excel rather than needing html or text editors.

When I have a sponsor stats page up I'll :

Mouse to highlight the table with the stats
Ctrl-C
Alt-Tab to excel where I already have a blank workbook open
Ctrl-V
Ctrl-C (so it's excel data on the clipboard instead of html)
Ctrl-PgDn (to get to a blank sheet)
[Alt-E] s v [Enter] (Edit, Paste Special, Values)

That gives me all the facts with none of the formatting. It looks funny written down but after a few times it becomes so automatic to the fingers I forget how convoluted it actually is!

And if you can't tell, yes, I spend far too much time in Excel. As I type this in another window I'm moving a few hundred thousand records over from MSAccess into an Excel pivot for some SERP analysis. :-)

Hony

YAY!!! Going to have to spend some time talking with you :-)
Thanks!!!

[pussyluver]

[Neo]

great that Greenie...good man.

[matyko]

Very nice tutorial. I will definitely use my idle time to finetune this for myself

[blogman]

greate tutorial !!! bookmarked

[deejne]

Awesome thread, thanx green!