Get selection statistics for the past N days
(user_id: str, days: int = 7)
| 1134 | (action = 'opened_report' AND action_type = 'doc_open') |
| 1135 | OR (action = 'doc_dwell_proxy' AND action_type = 'doc_engagement') |
| 1136 | ) |
| 1137 | ORDER BY timestamp ASC, id ASC |
| 1138 | """, |
| 1139 | (user_id, since_timestamp), |
| 1140 | ) |
| 1141 | rows = cursor.fetchall() |
| 1142 | conn.close() |
| 1143 | |
| 1144 | unique_docs = set() |
| 1145 | total_opens = 0 |
| 1146 | dwell_values: List[float] = [] |
| 1147 | for row in rows: |
| 1148 | metadata = _load_json_metadata(row["metadata"]) |
| 1149 | doc_key = _normalize_identifier(metadata.get("doc_token")) or _normalize_identifier(metadata.get("doc_url")) |
| 1150 | if row["action"] == "opened_report": |
| 1151 | total_opens += 1 |
| 1152 | if doc_key: |
| 1153 | unique_docs.add(doc_key) |
| 1154 | elif row["action"] == "doc_dwell_proxy": |
| 1155 | try: |
| 1156 | dwell_seconds = float(metadata.get("dwell_seconds") or 0.0) |
| 1157 | except (TypeError, ValueError): |
| 1158 | dwell_seconds = 0.0 |
| 1159 | if dwell_seconds > 0: |
| 1160 | dwell_values.append(dwell_seconds) |
| 1161 | |
| 1162 | average_dwell_seconds = round(sum(dwell_values) / len(dwell_values), 2) if dwell_values else 0.0 |
| 1163 | return { |
| 1164 | "total_doc_opens": total_opens, |