Behind the Software: how we let hotels in Italy build their own government reports
Once a lengthy task, my Cloudbeds Data Insights team enables Italian hotels to generate their own Italian Tourism Statistics Reports in any format they want. Here’s how we do it.
Hotels and rental properties have a legal obligation in many countries to meet strict reporting requirements. This means a lot of hotels have to manually collect data from different sources, then merge them into a single document and submit it to a government office. Normally those are daily police reports, statistics reports, and immigration reports. It’s a time-consuming task; widely disliked.
Get Daniel Allen’s stories in your inbox
Join Medium for free to get updates from this writer.
Our Cloudbeds property management system offers tailored government reporting solutions in several countries. But I want to focus on Italy as an example of how the Cloudbeds Data Insights API can provide a single interface for accessing and utilizing all of the data from a property. Hotel staff can save precious time when they build their own automation around their data, and streamline cumbersome reporting requirements.
Case in point: the Italian Tourism Report
Italian municipalities use various systems and portals. But the data they collect is virtually the same. In this article we’ll use Italy’s ROSS 1000 portal as an example of our approach. ROSS is part of Italy’s nationwide Italian Statistical Information System.
To read more about ROSS 1000 Click Here.
Submitting a daily report with all details of the guest checking in can be manual and time-consuming in Italy, especially for larger establishments. Some of the portals for collecting such data offer either a file upload or a webservice for transmitting the data they require. In our example, ROSS 1000 offers both. So here’s how we make it easy for our Italian customers to build their own reporting integrations on ROSS. From this point on, our users take the results and build their own Italian Tourism Statistics Reports (ITSRs) that fit ROSS 1000 or another statistical platform format.
Step 1: How they authenticate
Customers must have their own Client ID and Client Secret in order to interact with the service. We start by giving them an easy guide on our customer knowledge base for generating API credentials.
Note: customers who need multi-property credentials follow this guide instead.
Step 2: interacting with Cloudbeds Report Builder
We need a few things in order to build a report. First is the format. Reports are constructed using JSON to specify the Cloudbeds Data Fields we include, the filters customers can apply to that data, and many other features that allow them to customize their results. (Check out the documentation here to learn more about how customers create their own reports.)
Step 3: querying available datasets
Ordinarily each report is built using a single dataset. But we needed to allow customers to pull from two datasets — Guests and Occupancy — in the case of Italian government reporting. Customers can check which datasets are available to them by querying the endpoint we specify in this documentation.
# Required Headers
headers = {
"ACCEPT-LANGUAGE": "en",
"X-PROPERTY-ID": str(YOUR_PROPERTY_ID),
"Authorization": f"Bearer {YOUR_AUTH_TOKEN}",
}
# Get Datasets and print results
datasets_response = requests.get(
"https://api.cloudbeds.com/datainsights/v1.1/datasets",
headers=headers
)
for dataset in datasets_response.json():
print(f"ID: {dataset['id']}, Name: {dataset['name']}")Each dataset will have a set of Cloudbeds Data Fields a customer can use in their report. They can send another request to get the corresponding data fields using their dataset ID.
# Required Headers
headers = {
"ACCEPT-LANGUAGE": "en",
"X-PROPERTY-ID": str(YOUR_PROPERTY_ID),
"Authorization": f"Bearer {YOUR_AUTH_TOKEN}",
}
# Get Cloudbeds Data Fields (CDFs)
dataset_cdfs_response = requests.get(
"https://api.cloudbeds.com/datainsights/v1.1/datasets/1",
headers=headers
)
for category in dataset_cdfs_response.json()["cdfs"]:
print(f"Category: {category['category']}")
for cdf in category["cdfs"]:
print(
f"Cloudbeds Data Field Name: {cdf['name']}, "
f"Cloudbeds Data Column: {cdf['column']}"
)Step 4: the raw reports
Our Italian customers need to generate three reports from the two datasets to collect the data they need for the Italian statistics form. They can always query the API and pick out the data fields they would like to use. For purposes of this article we’ll go ahead and do the work for them. Below you’ll find the three example reports set as python variables. Later we use them in code to query the data. Our users have all the data they need to build ITSRs when they send them as their payload along with the required authentication headers.
- Guest Information. Here we’ve set the filters for the month of February, 2023. But of course they can set the filters to any date range they need.
GUEST_REPORT = {
"property_ids": [
YOUR_PROPERTY_ID,
],
"dataset_id": 2,
"filters": {
"and": [
{
"cdf": {
"type": "default",
"column": "checkin_date"
},
"value": "yesterday",
"operator": "greater_than_or_equal",
}
]
},
"columns": [
{"cdf": {"column": "italian_guest_type"}},
{"cdf": {"column": "checkin_date"}},
{"cdf": {"column": "guest_surname"}},
{"cdf": {"column": "guest_first_name"}},
{"cdf": {"column": "guest_gender"}},
{"cdf": {"column": "guest_birth_date"}},
{"cdf": {"column": "italian_guest_birth_date_municipality_code"}},
{"cdf": {"column": "italian_guest_birth_date_province_code"}},
{"cdf": {"column": "italian_guest_birth_country_code"}},
{"cdf": {"column": "italian_guest_nationality_code"}},
{"cdf": {"column": "guest_city"}},
{"cdf": {"column": "guest_state"}},
{"cdf": {"column": "guest_residence_country"}},
{"cdf": {"column": "guest_address"}},
{"cdf": {"column": "guest_document_type"}},
{"cdf": {"column": "guest_document_number"}},
{"cdf": {"column": "italian_guest_document_issuing_province_code"}},
{"cdf": {"column": "italian_guest_document_issuing_municipality_code"}},
{"cdf": {"column": "guest_document_issuing_country_code"}},
{"cdf": {"column": "checkout_date"}},
],
"settings": {"details": True, "totals": False, "transpose": False},
}- Occupied Rooms. You’ll notice that we’ve also included grouping by rows in this report. This will sum the data into totals per day.
OCCUPIED_ROOMS = {
"property_ids": [
YOUR_PROPERTY_ID,
],
"dataset_id": 4,
"filters": {
"and": [
{
"value": "2023–02–01T00:00:00.000Z",
"cdf": {"type": "default", "column": "stay_date"},
"operator": "greater_than_or_equal",
},
{
"value": "2023–02–28T00:00:00.000Z",
"cdf": {"type": "default", "column": "stay_date"},
"operator": "less_than_or_equal",
},
]
},
"columns": [
{"cdf": {"column": "booking_qty_type_a"}, "metrics": ["sum"]}
],
"settings": {"totals": False, "details": False},
"group_rows": [
{"cdf": {"column": "stay_date"}, "modifier": "day"}
],
"custom_cdfs": [],
}- Unoccupied Rooms and Beds. Lastly, we add one more filter to remove any of the rooms that are occupied before getting the total unoccupied rooms and beds per day.
UNOCCUPIED_ROOMS_AND_BEDS = {
"property_ids": [
YOUR_PROPERTY_ID,
],
"dataset_id": 4,
"filters": {
"and": [
{
"value": "2023–02–01T00:00:00.000Z",
"cdf": {"type": "default", "column": "stay_date"},
"operator": "greater_than_or_equal",
},
{
"value": "2023–02–28T00:00:00.000Z",
"cdf": {"type": "default", "column": "stay_date"},
"operator": "less_than_or_equal",
},
{
"value": "0",
"cdf": {"type": "default", "column": "booking_qty_type_a"},
"operator": "equals",
},
]
},
"columns": [
{"cdf": {"column": "room_available_type_a"}, "metrics": ["sum"]},
{"cdf": {"column": "bed_based_capacity"}, "metrics": ["sum"]},
],
"settings": {"totals": False, "details": False},
"group_rows": [{"cdf": {"column": "stay_date"}, "modifier": "day"}],
"custom_cdfs": [],
}Step 5: query report data
Customers can simply use the requests package for Python to query the data once their new reports variables are set. Below you can see a simple script to query the 3 individual reports and display their data using the “pretty printer” pprint.
# Set up the pretty printer
printer = pprint.PrettyPrinter(indent=4)
# Required Headers
headers = {
"ACCEPT-LANGUAGE": "en",
"X-PROPERTY-ID": str(YOUR_PROPERTY_ID),
"Authorization": f"Bearer {YOUR_AUTH_TOKEN}",
}
# Query parameters for the request
query_params = dict(
mode="Run", # Other option is "Preview", which limits the result to 10,000 records
)
# Query the Guest Information report
print("Querying the Guest Information data…")
report_query_response = requests.post(
"https://api.cloudbeds.com/datainsights/v1.1/reports/query/data",
params=query_params,
headers=headers,
json=GUEST_INFO_REPORT,
)
printer.pprint(report_query_response.json())
# Query the Occupied Rooms report
print("Querying the Occupied Rooms data…")
report_query_response = requests.post(
"https://api.cloudbeds.com/datainsights/v1.1/reports/query/data",
params=query_params,
headers=headers,
json=OCCUPIED_ROOMS,
)
printer.pprint(report_query_response.json())
# Query the Unoccupied Rooms report
print("Querying the Unoccupied Rooms data…")
report_query_response = requests.post(
"https://api.cloudbeds.com/datainsights/v1.1/reports/query/data",
params=query_params,
headers=headers,
json=UNOCCUPIED_ROOMS,
)
printer.pprint(report_query_response.json())And that’s it!
Going forward, our users can take the results and build their own Italian Tourism Statistics Reports in any format that suits their business needs.
The Takeaway
What I liked most about the ITSR build was how autonomy and automation came together. We gave our customers a tool they needed to plug in their own data and create a timesaving solution. All it takes is an easy-to-use data structure. With that, they start building their own automation, visualization, business intelligence, reporting… whatever would simplify their operations and help them make data-informed decisions. To us, that’s Cloudbeds agility at it’s best! Check out our Git repo for more.
To build the ROSS 1000 integration you have two options via a txt file and manual upload https://web.gies.sm/cc_allegati/ross1000/Tracciato_TXT_r4.pdf or via xml and a webservice https://web.gies.sm/cc_allegati/ross1000/Tracciato_XML-WEBSERVICE-2.4%20.pdf
References
Statistics Report Data Fields
We’ve marked all of the ROSS1000 report fields that map to a corresponding Cloudbeds Data Field (N/A for Not Available). The N/A fields do not exist in Cloudbeds PMS by default, but can be added to each account as Custom fields. Once added and populated, their data is accessible via this endpoint: https://hotels.cloudbeds.com/api/docs/#api-CustomFields-getCustomFields.
