38

The New Model profiles on Meta.SE (pretty big improvement!) have a section for "People reached" stat.

Now, unless I'm much mistaken, that stat is specific to a given site on the network.

If that assumption is indeed correct, is there a way to find out the "People Reached" summary across 100% of the sites on the network for all your connected profiles?

27
+550

As indicated in the comments a SEDE query would do so here it is:

To run it you need your accountid and that one can be found by visiting your network account on StackExchange.com and then take the id from the url. Yours is 41067, mine is 281857 and Annonomus Penguin is 1640390.

---- accountid: Account on stackexchnage.com ! "In the url on stackexchange.com"

-- create url from dbname
IF OBJECT_ID ( '#siteurl', 'P' ) IS NOT NULL 
    DROP PROCEDURE #siteurl;
GO

create procedure #siteurl
   @dbname nvarchar(250),
   @res nvarchar(250) OUT
   as
   begin
      declare @pos integer
      declare @rec nvarchar(250)
      set @res = @dbname
      set @pos = CHARINDEX('.', @dbname)
      if (@pos > 0 ) 
      begin
         set @rec = substring(@dbname, @pos+1, len(@dbname))
         exec #siteurl @rec,  @rec output
         set @res = @rec 
                  + '.' 
                  + substring(@dbname, 0, @pos) 
      end
   end;
go

declare @accountid int = ##accountid:int##  -- 93484

-- all databases
declare  db_c cursor for select [name] 
                         from sys.databases 
                         where database_id > 5 -- skip master, temp, model, msdb, Data.SE

declare @db_c_name sysname   -- holds name of db after fetch
declare @sql nvarchar(max) -- holds build up sql string

-- result table
create table #all_reached ( site nvarchar(250)
                            , userid int
                            , displayname nvarchar(50)
                            , reached bigint);

open db_c
fetch next from db_c into @db_c_name
while(@@FETCH_STATUS = 0)
begin
    set @sql = N'use '+ QUOTENAME(@db_c_name) +';
declare @url nvarchar(250)
exec #siteurl ''' + @db_c_name  + ''', @url output
declare @userid int = null
declare @displayname nvarchar(50)
select @userid = id 
     , @displayname = displayname
from users where accountid =  '+ cast(@accountid as nvarchar) + '
if @userid is not null 
begin
insert into #all_reached
select @url
     , @userid
     , @displayname
     , sum(isnull(p.viewcount,0))
from posts p
inner join (
  Select Id 
  From Posts 
  Where PostTypeId = 1 
  and owneruserid = @userid
  Union
  Select ParentId
    From Posts
   Where PostTypeId = 2
     And Id in (select AcceptedAnswerId from Posts)
     and owneruserid = @userid
  Union
  Select ParentId
    From Posts
   Where PostTypeId = 2
     And Score > 5
     and owneruserid = @userid
  Union
  Select a.ParentId
    From Posts a
         Join Posts q On a.ParentId = q.Id
   Where a.PostTypeId = 2
     And a.Score > 0.20 * (select sum(Score) from Posts where ParentId=q.Id)
     And a.Score > 0
     and a.owneruserid = @userid
  Union
  Select x.ParentId
    From (Select a.ParentId, Rank() Over(Partition By a.ParentId Order By ta.Score Desc) AnswerRank
            From Posts a
                 Join Posts ta On ta.ParentId = a.ParentId
           Where a.PostTypeId = 2
             And a.Score > 0
             and a.owneruserid = @userid
          ) x
          Where AnswerRank <= 3) 
rp on rp.id = p.id
end;'

    exec (@sql)
   fetch next from db_c into @db_c_name
end;
close db_c;
deallocate db_c;

select 'all ' as [site]
      , 'networkwide' as [display name]
      , sum(reached)
from #all_reached
union all
select replace(site,'.StackExchange','') as [site]
     , 'http://' 
     + site 
     + '.com/'
     + 'users'
     + '/'
     + cast(userid as nvarchar)
     + '|'
     + displayname
     , reached
from #all_reached
order by 3 desc

drop table #all_reached

This query uses the technique described here to iterate over all databases in SEDE. In the cursor loop I execute this query for each single database and add the results to the temporary table #all_reached. After all databases are processed a select over that temp-table produces this result:

people reached network wide

Notice that there is this feature request to include the statistic in the users table so this query wouldn't require that much resources to run.

  • For some reason it says I've reached 382k people on GD SE when my profile says 273k, any idea why that is? – Dom Apr 23 '15 at 21:36
  • I blame caching ... mine was slightly lower so I expect the actual used query might be a little bit different than we can accomplish with SEDE. – rene Apr 23 '15 at 21:41
  • Typo: The box where you enter your accountID says Account on stackexchnage.com and should be Account on stackexchange.com (swap the a and the n) – angussidney Mar 25 '16 at 1:14
  • Any idea why the query suddenly broke for me? I had 950K a few weeks ago, and I ran it again today, and now it says I have only around 400K. When I added them together my top sites manually, I got in the 900K range. – Anonymous Penguin May 29 '16 at 4:05
  • @AnonymousPenguin the statistic can be very jumpy, specially on highly viewed questions where you have an answer that is on the edge of either being included or not. A new answer or a single down vote does a lot of damage. Also the data you looked at was a week old, while your manually check is live data. SEDE was refreshed 43 minutes ago (when I post this comment) Also the actual implementation serverside might slightly differ. – rene May 29 '16 at 7:07
  • @rene I ran it again this morning (after the update today) and it's back to 950K. shrugs. Anyway, thanks for your hard work on this query! – Anonymous Penguin May 29 '16 at 14:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .