Public
Cron
Runs every 7 days
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
import { email } from "https://esm.town/v/std/email";
import { fetch } from "https://esm.town/v/std/fetch";
import process from "node:process";
// borrowed from: https://github.com/benvinegar/counterscale/blob/main/app/analytics/query.ts#L24
function accumulateCountsFromRowResult(
counts,
row: {
count: number;
isVisitor: number;
isVisit: number;
},
) {
if (row.isVisit == 1) {
counts.visits += Number(row.count);
}
if (row.isVisitor == 1) {
counts.visitors += Number(row.count);
}
counts.views += Number(row.count);
}
export default async function(interval: Interval) {
// you need to declare these two environment variables:
// api key to access Counterscale Analytics Engine (see Counterscale README)
const apiKey = process.env.counterscaleCfApiKey;
// cf account id
const accountId = process.env.counterscaleCfAccountId;
const url = `https://api.cloudflare.com/client/v4/accounts/${accountId}/analytics_engine/sql`;
// query counts grouped by site over last 7 days
const query = `
SELECT SUM(_sample_interval) as count,
double1 as isVisitor,
double2 as isVisit,
blob8 as site
FROM metricsDataset
WHERE timestamp > NOW() - INTERVAL '7' DAY
GROUP BY isVisitor, isVisit, site
ORDER BY isVisitor, isVisit ASC
`;
let result = await fetch(url, {
headers: {
"Authorization": `Bearer ${apiKey}`,
"Content-Type": "application/json",
},
method: "POST",
body: query,
});
let json = await result.json();
const counts = {};
json.data.forEach((row) => {
if (!counts.hasOwnProperty(row.site)) {
counts[row.site] = {
views: 0,
visitors: 0,
visits: 0,
};
}
accumulateCountsFromRowResult(counts[row.site], row);
});
var text = "Counterscale Weekly Summary\n"
+ "========================================\n\n";
for (var site in counts) {
if (site && counts.hasOwnProperty(site)) {
const siteCounts = counts[site];
text += `${site || "unknown"}\n`;
text += "---------------\n";
text += `Visitors: ${siteCounts.visitors}\n`;
text += `Views: ${siteCounts.views}\n`;
text += "\n\n";
}
}
await email({ subject: "Counterscale Weekly Summary", text });
console.log("Email sent!");
}
Val Town is a social website to write and deploy JavaScript.
Build APIs and schedule functions from your browser.
Comments
Nobody has commented on this val yet: be the first!
April 14, 2024