1. InGo Help Center
  2. Reports & Tracking

Get Linkedin Profile URL Data Appended to Reports

Daniel wrote this query, see below. Use this if you have to run a report on LI profile data.

We will be building this feature into the portal but it's not there yet. 

(updated May 9, 2016)

psql  -h db.ingo-internal.net -U ingouser  -d ingo -F $'\t' --no-align -c "
      coalesce(nullif(a.attendeefirstname, ''), u.firstname, '') firstname,
      coalesce(nullif(a.attendeelastname, ''), u.lastname, '') lastname,
      coalesce(nullif(a.email, ''), u.email, '') email,
      coalesce(a.display_name, '') display_name,
      coalesce(nullif(a.company_name, ''), u.company_name, '') company_name,
      coalesce(nullif(a.title, ''), u.company_position, '') title,
      COALESCE(a.picture, u.picture_url) as picture,
      (select array_agg(sp.service order by sp.service desc)::int[] from social_profile sp where sp.user_id = u.id) as authorized_networks,
        select sum(size) total
        from social_profile sp
        inner join users_network_size uns
          on uns.social_profile_id = sp.id
        where sp.user_id = u.id
      ) as network_size,
      null registrants_in_network,
      (select count(*) from social_post sp inner join social_profile spp on sp.social_profile_id = spp.id and spp.user_id = a.user_c where sp.conference_id = a.conference and spp.user_id = a.user_c )  posts,
      (SELECT COUNT(itc.id) total FROM invitedtoconference itc WHERE itc.owner = u.id and itc.conference = [CONF_ID] and itc.sent_date is not null) invites,
      (SELECT COUNT(rd.id) total FROM registration_data rd WHERE rd.clicked_impression_sender_id = u.id AND rd.conference_id = [CONF_ID]) clicks,
      (SELECT COUNT(rd.id) total FROM registration_data rd WHERE (rd.clicked_impression_sender_id = u.id  OR (rd.clicked_impression_sender_id is null AND rd.last_impression_sender_id = u.id)) AND rd.conference_id = [CONF_ID]) AS acquisitions,
      FROM attendee a
      INNER JOIN users u  ON a.user_c = u.id
      INNER JOIN social_profile spp ON spp.user_id = u.id
      WHERE a.conference = [CONF_ID]
      AND a.tester = false
      ORDER BY id DESC" > [FILE_NAME.csv]