1. Home
  2. Docs
  3. FAQs
  4. How To Create Daywise, Week On Week, Month on Month Reports? (And Automate It)

How To Create Daywise, Week On Week, Month on Month Reports? (And Automate It)

If you want to create a dashboard where you want Day-wise reports of week-on-week reports or month-on-month stats, then you can follow the below steps.

Here’s a very simple demo of what it’ll look like:

DigiXport_Database_Automated_Report

It contains 2 tables

  • Daywise Stats
  • Campaign Stats

Note: These will be updated automatically every day and will keep on adding new dates and also new campaigns.

So let’s begin

STEP – 1: Export Raw Data in Sheet

We have selected a single facebook account. Then select a dynamic date range (Eg: Last X days) and split it by date

Then select your required fields. Make sure to have “Date” as a first field. (We have selected fields as shown in an image)

DigiXport_AutomatedReport_UI

DigiXport_AutomatedReport_UI_Additional_option

Lastly, check the create database checkbox and the save query And click on Export Data.

Once done, you will see the output in cell A1 with datewise stat. You can rename this sheet as RAW_FB, as we won’t be creating dashboards in this sheet.

STEP – 2: Create Pivot Table

In the spreadsheet menu, click on Data > Pivot Table.  Click on create.

NOTE: Make sure to remove any integer after D. So for eg we have A1:D. (This will select the entire rows from A to D)

DigiXport_AutomatedReport_PivotTable_option

 

In the pivot table, we have added the Rows as date Start and Values as Amount Spent, Leads, and CPA (Custom Value which is Amount spent / leads)

We have also created 2nd pivot table with Rows as campaign name to get overall campaign stats.

DigiXport_AutomatedReport_PivotTable_option

 

STEP – 3: Create Dashboard Sheet

Now finally, you can create a new sheet where you can import values into your dashboard.

We have simply used an array formula to import some data range from the pivot tables.

DigiXport_AutomatedReport_Dashboard

 

Also, when you schedule the report the raw sheet will get updated which in turn will update your dashboard.

 

How To View Week On Week And Month on Month Reports?

For week on week reports, you can simply follow the above steps till exporting Raw data

Then to add an extra columns in Raw data you can simply, leave an empty column to the right and then add the new columns

For Eg: We have added Start Of Week and Month as a new column in RAW sheet. Formulas are as follows:

  • Start of Week : =ArrayFormula(if(A2:A = “”,“”, A2:A WEEKDAY(A2:A, 1)))
  • Month : =ArrayFormula(if(B2:B = “”,“”, TEXT(A2:A,“mm”)))

DigiXport_AutomatedReport_ExtraColumn

 

Now you can simply update the Range in Pivot table as A1:G. Then add Start of the week to get a week-on-week report and Month to get the month-on-month report.

 

Leave a Reply

Your email address will not be published. Required fields are marked *