How to get monthly pageviews programatically? Preferably using Data Explorer

data & reporting
1

Hey there community!

By any chance has anyone wrote a query using Data explorer to fetch monthly pageviews number?

Is there any other programatic way to get those numbers than manually accessing my dashboard?

2

This is the query behind the Consolidated Pageviews report, I hope it helps.

-- [params]
-- date :start_date
-- date :end_date

SELECT
    ar.date,
    CASE 
      WHEN ar.req_type=6 THEN 'Crawlers'
      WHEN ar.req_type=7 THEN 'Logged in users'
      WHEN ar.req_type=8 THEN 'Anonymous users'
    END,
    ar.count AS views
FROM application_requests ar
WHERE req_type IN (6,7,8)
    AND ar.date::date BETWEEN :start_date::date
	AND :end_date::date
ORDER BY ar.date ASC, ar.req_type
4 Likes
3

Perfect! That helps a bit!

Is it possible to fetch this number:

Screenshot 2021-02-02 at 14.45.04
Screenshot 2021-02-02 at 14.45.042268×260 35.1 KB

4

This query lists the total number of pageviews over the period.

-- [params]
-- date :start_date
-- date :end_date

WITH data AS (
    SELECT
        ar.date,
        CASE 
          WHEN ar.req_type=6 THEN 'Crawlers'
          WHEN ar.req_type=7 THEN 'Logged in users'
          WHEN ar.req_type=8 THEN 'Anonymous users'
        END AS Pageview,
        ar.count AS views
    FROM application_requests ar
    WHERE req_type IN (6,7,8)
        AND ar.date::date BETWEEN :start_date::date
    	AND :end_date::date
    ORDER BY ar.date ASC, ar.req_type
)

SELECT Pageview, SUM(views) qtt_views
FROM data
GROUP BY Pageview
3 Likes
5

Perfect! That was what I was looking for.

1 Like
6

Got one more question actually. Maybe that won’t be much of a hustle.

Is there a way to get the results in following format so using sum:

Screenshot 2021-02-02 at 15.29.21
Screenshot 2021-02-02 at 15.29.211824×772 15.8 KB

7

Yes, it is possible, in this case, you need to adjust the format of the date field first and then adjust the group by. Below is the adjusted query.

-- [params]
-- date :start_date
-- date :end_date

WITH data AS 
    (SELECT
        date_part('month', ar.date) AS month,
        date_part('year', ar.date) AS year,
        CASE 
          WHEN ar.req_type=6 THEN 'Crawlers'
          WHEN ar.req_type=7 THEN 'Logged in users'
          WHEN ar.req_type=8 THEN 'Anonymous users'
        END AS pageview,
        ar.count AS views
    FROM application_requests ar
    WHERE req_type IN (6,7,8)
        AND ar.date::date BETWEEN :start_date::date
    	AND :end_date::date
    ORDER BY ar.date ASC, ar.req_type
    )
    
  SELECT 
    month, 
    year, 
    sum(views) AS qtt_views
  FROM  data 
  GROUP BY year, month
  ORDER BY year DESC, month ASC
month year count
1 2021 1000
2 2021 500
1 2020 1500
2 2020 2000
3 2020 2500
4 Likes
8

Perfect! Thanks for help!

2 Likes
9

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.