How can I get all sitecore download event tracking list of item which is not clicked yet (like zero visit items)

Question

I am using Sitecore 10.2 having Sitecore Analytics enabled.

Download events tracking analytics is enabled for all media items like(.pdf, .docs, .xls).

How to get all media items which have not been clicked yet or having zero visits count?

Answer 1

Associating Files With The Download Goal From the Sitecore launchpad, click Media Library.

Click on the Media Library icon on the Sitecore launchpad.

In the content tree, select the PDF item you’d like to track downloads of.

Select a file in the Sitecore media library content tree.

In the top navigation bar, select Analyze, and then Attributes.

Select Analyze, then Attributes from the Sitecore top navigation.

Click Events, and select Download. Select Events, then Download after clicking Attributes in the top navigation.

Click OK, then remember to Save and Publish. Click the OK button.

Reviewing Results To review the results, from the Sitecore launchpad, click Experience Analytics.

Click Experience Analytics from the Sitecore launchpad.

Answer 2

If you open the Database, there are some tables that maintain the tracking under Reporting database. So you can run the query like the one below.

SELECT DimensionKeys.DimensionKey, 
   SiteNames.SiteName, 
   Fact_DownloadEventMetrics.Count, 
   Fact_DownloadEventMetrics.Visits, 
   Fact_DownloadEventMetrics.Value  
FROM
    DimensionKeys INNER JOIN
    Fact_DownloadEventMetrics ON DimensionKeys.DimensionKeyId = Fact_DownloadEventMetrics.DimensionKeyId INNER JOIN
    SiteNames ON Fact_DownloadEventMetrics.SiteNameId = SiteNames.SiteNameId

So You can check the details by adding where conditions into this.

Ref: Downloads table is empty in reporting DB

Also under the reporting database there are some views already created that Enable you to query the Fact_Downloads table. So I found one that can help you to filter by AssetId.

SELECT * FROM [dbo].[Downloads]

You can find them under Views.

You can use this by adding the WHERE condition to filter the records.

If you want to run this using Powershell then you can write the query something like this.

Import-Function -Name Invoke-SqlCommand

$connection = [Sitecore.Configuration.Settings]::GetConnectionString("reporting")

$query = @"
SELECT TOP (10) *
  FROM [dbo].[table]
"@
Invoke-SqlCommand -Connection $connection -Query $query

Ref: https://doc.sitecore.com/xp/en/developers/81/sitecore-experience-platform/the-reporting-database-reference.html

Hope this helps.