Zoho Analytics and BI Admin Assessment

The time has come. Prove yourself as an Analytics Guru (ok, ok, the technical term is a ‘Zoho Analytics and BI Admin’). Here are the details:

You have 3 chances to take the assessment
A score of 80% or higher earns you the certificate
Upon submission, you’ll be able to see which questions you missed but not what the right answers are
You have a time limit of 1 hour, 45 minutes
Feel free to use the Internet, your Analytics account, whatever, to find the right answers, but please don’t talk to another human (not that it would help you much). This is more of a “do you know where to find the right answer?” than a “do you have this memorized?” type of test.
For all of these questions—ASSUME you are on a STANDARD subscription of Zoho Analytics or higher. (That’s the one that comes with Zoho One)

Good luck. May the odds be ever in your favor.

(Ok, another aside—you can STACK the odds in your favor by watching all the courses in this learning path and working through the practice projects)

Time limit: 0

Quiz Summary

0 of 41 Questions completed

Questions:

Information

You have already completed the quiz before. Hence you can not start it again.

Quiz is loading…

You must sign in or sign up to start the quiz.

You must first complete the following:

Results

Quiz complete. Results are being recorded.

Results

0 of 41 Questions answered correctly

Your time:

Time has elapsed

You have reached 0 of 0 point(s), (0)

Earned Point(s): 0 of 0, (0)
0 Essay(s) Pending (Possible Point(s): 0)

Categories

Not categorized 0%

Current
Review
Answered
Correct
Incorrect

Question 1 of 41

1. Question

With which of the following data sources can you set up a recurring sync to Zoho Analytics without any serious custom development? (select all that apply)

Zoho Desk

A spreadsheet on your desktop

A spreadsheet in your Google Drive

Zoho Books

Quickbooks

Salesforce

Your Wordpress back-end

Shopify

Correct

Incorrect
Question 2 of 41

2. Question

How often can you schedule a data sync, assuming you’re on a standard Zoho One plan? (select all that apply)

Every hour

Every 3 hours

Every 6 hours

Every 12 hours

Daily

Weekly

Correct

Incorrect
Question 3 of 41

3. Question

Which of the following are true regarding a data sync to Zoho Analytics? (select all that apply)

There is no out-of-the-box, instantaneous data sync

You can manually trigger a sync at any time (though it takes 5+ minutes to run) but there is a limit of 5 manual syncs in between schedules

There is no way to sync data from Zoho apps to Analytics outside of the 3/6/12/24 hour schedules

You can use Deluge to instantaneously push data from Zoho apps to Analytics but that requires workflow rules and lots of API credits

Custom fields you add to modules in Zoho apps are automatically included in the next data sync that occurs

Correct

Incorrect
Question 4 of 41

4. Question

What data from Zoho CRM syncs to Analytics? (select all that apply)

Default modules (Leads, Accounts, Contacts, Deals)

Zoho user data

Notes left on records

Email records

Custom modules (and custom fields)

Correct

Incorrect
Question 5 of 41

5. Question

Which of the following filters can you apply to reports, without using any formula columns, aggregate formulas, SQL, etc? (select all that apply)

Date ranges

Categorical filters (i.e. select various Deal Stages)

Relative dates (i.e. Last 3 Months)

Wildcard text filters (i.e. "Item Name" includes 'Bicycle')

Numeric Ranges for actual values (i.e. Field X is between 80-120)

Numeric Sums (i.e. sum of Field X is greater than 2000)

Rankings (i.e. Show top 5 customers by sum of Field X)

Multiple OR Criteria (show if sum of Field X is greater than 2000 OR "Item Name" includes Bicycle)

Correct

Incorrect
Question 6 of 41

6. Question

Which data accessibility and configuration options are available to you when sharing reports? (select all that apply)

Select which columns of data appear in 'View Underlying Data'

Permission to export

Apply dynamic filters or not

Set the auto-refresh rate

Tell them when the last data sync occurred

Correct

Incorrect
Question 7 of 41

7. Question

You can use conditional logic in Pivot table formula columns.

True

False

Correct

Incorrect
Question 8 of 41

8. Question

Choose the correct definition of Conditional Formatting within the context of Pivot and Summary tables.

This refers to dynamic filtering, showing different users different views of a report

It refers to the various logical statements you set up in SQL tables

The column rules you set up in the report builder that highlight cells based on set criteria

Correct

Incorrect
Question 9 of 41

9. Question

Which formula column code would return the next birthday of a Contact (using the Birth Date field)?

You don't need a formula column for this

add_year(Birth_Date, 1)

add_year(Birth_Date,age_years(Birth_Date, today())+1)

add_year(year(Birth_Date) + 1)

Correct

Incorrect
Question 10 of 41

10. Question

Is there a way to clone Analytics reports or dashboards?

Yes. Click 'Clone Report' or 'Clone Dashboard' in the dropdown menu in the top right

Yes, but you have to do it in a roundabout way, clicking 'Save As' and giving your duplicated element a new name

No. Hopefully Analytics gets around to adding that feature soon

Why would you ever need to clone a report?

Correct

Incorrect
Question 11 of 41

11. Question

When creating a report or chart that uses data from multiple modules, what does the term ‘paths’ refer to in a report?

Refers to your order of operations of building and sharing a report

Refers to the series of lookup relationships employed to connect the tables used in the report

Refers to a series of successively more difficult steps in your Analytics development, i.e. your "path" to Analytics wizardry

Correct

Incorrect
Question 12 of 41

12. Question

Which code snippet for a formula column would return the AGE (in days) of a Deal? Our definition of AGE, much like human age, is the time from birth (Deal Creation) to death (Closed Won or Lost), but if it’s still open, the age should still be going up with time.

datediff(ifnull("Closing Date",now()),"Created Time")

datediff(if("Stage" IN ('Closed Won', 'Closed Lost'), "Closing Date", now()),"Created Time")

days_between("Created Time", today( ))

days_between("Created Time", if("Closing Date" is not null, "Closing Date", today( ))

Correct

Incorrect
Question 13 of 41

13. Question

Which function should you use in a formula column in order to calculate a conditional commission? The criteria are (a) only Closed Won deals qualify (obviously) and (b) If it’s < $10k, use a 3% commission rate. If it’s between $10k and $30k, a 5% commission rate, and if it’s above $30k, a 7% commission rate.

if()

sum_if()

a series of nested if() functions

a simple multiplication function

Correct

Incorrect
Question 14 of 41

14. Question

What are the 3 main filters we’ve taught?

Simple report filters

User filters

Air filters

Water filters

URL criteria (dynamic filters)

Correct

Incorrect
Question 15 of 41

15. Question

What are the 3 types of date filters you can set?

Actual

Hypothetical

Seasonal

Relative

Relevant

Correct

Incorrect
Question 16 of 41

16. Question

If I want to build a report of ‘Year over year sales for the month of July’ then I should use which date filter?

Actual

Seasonal

Relative

Correct

Incorrect
Question 17 of 41

17. Question

Your manager needs to see a report detailing the number of Closed Deals over the last 36 months, with a year-over-year comparison by month. What type of chart is best?

A stacked bar chart breaking out years by color

A timeline (line chart) with different colored lines by year

A pie chart with sections for month/year

An area chart

A geo chart

Correct

Incorrect
Question 18 of 41

18. Question

I am building a dashboard to share with my investors—who are not Zoho Users—every week. How can I share this dashboard with them? (select all that apply)

Generate an external share link for them to view the live report

Create an Analytics webtab in the CRM for them to view

In Analytics, set up a weekly email of the dashboard to go out to them

Correct

Incorrect
Question 19 of 41

19. Question

For your weekly team meeting, you need a Sales Pipeline report that visually shows both the # of deals and the total $$ amount by Deal Stage. What type of chart is best?

A bar chart

A timeline

A pie chart

An area chart

A geo chart

Correct

Incorrect
Question 20 of 41

20. Question

I need a Pivot report of ‘Year over Year Sales by Quarter’. How do I construct this report?

Throw the Date into the Columns section, showing just the Quarter value. Put Amount in the Data section

Throw the Date into the Rows section, showing just the Quarter value. Put Amount in the Columns section

Throw the Date into the Data section, showing just the Quarter value. Put Amount in the Data section

Throw the Date into the Rows section, showing just the Quarter value. Then throw Date into the Columns section, showing just the Year value. Put Amount in the Data section

Throw the Date into the Rows section, showing just the Year value. Then throw Date into the Columns section, showing just the Quarter value. Put Amount in the Rows section

Correct

Incorrect
Question 21 of 41

21. Question

Is it possible to show different people different views of the same dashboard? i.e. show the team manager everyone’s performance data and each team member just their own data?

Yes, but you have to create a different dashboard for each person

No, go look elsewhere at a more advanced software

Yes, leveraging URL Criteria filters

No, who would ever need that?

Correct

Incorrect
Question 22 of 41

22. Question

Which types of users can see all data in a workspace? (select all that apply)

Account Owner

Organization Admin

Workspace Admin

Analytics User

Correct

Incorrect
Question 23 of 41

23. Question
Match the following SQL-related terms to their definitions:
Sort elements
- A way of renaming a table in order to not have to type out it out every time you reference it
- A connection you establish between two tables of data, usually using some unique identifier
- SQL's version of filtering
- The section where you specify which columns of data you want to bring into your query
- Where you specify the table(s) from which you'll draw your data into the query
- A command for stacking data and sub-queries
Alias
JOIN
WHERE
SELECT
FROM
UNION ALL
Correct

Incorrect
Question 24 of 41

24. Question

If I set a normal filter (filters you add in the ‘Filter’ tab in the report builder) on a report, that filter will still be in effect when I add that report to a dashboard.

True

False

Correct

Incorrect
Question 25 of 41

25. Question

For comprehensive business reporting, what is best practice regarding Analytics workspaces?

Sync all your data sources to one workspace in Analytics

Sync every app to a different workspace so that if one gets deleted you don't lose everything

Only sync data sources to Analytics that don't have their own reporting capabilities

Correct

Incorrect
Question 26 of 41

26. Question

Whether in reports, URL Criteria, or SQL filters, does capitalization matter when using filters on text fields?

Yes

No

Correct

Incorrect
Question 27 of 41

27. Question

Do Zoho users need to be added to Analytics in order to view reports and dashboards?

Yes, you need an Analytics license for anyone to whom you want to share reports

No, you can create permalinks to share reports or throw Analytics reports into webtabs in other Zoho apps they use

Correct

Incorrect
Question 28 of 41

28. Question

Select all of the following that are possible when scheduling reports to be emailed.

Schedule reports to go out daily, weekly, monthly, quarterly, or yearly

Send reports in a PDF or Excel/CSV format

Send to non-Zoho users

Send to groups of Zoho users

Opt to not send when the report is empty

Customize the email message

Correct

Incorrect
Question 29 of 41

29. Question

Report description: A commissions report that is a simple 3% cut of all Closed Won Deals in the CRM. Do I need to use SQL or not in order to build this report?

Yes

No

Correct

Incorrect
Question 30 of 41

30. Question

Report description: A commissions report that awards commissions for all sold items marked as Commissionable in Zoho Books. Invoices must be paid within 15 days and commissions are awarded by payment and not by the invoice itself. Also, if items are returned by the client (credit notes) and refunded, that negatively counts toward commissions (i.e. commissions are rescinded). Do I need to use SQL or not in order to build this report?

Yes

No

Correct

Incorrect
Question 31 of 41

31. Question

I want to build a report showing the Customers with the most Invoices (count) and total $$ amount sold for the last 30 days. I want to be able to sort by either Invoice Count or Invoice Total. Do I need to use SQL?

Yes

No

Correct

Incorrect
Question 32 of 41

32. Question

I want to build a Pivot report showing the most commonly returned items (think Credit Notes). Based on this data, we may need to reevaluate our return and refund policies. I would like to see the Item name and the quantity returned in the last ‘x’ period of time. How can I build this report without resorting to SQL?

Throw the Credit Note Item Quantity into the Data section, the Item Name into the Rows, and the Credit Note Date into the Columns section

Throw the Credit Note Item Quantity into the Data section, the Item Name into the Rows, and the Credit Note Date in as a filter. Set your filter, save the report, and then 'Save As' to duplicate the report and then change the filter for the next date period you need

Throw the Credit Note Item Quantity into the Data section, the Item Name into the Rows, and the Credit Note Date in as a relative User Filter

You need SQL to build this report

Correct

Incorrect
Question 33 of 41

33. Question

I want to build a report showing, for any Active Item, the stock currently on hand, the amount ‘Committed’ (in Open Sales Orders), and the amount Sold in the last 120 days. How can I build this report without resorting to SQL?

Throw the Invoice Item Quantity, Sales Order Item Quantity, and Quantity Available (Stock In Flow table) into the Data section, the Item Name (from the Items table) into the Rows, the Invoice Date (last 120 days) as a relative date filter, and the Sales Order Status as a filter (Open)

This is not something you can build with or without SQL. But, you can build separate reports that tell you the same thing. Build an 'Items Sold' report, a 'Committed Stock' report, and a 'Stock on Hand' report, throw them all in a dashboard, and use a 'Item Name' user filter to filter all reports at once for any item

Throw the Item Name (from Items table) into the Rows section. Throw the 'Quantity Available' (from Stock In Flow) into the data section. Create a 'Committed Stock' aggregate formula for the Sales Order Items table that sums up the Quantity if the SO is Open and a 'Sold Last 120 Days' aggregate formula for the Invoice Items table, summing up the Quantity if the Invoice Date is in the last 120 days. Throw those 2 aggregate formulas into the Data section. Item Name as a user filter

You do need SQL to build this report

Correct

Incorrect
Question 34 of 41

34. Question

You can build a lot in Analytics without SQL. However, what are some of the advantages that SQL gives you over traditional report-building in Analytics? (select all that apply)

Better control over filter criteria

You directly control what JOINs happen and where. No confusion regarding JOIN paths

User Filters only work for reports built on top of SQL tables of data

You consolidate all your functions/formulas/filters in one place instead of formula or aggregate columns and filters spread across multiple tables and reports

Controlling which columns appear in your SQL report make it easier to toggle what appears in 'View Underlying Data' and in data sharing permissions and settings

Correct

Incorrect

Question 35 of 41

35. Question

Match the aggregate formula code to the metrics they return.

Sort elements

sum(if("Sales Orders (Zoho Books)"."Status" IN ('draft','open'),"Sales Order Items (Zoho Books)"."Quantity",0))
if(sum(“Deals”.”Deal Size”) < 0,0,
if(sum("Deals"."Deal Size") < 5000,.03*sum("Deals"."Deal Size"),
if(sum("Deals"."Deal Size") < 10000,1500+.05*(sum("Deals"."Deal Size")-50000),
4000+.07*(sum("Deals"."Deal Size")-100000)))
rank(sum(if("Deals"."Stage" LIKE 'Closed Won',"Deals"."Deal Size",0)),'desc')
(lag(sum(if(“Deals”.”Stage” LIKE ‘Closed Won’,”Deals”.”Deal Size”,0)),1) +
lag(sum(if(“Deals”.”Stage” LIKE ‘Closed Won’,”Deals”.”Deal Size”,0)),2) +
lag(sum(if(“Deals”.”Stage” LIKE ‘Closed Won’,”Deals”.”Deal Size”,0)),3))
/3
sum(if("Customers"."Rank" < 101,"Customers"."Revenue",0))
avgif("Deals"."Stage" = 'Closed Won' AND "Deals"."Closing Date" > subdate(currentdate(),'365'),"Deals"."Deal Size")

Committed Stock (item quantity in Open Sales Orders)

Total commission amounts for a tiered Commissions structure

A ranking of closed deals

Returns the average of the prior 3 Deal Size values (preferably grouped by ascending dates)

Sums up the total Revenue value for the top 100 customers

Returns the average Deal Size for Closed Won deals in the last year

Correct

Incorrect

Question 36 of 41

36. Question

When would you use the lag() aggregate formula?

When I want to reference a cell in a prior row in a table, like in an Excel formula

When I want to reference a cell in a subsequent row in a table, like in an Excel formula

lag() is not an aggregate formula but it requires one

Correct

Incorrect
Question 37 of 41

37. Question

You cannot use aggregate formulas within normal formula columns.

True

False

Correct

Incorrect
Question 38 of 41

38. Question

Data integrity is important. Always double check your share settings and test everything out where possible.

True

False

Correct

Incorrect
Question 39 of 41

39. Question

Select all of the benefits of setting up the automatic distribution of pre-filtered client or partner dashboards as taught in our course. (Don’t confuse this with distributing a dashboard to internal Zoho users. We’re talking about pre-filtered reports for external client users)

You ensure each partner/client only sees their own data

You don't have to create individual reports for every single client

Super simple to set up—no coding required

This type of reporting is a strong value-add

There is no manually exporting/running/sending of reports once this is set up

Correct

Incorrect
Question 40 of 41

40. Question

Which of the following show correct syntax for JOINing the Deals and Accounts tables in a SQL query? (select all that apply)

FROM Deals d
JOIN Accounts a ON a.Id = d.”Account Name”

FROM Deals d
JOIN Accounts a ON a.Id = d.Account Name

FROM “Deals” d
JOIN “Accounts” a ON a.Id = d.”Account Name”

FROM Deals
JOIN Accounts ON Id = “Account Name”

FROM “Deals”
JOIN “Accounts” ON “Deals”.”Account Name” = “Accounts”.”Id”

Correct

Incorrect
Question 41 of 41

41. Question

Which of the following correctly only includes CLOSED deal records where the Account type is ‘Partner’ or ‘Referrer’ and the Deal Closing Date is in the last 90 days? (select all that are correct)

WHERE a."Account Type" IN ('Partner', 'Referrer') OR d."Closing Date" > sub_date(today(),90) OR d.Stage LIKE 'Closed Won'

WHERE a."Account Type" LIKE ('Partner' OR 'Referrer') AND is_last_nday(d."Closing Date") LIKE '1' AND d.Stage LIKE 'Closed Won'

WHERE a."Account Type" IN ('Partner', 'Referrer') AND is_last_nday(d."Closing Date",90) LIKE '1' AND d.Stage LIKE 'Closed Won'

WHERE a."Account Type" IN ('Partner', 'Referrer') AND d."Closing Date" > today()-90 AND d.Stage LIKE 'Closed Won'

Correct

Incorrect

Zoho Analytics and BI Admin Assessment

Quiz Summary

Information

Results

Results

Categories

1. Question

2. Question

3. Question

4. Question

5. Question

6. Question

7. Question

8. Question

9. Question

10. Question

11. Question

12. Question

13. Question

14. Question

15. Question

16. Question

17. Question

18. Question

19. Question

20. Question

21. Question

22. Question

23. Question

Sort elements

24. Question

25. Question

26. Question

27. Question

28. Question

29. Question

30. Question

31. Question

32. Question

33. Question

34. Question

35. Question

Sort elements

36. Question

37. Question

38. Question

39. Question

40. Question

41. Question