# How to get monthly pageviews programatically? Preferably using Data Explorer
**URL:** https://meta.discourse.org/t/how-to-get-monthly-pageviews-programatically-preferably-using-data-explorer/178103
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [February 2, 2021, 1:00pm UTC](https://meta.discourse.org/t/how-to-get-monthly-pageviews-programatically-preferably-using-data-explorer/178103 "2021-02-02T13:00:42Z")
**Posts on this page:** 9
**Page:** 1
### Author:  [@Konrad\_Sopala](https://meta.discourse.org/u/Konrad_Sopala)
#### Post date: [February 2, 2021, 1:00pm UTC](https://meta.discourse.org/t/how-to-get-monthly-pageviews-programatically-preferably-using-data-explorer/178103/1 "2021-02-02T13:00:42Z")
Hey there community!
By any chance has anyone wrote a query using [Data explorer](https://meta.discourse.org/t/32566?silent=true) to fetch monthly pageviews number?
Is there any other programatic way to get those numbers than manually accessing my dashboard?
---
### Author:  [@michebs](https://meta.discourse.org/u/michebs)
#### Post date: [February 2, 2021, 1:25pm UTC](https://meta.discourse.org/t/how-to-get-monthly-pageviews-programatically-preferably-using-data-explorer/178103/2 "2021-02-02T13:25:45Z")
This is the query behind the `Consolidated Pageviews` report, I hope it helps.
```plaintext
-- [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
```
---
### Author:  [@Konrad\_Sopala](https://meta.discourse.org/u/Konrad_Sopala)
#### Post date: [February 2, 2021, 1:46pm UTC](https://meta.discourse.org/t/how-to-get-monthly-pageviews-programatically-preferably-using-data-explorer/178103/3 "2021-02-02T13:46:05Z")
Perfect! That helps a bit!
Is it possible to fetch this number:
---
### Author:  [@michebs](https://meta.discourse.org/u/michebs)
#### Post date: [February 2, 2021, 1:56pm UTC](https://meta.discourse.org/t/how-to-get-monthly-pageviews-programatically-preferably-using-data-explorer/178103/4 "2021-02-02T13:56:10Z")
This query lists the total number of pageviews over the period.
```sql
-- [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
```
---
### Author:  [@Konrad\_Sopala](https://meta.discourse.org/u/Konrad_Sopala)
#### Post date: [February 2, 2021, 2:04pm UTC](https://meta.discourse.org/t/how-to-get-monthly-pageviews-programatically-preferably-using-data-explorer/178103/5 "2021-02-02T14:04:43Z")
Perfect! That was what I was looking for.
---
### Author:  [@Konrad\_Sopala](https://meta.discourse.org/u/Konrad_Sopala)
#### Post date: [February 2, 2021, 2:30pm UTC](https://meta.discourse.org/t/how-to-get-monthly-pageviews-programatically-preferably-using-data-explorer/178103/6 "2021-02-02T14:30:39Z")
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:
---
### Author:  [@michebs](https://meta.discourse.org/u/michebs)
#### Post date: [February 2, 2021, 2:59pm UTC](https://meta.discourse.org/t/how-to-get-monthly-pageviews-programatically-preferably-using-data-explorer/178103/7 "2021-02-02T14:59:18Z")
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.
```sql
-- [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 |
---
### Author:  [@Konrad\_Sopala](https://meta.discourse.org/u/Konrad_Sopala)
#### Post date: [February 2, 2021, 3:33pm UTC](https://meta.discourse.org/t/how-to-get-monthly-pageviews-programatically-preferably-using-data-explorer/178103/8 "2021-02-02T15:33:39Z")
Perfect! Thanks for help!
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [March 4, 2021, 3:34pm UTC](https://meta.discourse.org/t/how-to-get-monthly-pageviews-programatically-preferably-using-data-explorer/178103/9 "2021-03-04T15:34:26Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.