1

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?

enter image description here

2 Answers 2

0

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

enter image description here

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.

enter image description here Select a file in the Sitecore media library content tree.

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

enter image description here

Select Analyze, then Attributes from the Sitecore top navigation.

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

Click OK, then remember to Save and Publish. enter image description here Click the OK button.

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

Click Experience Analytics from the Sitecore launchpad. enter image description here

0

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.

enter image description here

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.

1
  • how can we get a list of items from "Reporting database" that visit item is zero with help of PowerShell script
    – md shahbaj
    Commented Apr 10, 2023 at 11:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.