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?
Sitecore Stack Exchange is a question and answer site for developers and end users of the Sitecore CMS and multichannel marketing software. It only takes a minute to sign up.Sign up to join this community
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.
In the top navigation bar, select Analyze, and then Attributes.
Select Analyze, then Attributes from the Sitecore top navigation.
Reviewing Results To review the results, from the Sitecore launchpad, click Experience Analytics.
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.
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
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
Hope this helps.