Export Pretty Links with Total and Unique clicks

ExportPrettyLinksWStats.txt

SELECT l.id, l.url, l.slug, l.name, l.redirect_type,
               l.track_me, l.nofollow, l.sponsored, l.param_forwarding,
               gt.meta_value AS google_tracking, d.meta_value AS delay,
               l.created_at AS created_at, l.updated_at AS last_updated_at ,
               (SELECT GROUP_CONCAT(DISTINCT t.slug ORDER BY t.slug ASC SEPARATOR ',')
                  FROM wp_terms AS t
                  JOIN wp_term_taxonomy AS tt
                    ON t.term_id = tt.term_id
                   AND tt.taxonomy = 'pretty-link-category'
                  JOIN wp_term_relationships AS tr
                    ON tr.term_taxonomy_id = tt.term_taxonomy_id
                 WHERE tr.object_id=p.ID) AS link_categories,
               (SELECT GROUP_CONCAT(DISTINCT t.slug ORDER BY t.slug ASC SEPARATOR ',')
                  FROM wp_terms AS t
                  JOIN wp_term_taxonomy AS tt
                    ON t.term_id = tt.term_id
                   AND tt.taxonomy = 'pretty-link-tag'
                  JOIN wp_term_relationships AS tr
                    ON tr.term_taxonomy_id = tt.term_taxonomy_id
                 WHERE tr.object_id=p.ID) AS link_tags,
                (SELECT count(*) FROM wp_prli_clicks as cl WHERE cl.link_id = l.id) AS total_clicks,
                (SELECT count(*) FROM wp_prli_clicks as cl2 WHERE cl2.link_id = l.id AND cl2.first_click = 1) AS unique_clicks
          FROM wp_prli_links AS l
          LEFT JOIN wp_prli_link_metas AS gt
            ON l.id = gt.link_id AND gt.meta_key = 'google_tracking'
          LEFT JOIN wp_prli_link_metas AS d
            ON l.id = d.link_id AND d.meta_key = 'delay'
          JOIN wp_posts AS p
            ON p.ID = l.link_cpt_id
         WHERE l.link_status='enabled'